1 / 25

Problem Solving for the Midterm

Problem Solving for the Midterm. name. age. ssn. address. date. qty. prescribes. Patients. price. tradeName. Drugs. name. speciality. formula. ssn. exp_year. stDate. address. name. phone. isContracted. Makes. endDate. Doctors. Pharmacies. name. txt. PharmCo. phone.

wirt
Download Presentation

Problem Solving for the Midterm

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Problem Solving for theMidterm

  2. name age ssn address date qty prescribes Patients price tradeName Drugs name speciality formula ssn exp_year stDate address name phone isContracted Makes endDate Doctors Pharmacies name txt PharmCo phone super Problem 2.7 HasPriPhy Sell

  3. Relational Database Schema Doctors(ssn:integer,name:string, specialty:string,year_exp:integer) PK: ssn Patients(ssn:integer,name:string,age:integer,address:string,ssnDoctor:integer) PK: ssn FK: ssnDoctor references Doctors Pharmacies(name:string, phone:integer, address:string) PK: name PharmCo(name:string, phone:integer) PK: name Drugs(tradeName: string,formula:string,pharmCoName: string) PK: pharmCoName, tradeName FK: pharmCoName references PharmCo Prescribes(ssnPatient:integer, ssnDoctor:integer, pharmCoName: string , tradeName:sring, date:date, qty:integer) PK: FK: ssnPatient references Patients ssnPatient,ssnDoctor,pharCoName,tradeName FK: ssnDoctor references Doctors FK: (pharCoName,tradeName) referencesDrugs Sells(pharmCoName: string, tradeName: string, pharmacyName: string , price:float) PK: pharmCoName,tradeName,pharmacyName FK: (pharmCoName,tradeName) references Drugs FK: pharmacyName references Pharmacies Contracts(pharmCoName: string,pharmacyName: string , startDate:date, endDate:date, text:string, supervisor:string) PK: pharCoName,pharmacyName FK: pharmCoName references PharmCo FK: pharmacyName references Pharmacies

  4. Each faculty is assigned to exactly one office room. An office room is assigned to at least one faculty. Is the following E-R diagram correct? If your answer is NO, indicate what is wrong in this E-R diagram. Faculty Office AssignedTo

  5. Faculty Classes teaches A faculty teaches at most three classes, but a class is taught by exactly one faculty. Is the following E-R diagram correct? If your answer is NO, indicate what is wrong in this E-R diagram.

  6. Each faculty schedules office hours in exactly one office room at a weekday and time, and this information must be recorded. An office may be assigned to several faculties’ office hours at different days and times. All offices are assigned to faculty’s office hours. In the following E-R Diagram, complete the dashed lines to reflect the appropriated participation and key constraints. Time Faculty Office scheduled WeekDay

  7. Problem 4.2 Given two relations R1 and R2, where R1 contains N1 tuples and R2 contains N2 tuples, with N2>N1>0, give the minimum and maximum possible sizes (in tuples) for the resulting relation produced by each of the following relational algebra expressions. In each case, state any assumptions about the schemas for R1 and R2 needed to make the expression meaningful.

  8. Suppose R1 and R2 are multisets of tuples (rows); • N1 = number of copies of a tuple t in R1; • N2 = number of copies of a tuple t in R2. What’s the number of copies oftin R1UNION ALLR2 ?

  9. Suppose R1 and R2 are multisets of tuples (rows); • N1 = number of copies of a tuple t in R1; • N2 = number of copies of a tuple t in R2. What’s the number of copies oftin R1INTERSECT ALLR2 ?

  10. Suppose R1 and R2 are multisets of tuples (rows); • N1 = number of copies of a tuple t in R1; • N2 = number of copies of a tuple t in R2. What’s the number of copies oftin R1EXCEPT ALLR2 ?

  11. Problem 4.4 Consider the following schema: Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) The Catalog relation lists the prices charged for Parts by Suppliers. State what the following queries compute.

  12. Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)

  13. Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)

  14. Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)

  15. Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)

  16. Suppliers(sid:integer, sname:string, address:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real)

  17. (1) Find the ssn of faculty who are currently teaching courses taught by faculty of several departments. (2) Find the ssn of faculty who are not currently teaching any courses of his/her department. (3) Find the ssn of faculty who are currently teaching courses taught only by faculties of his/her department. (4) Find the ssn of faculty who are currently teaching some course of his/her department. (5) Find the ssn of faculty who are currently teaching exactly one course. select F.SSn from Class Co, Faculty F where F.Ssn = Co.Instructor- SSn and not exists (select * from Class C, Faculty F1 where Co.D-code = C.D-code and Co.C-no = C.C-no and C.Instructor-SSn = F1.Ssn and F1.D-code != F.D-Code);

  18. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) • Employees relation describes pilots and other kinds of employees as well; • Every pilot is certified for some aircraft (otherwise, he/she would not qualify as a pilot); • Only pilots are certified to fly. Write the following queries in relational algebra and SQL.

  19. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Find the aids of all aircrafts that can be used on non-stop flights from Bonn to Madrid.

  20. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Identify the flights that can be piloted by each pilot whose salary is more than $100,000.

  21. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Find the names of pilots who can operate planes with a range greater than 3,000 miles but are not certified on any Boeing aircraft.

  22. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Find the eids of employees who make the highest salary (RA only).

  23. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Find the eids of employees who make the second highest salary (RA only).

  24. Flight(flno:integer, from:string, to:string, distance: integer, departs:time, arrives:time) Aircraft(aid:integer, aname:string, cruisingrange:integer) Certified(eid:integer, aid:integer) Employees(eid:integer, ename:string, salary:integer) Find the eids of employees who make the second highest salary (RA only).

More Related