Homework # 8 – Due February 9, 2006

Submitted by : _____KEY___________________________________

1.Consider the relation:

PERSON_2 (Name, Sibling, Shoe_Size, Hobby)

Assume Name àà Sibling, Name à Shoe_Size, and Name àà Hobby.  Describe deletion, modification, and insertion anomalies for this relation.

Using the data in Figure 3-18 and adding Shoe_Size, we get the following relation:

Name

Sibling

Shoe_Size

Hobby

Jones

Fred

E

Model boats

Jones

Fred

E

Scuba Diving

Jones

Sally

E

Model boats

Jones

Sally

E

Scuba Diving

Jones

Frank

E

Model boats

Jones

Frank

E

Scuba Diving

Greene

Nikki

B

Embroidery

Chau

Jonathan

C

Scuba diving

Chau

Eileen

C

Scuba diving

 

 

a.  Deletion Anomaly

If a Name drops a Hobby, one line must be deleted for each Sibling.
For example, if Jones drops the Hobby of model boats, three lines will
have to be dropped – one for Sibling Fred, one for Sibling Sally and
one for Sibling Frank.

 

2 points for red statement    1 extra point for green

 

 

 

b.  Modification Anomaly

 

If a Hobby is changed for a Name with multiple siblings, we will have data integrity problems if the Hobby is not changed in every
associated row. 

For example, if Jones switches for model boats
to model airplanes, we will have to modify three rows. 

2 points for red statement

1 extra point for green

 

c.  Insertion Anomaly

 

If a Name adds a new Hobby, a line must be inserted for each Sibling.
                        For example, if Chau takes up the Hobby of model boats, two lines will
                        have to be added – one for Sibling Jonathan and one for Sibling Eileen.

 

2 points for red statement

1 extra point for green

 

 

 

 

d.  How would you place the PERSON_2 relation into 4NF? (show the table(s)) and the key(s)

 

PERSON_4 (Name, Shoe_Size)

PERSON_4_SIBLING (Name, Sibling)

PERSON_4_HOBBY (Name, Hobby)

Note that in the relation PERSON_4_SIBLING, the primary key is the composite key (Name, Sibling) and the column Name functions as a foreign key.

Note that in the relation PERSON_4_HOBBY, the primary key is the composite key (Name, Hobby) and the column Name functions as a foreign key.

Name of relation (PERSON_4) doesn’t matter...

 

 

2 points for each red statement having correct values in parenetheses. 

1 additional point for each red statement having correct underlining.

 

 

 

IGNORE green

 

 

Page 97 in our text 

A.1

1.   Phone à (FirstName, LastName)

TRUE when phone numbers are unique.

 

 

 

2 points for red statement

 

 

 

A.3

3.   (Phone, LastName) à FirstName

TRUE when each combination of phone number and FirstName is unique.

2 points for red statement

 

A.5

5.   Phone àà LastName

TRUE when a phone number is associated with more than one LastName. 

 

2 points for red statement

 

 

page 114 

 

problem 4.3  - try your answer and indicate which dbms (Access or SQL Server) it worked in.

SELECT COUNT (*)

FROM RETAIL_ORDER;

 

SELECT TOP 15

FROM RETAIL_ORDER:

 

2 points for each correct red statement

 

 

 

 

problem 4.4 

The following SQL statement will return any values of the foreign key that violate the constraint:

SELECT   DepartmentName

FROM     EMPLOYEE

WHERE    DepartmentName NOT IN

         (SELECT  DepartmentName

         FROM     DEPARTMENT

         WHERE    EMPLOYEE.DepartmentName =

                  DEPARTMENT.DepartmentName);

 

 

 

2 points for correct statement