Lab: 4, Unit 4



Lab: 4, Unit 4

Chapter 11 (Kroenke), Pages 405 - 406, LAB is from Review Questions 11.1 - 11.8

11.1 Install SQL Server 2000 and create a database named MEDIA. Use the default settings for file sizes, names, and locations.

Answer: Follow the directions as explained on pages 372-373 of the Kroenke book.

11.2 Write a SQL statement to create a table named PICTURE with columns Name, Description, DateTaken, and FileName. Assume that Name is char(20), Description is varchar(200), DateTaken is smalldate, and FileName is char(45). Also assume that Name and DateTaken are required. Use Name as the primary key. Set the default value of Description to '(None)'.

Answer: Name and Description are SQL Server keywords, so we’ll put them in brackets.

CREATE TABLE PICTURE(

[Name] Char(20) NOT NULL,

[Description] Varchar(200) NULL DEFAULT '(None)',

DateTaken SmallDateTime NOT NULL,

FileName Char(45) NULL,

CONSTRAINT PicturePK PRIMARY KEY ([Name])

);

11.3 Use the SQL Query Analyzer to submit the SQL statement in question 11.2 to create the PICTURE table in the MEDIA database.

Answer: Follow the directions on pages 373-375 of the Kroenke book. Start in the SQL Server Enterprise Manager. Launch the SQL Query Analyzer from the Tools menu, enter the CREATE TABLE statement from 11.2 above, and then click the arrow button (Execute Query button) or the F5 key to execute the command.

Alternatively, the CREATE TABLE statement from 11.2 can be created in a ASCII text editor such as Notepad using the file extension *.sql. The SQL Query Analyzer is opened as described above, the resulting *sql file can be opened using the File | Open command. With the *.sql file open and visible, click the arrow button (Execute Query button) or the F5 key to execute the command.

Question 11.2 does not mention adding data, but here is a dataset for use with this table:

INSERT INTO PICTURE VALUES (

'SpotAndBall', 'My dog Spot chasing a ball', '07-Sep-04', 'spot00001.jpg');

INSERT INTO PICTURE VALUES (

'SpotAndCat', 'My dog Spot chasing a cat', '08-Sep-04', 'spot00002.jpg');

INSERT INTO PICTURE VALUES (

'SpotAndCar', 'My dog Spot chasing a car', '11-Oct-04', 'spot00003.jpg');

INSERT INTO PICTURE VALUES (

'SpotAndMailman', 'My dog Spot chasing a mailman - BAD DOG!', '22-Nov-04', 'spot00004.jpg');

INSERT INTO PICTURE VALUES (

'TheJudgeAndI', 'I explain that Spot is really a good dog, and did not mean to chase the mailman', '13-Dec-04', 'me00001.jpg');

11.4 Write a CREATE TABLE statement to create the table SLIDE_SHOW (ShowID, Name, Description, Purpose). Assume that ShowID is a surrogate key. Set the data type of Name and Description however you deem appropriate. Set the data type of Purpose to char (15) and limit it to the set of values ('Home', 'Office', 'Family', 'Recreation', 'Sports', 'Pets'). Execute your CREATE TABLE statement using Query Analyzer.

Answer: Name and Description are SQL Server keywords, so we’ll put them in brackets.

CREATE TABLE SLIDE_SHOW(

ShowID Int NOT NULL IDENTITY (1000,1),

[Name] Char(20) NOT NULL,

[Description] Varchar(200) NULL,

Purpose Char(15) NOT NULL,

CONSTRAINT SlideShowPK PRIMARY KEY (ShowID),

CONSTRAINT ValidPurpose CHECK

(Purpose IN ('Home', 'Office', 'Family', 'Recreation',

'Sports', 'Pets'))

);

Here is a dataset for use with this table:

INSERT INTO SLIDE_SHOW ([Name], [Description], Purpose) VALUES (

'My Dog Spot', 'My dog Spot likes to chase things', 'Pets');

INSERT INTO SLIDE_SHOW ([Name], [Description], Purpose) VALUES (

'My Day In Court', 'I explain that Spot is really a good dog', 'Home');

11.5 Use SQL and the Query Analyzer to create the table SHOW_PICTURE_INT as an intersection table between PICTURE and SLIDE_SHOW. Create appropriate relationships between PICTURE and SHOW_PICTURE_INT and between SLIDE_SHOW and SHOW_PICTURE_INT. Set the referential integrity properties to disallow any deletion of a SLIDE_SHOW row that has any SHOW_PICTURE_INT rows related to it. Set the referential integrity properties to cascade deletions when a PICTURE is deleted. Cascade updates to PICTURE.Name.

Answer: Since ShowID is a surrogate key, there is no need to cascade UPDATEs to SHOW_PICTURE_INT. By not cascading DELETEs to SHOW_PICTURE_INT, no SLIDE_SHOW that has a picture in it can be deleted. Thus we set ON DELETE NO ACTION (the default, and we really don’t have to specify it).

CREATE TABLE SHOW_PICTURE_INT(

ShowID Int NOT NULL,

PictureID Char(20) NOT NULL,

CONSTRAINT IntPK PRIMARY KEY (ShowID, PictureID),

CONSTRAINT ShowFK FOREIGN KEY (ShowID)

REFERENCES SLIDE_SHOW (ShowID)

ON UPDATE NO ACTION

ON DELETE NO ACTION,

CONSTRAINT PictureFK FOREIGN KEY (PictureID)

REFERENCES PICTURE ([Name])

ON UPDATE CASCADE

ON DELETE CASCADE

);

Here is a dataset for use with this table:

INSERT INTO SHOW_PICTURE_INT VALUES (1000, 'SpotAndBall');

INSERT INTO SHOW_PICTURE_INT VALUES (1000, 'SpotAndCat');

INSERT INTO SHOW_PICTURE_INT VALUES (1000, 'SpotAndCar');

INSERT INTO SHOW_PICTURE_INT VALUES (1000, 'SpotAndMailman');

INSERT INTO SHOW_PICTURE_INT VALUES (1001, 'SpotAndMailman');

INSERT INTO SHOW_PICTURE_INT VALUES (1001, 'TheJudgeAndI');

11.6 Write a SQL statement to create a view name PopularShows that has SLIDE_SHOW.Name and PICTURE.Name for all slide shows that have a Purpose of either 'Home' or 'Pets'. Execute this statement using the SQL Query Analyzer.

CREATE VIEW PopularShows AS

SELECT S.[Name] AS Slide_Show_Name, P.[Name] AS Picture_Name

FROM SLIDE_SHOW AS S, PICTURE AS P, SHOW_PICTURE_INT AS SPI

WHERE S.ShowID = SPI.ShowID

AND SPI.PictureID = P.[Name]

AND S.Purpose IN ('Home', 'Pets');

Note that the semicolon at the end of this statement is not used when the statement is run in SQL Query Analyzer.

Answer: To test this view, use:

SELECT * FROM PopularShows;

[pic]

11.7 Open the view design tool and determine that PopularShows was constructed correctly. Modify this view to include PICTURE.Description and FileName.

Answer: Use the Enterprise Manager and click on Views. Right-click on the PopularShows view and choose Design View. In the top frame, click on the PICTURE object – which appears by its alias P - then click Description and FileName checkboxes.

To test this view, use:

SELECT * FROM PopularShows;

[pic]

[pic]

11.8 Can the SQL DELETE statement be used with the PopularShows view? Why or why not?

Answer: No, this view involves two tables. You can only DELETE a view that contains a single table.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download