database to reference:

Exercise 3 Write the following queries for University Database in relational algebra (2 pts for each correct solution): a. List the IDs and names of all instructors in the Physics department. b. List the IDs and names of all instructors in all departments located in the Watson building. C. List the IDs and names of all students who took at least one course in the Biology department. d. List the IDs and names of all students who have not taken any section in the year 2018. University Database student ID name dept_name tot_cred 00128 Zhang Comp. Sci. 102 12345 Shankar Comp. Sci. 32 19991 Brandt History 80 23121 Chavez Finance 110 44553 Peltier Physics 56 45678 Levy Physics 46 54321 Williams Comp. Sci. 54 55739 Sanchez Music 38 70557 Snow Physics 76543 Brown Comp. Sci. 58 76653 Aol Elec.Eng. 60 98765 Bourikas Elec.Eng. 98 98988 Tanaka Biology 120 instructor ID name dept_name salary 10101 Srinivasan Comp. Sci. 65000 12121 Wu Finance 90000 15151 Mozart Music 40000 22222 Einstein Physics 95000 32343 El Said History 60000 33456 Gold Physics 87000 45565 Katz Comp. Sci. 75000 58583 Califieri History 62000 76543 Singh Finance 80000 76766 Crick Biology 72000 83821 Brandt Comp. Sci. 920001 98345 Kim Elec. Erg. B0000 department dept name building budget Biology Watson 90000 Comp. Sc. Taylor 100000 Elec.Eng. Taylor 85000 Finance Painter 120000 History Painter 50000 Music Packard 80000 Physics Watson 70000 advisor SID i ID 0012845565 12345 10101 2312176543 44553 22222 45678 22222 76543 45565 76653 98345 9876598345 9898876766 course course_id title dept_name credits BIO-101 Intro to Biology Biology 4 BIO-301 Genetics Biology 4 BIO-399 Computational Biology Biology 3 CS-101 Intro to Computer Science Comp. Sci. 4 CS-190 Game Design Comp. Sci. 4 CS-315 Robotics Comp. Sci. 3 CS-319 Image Processing Comp. Sci. 3 CS-347 Database System Concepts Comp. Sci. 3 EE-181 Intro to Digital Systems Elec.Eng. 3 FIN-201 Investment Banking Finance 3 HIS-351 World History History 3 MU-199 Music Video Production Music 3 PHY-101 Physical Principles Physics 4 prereg course_id prereq_id BIO-301 B10-101 BIO-399 BIO-101 CS-190 CS-101 CS-315 CS-101 CS-319 CS-101 CS-347 CS-101 EE-181 PHY-101 teaches ID course_id sec_id semester year 10101 CS-1011 Fall 2017 10101CS-3151 Spring 2018 10101 CS-3471 Fall 2017 12121 FIN-2011 Spring 2018 15151 MU-1991 Spring 2018 22222 PHY-101 1 Fall 2017 32343 HIS-3511 Spring 2018 45565 CS-101 1 Spring 2018 45565 CS-3191 Spring 2018 76766 BIO-101 1 Summer 2017 76766 BIO-3011 Summer 2018 83821CS-1901 Spring 2017 83821CS-1902 Spring 2017 83821 CS-319 2 Spring 2018 98345 EE-1811 Spring 2017 takes ID course_id sec_id semester year grade 00128 CS-1011 Fall 2017A 00128 CS-3471 Fall 2017A- 12345CS-101 1 Fall 2017 12345 CS-1902 Spring 2017 A 12345CS-315 1 Spring 2018A 12345|CS-3471 Fall 2017 A 19991 HIS-3511 Spring 2018 B 23121 FIN-2011 Spring 2018 C+ 44553 PHY-101 1 Fall 2017 B- 45678 CS-101 1 Fall 2017 45678 CS-101 1 Spring 2018 B+ 45678 CS-3191 Spring 2018 B 54321 CS-1011 Fall 2017A- 54321 CS-1902 Spring 2017 B+ 55739 MU-1991 Spring 2018 A- 76543 CS-101 1 Fall 2017 A 76543 CS-3192 Spring 2018 A 76653 EE-181 1 Spring 2017 98765CS-101 1 Fall 2017 - 98765 CS-3151 Spring 2018 B 98988 BIO-101 1 Summer 2017 A 98988 BIO-3011 Summer 2018 section course_id sec id semester year building room number time slot_id BIO-1011 Summer 2017 Painter 514 B BIO-301 1 Summer 2018 Painter 514 A CS-1011 Fall 2017 Packard 101 H CS-1011 Spring 2018 Packard 101 CS-1901 Spring 2017 Taylor 3128 E CS-1902 Spring 2017 Taylor 3128 CS-3151 Spring 2018 Watson 120 D CS-319 1 Spring 2018 Watson 100 CS-3192 Spring 2018 Taylor 3128 C CS-3471 Fall 2017 Taylor 3128 EE-1811 Spring 2017 Taylor 3128 c FIN-2011 Spring 2018 Packard 101 B HIS-351 1 Spring 2018 Painter 514 MU-1991 Spring 2018 Packard 101 2018 Packard 101 D PHY-101 1 Fall 2017 Watson 100 500 classroom building room_number capacity Packard 101 Painter 514 Taylor 3128 Watson 100 30 Watson 120 L time slot time_slot_id daystart_hr start_min end_hrend_min MB 03 A WSDB FS D B M9D9 w9D F 909 M 11 0 11 W 110 11 F 110 M 13 0 w 13 D F 13 13 T 10 30 R 10 30 T 14 30 R 14 30 15 M 16 0 16 50 w 16 0 16 F 16 0 16 w 1001230 |