Question

Database

Normalize the relations to BCNF:

Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode, phoneNumber, currentOfficeHeld)

Play- (title, author, numberOfActs, setChanges)

Sponsor- (sponID, name, street, city, state, zip, areaCode, phoneNumber)

Subscriber- (subID, firstName, lastName, street, city, state, zip, areaCode, phoneNumber)

Production- (prodyear, seasonStartDate, seasonEndDate, title)

Performance – (perfyear,perfdate, time, seasonStartDate)

TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID)

DuesPayment- (MemID, duesYear, amount, datePaid)

Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate)

Ticket- (saleID, seatLocation, price, type)

Member - Production-(MemID, prodYear, seasonStartDate, role, task)

Answer 1

Normalization: In DBMS, normalization is the process of arranging and adjusting data so that data does not contain insert,update and delete anomalies.

Given relational tables are:

Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode, phoneNumber, currentOfficeHeld)
Play- (title, author, numberOfActs, setChanges)
Sponsor- (sponID, name, street, city, state, zip, areaCode, phoneNumber)
Subscriber- (subID, firstName, lastName, street, city, state, zip, areaCode, phoneNumber)
Production- (prodyear, seasonStartDate, seasonEndDate, title)
Performance – (perfyear,perfdate, time, seasonStartDate)
TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID)
DuesPayment- (MemID, duesYear, amount, datePaid)
Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate)
Ticket- (saleID, seatLocation, price, type)
Member_Production-(MemID, prodYear, seasonStartDate, role, task)

First normal form:

• For the table to be in 1NF, table should contain single atomic attributes. Which means each field does not contain multiple values.
• As per given data, tables contains single attributes only. So that given table satisfying 1NF.

Second normal form:

• For the table to be in 2NF, that table must be in 1NF and it it has no partial dependency. Which means no non prime attribues are dependent on subset of candidate key or composite key.
• Composite key or Candidate key: Composite key is nothing but the combination of two or more keys in any table which can be used to identify the records uniquely.

Let us convert tables into 2NF:

Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode, phoneNumber, currentOfficeHeld)

• Candidate key: {MemID, ZIP}
• Non_prime attributes:{dateJoined, firstName, lastName, street, city, state, areaCode, phoneNumber, currentOfficeHeld}

Here, {dateJoined, firstName, lastName,phoneNumber, currentOfficeHeld} is only dependent of MemID not on ZIP. {MemID} is a subset of candidate key {MemID, ZIP}. As per 2NF each non_prime attribute should not dependent on subset of candidate key,they should depend on complete composite key.

Now let us devide the table as per 2NF:

Member(MemID,dateJoined, firstName, lastName,phoneNumber, currentOfficeHeld,Zip)
Address(Zip,street, city, state, areaCode)

Here Zip in Member table acts as foreign key.

Play- (title, author, numberOfActs, setChanges):

• Candidate key: {Title}
• Non_prime attributes: {author, numberOfActs, setChanges}

In Play table, non_prime attribute {author, numberOfActs, setChanges} dependent on {title} which is a complete candidate key.So that this table satosfies 2NF.

Sponsor- (sponID, name, street, city, state, zip, areaCode, phoneNumber):

• Candidate key: {SponID, ZIP}
• Non_prime attributes:{Name, treet, city, state,areaCode,phoneNumber}

Here, {Name, phoneNumber} is only dependent of SponID not on ZIP. {SponID} is a subset of candidate key {SponID, ZIP}. As per 2NF each non_prime attribute should not dependent on subset of candidate key,they should depend on complete composite key.

Sponsor(sponID,Name, phoneNumber,Zip)
Address(Zip,street, city, state, areaCode) Which is already exists. So we simply relate these tables.

Subscriber- (subID, firstName, lastName, street, city, state, zip, areaCode, phoneNumber):

• Candidate key: {subID, ZIP}
• Non_prime attributes:{firstName, lastName, street, city, state, areaCode, phoneNumber}

Here, {firstName, lastName, phoneNumber} is only dependent of SunID not on ZIP. {SubID} is a subset of candidate key {SubID, ZIP}. As per 2NF each non_prime attribute should not dependent on subset of candidate key,they should depend on complete composite key.

Now let us devide the table as per 2NF:

Subscriber(SubID,firstName, lastName,phoneNumber,Zip)
Address(Zip,street, city, state, areaCode)

Here Zip in Member table acts as foreign key.

Production- (prodyear, seasonStartDate, seasonEndDate, title):

• Candidate key: {ProdYear}
• Non_prime attributes: {seasonStartDate, seasonEndDate,title}

In production table, non_prime attribute{seasonStartDate, seasonEndDate,title} dependent on {ProdYear} which is a complete candidate key.So that this table satisfies 2NF.

Performance – (perfyear,perfdate, time, seasonStartDate):

• Candidate key: {PerfYear}
• Non_prime attributes: {perfdate, time, seasonStartDate}

In performance table, non_prime attribute{perfdate, time, seasonStartDate} dependent on {PerfYear} which is a complete candidate key.So that this table satisfies 2NF.

TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID):

• Candidate key: {SaleID}
• Non_prime attributes: {totalAmount, perfyear, perfdate,subID}

In TicketSale table, non_prime attribute{totalAmount, perfyear, perfdate,subID} dependent on {SaleID} which is a complete candidate key.So that this table satisfies 2NF.

DuesPayment- (MemID, duesYear, amount, datePaid):

• Candidate key: {duesYear}
• Non_prime attributes: {MemID amount, datePaid}

In DuesPayment table, non_prime attribute {MemID amount, datePaid} dependent on {DuesYear} which is a complete candidate key.So that this table satisfies 2NF.

Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate):

• Candidate key: {SponID}
• Non_prime attributes: {donationDate, donationType, donationValue, prodYear, seasonStartDate}

In Donation table, non_prime attribute {donationDate, donationType, donationValue, prodYear, seasonStartDate} dependent on {SponID} which is a complete candidate key.So that this table satisfies 2NF.

Ticket- (saleID, seatLocation, price, type):

• In Ticket table, non_prime attribute {seatLocation, price, type} dependent on {SaleID} which is a complete candidate key.So that this table satisfies 2NF.

Member_Production(MemID, prodYear, seasonStartDate, role, task):

• In Member_Production table, non_prime attribute {seasonStartDate, role, task} dependent on {MemID,prodYear, } which is a complete candidate key.So that this table satisfies 2NF.
• Here {MemID,prodYear, } works as both primary keys and foreign keys.

So after applying 2NF, relational models are as folows:

Member(MemID,dateJoined, firstName, lastName,phoneNumber, currentOfficeHeld,Zip)
Address(Zip,street, city, state, areaCode)

Play- (title, author, numberOfActs, setChanges):

Sponsor(sponID,Name, phoneNumber,Zip)

Subscriber(SubID,firstName, lastName,phoneNumber,Zip)

Production- (prodyear, seasonStartDate, seasonEndDate, title)

Performance – (perfyear,perfdate, time, seasonStartDate)

TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID)

DuesPayment- (MemID, duesYear, amount, datePaid)

Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate)

Ticket- (saleID, seatLocation, price, type)

Member_Production(MemID, prodYear, seasonStartDate, role, task):

Third Normal form:

• For the table to be in 3NF, that table must be in 2NF and there it doesn't have any transitive dependency.
• Transitive dependency : If A-->B and B-->C then A-->C. So in 3NF tables doesn't contain such relations.
• After applying 2NF, resulted tables does not contain transitive dependency.So that these tables are satisfying 3 NF.

BCNF:

• Boyce and Codd Normal Form is a advanced than 3NF. BCNF nothing but 3NF which does not have multiple overlapping candidate keys.
• So resulted tables, does not have multiple overlapping candidate keys.

Hence tables are in BCNF