Question

SQL Homework -- This script creates the schema named mgs -- Connect as the user named mgs --CONNECT cs270226mgs/mgs; -- Use an anonymous PL/SQL script to -- drop all tables and sequences in the current schema and -- suppress any error messages that may displayed -- if these objects don't exist BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE category_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE product_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE customer_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE address_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE order_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE item_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE admin_id_seq'; EXECUTE IMMEDIATE 'DROP TABLE administrators'; EXECUTE IMMEDIATE 'DROP TABLE order_items'; EXECUTE IMMEDIATE 'DROP TABLE orders'; EXECUTE IMMEDIATE 'DROP TABLE products'; EXECUTE IMMEDIATE 'DROP TABLE categories'; EXECUTE IMMEDIATE 'DROP TABLE addresses'; EXECUTE IMMEDIATE 'DROP TABLE customers'; -- EXECUTE IMMEDIATE 'DROP TABLE author'; EXECUTE IMMEDIATE 'DROP TABLE bookauthor'; EXECUTE IMMEDIATE 'DROP TABLE books'; EXECUTE IMMEDIATE 'DROP TABLE order_items'; EXECUTE IMMEDIATE 'DROP TABLE orderitems'; EXECUTE IMMEDIATE 'DROP TABLE orders'; EXECUTE IMMEDIATE 'DROP TABLE promotion'; EXECUTE IMMEDIATE 'DROP TABLE publisher'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(''); END; / -- Create the tables CREATE TABLE categories ( category_id NUMBER PRIMARY KEY, category_name VARCHAR2(255) NOT NULL UNIQUE ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, category_id NUMBER REFERENCES categories (category_id), product_code VARCHAR2(10) NOT NULL UNIQUE, product_name VARCHAR2(255) NOT NULL, description VARCHAR2(1500) NOT NULL, list_price NUMBER(10,2) NOT NULL, discount_percent NUMBER(10,2) DEFAULT 0.00, date_added DATE DEFAULT NULL ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, email_address VARCHAR2(255) NOT NULL UNIQUE, password VARCHAR2(60) NOT NULL, first_name VARCHAR2(60) NOT NULL, last_name VARCHAR2(60) NOT NULL, shipping_address_id NUMBER DEFAULT NULL, billing_address_id NUMBER DEFAULT NULL ); CREATE TABLE addresses ( address_id NUMBER PRIMARY KEY, customer_id NUMBER REFERENCES customers (customer_id), line1 VARCHAR2(60) NOT NULL, line2 VARCHAR2(60) DEFAULT NULL, city VARCHAR2(40) NOT NULL, state VARCHAR2(2) NOT NULL, zip_code VARCHAR2(10) NOT NULL, phone VARCHAR2(12) NOT NULL, disabled NUMBER(1) DEFAULT 0 ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER REFERENCES customers (customer_id), order_date DATE NOT NULL, ship_amount NUMBER(10,2) NOT NULL, tax_amount NUMBER(10,2) NOT NULL, ship_date DATE DEFAULT NULL, ship_address_id NUMBER NOT NULL, card_type VARCHAR2(50) NOT NULL, card_number CHAR(16) NOT NULL, card_expires CHAR(7) NOT NULL, billing_address_id NUMBER NOT NULL ); CREATE TABLE order_items ( item_id NUMBER PRIMARY KEY, order_id NUMBER REFERENCES orders (order_id), product_id NUMBER REFERENCES products (product_id), item_price NUMBER(10,2) NOT NULL, discount_amount NUMBER(10,2) NOT NULL, quantity NUMBER NOT NULL ); CREATE TABLE administrators ( admin_id NUMBER PRIMARY KEY, email_address VARCHAR2(255) NOT NULL, password VARCHAR2(255) NOT NULL, first_name VARCHAR2(255) NOT NULL, last_name VARCHAR2(255) NOT NULL ); -- Disable substitution variable prompting SET DEFINE OFF; -- Insert data into the tables INSERT INTO categories (category_id, category_name) VALUES (1, 'Guitars'); INSERT INTO categories (category_id, category_name) VALUES (2, 'Basses'); INSERT INTO categories (category_id, category_name) VALUES (3, 'Drums'); INSERT INTO categories (category_id, category_name) VALUES (4, 'Keyboards'); CREATE SEQUENCE category_id_seq START WITH 5; 1. Draw a database diagram for the script file named create_mgs_tables.sql that’s in the mgs_ex_starts folder.

2. Draw a database diagram for a database that stores information about the downloads that users make. Each user must have an email address, first name, and last name. Each user can have one or more downloads. Each download must have a filename and download date/time. Each product can be related to one or more downloads. Each product must have a name.

Answer 1

1.

2.