Scribd
Upload
13 views2 pages

Tutorial 7 Solution

The document discusses relational algebra solutions for three schemas: Suppliers, Employees, and a salon's Appointments. It provides answers to various questions regarding keys, selections, and projections within these schemas, emphasizing the relationships and constraints between entities. The document also touches on efficiency in query execution and the importance of appropriate joins in relational databases.

Uploaded by

Jagrati Kaushik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views2 pages

Tutorial 7 Solution

The document discusses relational algebra solutions for three schemas: Suppliers, Employees, and a salon's Appointments. It provides answers to various questions regarding keys, selections, and projections within these schemas, emphasizing the relationships and constraints between entities. The document also touches on efficiency in query execution and the importance of appropriate joins in relational databases.

Uploaded by

Jagrati Kaushik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Relational Algebra Solution of Tutorial

First Schema
Suppliers(sID, sName, address) Parts(pID,
pName, colour) Catalog(sID, pID, price)

Catalog[sID] ⊆ Suppliers[sID] Catalog[pID] ⊆


Parts[pID]
1. Answer: Just because it is a key in one relation doesn’t mean it is in another; being a key is relative to
the relation. But is it a key for Catalog? No. We almost surely want to be able to list multiple parts
by one supplier in our catalog.

2. Answer: ΠpN ame(σcolour=“red”P avts)

3. Answer: Πprice((σcolour=“red”∨colour=“green”P avts) da Catalog)

4. Answer: ΠsID((σcolour=“red”∨colour=“green”P avts) da Catalog)

5. Answer: Trick question. Each tuple has only one colour, and each part has only one tuple (since
pID is a key), so no part can be recorded as both red and green.

6. Answer: ΠsName((ΠsID((σcolour=“red”∨colour=“green”Pavts) da Catalog)) da Supplievs)

Second Schema
Employees(number, name, age, salary)

Supervises(boss, employee)

Supervises[boss] ⊆ Employees[number] Supervises[employee]


⊆ Employees[number]
7. Answer: Every employee has one boss.
8. Answer: Yes
9. Answer: It would mean that every boss could have at most one employee. Not very sensible!

10. Answer: This would imply that neither alone is a key, since keys are minimal. Thus, bosses could have
multliple employees (sensible) and employees could have multiple bosses (possibly sensible).
11. Answer:Πname,salaryσboss=numbev((Πbossσnumber=employee((Πnumberσsalary>100Employee)×Supevvises))×Employee)

Third Schema
This schema is for a salon. Services could be things like “haircut” or “manicure”.

Clients(CID, name, phone)


Staff(SID, name)
Appointments(CID, date, time, service, SID)

Appointments[CID] ⊆ Clients[CID]
Appointments[SID] ⊆ Staff[SID]

12. Answer:Πname,time((ΠCID,SID,timeσdate=“F eb14”Appointments) da (ΠSIDσname=“Giuliano”Staf f ) da Clients)


13. Answer: This time, we mustn’t use natural join or we’ll force the client name and staff names to match, which
would be very inappropriate! So we use Cartesian product and are stuck enforcing all the things that do need to
match, like SID when we combine Staff and Appointments.

14. Answer:The first is more “efficient” because it produces smaller intermediate relations. But since this is all just
math, it doesn’t matter! (And in a DMBS, where queries are actually executed and can therefore be more or less
efficient, the DBMS optimizes our queries).

You might also like