--CREATE The tables

--Preface all tables with 
--INTERM_



--Copy code below
--1. Students Table
--Tracks information about students.


CREATE TABLE INTERM_Students (
    Student_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique student identifier
    First_Name VARCHAR2(50) NOT NULL,        -- First name of the student
    Last_Name VARCHAR2(50) NOT NULL,         -- Last name of the student
    Email VARCHAR2(100) NOT NULL UNIQUE,     -- Email must be unique for communication
    Phone VARCHAR2(15),                      -- Optional phone number
    Grade_Level VARCHAR2(20) NOT NULL        -- Grade level of the student
);


 
--Copy code below
--2. Tutors Table
--Stores information about the tutors.



CREATE TABLE INTERM_Tutors (
    Tutor_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique tutor identifier
    First_Name VARCHAR2(50) NOT NULL,        -- First name of the tutor
    Last_Name VARCHAR2(50) NOT NULL,         -- Last name of the tutor
    Subject VARCHAR2(50) NOT NULL,           -- Subject the tutor specializes in
    Hourly_Rate NUMBER(10, 2) NOT NULL,      -- Hourly rate charged by the tutor
    Experience_Years NUMBER NOT NULL         -- Years of tutoring experience
);


--Copy code below
--3. Sessions Table
--Tracks tutoring sessions and connects students with tutors.


CREATE TABLE INTERM_Sessions (
    Session_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique session identifier
    Student_ID NUMBER NOT NULL,             -- References the Students table
    Tutor_ID NUMBER NOT NULL,               -- References the Tutors table
    Session_Date DATE NOT NULL,             -- Date the session takes place
    Duration NUMBER(5, 2) NOT NULL,         -- Duration of the session in hours
    Cost NUMBER(10, 2) NOT NULL,            -- Total cost of the session (calculated as Duration * Hourly_Rate),
    CONSTRAINT FK_Student FOREIGN KEY (Student_ID) REFERENCES INTERM_Students(Student_ID), -- Relationship with INTERM_Students
    CONSTRAINT FK_Tutor FOREIGN KEY (Tutor_ID) REFERENCES INTERM_Tutors(Tutor_ID)          -- Relationship with INTERM_Tutors
);


--Copy code below 
--4. Feedback Table
--Stores feedback provided by students or their parents after sessions.

CREATE TABLE INTERM_Feedback (
    Feedback_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique feedback identifier
    Session_ID NUMBER NOT NULL,              -- References the Sessions table
    Rating NUMBER(1) CHECK (Rating BETWEEN 1 AND 5), -- Rating must be between 1 and 5
    Comments VARCHAR2(255),                  -- Optional comments
    CONSTRAINT FK_Session_Feedback FOREIGN KEY (Session_ID) REFERENCES INTERM_Sessions(Session_ID) -- Relationship with INTERM_Sessions
);
 

--Copy code below
--5. Payments Table
--Tracks payments made by students for tutoring services.

CREATE TABLE INTERM_Payments (
    Payment_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique payment identifier
    Student_ID NUMBER NOT NULL,             -- References the Students table
    Payment_Date DATE NOT NULL,             -- Date the payment was made
    Amount NUMBER(10, 2) NOT NULL,          -- Payment amount
    Payment_Method VARCHAR2(50) NOT NULL,   -- Payment method (e.g., Credit Card, Cash)
    CONSTRAINT FK_Student_Payment FOREIGN KEY (Student_ID) REFERENCES INTERM_Students(Student_ID) -- Relationship with Students
);
 

--Copy code below
--6. Technology Upgrades Table
--Tracks tech upgrades implemented to enhance tutoring services.

CREATE TABLE INTERM_Tech_Upgrades (
    Upgrade_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique identifier for upgrades
    Upgrade_Name VARCHAR2(100) NOT NULL,    -- Name of the technology upgrade (e.g., "Interactive Whiteboard")
    Implementation_Date DATE NOT NULL,      -- Date the upgrade was implemented
    Cost NUMBER(10, 2) NOT NULL             -- Cost of the upgrade
);
 

--Full Relationship Map
--1.	Sessions.Student_ID references Students.Student_ID: A session involves a student.
--2.	Sessions.Tutor_ID references Tutors.Tutor_ID: A session is conducted by a tutor.
--3.	Feedback.Session_ID references Sessions.Session_ID: Feedback is linked to a session.
--4.	Payments.Student_ID references Students.Student_ID: Payments are linked to specific students.
--Next Steps:
--	Populate the tables with data using INSERT statements.
--	Write queries to perform analytics and make decisions based on the data.