LAB Assesment Database ( Individual) PDF

Preview

Summary

Download LAB Assesment Database ( Individual) PDF

Description

SCHOOL OF COMPUTING SESSION 2020/2021 SEMESTER 1 COURSE CODE SECD2523 - DATABASE

LECTURER’S NAME MS. ROZILAWATI BINTI DOLLAH @ MD. ZAIN

LAB 1 (INDIVIDUAL TASK) TITLE LAB EXERCISE (ORACLE) STUDENT’S NAME SITI NUR FATIHAH BINTI AZIZAN MATRIC NUMBER B20EC0050 SECTION 03

Section 6 Lesson 3 Exercise : Data Definition Language

Part 1: Reading information from a script. In this exercise you will use the “obl Sports.ddl” file to consolidate your knowledge of DDL. Open the “obl Sports.ddl” in a text editor. 1. How many tables have been created using the CREATE TABLE statement? ANSWER: Ten tables were created. 2. How many columns are created for the price history table? ANSWER: Six columns were created. • • • •

START_DATE START_TIME PRICE END_DATE

• •

END_TIME ITM_NUMBER

3. What statement is used to enforce the constraint that the category column of the items table must have a value? ANSWER: • • •

(Table) Check constraints Foreign key (referential) constraints Primary key constraints

2

4. What is the name of the foreign key constraint between the customers and customer addresses tables? ANSWER: ALTER TABLE customers_addresses ADD CONSTRAINT customer_address_customer_fk FOREIGN KEY ( ctr_number ) REFERENCES customers ( ctr_number ); ALTER TABLE customers ADD CONSTRAINT customer_sales_rep_fk FOREIGN KEY ( sre_id ) REFERENCES sales_representatives ( id ); •

ctr_number

5. What are the lowest and highest values that can be stored in the commission_rate column for the sales_representatives table? ANSWER: • •

Highest values = 10 Lowest values = 5

INSERT INTO sales_representatives (id, email, first_name, last_name, phone_number, commission_rate, supervisor_id) VALUES('sr01', '[email protected]', 'Charles', 'Raymond', '0134598761', 10, 'sr01'); INSERT INTO sales_representatives (id, email, first_name, last_name, phone_number, commission_rate, supervisor_id) VALUES('sr02', '[email protected]', 'Victoria', 'Wright','0134598762', 5, 'sr01'); INSERT INTO sales_representatives (id, email, first_name, last_name, phone_number, commission_rate, supervisor_id) VALUES('sr03', '[email protected]', 'Barry', 'Speed', '0134598763', 5, 'sr01');

6. What are the lowest and highest values that can be stored in the price column for the price_history table? ANSWER: • •

Highest values = 149.00 Lowest values = 4.99

3

7. What are the 3 columns that make up the primary key for the price_history table? ANSWER: • • •

ITM_NUMBER START_DATE START_TIME

4

Part 2 : Updating Constraints Log-in to APEX and go to the SQL commands environment Modifying a column 1. Run the DESCRIBE command on the orders table to view its structure. 2. Task: Add a default constraint that will use todays date to assign a value to the odr_date column of the orders table if no date is provided. 3. Run the DESCRIBE command again to verify the command was successful.

5

Adding a check constraint 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Add a check constraint that will not allow the customers current balance to go below zero. 3. Run the DESCRIBE command again to verify the command was successful. 4. A check constraint is not shown in the results of a describe command. 1. Go to the Object Browser 2. Select the customers table. 3. Click on the CONSTRAINTS tab. 4. You will see your constraint here.

6

Adding a column The client has decided that they would like a separate column for the customer’s mobile phone number. This is an optional column that will be required to store 11 digits. 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Add column that will satisfy the clients requirements 3. Run the DESCRIBE command on the customers table to view its structure.

Phone_number column added

7

CREATE TABLE "CUSTOMERS" ( "CTR_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "EMAIL" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LAST_NAME" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "CURRENT_BALANCE" NUMBER(6,2) NOT NULL ENABLE, "SRE_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "TEM_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "LOYALTY_CARD_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP", "PHONE_NUMBER" VARCHAR2(11) COLLATE "USING_NLS_COMP", CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CTR_NUMBER") USING INDEX ENABLE, CONSTRAINT "CTR_EMAIL_UK" UNIQUE ("EMAIL") USING INDEX ENABLE, CONSTRAINT "CTR_LCN_UK" UNIQUE ("LOYALTY_CARD_NUMBER") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" /

8

Dropping a column The client has decided that they don’t need the mobile number column as most customers only provide a single contact number and that is already catered for with the existing phone_number column. 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Drop the column that was created to store the mobile phone number. 3. Run the DESCRIBE command on the customers table to view its structure.

Drop unused column phone_number

Phone_number column dropped

CREATE TABLE "CUSTOMERS" ( "CTR_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "EMAIL" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LAST_NAME" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "CURRENT_BALANCE" NUMBER(6,2) NOT NULL ENABLE, "SRE_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "TEM_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "LOYALTY_CARD_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP", CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CTR_NUMBER") USING INDEX ENABLE, CONSTRAINT "CTR_EMAIL_UK" UNIQUE ("EMAIL") USING INDEX ENABLE, CONSTRAINT "CTR_LCN_UK" UNIQUE ("LOYALTY_CARD_NUMBER") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP"

9

DDL GENERATED CREATE TABLE "CUSTOMERS" ( "CTR_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "EMAIL" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LAST_NAME" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(11) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "CURRENT_BALANCE" NUMBER(6,2) NOT NULL ENABLE, "SRE_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "TEM_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP", "LOYALTY_CARD_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP", CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CTR_NUMBER") USING INDEX ENABLE, CONSTRAINT "CTR_EMAIL_UK" UNIQUE ("EMAIL") USING INDEX ENABLE, CONSTRAINT "CTR_LCN_UK" UNIQUE ("LOYALTY_CARD_NUMBER") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "CUSTOMERS_ADDRESSES" ( "ID" VARCHAR2(8) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ADDRESS_LINE_1" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ADDRESS_LINE_2" VARCHAR2(30) COLLATE "USING_NLS_COMP", "CITY" VARCHAR2(15) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ZIP_CODE" VARCHAR2(7) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "CTR_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "CUSTOMER_ADDRESS_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "INVENTORY_LIST" (

"ID" VARCHAR2(11) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "COST" NUMBER(7,2) NOT NULL ENABLE, "UNITS" NUMBER(4,0) NOT NULL ENABLE, CONSTRAINT "INVENTORY_LIST_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "ITEMS" ( "ITM_NUMBER" VARCHAR2(10) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "CATEGORY" VARCHAR2(25) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "COLOR" VARCHAR2(15) COLLATE "USING_NLS_COMP", "Size" CHAR(1) COLLATE "USING_NLS_COMP", "ILT_ID" VARCHAR2(11) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "ITEM_PK" PRIMARY KEY ("ITM_NUMBER") USING INDEX ENABLE

10

) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "ORDERED_ITEMS" ( "QUANTITY_ORDERED" NUMBER(3,0) NOT NULL ENABLE, "QUANTITY_SHIPPED" NUMBER(3,0) NOT NULL ENABLE, "ITM_NUMBER" VARCHAR2(10) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ODR_ID" VARCHAR2(9) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "ORDERED_ITEM_PK" PRIMARY KEY ("ITM_NUMBER", "ODR_ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "ORDERS" ( "ID" VARCHAR2(9) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ODR_DATE" DATE NOT NULL ENABLE, "ODR_TIME" DATE NOT NULL ENABLE, "NUMBER_OF_UNITS" NUMBER(2,0) NOT NULL ENABLE, "CTR_NUMBER" VARCHAR2(6) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "ORDERS_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "PRICE_HISTORY" ( "START_DATE" DATE NOT NULL ENABLE, "START_TIME" DATE NOT NULL ENABLE, "PRICE" NUMBER(7,2) NOT NULL ENABLE, "END_DATE" DATE, "END_TIME" DATE, "ITM_NUMBER" VARCHAR2(10) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "PRICE_HISTORY_PK" PRIMARY KEY ("ITM_NUMBER" , "START_DATE", "START_TIME") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "SALES_REPRESENTATIVES" ( "ID" VARCHAR2(4) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "EMAIL" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LAST_NAME" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(11) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "COMMISSION_RATE" NUMBER(2,0) NOT NULL ENABLE, "SUPERVISOR_ID" VARCHAR2(4) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "SALES_REPRESENTATIVE_PK" PRIMARY KEY ("ID") USING INDEX ENABLE, CONSTRAINT "SRE_EMAIL_UK" UNIQUE ("EMAIL") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "SALES_REP_ADDRESSES" ( "ID" VARCHAR2(4) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,

11

"ADDRESS_LINE_1" VARCHAR2(30) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ADDRESS_LINE_2" VARCHAR2(30) COLLATE "USING_NLS_COMP", "CITY" VARCHAR2(15) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ZIP_CODE" VARCHAR2(7) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, CONSTRAINT "SALES_REP_ADDRESS_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / CREATE TABLE "TEAMS" ( "ID" VARCHAR2(4) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "NUMBER_OF_PLAYERS" NUMBER(2,0) NOT NULL ENABLE, "DISCOUNT" NUMBER(2,0), CONSTRAINT "TEAM_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) DEFAULT COLLATION "USING_NLS_COMP" / ALTER TABLE "CUSTOMERS_ADDRESSES" ADD CONSTRAINT "CUSTOMER_ADDRESS_CUSTOMER_FK" FOREIGN KEY ("CTR_NUMBER") REFERENCES "CUSTOMERS" ("CTR_NUMBER") ENABLE / ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "CUSTOMER_SALES_REP_FK" FOREIGN KEY ("SRE_ID") REFERENCES "SALES_REPRESENTATIVES" ("ID") ENABLE / ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "CUSTOMER_TEAM_FK" FOREIGN KEY ("TEM_ID") REFERENCES "TEAMS" ("ID") ENABLE / ALTER TABLE "ITEMS" ADD CONSTRAINT "ITEM_INVENTORY_LIST_FK" FOREIGN KEY ("ILT_ID") REFERENCES "INVENTORY_LIST" ("ID") ENABLE / ALTER TABLE "ORDERED_ITEMS" ADD CONSTRAINT "ORDERED_ITEM_ITEM_FK" FOREIGN KEY ("ITM_NUMBER") REFERENCES "ITEMS" ("ITM_NUMBER") ENABLE / ALTER TABLE "ORDERED_ITEMS" ADD CONSTRAINT "ORDERED_ITEM_ORDER_FK" FOREIGN KEY ("ODR_ID") REFERENCES "ORDERS" ("ID") ENABLE / ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDER_CUSTOMER_FK" FOREIGN KEY ("CTR_NUMBER") REFERENCES "CUSTOMERS" ("CTR_NUMBER") ENABLE / ALTER TABLE "PRICE_HISTORY" ADD CONSTRAINT "PRICE_HISTORY_ITEMS_FK" FOREIGN KEY ("ITM_NUMBER") REFERENCES "ITEMS" ("ITM_NUMBER") ENABLE / ALTER TABLE "SALES_REP_ADDRESSES" ADD CONSTRAINT "SALES_REP_ADD_SALES_REP_FK" FOREIGN KEY ("ID") REFERENCES "SALES_REPRESENTATIVES" ("ID") ENABLE /

12

ALTER TABLE "SALES_REPRESENTATIVES" ADD CONSTRAINT "SALES_REP_SALES_REP_FK" FOREIGN KEY ("SUPERVISOR_ID") REFERENCES "SALES_REPRESENTATIVES" ("ID") ENABLE / CREATE OR REPLACE EDITIONABLE SYNONYM "DBMS_XPLAN" FOR "LIVESQL"."ORACLE_SQL_USER_XPLAN" / CREATE OR REPLACE EDITIONABLE SYNONYM "V$SESSION" FOR "LIVESQL" ."ORACLE_SQL_USER_V$SESSION" / CREATE OR REPLACE EDITIONABLE SYNONYM "V$SQL_PLAN_STATISTICS_ALL" FOR "LIVESQL" ."ORACLE_SQL_USER_V$SQL_PLAN_S_A" /

13...