1.
COMS W4111.001 — Introduction to Databases
Fall 2020 Homework Assignment 1 Solutions
2. CREATE TABLE Drivers ( LicenseNumber Name
DateOfBirth PRIMARY KEY
CREATE TABLE InsurancePolicies ( PolicyNumber Cost
CHAR(32), CHAR(40), DATE, (LicenseNumber))
INTEGER, REAL,
REAL, (PolicyNumber))
INTEGER,
CHAR(32),
DATE,
REAL,
CHAR(50),
(LicenseNumber) REFERENCES Drivers
CREATE TABLE Tickets (
CREATE TABLE Cars (
CREATE TABLE CoveredBy (
CREATE TABLE Owns (
MaximumCoverage PRIMARY KEY
Number LicenseNumber Date
Fine Description FOREIGN KEY
ON DELETE CASCADE,
PRIMARY KEY
VIN
Year
Make
Model PRIMARY KEY
LicenseNumber PolicyNumber Since FOREIGN KEY FOREIGN KEY PRIMARY KEY
LicenseNumber VIN
FOREIGN KEY FOREIGN KEY PRIMARY KEY
(Number, LicenseNumber))
INTEGER, INTEGER, CHAR(40), CHAR(40), (VIN))
CHAR(32),
INTEGER,
DATE,
(LicenseNumber) REFERENCES Drivers, (PolicyNumber) REFERENCES InsurancePolicies, (LicenseNumber, PolicyNumber))
CHAR(32),
INTEGER,
(LicenseNumber) REFERENCES Drivers, (VIN) REFERENCES Cars, (LicenseNumber, VIN))
Without assertions, we cannot map into SQL the participation constraint of Drivers into Covered By, nor can we map the participation constraint of Cars into Owns.
3.
INSERT
INTO Customers (id, name, email)
VALUES (6, ‘Bill Clinton’, ‘wjc@whitewater.net’)
Customers
UPDATE Customers C
SET id = 6
WHERE C.name = ‘George Washington’
As there is a different tuple already with id =6 and id happens to be the primary key of Customers, this statement will fail.
UPDATE Customers C
SET email = ‘gw@vernon.net’ WHERE C.id = 6
The UNIQUE(email) constraint is violated because a different customer, with id=2, already has email = gw@vernon.net. Hence this statement fails.
DELETE
FROM Orders O WHERE O.id = 3
Orders
LineItems
DELETE
FROM Customers C WHERE C.id = 3
As the Orders table has an order with custID = 3, the customer with id=3 cannot be deleted due to the FOREIGN KEY constraint with the ON DELETE NO ACTION option. Hence this statement fails.
id
name
email
1
Thomas Jefferson
tj@monticello.com
2
George Washington
gw@vernon.net
3
John Kennedy
jfk@hyannis.org
4
George H. W. Bush
bush41@kennebunkport.us
6
Bill Clinton
wjc@whitewater.net
id
custID
saledate
1
1
03/05/1801
2
3
07/04/1961
id
orderID
item
quantity
cost
1
1
Quills
12
3.50
2
1
Parchment
5
3.88
1
2
Pens
7
23.00
2
2
Cuban Cigars
12
65.00
3
1
Tapered Candles
12
6.99
UPDATE Orders O SET id = 10 WHERE O.id = 1
Orders
LineItems
INSERT
INTO LineItems (id, orderID, item, quantity, cost) VALUES (1, 5, ‘Socks’, 5, 7.99)
Inserting a tuple into the LineItems table with orderID = 5 will fail: this insertion would violate the foreign key constraint on orderID since there is no order with that id in the Orders table.
DELETE
FROM Orders O
WHERE O.saledate > ‘01/01/1900’
Orders
LineItems
DELETE
FROM Customers C WHERE C.id = 3
Customers
id
custID
saledate
10
1
03/05/1801
2
3
07/04/1961
Id
orderID
item
quantity
cost
1
10
Quills
12
3.50
2
10
Parchment
5
3.88
1
2
Pens
7
23.00
2
2
Cuban Cigars
12
65.00
3
10
Tapered Candles
12
6.99
id
custID
saledate
10
1
03/05/1801
Id
orderID
item
quantity
cost
1
10
Quills
12
3.50
2
10
Parchment
5
3.88
3
10
Tapered Candles
12
6.99
id
name
email
1
Thomas Jefferson
tj@monticello.com
2
George Washington
gw@vernon.net
4
George H. W. Bush
bush41@kennebunkport.us
6
Bill Clinton
wjc@whitewater.net