CREATE TABLE Insitutions(
    Institution_ID INT PRIMARY KEY,
    Name VARCHAR(255),
    Location VARCHAR(255),
    Contact VARCHAR(255),
    Website VARCHAR(255)
);

CREATE TABLE Courses(
    Course_ID INT PRIMARY KEY,
    Institution_id INT RIMARY KEY,
    Course_Name VARCHAR(50),
    Department VARCHAR(100),
    Description TEXT,
    Credit_Hour INT,
    FOREIGN KEY (Institution_ID) REFERENCES Institutions(Institution_ID);
);

CREATE TABLE Students (
    Student_Id INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Date_Of_Birth DATE,
    Contact VARCHAR(255),
    Enrollment_Date DATE,
    Institution_Id INT,
    FOREIGN KEY (Institution_Id) REFERENCES Institutions(Institution_Id);
);

CREATE TABLE Faculty(
    Faculty_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Date_Of_Birth DATE,
    Contact VARCHAR(255),
    Hire_Date DATE,
    Institution_ID INT,
    Department VARCHAR(100),
    FOREIGN KEY (Institution_Id) REFERENCES Institutions(Institution_Id) 
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE Assignments (
    Assignment_ID INT PRIMARY KEY,
    Course_ID INT,
    Title VARCHAR(255),
    Description TEXT,
    Due_Date DATE,
    FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);

CREATE TABLE Grades(
    Grade_ID INT PRIMARY KEY,
    Student_ID INT,
    Assignment_ID INT,
    Score DECIMALS(5, 2),
    FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
    FOREIGN KEY (Assignment_ID) REFERENCES Assignments(Assignment_ID)
);

CREATE TABLE Events (
    Event_ID INT PRIMARY KEY,
    Institution_ID INT,
    Event_Name VARCHAR(255),
    Event_Date DATE,
    Description TEXT,
    FOREIGN KEY (institution_id) REFERENCES Institutions(institution_id)
);

-- Create Faculty_Courses Table
CREATE TABLE Faculty_Courses (
    Faculty_Courses_ID INT PRIMARY KEY,
    Faculty_ID INT,
    Course_ID INT,
    FOREIGN KEY (Faculty_ID) REFERENCES Faculty(Faculty_ID),
    FOREIGN KEY (Course_id) REFERENCES Courses(Course_ID)
);


-- Insert data into the Institutions Table
INSERT INTO Institutions (institution_id, name, location, contact, website)
VALUES
    (1, 'University of Science', 'New York', 'contact@science.edu', 'www.science.edu'),
    (2, 'University of Taxes', 'Washington', 'contact@taxes.edu', 'www.univtaxes.edu'),
    (3, 'Technical Institute', 'Tech Town', 'info@techinst.edu', 'www.techinst.edu');

select *from Institutions;