mangment information system ( ISMG)

ISMG3500 Homework 4, Due Wednesday, 9/23, 3:30 pm. 10 Points. Graded on a scale of 30-points then scaled down. Submit a hard copy in class and an ecopy to Canvas.

(Tentative. May need to be modified.)

Problem 1 (20 Points)

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 to describe 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 (10 Points)

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

1