Analyze the case study and develop: 1)Using the attached ER-Diagram for the case

Question

Analyze the case study and develop:

1)Using the attached ER-Diagram for the case study, create a relational schema diagram. Make sure your diagram identifies relations, their attributes, the primary key, and referential integrity

2)Create a set of SQL files which create the tables of your diagram in Oracle. Have one file for each table. Thus, each file will contain one complete create table statement. In addition, create a separate SQL file to create the entire database and another SQL file to drop all tables in the database. The create database file should contain a list of @<filename> where file name is the name of a file that creates a table. The drop database file, is to drop each table you created. Thus all the tables for your database can be created with your create file and dropped with you drop file. All files are expected to run on Oracle

User Name PIN Street Address CREDIT CARD City ADMINISTRATOR Address Phone State ate Code Zip STATE State Name Order Number On Hand RESTOCK ORDER Title Order Point Price Contains Quantity Ordered CATAGORY Category Classified by has REVIEW BOOK Year Published Published By Written by Review Text Review No Quantity Ordered PUBLISHER Contains Price At Purchase AUTHOR Order Date First Name ORDER Street Address Order Time Last Name City Shipping Address First Name Last Name State Credit Card Type Street Address Cust Name PIN Zip Credit Card Number Total Subtotal City Address CUSTOMER Shipping Handling Expiration Date State Credit Card Type Zip Expiration Date Credit Card Number

Explanation / Answer

Here is the create table statements:

CREATE TABLE ORDER(
ORDER_DATE DATE,
ORDER_TIME NUMBER,
STREET_ADDRESS VARCHAR2(150),
CITY VARCHAR2(150),
STATE VARCHAR2(150),
ZIP NUMBER,
CREDIT CARD TYPE VARCHAR2(10),
EXPIRATION_DATE DATE,
SUBTOTAL NUMBER,
SHIPPINGHANDLING VARCHAR2(100),
TOTAL NUMBER
PRIMARY KEY(ORDER_DATE,ORDER_TIME);

CREATE TABLE CUSTOMER(
FIRST_NAME VARCHAR2(150)
LAST_NAME VARCHAR2(150),
USER_NAME PRIMARY KEY,
PIN NUMBER,
STREET_ADDRESS VARCHAR2(150),
CITY VARCHAR2(150),
STATE VARCHAR2(150),
ZIP NUMBER,
CREDIT CARD TYPE VARCHAR2(10),
CREDIT_CARD_NUMBER NUMBER
EXPIRATION_DATE DATE
);

CREATE TABLE AUTHOR(
FIRST_NAME VARCHAR2(150),
LAST_NAME VARCHAR2(150)
PRIMARY KEY (FIRST_NAME,LAST_NAME)
);

CREATE TABLE PUBLISHER(
PUBLISHER_NAME VARCHAR2(150) PRIMARY KEY
);

CREATE TABLE REVIEW(
REVIEW_NO NUMBER PRIMARY KEY,
REVIEW_TEXT VARCHAR2(1500)
);

CREATE TABLE CATEGORY(
CATEGORY_ID NUMBER PRIMARY KEY
);

CREATE TABLE BOOK(
ISBN NUMBER PRIMARY KEY,
TITLE VARCHAR2(150),
PRICE NUMBER,
ON_HAND NUMBER,
ORDER_PRINT NUMBER,
ACTIVE VARCHAR2(10)
);

CREATE TABLE RESTOCK_ORDER(
ORDER_NUMBER NUMBER PRIMARY KEY
);

CREATE TABLE ADMINISTRATOR(
USER_NAME VARCHAR2(150) PRIMARY KEY,
PIN NUMBER,
STREET_ADDRESS VARCHAR2(150),
CITY VARCHAR2(150),
STATE VARCHAR2(150),
ZIP NUMBER,
PHONE NUMBER
);

CREATE TABLE CREDIT_CARD_TYPE(
CREDIT_CARD_TYPE VARCHAR2(150) PRIMARY KEY
);

CREATE TABLE STATE(
STATE_CODE NUMBER PRIMARY KEY,
STATE_NAME VARCHAR2(100)
);

CREATE TABLE WRITTEN_BY(
ISBN NUMBER,
FIRST_NAME VARCHAR2(150),
LAST_NAME VARCHAR2(150),
CONSTRAINT FK_1 FOREIGN KEY (ISBN)
REFERENCES BOOK(ISBN),
CONSTRAINT FK_2 FOREIGN KEY (FIRST_NAME)
REFERENCES AUTHOR(FIRST_NAME),
CONSTRAINT FK_3 FOREIGN KEY (LAST_NAME)
REFERENCES AUTHOR(LAST_NAME)
);

CREATE TABLE HAS_REVIEW(
ISBN NUMBER,
REVIEW_NO NUMBER,
CONSTRAINT FK_4 FOREIGN KEY (ISBN)
REFERENCES BOOK(ISBN),
CONSTRAINT FK_5 FOREIGN KEY (REVIEW_NO)
REFERENCES REVIEW(REVIEW_NO),
);

CREATE TABLE CONTAINS(
ISBN NUMBER,
ORDER_NUMBER NUMBER,
ORDER_QUANTITY NUMBER,
PRICE NUMBER,
CONSTRAINT FK_6 FOREIGN KEY (ISBN)
REFERENCES BOOK(ISBN),
CONSTRAINT FK_7 FOREIGN KEY (ORDER_NUMBER)
REFERENCES RESTOCK_ORDER(ORDER_NUMBER),
);

CREATE TABLE CLASSIFIED_BY(
ISBN NUMBER,
CATEGORY_ID NUMBER,
CONSTRAINT FK_8 FOREIGN KEY (ISBN)
REFERENCES BOOK(ISBN),
CONSTRAINT FK_9 FOREIGN KEY (CATEGORY_ID)
REFERENCES CATEGORY(CATEGORY_ID),
);

CREATE TABLE PUBLISHED_BY(
ISBN NUMBER,
YEAR NUMBER,
PUBLISHER_NAME VARCHAR2(150),
CONSTRAINT FK_10 FOREIGN KEY (ISBN)
REFERENCES BOOK(ISBN),
CONSTRAINT FK_11 FOREIGN KEY (PUBLISHER_NAME)
REFERENCES PUBLISHER(PUBLISHER_NAME),
);

Drop table Commands:

DROP TABLE PUBLISHED_BY;
DROP TABLE CLASSIFIED_BY;
DROP TABLE CONTAINS;
DROP TABLE HAS_REVIEW;
DROP TABLE WRITTEN_BY;
DROP TABLE STATE;
DROP TABLE CREDIT_CARD_TYPE;
DROP TABLE ADMINISTRATOR;
DROP TABLE RESTOCK_ORDER;
DROP TABLE BOOK;
DROP TABLE CATEGORY;
DROP TABLE REVIEW;
DROP TABLE PUBLISHER;
DROP TABLE AUTHOR;
DROP TABLE CUSTOMER;
DROP TABLE ORDER;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---