Database Management Systems (CIS4301) (Fall 2014) Instructor: Dr. Markus Schneider TA: Babak Alipour Clay Brooks Homework 2 Solutions Name UFID Email Address Pledge (Must be signed according to UF Honor Code) On my honor, I have neither given nor received unauthorized aid in doing this assignment. ______________________________________________Signature For scoring use only: Maximum Received Exercise 1 25 Exercise 2 25 Exercise 3 25 Exercise 4 25 Total 100 Exercise 1 (Knowledge Questions) [25pts] Answer the following questions: 1. Define Participation and Disjoint constraints, including the two variations of each constraint. [5 pts] Participation Constraints – Determines whether every member in the superclass must participate as a member of a subclass. Can be either mandatory or optional. Mandatory indicates the members must participate, optional indicates they may or may not participate as a member of a subclass. Disjoint Constraints – Describes relationship between members of the subclasses and indicates whether member of a superclass can be a member of one or more than one subclass. Can be either disjoint or non‐disjoint. Disjoint states that an instance of a superclass can be a member of only one subclass. Non disjoint rule states that an instance of a superclass can be a member of more than one subclass. 2. Briefly describe primary key, alternate key, and foreign key. Give one simple example of each [5 pts] Primary Key – Candidate key that identifies tuples uniquely within a relation. No two tuples within a relation can have the same primary key. UFID for students at UF would be a primary key. Alternate Key – Candidate keys that are not selected to be primary, but still will have uniqueness within the relation. Email addresses for students at UF would be an alternate key. Foreign Key – A set of attributes within one relation that matches candidate key of some other, or possibly same, relation. For entities Staff(staffNo, fName, lName, position, sex, DOB) and Client(clientNo, fName, lName, telNo, eMail, staffNo), with staffNo being a primary key for Staff, staffNo for client would be labeled as a foreign key that references Staff(staffNo) 3. List the basic operations of Relational Algebra with their name and their correct symbolic notation [5pts] The basic operations are:  (union), (difference), × (Cartesian product),  (projection), and  (selection). Further, we have the  (rename) operation. 4. Describe how to create Intersection and Division operations using only the five basic operations listed above. [5 pts] Intersection – R‐(R‐S) Division – T1 ←Πc(R) T2 ← Πc((S X T1) – R) T ← T1 – T2 5. List the 5 different Types of joins and describe them [5 pts] Theta Join ‐ Defines a relation that contains tuples satisfying the predicate F from the Cartesian product of R and S. Equijoin – An equi‐join is a comparator based‐join that uses only equality in the join predicate. Natural Join ‐ An Equijoin of the two relations R and S over all common attributes x. One occurrence of each common attribute is eliminated from the result. Outer Join – outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation. Semijoin ‐ Defines a relation that contains the tuples of R that participate in the join of R with S. Question 2 (Relational Algebra) [25 Points] Consider the following simplified database model of the University of Florida Department(code: string, name: string) Course(dept_code: string, prefix: string, number: integer, credits: integer, courseLevel: integer) Student(UFID: integer, fName: string, lName: string, email: string) courseRoll(UFID: integer, prefix: string, number: integer) 1. Find all of the 3000 level courses within the CEN department. [5 pts] Πprefix, number(σ(courseLevel = 3000 ∧ dept_code = “CEN”)(Course)) 2. Find prefix and course number all of the courses offered within the CEN department that are 3 credits or higher. [5 pts] Πprefix, number(σ(credits >= 3 ∧ dept_code = “CEN”)Course) 3. Find the UFID’s of students taking a course in the CEN department. [5 pts] ΠUFID(σdept_code = “CEN”(Course Roll ⋈ Course)) 4. Find the full names of students taking a 2000 level course in the CEN department or a 3000 level course in the MAS department. [5 pts] Πfname,lname(σ(courseLevel = 2000 ∧ dept_code = “CEN”) or (courseLevel 3000 ∧ dept_code = “MAS”)(CourseRoll ⋈ Course) ⋈ Student) 5. Find the email address of students with the last name of smith enrolled in a 4 credit 4000 level course in the EEL department or a 3 credit 3000 level course in either the MAS or CEN department. [5 pts] Πemail(σ lname = “smith” ∧( (courseLevel = 4000 ∧ credits = 4 ∧ dept_code = “EEL”) or (credits = 3 ∧ (dept_code = “MAS” or dept_code = “CEN”))(CourseRoll ⋈ Course) ⋈ Student) Exercise 3 (Relational Algebra) [25pts] Flights(flightNumber: string, travelFrom: string, travelTo: string, distance: integer, departs: time) Aircraft(planeId: string, planeName: string, range: integer) Pilots(employeeId: string, planeId: string) Employees(employeeId: string, employeeName: string, salary: integer) 1. Find employeeId’s of pilots who can operate "Boeing's 747" and "Boeing's 777". [2 pts] ΠemployeeId (σ planeName = 'Boeing 747' ^ planeName = 'Boeing 777' (Aircraft ⨝ Pilots)) 2. Find the id of the pilot, or pilots, with the highest salary. [3 pts] ΠemployeeId (Employees) ‐ σ e2.employeeId (σ e1.salary > e2.salary (σ e1 (Employees) x σe2(Employees))) 3. Find airplaneId's of aircraft that cannot fly non‐stop from ATL to JFK. [5 pts] ΠplaneId (σ range < distance (Aircraft x σ travelFrom = ‘ATL’ ^ travelTo = ‘JFK’ (Flights))) 4. Find names of pilots who can operate planes with a range greater than or equal to 1,500 miles, but can not operate "Boeing 747's" and "Boeing 777's". [7 pts] ΠemployeeName (Employees⨝ (σ employeeId (σ range >= 1500 (Aircraft ⨝ Pilots)) ‐ σ employeeId (σ name = 'Boeing 747' ^ 'Boeing 777' (Aircraft ⨝ Pilots))) 5. Find flightNumber of flights that can be piloted by every pilot whose salary is under $100,000. [8 pts]. ΠflightNumber(Flights) ‐ ΠflightNumber ((ΠflightNumber (Flights) x ΠemployeeId (σsalary < 100000 (Employees ⨝ Pilots))) ‐ ΠflightNumber,employeeId (σ range >= distance (Aircraft x Flights) ⨝ Pilots)) or ΠflightNumber(σrange >= distance (Aircraft x Flights) ⨝ σsalary < 100000(Pilots ⨝ Employees)) Exercise 4 (Relational Calculi) [25pts] Consider the following database schema: EMPLOYEE (emp_name, address_street, address_city, phone_no) DEPARTMENT (dept_name, mgr_name, mgr_start_date) WORKS_ON (emp_name, industry_name, salary) INDUSTRY (industry_name, location_city) MANAGES (emp_name, mgr_name) Write the Tuple Relational Calculus expression for problems 1 and 2. Write the Domain Relational Calculus expression for 3, 4, and 5. 1. List the names of all employees who don’t work for ‘Verizon Wireless’ and make more than $50,000 per year. [5 pts] TRC: {s.emp_name | WORKS_ON(s) ^ s.industry_name != ‘Verizon Wireless ^ s.salary > 50000} 2. Find the names and phone number of all employees who work for Intel and live in Santa Clara. [5 pts] TRC: {t.emp_name t.phone_no | (Ǝs) (WORKS_ON(s) ^ (s.emp_name = t.emp_name ^ s.industry_name = ‘Intel’ ^ t.address_city = ‘Santa Clara’} 3. Find the names of all employees who live in the same city and on the same street as their managers. [5 pts] DRC: {<t> | Ǝ s, c, m (<t, s, c> ∈ employee ∧ <t, m> ∈ manages ∧ <m,s,c> ∈ employee)} 4. Find the names of all employees in this database who live in the same city [5 pts] DRC: { <p> | Ǝ st, c, co, sa (<p, st, c> ∈ employee ∧ <p, co, sa> ∈ works ∧ <co, c> ∈ company)}
5. Find all companies located in every city in which Intel is located. [5 pts] DRC: {<C> | ∀ co2, ci2 (<co2, ci2> ∈ company ∧ co2 = Intel' ⇒ <co2, ci2> ∈ company)}