Week 5

Post date: Feb 02, 2019 6:33:35 PM

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

Relational database design using ER-to-Relational mapping

Rules with Cardinality Ratios:

• Many to many: A new table is added with the primary key being the combination of all foreign keys from each relation/table.

• 1 to many: A new column is going to be added in the relation/table "from the side of the many". It will be the foreign key from the relation/table from the "side of the 1".

• 1 to 1: More analysis is required (see the slides and/or https://db.grussell.org/section006.html#_Toc67114425).

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.

• 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'.