Student Course Registration System

// Database Design & Programming — HAMK · MySQL 8.0 · Team 7: Sazzad, Iram, Enamul, Sabiha

Entity–Relationship Diagram — Crow's Foot Notation

Entities & Relationships

Entity	Type	Key

Relationships

Interactive SQL Query Runner

mysql>

Database: studentregistration · Engine: MySQL 8.0 · Tables: students, instructors, courses, enrollment

Live Stats

Normalisation Proof — 1NF → 2NF → 3NF

1NF

Remove repeating groups. Each student record holds single (atomic) values per column. Phone numbers extracted to separate STUDENT_PHONE table. No multi-valued fields remain.

2NF

Remove partial dependencies. Every non-key attribute depends on the whole primary key. SECTION uses composite PK (CourseID, SectionNo) — all section attributes depend on both parts.

3NF

Remove transitive dependencies. Instructor data separated into its own table — no student attribute transitively depends on InstructorID. GPA is a derived attribute, not stored.

Schema Diagram (Relational)

Constraints Applied

🔑 PRIMARY KEY on all tables
🔗 FOREIGN KEY + ON DELETE CASCADE
✅ UNIQUE on Email columns
🔢 CHECK (Credits > 0)
🚫 NOT NULL on Name, Email
⚠️ Composite PK: SECTION(CourseID, SectionNo)
⚠️ Composite PK: ENROLLMENT(StudentID, CourseID, SectionNo)
No student can register for the same section twice.

DDL — CREATE TABLE statements

CREATE TABLE students (
    StudentID  INT          PRIMARY KEY,
    Name       VARCHAR(100) NOT NULL,
    Email      VARCHAR(100) UNIQUE NOT NULL,
    Major      VARCHAR(50),
    Year       VARCHAR(20)
);

CREATE TABLE instructors (
    InstructorID INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    Email        VARCHAR(100) UNIQUE NOT NULL,
    Department   VARCHAR(50)
);

CREATE TABLE courses (
    CourseID     INT          PRIMARY KEY,
    CourseName   VARCHAR(100) NOT NULL,
    Credits      INT          CHECK (Credits > 0),
    InstructorID INT,
    FOREIGN KEY (InstructorID) REFERENCES instructors(InstructorID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE enrollment (
    EnrollmentID INT         PRIMARY KEY,
    StudentID    INT,
    CourseID     INT,
    Semester     VARCHAR(20),
    Grade        VARCHAR(2),
    FOREIGN KEY (StudentID) REFERENCES students(StudentID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES courses(CourseID)
        ON DELETE CASCADE ON UPDATE CASCADE
);

DML — Sample INSERT statements

INSERT INTO students VALUES
(10566, 'Enamul Choton',       'enamulchoton@gmail.com',  'Robotics', '2024'),
(100255,'Iram Tanvir Mahtab',  'iramtanvir3@gmail.com',   'Robotics', '2024'),
(100355,'Shazzad',             'shazzad@gmail.com',        'Robotics', '2024'),
(100655,'Rafi Ahmed',          'rafiahmed@gmail.com',      'Robotics', '2024'),
(106661,'Sabiha',              'sabiha@gmail.com',         'Robotics', '2024');

INSERT INTO instructors VALUES
(1, 'Pacheo Fernando', 'pacheo.fernendo@hamk.fi', 'Computer Science'),
(2, 'Miika Sorvali',   'miika.sorvali@hamk.fi',   'Mathematics'),
(3, 'Menhaz Khan',     'mehnaz.khan@hamk.fi',     'Information Systems');

INSERT INTO courses VALUES
(12235, 'Linear Algebra',       3, 2),
(20065, 'Information Security', 3, 3),
(30001, 'Database Systems',     3, 1),
(50043, 'Data Structures',      4, 1),
(56933, 'Operating Systems',    4, 1);