SQL COMMANDS

The questions for this part are based on a database schema that contains the tables shown below:

SWIM_CLASS

SwimCode (PK) / SwimLevel
19419 / Aquaquest 5
19422 / Aquaquest 5
19417 / Aquaquest 5
21051 / Aquaquest 9
20275 / Aquaquest 8

POOL_LOCATION

PoolCode / PoolLocation
GW / GarnetWilliamsCC
DC / DufferinClarkCC
WC / WoodbridgePool
VC / VaughanCC
MC / MapleCC

1. Create a table called SWIMDAY to store the sample data shown below about the days when swim classes are held at pools in Vaughan’s community centers. You must also define the following constraints for the table.

Ø  The primary key consists of the SwimCode and DayCode Columns

Ø  A value must be provided for the maximum number of participants

Ø  The maximum number of participants per SwimLevel can only be 8 or 12

Ø  If a value is provided for the number of participants for a swim class, it cannot exceed the maximum number of participants

SWIMDAY

SWIMCODE / DAYCODE / MAX#_STUDENTS / NUMBERENROLLED
21051 / M / 12 / 4
19422 / M / 8 / 7
19417 / T / 8
21051 / R / 12 / 11
20275 / T / 12 / 12

Create table SWIMDAY

(SwimCode Int (5)

DayCode Char (1)

Max#_Students Int (2) NotNull

NumberEnrolled Int (2)

SwimDay_PK Primary Key(SwimCode,DayCode)

Max#_Students_CK Check (Max#_Students in (8,12))

NumberEnrolled_CK Check (NumberEnrolled<=Max#_Enrolled)

2.. Write the statement that adds the data in the last row of the table shown above to the SWIMDAY table.

INSERT INTO SwimDay

Values

(20275, ‘T’, 12, 12)

3.  Add a constraint to ensure that any value entered for the SwimCode entered in the SwimDay table must exist as a value in the SwimClass table.

ALTER TABLE SwimDay

Add Constraint SwimCode_FK

FOREIGN KEY (SwimCode)

References SEIM_CLASS (SwimCode)

4.  Add a column labelled PoolCode to the SwimDay table to store text data that refers to the PoolCode column in the Pool_Location table.

ALTER TABLE SWIMDAY

Add column PoolCode Char (2)

References POOL_LOCATION (PoolCode)

5.  Ensure that duplicate values cannot be entered for the SwimCode in the SwimClass table.

ALTER TABLE SWIM_CLASS

Add constraint SwimCode_UN

UNIQUE (SwimCode)

6. Create a query that displays the SwimCode, SwimLevel and DayCode for all Swim classes on all days.

SELECT SwimCode, SwimLevel, DayCode

FROM SwimClass, SwimDay

WHERE Swim_Class (SwimCode) = SwimDay(SwimCode)

7.  Create a view called Aqua9 that contains the SwimCode, Swim Level, DayCode, and PoolCode for all sections of Aquaquest 9.

CREATE VIEW Aqua9 as

SELECT SwimCode, SwimLevel, DayCode, PoolCode

FROM Swim_Class, SwimDay

WHERE Swim_Class(SwimCode) = SwimDay(SwimCode)

And DayCode =’T’

8.  Remove the Number_Enrolled column from the SwimDay table.

ALTER TABLE SwimDay

Drop Column Number_Enrolled

9.  Remove all Tuesday classes from the SwimDay table

DELETE FROM SwimDay

Where DayCode =’T’

10.  Remove the SwimDay table and all the data in this table.

DROP

TABLE SWIMDAY

SOLUTION TO Calgary Zoo Normalization Question

Userview#1

UNF

ANIMALHISTREC [AnimalID, AnimalName, Species,Gender, Birthdate, Birthweight, ArrivalDate, Origin (CheckDate, Weight, Health, Disposition, EmpName, EmpNo, EmpPosition)]

1NF

ANIMALHISTREC [AnimalID, AnimalName, Species,Gender, Birthdate, Birthweight, ArrivalDate, Origin]

HISTREC[AnimalID, CheckDate, Weight, Health, Disposition, EmpName, EmpNo, EmpPosition]

2NF = 1NF

3NF

ANIMALHISTREC [AnimalID, AnimalName, Species,Gender, Birthdate, Birthweight, ArrivalDate, Origin]

EMPLOYEE [EmpNo, EmpName, EmpPosition]

HISTREC[AnimalID, CheckDate, Weight, Health, Disposition, EmpNo (FK)]

Userview #2

UNF

CHECKUPFORM [EmpNo, EmpName, CheckDate, (BldgNo, BldgName, (AnimalID, AnimalName, Species, Weight, Health, Disposition))]

1NF

CHECKUPFORM [EmpNo, EmpName, CheckDate]

BLDGCHECKUP [EmpNo, CheckDate, BldgNo, BldgName]

CHECKUP [EmpNo, CheckDate, BldgNo AnimalID, AnimalName, Species, Weight, Health, Disposition]

2NF

EMPLOYEE [[EmpNo, EmpName]

CHECKUPFORM [EmpNo, CheckDate]

BLDG [BldgNo, BldgName]

BLDGCHECKUP [EmpNo, CheckDate, BldgNo]

ANIMAL [ AnimalID, AnimalName, Species]

CHECKUPANIMAL [EmpNo, CheckDate, AnimalID,Weight, Health, Disposition]

CHECKUP [EmpNo, CheckDate, BldgNo AnimalID]

3NF = 2NF

NOTE: I have treated the nested repeating groups in a manner different from the manner that I taught you this semester. If we were the follow the pattern which I taught you this semester, Userview#2 would be normalized as follows:

UNF

CHECKUPFORM [EmpNo, EmpName, CheckDate, (BldgNo, BldgName, (AnimalID, AnimalName, Species, Weight, Health, Disposition))]

1NF

CHECKUPFORM [EmpNo, EmpName, CheckDate]

BLDGCHECKUP [EmpNo, CheckDate, BldgNo, BldgName]

CHECKUP [BldgNo AnimalID, AnimalName, Species, Weight, Health, Disposition]

2NF

EMPLOYEE[[EmpNo, EmpName]

CHECKUPFORM [EmpNo,, CheckDate]

BLDG [BldgNo, BldgName]

BLDGCHECKUP [EmpNo, CheckDate, BldgNo]

ANIMAL [ AnimalID, AnimalName, Species]

CHECKUPANIMAL [BldgNo, AnimalID,Weight, Health, Disposition]

CHECKUP [ BldgNo AnimalID]

3NF =2NF (but the checkup animal makes no sense at all since the building has nothing to do with the animal checkup…)