Task 1: Based on the relational schema diagram provided by the instructor, develop relational-algebra queries for...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Task 1: Based on the relational schema diagram provided by the instructor, develop relational-algebra queries for the following tasks. 1. Find the tutors who are responsible for tutoring the same courses as 'Jim Champ' (Suppose there is only one User called Jim Champ). List their user ids. 2. Find faculty who are also students. List their names. 3. List names of tutors who are hired by College 'CLAS' (ID) and Campus 'iusb' (ID). 4. Count the total number of OnCalls and Appointments that Tutor 9003(ID) hired by Unit 'CS' (ID) of Campus 'iusb' (ID) attended in May 2021. Only status in attended (char value '3') will be counted. PF Campus D PID * Name Phone - Type F Unit Clige ID Unit PK (Campus Unit Campus FK (Campus ID) UnitCollege FK (Unit_Clige_ID) PID * Nama PF Unit Campus ID PF Unit ID Description Address ON City State Unit VARCHAR2 (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2 (50 CHAR) VARCHAR2(15 CHAR) CHAR (1 CHAR) INTEGER ID, ID) Non Academio Unit Country URL Campus_PK (ID) VARCHAR2 (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2 (100 CHAR) VARCHAR2 (20) VARCHAR2 (30 CHAR) Campus VARCHAR2 (50 CHAR) VARCHARZ (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2(10 CHAR) VARCHAR2 (50 CHAR) Non Academic Unit PK (Unit Campus_ID, Unit_ID) Non Academic Unit_Unit_FK (Unit_Campus_ID, Unit_ID) W PF IP IF I PE ID Name Phone Email URL Unit Campus D Unit 10 ID College 10 D PID INTEGER Unit Cige PK (0) College VARCHAR2 (20 CHAR) VARCHAR2(30 CHAR VARCHAR2(15 CHAR) VARCHAR2 (50 CHAR) Campus D Unit Clige ID College PK (ID) College Campus FK (Campus_10) College Unit Clige FK (Unit_Clige_ID) VARCHAR2(50 CHAR) VARCHAR2 (20 CHAR) INTEGER Academic Unit VARCHARZ (20 CHAR) VARCHARZ (20 CHAR) VARCHARZ (20 CHAR Academic Unit PK (Unit_Campus_ID, Unit_10) Academic Unit_Unit FK (Unit Campus_ID, Unit_ID) Academic Unit College FK (College ID) Unit Clie PF Tutor User ID PF Tutor Unit College ID PF Course Code PF Course Number INTEGER INTEGER PF" USER ID P Phone VARCHAR2(15CHAR) Tutor Responsible Courses INTEGER VARCHAR2(15 CHAR User Phone PK (USER_ID, Phone) User Phone User_FK (USER_ID) Supervisor PF* User D INTEGER Office Hour VARCHAR2(40 CHAR) Supervisor PK (User ID) Supervisor Faculty_FK (User ID) User Phone PF" User ID F PF Unit College ID Pay Rate Supervisor ID Tutor PK (User ID, Unit College_ID) Tutor Unit Clige FK (Unit Collage Tutor Supervisor FK (Supervisor_ID) Tutor User FK (UD) INTEGER INTEGER NUMBER INTEGER P Code P-Number - Tie F Unit Cige D INTEGER Tutor Responsible Courses PK (Tutor User ID, Tutor Unit College ID, Course Code, Course Number) TRC Course FK (Course Code Course Number) TRC Tutor FK (Tutor User ID, Tutor Unit College ID) Course VARCHAR2(15 CHAR) INTEGER VARCHAR2(20 CHAR) INTEGER Course PK (Code Number) Course Unit Clige FK (Unit_Clige ID) 75 Tutor D First Name Last Name Email User PK (D) F F ++ PF User ID Rank User INTEGER VARCHARZ (20 CHAR) VARCHAR2(30 CHAR) VARCHAR2(50 CHAR) Start Time End Time task type Status Course Code Course Number Faculty PK (User_ID) Faculty User FK (User ID) Tutor User Tutor Unt College ID Faculty User ID Location *OnCalDate Faculty INTEGER VARCHAR2 (30 CHAR) OnCall INTEGER INTEGER INTEGER INTEGER VARCHAR2(100 CHAR) DATE TIMESTAMP TIMESTAMP CHAR (1 CHAR) CHAR (1 CHAR) VARCHAR2(15 CHAR) INTEGER PF App ID PF Course Code PF Course Number PF" User ID * Grad Flag +H OnCal PK (D) OnCall Faculty FK (Faculty User ID) OnCall Course FK (Course Code, Course Number) OnCall Tutor FK (Tutor User ID, Tutor Unit College ID) PID F F F Student PK (User ID) Student User FK (User ID) Student INTEGER CHAR (1 CHAR) Location AppDate Start Time End Time Tutor User ID Tutor Unit College ID Student User ID Appt Appt For Course INTEGER VARCHAR2 (15 CHAR) INTEGER INTEGER VARCHAR2 (30 CHAR) DATE TIMESTAMP TIMESTAMP CHAR(1 CHAR) INTEGER INTEGER INTEGER Appt PK (ID) Appt Student FK (Student User ID) Appt Tutor FK (Tutor_User_ID, Tutor_Unit College_ID) Appt For Course PK (Appt ID, Course Code, Course Number) Appt For Course Appt FK (Appt_ID) Appt For Course Course FK (Course_Code, Course Number) Task 1: Based on the relational schema diagram provided by the instructor, develop relational-algebra queries for the following tasks. 1. Find the tutors who are responsible for tutoring the same courses as 'Jim Champ' (Suppose there is only one User called Jim Champ). List their user ids. 2. Find faculty who are also students. List their names. 3. List names of tutors who are hired by College 'CLAS' (ID) and Campus 'iusb' (ID). 4. Count the total number of OnCalls and Appointments that Tutor 9003(ID) hired by Unit 'CS' (ID) of Campus 'iusb' (ID) attended in May 2021. Only status in attended (char value '3') will be counted. PF Campus D PID * Name Phone - Type F Unit Clige ID Unit PK (Campus Unit Campus FK (Campus ID) UnitCollege FK (Unit_Clige_ID) PID * Nama PF Unit Campus ID PF Unit ID Description Address ON City State Unit VARCHAR2 (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2 (50 CHAR) VARCHAR2(15 CHAR) CHAR (1 CHAR) INTEGER ID, ID) Non Academio Unit Country URL Campus_PK (ID) VARCHAR2 (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2 (100 CHAR) VARCHAR2 (20) VARCHAR2 (30 CHAR) Campus VARCHAR2 (50 CHAR) VARCHARZ (20 CHAR) VARCHAR2 (20 CHAR) VARCHAR2(10 CHAR) VARCHAR2 (50 CHAR) Non Academic Unit PK (Unit Campus_ID, Unit_ID) Non Academic Unit_Unit_FK (Unit_Campus_ID, Unit_ID) W PF IP IF I PE ID Name Phone Email URL Unit Campus D Unit 10 ID College 10 D PID INTEGER Unit Cige PK (0) College VARCHAR2 (20 CHAR) VARCHAR2(30 CHAR VARCHAR2(15 CHAR) VARCHAR2 (50 CHAR) Campus D Unit Clige ID College PK (ID) College Campus FK (Campus_10) College Unit Clige FK (Unit_Clige_ID) VARCHAR2(50 CHAR) VARCHAR2 (20 CHAR) INTEGER Academic Unit VARCHARZ (20 CHAR) VARCHARZ (20 CHAR) VARCHARZ (20 CHAR Academic Unit PK (Unit_Campus_ID, Unit_10) Academic Unit_Unit FK (Unit Campus_ID, Unit_ID) Academic Unit College FK (College ID) Unit Clie PF Tutor User ID PF Tutor Unit College ID PF Course Code PF Course Number INTEGER INTEGER PF" USER ID P Phone VARCHAR2(15CHAR) Tutor Responsible Courses INTEGER VARCHAR2(15 CHAR User Phone PK (USER_ID, Phone) User Phone User_FK (USER_ID) Supervisor PF* User D INTEGER Office Hour VARCHAR2(40 CHAR) Supervisor PK (User ID) Supervisor Faculty_FK (User ID) User Phone PF" User ID F PF Unit College ID Pay Rate Supervisor ID Tutor PK (User ID, Unit College_ID) Tutor Unit Clige FK (Unit Collage Tutor Supervisor FK (Supervisor_ID) Tutor User FK (UD) INTEGER INTEGER NUMBER INTEGER P Code P-Number - Tie F Unit Cige D INTEGER Tutor Responsible Courses PK (Tutor User ID, Tutor Unit College ID, Course Code, Course Number) TRC Course FK (Course Code Course Number) TRC Tutor FK (Tutor User ID, Tutor Unit College ID) Course VARCHAR2(15 CHAR) INTEGER VARCHAR2(20 CHAR) INTEGER Course PK (Code Number) Course Unit Clige FK (Unit_Clige ID) 75 Tutor D First Name Last Name Email User PK (D) F F ++ PF User ID Rank User INTEGER VARCHARZ (20 CHAR) VARCHAR2(30 CHAR) VARCHAR2(50 CHAR) Start Time End Time task type Status Course Code Course Number Faculty PK (User_ID) Faculty User FK (User ID) Tutor User Tutor Unt College ID Faculty User ID Location *OnCalDate Faculty INTEGER VARCHAR2 (30 CHAR) OnCall INTEGER INTEGER INTEGER INTEGER VARCHAR2(100 CHAR) DATE TIMESTAMP TIMESTAMP CHAR (1 CHAR) CHAR (1 CHAR) VARCHAR2(15 CHAR) INTEGER PF App ID PF Course Code PF Course Number PF" User ID * Grad Flag +H OnCal PK (D) OnCall Faculty FK (Faculty User ID) OnCall Course FK (Course Code, Course Number) OnCall Tutor FK (Tutor User ID, Tutor Unit College ID) PID F F F Student PK (User ID) Student User FK (User ID) Student INTEGER CHAR (1 CHAR) Location AppDate Start Time End Time Tutor User ID Tutor Unit College ID Student User ID Appt Appt For Course INTEGER VARCHAR2 (15 CHAR) INTEGER INTEGER VARCHAR2 (30 CHAR) DATE TIMESTAMP TIMESTAMP CHAR(1 CHAR) INTEGER INTEGER INTEGER Appt PK (ID) Appt Student FK (Student User ID) Appt Tutor FK (Tutor_User_ID, Tutor_Unit College_ID) Appt For Course PK (Appt ID, Course Code, Course Number) Appt For Course Appt FK (Appt_ID) Appt For Course Course FK (Course_Code, Course Number)
Expert Answer:
Related Book For
Quantitative Analysis for Management
ISBN: 978-0132149112
11th Edition
Authors: Barry render, Ralph m. stair, Michael e. Hanna
Posted Date:
Students also viewed these databases questions
-
QUIZ... Let D be a poset and let f : D D be a monotone function. (i) Give the definition of the least pre-fixed point, fix (f), of f. Show that fix (f) is a fixed point of f. [5 marks] (ii) Show that...
-
For monotone functions f, f0: P Q between posets (P, vP ) and (Q, vQ), let f v f(i) Prove that the binary relation v is a partial order. [3 marks] (ii) For monotone functions between posets p : P 0...
-
Bonita Company's income statement contained the following condensed information. BONITA COMPANY Income Statement For the Year Ended December 31, 2022 Service revenue Operating expenses, excluding...
-
Divide. Round the quotients as indicated 1. Divide: 0.0453, 0.98. Round the quotient to the nearest thousandth. 2. Divide: 98.83, 3.5. Round the quotient to the nearest tenth.
-
Energy consumption around the world is rising. Find some figures to describe this growth. Now forecast the future demand for electricity. How accurate are your results? What other factors should be...
-
Veronicas expense report was reviewed by her company, and she was accused of taking her best friend on a business conference to Paris and charging all expenses for both of them on a company credit...
-
The Johnny Ho Manufacturing Company in Columbus, Ohio, is putting out four new electronic components. Each of Hos four plants has the capacity to add one more product to its current line of...
-
One key feature of Sage 50 Accounting software, it implements user access controls to restrict access to sensitive financial data. Maintain the security of financial information with password...
-
The coefficient of static friction u, between the 130-lb body and the 16 wedge is 0.21. Determine the magnitude of the force P required to begin raising the 130-lb body if (a) rollers of negligible...
-
How would you swap two numbers without using a third variable? Explain
-
Description Bring it all together! 1. Make a connection between the False Memory and Cannabis research and your own life. 2. Make a connection between Loftus's False Memory research and your own...
-
We can even write numbers based on these systems of casino chips. Say that abcReno means you have a red chip, b blue chips, and c white chips. (For example, 123Reno means 1 red chip worth 25, 2 blue...
-
If you flip a coin 10 times, what is the probability that you would get 5 heads?
-
Design your implementation of the linked list. You can choose to use a singly or doubly linked list. A node in a singly linked list should have two attributes: val and next. val is the value of the...
-
The accompanying supply and demand graph represents a hypothetical market for spaghetti pasta. Demonstrate how an increase in the price of penne, a different type of pasta, and a decrease in the...
-
a. What is the cost of borrowing if Amarjit borrows $28 500 and repays it over a four-year period? b. How many shares of each stock would he get if he used the $28 500 and invested equally in all...
-
When using a control chart, what are some patterns that would indicate that the process is out of control?
-
Bob Bell's fortieth birthday party promised to be the social event of the year in Cookeville. To prepare, Bob stocked up on the following liquors: LIQUOR AMOUNT ON HAND (OUNCES) Bourbon...
-
The oasis outpost of Abu Ilan, in the heart of the Negev desert, has a population of 20 Bedouin tribesmen and 20 Farima tribesmen. El Kamin, a nearby oasis, has a population of 32 Bedouins and 8...
-
Do any problems arise when translating physical assets and economic events into monetary units? Give one or two illustrations to support your answer.
-
Entries for the Warren Clinic 2015 income statement are listed below in alphabetical order. Reorder the data in proper format. Depreciation expense General/administrative expenses Interest expense $...
-
Great Forks Hospital reported net income for 2015 of \($2.4\) million on total revenues of \($30\) million. Depreciation expense totaled \($1\) million. a. What were total expenses for 2015? b. What...
Study smarter with the SolutionInn App