db.sql

--
-- File generated with SQLiteStudio v3.1.0 on Tue Aug 13 16:46:26 2019
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Room
DROP TABLE IF EXISTS Room;

CREATE TABLE Room (
    Room_ID VARCHAR (6)  PRIMARY KEY,
    Room    VARCHAR (30) 
);

INSERT INTO Room (
                     Room_ID,
                     Room
                 )
                 VALUES (
                     'ECG12',
                     'EC building, Ground floor, 12th room'
                 );

INSERT INTO Room (
                     Room_ID,
                     Room
                 )
                 VALUES (
                     'EC124',
                     'EC building, 1st floor, 24th room'
                 );


-- Table: Student
DROP TABLE IF EXISTS Student;

CREATE TABLE Student (
    Stu_ID       INT (7)      PRIMARY KEY,
    Student_Name VARCHAR (30),
    Class        INTEGER (7)  REFERENCES Class (Class_ID) 
);

INSERT INTO Student (
                        Stu_ID,
                        Student_Name,
                        Class
                    )
                    VALUES (
                        9150863,
                        'Uttej Anumolu',
                        'CoSc1'
                    );

INSERT INTO Student (
                        Stu_ID,
                        Student_Name,
                        Class
                    )
                    VALUES (
                        5656896,
                        'Senior',
                        'CoSc2'
                    );

INSERT INTO Student (
                        Stu_ID,
                        Student_Name,
                        Class
                    )
                    VALUES (
                        9940563,
                        'Charan',
                        'CoSc1'
                    );


-- Table: Module
DROP TABLE IF EXISTS Module;

CREATE TABLE Module (
    Module_ID   INT (4)      PRIMARY KEY,
    Module_Name VARCHAR (30),
    Lect_ID     INT (7)      REFERENCES Lecturers (Lect_ID),
    Room        VARCHAR (7)  REFERENCES Room (Room_ID) 
);

INSERT INTO Module (
                       Module_ID,
                       Module_Name,
                       Lect_ID,
                       Room
                   )
                   VALUES (
                       40005,
                       'Programming',
                       123456,
                       'ECG12'
                   );

INSERT INTO Module (
                       Module_ID,
                       Module_Name,
                       Lect_ID,
                       Room
                   )
                   VALUES (
                       50005,
                       'JavaScript',
                       654321,
                       'EC124'
                   );


-- Table: Class-Module
DROP TABLE IF EXISTS [Class-Module];

CREATE TABLE [Class-Module] (
    Class  VARCHAR (7) REFERENCES Class (Class_ID),
    Module INT (5)     REFERENCES Module (Module_ID) 
);

INSERT INTO [Class-Module] (
                               Class,
                               Module
                           )
                           VALUES (
                               'CoSc1',
                               40005
                           );

INSERT INTO [Class-Module] (
                               Class,
                               Module
                           )
                           VALUES (
                               'CoSc2',
                               50005
                           );


-- Table: Class
DROP TABLE IF EXISTS Class;

CREATE TABLE Class (
    Class_ID   VARCHAR (7)  PRIMARY KEY,
    Class_Name VARCHAR (30) 
);

INSERT INTO Class (
                      Class_ID,
                      Class_Name
                  )
                  VALUES (
                      'CoSc1',
                      'Computer Science Year 1'
                  );

INSERT INTO Class (
                      Class_ID,
                      Class_Name
                  )
                  VALUES (
                      'CoSc2',
                      'Computer Science Year 2'
                  );


-- Table: Lecturers
DROP TABLE IF EXISTS Lecturers;

CREATE TABLE Lecturers (
    Lect_ID   INT (6)      PRIMARY KEY,
    Lect_name VARCHAR (30) 
);

INSERT INTO Lecturers (
                          Lect_ID,
                          Lect_name
                      )
                      VALUES (
                          123456,
                          'Matthew'
                      );

INSERT INTO Lecturers (
                          Lect_ID,
                          Lect_name
                      )
                      VALUES (
                          654321,
                          'David'
                      );

INSERT INTO Lecturers (
                          Lect_ID,
                          Lect_name
                      )
                      VALUES (
                          852963,
                          'Simon'
                      );


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;