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 |
Biology |
$1200.00 |
5 |
Computer Science |
$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, 'Biology', 1200.00);
INSERT INTO Courses (Id, CourseName, Price) VALUES (5, 'Computer Science', 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
);
-- Inserting data for Offices
INSERT INTO Courses (Id, OfficeName, OfficeLocation) VALUES (1, 'Off_05', building A);
INSERT INTO Courses (Id, OfficeName, OfficeLocation) VALUES (2, 'Off_12', building B);
INSERT INTO Courses (Id, OfficeName, OfficeLocation) VALUES (3, 'Off_32', Adminstration);
INSERT INTO Courses (Id, OfficeName, OfficeLocation) VALUES (4, 'Off_44', IT Department);
INSERT INTO Courses (Id, OfficeName, OfficeLocation) VALUES (5, 'Off_43', IT Department);
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),
);
-- Inserting data for Instructors
INSERT INTO Instructors (Id, Name, OfficeId) VALUES (1, 'Ahmed Abdullah', 1);
INSERT INTO Instructors (Id, Name, OfficeId) VALUES (2, 'Yasmeen Mohammed', 2);
INSERT INTO Instructors (Id, Name, OfficeId) VALUES (3, 'Khalid Hassan', 3);
INSERT INTO Instructors (Id, Name, OfficeId) VALUES (4, 'Nadia Ali', 4);
INSERT INTO Instructors (Id, Name, OfficeId) VALUES (5, 'Omar Ibrahim', 5);
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
);
-- Inserting data for Schedules
INSERT INTO Schedules (Id, Title, SUN, MON, TUE, WED, THU, FRI, SAT) VALUES (1, 'Daily', 1, 1, 1, 1, 1, 0, 0);
INSERT INTO Schedules (Id, Title, SUN, MON, TUE, WED, THU, FRI, SAT) VALUES (2, 'DayAfterDay', 1, 0, 1, 0, 1, 0, 0);
INSERT INTO Schedules (Id, Title, SUN, MON, TUE, WED, THU, FRI, SAT) VALUES (3, 'Twice-a-Week', 0, 1, 0, 1, 0, 0, 0);
INSERT INTO Schedules (Id, Title, SUN, MON, TUE, WED, THU, FRI, SAT) VALUES (4, 'Weekend', 0, 0, 0, 0, 0, 1, 1);
INSERT INTO Schedules (Id, Title, SUN, MON, TUE, WED, THU, FRI, SAT) VALUES (5, 'Compact', 1, 1, 1, 1, 1, 1, 1);
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 |
Biology |
S_BI1 |
Nadia Ali |
Twice-a-Week |
16:00:00 |
18:00:00 |
|
โ๏ธ |
|
โ๏ธ |
|
|
|
8 |
Biology |
S_BI2 |
Omar Ibrahim |
Weekend |
08:00:00 |
10:00:00 |
|
|
|
|
|
โ๏ธ |
โ๏ธ |
9 |
CS50 |
S_CS1 |
Nadia Ali |
Weekend |
10:00:00 |
15:00:00 |
|
|
|
|
|
โ๏ธ |
โ๏ธ |
10 |
CS50 |
S_CS2 |
Omar Ibrahim |
Twice-a-Week |
14:00:00 |
16:00:00 |
|
โ๏ธ |
|
โ๏ธ |
|
|
|
11 |
CS50 |
S_CS3 |
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)
);
-- indexes
CREATE INDEX idx_sections_course_id ON Sections (CourseId);
CREATE INDEX idx_sections_instructor_id ON Sections (InstructorId);
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (1, 'S_MA1', 1, 1, 1, '08:00:00', '10:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (2, 'S_MA2', 1, 2, 3, '14:00:00', '18:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (3, 'S_PH1', 2, 1, 4, '10:00:00', '15:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (4, 'S_PH2', 2, 3, 1, '10:00:00', '12:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (5, 'S_CH1', 3, 2, 1, '16:00:00', '18:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (6, 'S_CH2', 3, 3, 2, '08:00:00', '10:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (7, 'S_BI1', 4, 4, 3, '11:00:00', '14:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (8, 'S_BI2', 4, 5, 4, '10:00:00', '14:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (9, 'S_CS1', 5, 4, 4, '16:00:00', '18:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (10,'S_CS2', 5, 5, 3, '12:00:00', '15:00:00');
INSERT INTO Sections (Id, SectionName, CourseId, InstructorId, ScheduleId, StartTime, EndTime) VALUES (11,'S_CS3', 5, 4, 5, '09:00:00', '11:00:00');
+ Student Enrollment ๐จ ๐ง
Section Name |
Student Name |
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 |
-- Students Table
CREATE TABLE Students (
Id INT PRIMARY KEY,
FName VARCHAR(255) NOT NULL,
LName VARCHAR(255) NOT NULL,
);
-- Inserting data for Students
INSERT INTO Students (Id, FName, LName) VALUES (1, 'Fatima', 'Ali');
INSERT INTO Students (Id, FName, LName) VALUES (2, 'Noor', 'Saleh');
INSERT INTO Students (Id, FName, LName) VALUES (3, 'Omar', 'Youssef');
INSERT INTO Students (Id, FName, LName) VALUES (4, 'Huda', 'Ahmed');
INSERT INTO Students (Id, FName, LName) VALUES (5, 'Amira', 'Tariq');
INSERT INTO Students (Id, FName, LName) VALUES (6, 'Zainab', 'Ismail');
INSERT INTO Students (Id, FName, LName) VALUES (7, 'Yousef', 'Farid');
INSERT INTO Students (Id, FName, LName) VALUES (8, 'Layla', 'Mustafa');
INSERT INTO Students (Id, FName, LName) VALUES (9, 'Mohammed', 'Adel');
INSERT INTO Students (Id, FName, LName) VALUES (10, 'Samira', 'Nabil');
-- Enrollments Table
CREATE TABLE Enrollments (
StudentId INT,
SectionId INT,
PRIMARY KEY (StudentId, SectionId),
FOREIGN KEY (StudentId) REFERENCES Students(Id),
FOREIGN KEY (SectionId) REFERENCES Sections(Id)
);
-- indexes
CREATE INDEX idx_enrollments_section_id ON Enrollments (SectionId);
-- Inserting data for Enrollments
INSERT INTO Enrollments (StudentId, SectionId) VALUES (1, 6);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (2, 6);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (3, 7);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (4, 7);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (5, 8);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (6, 8);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (7, 9);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (8, 9);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (9, 10);
INSERT INTO Enrollments (StudentId, SectionId) VALUES (10, 10);