Embedded Files
Post date: Oct 23, 2016 6:10:5 AM
Assignment 1 is due for next Sunday (see BlackBoard)
Chapter 3: The Relational Data Model and Relational Database Constraints
Relational model concepts
Relational model constraints and relational database schemas
Update operations, transactions, and dealing with constraint violations
Exercises: Using relational algebra and then SQL, answer the following questions:
List the names of all male employees.
List the Last name and Salary of all employees working in the department number 4.
List the name of the projects associated to the department number 5.
List the name of all sons of employees that are born in 1980.
List the name, birth-date, and salary of the manager of the company.
List the first name of all employees working in the Administration department.
List the SSN, first name, and last name of all employees working in Houston.
Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
List the names of employees who have a dependent with the same first name as themselves.
Find the names of employees that are directly supervised by 'Franklin Wong'.
Solutions:
∏Fname, Minit, Lname ( σSex="M" (Employee) )
∏Lname, Salary ( σDno=4 (Employee) )
∏Pname ( σDnum=5 (Project) )
∏Dependent_name ( σRelationship="Son" AND (Bdate>="1980-01-01" AND Bdate<="1980-12-31") (Dependent) )
∏Fname, Minit, Lname, Bdate, Salary ( σSuper_ssn=NULL (Employee) )
A ← Department ⨝Dnumber=Dno Employee
∏Fname ( σDname="Administration" (A) )
∏Ssn, Fname, Lname ( σPlocation="Houston" ( Employee ⨝Ssn=Essn Works_on ⨝Pno=Pnumber Project) )
∏Fname, Minit, Lname ( σDno = 5 AND Hours > 10 AND Pname = "ProductX" (Employee ⨝Ssn=Essn Works_on ⨝Pno=Pnumber Project) )
∏Fname, Minit, Lname ( σFname=Dependent_name (Employee ⨝Ssn=Essn Dependent) )
∏E1.Fname, E1.Minit, E1.Lname ( σE2.Fname="Franklin" AND E2.Lname = "Wong" (Employee E1 ⨝Ssn=Super_ssn Employee E2) )
SELECT Fname, Minit, Lname
FROM Employee
WHERE Sex = "M";
SELECT Lname, Salary
FROM Employee
WHERE Dno = 4;
SELECT Pname
FROM Project
WHERE Dnum = 5;
SELECT Dependent_name
FROM Dependent
WHERE Relation = "Son" AND Bdate BETWEEN "1980-01-01" AND "1980-12-31";
or
SELECT ... WHERE Relation = "Son" AND Bdate >= "1980-01-01" AND Bdate <= "1980-12-31";
or
SELECT ... WHERE Relation = "Son" AND Year(Bdate) = 1980;
SELECT Fname, Minit, Lname, Bdate, Salary
FROM Employee
WHERE Super_ssn IS NULL;
SELECT Fname
FROM Department INNER JOIN Employee ON Dnumber = Dno
WHERE Dname = "Administration";
or
SELECT Fname
FROM Department, Employee
WHERE Dnumber = Dno AND Dname = "Administration";
SELECT Ssn, Fname, Lname
FROM Employee, Works_on, Project
WHERE Ssn = Essn AND Pno = Pnumber AND Plocation = "Houston";
SELECT Fname, Minit, Lname
FROM Employee INNER JOIN Works_on ON Ssn = Essn INNER JOIN Project ON Pno = Pnumber
WHERE Dno = 5 AND Hours > 10 AND Pname = "ProductX";
SELECT Fname, Minit Lname
FROM Employee INNER JOIN Dependent ON Ssn = Essn
WHERE Fname = Dependent_name;
SELECT E1.Fname, E1.Minit, E1.Lname
FROM Employee E1 INNER JOIN Employee E2 ON E1.Ssn = E2.Super_ssn
WHERE E2.Fname = "Franklin" AND E2.Lname = "Wong";
Page updated
Google Sites
Report abuse