Question

This is all one question:

A theater uses the following table/sheet to manage ticket sales, which turned out to be a very bad practice. The manager of theater hires you to design a database to manage the ticket sale information.   

TICKET-SALES (InvoiceNumber, CustomerID, ShowTitle, SeatType, SeatLocation, TicketPrice, CustomerName, CustomerCell, ShowTime, Director_of_Show)

Note: A customer can purchase multiple seats in one order (with one InvoiceNumber). It is also the common sense that the price of a ticket/seat depends on the show, it’s showtime and seat location. Also note this theater may have multiple shows at the same time.  

To do your job, you need to answer the following questions:  

-List al Functional Dependencies

-List Multivalued Dependencies, if there is any.

-What is the key of original table (TICKET-SALES)?

-What normal form this table is in and Why? Give a clear justification/explanation

-How do you normalize it? Show the result of normalization

Answer 1

TICKET-SALES (InvoiceNumber, CustomerID, ShowTitle, SeatType, SeatLocation, TicketPrice, CustomerName, CustomerCell, ShowTime, Director_of_Show)

Functional Dependencies :

CustomerID -> CustomerName, CustomerCell ---------------- partial dependency

ShowTitle, ShowTime, SeatType, SeatLocation -> TicketPrice -------- transitive dependency

ShowTitle -> Director_of_Show ------- transitive dependency

Multivalued Dependencies:

InvoiceNumber , CustomerID ->-> SeatType

key of original table

Composite key ----------- InvoiceNumber , CustomerID

Normal form this table is in :

The given table is in 1NF because there exists partial dependency.

The result of normalization

Customer(CustomerID , CustomerName, CustomerCell)

Show(ShowTitle, ShowTime, SeatType, SeatLocation , TicketPrice)

Show(ShowTitle , Director_of_Show)

Seat(InvoiceNumber , CustomerID , SeatType)

bold attributes are primary keys and italicized are foreign keys.

Do ask if any doubt.