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