askdgjgawukdjsagkdjawgdukasjdhkajwdaksjd.pptx

asjdhwakdsgahkwda

1. FACULTY OF COMPUTER SCIENCE, INFORMATION TECHNOLOGY and Energy Databases ( cont .)

2. Table computing operation sequence

3. Query tree Step 1: Tables needed to query data (all tables which contain needed data) Step 2: Cartesian product Step 3: Selection by corresponding attributes Step 5: Projection Step 4: Selection Depends on query

4. Given tables Courses Course name CID Room Time Credit Points Computer Science Mathematics Mathematics Physics Psychology Psychology Zoology Zoology 518 575 611 544 501 617 335 412 N521 N502 N521 B505 A100 A110 A100 A100 2:00P.M. 3:00P.M. 4:00P.M. 4:00P.M. 3:00P.M. 11:00A.M. 9:00A.M. 8:00A.M. 4 5 7 3 4 4 5 6 Professors Professor Name Office CID Cruz Cruz Farber Farber Grammer Grammer Rosen Rosen K100 K100 K101 K101 K102 K102 K103 K103 335 412 501 617 544 551 518 575 Students Student Name ID Number CID Grade Ackermann Adams Chou Goodfriend Rao Adams 231455 888323 102147 453876 678543 786576 544 501 617 335 518 575 3.88 3.45 3.79 3.45 3.90 2.99

5. Find professors ’ names for courses with more then 4 credit points . Show also course name and course ID 1. Needed tables : Courses , Professors . Courses Course name CID Room Time Credit Points Computer Science Mathematics Mathematics Physics Psychology Psychology Zoology Zoology 518 575 611 544 501 617 335 412 N521 N502 N521 B505 A100 A110 A100 A100 2:00P.M. 3:00P.M. 4:00P.M. 4:00P.M. 3:00P.M. 11:00A.M. 9:00A.M. 8:00A.M. 4 5 7 3 4 4 5 6 Professors Professor Name Office CID Cruz Cruz Farber Farber Grammer Grammer Rosen Rosen K100 K100 K101 K101 K102 K102 K103 K103 335 412 501 617 544 551 518 575

6. 2. T1:=Courses x Professors T1 Courses . Course name Courses . CID Courses . Room Courses . Time Courses . Credit Points Professors . Professor Name Professors . Office Professors . CID Computer Science Computer Science Computer Science Computer Science Computer Science Computer Science Computer Science Computer Science 518 518 518 518 518 518 518 518 N521 N521 N521 N521 N521 N521 N521 N521 2:00P.M. 2:00P.M. 2:00P.M. 2:00P.M. 2:00P.M. 2:00P.M. 2:00P.M. 2:00P.M. 4 4 4 4 4 4 4 4 Cruz Cruz Farber Farber Grammer Grammer Rosen Rosen K100 K100 K101 K101 K102 K102 K103 K103 335 412 501 617 544 551 518 575 Mathematics Mathematics Mathematics Mathematics Mathematics Mathematics Mathematics Mathematics 575 575 575 575 575 575 575 575 N5 2 N5 2 N5 2 N5 2 N5 2 N5 2 N5 2 N5 2 3 :00P.M. 3 :00P.M. 3 :00P.M. 3 :00P.M. 3 :00P.M. 3 :00P.M. 3 :00P.M. 3 :00P.M. 4 4 4 4 4 4 4 4 Cruz Cruz Farber Farber Grammer Grammer Rosen Rosen K100 K100 K101 K101 K102 K102 K103 K103 335 412 501 617 544 551 518 575 … … … … … … … … Totally , 8 x 8 records

7. 3. T2:=Sel Courses.CID=Professors.CID (T1) T2 Courses . Course name Courses . CID Courses . Room Courses . Time Courses . Credit Points Professors . Professor Name Professors . Office Professors . CID Computer Science 518 N521 2:00P.M. 4 Rosen K103 518 Mathematics 575 N502 3:00P.M. 5 Rosen K103 575 Physics 544 B505 4:00P.M. 3 Grammer K102 544 Psychology 501 A100 3:00P.M. 4 Farber K101 501 Psychology 617 A110 11:00A.M. 4 Farber K101 617 Zoology 335 A100 9:00A.M. 5 Cruz K100 335 Zoology 412 A100 8:00A.M. 6 Cruz K100 412

8. 4. T3 := Sel Courses.Credit points >4 (T3) T3 Courses . Course name Courses . CID Courses . Room Courses . Time Courses . Credit Points Professors . Professor Name Professors . Office Professors . CID Mathematics 575 N502 3:00P.M. 5 Rosen K103 575 Zoology 335 A100 9:00A.M. 5 Cruz K100 335 Zoology 412 A100 8:00A.M. 6 Cruz K100 412

9. 5. T4:= P Course s . Course Name , Course s .CID , Professors.Professor Name (T3) T4 Courses . Course name Courses . CID Professors . Professor Name Mathematics 575 Rosen Zoology 335 Cruz Zoology 412 Cruz

10. Find professors ’ names who are giving courses to student Adams in the room A100 1. Needed tables : Courses Professors Students 2. T1:=Courses x Professors x Students 3. T2:=Sel Courses.CID=Professors.CID=Students.CID (T1) 4. T3:=Sel Students. Student Name = Adams & Courses . Room =A100 (T2) 5. T4:= P Professors.Professor Name (T3)

11. Find professors ’ names and course names of courses with grades are less than 3.5 1. Needed tables : Courses Professors Students 2. T1:=Courses x Professors x Students 3. T2:=Sel Courses.CID=Professors.CID=Students.CID (T1) 4. T3:=Sel Grade <3.5 (T2) 5. T4:= P Professor Name , Course Name (T3)

12. Pseudo code operations

13. Uses predicates GET R ( t.attr):C where R - new table t.attr – queried values from current tables C - condition

14. Find professor offices ( If the searched result is in one table ) Profesor Offices Office K100 K101 K102 K103 GET Professor Offices ( Professors.Office )

15. Find course ID wh ich happen s in the room A100 at 9:00 A.M. GET COURSE ( Courses.CID ): Courses.Room = A100 & & Courses.Time=9:00A.M.

16. Find courses (names) given by Rosen As needed information is in two tables then variable x is used x is record ( tuple ) from table Professors GET CN ( Courses.Course name ): x (x Professors & & x. Professor name = Rosen & & x.CID=Courses.CID ) to join If the result is in two tables !

17. Find grades in the course given by Rosen at 2:00 PM GET GR ( Students.Grade ): x Courses & x.Time=2:00 P.M & & y Professors & & y.Professor name = Rosen & & y.CID=x.CID=Students.CID If the result is in three tables !