-- Create new database
CREATE DATABASE UviDB;

-- Use the new database
USE UviDB;

-- Create tables
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DeptName VARCHAR(100),
    Building VARCHAR(100)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DateOfBirth DATE,
    Email VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    CourseDescription TEXT,
    CreditHours INT,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
);

CREATE TABLE Professors (
    ProfessorID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INT,
    Email VARCHAR(100),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
);

CREATE TABLE Classrooms (
    ClassroomID INT PRIMARY KEY,
    RoomName VARCHAR(100),
    Capacity INT,
    Location VARCHAR(100)
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    Grade VARCHAR(2),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ON DELETE CASCADE
);

CREATE TABLE TeachingAssignments (
    ProfessorID INT,
    CourseID INT,
    Semester VARCHAR(20),
    PRIMARY KEY (ProfessorID, CourseID),
    FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ON DELETE CASCADE
);

CREATE TABLE ClassSchedules (
    ScheduleID INT PRIMARY KEY,
    CourseID INT,
    ClassroomID INT,
    DayOfWeek VARCHAR(10),
    times TIME,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ON DELETE CASCADE,
    FOREIGN KEY (ClassroomID) REFERENCES Classrooms(ClassroomID) ON DELETE CASCADE
);

-- Inheritance tables
CREATE TABLE Staff (
    StaffID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
);

CREATE TABLE AdministrativeStaff (
    StaffID INT PRIMARY KEY,
    Position VARCHAR(100),
    OfficeNumber VARCHAR(10),
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

CREATE TABLE MaintenanceStaff (
    StaffID INT PRIMARY KEY,
    SkillSet VARCHAR(100),
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

CREATE TABLE SecurityStaff (
    StaffID INT PRIMARY KEY,
    Shift VARCHAR(10),
    ClearanceLevel VARCHAR(10),
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

-- Insert sample data into Departments
INSERT INTO Departments (DepartmentID, DeptName, Building) VALUES 
(1, 'Computer Science', 'Building A'),
(2, 'Mathematics', 'Building B'),
(3, 'Physics', 'Building C'),
(4, 'Chemistry', 'Building D'),
(5, 'Biology', 'Building E'),
(6, 'English', 'Building F'),
(7, 'History', 'Building G'),
(8, 'Geography', 'Building H'),
(9, 'Philosophy', 'Building I'),
(10, 'Art', 'Building J');

-- Insert sample data into Students
INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Email) VALUES 
(1, 'John', 'Doe', '2000-01-01', 'john.doe@example.com'),
(2, 'Jane', 'Smith', '1999-02-01', 'jane.smith@example.com'),
(3, 'Tom', 'Brown', '1998-03-01', 'tom.brown@example.com'),
(4, 'Lucy', 'Davis', '2000-04-01', 'lucy.davis@example.com'),
(5, 'Mike', 'Johnson', '1999-05-01', 'mike.johnson@example.com'),
(6, 'Emma', 'Williams', '2001-06-01', 'emma.williams@example.com'),
(7, 'Chris', 'Taylor', '2000-07-01', 'chris.taylor@example.com'),
(8, 'Laura', 'Lee', '1998-08-01', 'laura.lee@example.com'),
(9, 'James', 'White', '2001-09-01', 'james.white@example.com'),
(10, 'Sophia', 'Clark', '1999-10-01', 'sophia.clark@example.com');

-- Insert sample data into Courses
INSERT INTO Courses (CourseID, CourseName, CourseDescription, CreditHours, DepartmentID) VALUES 
(1, 'Database Systems', 'Introduction to databases', 4, 1),
(2, 'Calculus', 'Advanced calculus', 3, 2),
(3, 'Quantum Mechanics', 'Advanced quantum mechanics', 4, 3),
(4, 'Organic Chemistry', 'Study of organic compounds', 4, 4),
(5, 'Genetics', 'Study of genes', 3, 5),
(6, 'Shakespearean Literature', 'Study of Shakespeare', 4, 6),
(7, 'World History', 'Overview of world history', 3, 7),
(8, 'Physical Geography', 'Study of physical geography', 4, 8),
(9, 'Ethics', 'Introduction to ethics', 3, 9),
(10, 'Modern Art', 'Study of modern art', 4, 10);

-- Insert sample data into Professors
INSERT INTO Professors (ProfessorID, FirstName, LastName, DepartmentID, Email) VALUES 
(1, 'Alice', 'Johnson', 1, 'alice.johnson@example.com'),
(2, 'Bob', 'Williams', 2, 'bob.williams@example.com'),
(3, 'Sara', 'Connor', 3, 'sara.connor@example.com'),
(4, 'David', 'Brown', 4, 'david.brown@example.com'),
(5, 'Susan', 'Davis', 5, 'susan.davis@example.com'),
(6, 'Henry', 'Miller', 6, 'henry.miller@example.com'),
(7, 'Olivia', 'Taylor', 7, 'olivia.taylor@example.com'),
(8, 'George', 'Lee', 8, 'george.lee@example.com'),
(9, 'Sophia', 'Wilson', 9, 'sophia.wilson@example.com'),
(10, 'Jack', 'Clark', 10, 'jack.clark@example.com');

-- Insert sample data into Classrooms
INSERT INTO Classrooms (ClassroomID, RoomName, Capacity, Location) VALUES 
(1, 'Room 101', 30, 'Building A'),
(2, 'Room 102', 25, 'Building B'),
(3, 'Room 103', 20, 'Building C'),
(4, 'Room 104', 35, 'Building D'),
(5, 'Room 105', 40, 'Building E'),
(6, 'Room 106', 45, 'Building F'),
(7, 'Room 107', 50, 'Building G'),
(8, 'Room 108', 55, 'Building H'),
(9, 'Room 109', 60, 'Building I'),
(10, 'Room 110', 65, 'Building J');

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade) VALUES 
(1, 6, '2024-01-15', 'A'),
(1, 5, '2024-01-15', 'C'),
(1, 2, '2024-01-20', 'B'),
(2, 1, '2024-01-22', 'A'),
(2, 3, '2024-01-25', 'C'),
(3, 4, '2024-02-01', 'B'),
(3, 2, '2024-01-20', 'A'),
(4, 5, '2024-02-01', 'B'),
(5, 6, '2024-02-10', 'A'),
(6, 7, '2024-03-15', 'B'),
(7, 8, '2024-04-20', 'A'),
(8, 9, '2024-05-01', 'B'),
(9, 10, '2024-06-15', 'A'),
(10, 1, '2024-07-01', 'B');

-- Insert sample data into TeachingAssignments
INSERT INTO TeachingAssignments (ProfessorID, CourseID, Semester) VALUES 
(1, 1, 'Spring 2024'),
(2, 2, 'Spring 2024'),
(3, 3, 'Fall 2024'),
(4, 4, 'Fall 2024'),
(5, 5, 'Spring 2024'),
(6, 6, 'Spring 2024'),
(7, 7, 'Fall 2024'),
(8, 8, 'Fall 2024'),
(9, 9, 'Spring 2024'),
(10, 10, 'Spring 2024');

-- Insert sample data into ClassSchedules
INSERT INTO ClassSchedules (ScheduleID, CourseID, ClassroomID, DayOfWeek, times) VALUES 
(1, 1, 1, 'Monday', '10:00:00'),
(2, 2, 2, 'Tuesday', '14:00:00'),
(3, 3, 3, 'Wednesday', '15:00:00'),
(4, 4, 4, 'Thursday', '16:00:00'),
(5, 5, 5, 'Friday', '09:00:00'),
(6, 6, 6, 'Monday', '11:00:00'),
(7, 7, 7, 'Tuesday', '13:00:00'),
(8, 8, 8, 'Wednesday', '14:00:00'),
(9, 9, 9, 'Thursday', '15:00:00'),
(10, 10, 10, 'Friday', '16:00:00');

-- Insert sample data into Staff
INSERT INTO Staff (StaffID, FirstName, LastName, DepartmentID) VALUES 
(1, 'Emma', 'Brown', 1),
(2, 'Liam', 'Davis', 2),
(3, 'Olivia', 'Miller', 1),
(4, 'Noah', 'Wilson', 3),
(5, 'Ava', 'Moore', 4),
(6, 'Isabella', 'Taylor', 5),
(7, 'Sophia', 'Anderson', 6),
(8, 'Mason', 'Thomas', 7),
(9, 'Logan', 'Jackson', 8),
(10, 'Lucas', 'White', 9);

-- Insert sample data into AdministrativeStaff
INSERT INTO AdministrativeStaff (StaffID, Position, OfficeNumber) VALUES 
(1, 'Registrar', 'Office 101'),
(2, 'Secretary', 'Office 102'),
(3, 'Administrator', 'Office 103'),
(4, 'Clerk', 'Office 104'),
(5, 'Coordinator', 'Office 105'),
(6, 'Assistant', 'Office 106'),
(7, 'Supervisor', 'Office 107'),
(8, 'Director', 'Office 108'),
(9, 'Manager', 'Office 109'),
(10, 'Officer', 'Office 110');

-- Insert sample data into MaintenanceStaff
INSERT INTO MaintenanceStaff (StaffID, SkillSet) VALUES 
(1, 'Electrician'),
(2, 'Plumber'),
(3, 'Carpenter'),
(4, 'Technician'),
(5, 'Mechanic'),
(6, 'Painter'),
(7, 'Welder'),
(8, 'Gardener'),
(9, 'Cleaner'),
(10, 'HVAC Specialist');

-- Insert sample data into SecurityStaff
INSERT INTO SecurityStaff (StaffID, Shift, ClearanceLevel) VALUES 
(1, 'Night', 'Level 2'),
(2, 'Morning', 'Level 3'),
(3, 'Afternoon', 'Level 1'),
(4, 'Night', 'Level 3'),
(5, 'Morning', 'Level 2'),
(6, 'Afternoon', 'Level 2'),
(7, 'Night', 'Level 1'),
(8, 'Morning', 'Level 3'),
(9, 'Afternoon', 'Level 3'),
(10, 'Night', 'Level 2');
