CSE 444
University of Washington
Winter 2000
Homework 2
See the web page for some homework guidelines and policies.
DUE: at beginning of class, Wednesday January 26
Here are the relations and dependencies:
Name ® Breed
Breed ® MaxSize
{Ssn, Pnumber} ® Hours
Ssn ® Ename
Pnumber ® {Pname, Plocation}
{VisitingTeam, GameDate} ® {GameTime, Referee, Rink}
{Referee, GameDate, GameTime} ® VisitingTeam
{Referee, GameDate} ® Rink
You will write relational algebra queries against a DB whose schema is described as follows in SQL. Assume all relations are in BCNF.
/* EMPLOYEE; foreign key referencing department is added later */
create table employee (fname char(10), minit char, lname char(10), ssn numeric primary key, bdate smalldatetime, address char(30), sex char, salary numeric, superssn numeric references employee (ssn), dno numeric)/* DEPARTMENT */
create table department (dname char(20), dnumber numeric primary key, mgrssn numeric references employee (ssn), mgrstartdate smalldatetime)/* Foreign key from EMPLOYEE to DEPARTMENT */
alter table employee add foreign key (dno) references department (dnumber)/* DEPT_LOCATIONS */
create table dept_locations (dnumber numeric references department (dnumber), dlocation char(15))alter table dept_locations add primary key (dnumber, dlocation)
/* PROJECT */
create table project (pname char(20), pnumber numeric primary key, plocation char(15), dnum numeric)alter table project add foreign key (dnum, plocation) references dept_locations (dnumber, dlocation)
/* WORKS_ON */
create table works_on (essn numeric references employee (ssn), pno numeric references project (pnumber), hours numeric(18,1))alter table works_on add primary key (essn, pno)
/* DEPENDENT */
create table dependent (essn numeric references employee (ssn), dependent_name char(10), sex char, bdate smalldatetime, relationship char(10))alter table dependent add primary key (essn, dependent_name)
Write the following queries in relational algebra. If a query can not be
expressed in the relational algebra, explain why.