exam2sol - Database Management Systems (COP 5725) (Spring...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Database Management Systems (COP 5725) (Spring 2010) Instructor: Dr. Markus Schneider TA: Hechen Liu, Ravi Tiwari, Wenjie Yuan Exam 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: Question 1 Question 2 Question 3 Question 4 Total Maximum 16 24 18 42 100 Received 1 Question 1 (Tuple Relational Calculus) [16 points] 1. An expression of the Tuple Relational Calculus has the form {t | P(t)}, in which P is a predicate that is constructed from atomic components. Please list all kinds of possible atomic components. [6 points] Solution: − s ∈R. s tuple variable, R relation name − s.A θ t.B. s and t tuple variables, A and B attribute names, θ comparison operators, θ ∈ {=, ≠, <, ≤, >, ≥}, matching domains for s.A and t.B − s.A θ c. meaning of s and A like above, c is constant with c ∈ dom(s.A) 2. Let the following relation schemas be given: [10 points] R = (A, B, C) S = (D, E, F) Let the relations r(R) and s(S) be given. Give an expression in the Tuple Relational Calculus that is equivalent to each of the following Relational Algebra expressions: a. b. Solution: a. { t | q r (q[A] = t[A]) } b. { t | p r q s (t[A] = p[A] t[B] = p[B] = q[D] t[E] = q[E] t[F] = q[F])} t[C] = p[C] t[D] Question 2 (QBE) [24 points] Consider the following relational schemas of the COMPANY database: EMPLOYEE (SSN, Name, Bdate, Address, Salary, DeptId) DEPARTMENT (DeptId, Dname, Office, Mng-SSN) PROJECT (Code, Name, Budget, DeptId) JOIN (ESSN, PCode, StartDate) EMP-DEPENDENT (ESSN, Dependent-Name, Bdate, Relationship) (1) Find the social security numbers of employees who have joined project number 3 or project number 4. [3 points] 2 JOIN ESSN PCode StartDate P._x P._y 3 4 (2) Find the social security numbers of employees who take part in both project number 3 and project number 4. [3 points] JOIN ESSN PCode StartDate P._x _x 3 4 (3) Find the names of the employees who do not join any project. [4 points] EMPLOYEE SSN Name Bdate Address Salary DeptId _y P. JOIN ESSN PCode StartDate ¬ _y (4) Find the employee’s name, dependent’s name and their relationship for all the employees in department number 1. Show the result in a user-defined relation. [5 points] EMPLOYEE SSN Name Bdate Address Salary DeptId _x _y _x P. _y _z _z _t 1 ESSN Dependent-Name Bdate Relationship _t EMP-DEPENDENT RESULTS Name Dependent-Name Relationship (5) List SSN, Name, Bdate of all employees in department number 1. Order the list first by the values of the attribute Name in ascending order then by the values of the attribute Bdate in descending order. [4 points] EMPLOYEE SSN Name Bdate Address Salary DeptId P._x P.AO(1)._y P.DO(2)._z 1 (6) Find the names of the employees who join every project. [5 points] EMPLOYEE SSN Name Bdate Address Salary DeptId _y P. 3 JOIN ESSN PCode G._y CNT.UNQ._z _x CONDITIONS StartDate PROJECT Code Name Budget DeptId CNT.UNQ._z = CNT.UNQ._x Question 3 (SQL) [18 Points] Consider the following relational schemas: BOOK (BookId, Title, Publishername) BOOK_AUTHORS (BookId, AuthorName) PUBLISHER (Name, Address, Phone) LIBRARY_BRANCH (BranchId, BranchName, Address) BORROWER (CardNo, Name, Address, Phone) BOOK_COPIES (BookId, BranchId, No_Of_Copies) BOOK_LOANS (BookId, BranchId, CardNo, DateOut, DueDate) (a) Create a unique index for BookId in Book table. [4 points] create unique index Index_Book_BookID on Book (BookId) (b) Create a view named Copy_Branch that lists the No_Of_Copies of the book titled The Lost Tribe owned by each library branch. [4 points] Create view Copy_Branch as select BranchName, No_Of_Copies from ((BOOK natural join BOOK_COPIES) natural join LIBRARY_BRANCH) where Title=’The Lost Tribe’; (c) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. [5 points] Select B.Name, B.Address, COUNT(*) from BORROWER B, BOOK_LOANS L where B.CardNo=L.CardNo group by B.CardNo, B.Name, B.Address having COUNT(*)>5 (d) Retrieve the names of all borrowers who do not have any books checked out. [5 points] Select Name From BORROWER B where CardNo not in (select CardNo from Book_Loans); or 4 select Name from BORROWER B where Not Exists (select * from BOOK_LOANS L where B.CardNo=L.CardNo) Question 4 (Relational Algebra, Relational calculus and SQL) [42 Points] Consider the following relational schemas: employee(person_name, street,city) company(company_name, city) works(person_name, company_name, salary) manages(person_name, manager_name) Write the following queries in relational algebra, domain relational calculus and SQL Q1: Find the names of employees who work for some company in Boston. [6 points] Π " " | , , , , "Bonston" select distinct w.person_name from works w, company c where w.company_name=c.company_name and c.city=”Boston” Q2: Find the streets of employees who work for all companies in Boston. [12 points] solution: Π Π Π | , , , , select from where and , , , Boston , " " distinct e.street employee e, works w e.person_name=w.person_name not exists (( select company_name from company 5 where city=”Boston”) except (select company_name from works w1 where w1.person_name=w.person_name)) Q3: Find the names of all managers who earn more than $10,000. [6 points] ∏ ∏ | , , , 10000 , select distinct w.person_name from works w, manages m where w.person_name=m.manager_name and w.salary>10000 Q4: Find the names and cities of employees who work for Company A and earn more than $10,000. [6 points] ∏ , _ "" , | , , , , , "A" 10000 select person_name,city from employee where person_name in (select person_name from works where salary>10000 and company_name=”A”) Q5: Find the names of managers who manage all employees working for Company A. [12 points] ∏ ∏ , "A" | , , , , "A" , select m.manager_name from manages m where not exists((select person_name from works where company_name=”A”) except (select m2.person_name from manages m2 where m.manager_name=m2.manager_name)) 6 ...
View Full Document

Ask a homework question - tutors are online