# Assignment 3
## Group number ：Group A3_F10_115_148
### Section 1 ：Code description and explanation
#### Haolan Zou 500184187
- list_classroom()
List the number of classes in each classroom
```Python3
def list_classroom():
conn = database_connect()
if(conn is None):
return None
# Sets up the rows as a dictionary
cur = conn.cursor()
val = None
try:
cur.execute("""select classroomid,count(classtime)
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
group by classroomid;""")
val = cur.fetchall()
except:
# If there were any errors, we print something nice and return a NULL value
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
- list_location()
List the basic information of each unit, including Uoscode, Uosname, Semester, year, classtime and classroomid
```Python3
################################################################################
# Get location function
################################################################################
def list_location():
# TODO
# Get the students transcript from the database
# You're given an SID as a variable 'sid'
# Return the results of your query :)
# Get the database connection and set up the cursor
conn = database_connect()
if(conn is None):
return None
# Sets up the rows as a dictionary
cur = conn.cursor()
val = None
try:
# Try getting all the information returned from the query
# NOTE: column ordering is IMPORTANT
cur.execute("""select UoSCode, UoSname, semester, year, classtime ,classroomid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode);""")
val = cur.fetchall()
except:
# If there were any errors, we print something nice and return a NULL value
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
- list_time(time)
Search units with a sepecific time
```Python3
def list_time(time):
conn = database_connect()
if(conn is None):
return None
# Sets up the rows as a dictionary
cur = conn.cursor()
val = None
try:
# Try getting all the information returned from the query
# NOTE: column ordering is IMPORTANT
cur.execute("""select UoSCode, UoSname, semester, year, classtime ,classroomid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
where classtime = %s;""",[time])
val = cur.fetchall()
except:
# If there were any errors, we print something nice and return a NULL value
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
- check_insert1(code,semester,year,time,uosid)
Test for duplicate attributes in unidb.lecture
```Python3
def check_insert1(code,semester,year,time,uosid):
# Ask for the database connection, and get the cursor set up
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
# Try executing the SQL and get from the database
try:
sql = """SELECT *
FROM unidb.lecture
WHERE uoscode=%s and semester= %s and year = %s and classroomid = %s"""
cur.execute(sql, [code,semester,year,uosid])
r = cur.fetchone() # Fetch the first row
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return r
except:
print("Error1")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return None
```
- check_insert2(code,semester,year,time,uosid)
Test whether the given uoscode, semester, and year are valid, i.e. whether the school has offered the unit
```Python3
def check_insert2(code,semester,year,time,uosid):
# Ask for the database connection, and get the cursor set up
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
try:
# Try executing the SQL and get from the database
sql = """SELECT *
FROM unidb.Uosoffering
WHERE uoscode=%s and semester= %s and year = %s """
cur.execute(sql, (code,semester,year))
r = cur.fetchone() # Fetch the first row
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return r
except:
# If there were any errors, return a NULL row printing an error to the debug
print("Error")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return None
```
- add_lec(code,semester,year,time,uosid)
Use messages from users to add new lectures
```Python3
def add_lec(code,semester,year,time,uosid):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
cur.execute("""INSERT INTO UNiDB.lecture (UoSCode,semester,year, classtime ,classroomid)
VALUES (%s, %s, %s, %s, %s);""",[code,semester,year,time,uosid])
conn.commit()
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return
```
- list_lec()
List all current lecture information
```Python3
def list_lec():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
try:
cur.execute("""select *
from unidb.lecture
order by Uoscode""")
val = cur.fetchall()
except:
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
- find_uoscode(sid) [extension]
Find all the user's courses
```Python3
def find_uoscode(sid):
conn = database_connect()
if(conn is None):
return None
# Sets up the rows as a dictionary
cur = conn.cursor()
val = None
try:
# Try getting all the information returned from the query
# NOTE: column ordering is IMPORTANT
cur.execute("""Select distinct(uoscode)
from unidb.transcript join unidb.uosoffering using(uoscode)
where studid = %s
""",[sid])
val = cur.fetchall()
except:
# If there were any errors, we print something nice and return a NULL value
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
- list_classroom2(uos) [extension]
Use uoscode to find classroom information, i.e. find the classroom information for a given unit
```Python3
def list_classroom2(uos):
conn = database_connect()
if(conn is None):
return None
# Sets up the rows as a dictionary
cur = conn.cursor()
val = None
try:
# Try getting all the information returned from the query
# NOTE: column ordering is IMPORTANT
cur.execute("""select UoSCode, UoSname, semester, year, classtime ,classroomid,seats,deptid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
where UoScode = %s;""",[uos])
val = cur.fetchall()
except:
# If there were any errors, we print something nice and return a NULL value
print("Error fetching from database")
cur.close() # Close the cursor
conn.close() # Close the connection to the db
return val
```
#### Yilin Chen 500013544 (task group 1)
* List_classrooms
Shows all classroom information inclusing: classroomid, number of seats and type of room.
```Python3
def list_classrooms():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""SELECT *
FROM UniDB.classroom
ORDER BY classroomid
""")
val = cur.fetchall()
except:
print("Error fetching from database")
cur.close()
conn.close()
return val
```
* get_classroom_with_seat (seat_number)
Allows user to search classroom according the demand seats number:
```Python3
def get_classroom_with_seat(seat_number):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = []
try:
cur.execute("""select classroomid
from UNiDB.classroom
where seats > %s;""",[int(seat_number)])
val = cur.fetchall()
except:
print("Error fetching from database")
cur.close()
conn.close()
return val
```
* get_classroom_report
Shows the number of classroom for each room type (page 3):
```Python3
def get_classroom_report():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
try:
sql = """SELECT type, count(classroomid)
FROM unidb.classroom
GROUP BY type """
cur.execute(sql)
r = cur.fetchall()
except:
print("Error Invalid Login")
cur.close()
conn.close()
return r
```
* add_classroom(classroom_id, seat, room_type)
Allow user to add new classroom. If the given not valid input (eg: "sample" as input for seat number), do nothing:
```Python3
def add_classroom(classroom_id,seat,room_type):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
try:
cur.execute("""INSERT INTO UNiDB.classroom (classroomid,seats,type)
VALUES (%s, %s, %s);""",[classroom_id,seat,room_type])
except:
print("Error wrong information when insert")
conn.commit()
cur.close()
conn.close()
return
```
* update_classroom *(Extention)*
Update classroom seat number according to given classroomid. If given not exist id, do nothing:
```Python3
def update_classroom(classroom_id,seat):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
try:
cur.execute("""UPDATE UNiDB.classroom
SET seats = %s
WHERE classroomid = %s;""",[seat, classroom_id])
except:
print("Error wrong information when update")
conn.commit()
cur.close()
conn.close()
return
```
#### Xiangdong Chen 490575080 (task group 5)
* list_textbooks()
sort out all the textbooks stored in the Offering.
```Python3
def list_textbooks():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""select uoscode,semester,year,uosname,textbook
from unidb.uosoffering natural join unidb.unitofstudy""")
val = cur.fetchall();
except:
print("Error fetching from database")
cur.close()
conn.close()
return val
```
* search_via_textbook(textbook)
sort out all units using the given textbook.
```Python3
def search_via_textbook(textbook):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""select *
from unidb.uosoffering
where textbook = %s;""",[textbook]);
val = cur.fetchall();
except:
print("Search failed")
cur.close()
conn.close()
return val
```
* textbook_usage()
produce the report of all textbooks of which used in different number of units.
```Python3
def textbook_usage():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = []
try:
cur.execute("""select textbook,count(uoscode) as numberofunit
from unidb.uosoffering
where textbook is not null and textbook != 'none'
group by textbook
order by textbook;""");
val = cur.fetchall();
except:
print("Error when produce textbook report")
cur.close()
conn.close()
return val
```
* update_textbook(textbook,uoscode)
Update the textbook for a given unit.
```Python3
def update_textbook(textbook,uoscode):
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""update unidb.uosoffering
set textbook = %s
where uoscode = %s""",[textbook,uoscode]);
val = cur.fetchall();
except:
print("Update textbook failed")
cur.close()
conn.close()
return val
```
* semester_unit() **[Extension]**
output the units in two semesters via two tables.
```Python3
def semester_unit1():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""select * from unidb.whenoffered
where semester = 'S1'""");
val = cur.fetchall();
except:
print("Unable to check Semester 1 units")
cur.close()
conn.close()
return val
def semester_unit2():
conn = database_connect()
if(conn is None):
return None
cur = conn.cursor()
val = None
try:
cur.execute("""select * from unidb.whenoffered
where semester = 'S2'""");
val = cur.fetchall();
except:
print("Unable to check Semester 2 units")
cur.close()
conn.close()
return val
```
### Section 2 ： Testing
This section has two parts, one for SQL query and the other for web page.
For testing **SQL query**, firstly, we test the query by giving data. Then, we modified the record in some specific table and used the modified table to test the query again.
For testing the **web page**, all websides are run and test by other group member's machine. Each page and function would be used and tested in this process.
In addition, **suggestion** would be given after each testing. Both SQL query and web page test are doing by another group member other than the code owner:
* **Functionality Group 1**
* owner: Yilin
* tester: Haolan
* **Functionality Group 4**
* owner: Haolan
* tester: Xiangdong
* **Functionality Group 5**
* owner: Xiangdong
* tester: Yilin
#### SQL query:
1. **Group 4: lecture locations(*Test by Xiangdong*)**
* **query for list_classroom():**
* **code:**
```sql
select classroomid,count(classtime)
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
group by classroomid;
```
* **Test:**
whether giving classroomid & number of units it associated to.
* **Optput:**
* **initial state**: satisfy expectation by showing these two variables.
* **different states** - delete record for "EA404" from unitofstudy:
* satisfy expectation by classroom "Ea404" not be shown in outcome.
* **Suggestion:**
sort output by the number of time
* **query for list_time():**
* **code:**
```sql
select UoSCode, UoSname, semester, year, classtime ,classroomid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
where classtime = %s;
```
* **Test:**
whether return unit within giving classtime.
* **O ptput:**
* **initial state:** satisfy expectation by showing all unit at the given time.
* **different states** - delete semester 2 record for “INFO1003” from unitofstudy:
* satisfy expectation by one less call at "Mon12".
* **Suggestion:**
sort output by year so that user can find targeted unit in a more efficiecy way.
* **query for list_location():**
* **code:**
```sql
select UoSCode, UoSname, semester, year, classtime ,classroomid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode);```
* **Test:**
whether giving UoSCode, UoSname, semester, year, classtime ,classroomid.
* **Optput:**
* **initial state:** satisfy expectation by showing these six variables.
* ** different states** - delete semester 2 record for "INFO1003" from unitofstudy:
* satisfy expectation since final outcome not includes recode for S2 "INFO1003" in 2006.
* **Suggestion:**
sort output by year so that user can find targeted unit in a more efficiecy way.
* **query for check_insert1():**
* **code:**
```sql
SELECT *
FROM unidb.lecture
WHERE uoscode=%s and semester= %s and year = %s and classroomid = %s
```
* **Test:**
whether giving UoSCode, semester, year, classtime ,classroomid.
* **Optput:**
* **initial state**: satisfy expectation by showing these five variables.
* **different states** - delete semester 2 record for “INFO1003” from unitofstudy:
* satisfy expectation: no record being return while choosing uoscode= “INFO1003” and semester= "S2" and year = "2006" and classroomid = "CheLT4".
* **Suggestion:**
sort output by year & semester so that user can find targeted unit in a more efficiecy way.
* **query for check_insert2():**
* **code:**
```sql
SELECT *
FROM unidb.Uosoffering
WHERE uoscode=%s and semester= %s and year = %s
```
* **Test:**
whether giving correct out while giving uoscode, semester, and year.
* **Optput:**
* **initial state**: satisfy expectation by showing record with giving information.
* **different states** - delete semester 2 record for “INFO1003” from unitofstudy:
* satisfy expectation: no record being return while choosing uoscode= “INFO1003” and semester= "S2" and year = "2006" and classroomid = "CheLT4".
* **Suggestion:**
sort output by semester so that user can find targeted unit in a more efficiecy way.
* **query for add_lec(code,semester,year,time,uosid):**
* **code:**
```sql
INSERT INTO UNiDB.lecture (UoSCode,semester,year, classtime ,classroomid)
VALUES (%s, %s, %s, %s, %s);
```
* **Test:**
whether new lecture can be add.
* **Optput:**
* **initial state**: satisfy expectation by showing successful adding new lecturer with valid information.
* **different states** - remove room "CheLT4" from classroom table:
* satisfy expectation: cannot add lecturer in room "CheLT4".
* **query for list_lec():**
* **code:**
```sql
select *
from unidb.lecture
order by Uoscode;
```
* **Test:**
whether giving correct lecture information.
* **Optput:**
* **initial state**: satisfy expectation by showing all lecture information.
* **different states** - remove record for "COMP5046":
* satisfy expectation: outcome not include record for "COMP5046".
* **Suggestion:**
sort output by year so that user can find targeted unit in a more efficiecy way.
* **query for find_uoscode(sid):**
* **code:**
```sql
Select distinct(uoscode)
from unidb.transcript join unidb.uosoffering using(uoscode)
where studid = %s
```
* **Test:**
whether giving correct uoscode by given studid.
* **Optput:**
* **initial state**: satisfy expectation by showing uoscode with given studid.
* **different states** - remove record "ISYS2120" with studid "309145324":
* satisfy expectation: no output while inseting studid "309145324".
* **Suggestion:**
showing number of sid for the same uoscode so that user could be remind that there is another time/semester option for this unit.
* **query for list_classroom2(uos):**
* **code:**
```sql
select UoSCode, UoSname, semester, year, classtime ,classroomid,seats,deptid
from UNiDB.classroom join UNiDB.lecture using (classroomid)
join UNiDB.unitofstudy using(uoscode)
where UoScode = %s
```
* **Test:**
whether giving correct classroom information by given uoscode.
* **Optput:**
* **initial state**: satisfy expectation by showing classroom info with given uoscode.
* **different states** - delete semester 2 record for “INFO1003” from unitofstudy:
* satisfy expectation: only return the record in semester1 for this unit.
* **Suggestion:**
sort output by year and semester so that user can find targeted unit in a more efficiecy way.
2. **Group 1: Classrooms (*Test by Haolan*)**
* **query for list_classrooms():**
* **code:**
```sql
SELECT *
FROM UniDB.classroom
ORDER BY classroomid
```
* **Test:**
whether giving information for all classrooms.
* **Optput:**
* **initial state**: satisfy expectation by showing information for all classroom.
* **different states** - delete record for "BoschLT1" from unidb.classroom:
* satisfy expectation by classroom "BoschLT1" not be shown in outcome.
* **Suggestion:**
sort output by the number of seats
* **query for get_classroom_with_seat (seat_number):**
* **code:**
```sql
select classroomid
from UNiDB.classroom
where seats > %s
```
* **Test:**
whether return classroom satisfy given seats amount.
* **Optput:**
* **initial state:** satisfy expectation by showing all classroom with equal or more than target seats amount.
* **different states** - change seats record for "BoschLT1" from 270 to 200, given demand seats amount 270:
* satisfy expectation by not include "BoschLT1" in result.
* **Suggestion:**
sort output by seats amount so that user can find targeted classroom in a more efficiecy way.
* **query for get_classroom_report():**
* **code:**
```sql
SELECT type, count(classroomid)
FROM unidb.classroom
GROUP BY type
```
* **Test:**
whether return the number of classroom for each type.
* **Optput:**
* **initial state:** satisfy expectation by showing correct classroom amount for each type.
* **different states** - change type for "BoschLT1" to flat:
* satisfy expectation by type "flat" having one more classroom while "tiered" reduce one.
* **Suggestion:**
sort output by seats amount so that user can find targeted classroom in a more efficiecy way.
* **query for add_classroom(classroom_id,seat,room_type):**
* **code:**
```sql
INSERT INTO UNiDB.classroom (classroomid,seats,type)
VALUES (%s, %s, %s);
```
* **Test:**
whether new classroom can be add.
* **Optput:**
* **initial state**: satisfy expectation by showing successful adding new classroom with valid information.
* **different states** - remove classroom "CheLT4" from classroom table and try to add it back:
* satisfy expectation: "CheLT4" could be found again after this operation.
* **query for update_classroom():**
* **code:**
```sql
UPDATE UNiDB.classroom
SET seats = %s
WHERE classroomid = %s;
```
* **Test:**
whether seats amount can be updated.
* **Optput:**
* **initial state**: satisfy expectation by showing successful seats amount updaing.
* **different states** - remove classroom "CheLT4" from classroom table and try to update its seat amount:
* satisfy expectation: no modification since no such classroom.
* **Suggestion:**
Showing the comparsion between before and after the seats modification operation.
3. **Group 5: Textbooks (*Test by Yilin*)**
* **query for list_textbooks():**
* **code:**
```sql
select uoscode,semester,year,uosname,textbook
from unidb.uosoffering natural join unidb.unitofstudy
```
* **Test:**
whether giving information for all textbooks.
* **Optput:**
* **initial state**: satisfy expectation by showing information for all textbooks.
* **different states** - delete semester 2 record for “INFO1003” from unitofstudy:
* satisfy expectation no record for textbook "Snyder" with "INFO1003" in semester 2.
* **Suggestion:**
only returing unit which using at least one textbook
* **query for search_via_textbook(textbook):**
* **code:**
```sql
select *
from unidb.uosoffering
where textbook = %s;
```
* **Test:**
whether return unit information satisfy given textbook.
* **Optput:**
* **initial state:** satisfy expectation by showing all unit with given textbook.
* **different states** - change textbook record for "INFO1003" from "Snyder" to "Hoffer", given demand textbook is Hoffer:
* satisfy expectation by "Hoffer" have 3 unit record instead of 2 (2 is the amount before the textbook changing operation).
* **Suggestion:**
Output includes uoscode and semester instead of showing all information.
* **query for update_textbook(textbook,uoscode):**
* **code:**
```sql
update unidb.uosoffering
set textbook = %s
where uoscode = %s
```
* **Test:**
whether textbook can be updated.
* **Optput:**
* **initial state**: satisfy expectation by showing correct updated textbook.
* **different states** - remove unit "COMP5046" and try to update its textbook:
* satisfy expectation: no modification since no such classroom.
* **Suggestion:**
give warning message while giving uoscode is not exist.
* **query for textbook_usage():**
* **code:**
```sql
select textbook,count(uoscode) as numberofunit
from unidb.uosoffering
where textbook is not null and textbook != 'none'
group by textbook
order by textbook;;
```
* **Test:**
whether the return unit number is correct for each textbook.
* **Optput:**
* **initial state**: satisfy expectation by showing correct number of unit.
* **different states** - change textbook record for "INFO1003" from "Snyder" to "Hoffer", given demand textbook is Hoffer:
* satisfy expectation by "Hoffer" have 3 unit record instead of 2 and "Snyder" only get 1.
* **Suggestion:**
sort the result by unit amount
* **query for semester_unit():**
* **code 1:**
```sql
select * from unidb.whenoffered
where semester = 'S1';
```
* **Test:**
whether giving correct unit for S1
* **Optput:**
* **initial state**: satisfy expectation by showing correct unit.
* **different states** - remove unit "INFO1003" at S1:
* satisfy expectation: not showing this unit in the table
* **code 2:**
```sql
select * from unidb.whenoffered
where semester = 'S2';
```
* **Test:**
whether giving correct unit for S2
* **Optput:**
* **initial state**: satisfy expectation by showing correct unit.
* **different states** - remove unit "INFO3404" at S2:
* satisfy expectation: not showing this unit in the table
* **Suggestion:**
giving one more column to show if that unit having class in other semester.
#### webpage:
1. **Group 1: classrooms (*Test by Haolan*)**
* **page 1: All Classroom**
* **Test:** whether showing Classroom ID Number of seats, Room types
* **Output:** satisfy expectation by showing 3 required variables.
* **Suggestion:** give an option to sort unit number of seat.
* **page 2: Classroom Type**
* **Test:** number of classroom for each type.
* **Output:** satisfy expectation, all 3 types of room are given the number of classes.
* **Suggestion:** sort output by number of classroom.
* **page 3: Search Classroom (*need input*)**
* **Test 1:** whether showing classroom at given number of seats.
* **Output:** satisfy expectation, classrooms at given seat amount are all included.
* **Test 2:** Giving invalid input (*"a" instead of "12"*)
* **Output:** satisfy expectation, not giving classroom at the invalid number of seat.
* **Suggestion:** given an error message to user when input is invalid.
* **page 4: Add Classroom (*need input*)**
* **Test 1:** whether showing updated classroom
* **Output:** satisfy expectation, given lecture information is updated.
* **Suggestion:** only showing the updated classroom.
* **Test 2:** Giving invalid input
* **Output:** satisfy expectation, no modification.
* **Suggestion:** given an error message to user when input is invalid.
* **page 5: Update Classroom seat (*Extension*)**
* **Test:** whether showing new seats amount
* **Output:** satisfy expectation, return collect updated amount for the target classroom.
* **Suggestion:** only shows the target classroom/ put the target one at.
2. **Group 4: lecture locations (*Test by Xiangdong*)**
* **page 1: All locations**
* **Test:** whether showing UoSCode, UoS name, semester, year, and the classtime and classroomid.
* **Output:** satisfy expectation by showing 6 required variables.
* **Suggestion:** give an option to sort unit by year.
* **page 2: Classes in each room**
* **Test:** whether showing number of classes for each room.
* **Output:** satisfy expectation, all 9 rooms are given the number of classes.
* **Suggestion:** sort output by number of classes.
* **page 3: Search unit (*need input*)**
* **Test 1:** whether showing units at given date.
* **Output:** satisfy expectation, units at given date are all given.
* **Suggestion:** given a table only shows unit in current/given year.
* **Test 2:** Giving invalid input (*Mon instead of Mon12*)
* **Output:** satisfy expectation, no unit is given at the invalid date.
* **Suggestion:** given an error message to user when input is invalid.
* **page 4: Add lecture (*need input*)**
* **Test 1:** whether showing update lecture information
* **Output:** satisfy expectation, given lecture information is updated.
* **Suggestion:** only showing the updated lecture.
* **Test 2:** Giving invalid input
* **Output:** satisfy expectation, no modification.
* **Suggestion:** given an error message to user when input is invalid.
* **page 5: Find Classroom (*Extension*)**
* **Test:** whether showing target classroom
* **Output:** satisfy expectation, return output is correct & drop down box is working and all unit options are given.
* **Suggestion:** add find by year option.
3. **Group 5: textbooks (*Test by Yilin*)**
* **page 1: All textbooks in use**
* **Test:** whether showing all testbooks information.
* **Output:** satisfy expectation by showing 5 required textbook variables.
* **Suggestion:** give an option to sort unit by year.
* **page 2: Search unit with Textbook**
* **Test:** whether returning units according to given textbook.
* **Output:** satisfy expectation, units could be found by given textbook.
* **Suggestion:** have option to only show distinct unit.
* **page 3: Use of Textbook**
* **Test:** whether showing correct number of units for each textbook.
* **Output:** satisfy expectation, the number of unit is correct for each textbook.
* **Suggestion:** have option to sort result by desending unit amount order.
* **page 4: Update Textbook (*need input*)**
* **Test 1:** whether showing correct updated textbook information
* **Output:** satisfy expectation, given textbook information is updated.
* **Suggestion:** only showing the updated textbook.
* **page 5: Unit of semester (*Extension*)**
* **Test:** whether showing correct unit for semester 1 & 2.
* **Output:** satisfy expectation, return output is correct when choosing S1 & S2.
* **Suggestion:** Highlight the unit which only having in one semester.
### Section 3 : Security of the system
This web-page application is mainly developed via *PostgreSQL* through the *pgAdmin* platform and python through the *JupyterNotbook*.
* **Confidentiality**
When installing *PostgreSQL*, one configuration file is added to the directory for controlling the client authentication. This file basically specifies different connection types via set of records, thus the database is only accessible for those authorized and recorded individuals. This is literally to achieve confidentiality.
The deployment of *pgAdmin* eases the modification on the file *pghba.conf*. The owner or manager of the database has the highest privilege to grant the developer and therefore end-user the read/write access to given relations.
Though still there can be suggestions on the web-system. From the *CIA* practices, it is best to keep data encrypted using two-factor-authentication (*2FA*). The login system is simply checking the stored details of students and direct to the main page once given correct input. This means the malware such as key-logger would have a chance to directly log in and edit the webpage and therefore the database.
So one appreciable suggestion is to implement multi-factor authentication. For instance the canvas system requires the student details and the support of the okta-verify system to double check the identity of request.
Another possible suggestion is against password attacks like Brute Force or targeted guess. By restricting the attempt number and employing the *CAPTCHAs*, the cost of attack would be expensive which therefore ensures prevention to some extent.
* **Integrity**
Ideally PostgreSQL itself is secure enough to guarantee integrity. To detect any inconsistency in the database, *PostgreSQL* updated a method called *pgchecksums* which scans every file in the cluster.
For instance, once the database has been altered in a non-permissible way, the checksums function would be able to detect the failure and notify the receiver (generally the manager) to react to it.
For the web-page application, the *SQL* injection attack would happen since the end-user is normally allowed to provide input and execute unchecked.
One feasible prevention of integrity is on code-base. To catch the unexpected input and throw exceptions, the illegitimate and malicious alterations may be detected.
From reality, the utilization of the version control system allows the roll-back to previous states and therefore minimizes the integrity loss.
* **Availability**
Reaching Availability is supposed to provide intended service for intended users.
The web-page application is designed for students whereas several functions are not expected to perform, such as changing textbooks or adding classrooms.
It could be better to redesign the structure and the login system. Those requests involving alterations should be provided for administrative staff while the students should only access and read.
* **Summary**
Every security mechanism has costs as well as benefits, so the key factor is to consider the trade-off.
for example, the installation of authentication helps to ensure non-repudiation whereas it possibly leads to violation of confidentiality. This conflict does not mean the deployment of authentication is absolutely incorrect, but eventually ensures the total security.
This prototype application is of great trial and functionalities are rich. Still there is a probable suggestion for future versions.
* **Auditability**
The organization needs to catch the actions happening such as who altered which row. Audit information is useful after a security failure has occurred so that new exceptions would be analyzed and caught.
### Section 4 : Extensions
- **Group 1 extension(Done by Yilin 500013544)**
As shown in the figure, we have reached the function of modifying the number of seats in the updated classroom, as we also provide the function of displaying the classroom information. We need to get the Select and update permission of UniDB.classroom.Sql DDL statements as follows
```sql
Grant select on UNidb.classroom to y22s2i2120_yche6001;
Grant update on UNidb.classroom to y22s2i2120_yche6001;
```
- **Group 4 extension (Done by Haolan 500184187)**
The expanded functionality of Group 4 consists of two main areas, one is to provide a drop-down selection box based on the UNITS selected in the student transcript, and the other is to provide all classroom information for that UNITS by the UOScode selected in the drop-down selection box. The Sql DDL statements are as followed.
```sql
Grant usage on schema UNidb to y22s2i2120_hzou2291 ;
Grant select on UNidb.transcript to y22s2i2120_hzou2291;
Grant select on UNidb.uosoffering to y22s2i2120_hzou2291;
Grant select on UNidb.classroom to y22s2i2120_hzou2291;
Grant select on UNidb.lecture to y22s2i2120_hzou2291;
Grant select on UNidb.unitofstudy to y22s2i2120_hzou2291;
```
- **Group 5 extension(Done by Xiangdong 490575080)**
For the Group 5 extension we chose to use two tables on the page to show the courses in Semester 1 and Semester 2 respectively.The DDL statement used is as follows.
```sql
Grant usage on schema UNidb to y22s2i2120_xche3719 ;
Grant select on UNidb.student to y22s2i2120_xche3719;
Grant select on UniDB.whenoffered to y22s2i2120_xche3719;
```
Google Drive
Gist
Clipboard
Sign in with Wallet
