Database Implementation & Security for Remarkable University Grading

This project focuses on the implementation of a student grading system database, addressing key aspects such as database design improvements, SQL implementation, user and privilege management, SQL injection testing, and backup strategies. The database design improvements include normalization to eliminate data redundancy and capture all constraints. The project details the creation of users with specific privileges, including students, admin staff, and academic staff, and the assignment of table-level privileges. It also covers the creation of views and related privileges, such as views for student grades and academic staff enrollments. SQL injection testing is performed to identify vulnerabilities in the web interface, and a backup strategy is developed to protect the data against calamities and system failure. The project also discusses advanced data management using MySQL, cloud-based databases, and graph databases, comparing their advantages and disadvantages. The report includes SQL scripts, ER diagrams, and explanations of database design choices, user privileges, SQL injection tests, and backup strategies.

PART A. Database Implementation
1. Database Design
Improvements Made
To eliminate data redundancy and capture all the constraints, a further normalization of the
tables was done, which introduced additional tables. The main improvements include;
 The Student table was left as it is, because it was already in 3rd Normal Form
 Academic_Staff and Admin_Staff tables were merged to create a single Staff table
which holds all the staff data.
 The position and duty of members of staff is captured in the new Role table, which
holds data about the designation of members of staff
 A new table ; Staff_Category was introduced which holds data that shows whether a
member of staff is an Academic staff or admin staff
 A number of fields were removed from the table to eliminate redundancy; only two
attributes were left in the table; course_id and course_name

 Redundancy in the Enrolment table was eliminated by eliminating the student_name
attribute and creating a relationship with the student table, by making the attribute
Student_id to be the foreign key in this table.
 To ensure that each grade record is for a given enrolment in the enrolment table, the
enrolment_id attribute in table grade was made to be the primary key for the table, as
well as the foreign key, linking grade to enrolment. This ensures that a given grade is
related to an existing record in the enrolment table and that for a given enrolment,
only one grade can be recorded.
 The user table was modified to introduce a mutually exclusive relationship, where a
given user can either be a student or a staff but not both.
After the modification the data in the table looks as shown below.

2. SQL Implementation (4%)
SQL File Attached

PART B. Users and Privileges
1. Create Users
Create Students users
 CREATE USER 's01'@'localhost' IDENTIFIED BY 's01';
 CREATE USER 's02'@'localhost' IDENTIFIED BY 's02';
Create Admin staff users
 CREATE USER 'f01'@'localhost' IDENTIFIED BY 'f01';
 CREATE USER 'f02'@'localhost' IDENTIFIED BY 'f02';
Create Academic staff users
 CREATE USER 'a01'@'localhost' IDENTIFIED BY 'a01';
 CREATE USER 'a02'@'localhost' IDENTIFIED BY 'a02';
2. Assign Table-level Privileges
Grant Students right to select courses
 GRANT SELECT ON gradingsystemdb.course TO ‘S01’@'localhost’;
 GRANT SELECT ON gradingsystemdb. course TO ‘S02’@'localhost’;
Grant Select, Insert, Update on Enrolments to admin staff in charge of enrolments
 GRANT Select,Insert,Update ON enrolment TO ‘f01’@'localhost’;
Grant Select, Insert, Update on Enrolments to admin staff in charge of enrolments
 GRANT Select,Insert,Update ON course TO ‘f02’@'localhost’;
3. Create Views and Related Privileges
View to show grades for a given student; for this case we create a view for student with
student id "s01"
CREATE VIEW student_grade AS
SELECT student.first_name
, student.last_name

, course.course_name
, grade.score
, grade.grade
FROM
gradingsystemdb.enrolment
INNER JOIN gradingsystemdb.course
ON (enrolment.course_id = course.course_id)
INNER JOIN gradingsystemdb.student
ON (enrolment.student_id = student.student_id)
INNER JOIN gradingsystemdb.grade
ON (grade.enrolment_id = enrolment.enrolment_id)
INNER JOIN gradingsystemdb.student_user
ON (student_user.student_id = student.student_id)
WHERE (student.student_id = 's01') ;
Assign the View to the specific user;
 GRANT SELECT ON student_grade TO s01;
Test if the user can use the view by selecting the view

A view for an Academic staff ; can only see the enrolment of the courses they teach
The view shows courses taught by academic staff ; a01 : Professor Seb Binary
CREATE VIEW enrolments AS
SELECT DISTINCT
course.course_name, student.first_name, student.last_name, enrolment.year
, enrolment.trimester, enrolment.campus, enrolment.enrolment_id
FROM
gradingsystemdb.taught_by
INNER JOIN gradingsystemdb.staff
ON (taught_by.staff_id = staff.staff_id)
INNER JOIN gradingsystemdb.course
ON (taught_by.course_id = course.course_id)
INNER JOIN gradingsystemdb.enrolment
ON (enrolment.course_id = course.course_id)
INNER JOIN gradingsystemdb.student
ON (enrolment.student_id = student.student_id)
WHERE (staff.staff_id ='a01');
Grant Select rights on enrolments to a01
 GRANT SELECT ON enrolments TO a01
Test if the professor can view enrolments for courses he teaches

PART C. SQL Injection Test
The first task was to check if the web interface was vulnerable to SQL injection. To test this,
a comma was placed in the "Sex" field; as shown in the screen shot below;
The interface returned the following error message; indicating that the system was
vulnerable;
With these information, an SQL injection can be executed by closing inserts into the database
at the "Sex" field 5,, and then using the phone field 6 to send a payload that will cause an
SQL injection.
For this excersice an SQL injection will be launched that will insert a record into the Users
table.

The SQL injection was launched using the two last fields; Sex and Phone
Sex: M','9666');/*
 This data ensures that the insert statement into the student database is completed and
terminated using the semi colon and bracket.
 The /* is meant to create a block comment which takes care of the comma between
Sex and Phone.
Phone: */ INSERT INTO user (user_id) VALUES ('8555'); --
 the data in the phone field starts with a */ which closes the block comment that takes care of
apostrophise and the comma between Sex and Phone.
 the insert statement inserts only the user id in the users table
 the statement is terminated using a semi colon
 the hyphens at the end of the insert statement create a single line comment; meaning that anything
after the insert statement is considered as a comment ; the use of the comment is to take care of the
last single quote and closing bracket.
The full SQL code that is executed on the server is as follows;
INSERT INTO student (student_id, first_name, last_name, DOB, sex, phone) VALUES ('s8555', 'Mag', 'Tudor',
'5/8/1995', 'M','9666'); /*', '*/ INSERT INTO `user` (`user_id`) VALUES ('8555'); -- ')
After executing the insert was successful as shown below
A check inside the users table confirmed that the SQL injection was successful as the record was
added in that table.
The approach can be used to launch any type of SQL injection.

Backup strategy
A backup strategy is vital to safeguard with the intention of protecting your data against
calamities and system failure. A duplicate of the data is restored hence data will be
recovered.The student data, the courses and the enrollment should be backed up more often
as these tables are updating more often. The database will backup based on the three backup
strategies depending on the disk space in the backup server :
Full back up this strategy allows backing up of the wholedatabasewhich we may chose to run
periodically or at the end of the day.
Incremental backup this strategy will result in backing up only data that has changed since
the last backup. This has an advantage of size and speed as the data backed up is minimal and
takes lesser time.

Differential backups this strategy will only backup data from a set point in time and hence
will backup data that changes against a set point it will require more space and time than an
incremental strategy but less time than a full backup
Advanceddata management
MySQL
This database is free to use and open source which means there are no proprietarylicenses
required for its use. MySQL is one of the most secure and reliable database management
systems used by most open source web applications like Drupal, Facebook, andTwitter. It
secure enough to protect client data and even perform safe transactions. MySQLscalability is
unmatched to allow small processes even in big data warehouses. It allows to customize the
database to unique specifications. It allows large transactions at any speed with no lag.
MySQL does not experience downtime since it has solutions like cluster servers and
master/slave configurations. MySQL offers a complete transactional support with features
like unrestricted row-level locking, consistent and complete atomic, isolated, durable support
even for multi-version transactions. Itssimple configuration allows timely installation and
implementation. MySQL is free hencereduces the cost of ownership when deploying web
applications.The open sourcelicense allows the enterprise to fully customize the management
system(Singh, 2011).
However,MySQL does not have an inbuilt ERP embedded into its management system and
thisis by regulations like Sarbanes-Oxley as a crucial entity of database management systems.
MySQL does not support OLAT, ETL and Crystal Reports, these services make database
management easier especially when handling big data. MySQL does not have the advantage
when handling geospatial datasets over SQL server which has native data types that deal with
such data. MySQL does not support checkconstraints. MySQL does not support active

directory services like Kerberos and Windows authentication. Proprietary database
management systems have native algorithms in their functionalities that allow faster
processing of large tables.
cloud based databases host their data on remote servers that means the servers are not on site
but are based in secure locations. Theseservers connect to the systems via the internet. Most
servers are third party arrangementsmeaning that the enterprise may not necessarily own.The
enterprise does not have to worry about scaling by adding or subtracting nodes from a cluster
to add space as the hired firm will handle those technicalities. Cloud based database reduce
administrative burden as the inhouse database manager may focus on more issues other than
specializing which can cause team bottlenecks. Cloud based databasesare stored in hidden
locations.
However, this technology does not allow full customization as these servers host multiple
databases hence they have devised a common architecture to cater for all their clients. Due to
the nature of transmission which is the internet, they sometime experience downtime. They
also experience large scale outages(Singh, 2011).
Graph databases are designed to treat relationships between data as equally important to the
data itself. Graph databases use graph structure for queries with nodes, edges,and properties
to display and store data.graph databases offer flexibility as data captured may easily be
changed and extended for additional attributes and objects. They also offer fast relationship-
based searches. Graphdatabases offer naturally indexed relationships, this provides better
access compered to relational database management systems. Graph databases are not
efficient to perform large transactions and handling queries that span the entire
databases(Singh, 2011).