11. The MovieTicket relation keeps data about the movie tickets bought by custom

Question

11. The MovieTicket relation keeps data about the movie tickets bought by customers. MovieTicket MovielD MovieTitle ShowlD ShowDay ShowTime CustomerID CustomerName TicketNo BuyDate 1/1/2013 12 PM 1/1/201312 PM 1/1/2013| 12 PM 1/1/2013| 12 PM 1/2/20132 PM 1/2/20132 PM 1/3/2013 6 PM 1/3/2013 6 PM 12/31/2012 12/31/2012 1/1/2013 1/1/2013 1/1/2013 1/2/2013 1/2/2013 1/3/2013 a. Identify the functional dependencies in the relation. Is this relation in INF, 2NF, 3NF, and BCNF, and why? If not, show your steps of performing the normalization to transform the relation into 1NF, 2NF, 3NF, and BCNF. Show all referential integrity constraints in the relational model that results from the normalization. b.

Explanation / Answer

Functional dependencies:

MovieID,ShowID,TicketNo -> MovieTitle,ShowDay,ShowTime,CustomerName,BuyDate

MovieID -> MovieTitle

ShowID -> ShowDay,ShowTime

CustomerID -> CustomerName

CustomerID , TicketNum -> BuyDate

Normal forms

1 NF: Yes this relation is not in 1NF as by definition,

In First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row.

Here data is in 1 NF

2NF: No data is not in 2NF

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence.

Which is not true as per the above releations, other attributes can be uniquely identified by parts of primary key.

Hence, to get it in 2 NF, create below tables

Movie(MovieID, MovieTitle)

Shows(ShowID,ShowDay,ShowTime)

Customer(CustomerID,CustomerName)

Purchase(CustomerID,TicketNo,BuyDate)

MoviewDetails(MovieID,ShowID,TicketNo,CustomerID)

3NF: After division into separate tables they are in 3NF

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form

BCNF:

A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

Here,

All relations are in BCNF

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.