Problem 1: For each table below, perform the following 3 steps. (Perform all three steps on Table A first. Then perform all 3 steps on Table B).
Step 1: Determine if the table satisfies 2NF. If the table is already in 2NF, say so. If a table violates 2NF, say so and produce the 2NF tables. It is sufficient to just list the results of 2NF normalization.
Step 2: Determine if all resulting tables from Step 1 satisfy BCNF. For each of the resulting tables from Step 1, if it is already in BCNF, say so.If it violates BCNF, say so and produce the BCNF tables. It is sufficient to just list the results of BCNF normalization. There is NO need todescribe the normalization process verbally.
Step 3: Check and make sure every table you wrote in Steps 1 and 2 has a primary key specified. If not, specify the primary key for each table missing the primary key.
Points will be deducted if a table you write does not have a PK specified.
A. Sale: (Date, Customer#, Product#, Vendor#, Vendor-City, SalesRepName)
Additional FD: Customer#→ SalesRepName; Vendor#→Vendor-City
B. BookInfo: (BookTitle, Print#, DateofPrint, #copiesPrinted, #copiesSold, Price, MajorSponsorName, MajorSponsorPhone )
Additional FD: MajorSponsorName →MajorSponsorPhone
Problem 2: Determine if each of the following tables satisfy BCNF. If the table is already in BCNF, say so. If the table violates BCNF, say so and produce the BCNF tables. It is sufficient to just list the results of BCNF. There is NO Need to provide the normalization process. Do not go normalize it to 2NF first. Normalize each table to BCNF directly. The primary key and additional functional dependencies are already given. Do not assume further function dependencies. Be sure to identify the primary key for each table you write
Fundraising: (Event#,SubEvent, Date, OrganizerName, OrganizerPhone, Beneficiary)
Additional FDs: Event# → Date, OrganizerName
OrganizerName → OrganizerPhone
FileDownload: (SessionId,DownLoadId, FileName, SessionStDateTime, SessionEndDateTime, FileSize, FileType)
Additional FDs: SessionId → SessionStDateTime, SessionEndDateTime
FileName → FileSize, FileType