BSc CSIT in Nepal: DBMS Lab report 1(Question)

Consider a relation database consisting of following relations:

Teachers_detail(Tid, Tname, Address, Phno, Qualification, Major, Minor, Type)

Student_detail (Roll, Name, Address, Telephone, Email, DOB, faculty, yearsem, batch, level)

Subject_detail(Subid, Tid, Faculty, Yearsem, Level, Title, Hours, Fm, Pm, Type)

Exam_detail (ExamYear, Term, Start_date, End_date, result_day)

Marks_detail(Examyear, term, Roll, Subid, Fm, Marks)

Constraints in the database schema:

Primary key:

Teachers_detail (Tid)

Student_detail (Roll, Faculty, Level, Yearsem, Batch)       

Subject_detail (Subid)

Exam_detail (Examyear, Term)

Foreign key:

Subject_detail(tid)

Marks_detail(Examyear,term); Marks_detail(Roll); marks_detail(Subid)

Other constraints:

1. A subject must be taught by a teacher.

2. With in one exam year the sum of Fm must be equal to the Fm of the subject.

3. Result day is after one week of the end_date.

Theoretical part:

1. Find the entity sets and corresponding attributes.

2. Find the relationship sets.

3. Identify the strong entity set, weak entity set, multivalued attributes, and derived attributes.

4. Draw the E-R diagram and annotate it

5. Draw the schema diagram.

Practical part:

Write the SQL syntax for the following:

1. Create database named My_Dept.

2. Create relation schema with necessary constraints.

3. Describe the format of the tables.

4. Insert the proper sample information on the relations. At least five tuples.

5. Display the content of all the relations.

click here for

                          Theoritical Part                      Practical Part Solution