Week 5

Post date: Sep 29, 2018 5:59:26 PM

Chapter 9: Relational Database Design by ER-EER-to-Relational Mapping

Relational database design using ER-to-Relational mapping

Chapter 6: The Relational Algebra and Relational Calculus

Unary relational operations: SELECT and PROJECT;

Relational algebra operations from set theory

In-Class Exercises:

PART 1:

Ex 1:

Write the resulting relations based on the ER diagram of Week4-Ex1.

Example of a Solution:

• Person(driver-id, address, name)

• Car(license, model, year, #driver-id)

• Accident(report-number, location, date)

• Participated(#driver-id, #license, #report-number, damage-amount)

Ex 2:

Same as the previous exercise but with Week4-Ex2.

Solution:

• Patient(SSN, name)

• Doctor(DSSN, name, specialization)

• Test(Test-id, test-name, #DSSN)

• Examination(#SSN, #DSSN, date-admitted, date-checked-out)

• Test-log(#SSN, #Test-id, date, time, result)

Ex 3:

Same as the previous exercise but with Week4-Ex3.

Solution:

• Student(StudentNum, SSN, Class, Sex, Dirthdate, FN, MN, LN, StreetAddress, City, State, Zip, CPhone, CAddress, PPhone, DegreeProgram, #DCode_Major, #DCode_Minor)

• Department(DCode, Dname, College, DOffice, DPhone)

• Course(CNum, CName, CDescription, SemesterHours, Level, #DCode)

• Section(#CNum, SectionNum, Semester, Year, Instructor)

• Grade(NumericGrade, LetterGrade)

• Stud-Sect(#StudentNum, #CNum, #SectionNum, #Semester, #Year, #NumericGrade)

PART 2:

Exercises: Using the above figures, answer the following questions using relational algebra:

• 1. List the names of all male employees.

  2. List the Last name and Salary of all employees working in the department number 4.

  3. List the name of the projects associated to the department number 5.

  4. List the name of all sons of employees but only show the sons born in 1980.

  5. List the name, birth-date, and salary of the manager of the company.

  6. List the first name of all employees working in the Administration department.

  7. List the SSN, first name, and last name of all employees working in Houston.

  8. Retrieve the names of employees in department 5 who work more than 10 hours on the 'ProductX' project.

  9. List the names of employees who have a dependent with the same first name as themselves.

  10. Find the names of employees that are directly supervised by 'Franklin Wong'.

Solution:

1. ∏Fname, Minit, Lname (σSex = "M" (EMPLOYEE))

2. ∏Lname, Salary (σDno = 4 (EMPLOYEE))

3. ∏Pname (σDnum = 5 (PROJECT))

4. ∏Dependent_name (σ((Bdate >= "1980-01-01" AND Bdate <= "1980-12-31") AND Relationship = "Son")(DEPENDENT))

5. Or

6. ∏Dependent_name (σ((Bdate BETWEEN "1980-01-01" AND "1980-12-31") AND Relationship = "Son")(DEPENDENT))

7. ∏Fname, Minit, Lname, Bdate, Salary (σ(Super_ssn = NULL)(EMPLOYEE))

8. ∏Fname (σDname = "Administration" (EMPLOYEE ⨝Dno=Dnumber DEPARTMENT))

9. ∏SSN, Fname, Lname (σDlocation = "Houston" (EMPLOYEE ⨝Dno=Dnumber DEPT-LOCATIONS))

10. ∏Fname, Minit, Lname (σDno = 5 AND Hours > 10 AND Pname = "ProductX"(EMPLOYEE ⨝SSN=Essn WORKS-ON ⨝Pno=Pnumber PROJECT))

11. Another way to write it:

12. DATA ← EMPLOYEE ⨝SSN=Essn WORKS-ON ⨝Pno=Pnumber PROJECT

13. CONDITION ← σDno = 5 AND Hours > 10 AND Pname = "ProductX"(DATA)

14. ∏Fname, Minit, Lname (CONDITION)

15. ∏Fname, Minit, Lname (σFname = Dependent_name (EMPLOYEE ⨝SSN=Essn DEPENDENT))

16. ∏E.Fname, E.Minit, E.Lname(σ(S.Fname = "Franklin" AND S.Lname= "Wong")(EMPLOYEE E ⨝Super_ssn=SSN EMPLOYEE S))