Write the definitions of the following terms. (5 points for each definition) Sup
ID: 3923663 • Letter: W
Question
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 SQLExplanation / Answer
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 deptName=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.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.