Question 1:
Design the ER diagram for an XYZ Information Technology Training Institute which would meet information requirements for their training programmes. Clearly indicate entities, relationships, cardinality and the key constraints. The explanation of the environment is as follows:
Institute has 15 instructors and could handle upto 100 trainees for each training session. Institute offers 5 advanced technology courses, each of which is taught by the team of 2 or more instructors. Each instructor is allotted to the maximum of two teaching teams or might be assigned to do research each trainee undertakes one Advanced technology course per training session.
Question 2:
Staff No. Branch No. Branch Address Name Position Hrs/Week
E101 B02 Sun Plaza, Delhi, 110001 Rahul Assistant 16
E101 B04 2/3 UT, Delhi, 110111 Rahul Assistant 9
E122 B02 Sun Plaza, Delhi, 110001 Medha Assistant 14
E122 B04 2/3 UT, Delhi, 110111 Medha Assistant 10
Do the Normalization for above given table.
Question 3:
Describe how “GROUP By” clause works. What is the difference between the WHERE and HAVING clauses? Describe them with the help of example for each.
Question 4:
Given the relational schema:
ENROL (S#, C#, Class) - S# represents student number
TEACH (Prof, C#, Class) - C# represents course number
ADVISE (Prof, S#) - Prof is project guide of S#
PRE_REQ (C#, Pre_C#) - Pre_C# is prerequisite course
GRADES (S#, C#, Grade, Year)
STUDENT (S#, Sname) - Sname is student name
Give queries expressed in relational algebra, tuple calculus and domain calculus for the following:
(i) List all students taking courses with Prof Ashok.
(ii) List all student names who have chosen Prof Rahul as project Guide.
(iii) List those professors who teach more than one class.
(iv) List all student names who got Grade A in the year 2012 in C# P202.
(v) List all students who have taken the pre-requisite course Pre_C# P304.
Note: Make appropriate assumptions, if any.
Question 5:
What are the advantages of indexed-sequential file organisation? With the help of the example describe the structure of indexed-sequential file.
Question 6:
Write the differences between Hierarchical, Network and Relational data models with the help of appropriate examples. Describe why relational data model is a better choice over the two. Justify the answer.