$--$ Name: Amon Ahoure

$--$ Assignment: M$02$ Hands$-$On Lab $-$ Constructing the Database

$--$ Date: $1/29/2024$

CREATE TABLE CUSTOMERS $($

CUSTOMER$\_$ID NUMBER$(6)$ NOT NULL,

FIRST$\_$NAME VARCHAR$2(25)$ NOT NULL,

LAST$\_$NAME VARCHAR$2(25)$ NOT NULL,

STREET$\_$ADDRESS VARCHAR$2(50)$ NOT NULL,

CITY VARCHAR$2(50)$ NOT NULL,

ZIP$\_$CODE VARCHAR$2(10)$ NOT NULL,

COUNTRY CHAR$(50)$ NOT NULL,

PHONE$\_$NUMBER VARCHAR$2(25)$ NOT NULL,

EMAIL VARCHAR$2(50)$ NOT NULL,

DATE$\_$OF$\_$BIRTH DATE,

GENDER VARCHAR$2(1)$

$)$;

CREATE TABLE BOOKS $($

BOOK$\_$ID NUMBER$(6)$ NOT NULL,

BOOK$\_$TITLE VARCHAR$2(250)$ NOT NULL,

BOOK$\_$DESCRIPTION VARCHAR$2(2000)$ NOT NULL,

BOOK$\_$CATEGORY$\_$CD CHAR$(2)$ NOT NULL,

BOOK$\_$PRICE NUMBER$(8,2)$ NOT NULL,

BOOK$\_$REVIEWS NUMBER$(5)$ NOT NULL,

USER$\_$RATING NUMBER$(2,1)$ NOT NULL

$)$;

CREATE TABLE BOOK$\_$CATEGORY $($

BOOK$\_$CATEGORY$\_$CD CHAR$(2)$ NOT NULL,

BOOK$\_$CATEGORY$\_$NAME VARCHAR$2(100)$ NOT NULL,

BOOK$\_$CATEGORY$\_$DESCRIPTION VARCHAR$2(2000)$ NOT NULL

$)$;

CREATE TABLE ORDERS $($

ORDER$\_$NBR NUMBER$(12)$ NOT NULL,

CUSTOMER$\_$ID NUMBER$(6)$ NOT NULL,

ORDER$\_$DATE TIMESTAMP NOT NULL,

ORDER$\_$TOTAL NUMBER$(8,2)$ NOT NULL,

SALESPERSON NUMBER$(6)$ NOT NULL

$)$;

CREATE TABLE ORDER$\_$ITEMS $($

ORDER$\_$ITEM$\_$ID NUMBER$(20)$ NOT NULL,

ORDER$\_$NBR NUMBER$(12)$ NOT NULL,

BOOK$\_$ID NUMBER$(6)$ NOT NULL,

UNIT$\_$PRICE NUMBER$(8,2)$ NOT NULL,

QUANTITY NUMBER$(8)$ NOT NULL

$)$;

ALTER TABLE CUSTOMERS ADD CONSTRAINT PK$\_$CUSTOMERS PRIMARY KEY $($CUSTOMER$\_$ID$)$;

ALTER TABLE BOOKS ADD CONSTRAINT PK$\_$BOOKS PRIMARY KEY $($BOOK$\_$ID$)$;

ALTER TABLE BOOK$\_$CATEGORY ADD CONSTRAINT PK$\_$BOOK$\_$CATEGORY PRIMARY KEY $($BOOK$\_$CATEGORY$\_$CD$)$;

ALTER TABLE ORDERS ADD CONSTRAINT PK$\_$ORDERS PRIMARY KEY $($ORDER$\_$NBR$)$;

ALTER TABLE ORDER$\_$ITEMS ADD CONSTRAINT PK$\_$ORDER$\_$ITEMS PRIMARY KEY $($ORDER$\_$ITEM$\_$ID$)$;

$--$ Adding foreign key constraint to the tables

ALTER TABLE BOOKS ADD CONSTRAINT FK$\_$BOOK$\_$BOOK$\_$CAT FOREIGN KEY $($BOOK$\_$CATEGORY$\_$CD$)$

REFERENCES BOOK$\_$CATEGORY $($BOOK$\_$CATEGORY$\_$CD$)$;

ALTER TABLE ORDERS ADD CONSTRAINT FK$\_$ORD$\_$CUST FOREIGN KEY $($CUSTOMER$\_$ID$)$

REFERENCES CUSTOMERS $($CUSTOMER$\_$ID$)$;

ALTER TABLE ORDERS ADD CONSTRAINT FK$\_$ORD$\_$EMP FOREIGN KEY $($SALESPERSON$)$

REFERENCES EMPLOYEES $($EMPLOYEE$\_$ID$)$;

ALTER TABLE ORDER$\_$ITEMS ADD CONSTRAINT FK$\_$ORD$\_$ITM$\_$ORD FOREIGN KEY $($ORDER$\_$NBR$)$

REFERENCES ORDERS $($ORDER$\_$NBR$)$;

ALTER TABLE ORDER$\_$ITEMS ADD CONSTRAINT FK$\_$ORD$\_$ITM$\_$PROD FOREIGN KEY $($BOOK$\_$ID$)$

REFERENCES BOOKS $($BOOK$\_$ID$)$;

ALTER TABLE ORDERS ADD CONSTRAINT FK$\_$ORD$\_$EMP FOREIGN KEY $($SALESPERSON$\_$ID$)$

REFERENCES EMPLOYEE $($EMPLOYEE$\_$ID$)$;

ALTER TABLE ORDER$\_$ITEMS ADD CONSTRAINT UK$\_$ORD$\_$ITM UNIQUE $($ORDER$\_$NBR$,$ BOOK$\_$ID$)$;

$/*$

CREATE TABLE ORDER$\_$ITEMS $($

ORDER$\_$ITEM$\_$ID NUMBER$(20)$ NOT NULL,

ORDER$\_$NBR NUMBER$(12)$ NOT NULL,

BOOK$\_$ID NUMBER$(6)$ NOT NULL,

UNIT$\_$PRICE NUMBER$(8,2)$ NOT NULL,

QUANTITY NUMBER$(8)$ NOT NULL,

CONSTRAINT PK$\_$ORDER$\_$ITEMS PRIMARY KEY $($ORDER$\_$ITEM$\_$ID$),$

CONSTRAINT FK$\_$ORD$\_$ITM$\_$ORD FOREIGN KEY $($ORDER$\_$NBR$)$ REFERENCES ORDERS $($ORDER$\_$NBR$),$

CONSTRAINT FK$\_$ORD$\_$ITM$\_$PROD FOREIGN KEY $($BOOK$\_$ID$)$ REFERENCES BOOKS $($BOOK$\_$ID$),$

CONSTRAINT UK$\_$ORD$\_$ITM UNIQUE $($ORDER$\_$NBR$,$ BOOK$\_$ID$)$

$)$;

$*/$