Database System

Verified

Added on  2022/12/27

|20
|1426
|72
AI Summary
This document provides an overview of a database system for student enrollment. It covers the conceptual design, logical design, and implementation of the system. The document includes information on functional dependencies, relational schema, and SQL queries.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Running head: DATABASE SYSTEM
Database System
Name of the Student:
Name of the University:
Author Note

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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.
Document Page
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)
Document Page
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);

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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,
Document Page
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,
Document Page
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

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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);
Document Page
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';
Document Page
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';

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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';
Document Page
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';
Document Page
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';

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
13
DATABASE SYSTEM
Query 6: Maximum Tutor Salary
SELECT MAX(tutorsalary) FROM tutors;
Document Page
14
DATABASE SYSTEM
Query 7: Minimum Tutor Salary
SELECT MIN(tutorsalary) FROM tutors;
Document Page
15
DATABASE SYSTEM
Query 8: Average Tutor Salary
SELECT AVG(tutorsalary) FROM tutors;

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
16
DATABASE SYSTEM
Query 9: Average Course Fee
SELECT AVG(coursefee) FROM courses;
Document Page
17
DATABASE SYSTEM
Query 10: Minimum Course Fee
SELECT MIN(coursefee) FROM courses;
Document Page
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.

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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.
1 out of 20
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]