Database Design for Next Generation Auto

This assignment delves into database design for a hypothetical 'Next Generation Auto' company. It presents an Entity Relationship Diagram (ERD) outlining key entities and their relationships. Relational tables are designed up to the third normal form, ensuring data integrity. The assignment includes a dependency diagram illustrating data dependencies. Database creation in MS Access, query examples (select, join, count), and a report based on a join query are also presented. Finally, a bibliography of relevant database design resources is provided.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University

1DATABASE DESIGN
Table of Contents
1. Entity Relationship Diagram:......................................................................................................2
2. Relational Tables up to Third Normal Form:..............................................................................2
2.1 Dependency Diagram:...........................................................................................................5
3. Database Creation:.......................................................................................................................5
4. Form Creation for Divisions:.......................................................................................................6
5. Data Input into Tables:................................................................................................................6
6. Query Creation:...........................................................................................................................7
7. Report:.........................................................................................................................................8
Bibliography:...................................................................................................................................9

2DATABASE DESIGN
1. Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram of Next Generation Auto
(Source: Created by Author)
2. Relational Tables up to Third Normal Form:
AssemblyDivision
Entity Data Type Key
DivID Varchar2 Primary
DivName Varchar2 None

3DATABASE DESIGN
ContactPersonName Varchar2 None
ContactPersonPhone INT None
Order
Entity Data Type Key
OrderID Varchar2 Primary
DateofOrder Date None
Part
Entity Data Type Key
PartNo Varchar2 Primary
PartName Varchar2 None
PartDescription Varchar2 None
InStock INT None
Order_Part
Entity Data Type Key
OrderPartID Varchar2 Primary
OrderID Varchar2 Foreign Key (Order Table)
PartNo Varchar2 Foreign Key (Part Table)
Quantity INT None

4DATABASE DESIGN
Supplier
Entity Data Type Key
SupplierID Varchar2 Primary
SupplierName Varchar2 None
SupplierNumber INT None
SupplierEmail Varchar2 None
SupplierPostalAddress Varchar2 None
Supplier_Part
Entity Data Type Key
SupplierPartID Varchar2 Primary
SupplierID Varchar2 Foreign Key (Supplier Table)
PartNo Varchar2 Foreign Key (Part Table)

5DATABASE DESIGN
2.1 Dependency Diagram:
Figure 2: The Dependency Diagram of Database
(Source: Created by Author)
3. Database Creation:
A database called NGA has been created in the MS Access application. The MS Access
file has been provided below.

6DATABASE DESIGN
4. Form Creation for Divisions:
Figure 3: The Divisions Form
(Source: Created by Author)
5. Data Input into Tables:
Figure 4: The AssemblyDivision Table
(Source: Created by Author)

7DATABASE DESIGN
Figure 5: The Order Table
(Source: Created by Author)
Figure 6: The OrderPart Table
(Source: Created by Author)
Figure 7: The Part Table
(Source: Created by Author)
6. Query Creation:
Select Query: SELECT Part.Part_Name, Supplier.Supplier_Name FROM Supplier
INNER JOIN (Part INNER JOIN SupplierPart ON Part.Part_no = SupplierPart.Part_no) ON
Supplier.Supplier_ID = SupplierPart.Supplier_ID;

8DATABASE DESIGN
Join Query: SELECT OrderPart.Part_no, Order.Date_of_Order, Order.Div_ID,
OrderPart.Quantity FROM [Order] INNER JOIN OrderPart ON Order.Order_ID =
OrderPart.Order_ID;
Numeric Query (Count): SELECT Supplier.Supplier_Name,
Count(SupplierPart.Part_no) AS 'Total Part Supply' FROM Supplier INNER JOIN SupplierPart
ON Supplier.Supplier_ID = SupplierPart.Supplier_ID GROUP BY Supplier.Supplier_Name;
7. Report:
Figure 8: Report of Join Query
(Source: Created by Author)