Quesion 1 [30 marks] (a) Create a database for a small hardware shop with follow

Question

Quesion 1 [30 marks] (a) Create a database for a small hardware shop with following data dictionaries (You figure out what are the foreign keys). 1. Customer(CustomerlD int, Name char(30], Phone charl30]. City char[50] AccountBalance real) Data CustomerlD Name Phone City AccountBalance $200.00 $21.00 $1280 00 50.00 $550.00 $230.00 Terry 111-111-2222 Madison Leanne 222-333-4444Madison Jim 333-444-2222 Chicago Katy 444-555-1111 San Francisco 4 David 555-222-1111 New York Willie 333-111-3333 Atlanta 2. Inventory(ItemID int, Description charl100], ListPrice real) Data Description Garden rake Leaf reke Garden shovel Trowel Hoe Pruning shears Machete Culivator Bulb planter Pitchfork ListPrice $899 $7.99 $1599 57 99 $5.99 $15 99 %9.99 $5.99 $3.89 $2199 quentiy 28 43 21 20 12 42 10 3. Sale (SaleID int, CustomerlD int, SaleDate) Data SaleDate 02/05/2015 0207/2015 01107/2015 03/08/2015 03/09 2013 03/10/2014 031112015 04/12/2015 04/13/2015 04/14/2015 SalelD CustomerlD 101 102 103 104 105 106 107 108 109 110

Explanation / Answer

a).

CREATE DATABASE DepartmentalStore;

CREATE TABLE Customer(

CustomerID int NOT NULL,

Name char[30],

Phone char[30],

City char[50],

AccountBalance real,

PRIMARY KEY (CustomerID)

);

CREATE TABLE Inventory(

ItemID int NOT NULL,

Description char[100],

Quantity int,

ListPrice real,

PRIMARY KEY (ItemID)

);

CREATE TABLE Sale(

SaleID int NOT NULL,

CustomerID int NOT NULL,

SaleDate date,

PRIMARY KEY (SaleID),

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

);

CREATE TABLE SaleItem(

SaleID int NOT NULL,

ItemID int NOT NULL,

Quantity int,

SalePrice real,

FOREIGN KEY (SaleID) REFERENCES Sale(SaleID),

FOREIGN KEY (ItemID) REFERENCES Inventory(ItemID)

);

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (1,"Terry","111-111-2222","Madison","$200.00");

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (2,"Leanne","222-333-4444","Madison","$21.00");

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (3,"Jim","333-444-2222","Chicago","$1280.00");

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (4,"Katy","444-555-1111","San Francisco","$0.00");

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (5,"David","555-222-1111","New York","$550.00");

INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (6,"Willie","333-111-3333","Atlanta","$230.00");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (1,"Garden rake",28,"$8.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (2,"Leaf rake",43,"$7.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (3,"Garden shovel",21,"$15.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (4,"Trowel",6,"$7.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (5,"Hoe",20,"$5.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (6,"Pruning shears",5,"$15.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (7,"Machete",12,"$9.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (8,"Cultivator",42,"$5.99");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (9,"Bulb planter",5,"$3.89");

INSERT INTO Inventory(ItemID, Description, Quantity, ListPrice) VALUES (10,"Pitch fork",4,"$21.99");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (101,1,"2015-02-05");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (102,4,"2015-02-07");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (103,2,"2015-01-07");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (104,5,"2015-03-08");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (105,6,"2013-03-09");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (106,1,"2014-03-10");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (107,3,"2015-03-11");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (108,5,"2015-04-12");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (109,2,"2015-04-13");

INSERT INTO Sale(SaleID, CustomerID, SaleDate) VALUES (110,4,"2015-04-14");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (101,3,5,"$14.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (101,4,10,"$7.90");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (102,5,50,"$4.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (102,6,20,"$14.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (102,9,10,"$3.80");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (103,7,5,"$9.90");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (103,8,10,"$5.69");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (104,2,10,"$7.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (104,3,20,"$15.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (105,1,5,"$8.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (105,8,10,"$5.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (106,1,20,"$8.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (106,8,30,"$5.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (107,5,50,"$5.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (108,3,40,"$15.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (109,2,40,"$7.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (109,4,50,"$7.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (110,3,3,"$15.99");

INSERT INTO SaleItem(SaleID, ItemID, Quantity, SalePrice) VALUES (110,6,40,"$15.99");

b).

1). SELECT * FROM Inventory where Quantity < 15;

2). UPDATE Inventory set Quantity = Quantity + 30 where ItemID in (SELECT ItemId FROM Inventory where Quantity < 15);

3). select t1.SaleDate,t2.SalePrice from Sale as t1, SaleItem as t2 where t1.SaleId = t2.SaleId group by t1.SaleDate;

6). INSERT INTO Customer(CustomerID, Name, Phone, City, AccountBalance) VALUES (7,"Tom","222222-111-3333","Atlanta","$430.00");

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.