Database Schema: DDLs

CREATE TABLE CUSTOMER_PROFILE (CUST_ID VARCHAR(10),FIRST_NAME VARCHAR(25),LAST_NAME VARCHAR(25),GENDER CHAR(1),STREET VARCHAR(30),APTNO VARCHAR (20),ZIPCODE NUMERIC(7),DOB DATETIME,CONSTRAINT CUSTOMER_PROFILE_PK PRIMARY KEY (CUST_ID)



 CREATE TABLE DEPT_REPOSITORY (DEPT_ID VARCHAR(10),ACTIVITY_TYPE VARCHAR(25),ACTIVITY_PLAN VARCHAR(25),ACTIVITY_PRICE NUMERIC(10,2),CONSTRAINT DEPT_REPOSITORY_PK PRIMARY KEY (DEPT_ID)



 CREATE TABLE EMPLOYEES(EMPID VARCHAR(10),DEPT_ID VARCHAR(10),EMPLOYEE_NAME VARCHAR(30),DESIGNATION VARCHAR(30),CONSTRAINT EMPLOYEES_PK PRIMARY KEY (EMPID),CONSTRAINT EMPLOYEES_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID)



 CREATE TABLE CUSTOMER_DEPT (CUST_ID VARCHAR(10),DEPT_ID VARCHAR(10),CUST_ACTIVITY_TYPE VARCHAR(25),CUST_ACTIVITY_PLAN VARCHAR(25),PLAN_JOIN_DATE DATETIME,PLAN_TERMINATION_DATE DATETIME,TRAINER_ID VARCHAR(10),CONSTRAINT CUSTOMER_DEPT_pk PRIMARY KEY(CUST_ID,DEPT_ID),CONSTRAINT CUSTOMER_DEPT_CUST_ID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID),CONSTRAINT CUSTOMER_DEPT_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID),CONSTRAINT CUSTOMER_DEPT_TRAINER_ID_fk FOREIGN KEY(TRAINER_ID) REFERENCES EMPLOYEES(EMPID)




 CREATE TABLE ACCOUNTS (CUST_ID VARCHAR(10),DEPT_ID VARCHAR(10)CUST_TOT_COST NUMERIC(10,2),CONSTRAINT ACCOUNTS_pk PRIMARY KEY(CUST_ID,DEPT_ID),CONSTRAINT ACCOUNTS_CUSTID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID),CONSTRAINT ACCOUNTS_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID),



 CREATE TABLE INVENTORY(PROD_ID VARCHAR(10),PROD_NAME VARCHAR(25),DEPT_ID VARCHAR(10),PROD_COST NUMERIC(10,2),PROD_QUANT NUMERIC (5),CONSTRAINT INVENTORY_pk PRIMARY KEY(PROD_ID),CONSTRAINT INVENTORY_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID)



 CREATE TABLE CUSTOMER_LOGIN(CUST_USERNAME VARCHAR(20),CUST_ID VARCHAR(10),CUST_PWD VARCHAR (20),CONSTRAINT CUSTOMER_LOGIN_pk PRIMARY KEY(CUST_USERNAME),CONSTRAINT CUSTOMER_LOGIN_CUSTID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID));););););););