CREATE TABLE Faculties (
    faculty_id INT PRIMARY KEY,
    faculty_name VARCHAR(100),
    dean_name VARCHAR(100)
);

CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    faculty_id INT,
    FOREIGN KEY (faculty_id) REFERENCES Faculties(faculty_id),
    dept_code VARCHAR(10) UNIQUE
);

CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(100),
    dept_id INT,
    hire_date DATE,
    title VARCHAR(50),
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    dept_id INT,
    enrollment_year INT,
    admission_date DATE,
    tuition_fee DECIMAL(10, 2),
    status VARCHAR(20),
    advisor_id INT,
    total_credit INT DEFAULT 0,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id),
    FOREIGN KEY (advisor_id) REFERENCES Teachers(teacher_id)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credit INT,
    course_type VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Course_Records (
    record_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    teacher_id INT,
    semester VARCHAR(20),
    score DECIMAL(5, 2),
    grade_point DECIMAL(3, 2),
    attendance_rate INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

INSERT INTO Faculties VALUES (1, 'Engineering', 'Dr. Smith');
INSERT INTO Faculties VALUES (2, 'Arts', 'Dr. Joyce');

INSERT INTO Departments VALUES (101, 'Computer Science', 1, 'CS');
INSERT INTO Departments VALUES (102, 'Mechanical Eng', 1, 'ME');
INSERT INTO Departments VALUES (201, 'English Lit', 2, 'ENG');

INSERT INTO Teachers VALUES (1, 'Prof. Alice', 101, '2020-01-01', 'Professor', 9000.00);
INSERT INTO Teachers VALUES (2, 'Dr. Bob', 101, '2021-06-01', 'Lecturer', 6000.00);
INSERT INTO Teachers VALUES (3, 'Prof. Carol', 201, '2019-09-01', 'Professor', 8500.00);

INSERT INTO Students VALUES (1001, 'Zhang San', 101, 2022, '2022-09-01', 5000.00, 'Active', 1, 0);
INSERT INTO Students VALUES (1002, 'Li Si', 101, 2023, '2023-09-01', 5500.00, 'Active', 2, 0);
INSERT INTO Students VALUES (1003, 'Wang Wu', 201, 2022, '2022-09-01', 4500.00, 'Active', 3, 0);

INSERT INTO Courses VALUES (501, 'Database Systems', 4, 'Core', 101);
INSERT INTO Courses VALUES (502, 'Algorithms', 3, 'Core', 101);
INSERT INTO Courses VALUES (503, 'Shakespeare', 3, 'Elective', 201);

INSERT INTO Course_Records VALUES (1, 1001, 501, 1, '2023-Fall', 85.0, 3.5, 90);
INSERT INTO Course_Records VALUES (2, 1001, 502, 2, '2023-Fall', 92.0, 4.0, 95);
INSERT INTO Course_Records VALUES (3, 1002, 501, 1, '2023-Fall', 78.0, 2.7, 80);
INSERT INTO Course_Records VALUES (4, 1003, 503, 3, '2023-Fall', 88.0, 3.7, 100);