Parts Implemented by Berkay Olgun

Instructors

Note

All table creations exist in db_init.py file.

Note

All SQL operations in python files are wrapped with try-except statements in order to avoid errors

1. Creation

Detailed informations about instructors are kept in tables that it refers.

CREATE TABLE IF NOT EXISTS INSTRUCTORS(
    INS_ID INTEGER PRIMARY KEY,
    BACHELORS VARCHAR(90),
    MASTERS VARCHAR(90),
    DOCTORATES VARCHAR(90),
    DEPARTMENT INTEGER,
    ROOM INTEGER UNIQUE,
    LAB INTEGER,
    FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENTS(DEP_ID) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (ROOM) REFERENCES ROOMS(ROOM_ID) ON DELETE SET NULL,
    FOREIGN KEY (LAB) REFERENCES LABS(LAB_ID) ON DELETE SET NULL,
    FOREIGN KEY (INS_ID) REFERENCES PEOPLE ON DELETE CASCADE
)

2. Adding

Before adding an instructor if that person is not in table People he/she will be added to people before adding as instructor. Instructor id references the private key in the People table.

Warning

The room of an instructor should be added before it’s instructor.

def add_instructor(self, instructor):
    try:
        with dbapi2.connect(self.url) as connection:
            cursor = connection.cursor()
            statement = "INSERT INTO INSTRUCTORS (INS_ID, BACHELORS, MASTERS, DOCTORATES,
             DEPARTMENT, ROOM, LAB) VALUES (%s, %s, %s, %s, %s, %s, %s)"
            data = [instructor.instructor_id, instructor.bachelors, instructor.masters, instructor.doctorates,
                    instructor.department, instructor.room, instructor.lab]
            cursor.execute(statement, data)
            cursor.close()
    except Exception as err:
        print("Add Instructor Error: ", err)
    return instructor

3. Reading

In order to get room name lab name and instructor name join operations must be performed since the tables are in normal form.

def get_instructors(self):
    try:
        with dbapi2.connect(self.url) as connection:
            cursor = connection.cursor()
            statement = "SELECT P_ID, NAME, ROOMS.ROOM_NAME, LABS.LAB_NAME, BACHELORS, MASTERS,
             DOCTORATES FROM INSTRUCTORS JOIN PEOPLE ON (INSTRUCTORS.INS_ID = PEOPLE.P_ID)
              JOIN ROOMS ON (INSTRUCTORS.ROOM = ROOMS.ROOM_ID)
               LEFT JOIN LABS ON (INSTRUCTORS.LAB = LABS.LAB_ID)"
            cursor.execute(statement)
            datas = cursor.fetchall()
            cursor.close()
            retval = []
            for data in datas:
                val = {
                    "ID":data[0],
                    "Name": data[1],
                    "Room": data[2],
                    "Lab": data[3],
                    "Bachelors": data[4],
                    "Masters": data[5],
                    "Doctorates": data[6]
                }
                retval.append(val)
            return retval
    except Exception as err:
        print("Get Instructors Error: ", err)

    return None

4. Updating

In order to update instructors, characteristic attribute id is enough to filter any of them. attrs_lookup_table is used with attrs and values list in order to restrict naming and ordering attribute confusions.

def update_instructor(self, ins_id, attrs, values):
attrs_lookup_table = {
    "department": "DEPARTMENT",
    "room": "ROOM",
    "lab": "LAB",
    "bachelors": "BACHELORS",
    "masters": "MASTERS",
    "doctorates": "DOCTORATES"
}

try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "UPDATE INSTRUCTORS SET "
        for i in range(len(attrs) - 1):
            print(attrs_lookup_table[attrs[i]] + " = %s ,")
            statement += attrs_lookup_table[attrs[i]] + " = %s ,"
        statement += attrs_lookup_table[attrs[-1]] + " = %s WHERE INS_ID = %s"
        values.append(ins_id)
        cursor.execute(statement, values)
        cursor.close()

except Exception as err:
    print("Update Instructors Error: ", err)

5. Deleting

In order to delete an instructor, simple DELETE query is sufficient.

def delete_instructor(self, ins_id):
try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "DELETE FROM INSTRUCTORS WHERE INS_ID = %s"
        values = [ins_id]
        cursor.execute(statement, values)
        cursor.close()
except Exception as err:
    print("Delete Instructor Error: ", err)

Rooms

1. Creation

    CREATE TABLE IF NOT EXISTS ROOMS (
    ROOM_ID SERIAL PRIMARY KEY,
    BUILDING INTEGER,
    ROOM_NAME VARCHAR(10) UNIQUE NOT NULL,
    AVAILABLE BOOL DEFAULT TRUE,
    CLASS BOOLEAN DEFAULT FALSE,
    LAB BOOLEAN DEFAULT FALSE,
    ROOM BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (BUILDING) REFERENCES BUILDINGS(BU_ID) ON UPDATE CASCADE ON DELETE CASCADE
)

Building key references the building that room is in.

2. Adding

Warning

Before adding a room a building must exist to refer with a foreign key.

def add_room(self, room):
try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "INSERT INTO ROOMS (BUILDING, ROOM_NAME, AVAILABLE, CLASS, LAB, ROOM) VALUES (%s, %s, %s, %s, %s, %s)"
        data = [room.building, room.name, room.availability, room.classroom, room.lab, room.room]
        cursor.execute(statement, data)
        statement = "SELECT ROOM_ID FROM ROOMS WHERE ROOM_NAME = %s"
        data = [room.name]
        cursor.execute(statement, data)
        value = cursor.fetchall()
        room.id = value[0]
        cursor.close()
except Exception as err:
    print("Add Room Error: ", err)
return room

After adding a room its id is taken to include it in return.

3. Reading

In order to get the name of the room’s building a join operation must be performed.

def get_rooms(self):
    try:
        with dbapi2.connect(self.url) as connection:
            cursor = connection.cursor()
            statement = "SELECT ROOM_ID, BU_NAME, ROOM_NAME FROM ROOMS JOIN BUILDINGS ON(ROOMS.BUILDING = BUILDINGS.BU_ID)"
            cursor.execute(statement)
            datas = cursor.fetchall()
            cursor.close()
            retval = []
            for data in datas:
                val = {
                    "ID": data[0],
                    "Name": data[2],
                    "Building": data[1]
                }
                retval.append(val)
            return retval
    except Exception as err:
        print("Get Rooms Error: ", err)

Rooms are returned with a list of dictionaries

4. Updating

Attributes are taken from the lookup table values and names are compared with given parameters and new values are written on the instance.

def update_room(self, room_id, attrs, values):
attrs_lookup_table = {
    "building": "BUILDING",
    "room_name": "ROOM_NAME",
    "class": "CLASS",
    "lab": "LAB",
    "room": "ROOM",
    "available": "AVAILABLE"
}

try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "UPDATE ROOMS SET "
        for i in range(len(attrs) - 1):
            statement += attrs_lookup_table[attrs[i]] + " = %s ,"
        statement += attrs_lookup_table[attrs[-1]] + " = %s WHERE ROOM_ID = %s"
        values.append(room_id)
        cursor.execute(statement, values)
        cursor.close()

except Exception as err:
    print("Update Rooms Error: ", err)

5. Deleting

Simple DELETE query with a room id is sufficient to delete any room from the application.

def delete_room(self, room_id):
try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "DELETE FROM ROOMS WHERE room_id = %s"
        values = [room_id]
        cursor.execute(statement, values)
        cursor.close()
except Exception as err:
    print("Delete Room Error: ", err)

Classrooms

1. Creation

Private and foreign key class id refers to the room id that the class in. .. note:: In our structure every classroom is a (inside a) room.

    CREATE TABLE IF NOT EXISTS CLASSES(
    CL_ID INTEGER PRIMARY KEY,
    CAP INTEGER NOT NULL,
    TYPE VARCHAR(15) DEFAULT 'Lecture',
    AIR_CONDITIONER BOOL,
    LAST_RESTORATION VARCHAR(4),
    BOARD_TYPE VARCHAR(5) DEFAULT 'Mixed',
    FOREIGN KEY (CL_ID) REFERENCES ROOMS(ROOM_ID) ON DELETE CASCADE
)

2. Adding

Before adding a classroom a room is added if not exists, with the proper values.

def add_classroom(self, classroom):
        try:
            with dbapi2.connect(self.url) as connection:
                cursor = connection.cursor()
                statement = "INSERT INTO CLASSES (CL_ID, TYPE, AIR_CONDITIONER, LAST_RESTORATION, BOARD_TYPE, CAP) VALUES (%s, %s, %s, %s, %s, %s)"
                data = [classroom.id, classroom.type, classroom.conditioner, classroom.restoration_date, classroom.board_type, classroom.cap]
                cursor.execute(statement, data)
                cursor.close()
        except Exception as err:
            print("Add Classroom Error: ", err)
        return classroom

3. Reading

Selecting all the values by the name in order to avoid ordering problems when giving them to attributes dictioanary.

def get_classrooms(self):
    try:
        with dbapi2.connect(self.url) as connection:
            cursor = connection.cursor()
            statement = "SELECT CLASSES.CL_ID, ROOMS.ROOM_NAME, CLASSES.CAP, CLASSES.TYPE, BUILDINGS.BU_NAME FROM CLASSES JOIN ROOMS ON CL_ID = ROOM_ID JOIN BUILDINGS ON BUILDINGS.BU_ID = ROOMS.BUILDING"
            cursor.execute(statement)
            datas = cursor.fetchall()
            cursor.close()
            retval = []
            for data in datas:
                val = {
                    "ID":data[0],
                    "Name": data[1],
                    "Capacity": data[2],
                    "Class Type": data[3],
                    "Building Name": data[4],
                }
                retval.append(val)
            return retval
    except Exception as err:
        print("Get Classrooms Error: ", err)

    return None

4. Updating

Same update process is applied to classrooms. Attribute names and their values are given parameters from the form.

def update_classroom(self, class_id, attrs, values):
attrs_lookup_table = {
    "type": "TYPE",
    "air_conditioner": "AIR_CONDITIONER",
    "last_restoration": "LAST_RESTORATION",
    "board_type": "BOARD_TYPE",
    "cap": "CAP"
}

try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "UPDATE CLASSES SET "
        for i in range(len(attrs) - 1):
            statement += attrs_lookup_table[attrs[i]] + " = %s ,"
        statement += attrs_lookup_table[attrs[-1]] + " = %s WHERE CL_ID = %s"
        print(statement, values)
        values.append(class_id)
        cursor.execute(statement, values)
        cursor.close()
except Exception as err:
    print("Update Classroom Error: ", err)

5. Deleting

Note

By the cascade nature if referred room is deleted the classroom is deleted.

def delete_classroom(self, cl_id):
try:
    with dbapi2.connect(self.url) as connection:
        cursor = connection.cursor()
        statement = "DELETE FROM CLASSES WHERE CL_ID = %s"
        values = [cl_id]
        cursor.execute(statement, values)
        cursor.close()
except Exception as err:
    print("Delete Classroom Error: ", err)