HOMEWORK 3

Foreign key

We consider the relations  Employee and  Department.

Employee(EmpId,LastName,Dept)

Department(DeptId,Description,Location)

For example, the following diagram represents part of the  Employee and
the entire  Department table.

 Employee:

EmpId  LastName  Dept 
1  Slate  1 
4  Flinstone  2 
5  Rublle  2 
7  Rockhead  3 
11  Gravel  1 

 Department:

DeptId  Description  Location 
1  Administration  Bedrock 
2  Quarry  Bedrock 
3  Stockpile  Bedrock 

 What is the primary key of  Employee?
 What is the primary key of  Department?
 
Explain why the  Dept attribute in the  Employee table is a
foreign key referring to  DeptId in  Department. 

What does it mean for the relation  Employee to have a foreign key?

Relational algebra

 What is the difference between Equi-Join and Natural-Join?

 Here is the relational database schema we consider.

Student (Id: INT, Name: STRING, Address: STRING, Status: STRING)
 
Professor (Id: INT, Name: STRING, DeptId: DEPTS)
 
Course (DeptId: DEPTS, CrsName: STRING, CrsCode: COURSES)
 
Enrolled (CrsCode: COURSES, StudId: INT, Grade: GRADES, Semester: SEMESTERS)
 
Department(DeptId: DEPTS, Name: STRING)
 
Teaching(ProfId:INTEGER, CrsCode:COURSES, Semester:SEMESTERS)

NB: For example a Semester is of the form F2000 for Fall 2000. 

List the names of all the professors teaching in the history department (HIS DeptId).

List the names of all the professors who taught in Fall 1994 (F1994).

List all the Ids of the professors who taught at least 2 courses in Fall 1999.

Find the names of the courses taught in Fall 1995 together with the names of the professors who taught those courses.

Find the ProfIds of the professors who taught all the courses of the CS department (DeptId CS).

Good work!