Write the definitions of the following terms 5 points for ea

Write the definitions of the following terms. (5 points for each definition) Super key Candidate key Nature join (provide the symbol for this operation first and then definition) Theta join (provide the symbol for this operation first and then definition) Semi-join (provide the symbol for this operation first and then definition) With given the database, Company, please answer to the following questions. Employee (empNo, fName, IName, address, DOB, sex, position, deptNo) Department (deotNo, deptName, mgrEmpNo) Project (proiNo, projName, deptNo) WorksOn (empNo, proiNo, dateWorked, hoursWorked) (a) Produce a list of the names of all employees who work on the SCCS project. Relational Algebra Relational Calculus SQL (b) Produce a list of the names and addresses of all employees who work for department. Relational Algebra Relational Calculus SQL

Solution

1) Super Key: This is acombination of attributes that uniquely finds the tuples of a relational schema.

Candidate Key: This is an attribute or set of attributes that uniquely identifies tuples and meets all the requirements of primary key.

Lets consider a Relation R ( A,B,C,D,E)
ABCDE Attribute \'A\' uniquely identifies the other attributes B,C,D,E.
BCADE Attributes \'BC\' combinely identifies all the other attributes A,D,E
Candidate Keys :A, BC
Super Keys : A,BC,ABC,AD

ABC,AD are not Candidate Keys since both are not minimal super keys.

Natural Join (): If 2 tables are their 1-> CourseID, Course, Dept 2-> Dept, HOD

12 gives CourseID, Course, Dept, HOD

Theta Join (_): If 2 tables 1-> student table-> SID, Name, Std, 2-> Subjects table-> class, subject.

student _{Student.Std = Subject.Class} subjects gives SID, Name,Std,Class,subject.

Semi Join Here first we take the natural join of two relations then we project the attributes of first tabke only.

2) Project _fname,Iname(E)(Employee Select _{projName=SCCS} Employee Project)

{<fname,lname>|<fanme,lname>?Employee^deptno (Project)^ projectName=SCCS}

Select fname,lname

From Employee e and Project p

Where e.deptno=p.deptno and p.projtName=SCCS.

3) As Department name is not given, lets take it as xxxx

Project _{fname,Iname,address}(E)(Employee Select dept_Name=xxxx Employee Department)

{<fname,lname,address>|<fanme,lname,address>?Employee^deptno (Department)^ deptName=xxxx}

Select fname,lname,address

From Employee e and Department d

Where e.deptno=d.deptno and d.deptName=xxx.