Coursework Sample: Relational Database Design for a Student Records System

Model Answer

Introduction

This coursework presents the design of a relational database to support the student records system of a UK university department, covering students, the programmes they are registered on, the modules they study, and the marks they achieve. The brief requires a system free from update, insertion and deletion anomalies, meaning the design must be developed to at least Third Normal Form (3NF) (Connolly and Begg, 2014). Designing such a system is a routine but high-stakes task in practice, since a student records database sits at the centre of a university’s academic administration and any structural weakness in it can propagate incorrect marks, missing enrolments or orphaned records across downstream systems such as transcripts, progression boards and degree classification calculations. The following sections identify the core entities and their relationships, present a plausible unnormalised starting point together with the anomalies it would produce in practice, walk through the normalisation process from First Normal Form to 3NF, present the resulting logical schema, and provide the Structured Query Language (SQL) needed to implement it. A short evaluation then considers the trade-offs of full normalisation for a reporting-heavy system of this kind.

Requirements and Entity Analysis

A student records system of this kind must capture five core entities. A Student (uniquely identified by a StudentID) has attributes including full name, date of birth and an email address, and is registered on exactly one Programme at a time, such as BSc Computer Science. A Programme (identified by a ProgrammeID) belongs to a single owning Department. A Module (identified by a ModuleCode, such as CS205) has a name, a credit value, and belongs to a single Department, but may be studied by many students across many programmes, and a given student studies many modules over the course of their degree; this is therefore a many-to-many relationship, resolved by an associative entity, Enrolment, which records the mark a specific student achieved on a specific module in a specific academic year. A Lecturer (identified by a LecturerID) belongs to a Department and may lead one or more modules, while a module has exactly one nominated module leader in a given year.

The cardinalities can be summarised as follows: one Department has many Programmes, Modules and Lecturers (1:M in each case); one Programme has many Students (1:M); one Student has many Enrolments and one Module has many Enrolments, so Student and Module share a many-to-many relationship realised through Enrolment (M:M via an associative entity); and one Lecturer leads many Modules, but a given Module in a given academic year has exactly one leader (1:M). Correctly identifying these relationships before normalisation matters because the associative Enrolment entity, in particular, is where the mark — the most operationally important piece of data in the whole system — actually lives, and getting its composite key wrong would silently corrupt the academic record (Elmasri and Navathe, 2015). It is also worth noting what the design deliberately excludes at this scope: it does not model individual assessment components (coursework versus examination weightings) beneath the single overall module Mark, nor does it model staff outside the Lecturer role, such as personal tutors or administrators, both of which would be reasonable extensions to a production system but fall outside the brief for this coursework.

Unnormalised Data and the Case for Normalisation

A naive first attempt at a student records system might store all of this information in a single flat table, of the kind a non-specialist might build directly in a spreadsheet, as illustrated in Table 1 below for two students. Each row repeats the student’s personal details for every module they are enrolled on, and repeats the module and lecturer details for every student enrolled on that module.

Table 1: Unnormalised (1NF) student enrolment data, illustrating repeated student and module facts across rows.

This flat structure produces three classic anomalies once real data is entered (Codd, 1970; Date, 2003). First, an update anomaly: if Module CS205 changes its name, every row containing CS205 must be updated consistently, and a single missed row leaves the database internally contradictory. Second, an insertion anomaly: a new module cannot be added to the system until at least one student has enrolled on it, because the module’s details only exist as repeated fragments within student rows. Third, a deletion anomaly: if Priya Shah were to withdraw and her only remaining enrolment row were deleted, the fact that Dr Osei leads CS205 would be lost from the database entirely, even though that fact has nothing to do with Priya’s own enrolment. Normalisation is the systematic process of decomposing this flat structure into a set of smaller, related tables such that each fact is stored exactly once, eliminating these anomalies while preserving all of the original information via foreign key relationships (Silberschatz, Korth and Sudarshan, 2019).

First Normal Form (1NF)

1NF requires that every cell hold a single atomic value and that there be no repeating groups of columns, such as ModuleCode1, ModuleCode2 and ModuleCode3 for a student enrolled on several modules (Coronel and Morris, 2018). Table 1 already satisfies this requirement, since it has been flattened to one row per student-module pairing rather than storing repeating module columns; it is therefore taken as the 1NF starting point for the remainder of this walkthrough. Because no single column uniquely identifies a row, the primary key at this stage must be the composite (StudentID, ModuleCode). A single-column surrogate key such as an auto-incrementing EnrolmentID could also have been chosen at this point, but the composite natural key is retained here so that the functional-dependency reasoning in the following two sections remains visible and easy to follow.

Second Normal Form (2NF)

2NF requires that every non-key attribute be fully functionally dependent on the whole of the primary key, not merely part of it, a test that only becomes meaningful where the key is composite (Hoffer, Ramesh and Topi, 2015). In Table 1, StudentName and DoB depend only on StudentID, not on the full (StudentID, ModuleCode) key, and ModuleName and LecturerName depend only on ModuleCode, not on the full key; only Mark genuinely depends on the combination of both. These are partial dependencies, and 2NF is achieved by removing them into their own relations: Student(StudentID, StudentName, DoB) and a provisional Module(ModuleCode, ModuleName, LecturerName), leaving Enrolment(StudentID, ModuleCode, Mark) holding only the fact that genuinely depends on the composite key. At this stage the update anomaly identified earlier is already resolved for student data, since StudentName and DoB now exist in exactly one row per student regardless of how many modules that student takes.

Third Normal Form (3NF)

3NF additionally requires that no non-key attribute depend transitively on the primary key via another non-key attribute (Connolly and Begg, 2014). In the provisional Module relation above, LecturerName is not properly a fact about the module code itself; a lecturer belongs to a department and can be identified independently of any one module, and storing LecturerName as plain text repeats it for every module that lecturer leads, recreating precisely the update anomaly identified in Table 1 if a lecturer’s name changes. LecturerName is therefore removed to its own Lecturer relation identified by LecturerID, and Module instead stores a LecturerID foreign key. Applying the same transitive-dependency logic to Department — since DeptName depends on DeptID, which in turn depends on ModuleCode or LecturerID rather than being a direct fact about either — produces the final schema set out in Table 2. A useful test to apply at each stage is to ask, for every non-key attribute, whether it describes the entity named by the primary key directly, or only describes it by way of some other attribute; wherever the answer is the latter, a transitive dependency has been found and a further decomposition is required.

Final Logical Schema

Table 2: Final logical schema in Third Normal Form (3NF).

Every relation in Table 2 now has a primary key on which all other attributes are fully and non-transitively dependent, and every foreign key enforces a referential link back to the relation where that fact is stored once and only once, eliminating all three anomalies identified in the flat starting structure. In practice, the ProgrammeID, DeptID and LecturerID foreign key columns would also be indexed, since these are precisely the columns a student records system is most frequently queried and joined on, and an unindexed foreign key can turn what should be a fast lookup into a full table scan once the Student and Enrolment tables grow to realistic university size.

SQL Implementation

The schema in Table 2 translates directly into the Data Definition Language statements below, with each foreign key constraint enforcing the referential integrity of the corresponding relationship, followed by an example aggregate query that reports the average mark and cohort size for each module.

CREATE TABLE Department ( DeptID INT PRIMARY KEY, DeptName VARCHAR(100) NOT NULL ); CREATE TABLE Programme ( ProgrammeID INT PRIMARY KEY, ProgrammeName VARCHAR(100) NOT NULL, DeptID INT NOT NULL, FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); CREATE TABLE Student ( StudentID VARCHAR(10) PRIMARY KEY, StudentName VARCHAR(100) NOT NULL, DoB DATE NOT NULL, ProgrammeID INT NOT NULL, FOREIGN KEY (ProgrammeID) REFERENCES Programme(ProgrammeID) ); CREATE TABLE Lecturer ( LecturerID INT PRIMARY KEY, LecturerName VARCHAR(100) NOT NULL, DeptID INT NOT NULL, FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); CREATE TABLE Module ( ModuleCode VARCHAR(10) PRIMARY KEY, ModuleName VARCHAR(100) NOT NULL, Credits INT NOT NULL, DeptID INT NOT NULL, LecturerID INT NOT NULL, FOREIGN KEY (DeptID) REFERENCES Department(DeptID), FOREIGN KEY (LecturerID) REFERENCES Lecturer(LecturerID) ); CREATE TABLE Enrolment ( StudentID VARCHAR(10) NOT NULL, ModuleCode VARCHAR(10) NOT NULL, AcademicYear CHAR(9) NOT NULL, Mark DECIMAL(4,1), PRIMARY KEY (StudentID, ModuleCode, AcademicYear), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (ModuleCode) REFERENCES Module(ModuleCode) ); SELECT ModuleCode, AVG(Mark) AS AverageMark, COUNT(*) AS Cohort FROM Enrolment GROUP BY ModuleCode;

Applying this query to the two CS205 enrolments recorded in Table 1 provides a useful manual check on the query’s correctness before it is trusted against the full dataset: the recorded marks are 68 and 81, giving an average of (68 + 81) ÷ 2 = 74.5 and a cohort count of 2, which is exactly what the GROUP BY query above would be expected to return for ModuleCode = ‘CS205’ once both rows have been migrated into the normalised Enrolment table.

Evaluation

Full normalisation to 3NF is the correct default position for a student records system, because the integrity of the mark data is paramount and update, insertion or deletion anomalies of the kind shown in Table 1 could otherwise silently corrupt academic records (Kroenke and Auer, 2015). Referential integrity constraints on the foreign keys in Table 2 ensure, for example, that an Enrolment row cannot reference a StudentID or ModuleCode that does not exist, and that a Department cannot be deleted while Programmes, Modules or Lecturers still reference it, absent an explicit cascade rule. The principal cost of this design is query complexity: producing a single row per student per module with lecturer and department names now requires joining across five relations rather than reading from one flat table, which can affect performance on very large datasets and complicates ad-hoc reporting for non-technical staff. In practice, many institutions address this by retaining the normalised schema above as the transactional system of record, while building a separate, deliberately denormalised reporting layer or data warehouse for analytics, so that operational integrity and reporting convenience are not forced to compete within the same schema (Silberschatz, Korth and Sudarshan, 2019).

Conclusion

This coursework has designed a student records database that progresses from a flat, anomaly-prone starting structure to a fully normalised Third Normal Form schema comprising six related tables: Department, Programme, Student, Lecturer, Module and Enrolment. Each stage of the walkthrough demonstrated a concrete functional-dependency problem in the previous structure and the specific decomposition that resolved it, culminating in a schema where every fact is stored exactly once and referential integrity constraints prevent orphaned records. The accompanying SQL implementation and the worked average-mark calculation confirm that the design is not only theoretically sound but directly implementable and query-able against realistic data. Future extensions might add assessment-level detail beneath the single overall Mark attribute, or an audit table to track changes to student records over time.

References

• Codd, E.F. (1970) ‘A Relational Model of Data for Large Shared Data Banks’, Communications of the ACM, 13(6), pp. 377–387.
• Connolly, T. and Begg, C. (2014) Database Systems: A Practical Approach to Design, Implementation, and Management. 6th edn. Harlow: Pearson.
• Coronel, C. and Morris, S. (2018) Database Systems: Design, Implementation, and Management. 13th edn. Boston: Cengage.
• Date, C.J. (2003) An Introduction to Database Systems. 8th edn. Boston: Addison-Wesley.
• Elmasri, R. and Navathe, S.B. (2015) Fundamentals of Database Systems. 7th edn. Harlow: Pearson.
• Hoffer, J.A., Ramesh, V. and Topi, H. (2015) Modern Database Management. 12th edn. Harlow: Pearson.
• Kroenke, D.M. and Auer, D.J. (2015) Database Processing: Fundamentals, Design, and Implementation. 14th edn. Harlow: Pearson.
• Silberschatz, A., Korth, H.F. and Sudarshan, S. (2019) Database System Concepts. 7th edn. New York: McGraw-Hill.