<?= e($title) ?> | ICT3612 Assessment 3

//////////////////////////// common.php //////////////////////////// <?= e($title) ?> | ICT3612 Assessment 3

Source code used for this task

[ 'entity' => 'A module offered by the institution, identified by its module code.', 'primary' => 'ModuleCode', 'foreign' => 'None', ], 'Lecturers' => [ 'entity' => 'A lecturer who can perform one or more roles for modules.', 'primary' => 'LecturerID', 'foreign' => 'None', ], 'Roles' => [ 'entity' => 'A recognised responsibility, such as primary lecturer or internal moderator.', 'primary' => 'RoleID', 'foreign' => 'None', ], 'ModuleLecturerRole' => [ 'entity' => 'An associative table recording which lecturer performs which role for a module in a given year.', 'primary' => 'ModuleCode + LecturerID + RoleID + Year (composite key)', 'foreign' => 'ModuleCode â†’ Modules.ModuleCode; LecturerID â†’ Lecturers.LecturerID; RoleID â†’ Roles.RoleID', ], ]; $eventEntities = [ 'Users' => ['UserID (PK)', 'FullName', 'Email'], 'Registrations' => ['UserID (PK, FK)', 'EventID (PK, FK)', 'RegisteredAt', 'AttendanceStatus'], 'Events' => ['EventID (PK)', 'EventName', 'EventDate', 'Venue'], ]; render_page_start('Task 1 Â· Database Design'); include 'menu.inc'; ?>

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.

$details): ?>

Primary key:

Foreign key(s):

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.

1 â”€â”€â”€ M

M â”€â”€â”€ 1

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.