Database Design Assignment: NGA Database Design and Implementation

This assignment focuses on the design and implementation of a database for Next Generation Auto (NGA). It begins with an Entity Relationship Diagram (ERD) illustrating the relationships between entities such as AssemblyDivision, Order, Part, and Supplier. The assignment then details the creation of relational tables up to the Third Normal Form, including data types and primary/foreign keys. A dependency diagram is also provided. The practical aspect involves creating a database in MS Access, followed by the creation of forms for data input. The assignment includes data input examples for each table and demonstrates the creation of various SQL queries: a select query, a join query, and a numeric query (count). Finally, a report based on a join query is presented. The assignment concludes with a bibliography of relevant sources. This assignment provides a comprehensive overview of database design principles and practical application.

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)