Table 1: CUSTOMER. Problem 1 Assume that The Queen Anne Curiosity Shop designs a
ID: 3889112 • Letter: T
Question
Table 1: CUSTOMER. Problem 1 Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CustomerID LastName First Name City Phone Shire Robert Seattle 20624-2433 Goodyear Katharine Seattle 206-324-3544 CUSTOMER(Customer ID,Lastname,stName , city, Phone . VENDORVencorID.Companyare, contactLas LN ame, ContactFirstName, City Ena) LUEMItem ID. Item Description, Purchase Date, ItemCost, Vendor ID ) SALE (SaleID. Customer Io, Item ID, SaleDate, SubTotal Tax, Total Bancroft Chris Bellevue 425-635-9788 Table 2: VENDOR. The referential integrity constraints are: • vendorD in ITEM must exist in vendorID in VENDOR • CustomerID in SALE must exist in CustomerID in CUSTOMER temID in SALE must exist in ItemID in ITEM VendoscDI Company Name ContactlantName ContactFirsLName City Linens and Thin Huntington Seattle European Specialties Tedeema Ken Seattie late Andrew I Kirkland increase smewhere on New Yock Brokerage Mark Seattle I ButObusineEEcom Assume that CustomerID of CUSTOMER, ItemID of ITEM, and SalclD of SALE are all surrogate keys starting at 1, incrementing by 1. Write SQL statements and answer questions for this database as follows. Table 3: ITEM. ItemlD Item Description Purchase Date temCoast VendorID 1. Write SQL CREATE TABLE statements for each of these tables. (60 points) 7-Nov-12 Antique Jenk Ant he Doak Chair 2- N. 300 Table L |4-Nov-12 600 (QMake your own assumptions regarding cascading deletions/updates Candles 14-Nov-12 2R-N 70 Antique Chair Antique Chair 28-Nov-12 1OGO (b) See data samples in Question 2 to determine whether an attribute should be NULL or NOT NULL. Table 4: SALE SaleID CustomerID ItemID Sale Date Subtotal Total (c) For data types, refer to our sample SQL in slides 2. Write SQL statements to insert following data to each table. Make sure you insert the complete and exactly the same data as shown in the following tables. 40 points) 14-Dev-12 3500 290.5 3790.5 15-Dec-12 1000 103 15-Dec-12 50 4.16 54.1 15-Jan-13 3000 29 3249Explanation / Answer
1.CREATE TABLE CUSTOEMER (CUSTOMERID NUMBER(20) PRIMARY KEY,LAST_NAME VARCHAR2(40),FIRST_NAME VARCHAR2(40),CITY VARCHAR2(40),PHONE NUMBER(20));
CREATE TABLE VENDOR(VENDORID NUMBER(20) PRIMARY KEY,COMAPANY_NAME VARCHAR2(20),,CONTACTLASTNAME VARCHAR2(40),CONTACTFIRSTNAME VARCHAR2(40),CITY VARCHAR2(20),EMAIL VARCHAR2(40));
CREATE TABLE ITEM(ITEMID NUMMBER(20) PRIMARY KEY,ITEMDESCRIPTION VARCHAR2(100),PURCHASEDATE DATE,ITEMCOST NUMEBER(20),VENDORID NUMBER(20));
CREATE TABLE SALE (SALEID NUMBER(20),CUSTOMERID NUMBER(20),ITEMID NUMBER(20),SALEDATE DATE,SUBTOTAL NUMBER(20),TAX DECIMAL(6,2),TOTAL DECIMAL(10,3));
-------------------------------------------------------------------
you can change the datatype as you required.
Adding foreign key constraint:
alter table
ITEM
add constraint
fk_ITEM_V_ID FOREIGN KEY (VENDOR_ID)
references
VENDOR (VENDOR_ID)-----------------------the first referential integrity mean vendorid should be foreign key in ITEM table where master table will be VENDOR
alter table
SALE
add constraint
fk_SALE_CUST_ID FOREIGN KEY (CUSTOMERID)
references
CUSTOMER (CUSTOMERID)-----------------------the second referential integrity mean custommeID should be foreign key in SALE table where master table will be CUSTOMER
alter table
SALE
add constraint
fk_SALE_CUST_ID FOREIGN KEY (ITEMID)
references
ITEM (ITEMID)-----------------------the second referential integrity mean ITEMID should be foreign key in SALE table where master table will be ITEM
delete cascade mean when we delete a record in master table lets pick customer table, for the same customer id the record should be deleted from the sale
table.You can achieve by adding on delete cascade on the foreign key constraint
e.g.
alter table
SALE
add constraint
fk_SALE_CUST_ID FOREIGN KEY (ITEMID)
references
ITEM (ITEMID)
on DELETE CASCADE
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.