ZuoTi.Pro
Question

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.


engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Do like and comment if you have any queries.

0 0
Add a comment
Know the answer?
Add Answer to:
1. Consider the following database. Note that it distinguishes between a course and the class of...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • 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...

  • 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....

  • 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...

  • 4. The exams database: The following database contains multiple choice exam questions tables Questions and On...

    4. The exams database: The following database contains multiple choice exam questions tables Questions and On Hons). Each question has a question Tert and each one of the options has and optid, which is an integer representing options A) (optid=1), B) (optid=2), C) (optid=3), etc. as well as the text of the option. The field correct is either Oor 1 and indicates which is the correct option for that particular question. courseThe exams have numbers 1, 2, 3, etc. Each...

  • The lab for this week addresses taking a logical database design (data model) and transforming it...

    The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to define the...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT