Question

Relational Database Design Theory, please answers ALL parts because they are related to each other, and I can't separate it into different questions. Please help!

By looking at the PHLogger table:

A. List all non-trivial functional dependencies.


B. What is the highest normal form the PHLogger table is in currently?

C. The external consulting experts at DBInstructor, Inc., have noticed that city and state of an address can be inferred by its postal code (zip code). What new functional dependencies would be introduced by codifying this rule?

D. What is the highest normal form the PHLogger table is in after adding the new functional dependencies?

E. Decompose the PHLogger table into multiple tables to the highest normal form possible.

F. After decomposition, what is the highest normal form design that you could produce that is lossless and dependency preserving[3NF/BCNF]? Explain.

PHLogger contact address street city pcode state CREATE TABLE PHLogger( phlid name address_street VARCHAR(50) NOT NULL, address_city address_state CHAR(2) NOT NULL, address_pcode VARCHAR(10) NOT NULL, PRIMARY KEY (phlid), FOREIGN KEY (phlid) REFERENCES User (phlid) ON DELETE CASCADE VARCHAR(8), VARCHAR(50) NOT NULL, VARCHAR(20) NOT NULL,

Answer 1

A. non-trivial functional dependencies:

            address_pcode $\rightarrow$ address_city, address_state

In the above given, address_city and address_state can be inferred by address_pcode.

B. The highest normal form the PHLogger table is in currently is 2NF.

its not in 3NF as address_city and address_state can be inferred by address_pcode which is neither superkey nor prime key attribute i.e., transitive dependency exists (i.e., phlid$\rightarrow$address_pcode and address_pcode$\rightarrow$ address_state, address_city).

C.

phlid

name

address_street

address_pcode

phlid$\rightarrow$ name, address_street,address_pcode

address_pcode$\rightarrow$address_city,address_state

address_pcode

address_city

address_state

D. The highest normal form the PHLogger table is in after adding the new functional dependencies is Boyce-Codd Normal Form (BCNF).

as its in 3NF and phlid is superkey in the relation name, address_street, address_pcode and address_pcode is the superkey in relation address_city, address_state.

phlid$\rightarrow$ name, address_street,address_pcode

address_pcode$\rightarrow$address_city,address_state

E.   PHLogger

phlid

name

address_street

address_pcode

      PCode

address_pcode

address_city

address_state

F. After decomposition, the highest normal form design that you could produce that is lossless and dependency preserving is BCNF.

After decomposition, its in 3NF

and

phlid is superkey in the relation name, address_street, address_pcode and address_pcode is the superkey in relation address_city, address_state.

phlid$\rightarrow$ name, address_street,address_pcode

address_pcode$\rightarrow$address_city,address_state