3NF Database Normalization

Question

There is a report table for overdue DVD rentals. The store has multiple copies of the same DVD (they are all numbered to be identified). How can I normalize this data to meet 3NF requirements?

Answer 1

Look at objects--actors:

1. Customer
2. Title
3. Physical Medium (DVD, the thing you take home when borrowing)
4. Artist
5. Rental (act of renting = transaction)

• One artist can author many titles (moves, albums); a title (album) can be performed by several artists.
• One title can have many physical-media (dvd, tape, blue ray) copies; a medium has one title only.
• One medium (DVD) can be rented by many customers (one at the time); a customer can rent many media (DVD). Media.Status tracks availability of a medium (DVD).

Answer 2

The data model:

VIDEO_ARTIST table

• ARTIST_ID, pk
• FIRST_NAME
• LAST_NAME

VIDEOS table

• VIDEO_ID, pk
• VIDEO_TITLE
• ARTIST_ID, fk
• RUNNING_TIME

VIDEO_COPIES table

• VIDEO_COPY_ID, pk
• VIDEO_ID, fk
• VIDEO_COPY_NUMBER

Notice that I'm not using the primary key for the value displayed to the users.

1. Copy numbers could change, but you don't want to break referencial integrity
2. Do not expose table keys

VIDEO_RENTALS table

• VIDEO_COPY_ID, pk, fk
• ACCOUNT_ID, pk, fk
• DUE_DATE, pk

VIDEO_RENTALS_ACCOUNTS table

• ACCOUNT_ID, pk
• ACCOUNT_NUMBER, unique
• FIRST_NAME
• LAST_NAME

Same logic regarding the ACCOUNT_NUMBER as with the VIDEO_COPY_NUMBER...

Here's the SQL to use based on the data model to get the report example you provided:

SELECT v.video_title 'Video Title',
       aa.artist_name 'Artist',
       vc.video_copy_number 'Copy Number',
       v.running_time 'Length',
       vr.due_date 'Date Due',
       acct.borrower_name 'Borrower',
       acct.account_number 'Card Number'
  FROM VIDEO_RENTALS vr
  JOIN VIDEO_COPIES vc ON vc.video_copy_id = t.video_copy_id
  JOIN VIDEOS v ON v.video_id = vr.video_id
  JOIN (SELECT a.artist_id,
               a.firstname +' '+ a.lastname AS artist_name
          FROM ARTIST a) aa ON aa.artist_id = vr.artist_id
  JOIN (SELECT vra.account_id,
               vra.account_number,
               vra.firstname +' '+ vra.lastname AS borrower_name
          FROM VIDEO_RENTALS_ACCOUNTS vra) acct ON acct.account_id = vr.account_id