Database System Project: Student Enrollment Database, COS515

This project report details the design and implementation of a student enrollment database system. It begins with a project intent outlining the system's purpose in schools for course enrollment, managed by an enrollment clerk. The report then progresses through the conceptual design, logical design, and implementation phases. The logical design includes functional dependencies and a relational schema defining tables for students, courses, tutors, and enrollment. The implementation phase provides SQL code for creating tables and inserting data. Furthermore, SQL queries are provided to demonstrate data retrieval, including queries for courses taught by specific tutors and calculations of minimum, maximum, and average values for tutor salaries and course fees. Finally, the report addresses advanced SQL aspects, focusing on database security and user privileges, concluding with a bibliography. This project provides a comprehensive overview of database design and implementation principles.

Running head: DATABASE SYSTEM
Database System
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM
Phase 0: Project Intent
The project that is being discussed in the project is a student enrollment project. The
system would be generally used in the schools for the students’ enrollment. The system
would be offering the students a number of courses and hence the students would be enrolled
in in the courses under a particular tutor whose records are stored in the system. The
enrollment clerk would generally handle the system. However, the instances of the
enrollment clerk has not been included in this report. However the clerk would be performing
all the functions. This system would also act effectively in reducing the time consumptions of
process in the organization and provide some better data maintenances options for the
organization.
The report contains the description of the database to be used in the system. The ER
Diagram, the logical design and conceptual designs of the database has been included in the
report. Some additional illustrations have also been described with the help of queries in the
report.

2
DATABASE SYSTEM
Phase 1: Conceptual Design
Phase 2: Logical Design
Functional Dependencies
StudentID -> StudentName, Age
CourseID -> CourseName, CourseDuration, CourseFee
TutorID -> TutorName, TutorSalary
EnrollmentID -> StudentID, CourseID, TutorID
Relational Schema
Student (StudentID (primary key), StudentName, Age)
Course (CourseID (primary key), CourseName, CourseDuration, CourseFee)
Tutor (TutorID (primary key), TutorName, TutorSalary)

3
DATABASE SYSTEM
Enrollment (EnrollmentID (primary key), StudentID, CourseID, TutorID)
Phase 3: Implementation
Database tables and data
Tutor table
CREATE TABLE "SYSTEM"."TUTORS"
( "TUTORID" NUMBER NOT NULL ENABLE,
"TUTORNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"TUTORSALARY" NUMBER NOT NULL ENABLE,
CONSTRAINT "TUTORS_PK" PRIMARY KEY ("TUTORID")
INSERT INTO tutor VALUES (1, ‘David’, 300);
INSERT INTO tutor VALUES (2, ‘Stuart’, 500);
INSERT INTO tutor VALUES (3, ‘Kelly’, 800);
INSERT INTO tutor VALUES (4, ‘James’, 500);
INSERT INTO tutor VALUES (5, ‘Harris’, 700);

4
DATABASE SYSTEM
Student Table
CREATE TABLE "SYSTEM"."STUDENT"
( "STUDENTID" NUMBER NOT NULL ENABLE,
"STUDENTNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"AGE" NUMBER NOT NULL ENABLE,
CONSTRAINT "STUDENT_PK" PRIMARY KEY ("STUDENTID")
INSERT INTO student VALUES (1, ‘Chris’, 13);
INSERT INTO student VALUES (2, ‘Darren’, 15);
INSERT INTO student VALUES (3, ‘Henry’, 8);
INSERT INTO student VALUES (4, ‘Denis’, 19);
INSERT INTO student VALUES (5, ‘Crystal’, 14);
Courses Table
CREATE TABLE "SYSTEM"."COURSES"
( "COURSEID" NUMBER NOT NULL ENABLE,

5
DATABASE SYSTEM
"COURSENAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"COURSEDURATION_MIN" NUMBER NOT NULL ENABLE,
"COURSEFEE" NUMBER NOT NULL ENABLE,
CONSTRAINT "COURSES_PK" PRIMARY KEY ("COURSEID")
INSERT INTO courses VALUES (1, ‘English’, 1300, 900);
INSERT INTO courses VALUES (2, ‘Maths’, 1500, 800);
INSERT INTO courses VALUES (3, ‘Computer’, 1800, 750);
INSERT INTO courses VALUES (4, ‘Science’, 1900, 850);
INSERT INTO courses VALUES (5, ‘Spanish’, 1400, 600);
Enrollment Table
CREATE TABLE "SYSTEM"."ENROLLMENT"
( "ENROLLMENTID" NUMBER NOT NULL ENABLE,
"COURSEID" NUMBER NOT NULL ENABLE,

6
DATABASE SYSTEM
"TUTORID" NUMBER NOT NULL ENABLE,
"STUDENTID" NUMBER NOT NULL ENABLE,
CONSTRAINT "ENROLLMENT_PK" PRIMARY KEY ("ENROLLMENTID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "ENROLLMENT_COURSES_FK1" FOREIGN KEY
("COURSEID")
REFERENCES "SYSTEM"."COURSES" ("COURSEID") ENABLE,
CONSTRAINT "ENROLLMENT_STUDENT_FK1" FOREIGN KEY
("STUDENTID")
REFERENCES "SYSTEM"."STUDENT" ("STUDENTID") ENABLE,
CONSTRAINT "ENROLLMENT_TUTORS_FK1" FOREIGN KEY ("TUTORID")
REFERENCES "SYSTEM"."TUTORS" ("TUTORID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING

7
DATABASE SYSTEM
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
INSERT INTO enrollment VALUES (1,1,1,4);
INSERT INTO enrollment VALUES (2,2,1,5);
INSERT INTO enrollment VALUES (3,4,2,5);
INSERT INTO enrollment VALUES (4,3,2,4);
INSERT INTO enrollment VALUES (5,2,3,3);
INSERT INTO enrollment VALUES (6,5,3,2);
INSERT INTO enrollment VALUES (7,1,4,3);
INSERT INTO enrollment VALUES (8,5,4,2);
INSERT INTO enrollment VALUES (9,3,5,1);
INSERT INTO enrollment VALUES (10,2,5,1);

8
DATABASE SYSTEM
SQL Queries
Query 1: Course taught by Harris
SELECT tutors.tutorname, courses.coursename from tutors
INNER JOIN enrollment ON enrollment.tutorid = tutors.tutorid
INNER JOIN courses ON enrollment.courseid = courses.courseid
WHERE tutors.tutorname = 'Harris';

9
DATABASE SYSTEM
Query 2: Course taught by James
SELECT tutors.tutorname, courses.coursename from tutors
INNER JOIN enrollment ON enrollment.tutorid = tutors.tutorid
INNER JOIN courses ON enrollment.courseid = courses.courseid
WHERE tutors.tutorname = 'James';

10
DATABASE SYSTEM
Query 3: Course taught by Kelly
SELECT tutors.tutorname, courses.coursename from tutors
INNER JOIN enrollment ON enrollment.tutorid = tutors.tutorid
INNER JOIN courses ON enrollment.courseid = courses.courseid
WHERE tutors.tutorname = 'Kelly';

11
DATABASE SYSTEM
Query 4: Course taught by Staurt
SELECT tutors.tutorname, courses.coursename from tutors
INNER JOIN enrollment ON enrollment.tutorid = tutors.tutorid
INNER JOIN courses ON enrollment.courseid = courses.courseid
WHERE tutors.tutorname = 'Stuart';