0

Currently im trying to do an assignment question which is assigned to me which is asking me the normalization process. im aware of the 3 normal form. but im stuck with the 3rd normalisation form because i feel that im going to get the similar answer. if i did a mistake in my 1NF and 2NF please let me know. Can someone guide me with my 3NF form?

UNF:

Employee_Id, Employee_Gender, Employee_Position, Employee_Name, Employee_First, Employee_Last, Member_Id, Member_ Name, Member_ First, Member_ Last, Member_ Feedback, Location_Branch_Id, Dvd_Branch_Stock, Branch_Location, Location_Area, DVD_Id, DVD_Title, DVD_Quantity, DVD_Genres, DVD_Released_Date, Rental_ID, Rental_Borrowed_Date, Rental_Due_Date, Rental_Overdue, Rental_Dvd_Title,Rental_Status

1NF:

Employee_Id Employee_Gender Employee_Position

Employee_Id Employee_Name Employee_First Employee_Last

Member_Id Member_ Feedback

Member_Id Member_ Name Member_ First Member_ Last

Location_Branch_Id Location_Area

Location_Branch_Id Dvd_Branch_Stock Branch_Location

DVD_Id, DVD_Quantity

DVD_Id DVD_Title DVD_Genres DVD_Released_Date

Rental_ID Rental_Dvd_Title

Rental_ID Rental_Borrowed_Date Rental_Due_Date Rental_Overdue Rental_Status

2NF:

Employee_Id Employee_Gender Employee_Position

Employee_Id Employee_Name

Employee_Name Employee_First Employee_Last

Member_Id Member_ Feedback

Member_Id Member_ Name

Member_ Name Member_ First Member_ Last

Location_Branch_Id Location_Area

Location_Branch_Id Dvd_Branch_Stock

Dvd_Branch_Stock Branch_Location

DVD_Id, DVD_Quantity

DVD_Id DVD_Title

DVD_Title DVD_Genres DVD_Released_Date

Rental_ID Rental_Dvd_Title

Rental_ID Rental_Borrowed_Date

Rental_Borrowed_Date Rental_Due_Date Rental_Overdue Rental_Status

ganeshwaran
  • 1
  • 2
  • Normalization (up to 3NF) is a transformation that depends on and preserves functional dependencies. Without knowledge of the FDs in your schema, any answer you'll get will be opinion-based. Please read up on functional dependencies and include them in your question. – reaanb Jul 11 '18 at 05:23

1 Answers1

0

I see an issue with the current form of Employee and Member tables.

Those tables still have duplicate data as the field Employee_Last and Employee_First and Employee_Name will likely contain the same data as Member_Last, Member_First and Member_Name 'names which are dependent on the person and not on being a member or employee.

What I can see directly is that you should create a Table Person with ID, Name, First, Last, Gender

PERSON(ID,Name,First,Last,Gender)
EMPLOYEE(ID,Position,Person_ID)
MEMBER(ID,Feedback,Person_ID)

// If a regular member should not give you the gender, you should save the gender in 
the employee table as it is not necessary to know for a regular Person.

Otherwise, this has to into the Person Table. -->Normally the gender is Dependant on the Person and not if the person is a member or employee.

For the other tables, I don't know the direct "dependencies" but you could check if the data is in a "useful" state for the solution. Check the other tables so you have the relations right and don't save data as a duplicate. 'Location <-1:N-> Branch <-1:N-> DVD<-1:N->Rental.

Other sources regarding the 3rd normalization form. https://www.1keydata.com/database-normalization/third-normal-form-3nf.php

Hope this helps,

Georg

Georg.Duees
  • 154
  • 6