1 / 24

Chapter 4

Chapter 4. Practice Problem Solutions. A list (Bno, Street, Area,Tel_No, Fax_No) of branches located in Bay Area Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno)

hyatt-dotson
Download Presentation

Chapter 4

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. Chapter 4 Practice Problem Solutions

  2. A list (Bno, Street, Area,Tel_No, Fax_No) of branches located in Bay Area • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  3. A list (Bno, Street, Area,Tel_No, Fax_No) of branches located in Bay Area • Select Branch where Area = ‘Bay Area’ giving T1 • Project T1 over (Bno, Street, Area,Tel_No, Fax_NO) giving T2

  4. A list (Pno, Street, Area, City) of properties for rent with 4 bedrooms or less than $1,000 per month rent • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  5. A list (Pno, Street, Area, City) of properties for rent with 4 bedrooms or less than $1,000 per month rent • Select Property_for_Rent where Rooms = 4 or Rent < 1000 giving T1 • Project T1 over (Pno, Street, Area, City) giving T2

  6. A list (Sno, FName, LName) of female mangers with salary between one to two million dollars • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  7. A list (Sno, FName, LName) of female mangers with salary between one to two million dollars • Select Staff where Sex = ‘F’ giving T1 • Select T1 where Salary >= 1M and Salary <= 2M giving T2 • Select T2 where Position = ‘manager’ giving T3 • Project T3 over (Sno, FName, LName) giving T4

  8. A list (Pno, Street, Area, City) of properties for rent located in SF, LA, NY, or DC • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  9. A list (Pno, Street, Area, City) of properties for rent located in SF, LA, NY, or DC • Project Property_for_Rent over (Pno, Street, Area, City) giving T1 • Select T1 where City = ‘SF’ and City = ‘LA’ and City = ‘NY’ and City = ‘DC’ giving T2

  10. A list (Pno, Street, Area, City) of properties for rent located in SF, LA, NY, or DC • Project Property_for_Rent over (Pno, Street, Area, City) giving T1 • Select T1 where City = ‘SF’ giving T2 • Select T1 where City = ‘LA’ giving T3 • Select T1 where City = ‘NY’ giving T4 • Select T1 where City = ‘DC’ giving T5 • T2 Union T3 giving T6 • T6 Union T4 giving T7 • T7 Union T5 giving T8

  11. A list (Pno, Street, Area, City) of properties for rent managed by John Dow and owned by Sue Lee • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  12. A list (Pno, Street, Area, City) of properties for rent managed by John Dow and owned by Sue Lee • Property_for_Rent join Owner where Property_for_Rent.Ono = Owner.Ono giving T1 • T1 join Staff where T1.Sno = Staff.Sno giving T2 • Select T2 where Staff.Fname = ‘John’ and Staff.Lname = ‘Dow’ giving T3 • Select T3 where Owner.Fname = ‘Sue’ and Owner.Lname = ‘Lee’ giving T4 • Project T4 over (Pno, Street, Area, City) giving T8

  13. A list (Sno, FName, LName) of staffs who do not supervise any property for rent • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  14. A list (Sno, FName, LName) of staffs who do not supervise any property for rent • Staff leftouterjoin Property_for_Rent where Staff.Sno = Property_for_Rent.Sno giving T1 • Select T1 where Pno = null giving T2 • Project T2 over (Sno, FName, LName) giving T3

  15. A list (Sno, FName, LName) of staffs who do not supervise any property for rent • Property_for_Rent rightouterjoin Staff where Staff.Sno = Property_for_Rent.Sno giving T1 • Select T1 where Property_for_Rent.Pno = null giving T2 • Project T2 over (Sno, FName, LName) giving T3

  16. A list (Sno, FName, LName) of staffs who do not supervise any property for rent • Property_for_Rent fullouterjoin Staff where Staff.Sno = Property_for_Rent.Sno giving T1 • Select T1 where Property_for_Rent.Pno = null giving T2 • Project T2 over (Sno, FName, LName) giving T3

  17. A list (Rno, Fname, Lname) of renters who have not looked any property for rent • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  18. A list (Rno, Fname, Lname) of renters who have not looked any property for rent • Renter leftouterjoin Viewing where Renter.Rno = Viewing.Rno giving T1 • Select T1 where Pno = null giving T2 • Project T2 over (Rno, Fname, Lname) giving T3

  19. A list (Rno, Fname, Lname) of renters who have not looked any property for rent • Viewing rightouterjoin Renter where Renter.Rno = Viewing.Rno giving T1 • Select T1 where Pno = null giving T2 • Project T2 over (Rno, Fname, Lname) giving T3

  20. A list (Rno, Fname, Lname) of renters who have not looked any property for rent • Viewing fullouterjoin Renter where Renter.Rno = Viewing.Rno giving T1 • Select T1 where Pno = null giving T2 • Project T2 over (Rno, Fname, Lname) giving T3

  21. A staff list (Sno, FName, LName) of staffs who supervise a property for rent • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  22. A staff list (Sno, FName, LName) of staffs who supervise a property for rent • Staff join Property_for_Rent where Staff.Sno = Property_for_Rent.Sno giving T1 • Project T1 over (Sno, FName, LName) giving T2

  23. A renter viewing list (Rno, Fname, Lname) of renters who have looked a property for rent • Branch (Bno, Street, Area, City, Pcode, Tel_No, Fax_No) • Staff (Sno, FName, LName, Position, Sex, DOB, Salary, Bno) • Property_for_Rent (Pno, Street, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, FName, LName, Pref_Type, Max_Rent) • Owner ( Ono, FName, LName) • Viewing (Rno, Pno, Date, Comment)

  24. A renter viewing list (Rno, Fname, Lname) of renters who have looked a property for rent • Renter join Viewing where Renter.Rno = Viewing.Rno giving T1 • Project T1 over (Rno, Fname, Lname) giving T2

More Related