UniversityDB

CREATE TABLE IF NOT EXISTS Student_Personal_Information (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    address VARCHAR(100),
    phone_number VARCHAR(15),
    email VARCHAR(100)
);


CREATE TABLE IF NOT EXISTS Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(10) UNIQUE,
    professor VARCHAR(50),
    start_date DATE,
    end_date DATE
);


CREATE TABLE IF NOT EXISTS Grade (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES Student_Personal_Information(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);


INSERT INTO Student_Personal_Information (first_name, last_name, date_of_birth, address, phone_number, email)
VALUES
    ('Maria', 'Gonzales', '1998-03-15', '123 Main St, Manggahan, Pasig City', '09123456789', '[email protected]'),
    ('Jose', 'Rodriguez', '1997-05-22', '456 Elm St, Maybunga, Pasig City', '09234567890', '[email protected]'),
    ('Luz', 'Fernandez', '1999-11-10', '789 Oak St, Pinagbuhatan, Pasig City', '09345678901', '[email protected]'),
    ('Ramon', 'Ortiz', '1996-08-03', '234 Birch St, Kapasigan, Pasig City', '09456789012', '[email protected]'),
    ('Carmen', 'Santiago', '2000-02-28', '567 Cedar St, Bangbang, Pasig City', '09567890123', '[email protected]');


INSERT INTO Courses (course_name, course_code, professor, start_date, end_date)
VALUES
    ('Advanced Database Management System', 'ADMS101', 'Dr. Juan Santos', '2023-01-10', '2023-05-10'),
    ('Advanced System Analysis and Design', 'ASAD201', 'Prof. Cristina Lopez', '2023-02-15', '2023-06-15'),
    ('Management of Information System', 'MIS301', 'Dr. Manuel Reyes', '2023-03-20', '2023-07-20'),
    ('Introduction to Computing', 'COMP101', 'Prof. Lourdes Garcia', '2023-01-15', '2023-05-15'),
    ('Fundamentals of Programming', 'PROG101', 'Dr. Andres Torres', '2023-01-20', '2023-05-20');

INSERT INTO Grade (student_id, course_id, grade)
VALUES
    (1, 1, '1.25'),
    (2, 1, '1.50'),
    (3, 2, '1.00'),
    (4, 3, '1.50'),
    (5, 4, '1.25'),
    (1, 5, '1.50'),
    (2, 5, '1.75'),
    (3, 4, '1.00'),
    (4, 3, '1.25'),
    (5, 2, '1.50');

SELECT * FROM Student_Personal_Information;
SELECT * FROM Courses;
SELECT * FROM Grade;