database.sql

-- Table: Rooms
DROP TABLE IF EXISTS Rooms;

CREATE TABLE Rooms (
    Room_Code VARCHAR (10)  PRIMARY KEY    
);

INSERT INTO Rooms (Room_Code)
                 VALUES ('ECG-10');

INSERT INTO Rooms (Room_Code)
                 VALUES ('EC1-24');


-- Table: Students
DROP TABLE IF EXISTS Students;

CREATE TABLE Students (
    StudentID       INT (7)      PRIMARY KEY,
    StudentName VARCHAR (40)
);

INSERT INTO Students (StudentID, StudentName)
                    VALUES (8791180,'Hugo Seabra');

INSERT INTO Students (StudentID, StudentName)
                    VALUES (8795265,'Albert Ein');



-- Table: Modules
DROP TABLE IF EXISTS Modules;

CREATE TABLE Modules (
    ModuleID   INT (4)      PRIMARY KEY,
    ModuleName VARCHAR (40),
    LectID     INT (7)      REFERENCES Lecturers (LectID),
    Room       VARCHAR (10)  REFERENCES Rooms (Room_Code) 
);

INSERT INTO Modules (ModuleID, ModuleName, LectID, Room)
                   VALUES (4000,'Programming and Algorithms',1325642,'ECG-10'
                   );

INSERT INTO Modules (ModuleID, ModuleName, LectID, Room)
                   VALUES (4003,'Object Oriented Programming',2345345,'EC1-24');


-- Table: Class
DROP TABLE IF EXISTS Class;

CREATE TABLE Class (
    ClassID   VARCHAR (8)  PRIMARY KEY,
    ClassName VARCHAR (40) 
);

INSERT INTO Class (ClassID, ClassName)
            VALUES ('CompSci','Computer Science First Year');

INSERT INTO Class (ClassID, ClassName)
            VALUES ('CompSci2','Computer Science Second Year');


-- Table: Lecturers
DROP TABLE IF EXISTS Lecturers;

CREATE TABLE Lecturers (
    LectID   INT (7)      PRIMARY KEY,
    LectName VARCHAR (40) 
);

INSERT INTO Lecturers (LectID, LectName)
                      VALUES (1325642,'Rob');

INSERT INTO Lecturers (LectID, LectName)
                      VALUES (2345345, 'Lerry');