BIT358: Advanced Database Assignment

BIT358: AdvancedDatabase
Student name
Student ID

BIT358: AdvancedDatabase
Table of Contents
Task 1: ER diagram.................................................................................................................................3
Task 2: relational data logical model.....................................................................................................3
Task 3: data dictionary...........................................................................................................................4
Task 4:SQL code.....................................................................................................................................7

BIT358: AdvancedDatabase
Task 1: ER diagram
Business study
Melbourne polytechnic is an overgrowing university situated in Australia. Their existing database has
overwhelmed with tremendous tedious data which are student registration,personal details and
grade details. With over increasing in student number they have to implement a reporting system
which will assist them to store and evaluate all the details. The polytechnic university mainly offers
three major streams as software development, business information system and network security.
The new system must be able to sort and filter every details for reporting and grading process.
The ER diagram has been developed for Melbourne polytechnic as shown in figure 1. The
diagram consists of entities such as student, student registration, faculty, degree course, academic
course, department and degree grades. Every entity is filled with appropriate attributes, primary
keys and foreign keys are also been set.
Figure1: the ER diagram suited for Melbourne polytechnic
Task 2: relational data logical model
Student1332(student1332_id, first name, second name, email, department_id1332)
Department1332(Department1332_id, Department1332_name, Department1332_code,
department description)
Degree course1332(Degree course1332_id, Degree course1332_name, course difficulty, course type)

BIT358: AdvancedDatabase
Degree grades1332(Degree grades1332_id, Department1332_id, degree code, degree description)
Session1332(session1332_id, session_date, session_duration, session_type)
Academic course1332(Academic course1332_id, session_id, course name, course semester)
Course registration1332(Course registration1332_id, student registration_id, registration date)
Student registration1332(student registration_id, student_id, date, year)
Faculty1332(faculty_id, faculty name, faculty code, faculty expertise)
Task 3: data dictionary
Table
name
Attributes description Data type Data Format Rang
e
mandatory PK/FK Fk
reference
Table
Student Student_id Unique id of
every
student.
INT(8) 12345678 null Y pk
First name Name of the
student
Varchar (20) Aaaa null Y
Last name Surname of
the student
Varchar (20) Aaa null Y
Email Personal
Email
address of
the student
Nvarchar(255) aaaaa@domain
name
6-255 Y
Department_i
d
Unique
identification
number of
different
departments
INT (6) 123456 null Y FK Department
Table name Attributes description Data type Data Format Range mandator
y
PK/FK Fk
reference
Table
department department_id Unique
identification
number of
different
departments
INT (6) 123456 Null Y pk
department
name
Name of the
department
Varchar (20) Aaaa Null Y
Department
code
Department
code to
identify
courses
INT (20) 999 Null Y
Description Description
of all options
varchar(200) aaaaa 50-
200
Y

BIT358: AdvancedDatabase
under each
department.
Table
name
Attributes description Data type Data Format Rang
e
mandatory PK/FK Fk
reference
Table
Degree
Course
degreecourse_id Unique id of
every degree
course.
INT(6) 123456 null Y pk
DegreeCourse
name
Name of the
degreecours
e
Varchar (20) Aaaa null Y
Course difficulty The difficulry
level of
course.
Varchar (20) Aaa null Y
Course type Type of the
course
varchar(250) aaaaa@domain
name
null Y
Table
name
Attributes description Data type Data Format Rang
e
mandator
y
PK/FK Fk reference
Table
Degre
e
grades
degreegrades_i
d
Unique id of
every degree
grades
INT(6) 123456 null Y pk
Degreegrades
name
Name of the
degreegrade
s
Varchar (20) Aaaa null Y
Grade code It specifies
different
codes for
different
grades
Varchar (20) Aaa null Y
Degree
description
Describes
degrees
varchar(250) aaaaa@domai
n name
null Y
Department_id Unique
identification
number of
different
departments
INT(6) 123456 null y fk departmentt
Table
name
Attributes description Data type Data Format Range mandatory PK/
FK
Fk reference
Table
session sessiion_id Unique id of
every session
INT(6) 123456 null Y pk
Session date Starting date
of every
datetime
(20)
99/99/9999 null Y

BIT358: AdvancedDatabase
session
Session duration Time limit of
every session
varchar(20) aa null Y
Session type Types of
session as
full or half
varchar(250) aaaaa null Y
Table
name
Attributes description Data type Data Format Range mandatory PK/
FK
Fk
reference
Table
Academic
course
Academic
course_id
Unique id of
each course
INT(10) 9999999999 null Y pk
Course name Name of
each course
varchar(20) aaa null Y
Course
semester
Time limit of
each course
varchar(20) aa null Y
Session_id Unique id
of every
session
Int(6) 123456 null Y fk
Table
name
Attributes description Data type Data Format Range mandatory PK/FK Fk
reference
Table
Course
registration
Course
registration_id
Unique id of
each course
INT(10) 9999999999 null Y pk
Registration
date
Name of
each course
datetime(20) 99/99/9999 null Y
Student
registration_id
Unique
registration
number for
each
student
int(8) 99999999 null Y Fk
Table
name
Attributes description Data type Data Format Range mandatory PK/FK Fk
reference
Table
Student
registration
Student
registration_id
Unique
registration
number for
each
INT(8) 99999999 null Y pk

BIT358: AdvancedDatabase
student
Student_id Unique roll
number of
each
student
Int(8) 99999999 null Y Fk
year Year of
study
datetime(20) 9999 null Y
Table
name
Attributes description Data type Data Format Range mandatory PK/
FK
Fk
reference
Table
faculty faculty_id Unique
registration
number for
each
student
INT(8) 99999999 null Y pk
Faculty_name Year of
study
datetime(20) 9999 null Y
Faculty_code Int(8) 99999999 null y
Faculty_expertise Char(20) aaa null y
Task 4:SQL code
i.
Figure: relational data logical model in oracle SQL developer
ii.

BIT358: AdvancedDatabase
Figure: Primary key established
iii.
default clause has been set under the entity course table precisely on attribute coursedifficulty.
Since there are only three streams to study for getting the polytechnic degree, all courses must have
basic difficulty. So the value was selected default with code shown below.
Create table degreecourse (course_id int not null, course_name varchar(20) not null, course
difficulty varchar(20) DEFAULT basic);
Figure showcasing default clause
iv.

BIT358: AdvancedDatabase
Figure: showcasing successful insert into commit
v.
Figure : drop table "ABUSER"."COURSEREGISTRATION" cascade constraints PURGE
vi.

BIT358: AdvancedDatabase
Figure: alter table "ABUSER"."REGISTRATION" rename to courseregistraion
vii.
Figure: insert a value of the surrogate key in session_ep table
create table sessions_emp
(id number primary key,
sessions_name varchar2(100));
create sequence emp_seqs;