◈ TASK_01 // DATABASE DESIGN

// 1(a) TABLE ANALYSIS

▸ Modules

Entity: Academic modules/courses

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

▸ Lecturers

Entity: Academic staff members

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

▸ Roles

Entity: Lecturer roles for modules

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

▸ ModuleLecturerRole

Entity: Junction table (Many-to-Many)

Column	Type	Key
moduleLecturerRoleID	INT	PRIMARY
moduleID	INT	FOREIGN → Modules
lecturerID	INT	FOREIGN → Lecturers
roleID	INT	FOREIGN → Roles

▸ Key Summary

• Modules: PK = moduleID
• Lecturers: PK = lecturerID
• Roles: PK = roleID
• ModuleLecturerRole: PK = moduleLecturerRoleID, FK = moduleID, lecturerID, roleID

// 1(b) EVENTS REGISTRATION

▸ Table Definitions

Table	PK	FK
Users	userID	—
Events	eventID	—
EventRegistrations	registrationID	userID, eventID

▸ Relationships

• Users → EventRegistrations: One-to-Many
• Events → EventRegistrations: One-to-Many
• Users ↔ Events: Many-to-Many (via junction)

▸ SQL 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)
);