Question

1. Give an example of 3 relations illustrating primary keys and foreign keys.

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

3. -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 grade over 80 and some grade below 50.

- Find the Terms when the course number “369” by the department “CS” was not offered.

- Find the Department and course number of courses that have never been offered.




Answer 1

NOTE: I am mentioning complete question again since there are no question Numbers

SOLUTION 1 FOR -> Give the Student number of all students who have taken the course number “343” by the department “CS”.

SELECT surName || firstName AS Name
FROM Student s,Offering O, Took t
WHERE O.dept=="CS" AND O.cNum==343 AND O.oID==t.oID AND s.sID==t.sID

EXPLANATION:

1. We have dept and cnum in offering and student details in student table and the details of who has taken the course in took table so we need to join those tables to get the data

2. We need to first check for dept and course filtering in the offerings so we will get the offering ID

3. map this data with the Took table with the offering Id (oID) we will get the   student Ids from this (sID) we now can join the student table with the student id and get the name

4. here I have concated the surname and first name to get the name

OUTPUT:

SQL Statement: SELECT surName|firstName AS Name FROM Student s, Offering 0, Took t WHERE 0.dept=="CS" AND O.cNum==343 AND O.OID==t.OID AND S. SID==t.SID Edit the SQL Statement, and click "Run SQL" to see the result. Run SQL >> Result: Number of Records: 1 Name yedNavi

SOLUTION 2 FOR-> Find sID of all students who have earned some grade over 80 and some grade below 50.

SELECT sID
FROM Took
WHERE grade>80

INTERSECT

SELECT sID
FROM Took
WHERE grade<50

EXPLANATION:

1 INTERSECT-> intersect will return only the common items in the two querys data

2. here by using 1st query i am finding the sids of students who have grade >80

3. By using seconf 2nd query i am findingg the sids of students whose garde is < 50

4 Now by applying intersect we will the sIDs whic are common in both the querys

OUTPUT:

SQL Statement: SELECT SID FROM Took WHERE grade>80 INTERSECT SELECT SID FROM Took WHERE grade 50 Edit the SQL Statement, and click "Run SQL" to see the result. Run SQL >> Result: Number of Records: 1 SID 51001

SOLUTION 3:For ->Find the Terms when the course number “369” by the department “CS” was not offered.

SELECT term
from Offering
where term NOT IN (
SELECT term
from Offering
WHERE dept=="CS" AND cNum==369)

EXPLANATION:

1 . We need to first find out all terms in which the course 369 is offered by 369

2. Check all the terms that are available in the Offering then we need to project that term where the course is not offered like if Term is not in the Offering of previously fetched data of the terms of 369 CS then we need to project that Term

3. SImply it is like check for all the available terms in the Offering then check for the terms of 369 CS if any term is missing for CS 369 to all terms project them

OUTPUT:

SQL Statement: SELECT term from Offering where term NOT IN ( SELECT term from Offering WHERE dept=="CS" AND Num==369) Edit the SQL Statement, and click "Run SQL" to see the result. Run SQL >> Result: Number of Records: 2 term

SOLUTION 4: FOR-> Find the Department and course number of courses that have never been offered

SELECT C.dept,C.cNum
FROM Course C,Offering O
WHERE C.dept==O.dept and C.cNum==O.cNum

EXPLANATION:

1. We have all the courses in the Course Table and Offerings in the Offering table so we need to join those table

2. We need to join them based dept and course Number if the course is offered then it will be in the offering table and projected

3 For Improvement of the presentation of the data, we can also do group by dept,cNum so that the course offered by a department will be group and presented well

OUTPUT:

SQL Statement: SELECT C.dept,C.cNum FROM Course C, Offering o WHERE C.dept==0.dept and C.cNum==0.cNum Edit the SQL Statement, and click "Run SQL" to see the result. Run SQL >> Result: Number of Records: 3 dept cNum 310 343 EC 343