In this lab you will revisit your database schema from lab 2, and work on improving the schema. If you do not like your original schema, feel free to generate a new one. Make sure the schema you use conforms to the requirements of the second lab assignment.

"make sure that it contains at least five relational schemas and that each relation has at least three attributes. Moreover, make sure that the relational schemas contain interrelated data, so that interesting tables can be created by joining appropriately some of the tables in your database. Finally, choose a key for each relational schema. Also enforce foreign key constraints when applicable."

Also, if the schema you initially turned in did not conform to these requirements (as indicated in the grade email you received), please alter it so that it does.

1. Give the schema you are starting with.
2. Write down meaningful functional dependencies for your schema (they should be non-trivial). Make sure that all functional dependencies that hold in the data you wish to store are either listed, or can be inferred from the listed dependencies.
3. Determine if the primary keys you originally chose are candidate keys. Replace those that are not by candidate keys.
4. Determine if there are candidate keys other than your primary keys.
5. BCNF and 3NF
1. Test if your schema is in BCNF. If not, write down one or more functional dependencies that violate the conditions for being in BCNF
2. Decompose your original schema into a BCNF, lossless join decomposition.
3. Test if your original schema is in 3NF. If not, write down one or more functional dependencies that violate the conditions for being in 3NF. Make sure you go back to the original schema and not your schema from the end of the previous step.
6. Pick a relation from your schema, say R, and write a SQL query to test whether an instance of that relation satisfies the primary key constraint (that is, view the primary key as a functional dependency and then write a SQL query that tests whether a given instance satisfies the primary key constraint).
   

For this assignment, you will turn in a single file which contains the results of these actions. Please label each part carefully.

submit cmps180-pk.f03 lab6 filename