Question

Consider the following schema:
Student(id, name)
Registration(id, code)
Course(code, instructor)

A) Explain how we can violate the foreign key constraint from the id attribute of Registration to the Student relation.  
Ans.

B)Give the following queries in the relational algebra.

1)What are the names of students registered in IT244?   
Ans.

2 )What are the names of students registered in both IT244 and CS141?
Ans.                                

3)What are the names of students who are taking a subject not taught by Philips?
Ans.                            

C )For each of the following relational algebra queries, write in English what they mean.
1. Πinstructor (σcode=IT446 or code=IT230(Course))       
Ans.

2. Πname (σcode=IT446 (Student ⋈ Registration)) ⊔ Πname (σcode=IT441(Student ⋈ Registration))                      

Ans.

Answer 1

A)
Ans. If id attribute in Registration exists and it does not match with any id in the Student table , it violates foreign key constraint. Also if the value of id in Registration table is null it also violates foreign key constraint

B)Give the following queries in the relational algebra.

1)
Ans.

Π_name(σ_code='IT446' (Student Registration))       

2 )
Ans.

Π_name(σ_{code='IT446' or code = 'CS141'} (Student Registration))                                    

3)
Ans.

      Π_name(σ _{instructor != 'Philips'}(Student Registration Course))                        

C )For each of the following relational algebra queries, write in English what they mean.
1.
Ans. What are the name of instructors who teach course IT446 or course IT230

2.   

Ans. What are the name of students who have register for the Course IT446 and Course IT441

Do ask if any doubt. Please upvote.