7.24
Strictly speaking, a foreign key is a set of attributes, but when that set contains only one attribute, then that attribute itself is often informally called a foreign key. The schema of this question has the following five foreign keys:
1. the attribute Cust# of relation ORDER that references relation CUSTOMER,
2. the attribute Order# of relation ORDER_ITEM that references relation
ORDER,
3. the attribute Item# of relation ORDER_ITEM that references relation
ITEM,
4. the attribute Order# of relation SHIPMENT that references relation
ORDER, and
5. the attribute Warehouse# of relation SHIPMENT that references relation
WAREHOUSE.
We now give the queries in relational algebra:
a. SHIP_W2 < Û Warehouse# = ‘W2’ (SHIPMENT)
RESULT < ? Order#, Ship_date (SHIP_W2)
b. ORDERS_JL < Û Cname = ‘Jose Lopez’ (ORDER * CUSTOMER)
RESULT < ? Order#, Warehouse# (ORDERS_JL * SHIPMENT)
c. BY_CUSTNUM ? Cust# F COUNT Order#, AVERAGE Ord=Amt (ORDER)
RESULT (CUSTNAME, #OFORDERS, AVG_ORDER_AMT) ?
? Cname, COUNT=Order#, AVERAGE=Ord=Amt (BY_CUSTNUM * CUSTOMER)
d. TIMELY_SHIPPED < Û Odate + 30 ³ Ship=date (ORDER *
SHIPMENT)
RESULT < ? Order# (ORDER) - ? Order# (TIMELY_SHIPPED)
The above query lists all orders for which no “timely” shipment
was made, including orders for which no shipment was ever made. It
is instructive to compare the above query with the one below that lists
those orders for which at least one “late” shipment was made.
LATE_SHIPPED < Û Odate + 30 < Ship=date (ORDER *
SHIPMENT)
RESULT < ? Order# (LATE_SHIPPED)
e. NEW_YORK < ? Warehouse# (Û City = ‘New York’ (WAREHOUSE))
RESULT < ? Order#, Warehouse# (SHIPMENT) ¸ NEW_YORK
7.25
The schema of this question has the following two foreign keys:
1. the attribute SSN of relation TRIP that references relation SALESPERSON,
and
2. the attribute Trip_ID of relation EXPENSE that references relation
TRIP.
In addition, the attributes Dept_No of relation SALESPERSON and Account# of relation EXPENSE are probably also foreign keys referencing other relations of the database not mentioned in the question. We now give the queries in relational algebra:
a. COSTLY_TRIPS < Û SUM=Amount > 2000 (Trip=Id F SUM Amount
(EXPENSE))
RESULT ? TRIP * ? Trip=Id (COSTLY_TRIPS)
b. RESULT < ? SSN (Û To=City = ‘Honolulu’ (TRIP))
c. RESULT ? F SUM Amount (Û SSN = ‘234-56-7890’ (TRIP) * EXPENSE)
==============================
7.26
The schema of this question has the following four foreign keys:
1. the attribute SSN of relation ENROLL that references relation STUDENT,
2. the attribute Course# in relation ENROLL that references relation
COURSE,
3. the attribute Course# in relation BOOK_ADOPTION that references
relation COURSE, and
4. the attribute Book_ISBN of relation BOOK_ADOPTION that references
relation TEXT.
We now give the queries in relational algebra:
a. COURSES_JS_W99 < Û Name = ‘John Smith’ (STUDENT) * Û
Quarter = ‘W99’ (ENROLL)
RESULT ? F COUNT Course# (COURSES_JS_W99)
b. CS_ADOPTIONS < ? Course#, Book=ISBN (Û Dept = ‘CS’ (COURSE)
* BOOK_ADOPTION)
BOOK_COUNT ? Course# F COUNT Book=ISBN (CS_ADOPTIONS)
COURSES_NEEDED < ? Course# (Û COUNT=Book=ISBN > 2 (BOOK_COUNT))
RESULT < ? Course#, Book=ISBN, Book=Title (COURSES_NEEDED
* BOOK_ADOPTION * TEXT)
c. DEPT_PUBS < ? Dept, Publisher (COURSE * BOOK_ADOPTION * TEXT)
RESULT < ? Dept (COURSE) ? ? Dept (Û Publisher ? ‘BC Publishing’
(DEPT_PUBS))
==========================
7.27
a.
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
25 a 6 25 c 3
b.
P Q R A B C
15 b 8 10 b 6
15 b 8 10 b 5
c.
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
15 b 8 null null null
25 a 6 25 c 3
d.
P Q R A B C
15 b 8 10 b 6
Null null null 25 c 3
15 b 8 10 b 5
e.
P Q R
10 a 5
15 b 8
25 a 6
10 b 6
25 c 3
10 b 5
f.
P Q R A B C
10 a 5 10 b 5
=============================
7.28
The schema of this question has the following three foreign keys:
1. the attribute Serial-No of relation OPTIONS that references relation
CAR,
2. the attribute Satesperson-id in relation SALES that references relation
SALESPERSON, and
3. the attribute Serial-No of relation SALES that references relation
CAR.
Suppose that the OPTIONS and SALES relations are empty, and the CAR and SALESPERSON relations have exactly one tuple each as shown below:
CAR Serial-No Model Manufacturer Price
NN-97-6 ZX-300 Nissan 20000
SALESPERSON Salesperson-id Name Phone
EVE-00-69 Susan McPherson (316) 978-3923
Now, an attempt to insert the tuple < ‘DAY-98-12’, ‘NN-97-6’, ‘1998-09-28’, 23548 > into SALES violates referential integrity because SALESPERSON does not contain any tuple with key ‘DAY-98-12’. No attempt at inserting a tuple into SALESPERSON can cause referential integrity constraint violation because that relation does not have any foreign keys.
An example of an acceptable insertion into SALES is that of tuple < ‘EVE-00-69’, ‘NN-97-6’, ‘1998-09-28’, 23548 >. And an acceptable insertion into SALESPERSON is that of tuple < ‘MOR-89-23’, ‘John Feist’, ‘(708) 234-0128’ >.
We now give the queries in relational algebra:
a. RESULT < ? Serial-No, Manufacturer, Sale-price (Û Name =
‘Jane Doe’ (SALESPERSON) * SALES * CAR)
b. NO_OPTIONS < ? Serial-No (CAR) ? ? Serial-No (OPTIONS)
RESULT < ? Serial-No, Model (NO_OPTIONS * CAR)
c. Left outer join between relations SALESPERSON and SALES (in that
order) is the relation that contains all rows contained in the natural
join between these relations and, additionally, exactly one row for sales
persons that have not made any sale. The Serial-No, Date and Sale-price
attributes in those extra rows will be null. As an example, suppose
these relations have the following tuples:
SALES Salesperson-id Serial-No Date Sale-price
EVE-00-69 TY-1258G 1996-02-24 19708
MOR-89-23 ZX-300 1997-01-13 32006
EVE-00-69 ZX-300 1998-06-17 23259
SALESPERSON Salesperson-id Name Phone
EVE-98-12 Rosemarie Marlow (800) 468-7399
MOR-89-23 John Feist (708) 234-0128
EVE-00-69 Susan McPherson (316) 978-3923
Then, the outer join between SALESPERSON and SALES is the following relation:
Salesperson-id Name Phone Serial-No Date Sale-price
EVE-98-12 Rosemarie Marlow (800) 468-7399 null null null
MOR-89-23 John Feist (708) 234-0128 ZX-300 1997-01-13 32006
EVE-00-69 Susan McPherson (316) 978-3923 TY-1258G 1996-02-24 19708
EVE-00-69 Susan McPherson (316) 978-3923 ZX-300 1998-06-17 23259
c. Consider the query:
RESULT < ? Serial-No (CAR) ? ? Serial-No (Û Price = 3000 (OPTIONS))
This query produces the Serial
numbers of cars all of whose options (if any) cost less than $3,000.