Imagine that you are hired to create a basic database and a basic report for a school. In the SQL comments at the top of the script, write a short description of the school and how the database is used by the school personnel. (some examples: “This database is used to register new students at Dog Obedience School”, “The Beauty Academy database determines how much revenue each class generates”, “Lucky Brake Driving School Database tracks accident rates across classes”, “CUNY SPS BSIS program could track per cent of students working in the field that have taken this course”, etc. You should create and populate tables – using SQL exclusively – for Courses and Students. Each table should contain at least four or five rows. You are encouraged to add one or more new columns that would be of interest to the people who use the information in your database, as you have described in number one above. Write a SQL Query that shows which students are taking which courses. You need to create an addi

School Enrollment Management System SQL Script

Drop table if exists Student;
Drop table if exists Course;

— Question 1
— The database is used to register students and assign them courses in a school

— Question 2
— This script creates a student’s table
create table Student
(
StudentID int primary key,
FirstName varchar(100),
LastName varchar(100),
Address_ varchar(300),
PhoneNumber int,
DOB date,
AdmissionDate date
);

— This script populates the Student table
insert into Student values(100, ‘John’, ‘Doe’, ‘813 Howard St Oswego NY 13126’, ‘149065488’, ‘1990-07-22’, ‘2018-09-20’);
insert into Student values(101, ‘Becky’, ‘Brown’, ’71 Cherry Court Southampton’, ‘409812345’, ‘1992-01-01’, ‘2018-09-20’);
insert into Student values(102, ‘George’, ‘Slim’, ’32 hh Austin St TX’, ‘1490833334’, ‘1983-11-27’, ‘2012-01-15’);
insert into Student values(103, ‘Suzzie’, ‘White’, ’44 tyu Chicago St Illinois’, ‘12789045’, ‘1985-02-12’, ‘2015-10-05’);
insert into Student values(104, ‘David’, ‘Beattle’, ’15 YY Tacoma St Seattle’, ‘12999564’, ‘1991-05-17’, ‘2019-09-02’);

— This script creates a course table
create table Course
(
CourseID int primary key,
CourseName varchar(100),
CourseDescription varchar(100),
CourseCredits int,
CourseDuration varchar(100),
LecturerID int
);

— This script populates the Course table
insert into Course values(200, ‘Software Application Programming I’, ‘Skills on developing a program’, 3, ’16 weeks’, 303);
insert into Course values(201, ‘Computer Network Security’, ‘Skills on setting up and maintaining network security’, 3, ’16 weeks’, 310);
insert into Course values(202, ‘Systems Analysis and Design’, ‘Skills on analyzing a system and designing a solution’, 3, ’16 weeks’, 322);
insert into Course values(203, ‘Networks and Business Data Communication’, ‘Skills on designing and implementing communication on networks’, 3, ’16 weeks’, 311);
insert into Course values(204, ‘Database Architecture and Programming’, ‘Skills on designing and evluating databases’, 3, ’16 weeks’, 333);

— Question 3
— This script adds a foreign key column on Student table to enable database users pull admission reports from Student and Course tables
Alter table Student
add CourseID int,
foreign key (CourseID) references Course(CourseID);

— This script assigns courses to students on Student table
update Student set CourseID = 200 where studentID = 100 or studentID = 102;
update Student set CourseID = 203 where studentID = 101 or studentID = 104;
update Student set CourseID = 201 where studentID = 103;

— Question 4
— This script displays students and the courses they are enrolled in
select Student.StudentID, Student.FirstName, Student.LastName, Course.CourseID, Course.CourseName, Course.CourseDescription
from Student
inner join
Course on Student.CourseID = Course.CourseID