Mar 15, XXXXXXXXXXCOP 471 XXXXXXXXXXDue: Wed, Mar 29
XXXXXXXXXXbefore 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]
) 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]
) 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]
) 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.
XXXXXXXXXXSignature: ______________________
=========================================================
Notation for the ER diagram