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

12
DATABASE SYSTEM
Query 5: Course taught by David
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 = 'David';

13
DATABASE SYSTEM
Query 6: Maximum Tutor Salary
SELECT MAX(tutorsalary) FROM tutors;

14
DATABASE SYSTEM
Query 7: Minimum Tutor Salary
SELECT MIN(tutorsalary) FROM tutors;

15
DATABASE SYSTEM
Query 8: Average Tutor Salary
SELECT AVG(tutorsalary) FROM tutors;

16
DATABASE SYSTEM
Query 9: Average Course Fee
SELECT AVG(coursefee) FROM courses;

17
DATABASE SYSTEM
Query 10: Minimum Course Fee
SELECT MIN(coursefee) FROM courses;

18
DATABASE SYSTEM
Phase 4: Advanced SQL Aspects
For the security in the database, system the least privileges are to be provides to the
users. Only the necessary privileges are to be granted to the users. The restriction for
CREATE ANY JOB, BECOME USER, EXP_FULL_DATABASE, and
IMP_FULL_DATABASE privileges is to be done in the database. For additional security, the
library privileges are to be granted only to the trusted users for the system. The administrative
schema such as SYS and SYSDBS are not to be granted access for the non-administrative
users.

19
DATABASE SYSTEM
Bibliography
Gahi, Y., Guennoun, M. and El-Khatib, K., 2015. A secure database system using
homomorphic encryption schemes. arXiv preprint arXiv:1512.03498.
Poddar, R., Boelter, T. and Popa, R.A., 2016. Arx: A strongly encrypted database system.
IACR Cryptology ePrint Archive, 2016, p.591.