All the specifications and questions are in the document attached
Mar 15, 2023 COP 4710 Due: Wed, Mar 29 before 11:50pm Assignment-4 1. Design a relational database for the following Community E-R diagram and write an SQL schema similar to setupCompany.sql (xampp\htdocs\course\scripts\company\) with all constraints (and no data values)[community.sql]. [25] Person with_father 1 N with_mother with_spouse N M N 1 Sex = F Sex = M Personal relationships MStatus BCity Person PersonID Sex BirthInfo BCountry y BDate e EmailID Address Phone Webpage Education Person attributes MName Name LName FName known_as 1 N Remark DPlace DYear MsgFile Citizen CStatus CCountry is_a 1 N Comment s 2. Consider the universal table D = {P, Q, R, S, T, U, V, W, X, Y, Z} with the following set of functional dependencies: {P, R} → {Q, T, U} {T} → {S} {V, W} → {X, Z} {X} → {Y} a) What is the primary key for D? [5] b) Decompose D into 2NF tables. [10] c) Decompose D into 3NF tables. [10] 3. Consider the following table: CAR_REG(State, TagID, OwnerSSN, TaxRate, RegFee, Address) Assume that an owner may own several cars, and hence {State, TagID} is the primary key. Additional functional dependencies are {State} → {TaxRate} {OwnerSSN} → {Address} a) Based on the primary key, is this table in 1NF, 2NF, or 3NF? Why? [10] b) How would you successively normalize it completely? [15] 4. Consider the following table for published books: BOOK(BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher) AuthorAffil refers to the affiliation of author. Suppose the following functional dependencies exist: {BookTitle} → {Publisher, BookType} {BookType} → {ListPrice} {AuthorName} → {AuthorAffil} a) What is the primary of the table? What is the normal form of this table? Explain your answer. [10] b) Apply normalization until you cannot decompose the tables further. State the reasons behind each decomposition. [15] Submission: Save the Q1 answer in community.sql and the answer for all other questions in a word file and save the Word file as a pdf file. Upload the SQL file and the pdf file through Canvas along with the following originality declaration. ======================================================== Name: _________________________ Panther-ID: x x x -________ (Last 4 digits) Course: COP 4710 Assignment#: 4 Due: Wed, Mar 29, 2023 I hereby certify that this work is my own and none of it is the work of any other person. Signature: ______________________ ========================================================= Notation for the ER diagram