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.
Assumptions:
- Each student can only be registered in one programme at a time.
- Each programme consists of exactly 5 compulsory courses.
- The result for each course is a simple pass or fail, represented by a boolean value.
Entities:
1. Student
- StudentID (Primary Key)
- Name
- AccountID (Foreign Key referencing Account.AccountID)
2. Programme
- ProgrammeID (Primary Key)
- Name
- StartDate
- EndDate
3. Course
- CourseID (Primary Key)
- Name
- Credits
- ProgrammeID (Foreign Key referencing Programme.ProgrammeID)
4. Account
- AccountID (Primary Key)
- Username
- Password
5. Result
- ResultID (Primary Key)
- CourseID (Foreign Key referencing Course.CourseID)
- StudentID (Foreign Key referencing Student.StudentID)
- Marks
- Pass
Relationships:
1. Registers
- StudentID (Foreign Key referencing Student.StudentID)
- ProgrammeID (Foreign Key referencing Programme.ProgrammeID)
2. Administers
- CourseID (Foreign Key referencing Course.CourseID)
- ProgrammeID (Foreign Key referencing Programme.ProgrammeID)
3. Receives
- StudentID (Foreign Key referencing Student.StudentID)
- ProgrammeID (Foreign Key referencing Programme.ProgrammeID)
Relational Schema:
1. Student (StudentID, Name, AccountID)
- Primary Key: StudentID
- Foreign Key: AccountID references Account(AccountID)
2. Programme (ProgrammeID, Name, StartDate, EndDate)
- Primary Key: ProgrammeID
3. Course (CourseID, Name, Credits, ProgrammeID)
- Primary Key: CourseID
- Foreign Key: ProgrammeID references Programme(ProgrammeID)
4. Account (AccountID, Username, Password)
- Primary Key: AccountID
5. Result (ResultID, CourseID, StudentID, Marks, Pass)
- Primary Key: ResultID
- Foreign Key: CourseID references Course(CourseID)
- Foreign Key: StudentID references Student(StudentID)
Data Entry for Relations:
Student:
- StudentID: 1, Name: John Doe, AccountID: 1
- StudentID: 2, Name: Jane Smith, AccountID: 2
- ...
Programme:
- ProgrammeID: 1, Name: Computer Science, StartDate: 2021-07-01, EndDate: 2022-06-30
- ProgrammeID: 2, Name: Business Administration, StartDate: 2021-07-01, EndDate: 2022-06-30
- ...
Course:
- CourseID: 1, Name: Introduction to Programming, Credits: 4, ProgrammeID: 1
- CourseID: 2, Name: Database Management, Credits: 4, ProgrammeID: 1
- ...
Account:
- AccountID: 1, Username: johndoe123, Password: password123
- AccountID: 2, Username: janesmith456, Password: password456
- ...
Result:
- ResultID: 1, CourseID: 1, StudentID: 1, Marks: 85, Pass: true
- ResultID: 2, CourseID: 1, StudentID: 2, Marks: 70, Pass: true
- ...
Domain of Attributes:
- StudentID: Integer
- Name: String
- AccountID: Integer
- ProgrammeID: Integer
- CourseID: Integer
- Name: String
- Credits: Integer
- StartDate, EndDate: Date
- ResultID: Integer
- Marks: Integer
- Pass: Boolean
Primary Keys:
- Student: StudentID
- Programme: ProgrammeID
- Course: CourseID
- Account: AccountID
- Result: ResultID
Foreign Keys and Referential Integrity Constraints:
- Student: AccountID (references Account(AccountID))
- Course: ProgrammeID (references Programme(ProgrammeID))
- Result: CourseID (references Course(CourseID)), StudentID (references Student(StudentID))
- Registers: StudentID (references Student(StudentID)), ProgrammeID (references Programme(ProgrammeID))
- Administers: CourseID (references Course(CourseID)), ProgrammeID (references Programme(ProgrammeID))
- Receives: StudentID (references Student(StudentID)), ProgrammeID (references Programme(ProgrammeID))