Digital Electronics

Database Management Systems

JGEC/CSE/DBMS (PCC-CS503) 2025/ClassTest I

Full marks: 15                                                                                                                Time:  45 minutes

(You can answer all questions but maximum score will be 15)                                                 

1)      Give example of the following i) meta data, ii) derived attribute, iii) composite attribute and iv) aggregate function.    2

2) Consider two relations: Instructor(ID, Name, Dept, Salary) and Teaches(ID, CourseID, SectionId, Sem, Year). Assume ID is the foreign key attribute of Teaches referencing Instructor relation. What happens if you want to insert a record into Teaches and delete a record from the Instructor relations, respectively?     2

4)  There are two relations, Instructor(ID, Name, Dept, Salary) and Teaches(ID, CourseID, SectionId, Sem, Year) having n and m numbers of rows, respectively. Each tuple or row of Instructor and Teaches needs si and st bytes to store themselves, respectively. Say, there are k numbers of instructors from the physics department and p number of instructors with salaries greater than 90000. Say, each comparison takes t seconds. Find the size of all the results and comparison time for execution of the queries.                                                                                                               1.5x4+2=8

a)      Instructor x Teaches

b)      InstructorInstructor.ID = Teaches.ID ⨝Teaches  (Teaches.ID is the foreign key referencing Instructor)

c)       σ Dept = “Physics”^ Salary > 90000 (InstructorInstructor.ID = Teaches.ID ⨝Teaches)

d)       σ Dept = “Physics”( σSalary > 90000 (InstructorInstructor.ID = Teaches.ID ⨝Teaches))

e)      Left outer join and right outer join of Instructor and Teaches (find only the size of the resultant instances while I) Teaches.ID is the foreign key referencing Instructor, ii) Teaches.ID is not a foreign key attribute)

5)  Consider the relations: R(A,B,C,D), S(E,F,G), T(G,D,H) and draw the equivalent query tree of the following query and optimize it.  ∏AϭH>10(∏A,B,C,D,E,F,G,H(ϭS.G=T.G and R.D=T.D(RxSxT))))                          2+2=4