DATABASE Assesment 3 To Sarwar Tapan
DATABASE Assesment 3 To Sarwar Tapan
DATABASE Assesment 3 To Sarwar Tapan
Modelling Normalisation
Assessment Item 3
ITC 423
Modelling Normalisation
Modelling Normalisation
Task:
Question1.
Part A:
Normalizing the table to 3NF with dependency diagrams/Notations:
Using the data in a given table normalizing the table to 3NF with dependency
diagrams/Notations:
1. Normalisation Form (1NF):
{Book_Id, Borrower_Number} => {Book_Title, Book_Author, Date_Published, Publisher,
Borrowed_Date, Returned_Date}
{Book_Id} => {Book_Author, Book_Title, Date_Published, Publisher}
ITC 423
Modelling Normalisation
Question2.
Dependency diagram:
Publisher
Publisher_Location
Publisher_Name
Publisher_Details
ITC 423
Modelling Normalisation
Question 3:
ER-Diagram:
To make an Entity Relationship Diagram(ERD) for the above related Normalisation form:
ITC 423
Modelling Normalisation
Step 1:
Book and PUBLISHER are the first step of entities.
Book:
PRIMARY KEY: Book_Id
FOREIGN KEY: publisher.
The attributes are Book_Author, Book_Title, Date_Published, Publisher.
Publisher:
PRIMARY KEY: Publisher
Attributes are Publisher_Location, Publisher_Name, Publisher_Details.
The Entity diagram describes the relation of Book and PUBLISHER with 1: M relationship.
Step 2:
Borrower:
PRIMARY KEY: Borrower_Number
The attributes are Borrower_Name, Borrowed_Date, Borrowed_Time, Borrower_Location.
The Entity diagram shows the relation of Borrower and Borrowed_Interlink with 1: 0, Many
relationship.
Step 3:
Borrowed_Interlink
PRIMARY KEY : Book_Id
FOREIGN KEY: Borrower_Number
The attributes are Book_Id, Borrowed_Date, Returned_Date, Publisher_Name,
Publisher_Location, Borrower_Number.
ITC 423
Modelling Normalisation
The Entity diagram respectively describes the relation of Borrowed_Interlink with Book and
Borrower_Number with Many : 1 relationship
Part B:
Question 1:
ITC 423
Question2:
ERDDiagram:
Modelling Normalisation
ITC 423
Modelling Normalisation
PartC:
RDMformaintenancebook:
Maintenance book (idMaintanance book, code_services, date_services, time_services,
code_damage, type_services)
PRIMARY KEY (idMaintanance book)
FOREIGN KEY id Damage log book references Damage log book
RDM for Damage log book:
Damage log book (idDamage log book, driver_id, damage_date, damage_type, damage_time)
PRIMARY KEY ( idDamage book)
FOREIGN KEY idDamage references Damage.
RDM for Damage:
Damage (id Damage, minor_damage, major_damage, damage_type)
PRIMARY KEY (id Damage).
RDM for Service Book:
Service book (idService book, bus_id, minor_service, major_service, service_date)
PRIMARY KEY ( idService book)
FOREIGN KEY id Maintenance book references Maintenance book.
RDM for Relief Driver:
Relief Driver (id Relief Driver,shift_id, replacement_id)
PRIMARY KEY ( id Relief Driver)
FOREIGN KEY id Coordinator reference Coordinator.
ITC 423
Modelling Normalisation