Database Management for Business: SDA Database System

Desklib offers past papers and solved assignments; access this database management assignment now!

ISY1002 (ISY103) Database
Management for Business
2018.3 Group Assignment –
Database Report & Presentation

Table of Contents
Introduction...........................................................................................................................................3
Part A (20 marks)...................................................................................................................................4
SDADB ERD:.......................................................................................................................................4
SDADB Database Schema:.................................................................................................................4
PART B (15 Marks).................................................................................................................................5
Create commands:.............................................................................................................................5
Insertion:...........................................................................................................................................9
Part C (24 marks).................................................................................................................................14
Queries............................................................................................................................................14
Part C...................................................................................................................................................19
References...........................................................................................................................................20

Introduction
In this assignment, we are going to develop a database system for the Sydney Dance
Academy (SDA) which is a dance academy. Academy requires many requirements specified
in the brief. We are also going to develop the entity relationship diagram and the database
schema of the database that we are going to create. The database is going to develop in
MySQL. Several entities and attributes are going to introduce to fulfill the requirement of the
Sydney Dance Academy. On the basis of the developed database system, several queries are
going to be performed which are mentioned in the brief.

Part A (20 marks)
SDADB ERD:
Figure 1 ER Diagram
SDADB Database Schema:
Figure 2 Database schema

PART B (15 Marks)
Create commands:
Create database SDADB;
Use SDADB;
Employee Table
create table employee(emp_id varchar(10) Primary key,
emp_name char(100) Not Null,
designation char(100) Not Null,
contact_no int Not Null,
location_id int Not Null);
Screenshot
Timetable Table
create table timetable (class_id varchar(5) Not Null,
room_no int Not Null,
start_time varchar(5) Not Null,
end_time char(5) Not Null,
style_id varchar(5) Not Null,
location_id int Not Null,
week_day char(20) Not Null,
class_date date Not Null,
instructor_id varchar(10) Not Null,

Primary key(class_id,room_no) );
Screenshot
Location Table
create table location (location_id int Primary key,
location char(50) Not Null,
rooms_available int Not Null);
Screenshot
Registered_student Table
create table Registered_student(reg_id varchar(10) Primary key,
std_fname char(100)Not Null,
std_lname char(100)Not Null,
std_phn_no int ,
email_id varchar(100) Not Null,

Payment_id int Not Null,
payment_date date Not Null,
style_id varchar(5) Not Null,
std_address varchar(200) Not Null,
no_of_student int
);
Screenshot
Classes_attend Table
create table classes_attend (reg_id varchar(10) Not Null,
class_id varchar(5) Not Null,
Primary key(reg_id, class_id));
Screenshot
Style Table
create table Style(style_id varchar(5) Primary key,
style char(100) Not Null,

age_group varchar(30),
registration_fee float Not NULL,
per_class_charge float Not Null);
Screenshot
Payment Table
create table payment(payment_id int Primary key,
method char(50) Not Null,
extra_charge float Not Null);
Screenshot

Insertion:
Employee Table
 insert into employee values('E221','Samuel White','Instructor',456123957,1);
 insert into employee values('E222','Ryan Wilson','Manager',336123957,2);
 insert into employee values('E223','Steve Hill','Manager',126123022,3);
 insert into employee values('E224','Justin Brown','Instructor',636123022,3);
 insert into employee values('E225','Brett lee','Instructor',236123022,4);
 insert into employee values('E226','Henry Smith','Manager',536123022,1);
 insert into employee values('E227','Jessica James','Instructor',136123957,2);
Screenshot
Timetable Table
 insert into timetable values('C11',1,'9AM','11AM', 'ST1',1,'Tuesday','2019-01-
08','E221');
 insert into timetable values('C12',2,'9AM','11AM', 'ST2',3,'Tuesday','2019-01-
08','E224');
 insert into timetable values('C13',2,'1PM','3PM', 'ST3',3,'Tuesday','2019-01-
08','E224');
 insert into timetable values('C14',2,'1PM','3PM', 'ST3',1,'Tuesday','2019-01-
08','E221');
 insert into timetable values('C15',5,'1PM','3PM', 'ST6',4,'Tuesday','2019-01-
08','E225');
 insert into timetable values('C16',6,'9PM','10PM', 'ST5',4,'Tuesday','2019-01-
08','E225');
 insert into timetable values('C17',1,'9AM','11AM', 'ST1',1,'Wednesday','2019-01-
09','E221');

 insert into timetable values('C18',3,'1PM','3PM', 'ST2',1,'Wednesday','2019-01-
08','E221');
 insert into timetable values('C19',2,'9AM','11AM', 'ST1',1,'Thursday','2019-01-
10','E221');
 insert into timetable values('C20',1,'9AM','11AM', 'ST1',2,'Thursday','2019-01-
10','E221');
Screenshot
Location Table
 insert into location values(1,'Parramatta',10);
 insert into location values(2,'Chatswood',10);
 insert into location values(3,'Hornsby',10);
 insert into location values(4,'Liverpool',10);
 insert into location values(5,'Blacktown',20);
 insert into location values(6,'Botany',20);
 insert into location values(7,'Castle hill',20);
Screenshot
Registered_student Table
 insert into registered_student values('S111','Sam','Brown','456123789',
'[e-mail]',1,'2019-01-02','ST1','444 Brown street',4);

 insert into registered_student values('S113','Jennifer','Rose','551123721',
'[e-mail]',3,'2019-01-05','ST3','55 Green street',4);
 insert into registered_student values('S115','Jane','Taylor',Null,
'[e-mail]',3,'2019-01-07','ST5','355 summerhill',5);
 insert into registered_student values('S114','Joseph','Jones',Null,
'[e-mail]',3,'2019-01-06','ST4','55 summerhill',5);
 insert into registered_student values('S112','Kim','Lee','221123789',
'[e-mail]',2,'2019-01-05','ST2','321 Richard street',5);
Screenshot
Classes_attend Table
 insert into classes_attend values('S111','C11');
 insert into classes_attend values('S112','C12');
 insert into classes_attend values('S113','C13');
Screenshot
Style Table
 insert into style values('ST1','Jazz','5yrs - 8yrs','10','20');
 insert into style values('ST2','Jazz','8yrs - 13yrs','20','30');
 insert into style values('ST3','Jazz','13yrs+','30','40');
 insert into style values('ST4','Hip Hop','5yrs - 8yrs','10','25');
 insert into style values('ST6','Hip Hop','13yrs+','30','45');
 insert into style values('ST5','Hip Hop','8yrs - 13yrs','10','35');
Screenshot

Payment Table
 insert into payment values(1,'credit card',1);
 insert into payment values(2,'debit card',2);
 insert into payment values(3,'cash',0);
Screenshot