1. Consider the following database. Note that it distinguishes between a course and the class of...
1. Consider the following database. Note that it distinguishes
between a course and the class of that course in a particular year.
Courses can have prerequisites (aka a prereq), which is again a
course.
Person(UPI, givenname, surname) with key UPI
Courses(course, title, program) with key course
Classes(classnr, course, year) with key classnr
Enrollments(UPI, classnr)
Grades(UPI, classnr, grade)
Prereqs(course, prereq)
Teachers(UPI, classnr)
With natural foreign key constraints given by common attribute names.
1. List all UPIs of all teachers who teach a course with a prerequisite.
2. List all enrollments (i.e., list UPI and classnr) where the enrolled person has a grade for all prerequisites.
3. List all enrollments where the enrolled person has a grade for any prerequisite.
4. List all enrollments where the enrolled person does not have a grade for all prerequisites.
Q.a) Give a Relational Algebra representation and SQL statements representation for each query and Justify SQL to Relational Algebra conversion for each query in the Answer. (give a brief reason why it is correct?)
* The Answer need Explanation of how you converted relational algebra expressions in SQL representation for all 4 Queries.
Homework Answers
Add Answer to:
1. Consider the following database. Note that it distinguishes
between a course and the class of...
Person(UPI, givenname, surname) with key UPI Courses(course, title, program) with key course Classes(classnr, course, year) with...
Person(UPI, givenname, surname) with key UPI Courses(course, title, program) with key course Classes(classnr, course, year) with key classnr Enrollments(UPI, classnr) Grades(UPI, classnr, grade) Prereqs(course, prereq) Teachers(UPI, classnr) With natural foreign key constraints given by common attribute names. 1. List all UPIs of all teachers who teach a course with a prerequisite. 2. List all enrollments (i.e., list UPI and classnr) where the enrolled person has a grade for all prerequisites. 3. List all enrollments where the enrolled person has a...
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber ,...
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber , CourseTitle, NumberOfUnits, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00. Enrollment = (SSN , CourseNumber, Grade) Express the following queries using SQL statements with minimum number of tables and operations. a. List the SSN and Name of every student who has no grade in at least one course. b. List the name of every student who enrolled in at least two courses....
Please do these carefully. Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title,...
Please do these carefully. Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: 1. List the name of every student and SSN who has not taken any course more than once. 2. List every...
Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = (...
Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: 1. List every two CourseNumber and their titles which have the same prerequisites. 2. List the name of every student and SSN who has completed...
Give an example of 3 relations illustrating primary keys and foreign keys. Consider the following relations:...
Give an example of 3 relations illustrating primary keys and foreign keys. Consider the following relations: Student(sID, surName, firstName, campus, email) Course(dept, cNum, name) Offering(oID, dept, cNum, term, instructor) Took(sID, oID, grade) Such as: Offering[dept, cNum] ⊆ Course[dept, cNum] Took[sID] ⊆ Student[sID] Took[oID] ⊆ Offering[oID] Answer the following query using relational algebra -Give the Student number of all students who have taken the course number “343” by the department “CS”. - Find sID of all students who have earned some...
2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST...
2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST TITLE 313 1313 50s0 Basketbal 8989 Basketbal 007 COMP203A 7007 COMP20e 007 ames Bond 302 Database Systems COMP203 Computer Or n Brown 00 CoMP302 A o0 3un SmuCOMP42daedDatabase Systems 3 13 COMP 209 70 5050 Susan Smith COMP447 nced Database S 1313 COMP2O3A 1313 COMP302 5050 COMP20 C 1313 COMP42 7007 Write the SQL statement that displays the names of the students who have...
The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName,...
The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and (roomNo, hoteINo) forms the primary key; Booking contains details of bookings and (hoteINo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key....
Problems and Exercises 1 through 5 are based on the class schedule 3NF relations along with some sample data in Figure 7-16. For Problems and Exercises 1 through 5, draw a Venn or ER diagram and mark it to show the data you expect your query to use to p
Problems and Exercises 1 through 5 are based on the class schedule 3NF relations along with some sample data in Figure 7-16. For
Problems and Exercises 1 through 5, draw a Venn or ER diagram and
mark it to show the data you expect your query to use to produce the
results.Figure 7-16: Class scheduling relations (for Problems and Exercises 1-5Figure 7-17 Adult literacy program (for Problems and Exercises 6-14)1. Write SQL retrieval commands for each of the following
queries:a....
Consider the following relations for course-enrollment database in a university: STUDENT(S-ID,S-Name, Department, Birth-date) COURSE(C-ID, C-Name, Department)...
Consider the following relations for course-enrollment database in a university: STUDENT(S-ID,S-Name, Department, Birth-date) COURSE(C-ID, C-Name, Department) ENROLL(S-ID, C-ID, Grade) TEXTBOOK(B-ISBN, B-Title, Publisher, Author) BOOK-ADOPTION(C-ID, B-ISBN) (a) Draw the database relational schema and show the primary keys and referential integrity constraints on the schema. (b) How many superkeys does the relation TEXTBOOK have? List ALL of them. (c) Now assume each COURSE has distinct C-Name. (i) If C-ID is a primary key, what are the candidate keys and the unique keys...
This is database system concept. 1.Find the ids of instructors who are also students using a...
This is database system concept. 1.Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference. 2.Find the ids of instructors who are also students using the set membership operator. 3.Find the ids of instructors who are...
2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST TITLE 313 1313 50s0 Basketbal 8989 Basketbal 007 COMP203A 7007 COMP20e 007 ames Bond 302 Database Systems COMP203 Computer Or n Brown 00 CoMP302 A o0 3un SmuCOMP42daedDatabase Systems 3 13 COMP 209 70 5050 Susan Smith COMP447 nced Database S 1313 COMP2O3A 1313 COMP302 5050 COMP20 C 1313 COMP42 7007 Write the SQL statement that displays the names of the students who have...
Most questions answered within 3 hours.
-
Where is the error in this code sequence?
String s1 = "Hello";
String s2 = "ello";...
asked 10 months ago -
Financial data for Joel de Paris, Inc., for last year
follow:
Joel de Paris, Inc.
Balance...
asked 10 months ago -
Consider this reaction:
Al2(SO4)3 (aq)+ BaCl3
(aq) Al2Cl6 (aq)- +
3BaSO4(s) . What is the...
asked 10 months ago -
Suppose that Savneet is considering increasing her
recent random sample from 20 car rentals to 40...
asked 10 months ago -
Trucks arrive at an unloading terminal at an average rate of 120
per hour.
Trucks arrive...
asked 10 months ago -
Why are methanol and ethanol completely soluble in water while
octanol is not very little soluble....
asked 10 months ago -
A facilities manager at a university reads in a research report
that the mean amount of...
asked 10 months ago -
When the CuSO4 is rehydrated by adding water to the anhydrous
compound, is this an endothermic...
asked 10 months ago -
A ray of sunlight is passing from diamond into crown glass; the
angle of incidence is...
asked 10 months ago -
A block of mass 0.249 kg is placed on top of a light, vertical
spring of...
asked 10 months ago -
how do the kidneys compensate in the presences of acidosis
a) trigger hyperventilate
b) reserve acid...
asked 10 months ago -
Question 501 pts
The rental rate of capital to the firm increases. Which of the
following...
asked 10 months ago