SQL

Database Management Systems (COIS 3400H, Fall 2014)

Assignment #3

Due: Tuesday, Nov. 25th, 11:59pm (Submit by the Learning System)

1. [21 points] Consider the relations Books1 and Books2. Give the results of the following relational algebra expressions:

(a) πname (σprice<15(Books2))

(b) Books1∪Books2

(c) Books1∩Books2

(d) Books1- Books2,

(e) Books1×Books2

(f) Books1 Books1.myprice<Books2.priceBooks2,

(g) Books1 Books2

Books1 Books2 bid name myprice 234 Chicago 22.95 12 Berlin 9.95 567 London 17.95

2. [20 points] In Assignment 2, Question 4, you were asked to draw a UML diagram for a financial system (see the diagram next page). Transfer the UML model to a database schema. Specify keys & foreign keys for each relation.

bid name price 234 Chicago 7.95 12 Berlin 9.95 567 London 17.95

Bank Code{key} Name Addr

BankBranch BranchNo{pkey} Addr

Account AccNo{key} Type Balance

Customer SSN{key} Name Phone Addr

Loan LoanNo{key} Amount Type

ProferredCustomer years service

2..* 1

{IDß}

100..*

*

1

1..*

1

1..*

1..* *

3. [29 points] Create a database for a small hardware shop with following data dictionaries (You figure out what are the foreign keys). Write corresponding SQL statements/queries for the given questions.

(1) Customer(CustomerID int, Name char[30], Phone char[30], City char[50], AccountBalance real) Data:

CustomerID Name Phone City AccountBalance 1 Terry 111-111-2222 Madison $200.00 2 Leanne 222-333-4444 Madison $21.00 3 Jim 333-444-2222 Chicago $1280.00 4 Katy 444-555-1111 San Francisco $0.00 5 David 555-222-1111 New York $550.00 6 Willie 333-111-3333 Atlanta $230.00

(2) Inventory(ItemID int, Description char[100], ListPrice real)

Data

ItemID Description quantity ListPrice 1 Garden rake 28 $8.99 2 Leaf rake 43 $7.99 3 Garden shovel 21 $15.99 4 Trowel 6 $7.99 5 Hoe 20 $5.99 6 Pruning shears 5 $15.99 7 Machete 12 $9.99 8 Cultivator 42 $5.99 9 Bulb planter 5 $3.89

10 Pitchfork 4 $21.99

(3) Sale (SaleID int, CustomerID int, SaleDate)

Data SaleID CustomerID SaleDate

101 1 02/05/2005 102 4 02/07/2005 103 2 01/07/2005 104 5 03/08/2005 105 6 03/09/2003 106 1 03/10/2004 107 3 03/11/2005 108 5 04/12/2005 109 2 04/13/2005 110 4 04/14/2005

(4) SaleItem(SaleID int, ItemID int, Quantity int, SalePrice real)

Data

SaleID ItemID Quantity SalePrice 101 3 5 $14.99 101 4 10 $7.90 102 5 50 $4.99 102 6 20 $14.99 102 9 10 $3.80 103 7 5 $9.09 103 8 10 $5.69 104 2 10 $7.99 104 3 20 $15.99 105 1 5 $8.99 105 8 10 $5.99 106 1 20 $8.99 106 8 30 $5.99 107 5 50 $5.99 108 3 40 $15.99 109 2 40 $7.99 109 4 50 $7.99 110 3 3 $15.99 110 6 40 $15.99

Questions:

(a) List all parts that have less than 10 left in warehouse. (b) Order 20 new ones for each part in the result of query 1. (c) List total sales amount by month for this year's sale. (d) What is the largest discount (ListPrice-SalePrice) given? (e) What is the current value of their inventory? (f) Add a new customer to customer table (g) The new customer bought 10 Pruning shears today at a 10% discount

of the list price. Record this sale in the database.