Part A: Conceptual Data Model – Defining Entities and Relationships
1. Entities and Key Attributes
Product:
Attributes: ProductID(Primary Key), ProductName, Price, Category
Customer:
Attributes: CustomerID (Primary Key), FirstName, LastName, Email,
PhoneNumber
Order
Attributes: OrderID (Primary Key), OrderDate, TotalAMount,
CustomerID(Foreign Key)
Inventory:
Attributes: InventoryID (Primary Key), ProductID (Foreign Key), StockQuality
2. Relationships:
Customer to Order:
One customer can place multiple orders.
Relationship: One – to – Many (1)
Order to Product
Each order can include multiple products, and each product can appear in
multiple orders.
Relationships: Many – to – Many(M), usually implemented with a junction
table (orderDetails) containing attributes such as OrderID, ProductID,
Quality and Subtotal
Product to inventory
Each product has one inventory entry.
Relationship: one – to – One (1:1)
Part B: Tables and there attributes
Product Table
CREATE Product(ProductID INT PRIMARY
KEY,
ProductName VARCHAR(100),
Price DECIMAL (10, 2),
Category VARCHAR(50));
Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName(VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(20)
);
Order Table
CREATE TABLE Orders(
OrderID INT PRIMARY KEY,
OrderDate DATE,
TotalAomunt DECIMAL (10, 2)
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES
Customers());
Inventory Table
CREATE TABLE Inventory (
InventoryID INT PRIMARY KEY,
ProductID INT,
StockQuality INT,
FOREIGN KEY (ProductID) References
Products(ProductID
)
Part C: Entity-Relationship (ER) Diagram
The ER diagram will represent the relationships and entities defined above. Here’s summary of how
the diagram should look:
Product Table
CREATE Product(ProductID INT PRIMARY
KEY,
ProductName VARCHAR(100),
Price DECIMAL (10, 2),
Category VARCHAR(50));
Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName(VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(20)
);
Order Table
CREATE TABLE Orders(
OrderID INT PRIMARY KEY,
OrderDate DATE,
TotalAomunt DECIMAL (10, 2)
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES
Customers());
Inventory Table
CREATE TABLE Inventory (
InventoryID INT PRIMARY KEY,
ProductID INT,
StockQuality INT,
FOREIGN KEY (ProductID) References
Products(ProductID
)
Part C: Conceptual Vs. Physical Design
1. Conceptual Design:
Focuses on high-level relationships between entities.
The aim is to capture the overall structure of the database and its entities without
worrying about the technical details
In this scenario, the conceptual design highlights the relationships between
Customer, Order, Product and Inventory without specifying the data types or
indexes
2. Physical Design:
This phase deals with how the database will be implemented on a specific database
management system (DBMS)
It focuses on storage, indexing and performance optimization.
For example, in the physical design, you would decide hot to store data in tables,
implement indexes for faster querying and determine how to manage storage for
large datasets like product information.