Table 1: CUSTOMER. Problem 1 Assume that The Queen Anne Curiosity Shop designs a
ID: 3890222 • 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
============== CUSTOMER=============
CREATE TABLE CUSTOMER (
CustomerID int,
LastName varchar(255),
FirstName varchar(255),
City varchar(255),
Phone varchar(255),
);
INSERT INTO CUSTOMER (CustomerID, LastName, FirstName, City, Phone) VALUES (1, 'Shire', 'Robert', 'Seattle', '206-524-2433');
INSERT INTO CUSTOMER (CustomerID, LastName, FirstName, City, Phone) VALUES (2, 'goodyear', 'Katherine', 'Seattle', '206-524-2444');
============ VENDOR===========
CREATE TABLE VENDOR (
VendorID int,
CompanyName varchar(255),
ContactLastName varchar(255),
ContactFirstName varchar(255),
City varchar(255),
Email varchar(255),
);
INSERT INTO VENDOR (VendorID, CompanyName, ContactLastName, City, Email) VALUES (1, 'NewYorkCompany', 'Lee', 'Andreew', 'Seattle', 'cs@business.com');
INSERT INTO VENDOR (VendorID, CompanyName, ContactLastName, City, Email) VALUES (2, 'European', 'Smith', 'mark', 'Seattle', 'myt@business.com');
============ ITEM===========
CREATE TABLE ITEM (
ItemID int,
ItemDescription varchar(255),
PurchedDate Date,
ItemCost int,
VendorID int
);
INSERT INTO ITEM (ItemID, ItemDescription, PurchedDate, ItemCost, VendorID) VALUES (1, 'Antiquedesk', 7-Nov-2012, 18000, 2);
INSERT INTO ITEM (ItemID, ItemDescription, PurchedDate, ItemCost, VendorID) VALUES (2, 'Antiquedeskchair', 28-Nov-2012, 300, 1);
============ SALE ===========
CREATE TABLE SALE (
SaleID int,
CustomerID int,
ItemID int,
SaleDate Date,
SubTotal int,
Tax int,
Total int
);
INSERT INTO SALE (SaleID, CustomerID, ItemID, SaleDate, SubTotal, Tax, Total) VALUES (1, 1, 2, 14-Dec-2012, 3500, 290,3790);
INSERT INTO SALE (SaleID, CustomerID, ItemID, SaleDate, SubTotal, Tax, Total) VALUES (2, 2, 3, 15-Dec-2012, 1000, 83,1083);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.