WORK OUTLINE:
- PROBLEM AREA
- SCOPE OF WORK
- RELATIONSHIPS
- ENTITY RELATIONSHIP DIAGRAM (ASSUMPTIONS/LIMITATIONS)
- RELATIONAL TABLES
- SCHEMA NORMALIZATION
- FINAL SCHEMA DESIGN
1. PROBLEM AREA
Design a relational database for a University to store records of timetables and keep proper track of students’ attendance.
2. SCOPE OF WORK
I have properly studied the case/problem, considering all the information provided and put in this report assumptions and limitations to the data model adopted in a bid to cater for real life scenarios even within the context of this work.
3. RELATIONSHIPS
In the ER Diagram below, there are relationships between entities which enforces the constraints on both sides of the relationship (side of Entity A, side of Entity B).
The keys are as follows:
| Key cardinality | Explanation |
|---|---|
| 1:1 | This is a one to one relationship between entities A and B, where one element of A can only be linked to one element of B and vice versa |
| 1:1..* | This is a one to many relationship between entities A and B, where one element of A can be linked to many elements of B but a member of B is only linked to one element of A. |
| 1..*:1..* | Many to Many relationship, many elements of A can be linked to that of B and vice versa. |
4.1 ENTITY RELATIONSHIP DIAGRAM
4.2 ER Explanations and adequate assumptions:
There is a one to many relationship between the School and Course Entities. This is on the premise that a school runs multiple courses. School_ID is the primary key in the school entity and a foreign key in the course entity while Course_ID is the primary key in the course table.
There is a one to many relationship between the Course and Module Entities. This is from the case which states that a course contains multiple Modules. Course_ID and Module_Code are the primary keys of the course and module entities respectively and
Course_ID is a foreign key in Module table for the relationship.The relationship between the Module and Session entities is a One to Many relationship. This is on the assumption that a module i.e CMM524 will constitute many sessions. Module_Code and Session_ID are the respective primary keys with Module_Code being the foreign key in Session table representing the relationship.
The Timetable required as in the case can be derived from the relationship between the primary (composite) keys in the School, Course, Semester and Module and Session entities. This will be discussed in more details later.
Course and Semester entities are on a one to many relationship. This has that one course can include one or many semesters. Course_ID and Semester_ID are the primary keys in their respective tables. Course_ID is a foreign key in semester to represent the one to many relationship.
A one to many relationship also exists between the module and semester entities; thus stating that one module can be taught in many semesters. Module_Code and Semester_ID are the respective primary keys. Module_Code is another foreign key in the semester table based on the relationship. A Semester is assumed to be in the academic calender year i.e 1st Semester (01) runs from September to December, 2nd Semester (02) runs from January to May while 3rd Semester (03) runs from June to August.
There’s a one to many relationship between Course and Student entities signifying that a course can have many students enrolled into it and many students can enroll for a course. There are Student_ID and Course_ID as primary keys for both entities.
Course_ID is the foreign key in student entity representing that relationship.A student will have different student IDs for undergraduate degree and post graduate programs. The Student ID for a particular student for undergraduate i.e. BSc Yoruba Engineering will be different for MSc and then PHD Yoruba Engineering.
An assumption was made in the Session Entity which has that the Room_No attribute values can point to which type of session is in the particular session’s room. This means that a particular Lecture Room number LTR_100 will be for lectures only as the prefix LTR points to a lecture room. Similarly, Tutorial Room (TTR_200) will be used for tutorial only as the TTR prefix is for tutorial rooms and Lab Room (LBR_300) for laboratory purposes only as it the prefix LBR is for Lab rooms. The Room attribute value has a dual functionality in the table in identifying the Room number and the session type.
There is a Many to Many relationship between Student and Session represented by the attends relationship. This has that many students can be a part of many sessions. Student_ID and Session_ID are the primary keys. The many to many relationship
extends to a new table attendance which would contain the primary keys from the original tables as foreign keys. There is an attribute, attendance_record which would determine if the student attended the session or not. The attribute/column will have a Boolean data type (True or False) which would return True if the student attended the session or false if the student was absent.
4C. ER LIMITATIONS
There is a possibility that the Course and Session entities have a many to many relationships. This will lead to a new table to be created in the physical schema design having the primary keys of both tables in it.
In the event that a lecture is taken in a Lab or Tutorial Room, this will render my assumption is point (9) above invalid. As an adjustment, the record that can be inserted into the table or record updated stating the Room_No = “Room number of room used” which would point to whether it was a Lecture, Lab or Tutorial
5. RELATIONAL TABLES
After mapping the Entity Relationship Diagram above to relational tables, I have the following sets of tables and their mappings showing their relationship:
- The School and Course tables showing the one to many relationship via the foreign key (School_ID).
- The Course and Module tables showing the one to many relationship via the foreign key (Course_ID)
- The Module and Session tables showing their one to many relationship via the foreign key (Module_Code).
- The Course and Semester tables showing their one to many relationship via the foreign key (Course_ID)
- The Module and Semester tables showing their one to many relationship via the foreign key (Module_Code)
- The Course and Student tables showing their one to many relationship via the foreign key (Course_ID).
- The Student and Session tables showing their many to many relationship via a new table ATTENDANCE, having the primary keys of both tables as composite keys.
6. SCHEMA NORMALIZATION
1. SCHOOL
The School Table has a primary key School_ID which has one other attribute/column School_Name which is the name or a description of the different schools. A sample design of the Table is shown below:
For normalization, I have that:
- The School table is in 1NF as all attributes are atomic.
-
For 2NF, there’s a full functional dependency on School_ID from School_Name. The non-prime attribute School_Name is fully functionally dependent on the primary key School_ID as represented below.
School_ID -> School_Name {full functional dependency}
The table is in 3NF as there is no transitive dependencies on the primary key.
2. COURSE
Based on my design, the Course table has a primary key, Course_ID uniquely identifying every module and another attribute Course_Name which gives a name or a brief description of each course.
For normalization, I have that:
- The Course table is in 1NF as all attributes are atomic.
- For 2NF, there’s a full functional dependency on Course_ID from Course_Name. The non-prime attribute Course_Name is fully functionally dependent on the primary key Course_ID as represented below.
Course_ID -> Course_Name {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the primary key.
3. MODULE
The Module entity like the two previously discussed, has a primary ID, Module_Code and one other attribute Module_Name showing the Names or descriptions of different modules.
To show normalization on the Module Table:
- The table is in 1NF as all attributes are atomic as in the design above.
- For 2NF, there’s a full functional dependency on Module_Code from Module_Name. The non-prime attribute Module_Name is fully functionally dependent on the primary key Module_Code as represented below.
Module_Code > Module_Name {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the primary key
4. SESSION
As I discussed earlier, my session table is going to be uniquely a user defined object table such that some columns(attributes) like the primary key (Session_ID) and Room_No which would naturally determine that type of session type it is (Lecture, Lab, Tutorial).
The Session table has primary key Session_ID which uniquely identifies each record on the table considering the Room attribute can point to which session is holding in it. The session date and time specifies the date and time a session took or is to take place.
To show normalization on the Session Table:
- The table is in 1NF as all attributes are atomic.
- For 2NF, there’s a full functional dependency of all the non-prime attributes on the primary key. The non-prime attribute Room_No, Session_Date and Session_Time are fully functionally dependent on the primary key Session_ID as represented below.
Session_ID > Room_No {full functional dependency}
Session_ID > Session_Date {full functional dependency}
Session_ID > Session_Time {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the primary key
5. SEMESTER
The semester table has a semester_ID that will uniquely identify each record on the table. The semester_year represents the year the semester is in and the semester_code represents the semester in that semester/school calendar year. An example can be semester
ID SEMPLE03, which according to the table is year 2020 3rd Semester. To maintain the integrity of the schema, there’s a constraint that for a particular semester_year attribute (i.e.
2019) will only have values (01,02,03). This is to maintain the integrity and avoid redundancies or/and data inconsistency. The variances for dates obtainable for each semester as in (6) of the assumption module of this document.
For Normalization:
- The table is in 1NF as all attributes are atomic as in the design above.
- For 2NF, the 2 attributes are fully functionally dependent on the primary key and as such meets the 2NF requirement.
Semester_ID > Semester_Year {full functional dependency}
Semester_ID > Semester_Code {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the primary key.
6. STUDENT
The Student table has a primary key Student_ID which is unique and a student_name attribute given the name of student. A schema sample is seen below:
To show normalization on the Student Table:
- The table is in 1NF as all attributes are atomic as in the design above.
- For 2NF, there’s a full functional dependency on Student_ID from Student_Name. The non-prime attribute Student_Name is fully functionally dependent on the primary key Student_ID as represented below.
Student_ID > Module_Name {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the primary key
7. ATTENDANCE
The Attendance table is a result of a many to many relationship between the Student and Session tables. The table has a composite key comprising the Primary keys Student_ID and Session_ID has was from both related tables. There is another attribute, Attendance_Record which I assumed will form the basis of attendance. The column will have a Boolean data type which would contain the value “TRUE” if the Student attended and “FALSE” if the student didn’t attend.
To show normalization on the Attendance sample Table:
- The table is in 1NF as all attributes are atomic as sample.
- Attendance_Record has a full functional dependency on the composite keys hence the 2NF confirmation and as seen below:
Student_ID, Session_ID > Attendance_Record {full functional dependency}
- The table is in 3NF as there is no transitive dependencies on the composite keys
The Attendance table will give the attendance record of a student for a particular session. A table that combines primary keys from the School, Course, Semester, Module and Session will supply the universities timetable.
Final Schema descriptions (Table Names, keys, columns and meanings) have been done in the Relational Tables & Schema Normalization portions of this document.
Top comments (0)