APPENDIX B: IMPLEMENTATION of ASEPDB
ASEPDB.sql
This SQL file creates all the tables in ASEPDB. All primary keys and foreign keys are also defined in this SQL file.
CREATE TABLE student(
SID VARCHAR(10),
FirstName VARCHAR(20),
LastName VARCHAR(20),
Password VARCHAR(20),
Email VARCHAR(30)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE TABLE instructor(
IID VARCHAR(10),
FirstName VARCHAR(20),
LastName VARCHAR(20),
Password VARCHAR(20),
Email VARCHAR(30)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE TABLE assistant(
AID VARCHAR(10),
FirstName VARCHAR(20),
LastName VARCHAR(20),
Password VARCHAR(10),
Email VARCHAR(30)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE TABLE course(
CID VARCHAR(10),
Name VARCHAR(50),
IID VARCHAR(10),
AID VARCHAR(10))
TABLESPACE RBS STORAGE (INITIAL
20K NEXT 10K);
CREATE TABLE enrollment(
CID VARCHAR(10),
SID VARCHAR(10)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE TABLE assignment(
CID VARCHAR(10),
Type VARCHAR(20),
GivenDate VARCHAR(10),
DueDate VARCHAR(10),
Format VARCHAR(5)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE TABLE evaluation(
CID VARCHAR(10),
Type VARCHAR(20),
SID VARCHAR(10),
Grade NUMBER(3) ,
Feedback VARCHAR(5) ,
Submitted CHAR(1)) TABLESPACE
RBS STORAGE (INITIAL 20K NEXT 10K);
CREATE UNIQUE INDEX student_Index
ON student (SID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX instructor_Index
ON instructor (IID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX assistant_Index
ON assistant (AID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX course_Index
ON course (CID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX enrollment_Index
ON enrollment (CID, SID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX assignment_Index
ON assignment (CID, Type)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
CREATE UNIQUE INDEX evaluation_Index
ON evaluation (CID, Type, SID)
TABLESPACE RBS STORAGE (INITIAL
10K NEXT 10K);
ALTER TABLE student ADD CONSTRAINT
PRIMARY KEY (SID) ;
ALTER TABLE instructor ADD CONSTRAINT
PRIMARY KEY (IID) ;
ALTER TABLE assistant
ADD CONSTRAINT PRIMARY KEY (AID) ;
ALTER TABLE course
ADD CONSTRAINT PRIMARY KEY (CID) ;
ALTER TABLE enrollment ADD CONSTRAINT
PRIMARY KEY (CID, SID) ;
ALTER TABLE assignment ADD CONSTRAINT
PRIMARY KEY (CID, Type) ;
ALTER TABLE evaluation ADD CONSTRAINT
PRIMARY KEY (CID, Type, SID);
ALTER TABLE course ADD CONSTRAINT
FOREIGN KEY (IID) REFERENCES instructor;
ALTER TABLE course ADD CONSTRAINT
FOREIGN KEY (AID) REFERENCES assistant;
ALTER TABLE enrollment ADD CONSTRAINT
FOREIGN KEY (CID) REFERENCES course;
ALTER TABLE enrollment ADD CONSTRAINT
FOREIGN KEY (SID) REFERENCES student;
ALTER TABLE assignment ADD CONSTRAINT
FOREIGN KEY (CID) REFERENCES course;
ALTER TABLE evaluation ADD CONSTRAINT
FOREIGN KEY (CID, SID) REFERENCES enrollment;
ALTER TABLE evaluation ADD CONSTRAINT
FOREIGN KEY (CID, type) REFERENCES assignment;
insertStudent.sql
This SQL file inserts student data to the student table of ASEPDB. The sample content of this file is given below.
INSERT INTO student VALUES ('93110475'
, 'Ertan' , 'Karakurt' , 'ekarak' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94020940'
, 'Serkan' , 'Kutan' , 'kutan' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94016950'
, 'Alpaslan' , 'Coskun' , 'alpcos' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('93110087'
, 'Okan' , 'Kolak' , 'kolakan' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94015560'
, 'Ahmet Yasin' , 'Haliloglu' , 'hyasin' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94017930'
, 'Ibrahim' , 'Okuyucu' , 'okuyucu' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('93110433'
, 'Benan' , 'Tuncbilek' , 'tbenan' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('93110136'
, 'Murat' , 'Tuncer' , 'mtuncer' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94023860'
, 'Baris' , 'Yaslan' , 'ybaris' ,'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO student VALUES ('94020340'
, 'Serdar' , 'Unal' , 'sunal' ,'hyasin@ug.bcc.bilkent.edu.tr');
insertInstructor.sql
This SQL file inserts instructor data to the instructor table of ASEPDB. The sample content of this file is given below.
INSERT INTO instructor VALUES
('1' , 'Ilyas' , 'Cicekli' , 'ilyas' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('2' , 'Ezhan' , 'Karasan' , 'ezhan' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('3' , 'Tugrul' , 'Dayar' , 'tugrul' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('4' , 'H. Altay', 'Guvenir' , 'guvenir' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('5' , 'Varol' , 'Akman' , 'akman' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('6' , 'David' , 'Davenport' , 'david' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('7' , 'Cevdet' , 'Aykanat' , 'caykanat' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO instructor VALUES
('8' , 'Pierre' , 'Flener' ,pf' , 'hyasin@ug.bcc.bilkent.edu.tr');
insertAssistant.sql
This SQL file inserts assistant data to the assistant table of ASEPDB. The sample content of this file is given below.
INSERT INTO assistant VALUES
('1' , 'Murat' , 'Temizsoy' , 'temizsoy' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('2' , 'Oleg' , 'Gusak' , 'gusak' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('3' , 'Tamer' , 'Kahveci' , 'tamer' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('4' , 'Tuba' , 'Yavuz' , 'ytuba' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('5' , 'Bilge' , 'Say' , 'say' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('6' , 'Yucel' , 'Saygin' , 'ysaygin' , 'hyasin@ug.bcc.bilkent.edu.tr');
INSERT INTO assistant VALUES
('7' , 'Bora' , 'Ucar' , 'ubora' , 'hyasin@ug.bcc.bilkent.edu.tr');
insertCourse.sql
This SQL file inserts course data to the course table of
ASEPDB. The sample content of this file is given below.
INSERT INTO course VALUES ('CS416',
'Compiler Design' , '1', '1');
INSERT INTO course VALUES ('CS421',
'Computer Networks' , '2', '2');
INSERT INTO course VALUES ('CS471',
'Numerical Methods' , '3', '3');
INSERT INTO course VALUES ('CS476',
'Automata Theory and Formal Languages' , '4', '4');
INSERT INTO course VALUES ('CS482',
'Logic for Computer Science' , '5', '5');
INSERT INTO course VALUES ('CS492',
'Senior Project' , '6', '6');
INSERT INTO course VALUES ('CS102',
'Algorithms & Programming II' , '6', '1');
INSERT INTO course VALUES ('CS473',
'Algorithms II' , '7', '7');
INSERT INTO course VALUES ('CS322',
'Program Verification' , '8', '7');
INSERT INTO course VALUES ('CS318',
'Symbolic Programming' , '1', '2');
insertEnrollment.sql
This SQL file inserts enrollment data to the enrollment table of ASEPDB. The sample content of this file is given below.
INSERT INTO enrollment VALUES
('CS416','93110277');
INSERT INTO enrollment VALUES
('CS416','94023520');
INSERT INTO enrollment VALUES
('CS416','92110294');
INSERT INTO enrollment VALUES
('CS416','93110243');
INSERT INTO enrollment VALUES
('CS416','94029730');
INSERT INTO enrollment VALUES
('CS416','93110459');
INSERT INTO enrollment VALUES
('CS482','93110219');
INSERT INTO enrollment VALUES
('CS482','91110188');
INSERT INTO enrollment VALUES
('CS482','94017090');
INSERT INTO enrollment VALUES
('CS421','93110368');
INSERT INTO enrollment VALUES
('CS421','93110459');
INSERT INTO enrollment VALUES
('CS421','94029730');
INSERT INTO enrollment VALUES
('CS322','93110087');
INSERT INTO enrollment VALUES
('CS322','94016950');
INSERT INTO enrollment VALUES
('CS322','94020940');
INSERT INTO enrollment VALUES
('CS322','93110475');
INSERT INTO enrollment VALUES
('CS476','93110243');
INSERT INTO enrollment VALUES
('CS476','91110138');
INSERT INTO enrollment VALUES
('CS476','92110294');
INSERT INTO enrollment VALUES
('CS476','94023520');
INSERT INTO enrollment VALUES
('CS476','93110285');
INSERT INTO enrollment VALUES
('CS471','94026080');
INSERT INTO enrollment VALUES
('CS471','94015600');
INSERT INTO enrollment VALUES
('CS471','93110152');
INSERT INTO enrollment VALUES
('CS471','92110145');
INSERT INTO enrollment VALUES
('CS471','93110384');
INSERT INTO enrollment VALUES
('CS471','93110269');
dropAll.sql
This SQL file drops all the tables in ASEPDB. All the data in the database get lost after the execution of this SQL file.
DROP TABLE evaluation;
DROP TABLE assignment;
DROP TABLE enrollment;
DROP TABLE course ;
DROP TABLE assistant ;
DROP TABLE instructor;
DROP TABLE student ;
A SAMPLE OUTPUT of the ASEPDB DATABASE CREATION PROCESS
SQL> select * from tab;
no rows selected
SQL> start AsepDB.sql
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
ASSIGNMENT TABLE
ASSISTANT TABLE
COURSE TABLE
ENROLLMENT TABLE
EVALUATION TABLE
INSTRUCTOR TABLE
STUDENT TABLE
7 rows selected.
SQL> describe student;
Name Null? Type
-------------------------------
-------- ----
SID NOT NULL VARCHAR2(10)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL VARCHAR2(30)
SQL> describe instructor;
Name Null? Type
-------------------------------
-------- ----
IID NOT NULL VARCHAR2(10)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL VARCHAR2(30)
SQL> describe assistant;
Name Null? Type
-------------------------------
-------- ----
AID NOT NULL VARCHAR2(10)
FIRSTNAME VARCHAR2(20)
LASTNAME VARCHAR2(20)
PASSWORD VARCHAR2(10)
EMAIL VARCHAR2(30)
SQL> describe course;
Name Null? Type
------------------------------- -------- ----
CID NOT NULL VARCHAR2(10)
NAME VARCHAR2(50)
IID VARCHAR2(10)
AID VARCHAR2(10)
SQL> describe enrollment;
Name Null? Type
------------------------------- -------- ----
CID NOT NULL VARCHAR2(10)
SID NOT NULL VARCHAR2(10)
SQL> describe assignment;
Name Null? Type
------------------------------- -------- ----
CID NOT NULL VARCHAR2(10)
TYPE NOT NULL VARCHAR2(20)
GIVENDATE VARCHAR2(10)
DUEDATE VARCHAR2(10)
FORMAT VARCHAR2(5)
SQL> describe evaluation;
Name Null? Type
------------------------------- -------- ----
CID NOT NULL VARCHAR2(10)
TYPE NOT NULL VARCHAR2(20)
SID NOT NULL VARCHAR2(10)
GRADE NUMBER(3)
FEEDBACK VARCHAR2(5)
SUBMITTED CHAR(1)
SQL> start insertStudent.sql
SQL> start insertInstructor.sql
SQL> start insertAssistant.sql
SQL> start insertCourse.sql
SQL> start insertEnrollment.sql
SQL> select * from student;
SID FIRSTNAME LASTNAME PASSWORD
---------- -------------------- -------------------- --------------------
------------------------------
93110475 Ertan Karakurt ekarak
hyasin@ug.bcc.bilkent.edu.tr
94020940 Serkan Kutan kutan
hyasin@ug.bcc.bilkent.edu.tr
94016950 Alpaslan Coskun alpcos
hyasin@ug.bcc.bilkent.edu.tr
93110087 Okan Kolak kolakan
hyasin@ug.bcc.bilkent.edu.tr
94015560 Ahmet Yasin Haliloglu hyasin
hyasin@ug.bcc.bilkent.edu.tr
94017930 Ibrahim Okuyucu okuyucu
hyasin@ug.bcc.bilkent.edu.tr
93110433 Benan Tuncbilek tbenan
hyasin@ug.bcc.bilkent.edu.tr
93110136 Murat Tuncer mtuncer
hyasin@ug.bcc.bilkent.edu.tr
94023860 Baris Yaslan ybaris
hyasin@ug.bcc.bilkent.edu.tr
94020340 Serdar Unal sunal
hyasin@ug.bcc.bilkent.edu.tr
10 rows selected.
SQL> select * from instructor;
IID FIRSTNAME LASTNAME PASSWORD
---------- -------------------- -------------------- --------------------
------------------------------
1 Ilyas Cicekli ilyas
hyasin@ug.bcc.bilkent.edu.tr
2 Ezhan Karasan ezhan
hyasin@ug.bcc.bilkent.edu.tr
3 Tugrul Dayar tugrul
hyasin@ug.bcc.bilkent.edu.tr
4 H. Altay Guvenir guvenir
hyasin@ug.bcc.bilkent.edu.tr
5 Varol Akman akman
hyasin@ug.bcc.bilkent.edu.tr
6 David Davenport david
hyasin@ug.bcc.bilkent.edu.tr
7 Cevdet Aykanat caykanat
hyasin@ug.bcc.bilkent.edu.tr
8 Pierre Flener pf
hyasin@ug.bcc.bilkent.edu.tr
8 rows selected.
SQL> select * from assistant;
AID FIRSTNAME LASTNAME PASSWORD
---------- -------------------- -------------------- ----------
------------------------------
1 Murat Temizsoy temizsoy
hyasin@ug.bcc.bilkent.edu.tr
2 Oleg Gusak gusak
hyasin@ug.bcc.bilkent.edu.tr
3 Tamer Kahveci tamer
hyasin@ug.bcc.bilkent.edu.tr
4 Tuba Yavuz ytuba
hyasin@ug.bcc.bilkent.edu.tr
5 Bilge Say say
hyasin@ug.bcc.bilkent.edu.tr
6 Yucel Saygin ysaygin
hyasin@ug.bcc.bilkent.edu.tr
7 Bora Ucar ubora
hyasin@ug.bcc.bilkent.edu.tr
7 rows selected.
SQL> select * from course;
CID NAME IID
---------- -------------------------------------------------- ----------
AID
----------
CS416 Compiler Design 1
1
CS421 Computer Networks 2
2
CS471 Numerical Methods 3
3
CS476 Automata Theory and Formal Languages 4
4
CS482 Logic for Computer Science 5
5
CS492 Senior Project 6
6
CS473 Algorithms II 7
7
CS322 Program Verification 8
7
8 rows selected.
SQL> select * from enrollment where CID="CS322";
CS322 93110087
CS322 94016950
CS322 94020940
CS322 93110475
4 rows selected.
SQL> select * from assignment;
CID TYPE GIVENDATE DUEDATE FORMAT
---------- -------------------- ---------- ---------- -----
CS482 Homework5 28/4/1998 5/5/1998 doc
CS322 Report2 1/5/1998 7/5/1998 mp3
2 rows selected.
SQL> select * from evaluation;
CID TYPE SID GRADE FEEDB SUBMITTED
---------- -------------------- ---------- ---------- ----- -
CS482 Homework5 93110433 -1 N N
CS482 Homework5 93110459 -1 N N
CS482 Homework5 93110475 90 doc Y
CS482 Homework5 94009920 -1 N N
CS482 Homework5 94015560 97 doc Y
CS482 Homework5 94015600 -1 N N
CS482 Homework5 94016060 -1 N N
CS482 Homework5 94016410 -1 N N
CS482 Homework5 94016950 -1 N N
CS482 Homework5 94031440 -1 N N
CS482 Homework5 94031530 -1 N N
CS482 Homework5 94031540 -1 N N
CS322 Report2 93110087 -1 N Y
CS322 Report2 93110102 -1 N N
CS322 Report2 93110110 -1 N N
CS322 Report2 93110475 -1 N N
CS322 Report2 94015560 -1 N Y
CS322 Report2 94016950 -1 N N
18 rows selected.