Modules
A module offered by the institution, identified by its module code.
Primary key: ModuleCode
Foreign key(s): None
Task 1(a)
Entities, primary keys and foreign keys
The original design resolves the many-to-many relationship between modules, lecturers and roles through ModuleLecturerRole.
Lecturers
A lecturer who can perform one or more roles for modules.
Primary key: LecturerID
Foreign key(s): None
Roles
A recognised responsibility, such as primary lecturer or internal moderator.
Primary key: RoleID
Foreign key(s): None
ModuleLecturerRole
An associative table recording which lecturer performs which role for a module in a given year.
Primary key: ModuleCode + LecturerID + RoleID + Year (composite key)
Foreign key(s): ModuleCode → Modules.ModuleCode; LecturerID → Lecturers.LecturerID; RoleID → Roles.RoleID
Task 1(b)
Users and events database diagram
A user may register for many events and an event may have many users. The Registrations associative entity resolves this many-to-many relationship.
Users
- UserID (PK)
- FullName
1 ─── M
Registrations
- UserID (PK, FK)
- EventID (PK, FK)
- RegisteredAt
- AttendanceStatus
M ─── 1
Events
- EventID (PK)
- EventName
- EventDate
- Venue
Keys: Users.UserID and Events.EventID are primary keys. Registrations has the composite primary key (UserID, EventID); both columns are also foreign keys referencing their parent tables.
Source code used for this task