Database for an Academy that provide trainings
Create Database MetigatorAcademy;
Go
Use MetigatorAcademy;| Id | Course Name | Price |
|---|---|---|
| 1 | Mathematics | $1000.00 |
| 2 | Physics | $2000.00 |
| 3 | Chemistry | $1500.00 |
| 4 | Full Stack Development | $1200.00 |
| 5 | Automation Testing | $3000.00 |
-- Courses Table
CREATE TABLE Courses (
Id INT PRIMARY KEY,
CourseName VARCHAR(255) NOT NULL,
Price DECIMAL(15, 2) NOT NULL
);
-- Inserting data for Courses
INSERT INTO Courses (Id, CourseName, Price) VALUES (1, 'Mathematics', 1000.00);
INSERT INTO Courses (Id, CourseName, Price) VALUES (2, 'Physics', 2000.00);
INSERT INTO Courses (Id, CourseName, Price) VALUES (3, 'Chemistry', 1500.00);
INSERT INTO Courses (Id, CourseName, Price) VALUES (4, 'Full Stack Development', 1200.00);
INSERT INTO Courses (Id, CourseName, Price) VALUES (5, 'Automation Testing', 3000.00);
| Id | Office Name | Location |
|---|---|---|
| 1 | Off_05 | Building A |
| 2 | Off_12 | building B |
| 3 | Off_32 | Adminstration |
| 4 | Off_44 | IT Department |
| 5 | Off_43 | IT Department |
-- Courses Table
CREATE TABLE Offices (
Id INT PRIMARY KEY,
OfficeName VARCHAR(50) NOT NULL,
OfficeLocation VARCHAR(50) NOT NULL
);
| Id | Name | Location |
|---|---|---|
| 1 | Ahmed Abdullah | Off_05 |
| 2 | Yasmeen Mohammed | Off_12 |
| 3 | Khalid Hassan | Off_32 |
| 4 | Nadia Ali | Off_44 |
| 5 | Omar Ibrahim | Off_43 |
-- Instructors Table
CREATE TABLE Instructors (
Id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
OfficeId INT NOT NULL REFERENCES Offices(Id),
);
| Id | Title | SUN | MON | TUE | WED | THU | FRI | SAT |
|---|---|---|---|---|---|---|---|---|
| 1 | Daily | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ||
| 2 | DayAfterDay | ✔️ | ✔️ | ✔️ | ||||
| 3 | Twice-a-Week | ✔️ | ✔️ | |||||
| 4 | Weekend | ✔️ | ✔️ | |||||
| 5 | Compact | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
-- Schedules Table
CREATE TABLE Schedules
(
Id INT PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
SUN BIT NOT NULL,
MON BIT NOT NULL,
TUE BIT NOT NULL,
WED BIT NOT NULL,
THU BIT NOT NULL,
FRI BIT NOT NULL,
SAT BIT NOT NULL
);
| Id | Course Name | Section Name | Instructor Name | ScheduleTitle | Start Time | End Time | SUN | MON | TUE | WED | THU | FRI | SAT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Mathematics | S_MA1 | Ahmed Abdullah | DayAfterDay | 10:00:00 | 12:00:00 | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ||
| 2 | Mathematics | S_MA2 | Yasmeen Mohammed | Twice-a-Week | 14:00:00 | 16:00:00 | ✔️ | ✔️ | |||||
| 3 | Physics | S_PH1 | Ahmed Abdullah | Weekend | 16:00:00 | 18:00:00 | ✔️ | ✔️ | |||||
| 4 | Physics | S_PH2 | Khalid Hassan | Daily | 08:00:00 | 10:00:00 | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ||
| 5 | Chemistry | S_CH1 | Yasmeen Mohammed | Daily | 10:00:00 | 12:00:00 | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ||
| 6 | Chemistry | S_CH2 | Khalid Hassan | DayAfterDay | 14:00:00 | 16:00:00 | ✔️ | ✔️ | ✔️ | ||||
| 7 | Full Stack Dev. | S_FSD | Nadia Ali | Twice-a-Week | 16:00:00 | 18:00:00 | ✔️ | ✔️ | |||||
| 8 | Automation Test. | S_AUT | Omar Ibrahim | Weekend | 08:00:00 | 10:00:00 | ✔️ | ✔️ | |||||
| 9 | Full Stack Dev. | S_FSD | Nadia Ali | Weekend | 10:00:00 | 15:00:00 | ✔️ | ✔️ | |||||
| 10 | Automation Test. | S_AUT | Omar Ibrahim | Twice-a-Week | 14:00:00 | 16:00:00 | ✔️ | ✔️ | |||||
| 11 | Full Stack Dev. | S_FSD | Nadia Ali | Compact | 09:00:00 | 11:00:00 | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
-- sections table
CREATE TABLE Sections (
Id INT PRIMARY KEY,
SectionName VARCHAR(50) NOT NULL,
StartTime time(7) NOT NULL,
EndTime time(7) NOT NULL,
ScheduleId INT NOT NULL REFERENCES Schedules(Id)
CourseId INT,
InstructorId INT,
FOREIGN KEY (CourseId) REFERENCES Courses(Id),
FOREIGN KEY (InstructorId) REFERENCES Instructors(Id)
);
Requirement
- Student Table to be renamed to participant
- Two type of participant (individual, corporate)
// Participant Table
-- Students Table
public class Participants
{
public int Id {get; set;}
public string FName {get; set;}
public string LName {get; set;}
}
// Individual (الأفراد)
public class IndividualParticipant : Participant
{
public string University {get; set;}
public int YearOfGraduation {get; set;}
public bool IsIntern {get; set;}
}
// Corporate (الشركات)dfd
public class CorporateParticipant : Participant
{
public string Company { get; set; }
public string JobTitle { get; set; }
}TPH (1 table per heirarchy)
- Participant Table
| Id | FName | LName | Participant_Type | University | Year Of Graduation | IsIntern | Company | JobTitle |
|---|---|---|---|---|---|---|---|---|
| 1 | Fatima | Ali | I | XYZ | 2024 | |||
| 2 | Noor | Saleh | C | POQ | 2023 | ✔️ | ||
| 3 | Omar | Youssef | I | ABC | Developer | |||
| 4 | Huda | Ahmed | C | ABC | Developer | |||
| 5 | Amira | Tariq | I | POQ | 2025 | |||
| 6 | Zainab | Ismail | I | POQ | 2023 | ✔️ | ||
| 7 | Yousef | Farid | C | EFG | QA | |||
| 8 | Layla | Mustafa | C | EFG | QA | |||
| 9 | Mohammed | Adel | I | XYZ | 2024 | |||
| 10 | Samira | Nabil | I | XYZ | 2024 |
CREATE TABLE Participants (
Id INTEGER NOT NULL PRIMARY KEY,
FName NVARCHAR(50) NOT NULL,
LName NVARCHAR(50) NOT NULL,
ParticipantType VARCHAR(1) NOT NULL,
University NVARCHAR(50) NULL,
YearOfGraduation INT NULL,
IsIntern bit NULL,
Company NVARCHAR(50) NULL,
JobTitle NVARCHAR(50) NULL
);TPT Table Per Type (1 table per type)
- Participant Table
| Id | FName | LName |
|---|---|---|
| 1 | Fatima | Ali |
| 2 | Noor | Saleh |
| 3 | Omar | Youssef |
| 4 | Huda | Ahmed |
| 5 | Amira | Tariq |
| 6 | Zainab | Ismail |
| 7 | Yousef | Farid |
| 8 | Layla | Mustafa |
| 9 | Mohammed | Adel |
| 10 | Samira | Nabil |
-- Participants
CREATE TABLE Participants (
Id INT PRIMARY KEY,
FName NVARCHAR(50) NOT NULL
);- Individual Table
| Id | University | Year Of Graduation | IsIntern | ParticipantId (FK) |
|---|---|---|---|---|
| 1 | XYZ | 2024 | 1 | |
| 2 | POQ | 2023 | ✔️ | 2 |
| 3 | POQ | 2025 | 5 | |
| 4 | POQ | 2023 | ✔️ | 5 |
| 5 | XYZ | 2024 | 9 | |
| 6 | XYZ | 2024 | 10 |
-- Individual
CREATE TABLE IndividualParticipants (
Id INT PRIMARY KEY,
University NVARCHAR(50) NULL,
YearOfGraduation INT NULL,
IsIntern bit NULL,
ParticipantId INT NOT NULL REFERENCES Participants("Id")
);- Corporate Table
| Id | Company | JobTitle | ParticipantId |
|---|---|---|---|
| 1 | ABC | Developer | 3 |
| 2 | ABC | Developer | 4 |
| 3 | EFG | QA | 7 |
| 4 | EFG | QA | 8 |
-- Corporate
CREATE TABLE Corporates (
Id INT PRIMARY KEY,
Company NVARCHAR(50) NOT NULL
JobTitle NVARCHAR(50) NOT NULL
ParticipantId INT NOT NULL REFERENCES Participants("Id")
);TPC Table Per Concrete Type (1 table per concrete type)
- Individual Table
| Id | FName | LName | University | Year Of Graduation | IsIntern |
|---|---|---|---|---|---|
| 1 | Fatima | Ali | XYZ | 2024 | |
| 2 | Noor | Saleh | POQ | 2023 | ✔️ |
| 5 | Amira | Tariq | POQ | 2025 | |
| 6 | Zainab | Ismail | POQ | 2023 | ✔️ |
| 9 | Mohammed | Adel | XYZ | 2024 | |
| 10 | Samira | Nabil | XYZ | 2024 |
-- Individuals
CREATE TABLE Individual (
Id INTEGER NOT NULL PRIMARY KEY,
FName NVARCHAR(50) NOT NULL,
LName NVARCHAR(50) NOT NULL,
University NVARCHAR(50) NOT NULL,
YearOfGraduation INT NOT NULL,
IsIntern bit NOT NULL,
);- Corporates Table
| Id | FName | LName | Company | JobTitle |
|---|---|---|---|---|
| 3 | Omar | Youssef | ABC | Developer |
| 4 | Huda | Ahmed | ABC | Developer |
| 7 | Yousef | Farid | EFG | QA |
| 8 | Layla | Mustafa | EFG | QA |
-- CorporateParticipants
CREATE TABLE CorporateParticipants (
Id INT PRIMARY KEY,
FName NVARCHAR(50) NOT NULL,
LName NVARCHAR(50) NOT NULL,
Company NVARCHAR(50) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL
);| Section Name | Participant |
|---|---|
| S_MA2 | Fatima Ali |
| S_MA2 | Noor Saleh |
| S_PH1 | Omar Youssef |
| S_PH1 | Huda Ahmed |
| S_PH2 | Amira Tariq |
| S_PH2 | Zainab Ismail |
| S_BI1 | Yousef Farid |
| S_BI1 | Layla Mustafa |
| S_CS1 | Mohammed Adel |
| S_CS2 | Samira Nabil |
-- Enrollments Table
CREATE TABLE Enrollments (
ParticipantId INT,
SectionId INT,
PRIMARY KEY (ParticipantId, SectionId),
FOREIGN KEY (ParticipantId) REFERENCES Participants(Id),
FOREIGN KEY (SectionId) REFERENCES Sections(Id)
);