Data Modeling - Normalisation UNF - 3NF

sausagekayak

Hi guys,

Im hoping a database guru out there will be able to answer this for me!

Im trying to help my wife with her college database homework. She was given a visual graphic of a "game rental overdue invoice" and asked to normalise it to 3NF.

Im having trouble with it, would anyone be able to suggest what 1NF and or 2NF should be.
The store_code is throwing me off as I dont see why is should be a primary key.

Here it is in UNF:

Overdue_Record =

Store_Code +

---

Store_Address +
Customer_Name +
Customer_Address +
{
Title_ID +
Title_Name +
Platform +
Date_Rented +
Amount_Owed +
}
Total_Outstanding

Total outstanding is an addition of the repeated Amount_owed attribute.

Thanks

Tom Dunne

Nobody is going to straight out give you the answer.

Post up your attempts and we can offer some guidance.

sausagekayak

Tom Dunne wrote: »

Nobody is going to straight out give you the answer.

Post up your attempts and we can offer some guidance.

Sorry Tom, Im not looking for someone to give me the answer, sure how would I learn that way, probably worded the question wrong. Here is my attempt at her assignment:

Here it is in UNF:
Overdue_Record =
Store_Code + (PK)
Store_Address +
Customer_Name +
Customer_Address +
{
Title_ID +
Title_Name +
Platform +
Date_Rented +
Amount_Owed +
}
Total_Outstanding

---

First Normal Form (1NF)

---

As per 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. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.
The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key.

#STORE_TABLE#

STORE_CODE (PK)
CUSTOMER_NAME (PK)

---

STORE_ADDRESS +
CUSTOMER_ADDRESS +



#TITLES_TABLE#

TITLE_ID (PK)
STORE_ID (PK)
CUSTOMER_NAME (PK)

---

TITLE_NAME +
PLATFORM +
DATE_RENTED +
AMOUNT_OWED +

---

Second Normal Form (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. If any column depends only on one part of the concatenated key,
then the table fails Second normal form.



#STORE_TABLE#

STORE_CODE (PK)

---

STORE_ADDRESS +


#CUSTOMER_TABLE TABLE#

CUSTOMER_NAME (PK)

---

CUSTOMER_ADDRESS +


#TITLES_TABLE#

TITLE_ID (PK)

---

TITLE_NAME +
PLATFORM +


#TITLES_SALE_TABLE#

TITLE_ID (PK)
STORE_ID (PK)
CUSTOMER_NAME (PK)

---

DATE_RENTED +
AMOUNT_OWED +

---

Second Normal Form (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.




#STORE_TABLE#

STORE_CODE (PK)

---

STORE_ADDRESS +


#CUSTOMER_TABLE TABLE#

CUSTOMER_ID (PK)

---

CUSTOMER_NAME +
CUSTOMER_ADDRESS +


#TITLES_TABLE#

TITLE_ID (PK)

---

TITLE_NAME +
PLATFORM +


#TITLES_SALE_TABLE#

TITLE_ID (PK)
STORE_ID (PK)
CUSTOMER_ID (PK)

---

DATE_RENTED +

Tom Dunne

Sorry if I came across as a bit snarly, that was not the intention.

I can't help but notice in your 3NF, you are missing something. Can you spot it?

Put it another way, before you go talking about 3NF, let's get back to basics. How do you calculate the overdue amount? What needs to be in place before you can calculate that? And what are the attributes of an overdue invoice?

dunnacoirbe50

bit late to the party here but currently doing something similar.

Would you not need to include a 'Record ID' in one of your tables?

and i understand that the return date is missing from the 3NF section.