STEPS TO CREATE PHYSICAL ERD

1.      Download and install XAMPP

2.      Run and start the MySQL server

3.      Download and install DBeaver

4.      Connect with Xampp MySQL server

5.      Create and use a database from SQL Editor

CREATE DATABASE loe_academy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
USE loe_academy;

1. Refresh and Create Tables one by one to log errors if any errors occurred during SQL query execution

-- Core reference tables
CREATE TABLE IF NOT EXISTS pantheon (
  pantheon_id INT AUTO_INCREMENT PRIMARY KEY,
  pantheon_name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pathway (
  pathway_id INT AUTO_INCREMENT PRIMARY KEY,
  pathway_name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS year_group (
  year_group_id INT AUTO_INCREMENT PRIMARY KEY,
  year_name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS building (
  building_id INT AUTO_INCREMENT PRIMARY KEY,
  building_name VARCHAR(120) NOT NULL,
  building_code VARCHAR(30) UNIQUE,
  location_description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS room (
  room_id INT AUTO_INCREMENT PRIMARY KEY,
  building_id INT NOT NULL,
  room_name_or_number VARCHAR(50) NOT NULL,
  capacity INT NOT NULL,
  notes VARCHAR(255),
  CONSTRAINT fk_room_building
    FOREIGN KEY (building_id) REFERENCES building(building_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS manager (
  manager_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  contact_number VARCHAR(30),
  email VARCHAR(120)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS teacher (
  teacher_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  subject_specialism_text VARCHAR(150) NOT NULL,
  pantheon_id INT NOT NULL,
  manager_id INT NOT NULL,
  job_title VARCHAR(80),
  pay_scale_grade VARCHAR(30),
  CONSTRAINT fk_teacher_pantheon
    FOREIGN KEY (pantheon_id) REFERENCES pantheon(pantheon_id),
  CONSTRAINT fk_teacher_manager
    FOREIGN KEY (manager_id) REFERENCES manager(manager_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  home_address VARCHAR(255) NOT NULL,
  term_address VARCHAR(255) NOT NULL,
  primary_contact_number VARCHAR(30) NOT NULL,
  email VARCHAR(120),
  boarding_status VARCHAR(30),
  pantheon_id INT NOT NULL,
  pathway_id INT NOT NULL,
  CONSTRAINT fk_student_pantheon
    FOREIGN KEY (pantheon_id) REFERENCES pantheon(pantheon_id),
  CONSTRAINT fk_student_pathway
    FOREIGN KEY (pathway_id) REFERENCES pathway(pathway_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS emergency_contact (
  emergency_contact_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  contact_name VARCHAR(120) NOT NULL,
  relationship_to_student VARCHAR(60),
  phone_number VARCHAR(30) NOT NULL,
  email VARCHAR(120),
  priority_order INT,
  CONSTRAINT fk_ec_student
    FOREIGN KEY (student_id) REFERENCES student(student_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subject (
  subject_id INT AUTO_INCREMENT PRIMARY KEY,
  subject_name VARCHAR(120) NOT NULL UNIQUE,
  is_core_subject TINYINT(1),
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS speciality (
  speciality_id INT AUTO_INCREMENT PRIMARY KEY,
  speciality_name VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS class (
  class_id INT AUTO_INCREMENT PRIMARY KEY,
  class_code VARCHAR(50) NOT NULL UNIQUE,
  room_id INT NOT NULL,
  teacher_id INT NULL,
  subject_id INT NOT NULL,
  year_group_id INT NULL,
  CONSTRAINT fk_class_room
    FOREIGN KEY (room_id) REFERENCES room(room_id),
  CONSTRAINT fk_class_teacher
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
  CONSTRAINT fk_class_subject
    FOREIGN KEY (subject_id) REFERENCES subject(subject_id),
  CONSTRAINT fk_class_year
    FOREIGN KEY (year_group_id) REFERENCES year_group(year_group_id)
) ENGINE=InnoDB;

-- Catering
CREATE TABLE IF NOT EXISTS meal_type (
  meal_type_id INT AUTO_INCREMENT PRIMARY KEY,
  meal_type_name VARCHAR(40) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meal (
  meal_id INT AUTO_INCREMENT PRIMARY KEY,
  meal_name VARCHAR(120) NOT NULL,
  meal_type_id INT NOT NULL,
  description VARCHAR(255),
  CONSTRAINT fk_meal_type
    FOREIGN KEY (meal_type_id) REFERENCES meal_type(meal_type_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ingredient (
  ingredient_id INT AUTO_INCREMENT PRIMARY KEY,
  ingredient_name VARCHAR(120) NOT NULL UNIQUE,
  shelf_life_days INT NOT NULL,
  storage_conditions VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- Finance
CREATE TABLE IF NOT EXISTS grants (
  grant_id INT AUTO_INCREMENT PRIMARY KEY,
  grant_name VARCHAR(150) NOT NULL,
  provider_name VARCHAR(150),
  start_date DATE,
  end_date DATE,
  total_amount DECIMAL(12,2)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bursary (
  bursary_id INT AUTO_INCREMENT PRIMARY KEY,
  bursary_name VARCHAR(150),
  grant_id INT NOT NULL,
  amount DECIMAL(10,2),
  start_date DATE,
  end_date DATE,
  eligibility_notes VARCHAR(255),
  CONSTRAINT fk_bursary_grant
    FOREIGN KEY (grant_id) REFERENCES grants(grant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_date DATE NOT NULL,
  payment_method VARCHAR(40),
  payer_name VARCHAR(120),
  reference_number VARCHAR(80),
  CONSTRAINT fk_payment_student
    FOREIGN KEY (student_id) REFERENCES student(student_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_class (
  student_id INT NOT NULL,
  class_id INT NOT NULL,
  assigned_date DATE,
  note VARCHAR(255),
  PRIMARY KEY (student_id, class_id),
  CONSTRAINT fk_sc_student
    FOREIGN KEY (student_id) REFERENCES student(student_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_sc_class
    FOREIGN KEY (class_id) REFERENCES class(class_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meal_ingredient (
  meal_id INT NOT NULL,
  ingredient_id INT NOT NULL,
  quantity VARCHAR(40),
  PRIMARY KEY (meal_id, ingredient_id),
  CONSTRAINT fk_mi_meal
    FOREIGN KEY (meal_id) REFERENCES meal(meal_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_mi_ingredient
    FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_speciality (
  student_id INT NOT NULL,
  speciality_id INT NOT NULL,
  start_date DATE,
  PRIMARY KEY (student_id, speciality_id),
  CONSTRAINT fk_ss_student
    FOREIGN KEY (student_id) REFERENCES student(student_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_ss_speciality
    FOREIGN KEY (speciality_id) REFERENCES speciality(speciality_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subject_year_group (
  subject_id INT NOT NULL,
  year_group_id INT NOT NULL,
  PRIMARY KEY (subject_id, year_group_id),
  CONSTRAINT fk_syg_subject
    FOREIGN KEY (subject_id) REFERENCES subject(subject_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_syg_year
    FOREIGN KEY (year_group_id) REFERENCES year_group(year_group_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_bursary (
  student_id INT NOT NULL,
  bursary_id INT NOT NULL,
  award_date DATE,
  PRIMARY KEY (student_id, bursary_id),
  CONSTRAINT fk_sb_student
    FOREIGN KEY (student_id) REFERENCES student(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_sb_bursary
    FOREIGN KEY (bursary_id) REFERENCES bursary(bursary_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

1. View Diagram and Export it as a png or any required format

Conceptual Diagram Code for

Table Pantheon {
  PantheonID int [pk, increment]
  PantheonName varchar(100) [not null, unique]
  Description varchar(255)
}

Table Pathway {
  PathwayID int [pk, increment]
  PathwayName varchar(100) [not null, unique]
  Description varchar(255)
}

Table YearGroup {
  YearGroupID int [pk, increment]
  YearName varchar(50) [not null, unique]
  Description varchar(255)
}

Table Building {
  BuildingID int [pk, increment]
  BuildingName varchar(120) [not null]
  BuildingCode varchar(30) [unique]
  LocationDescription varchar(255)
}

Table Room {
  RoomID int [pk, increment]
  BuildingID int [not null]           
  RoomNameOrNumber varchar(50) [not null]
  Capacity int [not null]
  Notes varchar(255)
}

Table Manager {
  ManagerID int [pk, increment]
  FullName varchar(120) [not null]
  ContactNumber varchar(30)
  Email varchar(120)
}

Table Teacher {
  TeacherID int [pk, increment]
  FullName varchar(120) [not null]
  SubjectSpecialismText varchar(150) [not null]
  PantheonID int [not null]           
  ManagerID int [not null]            
  JobTitle varchar(80)
  PayScaleGrade varchar(30)
}

Table Student {
  StudentID int [pk, increment]
  FullName varchar(120) [not null]
  HomeAddress varchar(255) [not null]
  TermAddress varchar(255) [not null]
  PrimaryContactNumber varchar(30) [not null]
  Email varchar(120)
  BoardingStatus varchar(30)
  PantheonID int [not null]            
  PathwayID int [not null]            
}

Table EmergencyContact {
  EmergencyContactID int [pk, increment]
  StudentID int [not null]             
  ContactName varchar(120) [not null]
  RelationshipToStudent varchar(60)
  PhoneNumber varchar(30) [not null]
  Email varchar(120)
  PriorityOrder int
}

Table Subject {
  SubjectID int [pk, increment]
  SubjectName varchar(120) [not null, unique]
  IsCoreSubject boolean
  Description varchar(255)
}

Table Speciality {
  SpecialityID int [pk, increment]
  SpecialityName varchar(120) [not null, unique]
  Description varchar(255)
}

Table Class {
  ClassID int [pk, increment]
  ClassCode varchar(50) [not null, unique]
  RoomID int [not null]               
  TeacherID int [not null]                      
  SubjectID int [not null]                       
  YearGroupID int [not null]                      
}

Table MealType {
  MealTypeID int [pk, increment]
  MealTypeName varchar(40) [not null, unique] 
  Description varchar(255)
}

Table Meal {
  MealID int [pk, increment]
  MealName varchar(120) [not null]
  MealTypeID int [not null]           
  Description varchar(255)
}

Table Ingredient {
  IngredientID int [pk, increment]
  IngredientName varchar(120) [not null, unique]
  ShelfLifeDays int [not null]
  StorageConditions varchar(255) [not null]
}

Table Payment {
  PaymentID int [pk, increment]
  StudentID int [not null]             
  Amount decimal(10,2) [not null]
  PaymentDate date [not null]
  PaymentMethod varchar(40)
  PayerName varchar(120)
  ReferenceNumber varchar(80)
}

Table Grant {
  GrantID int [pk, increment]
  GrantName varchar(150) [not null]
  ProviderName varchar(150)
  StartDate date
  EndDate date
  TotalAmount decimal(12,2)
}

Table Bursary {
  BursaryID int [pk, increment]
  BursaryName varchar(150)
  GrantID int [not null]              
  Amount decimal(10,2)
  StartDate date
  EndDate date
  EligibilityNotes varchar(255)
}

/* ----------------------------
   Junction tables (M:N)
   ---------------------------- */

Table StudentClass {
  StudentID int [not null]
  ClassID int [not null]
  AssignedDate date
  Note varchar(255)

  Indexes {
    (StudentID, ClassID) [pk]
  }
}

Table MealIngredient {
  MealID int [not null]
  IngredientID int [not null]
  Quantity varchar(40)

  Indexes {
    (MealID, IngredientID) [pk]
  }
}

Table StudentSpeciality {
  StudentID int [not null]
  SpecialityID int [not null]
  StartDate date

  Indexes {
    (StudentID, SpecialityID) [pk]
  }
}

Table SubjectYearGroup {
  SubjectID int [not null]
  YearGroupID int [not null]

  Indexes {
    (SubjectID, YearGroupID) [pk]
  }
}

Table StudentBursary {
  StudentID int [not null]
  BursaryID int [not null]
  AwardDate date

  Indexes {
    (StudentID, BursaryID) [pk]
  }
}

/* ----------------------------
   Relationships (FK refs)
   ---------------------------- */

Ref: Room.BuildingID > Building.BuildingID

Ref: Teacher.PantheonID > Pantheon.PantheonID
Ref: Teacher.ManagerID > Manager.ManagerID

Ref: Student.PantheonID > Pantheon.PantheonID
Ref: Student.PathwayID > Pathway.PathwayID

Ref: EmergencyContact.StudentID > Student.StudentID

Ref: Class.RoomID > Room.RoomID
Ref: Class.TeacherID > Teacher.TeacherID
Ref: Class.SubjectID > Subject.SubjectID
Ref: Class.YearGroupID > YearGroup.YearGroupID

Ref: Meal.MealTypeID > MealType.MealTypeID

Ref: Payment.StudentID > Student.StudentID

Ref: Bursary.GrantID > Grant.GrantID

Ref: StudentClass.StudentID > Student.StudentID
Ref: StudentClass.ClassID > Class.ClassID

Ref: MealIngredient.MealID > Meal.MealID
Ref: MealIngredient.IngredientID > Ingredient.IngredientID

Ref: StudentSpeciality.StudentID > Student.StudentID
Ref: StudentSpeciality.SpecialityID > Speciality.SpecialityID

Ref: SubjectYearGroup.SubjectID > Subject.SubjectID
Ref: SubjectYearGroup.YearGroupID > YearGroup.YearGroupID

Ref: StudentBursary.StudentID > Student.StudentID
Ref: StudentBursary.BursaryID > Bursary.BursaryID