Question 1: (5 Marks)
Design an ER diagram for a University, which manages the students, Programmes, courses and
student results. You should clearly identify the entities, relationships, relationship cardinality
and key constraints in your answer. You must also add proper attributes for each entity. A brief
description of the University is given below. Make suitable assumptions.
A University allows students to register for its programmes offered during an Academic year.
The academic year starts in July and ends in June. All the programmes of the University are of
1-year duration. Each programme consists of 5 compulsory courses of 4 credits each. A student
is given an account on the University MIS, where s/he can see her/his result. University
conducts the examinations at the end of the academic year. A student is declared passed in a
course if s/he secures 50% marks in the examination of that course. A student must pass all the
courses of the programme in which s/he has registered. The students who have passed their
programme are awarded a certificate during a convocation, which is held once a year.
Question 2: (8 Marks)
Design the Relational Schema for the E-R diagram that you have drawn for Question 1. The
relations must be at least in 3NF. Perform the following on the relations:
a) Enter about 5 sets of meaningful data in each of the relations.
b) Identify the domain of various attributes.
c) Identify the Primary key of every relation.
d) Identify the Foreign keys and referential integrity constraints in the relations.
Question 3: (10 Marks)
Answer the following queries using SQL for the database created in Question 1 and Question 2
(i) List the names of all the students of the Programme whose programme code is CMAD.
(ii) List the Programmes, which has no student.
(iii) Find the list of students who have been admitted to more than one Programme during the
current academic year. (A student must have a unique ID)
(iv) List the user id of the student whose unique ID is “A001”
(v) List the students who have not passed all the courses of their programme.
(vi) Find the student who has secured the highest total marks in the BCA programme.(vii) List the number of students who have passed the examination of the course DBMS in the present
academic year.
(viii) List the details of the student who has not appeared in a single examination in the present
academic year.
(ix) Find the details of the University topper (student with the highest percentage of marks among all
the students of the University) of the current academic year.
(x) Make a programme-wise list of students who are awarded a certificate in the present
academic year.
Note: Make suitable assumptions, if any.
Answer 1:
ER Diagram:
Entities:
- University
- Programme
- Course
- Student
- Result
Relationships:
- University offers Programme (one-to-many)
- Programme consists of Course (one-to-many)
- Student registers for Programme (many-to-many)
- Student has Result (one-to-one)
Attributes:
University:
- Name (key)
- MIS
Programme:
- Code (key)
- Name
- Duration
Course:
- Code (key)
- Name
- Credits
Student:
- ID (key)
- Name
- Email
Result:
- Marks
- Pass/Fail
Answer 2:
Relational Schema:
University(Name, MIS)
Programme(Code, Name, Duration)
Course(Code, Name, Credits)
Student(ID, Name, Email)
Result(StudentID, ProgrammeCode, CourseCode, Marks, Pass/Fail)
a) Example data for each relation:
University:
(Name, MIS)
("ABC University", "abcuni")
Programme:
(Code, Name, Duration)
("CMAD", "Computer Science and Mobile Application Development", 1 year)
Course:
(Code, Name, Credits)
("CSC101", "Introduction to Computer Science", 4)
Student:
(ID, Name, Email)
("A001", "John Doe", "john.doe")
Result:
(StudentID, ProgrammeCode, CourseCode, Marks, Pass/Fail)
("A001", "CMAD", "CSC101", 80, "Pass")
b) Domains of various attributes:
- University.Name: String
- University.MIS: String
- Programme.Code: String
- Programme.Name: String
- Programme.Duration: Integer
- Course.Code: String
- Course.Name: String
- Course.Credits: Integer
- Student.ID: String
- Student.Name: String
- Student.Email: String
- Result.StudentID: String
- Result.ProgrammeCode: String
- Result.CourseCode: String
- Result.Marks: Integer
- Result.Pass/Fail: String
c) Primary keys of each relation:
- University: Name
- Programme: Code
- Course: Code
- Student: ID
- Result: (StudentID, ProgrammeCode, CourseCode)
d) Foreign keys and referential integrity constraints:
- Programme.Code (in Programme) is a foreign key referencing ProgrammeCode (in Result)
- Course.Code (in Course) is a foreign key referencing CourseCode (in Result)
- Student.ID (in Student) is a foreign key referencing StudentID (in Result)
Answer 3:
(i) SELECT Student.Name FROM Student JOIN Programme ON Student.ID = Programme.StudentID WHERE Programme.Code = "CMAD";
(ii) SELECT Programme.Name FROM Programme LEFT JOIN Student ON Programme.Code = Student.ProgrammeCode WHERE Student.ID IS NULL;
(iii) SELECT Student.Name FROM Student GROUP BY Student.ID HAVING COUNT(DISTINCT ProgrammeCode) > 1;
(iv) SELECT Student.MIS FROM Student WHERE Student.ID = "A001";
(v) SELECT Student.Name FROM Student JOIN Result ON Student.ID = Result.StudentID GROUP BY Student.ID HAVING COUNT(Result.Pass/Fail) < (SELECT COUNT(CourseCode) FROM Programme WHERE Programme.Code = Student.ProgrammeCode);
(vi) SELECT Student.Name FROM Student JOIN Result ON Student.ID = Result.StudentID JOIN Programme ON Programme.Code = Student.ProgrammeCode WHERE Programme.Name = "BCA" GROUP BY Student.ID ORDER BY SUM(Result.Marks) DESC LIMIT 1;
(vii) SELECT COUNT(Student.Name) FROM Student JOIN Result ON Student.ID = Result.StudentID JOIN Course ON Result.CourseCode = Course.Code WHERE Course.Name = "DBMS";
(viii) SELECT Student.Name FROM Student LEFT JOIN Result ON Student.ID = Result.StudentID WHERE Result.Marks IS NULL;
(ix) SELECT Student.Name FROM Student JOIN Result ON Student.ID = Result.StudentID GROUP BY Student.ID ORDER BY SUM(Result.Marks) / COUNT(Result.CourseCode) DESC LIMIT 1;
(x) SELECT Programme.Name, Student.Name FROM Programme JOIN Student ON Programme.Code = Student.ProgrammeCode WHERE Programme.Duration = 1;