Database Project: AMC - Database Design and Implementation

This assignment presents a database project solution for the Australian Manufacturing Company (AMC). The project focuses on designing and implementing a database system to manage the company's operations. The solution includes the creation of an Entity-Relationship Diagram (ERD) to visualize the database structure, identification of entities, and their attributes. The solution also addresses the creation of a relational schema, defining tables and their respective attributes, including primary and foreign keys. Additionally, the assignment covers the use of SQL to query the database. The project aims to provide practical experience in database modeling, normalization, and writing SQL statements to query a relational database. The solution also highlights the use of external resources such as YouTube tutorials to address challenges encountered during the project. The solution also includes a list of references.

Database
Development

Introduction
 The database has been designed for the AMC
organization.
 The database has been designed on XAMPP
 Seven entities have been identified here and the
associated attribute have also been identified.

ER Diagram
Products
Order
Stores ProductsOrdered
Patrons
Department
Employees
ProductNumberPK
ProductTitle
UnitPrice
OrderNumberPK
OrderDate
NumberOfProducts
StoreIDPK
Name
Address
IDPK
ProductNumberFK
OrderNumberFK
CustomerIDPK
LastName
FirstName
DeptIDPK
DeptName
EmployeeIDPK
LastName
FirstName
PhoneNumber
Address
QuantityOrdered
StoreIDFK
DeptIDFK
FaxNumber
CustomerIDFK
EmployeeIDFK
Products
Order
Stores ProductsOrdered
Patrons
Department
Employees
ProductNumberPK
ProductTitle
UnitPrice
OrderNumberPK
OrderDate
NumberOfProducts
StoreIDPK
Name
Address
IDPK
ProductNumberFK
OrderNumberFK
CustomerIDPK
LastName
FirstName
DeptIDPK
DeptName
EmployeeIDPK
LastName
FirstName
PhoneNumber
Address
QuantityOrdered
StoreIDFK
DeptIDFK
FaxNumber
CustomerIDFK
EmployeeIDFK

Relational
Schema
 Department (DeptID (pk), DeptName)
 Products (ProductNumber (pk), ProductTitle, UnitPrice)
 Stores (StoreID (pk), Name, Address, FaxNumber)
 Employees (EmployeeID (pk), LastName, FirstName
StoreID (fk), DeptID (fk))
 Patrons (CustomerID (pk), LastName, FirstName,
PhoneNumber, Address)
 Orders (OrderNumber (pk), OrderDate,
NumberOfProducts, CustomerID (fk), EmployeeID (fk))
 ProductsOrdered (ID (pk), ProductNumber (fk),
OrderNumber (fk), QunatityOrdered)

Relationship
Design

Conclusion
 There has been a lot of problems encountered in the
system
 YouTube tutorials were referred.
 The references for the tables were performed.
 The solution was obtained.

Bibliograph
y
 Gherlone, M., Marangon, A. and Patuelli, C., 2019. Full
crash simulations material database development
including failure criteria for coarse shell elements.
 Guth, D., Barlow, J., Ponchillia, P., Rodegerdts, L., Kim,
D.S. and Lee, K., 2018. 17-01 An Intersection
Database Enhances Blind Pedestrians' Access to
Complex Signalized Intersections: Stage 2 Analysis &
Database Development.