ER Diagram, Relational Data logical model, Data dictionary, Oracle SQL Code

COVER PAGE

Contents
Task 1 – ER Diagram....................................................................................................................................3
Task 2 – Relational Data logical model........................................................................................................3
Task 3 – Data dictionary..............................................................................................................................3
Task 4 – Oracle SQL Code............................................................................................................................5

Task 1 – ER Diagram
Task 2 – Relational Data logical model
 Student (studentID, firstName, lastName, major, dob, address)
Primary key (studentID)
 Enrollment (enrollmentID, classID, studentID, marks)
Primary key (enrollmentID)
Foreign key (classID) references class (classID)
 class (classID, subject, lecturerID, campus, semester)
Primary key (classID)
Foreign key (lecturerID) references lecturer (lecturerID)
Foreign key (subject) references subject (subjectID)
Foreign key (campus) references location (campus)
 Lecturer (lecturerID, firstName, lastName,subject)
Primary key (lecturerID)
Foreign key (subject) references subject (subjectID)
 Subject (subjectID, name, students)
Primary key (subjectID)
 Location (campus,buildingNo,roomNo)
Primary key (campus)

Task 3 – Data dictionary
Table Name Attribute
Name
Contents Type Format Range Required PK?FK FK Reference
tabke
Student
StudentID Student
registration
ID
Char(5) 12345 Y PK
firstName Student’s
firstname
Varchar2(50) Aaaaa Y
lastName Student’s
lastname
Varchar2(50) Aaaaa Y
Major Student’s
major
Varchar2(50) Aaaaa Y
Dob Student’s
date of birth
Date dd-Mon-
yyyy
Y
Address Address of
the student
Varchar2(50) AAaaa Y
Lecturer
lecturerID Lecturer’s ID Char(5) 12345 Y PK
firstName Lecturer’s
first name
Varchar2(50) Aaaa Y
lastName Lecturer’s
last name
Varchar2(50) Aaaa Y
Class
ClassID Id of the class integer 1 1-10000 Y PK
subject Title of the
course
Char(6) BIT235 Y
lecturerID Lecturer ID Char(5) 12345 Y FK Lecturer
campus The campus
offering the
course
Varchar2(50) Aaaaa Y
Enrollment
EnrollmentI
D
Unique
enrollment ID
integer 1001 1001-
999999
Y PK
classID ID of the
class
integer 1 Y FK class
studentID ID of the
student
Char(5) S001 Y FK Student
marks Grade
obtained
Char(2) HD N
Subject
subjectID Id of the
subject
Char(6) BIT235 Y PK