Task 1 - Database Design Analysis

1(a) - Database Table Analysis

Table: Modules

Entity: Represents academic modules/courses offered by the institution.

Column	Type	Key
moduleID	INT	PRIMARY KEY
moduleCode	VARCHAR(20)
moduleName	VARCHAR(100)

Table: Lecturers

Entity: Represents lecturers/academic staff members.

Column	Type	Key
lecturerID	INT	PRIMARY KEY
firstName	VARCHAR(50)
lastName	VARCHAR(50)
email	VARCHAR(100)

Table: Roles

Entity: Represents different roles that lecturers can have for modules.

Column	Type	Key
roleID	INT	PRIMARY KEY
roleName	VARCHAR(50)
roleDescription	TEXT

Table: ModuleLecturerRole

Entity: Junction/associative table linking modules, lecturers, and their roles.

Column	Type	Key
moduleLecturerRoleID	INT	PRIMARY KEY
moduleID	INT	FOREIGN KEY (references Modules)
lecturerID	INT	FOREIGN KEY (references Lecturers)
roleID	INT	FOREIGN KEY (references Roles)

Primary and Foreign Keys Summary:

• Modules: Primary Key - moduleID
• Lecturers: Primary Key - lecturerID
• Roles: Primary Key - roleID
• ModuleLecturerRole:
  • Primary Key - moduleLecturerRoleID
  • Foreign Key - moduleID (references Modules.moduleID)
  • Foreign Key - lecturerID (references Lecturers.lecturerID)
  • Foreign Key - roleID (references Roles.roleID)

1(b) - Events Registration Database Design

Table Definitions:

Users Table

Column	Data Type	Key
userID	INT	PRIMARY KEY (AUTO_INCREMENT)
username	VARCHAR(50)	UNIQUE
email	VARCHAR(100)	UNIQUE
password	VARCHAR(255)
fullName	VARCHAR(100)

Events Table

Column	Data Type	Key
eventID	INT	PRIMARY KEY (AUTO_INCREMENT)
eventName	VARCHAR(100)
eventDate	DATETIME
location	VARCHAR(200)
description	TEXT
capacity	INT

EventRegistrations Table (Junction Table)

Column	Data Type	Key
registrationID	INT	PRIMARY KEY (AUTO_INCREMENT)
userID	INT	FOREIGN KEY (references Users.userID)
eventID	INT	FOREIGN KEY (references Events.eventID)
registrationDate	DATETIME
status	VARCHAR(20)

Relationships:

• Users to EventRegistrations: One-to-Many (One user can register for multiple events)
• Events to EventRegistrations: One-to-Many (One event can have multiple registrations)
• Users to Events: Many-to-Many (Through EventRegistrations junction table)

SQL Creation Script:

CREATE TABLE Users (
    userID INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    fullName VARCHAR(100) NOT NULL
);

CREATE TABLE Events (
    eventID INT AUTO_INCREMENT PRIMARY KEY,
    eventName VARCHAR(100) NOT NULL,
    eventDate DATETIME NOT NULL,
    location VARCHAR(200) NOT NULL,
    description TEXT,
    capacity INT DEFAULT 0
);

CREATE TABLE EventRegistrations (
    registrationID INT AUTO_INCREMENT PRIMARY KEY,
    userID INT NOT NULL,
    eventID INT NOT NULL,
    registrationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Registered',
    FOREIGN KEY (userID) REFERENCES Users(userID) ON DELETE CASCADE,
    FOREIGN KEY (eventID) REFERENCES Events(eventID) ON DELETE CASCADE,
    UNIQUE KEY unique_registration (userID, eventID)
);