BIT358 Advance Databases - Semester 1, 2019 - Assignment 2

This document presents a comprehensive solution to a database assignment for the BIT358 course, focusing on database design and implementation. The assignment includes the creation of an Entity-Relationship (ER) diagram to model the database structure, followed by the development of a relational data logical model. A detailed data dictionary is provided to define the attributes and characteristics of each table. The core of the solution involves writing and executing Oracle SQL code, including commands for creating, altering, and inserting data into tables. The SQL code addresses various tasks, such as creating tables, inserting data, altering table constraints, and executing complex queries to retrieve specific information from the database. Screenshots of the SQL code execution are included as evidence of successful operation. The assignment covers key database concepts and practical SQL skills, providing a complete solution for database design and implementation.

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

students Number of
students
taking the
course
integer 45 Y
name Name of the
subject
Varchar2(50) AAAaaaa Y
Location
campus Name of the
campus
Char(6) C001 Y PK
buildingNo Building
number
Char(6) B001 Y
roomNo Room
number
Char(6) R001 Y
Task 4 – Oracle SQL Code
i. Drop, create and insert commands.
drop table student cascade constraints;
drop table lecturer cascade constraints;
drop table subject cascade constraints;
drop table location cascade constraints;
drop table class cascade constraints;
drop table enrollment cascade constraints;
create table student(
studentID char(5) primary key,
firstName varchar2(50) not null,
lastName varchar2(50) not null,
major varchar2(50) not null,
dob date not null,
address varchar2(50) not null
);
Create table subject (
subjectID char(6) primary key,
name varchar2(50) not null,
students integer not null

);
create table lecturer (
lecturerID char(5) primary key,
firstname varchar2(50) not null,
lastName varchar2(50) not null,
subject char(6) not null,
foreign key (subject) references subject (subjectID)
);
Create table location(
Campus char(6) primary key,
buildingNo char(6) not null,
roomNo char(6) not null
);
create table class (
classID integer primary key,
subject char(6) not null,
lecturerID char(5) not null,
campus char(6) not null,
semester smallint not null,
foreign key (lecturerID) references lecturer (lecturerID),
foreign key (subject) references subject (subjectID),
foreign key (campus) references location (campus)
);
create table enrollment (
enrollmentID integer primary key,
classID integer not null,
studentID char(5) not null,
marks char(2) null,

foreign key (classID) references class (classID),
foreign key (studentID) references student (studentID),
check (enrollmentID>100)
);
insert into student values (12342,'John','Smith','Software
Development','02-Jan-1995','Preston');
insert into student values (45325,'Kelly','White','Software
Development','10-May-1998','Preston');
insert into student values (54344,'Lisa','Simpson','Software
Engineering','20-Dec-1990','CollingWood');
insert into lecturer values (12345,'Jon','Snow','BIT235');
insert into lecturer values (12346,'David','Silva','BIT100');
insert into lecturer values (12347,'Cersei','lannister','BIT358');
insert into location values ('PE227','df344','sdf4f');
insert into location values ('PE228','df344','sdf4f');
insert into location values ('PE229','df344','sdf4f');
insert into subject values ('BIT235','Java
Programming','12345','PE227','Preston');
insert into course values ('BIT100','Ruby
Programming','12346','MH602','Prahran');
insert into course values ('BIT358','Advance
Database','12347','MH603','Prahran');
insert into course values ('BIT123','System
Analysis','12345','Cf303','Preston');
insert into class values (1,'BIT235','12345','PE227','1');
insert into class values (2,'BIT100','12346','PE228','1');
insert into class values (3,'BIT358','12347','PE228','2');

insert into enrollment values (1001,'1',12342,'HD');
insert into enrollment values (1002,'2',12342,'D');
insert into enrollment values (1003,'3',45325,'HD');
insert into enrollment values (1004,'1',45325,'C');
insert into enrollment values (1005,'2',54344,'P');
iv. Alter table statements
 Alter statement to make grade column not null in the enrollment table
alter table enrollment modify marks char(2) not null;
 Alter table statement to add a check constraint on the grade column in the enrollment table.
alter table enrollment add check (marks in
('HD','D','C','P','N'));

 Alter statement to add title column in the lecturers table
alter table lecturer add title char(6) null;
v. SQL Statements.
i. List the Student ID, Name, Date of Birth and address for each student in the database
select studentID, firstName || ' ' || lastName as Name, dob,address
from student;

ii. List the Student ID, Name and campus for each student in the database who are registered
for more than or less than 4 courses in a specific semester (For example, Semester 1, 2019)
select studentID, firstName || ' ' || lastName as Name, address from
student
where studentID IN
(select studentID from class
where semester='1' group by studentID having count(classID)<4);

iii. For each row in the Student table display the Student ID, Name, Course Code and the
Course Title. Your query should also display the Students who have NOT as yet enrolled in
any courses. You are to use one query only for this problem.
select student.studentID, firstName || ' ' || lastName as
Name, class.subject, subject.name from student
left join enrollment on enrollment.studentID=student.studentID
inner join class on class.classID=enrollment.classID
inner join subject on subject.subjectID=class.subject;
iv. There have been many data entry operator errors where data has been entered with Caps
Lock on sometimes, other times with Caps Lock off and other times where the Caps Lock has
been used intermittently. With this in mind display the details of student enrolled in ‘Ruby
Programming’ Hint: Need to use SQL function
select student.studentID, firstName || ' ' || lastName as
Name, subject.subjectID, subject.name from student
inner join enrollment on
enrollment.studentID=student.studentID
inner join class on class.classID=enrollment.classID
inner join subject on subject.subjectID=class.subject
where subject.name like 'Ruby%';