2018T3 ISY1002: Sydney Dance Academy (SDA) Database Project Report

This assignment is a comprehensive database project for the ISY1002 course, focusing on the design and implementation of a database system for the Sydney Dance Academy (SDA). The project involves creating an Entity Relationship Diagram (ERD) to model the database, designing the database schema in MySQL, and implementing SQL queries to retrieve and manipulate data. The project requires the student to address database normalization, referential integrity, and data validation. The assignment also includes creating sample data, answering specific SQL queries, and writing a reflection on the database design process. The project aims to provide practical experience in database modeling, normalization, and writing SQL statements, including creating tables, inserting records, and retrieving data using various SQL commands like SELECT, JOIN, and WHERE clauses. The final deliverable is a detailed report including the ERD, database schema, SQL queries, outputs, and a personal reflection on the project experience.

ISY1002 (ISY103) Database Management for Business
2018.3 Group Assignment – Database Report & Presentation
Total Marks: 50
Assessment Weighting: 25%
Aim: To give you practical experience in with database modelling, normalization and writing SQL
statements.
Assignment Summary: The project specification details provided in this document and you
must make use of the following specifications to come up with your solution. Make appropriate
assumptions where ever required.
Please make sure that your group must meet your lecturer/tutor at least twice to update the
progress of your assignment.
Background Information for Sample Database Project
Sydney Dance Academy (SDA) requires a database system to manage their new
Registrations, enrolments, timetabling, room allocations and payments.
Currently SDA has 8 (Sydney CBD, Parramatta, Chatswood, Hornsby, Liverpool, Blacktown,
Botany, Castle hill) locations across Sydney. Each location maintains the required data on flat
files. Each location has a Branch manager that manages all the operational data required to
manage the branch. Apart from branch manager all the other staff that SDA has are Dance
instructors. They are a helping hand at the reception as well.
When a prospective student rings up or comes into the reception at one of the Dance academy
locations to enrol into dance classes for the first time they are asked to provide their name,
address, and telephone number. The student may enrol into one or many dance classes.
SDA teaches Dances of various styles (Example Jazz, Belle, and Hip Hop etc) and each dance
style can have different rates per class.
SDA enrols students to dance classes for the whole term and there are SIX (6) terms per year.
Each style of dance may have classes for various age groups.
SDA has the following age groups – Age group 1 – CHR (children): 5yrs – 8yrs, Age group 2 –
PRETEENS (Pre – teens) : 8yrs – 13yrs and TEENS/ADULTS (Teenagers and Adults): 13yrs+.
Each Dance class has a specific style it belongs to, specific age group, a set timetable per term
(day of the week and start and end time), a location (One of the 8 locations), room number
allocated and each Dance class is capped to 15 students maximum.
Each Dance class has an instructor assigned. There can be multiple Dance classes timetabled
for one type of Dance in a week. An Instructor may be timetabled for more than one dance
classes in a week however a single Dance class can only have one instructor.
For enrolling in each dance term a student must pre-pay all the classes on the term. The
payment can be either made by a bank transfer or they can pay electronically or cash by
approaching the Dance class location in person.
Your group has been approached by SDA to create a Database that centralizes all the
Academy data so that they can manage their enrolments, timetabling, room allocations and
payments accurately. They want a software/online application in future to be built on the
database you design so that the operation of the Dance Academy is greatly improved.
1 | P a g e

System Requirements
The system is a prototype system and as such is not a full production version. You
will be required to enter a representative sample data into your tables in order to test the
design and operation of your database.
Project Specification
1. Part A (20 marks)
1. Your first task is to study the Case study requirements and gather as much
knowledge to list all the main Entities and attributes.
2. Create an Entity Relationship Diagram (ERD) to help you decide on the
relationships.
(10 marks)
Your entity relation diagram that models your database design should:
i. Include all entities, relationships (including names) and attributes.
ii. Identify primary and foreign keys.
iii. Include cardinality/ multiplicity and show using crow’s feet or UML
notation.
iv. Include participation (optional / mandatory) symbols if applicable.
The E-R should be created as part of a Microsoft Word document. Hand-drawn
diagrams will not be accepted. It is recommended that you complete your ERD
using Draw.io, Visio or try www.gliffy.com (Search for ERD gliffy to get started.).
3. Create relationships between tables and enforce the referential integrity as
shown below.
Relationships:
 Student can enrol themselves into more than one Dance Class.
 One type of Dance can have more than one classes timetabled and they
can run at various locations
 The dance styles can be Jazz, Belle, Hip-hop, Salsa etc.
 Each room can be timetabled more than once as long as the classes
don’t overlap.
 Each enrolment needs a payment that needs to be pre-paid, but a
student is allowed to make part payments. Means they can pay the
whole amount using different payment methods like cash, debit card,
credit card etc.
4. The database should include suitable validation and integrity checks as well as
appropriate referential integrity checks. That is, AS A MINIMUM, your system
should ensure that the following events cannot occur:
Referential Integrity Constraints:
 A class cannot have a student who is not registered means does not
exist in the system beforehand.
 A student cannot be deleted from the system if a student is enrolled in a
current dance class. Similarly an instructor cannot be deleted from the
system if they are instructing a current dance class.
2 | P a g e

 An enrolment cannot be deleted if a payment has been made and dance
class is allocated to the student. SDA does not cater any refunds.
 Only Dance styles entered in the database can be attached to a dance
class. A new form of dance style for a dance class may be only added to
the system after the required dance style is added first.
 Rooms cannot be deleted if there are dance classes already allocated to
that room.
 Payment methods cannot be deleted once payment methods have been
recorded against payments.
2. PART B (15 Marks)
1. Using MySQL, you are required to develop a demonstration prototype
system that handles new Registrations, enrolments, timetabling, room
allocations and payments.
Use MySQL to create a new database called SDADB. Create tables
according to your ERD. Follow a standard naming convention for table
names and also field names. Avoid using spaces and any special
characters in table and also field names. Use underscore_case or use
camelCase to separate parts of a name. (10 marks)
2. Add at least two new records into the appropriate tables to include students
details, instructor details, location details, dance styles, dance class
timetable, rooms details etc. (5 Marks)
2. Part C (24 marks)
Use the Sydney Dance Academy (SDADB) database that you created in MySQL to
design and execute SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question
must be tabulated as shown in the example below and include the SQL statement
and also the output that is produced when you execute the statement in your
database. The output includes the records that are listed and also the message that
appears when you run the SQL statement.
For example:
Question 1:
SQL: SELECT lName, position
FROM Staff
WHERE salary > 20000;
Output: +-------+----------+
| lName | position |
+-------+----------+
| Brand | Manager |
| White | Manager |
+-------+----------+
2 rows in set (0.03 sec)
Do not use screen captures to display the SQL statement or the output.
3 | P a g e

You should right-click on the MySQL Command Prompt window; choose Mark and
then press the [Enter] key to Copy and then Paste into your Word document that
includes the answers to all questions.
Format and indent the clauses in your SQL statements for better readability and
understanding as shown in the example above. Statements must be syntactically and
semantically correct.
Format both the SQL and also the Output in Courier New
10 or 11 point.
Each question is 2 marks.
1. List the first name, last name of students (join student’s first and last name with a space
in between and use the alias Student Name for the column heading), and email
address. Sort the output in ascending order by the Student Lastname.
2. List all the dance styles that has got a rate higher than $12 per class.
3. Retrieve Students’ last name, first name, mobile, and email for all students who do not
have a mobile phone number recorded in the Student table. Sort the output in
ascending order by the Student last name, and then first name.
4. List all Student last name for all students that have a suburb that has the word ‘hill’
anywhere in the suburb name. Sort the output ascending order by the last name of
student.
5. List all the instructors who are currently timetable for 5 or more dance classes.
6. For each student who has been enrolled to dance classes multiple times display the
total amount of money they paid.
7. Count the number of students enrolled in each type of Dance style.
8. Display the current timetable of the classes done in Room 1 and 3 at Chatswood
location.
9. Display all the students’ information who are currently not enrolled in any of the dance
classes.
10. List the payment method description and sum of payment amounts for all
payments which were made after 1-July-2017.
3. Part C – Every group member needs to write this part individually and add to the
documentation (4 marks)
1. Write a page to the department manager as a personal reflection that describes your
experience building the database. You can discuss any challenges / difficulties that you
experienced or solutions that you found. Comment on any limitations and / or strengths
of your database design. Comment on whether your database meets all the system
requirements as specified in Part A Question 4. Avoid making excuses or comments
that reflect negativity. Include an acknowledgement of all students you have spoken to
about the assignment.
4 | P a g e

4. Part D (7 marks)
1. Deliverables for Parts A, B, & C must be printed as a report with a cover sheet
attached. See Administrative Details on page 10 for more details. Your report must
include headers and footers that include your name, student number, unit name,
assignment name, and page numbers. Your report must be checked for spelling and
grammar. Your report must also be formatted so that it is well set out and easy to read.
a) A soft copy of your assignment documentation report must be zipped and
uploaded to Moodle.
b) The SQL that can be used to restore your database should also be uploaded to
Moodle. You can create the SQL for your database as follows:
Use the mysqldump command to create a text version of the database. Use
mysqldump to create SQL file that contains a list of SQL statements which can
be used to restore/recreate the original database.
The syntax is as follows:
$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
WHERE:
[uname] Your database username
[pass] The password for your database
[dbname] The name of your database
[backupfile.sql] The filename for your database backup
You do not need to log on to MySQL. For example from the DOS command
prompt, the syntax is:
e:\xampp\mysql\bin>mysqldump -u root MyDB > e:\MyDB_bak.sql
This will create the SQL file that can be used to restore your database.
OR ---
You can save your script as you go to notepad.
You are also required to present your work in the class. The presentation
can be around 5 to 10 minutes long.
5. Submission
a. When to submit
Refer to the Course Description for due date, extensions and late policy.
b. What to submit
Part A
(1) Include in your documentation the ERD that you created using Word, Visio, or
other software. Copy and paste your diagram into your Word document as
illustrated by the following example:
5 | P a g e

Orders Database ERD:
(2) Title your SDA ERD.
(3) Include in your documentation the schema that appears on the Designer tab
in MySQL. Arrange the tables so that the lines joining tables do not cross
over (if possible). Copy and paste your diagram into your Word document as
illustrated by the following example:
DreamHome Database Schema:
(4) Title your SDA Database Schema.
6 | P a g e

(5) Use the DESCRIBE command to describe the structure of each and every
one of your tables in your WC database. Do not use screen captures. The
following is an example of how each table should be documented:
Describe Branch;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| branchNo | char(4) | NO | PRI | NULL | |
| street | varchar(16) | YES | | NULL | |
| city | varchar(10) | YES | | NULL | |
| postcode | char(8) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 | P a g e

(6) Show the first five records in each of your tables as shown by the example
below:
SELECT * FROM Branch;
+----------+--------------+----------+----------+
| branchNo | street | city | postcode |
+----------+--------------+----------+----------+
| B002 | 56 Clover Dr | London | NW10 6EU |
| B003 | 163 Main St | Glasgow | G11 9QX |
| B004 | 32 Manse Rd | Bristol | BS99 1NZ |
| B005 | 22 Deer Rd | London | SW1 4EH |
| B007 | 16 Argyll St | Aberdeen | AB2 3SU |
+----------+--------------+----------+----------+
(7) Also print the records that you have added to the tables that were specified.
(8) Use Courier New 8 or 9 point to ensure that the columns fit between the
margins. Columns should not wrap if this can be avoided.
(9) Use screen captures to show referential integrity constraints as shown by the
following example for the propertyForRent table in the dream home database:
8 | P a g e

Part B
(1) The answer to each question must be clearly numbered and tabulated as
shown in the example below.
Question 1:
SQL: SELECT lName, position
FROM Staff
WHERE salary > 20000;
Output: +-------+----------+
| lName | position |
+-------+----------+
| Brand | Manager |
| White | Manager |
+-------+----------+
2 rows in set (0.03 sec)
1. Add a heading immediately above the table to identify the question number.
2. Include both the SQL statement and also the output that is produced when you
execute the statement in your database. The output includes the records that
are listed and also the message that appears when you run the SQL statement.
3. Do not use screen captures to display the SQL statement or the output. Copy
the text of your SQL statement and also the output into the table.
4. Edit the keywords of your SQL statement (if necessary) to appear in uppercase
as shown by the example above. Field names and table names should be
entered in upper or lowercase as they appear in the database schema.
5. Format and indent the clauses in your SQL statements for better readability and
understanding as shown in the example above. Statements must be
syntactically and semantically correct.
6. Format both the SQL and also the Output in Courier New 10 or 11 point or
smaller in order to avoid wrapping.
7. Marks will be deducted where students fail to follow the requirements given
above.
Part C
1. Include your personal report as per instructions given.
Part D
1. PART A, B and C plus the SQL Script as described in PART D in the
specification.
2. Presentation
9 | P a g e

Administrative Details
Deadlines: There will be NO extensions except where Calendar rules apply. Deadlines
must be met, unless an acceptable medical certificate covers 25% or more of the lead-time
between the assignment and the deadline. Other absences must be accounted for by
substantiating documentation, for example a police accident report or statutory declaration.
Any assessment activities missed for medical (or other acceptable substantiated) reasons
must be completed by a new deadline that will be assigned in negotiation with the lecturer.
The Penalty for Late Submission of assignments is 5% of the total marks for each day (or
part thereof, including weekends) the assignment is late. For assignments received more
than 7 days after the due date, the assignment will be assessed, but a zero mark will be
awarded.
Electronic failure causing delay: In most work submissions you have a final deadline but
it is recommended that you submit well before this time. Timely submission is your
responsibility – last minute technical disaster will not be accepted as a reason for late
submission. You are expected to have discipline in time management, to make back-ups,
and always have the back-ups available for submission.
Your report is to be presented as a hard copy. All assessment activities that are handed in
must be presented to a professional standard and must include:
1. A hard copy to be submitted to the designated lecturer or nominated alternative by the
due time and date. It must have a title page which includes the student’s name and ID
number, title of assignment and a signed statement that the work is exclusively the
student’s own work and that there has been no plagiarism or collusion. Reports
without a coversheet will not be accepted.
2. A footer to be shown on every page that includes student’s name, ID number and
page #.
3. A soft copy of your report to be submitted on AIH Moodle. The same assignment
deadlines apply to this submission format.
E-mailing of your assignment will be accepted only after consultation with the tutor. If
emailing, through arrangement with the tutor, send the document as an e-mail
attachment in MS Word or PDF format. The subject line in the e-mail must include
student name. When sending e-mails please request an automated receipt. Where
there is doubt as to whether the assessment activity was submitted on time, no claims
for timely submission will be accepted if there is no receipt.
Submissions not clearly identified with the author’s details as outlined in the points above
will not be marked and may result in a 0 grade for that activity.
PLAGIARISM AND COLLUSION Any submitted work that contains plagiarism or collusion
will be reported to the appropriate authorities according to the University policy and will
receive an automatic zero ‘0’ mark for the assignment.
10 | P a g e

Final Report Structure:
Your final report must comprise the following parts:
 Title Page
o Report title
o Names and Student IDs for team members
 Table of Contents
o Should be auto-generated from Heading Styles in MS Word.
 Background
o Requirements / Business Rules
o Mission Statement / System Definition
o DBMS Selection
 ERD x2
o Use Gliffy.com or draw.io or Visio or any other tool to create the Conceptual
model. This is to show your design.
o Include a screen capture of Designer tab to show table relationships in MySQL.
 Database Schema
o Show each table design
 Foreign Key Constraints
o Show as screen captures for all tables with foreign keys.
 X12 SQL Statements
o Show statement and output formatted using Courier New
o Statements should be similar to the examples given and may include any of the
following elements:
 WHERE clause with examples of various conditions (Like, Between, =,
AND / OR, IN, etc.)
 ORDER BY clause
 Concatenation to combine values including alias
 Expressions to calculate results
 2 or 3 table joins using either new or old syntax
 Subquery
 Functions to aggregate data (SUM, MIN, MAX, AVERAGE, COUNT)
including sub-totals (GROUP BY)
 Group Report
o Challenges & Solutions
 References
o Harvard Style
o If you do not have any then just reference the prescribed text.
11 | P a g e

Database Management for Business Project (Assignment) 2018T3
Database Management for Business
Student Name: _______________________________ Student ID#: ___________
I acknowledge that this project is my own work, that there is no collusion with other students,
past or present, and that any work used from another source has been correctly cited and
referenced (using the Harvard style).
Signed: _________________________________ Date: ____________________
Marking Guide for Assignment
PART MARKS
A: ER Diagram (entities, attributes, relationships, keys) ______ / 5
Schema matches ERD
Database Design (appropriate data types, field size),
Data Integrity (keys), Constraints (FKs)
_______ / 2
_______ / 3
_______ / 3
Sufficient data / + additional records shown _______ / 2
B: SQL & Output
Q1 /2
Q2 /2
Q3 /2
Q4 /2
Q5 /2
Q6 /2
Q7 /2
Q8 /2
Q9 /2
Q10 /2
Q11 /2
Q12 /2
______ / 24
C: Personal Report _______ / 4
D: Report Layout & Presentation and SQL DDL Script _______ / 7
Late Penalties 5% each day late
Total: _______ / 50
Comments:
End of the document
12 | P a g e