Database Implementation Assignment 2022

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';

Set Privilege to Academic staff to see the names and genders of the students but birthdays or phone
numbers of student.
GRANT SELECT (student_id,first_name,last_name,sex) ON assignment2.student TO
'acstaff_Kally'@'localhost';
GRANT SELECT (student_id,first_name,last_name,sex) ON assignment2.student TO
'acstaff_Verge'@'localhost';

3. Create Views and Related Privileges:
Views are created for security purpose as they encapsulate the name of the tables and only shows the
required columns and user cannot view all the columns of table. Additionally the data is not stored and
it can pull only the data which is related to that user. View hides the joins of different tables and it
shows as a single table.
View for student to see only the grades:
Create view check_grades AS
Select student_id,grade from enrollment;

Academic staff can only see the enrolment of the courses they teach.
CREATE view view_enrollments as
Select first_name,dateOfEnrollment,grade,course_taught.academic_staff_id from enrollment
inner join course_taught
on course_taught.course_id=enrollment.course_id
inner join student
on student.student_id=enrollment.student_id;
Academic staff can only modify grades of the course they teach.
GRANT Update ON assignment2.view_enrollments TO 'adm_calypso'@'localhost';
GRANT Update ON assignment2.view_enrollments TO 'adm_teresita'@'localhost';

PART C. SQL Injection Test:
1) Download the injection zip and extract the files:
Save the file in the “Explorer” folder in the XAMMP server:

2) Similarly place the *.php files in same folder:
3) When address localhost/index.html is type on the browser the form for student is open up:

4) Tried to add injection in it:
When correct input is given to the form:

5) Explain what you are trying to input and what the expected results are.
We are trying to delete records from the table by passing the query. The results are that the records
are deleted.

6) Renamed the file pdo_main.php to main_php:
7) Trying the same activity with the new file:
It is considering as value and filtering for sql injection:

PART D. Database Backup Strategy:
1. Importance of backup strategy:
 Customer Protection:
If any customer has lost the data, then from the database backup the data can be restored.
Following are the backup strategies:
1. Full Database Backup:
Full database backup provides assurance as the multiple copy of database is stored at different
backup location and in case of any issues at main database the copy of back up is restored.
2. Differential Backup:
This kind of backup only those part which is updated from previous backup. This kind of strategy
is used to reduce the number of logs in the backup. This is useful we have to restore only those
specific changes.
Portions of the database should be backed up:
If it is banking domain then the complete database must be backed up on daily basis, but if it is other
domain such as Student database then the strategy must be used as Differential backup and it should
cover the tables such as student details, staff details and should do back up on alternate days.
Backups should be retained before the newest backup overwrites the oldest:
There must exists 30 days backup and going forward backup of database which 30 days old is replaced
by the latest back up.
Backup of database: