Search code examples
mysqlsqldatabase-normalization

Normalisation to 3nf and functional dependencies two

I need to identify functional dependenciers and normalise the following tables to 3NF. I'm still unclear of all dependencies.

If I have dependencies do I push them into other tables?

Video store

Customer table

customerNo | customername | address | phone | numberofbooking
  • Customer Number – The unique number related to a customer. 
  • Customer Name – The name of the customer.  
  • Address – The customers address. 
  • Phone – Phone number of the customer. 
  • Number Of Rentals – Current number of rentals customer has.  

customerno is my primary key, everything except numberofbooking is dependent on customerno?

video table

SerielNo | CopyNumber | title | yearmade | director | origen
  • SerialCode – Unique serial code at the back of a DVD.  
  • CopyNumber - the copy number of that video. For instance a video could havmultiple copies in store, this is used to distinguish between each copy. 
  • Title – The name of the movie. 
  • Year Made – The year hte movie was published.  
  • Director – The director of the movie. 
  • Origin – The country in which the movie was made.  

As serielno is not unique I was or copy number I was goint to add a videoId unique to each video as a serial number can have many copies.

rentals table

title | customerName
  • title – The video name that has been rented. 
  • customername – The name of the customer that has rented the movie.  

Not sure what to do here.

Solution

    • Remove the NumberOfBooking field from the customer table. This is redundant, and can easily be calculated from the rentals table.

    • Consider splitting the video table into two tables, where one contains the information of a unique video, and the other contains information about the copies of each video.

    • Consider adding an id to identify the video copies, this is easier to use than a combination of serial number and video copy number.

    • Consider adding a director table, as some directors will have made several videos.

    • Consider adding a country table, as many videos will come from the same country.

    • Use the customer number in the rentals table, not the customer name. The customer name might not be unique.

    • Use the identity of the video copy in the rentals table, not the video title.