2808ICT Assignment 2: Database Design and Security Measures

This assignment solution presents a comprehensive database implementation for a student grading system, addressing design, SQL implementation, security, and backup strategies. The solution begins with database design, including the creation of tables for students, staff, courses, course taught, and enrollment, along with corresponding SQL scripts for database and table creation in XAMPP (MySQL). It then covers SQL implementation, including insert queries to populate the tables with sample data. The solution also delves into user creation and privilege assignment, establishing different user roles (student, academic staff, and admin staff) with specific table-level privileges. Additionally, the solution demonstrates the creation of views to enhance data security and control access. A significant portion is dedicated to SQL injection testing, including an explanation of the process and results. Finally, the assignment concludes with a discussion on database backup strategies, including the importance of backups, different backup types (full and differential), and recommendations for backup retention policies. This solution provides a well-structured approach to database design and implementation, emphasizing security and data integrity.

PART A. Database Implementation:
1. Database Design
2. SQL Implementation:
Below is the script used to create database and corresponding tables in XAMMP (MySQL):
DROP DATABASE IF EXISTS Assignment2;
CREATE DATABASE Assignment2;
USE Assignment2;
Create table student(

student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50) not null,
last_name varchar(50) not null,
DOB DATE not null,
sex varchar(1) not null,
phone varchar(20) not null
);
Create table staff(
staff_id INT AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50) not null,
last_name varchar(50) not null,
DOB DATE not null,
sex varchar(1) not null,
phone varchar(20) not null,
type_of_staff varchar(20) not null
);
Create table course(
course_id varchar(20) PRIMARY KEY,
course_name varchar(30) Not null,
description varchar(100)
);
Create table course_taught(
taught_id INT AUTO_INCREMENT PRIMARY KEY,
course_id varchar(20) Not null,

academic_staff_id INT Not null,
CONSTRAINT fk_academic_staff_taught
FOREIGN KEY (academic_staff_id)
REFERENCES staff(staff_id),
CONSTRAINT fk_course_taught
FOREIGN KEY (course_id)
REFERENCES course(course_id)
);
Create table Enrollment(
enroll_id INT AUTO_INCREMENT PRIMARY KEY,
course_id varchar(20) Not null,
student_id INT Not null,
admin_staff INT Not null,
dateofEnrollment DATE Not null,
grade varchar(10),
CONSTRAINT fk_admin_staff_enroll
FOREIGN KEY (admin_staff)
REFERENCES staff(staff_id),
CONSTRAINT fk_course_enroll
FOREIGN KEY (course_id)
REFERENCES course(course_id),
CONSTRAINT fk_student_enroll
FOREIGN KEY (student_id)
REFERENCES student(student_id)
);

Snapshot of Database and table creation:

Database created with the below tables:
Insert Queries to populate the tables:
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (1, 'Randi', 'Dowrey',
'1997-01-14', 'F', '786 180 8466');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (2, 'Rorke', 'Dray', '1997-
08-15', 'M', '757 303 3683');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (3, 'Dulcea', 'Ayce',
'1996-11-12', 'F', '236 230 4641');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (4, 'Georgie', 'Cogswell',
'1997-05-13', 'F', '422 428 9305');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (5, 'Brenden', 'Parken',
'1997-09-13', 'M', '882 120 4222');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (6, 'Paxton',
'Berthouloume', '1997-06-01', 'M', '809 186 5247');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (7, 'Alaster',
'Ferneyhough', '1997-12-07', 'M', '183 585 5016');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (8, 'Alonzo', 'Surgen',
'1998-10-01', 'M', '469 968 0689');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (9, 'Carree', 'Armistead',
'1997-02-20', 'F', '957 358 3070');
Insert into student (student_id, first_name, last_name, DOB, sex, phone) values (10, 'Xylina', 'Donat',
'1998-08-23', 'F', '663 638 8120');

Insert Queries for staff table:
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (1, 'Mathilda',
'Sloegrave', '1998-06-24', 'Female', '994 171 3671', 'admin');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (2, 'Sayres',
'Addison', '1998-07-28', 'Male', '910 868 0446', 'academic');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (3, 'Terrijo',
'FitzAlan', '1998-09-16', 'Female', '919 127 8879', 'academic');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (4, 'Caroljean',
'Anersen', '1998-03-25', 'Female', '292 224 3109', 'academic');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (5, 'Veradis',
'Bugs', '1997-05-29', 'Female', '113 844 5036', 'academic');

Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (6, 'Elsbeth',
'Varley', '1997-09-13', 'Female', '305 153 6191', 'admin');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (7, 'Kally',
'Derrett', '1998-07-21', 'Female', '313 589 2753', 'academic');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (8, 'Verge',
'Philipsen', '1997-07-15', 'Male', '162 388 5194', 'academic');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (9, 'Calypso',
'Tinsley', '1997-10-29', 'Female', '114 690 7508', 'admin');
Insert into staff (staff_id, first_name, last_name, DOB, sex, phone, type_of_staff) values (10, 'Teresita',
'Watsham', '1997-06-05', 'Female', '919 973 4277', 'admin');
Insert Queries for Course table:
Insert into course values ('CSC1242','Algortihms','Algortihm and Tunning machine');
Insert into course values ('CSC8141','Database programming','MySQL and Sql injections');
Insert into course values ('CSC1292','Machine learning','Machine learning');
Insert into course values ('CSC1224','Data science','Data science');
Insert into course values ('CSC1225','Python programming','Python programming');

Insert queries for Course_taught table:
Insert into course_taught (course_id, academic_staff_id) values ('CSC1292',2);
Insert into course_taught (course_id, academic_staff_id) values ('CSC1292',3);
Insert into course_taught (course_id, academic_staff_id) values ('CSC8141',4);
Insert into course_taught (course_id, academic_staff_id) values ('CSC1292',4);
Insert into course_taught (course_id, academic_staff_id) values ('CSC1225',5);
Insert into course_taught (course_id, academic_staff_id) values ('CSC1224',7);
Insert into course_taught (course_id, academic_staff_id) values ('CSC1242',8);

Insert Queries for Enrollment Table:
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1292',1,6,'2019-09-12','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC8141',2,6,'2019-09-20','A');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1225',3,6,'2019-08-22','D');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1242',1,9,'2019-07-11','C');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1224',4,6,'2019-04-24','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1242',5,6,'2019-05-25','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1225',6,10,'2019-06-12','A');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC8141',4,1,'2019-07-17','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC8141',7,1,'2019-07-09','C');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1292',8,6,'2019-03-04','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1224',7,10,'2019-01-02','C');

Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1292',9,9,'2019-05-09','B');
Insert into Enrollment (course_id,student_id,admin_staff,dateofEnrollment,grade) values
('CSC1242',9,9,'2019-06-10','D');
Records in all tables:

PART B. Users and Privileges
1. Create Users:
Below users are created (Student user as prefix as “stud”, academic staff has “acstaff” and admin staff
has “acstaff” as prefix.
CREATE USER 'stud_randi'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'stud_Rorke'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'acstaff_Kally'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'acstaff_Verge'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'adm_calypso'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'adm_teresita'@'localhost' IDENTIFIED BY 'password';

2. Assign Table-level Privileges
Adding Admin staff to permission on Course table and Enrollment table:
GRANT Update ON assignment2.enrollment TO 'adm_calypso'@'localhost';
GRANT Update ON assignment2.course TO 'adm_teresita'@'localhost';
Give viewing permission to Students and Academic Staff on Course:
GRANT SELECT ON assignment2.course TO 'stud_randi'@'localhost';
GRANT SELECT ON assignment2.course TO 'stud_Rorke'@'localhost';
GRANT SELECT ON assignment2.course TO 'acstaff_Verge'@'localhost';
GRANT SELECT ON assignment2.course TO 'acstaff_Kally'@'localhost';