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.
Entities:
1. University
2. Student
3. Programme
4. Course
5. Result
Relationships:
1. University offers Programme (1 to many)
2. Programme has Course (1 to many)
3. Student registers for Programme (many to many)
4. Student has Result (1 to many)
Attributes:
1. University:
- university_id (Primary Key)
- university_name
- university_address
2. Student:
- student_id (Primary Key)
- student_name
- student_address
- student_dob
3. Programme:
- programme_id (Primary Key)
- programme_name
- programme_duration
- start_date
- end_date
4. Course:
- course_id (Primary Key)
- course_name
- credits
5. Result:
- result_id (Primary Key)
- student_id (Foreign Key referencing Student)
- course_id (Foreign Key referencing Course)
- marks
- pass_status
Relational Schema:
1. University
- university_id (Primary Key)
- university_name
- university_address
2. Student
- student_id (Primary Key)
- student_name
- student_address
- student_dob
3. Programme
- programme_id (Primary Key)
- programme_name
- programme_duration
- start_date
- end_date
4. Course
- course_id (Primary Key)
- course_name
- credits
5. Result
- result_id (Primary Key)
- student_id (Foreign Key referencing Student)
- course_id (Foreign Key referencing Course)
- marks
- pass_status
a) Sample data:
University:
university_id | university_name | university_address
1 | ABC University | XYZ Street
Student:
student_id | student_name | student_address | student_dob
1 | John Doe | ABC Lane | 1995-07-15
2 | Jane Smith | DEF Road | 1996-05-20
Programme:
programme_id | programme_name | programme_duration | start_date | end_date
1 | BCA | 1 year | 2020-07-01 | 2021-06-30
2 | CMAD | 1 year | 2020-07-01 | 2021-06-30
Course:
course_id | course_name | credits
1 | Programming | 4
2 | Database | 4
Result:
result_id | student_id | course_id | marks | pass_status
1 | 1 | 1 | 80 | Passed
2 | 1 | 2 | 70 | Passed
b) Domain of various attributes:
- university_id: Integer
- university_name: String
- university_address: String
- student_id: Integer
- student_name: String
- student_address: String
- student_dob: Date
- programme_id: Integer
- programme_name: String
- programme_duration: String
- start_date: Date
- end_date: Date
- course_id: Integer
- course_name: String
- credits: Integer
- result_id: Integer
- marks: Integer
- pass_status: String
c) Primary keys:
- university_id for University
- student_id for Student
- programme_id for Programme
- course_id for Course
- result_id for Result
d) Foreign keys and referential integrity constraints:
- student_id in Result referencing student_id in Student
- course_id in Result referencing course_id in Course
SQL queries:
(i) SELECT student_name FROM Programme JOIN Student ON Programme.programme_id = Student.programme_id WHERE Programme.programme_id = 'CMAD';
(ii) SELECT programme_name FROM Programme LEFT JOIN Student ON Programme.programme_id = Student.programme_id WHERE Student.student_id IS NULL;
(iii) SELECT student_id FROM Student GROUP BY student_id HAVING COUNT(DISTINCT programme_id) > 1;
(iv) SELECT user_id FROM Student WHERE student_id = 'A001';
(v) SELECT student_id FROM Result WHERE pass_status = 'Failed';
(vi) SELECT Student.student_id, SUM(Result.marks) FROM Student JOIN Result ON Student.student_id = Result.student_id JOIN Course ON Result.course_id = Course.course_id JOIN Programme ON Course.programme_id = Programme.programme_id WHERE Programme.programme_name = 'BCA' GROUP BY Student.student_id ORDER BY SUM(Result.marks) DESC LIMIT 1;
(vii) SELECT COUNT(DISTINCT Result.student_id) FROM Result JOIN Course ON Result.course_id = Course.course_id JOIN Programme ON Course.programme_id = Programme.programme_id WHERE Course.course_name = 'DBMS' AND Programme.start_date >= '2020-07-01' AND Programme.end_date <= '2021-06-30';
(viii) SELECT student_id, student_name, student_address FROM Student WHERE student_id NOT IN (SELECT student_id FROM Result WHERE Date(result_date) >= '2020-07-01' AND Date(result_date) <= '2021-06-30');
(ix) SELECT Student.student_id, Student.student_name, (SUM(Result.marks) / (COUNT(DISTINCT Result.course_id) * 100)) * 100 AS percentage FROM Student JOIN Result ON Student.student_id = Result.student_id JOIN Course ON Result.course_id = Course.course_id JOIN Programme ON Course.programme_id = Programme.programme_id WHERE Programme.start_date >= '2020-07-01' AND Programme.end_date <= '2021-06-30' GROUP BY Student.student_id ORDER BY percentage DESC LIMIT 1;
(x) SELECT Programme.programme_name, Student.student_name FROM Programme JOIN Student ON Programme.programme_id = Student.programme_id JOIN Result ON Student.student_id = Result.student_id WHERE Result.pass_status = 'Passed' AND Programme.start_date >= '2020-07-01' AND Programme.end_date <= '2021-06-30';