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

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

problem 3:  

Describe how “GROUP By” clause works. What is the difference between the WHERE and HAVING clauses? Describe them with the help of ex for each.

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

problem 5: 

What are the advantages of indexed-sequential file organisation? With the help of the ex describe the structure of indexed-sequential file.

problem 6:   

prepare the differences between Hierarchical, Network and Relational data models with the help of appropriate exs. Describe why relational data model is a better choice over the two. Justify the answer.