Answer To: Unit ISY1002/ISY103 Database Management for Business Assessment Type Group Assignment – Database...
Neha answered on Sep 30 2021
66073 - AMC DB/~$Report.docx
66073 - AMC DB/ER Diagram.png
66073 - AMC DB/Presentation.pptx
AMC Database
Submitted By:-
Entity Relationship Diagram
Normalization
The normalization can be achieved by the tables in the database. The tables need to be organised in a proper format. There are few rules of each form of normalization which should be achieved by the tables. Once the tables are normalised then it becomes easier to insert, update and delete the tables without having any anomalies. The normalization also helps to achieve data integrity and redundancy.
First normal form – Each column can have atomic value and unique names for them. This condition is satisfied by tables.
Second normal form – To achieve the second normal form we need to remove the partial dependencies from the columns. The partial dependency can be achieved by removing the non-attribute key which has dependency on the candidate key or its part. There is no column in the table which has partial dependency on the candidate key.
Third Normal Form – In this stage we have to remove transitive dependency from the tables to remove redundancy and data integrity.
Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Suburb, State, Postcode)
Employee (EmployeeId, EmpName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
Supplier (SupplierId, Name, Address,Phone,Email)
Department (DepartmentId, Name, ShopId, Email, Phone)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay)
What I learnt
This assignment helped to learn about different concepts of the normalization and its importance for the database. As per the given case study, there will be requirement of 9 tables which will be related with each other. I created primary key for every table, and it was referenced by other tables as foreign key. The normalization was achieved why performing multiple forms of normalization so that we can eliminate the data integrity and redundancy from the database. All the tables satisfy the 3rd normal form conditions of normalization
Thank You
.MsftOfcThm_Accent1_Fill {
fill:#B71E42;
}
.MsftOfcThm_Accent1_Stroke {
stroke:#B71E42;
}
66073 - AMC DB/Report.docx
Part A: ERD for AMC
Part B: Normalisation
The normalization can be achieved by the tables in the database. The tables need to be organised in a proper format. There are few rules of each form of normalization which should be achieved by the tables. Once the tables are normalised then it becomes easier to insert, update and delete the tables without having any anomalies. The normalization also helps to achieve data integrity and redundancy.
First normal form – Each column can have atomic value and unique names for them. This condition is satisfied by tables.
Second normal form – To achieve the second normal form we need to remove the partial dependencies from the columns. The partial dependency can be achieved by removing the non-attribute key which has dependency on the candidate key or its part. There is no column in the table which has partial dependency on the candidate key.
Third Normal Form – In this stage we have to remove transitive dependency from the tables to remove redundancy and data integrity.
Part C: Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Suburb, State, Postcode)
Employee (EmployeeId, EmpName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
Supplier (SupplierId, Name, Address,Phone,Email)
Department (DepartmentId, Name, ShopId, Email, Phone)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay)
Table Patron
Column Name
Data Type
Length
Key Constraints
PatronId
varchar
5
Primary
firstname
varchar
50
lastName
varchar
50
Phone
varchar
20
Address
varchar
100
Table Shop
Column Name
Data Type
Length
Key Constraints
ShopId
varchar
5
Primary
Name
varchar
50
PhoneNo
Varchar
50
Email
Varchar
50
Fax
varchar
50
Street
Varchar
30
Suburb
Varchar
30
State
Varchar
30
Postcode
int
Table Supplier
Column Name
Data Type
Length
Key Constraints
SupplierId
varchar
5
Primary
Name
varchar
50
Address
varchar
100
Phone
varchar
20
Table Product
Column Name
Data Type
Length
Key...