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
Entities:
1. University
2. Programme
3. Course
4. Student
5. Result
6. Convocation
Relationships:
1. University offers Programme
2. Programme has Course
3. Student registers for Programme
4. Student has Result
5. Programme holds Convocation
Relationship Cardinalities and Key Constraints:
University:
- Attributes: university_id (Primary Key), university_name, start_date, end_date
Programme:
- Attributes: programme_code (Primary Key), programme_name, duration
Course:
- Attributes: course_code (Primary Key), course_name, credits
Student:
- Attributes: student_id (Primary Key), student_name, date_of_birth
Result:
- Attributes: result_id (Primary Key), student_id (Foreign Key), course_code (Foreign Key), marks
Convocation:
- Attributes: convocation_id (Primary Key), programme_code (Foreign Key), year
Relational Schema:
University (university_id, university_name, start_date, end_date)
Programme (programme_code, programme_name, duration)
Course (course_code, course_name, credits)
Student (student_id, student_name, date_of_birth)
Result (result_id, student_id, course_code, marks)
Convocation (convocation_id, programme_code, year)
a) Example Data:
University:
1. (1, "University of XYZ", "2022-07-01", "2023-06-30")
2. (2, "ABC University", "2022-07-01", "2023-06-30")
Programme:
1. ("CMAD", "Master of Computer Applications Development", 1)
2. ("BCA", "Bachelor of Computer Applications", 1)
Course:
1. ("COMP101", "Programming Fundamentals", 4)
2. ("COMP102", "Database Management Systems", 4)
Student:
1. ("S001", "John Doe", "2000-01-01")
2. ("S002", "Jane Smith", "2001-02-03")
Result:
1. (1, "S001", "COMP101", 80)
2. (2, "S001", "COMP102", 75)
Convocation:
1. (1, "CMAD", 2023)
2. (2, "BCA", 2023)
b) Domain of Attributes:
- university_id: Integer
- university_name: String
- start_date: Date
- end_date: Date
- programme_code: String
- programme_name: String
- duration: Integer
- course_code: String
- course_name: String
- credits: Integer
- student_id: String
- student_name: String
- date_of_birth: Date
- result_id: Integer
- marks: Integer
- convocation_id: Integer
- year: Integer
c) Primary Keys:
- university_id (University)
- programme_code (Programme)
- course_code (Course)
- student_id (Student)
- result_id (Result)
- convocation_id (Convocation)
d) Foreign Keys and Referential Integrity Constraints:
- programme_code (Convocation, referencing Programme.programme_code)
- student_id (Result, referencing Student.student_id)
- course_code (Result, referencing Course.course_code)
- year (Convocation, referencing University.end_date)
SQL Queries:
(i) List the names of all the students of the Programme whose programme code is CMAD.
SELECT student_name
FROM Student s
JOIN Programme p ON s.student_id = p.student_id
WHERE p.programme_code = 'CMAD';
(ii) List the Programmes which have no students.
SELECT p.programme_name
FROM Programme p
LEFT JOIN Student s ON p.programme_code = s.programme_code
WHERE s.student_id IS NULL;
(iii) Find the list of students who have been admitted to more than one Programme during the current academic year.
SELECT s.student_name
FROM Student s
JOIN Programme p1 ON s.student_id = p1.student_id
JOIN Programme p2 ON s.student_id = p2.student_id
WHERE p1.year = p2.year
GROUP BY s.student_name
HAVING COUNT(DISTINCT p1.programme_code) > 1;
(iv) List the user id of the student whose unique ID is “A001”.
SELECT student_id
FROM Student
WHERE student_id = 'A001';
(v) List the students who have not passed all the courses of their programme.
SELECT s.student_name
FROM Student s
JOIN Result r ON s.student_id = r.student_id
JOIN Programme p ON s.programme_code = p.programme_code
WHERE r.marks < 50
GROUP BY s.student_name
HAVING COUNT(DISTINCT r.course_code) < COUNT(DISTINCT p.course_code);
(vi) Find the student who has secured the highest total marks in the BCA programme.
SELECT s.student_id, s.student_name, SUM(r.marks) AS total_marks
FROM Student s
JOIN Result r ON s.student_id = r.student_id
JOIN Programme p ON s.programme_code = p.programme_code
WHERE p.programme_code = 'BCA'
GROUP BY s.student_id, s.student_name
ORDER BY total_marks DESC
LIMIT 1;
(vii) List the number of students who have passed the examination of the course DBMS in the present academic year.
SELECT COUNT(DISTINCT s.student_id) AS num_students
FROM Student s
JOIN Result r ON s.student_id = r.student_id
JOIN Programme p ON s.programme_code = p.programme_code
WHERE p.year = YEAR(CURRENT_DATE()) AND r.course_code = 'DBMS' AND r.marks >= 50;
(viii) List the details of the student who has not appeared in a single examination in the present academic year.
SELECT s.student_id, s.student_name
FROM Student s
LEFT JOIN Result r ON s.student_id = r.student_id
JOIN Programme p ON s.programme_code = p.programme_code
WHERE p.year = YEAR(CURRENT_DATE()) AND r.result_id IS NULL;
(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.
SELECT s.student_id, s.student_name, (SUM(r.marks) / (COUNT(r.result_id) * 100)) AS percentage
FROM Student s
JOIN Result r ON s.student_id = r.student_id
JOIN Programme p ON s.programme_code = p.programme_code
JOIN Convocation c ON p.programme_code = c.programme_code
JOIN University u ON c.year = YEAR(u.end_date)
WHERE p.year = YEAR(CURRENT_DATE())
GROUP BY s.student_id, s.student_name
HAVING percentage = MAX(percentage);
(x) Make a programme-wise list of students who are awarded a certificate in the present academic year.
SELECT p.programme_name, s.student_name
FROM Programme p
JOIN Convocation c ON p.programme_code = c.programme_code
JOIN Student s ON p.student_id = s.student_id
WHERE c.year = YEAR(CURRENT_DATE());