ISY1002/ISY103 Assignment – Database Project, Report and Presentation

1 | P a g e
Unit ISY1002/ISY103 Database Management for Business
Assessment
Type
Group Assignment – Database Project, Report and Presentation
Assessment
Number
3
Assessment
Weighting
25%
Total Marks 100
Alignment with
Unit and Course
Unit Learning Outcome
ULO2: Show an understanding of the fundamentals
of database design, implementation, management,
and maintenance, including consideration of
appropriate data security
ULO3: Implement a relational database using the
Structured Query Language (SQL) Data Definition
Language (DDL)
ULO4: Demonstrate an ability to query a relational
database using Structured Query Language (SQL) and
Data Manipulation Language (DML)
Graduate Attributes
Assessed
GA1:Communication
GA2: Collaboration
GA4: Critical Thinking &
Problem Solving
GA5: Flexibility
Due Date/Time End of Week 11 – refer Moodle for date and time
2 | P a g e
Aim:
To give you practical experience in database modelling, normalization and writing SQL
statements to query a relational database
Assignment Summary:
The project specification details provided in this document and you must use only given
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:
Australian Manufacturing Company (AMC) requires a database system to manage their stores,
employees, products, suppliers, patrons and orders.
The following information has been gathered about current business activities of AMC.
 AMC has many stores in different cities across Australia. Each store has unique store
number and other details like name, contact details (phone number, email address and
fax number) and location details (postal address composed of street number, suburb,
state and postcode).
 There are many departments within each store, for example, HR, accounts, finance and
sales etc. each department is uniquely identified by department number. AMC also
records department title, phone number and email address.
 Each store has a number of employees. Each department is supervised by a department
supervisor and each store is managed by a store manager who both are employees of
AMC. Each department has a single supervisor only i.e. the department supervisor is
also the supervisor for all the employees work within that department.
 Each employee is referenced by unique employee number, name (first and last names),
address, mobile number, email, TFN, salary and joining date. An employee’s salary is
recorded differently as per his or her employment type. Employees can be employed as
fulltime or casual basis. For a fulltime staff, annual salary is recorded and hourly rate for
a casual staff.
 Each store creates payslips for all employees work in that store on a weekly basis. As a
minimum payslip number, no of hours worked, employee detail, store detail and gross
pay are recorded.
 AMC sales different products like fashion, health, beauty, electronics etc. Each product
is characterised by product number, name, brand, description and price.
 Each product can be supplied by one or more supplier and each supplier can supply one
or many products. AMC also keeps appropriate details of their suppliers.
 Each store maintains an inventory of their products available for sale. The quantity of
each product available in store and the quantity ordered are kept.
 AMC patron visits the store and shops in the store. Each customer is characterised by
customer number, name (first and last names), mobile number and address.
 A patron may place orders. An order contains one or many products at a time. A store
keeps track of order number, order date, product(s) ordered and quantity ordered for
each product.
3 | P a g e
The proprietors of AMC have appointed your group as Database System Consultant to analyse,
design and develop conceptual data model and physical database. They want a software/online
application in future to be built on the database you design so that the operation of the AMC is
greatly improved.
Your created models must accommodate the provided requirements. It is understandable that
the provided information may not sufficient to create model that centralise all the data
appropriately. You are free to make assumptions about their business activities if require. Make
sure you record your assumptions.
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: Entity Relationship Diagram (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.
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 ERD 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).
2. Part B: Normalisation (10 marks)
1. Normalise all entities and relationships in optimal normal form. Provide the
normalised schema and discuss the level of normalisation achieved for each
relation. You goal should be normalise all entities in 3NF. Provide the reasons for
any relation that is not maintained in 3NF.
3. Part C: Relational schema and MySQL database (35 marks)
1. Create relational schema that matches ERD and normalised schemas you
created in Part A and B. The schema should include suitable data validation,
integrity and referential integrity constraints, for example, types and length of
attributes, primary and foreign keys etc. Make sure you follow standards, naming
conventions and notation. (10 Marks)
4 | P a g e
2. Using MySQL, you are required to develop a demonstration prototype system
(database) that stores data for AMC stores, employees, products, suppliers,
patrons and orders. Create a new database called AMCDB. Create tables
according to your relational schema created in step 1. (20 Marks)
Follow a standard naming convention for table names and also field names. Avoid
using spaces and any special characters in table and field names. Use
underscore_case or use camelCase to separate parts of a name.
You are required to be consistent in tables’ name, fields’ name, data types, field
size and key constraints with relational schema created in step 2.
3. Add at least three records into each table. (5 Marks)
4. Part D: SQL (24 marks)
Use the AMCDB 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.
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.
5 | P a g e
1. List the full name of Patrons, phone number and address in the descending order of
customer number. The full name is comprised of first and last name joined with single
space. Use the alias Customer full name for the composed column heading. (2 marks)
2. List all the products having price less than $100. (2 marks)
3. List the product number, product title, quantity ordered, unit price and total amount per
product (unit price * quantity ordered) for order having order number 1005. (3 marks)
4. List name, full address and fax number of stores that has the word ‘George’ anywhere in
the street component of address. Your query should consider the case sensitivity of the
street as well i.e. the stores with words ‘George’, ‘GEORGE’, ‘george’, ‘GeorGe’ etc. in
street should be returned by your query too. (2 marks)
5. List all the orders having at least two different products ordered. (2 marks)
6. List customer number, name and address of all the customers who has not placed any
order. (2 minutes)
7. Retrieve first name, last name and employee number of all the employees along with
department name and store name where they work. (3 marks)
8. For each store, display store name and number of employees works in account
department. (2 marks)
9. List all the orders placed for financial year 2018 i.e. orders placed after 30/06/2017 and
before 01/07/2018. (2 marks)
10. Retrieve customer number and the total number of orders placed by each customer. (2
marks)
11. For each order, retrieve the order number, order date and number of products ordered in
it. Sort the output in descending order of order number. (2 marks)
5. Part E: Personal Report – Every group member needs to write this part individually
and add to the documentation (4 marks)
1. Write a page to the AMC proprietors 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 Project Specification. Avoid making excuses or comments
that reflect negativity. Include an acknowledgement of all students you have spoken to
about the assignment.
6. Part F: Report Layout, SQL script and presentation (7 marks)
1. Deliverables for Parts A, B, C, D and E must be printed as a report with a cover sheet
attached. See Administrative Details on page 10 for more details. Your report must
include header and footer that include all the group members’ name and 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) Each group must submit a single zip file on Moodle which contains all the
assignment files (single word file with deliverables for parts A – D, separate
6 | P a g e
personal reports for each group member, MYSQL backup script notepad file and
presentation).
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.
7. Submission
a. When to submit
Refer to the Unit Description for due date, extensions and late submission policy.
b. What to submit
Part A
(1) Copy and paste the ERD that you created using Word, Visio, or other
software into your Word document.
(2) Title your AMC ERD.
Part B:
Include in your word document normalised schema and discussion on which normal
form each relation is in. Provide the reasons for any relation that is not maintained
in 3NF.
Part C:
7 | P a g e
(1) Include the relational schema in your documentation. The following is sample
tabular format on relational schema. You are free to add more columns if
required.
Table Name:
Field Name Data type Length Key Constraints
(3) Provide database schema of your AMCDB in your word document. Title your
AMCDB Database Schema. Below is sample. 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) Use the DESCRIBE command to describe the structure of each table in your
AMCDB 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 | |
+———-+————-+——+—–+———+——-+
8 | P a g e
(5) 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 |
+———-+————–+———-+———-+
(6) 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.
(7) Use screen captures to show referential integrity constraints as shown by the
following example for the propertyForRent table in the dream home database:
Part D:
(1) The answer to each SQL query question must be clearly numbered and
tabulated as shown in the example below.
Question 1:
SQL: SELECT lName, position
FROM Staff
Output: +——-+———-+
| lName | position |
+——-+———-+
| Brand | Manager |
9 | P a g e
| 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 E:
1. Include your personal report as per instructions given.
Part F:
1. PART A, B, C, D and E plus the SQL Script as described in PART F in the
specification.
2. Presentation
10 | 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 backups 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 mustinclude:
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 students’ 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.
11 | 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 group 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.
 Normalised Schema
o Normalised schema
o Appropriate arguments provided on leaving each entity in normal form which
you considered as optimal
 Database Schema
o Show each table design
o Include a screen capture of Designer tab to show table relationships in MySQL.
o Records for each table
 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 than just reference the prescribed text.
12 | P a g e
Database Management for Business Database Project (Assignment) 2019 T1
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 Task Total
Marks
A ER Diagram:
 Completeness of ER diagram, included all essential entities, attributes
and relationships with names, cardinalities and participation (10 marks)
 Primary and foreign keys (4 marks)
 Resolution of many to many relationships (3 marks)
 All assumptions clearly noted (3 marks)
Deduct marks (4 marks) if incorrect and inconsistent notation used
20
B Normalisation
 All relations are in appropriate format (7 marks)
 Appropriate arguments provided on leaving each entity in normal form
which you considered as optimal (3 marks)
10
C Relational Schema (10 marks)
 Schema matches ERD. All tables and attributes included with
appropriate formatting i.e. data types, field size, allow null value or not
etc. (5 marks)
 Primary and foreign keys included, foreign keys with reference to
primary table (3 marks)
 Appropriate notations (2 marks)
MYSQL Database (20 marks)
 Consistent translation from relational schema to MYSQL database (15
marks)
 Appropriate naming convention used (5 marks)
Records in MYSQL Database (5 marks)
 At least 3 records present in all tables (3 marks)
 Additional records shown (2 marks)
35
D SQL & Output 24
13 | P a g e
Database Management for Business Database Project (Assignment) 2019 T1
Q1 2
Q2 2
Q3 3
Q4 2
Q5 2
Q6 2
Q7 3
Q8 2
Q9 2
Q10 2
Q11 2
E Personal Report 4
F Report Layout, Presentation and SQL DDL Script 7
Late Penalties 5% each day late
Total: 100
Comments:
End of the document