Question

Create an RDM that has entities, Customers, Products, Orders, Payments, and OrderDetails using MS ACCESS. For each entity use the appropriate attributes and explain the relationship being used. (Add as much attributes that reflect on the real world) RDM = relational database model

Answer 1

CREATE TABLE IF NOT EXISTS Customer (

ID INT NOT NULL ,

Name TEXT NOT NULL ,

PhoneNo VARCHAR(45) NULL ,

PRIMARY KEY (ID) );

CREATE TABLE IF NOT EXISTS Order (

ID INT NOT NULL ,

customer_id INT NULL ,

PRIMARY KEY (ID) ,

INDEX fk_Order_1_idx (customer_id) ,

CONSTRAINT fk_Order_1

FOREIGN KEY (customer_id)

REFERENCES Customer (ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS Product (

I` INT NOT NULL ,

Name VARCHAR(45) NOT NULL ,

Description TEXT NULL ,

PRIMARY KEY (ID) );

CREATE TABLE IF NOT EXISTS OrderDetail (

ID INT NOT NULL ,

Order_ID INT NOT NULL ,

Product_ID INT NOT NULL ,

Quantity INT NOT NULL ,

PRIMARY KEY (ID) ,

INDEX fk_OrderDetail_1_idx (Order_ID) ,

INDEX fk_OrderDetail_2_idx (Product_ID) ,

CONSTRAINT fk_OrderDetail_1

FOREIGN KEY (Order_ID )

REFERENCES Order (ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT fk_OrderDetail_2

FOREIGN KEY (Product_ID)

REFERENCES Product (ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION);