Page 1 of 7
RMIT Classification: Trusted
School of Computing Technologies
ISYS3412 Practical Database Concepts/ISYS1055 Database Concepts
Assessment 4

Assessment Type: Individual project. Submit online via Canvas→Assignments→Assessment 4. Marks
are awarded for meeting requirements as closely as possible. Clarifications/updates may be made via
announcements/relevant discussion forums.
Due dates:
Milestone 1 submission due 9:00am 12 April 2021, Monday.
Milestone 1 demonstration due in practical classes in Week 6 (12-16 April 2021).
Final report due 23:59 4 June 2021, Friday.

Weighting: 40 marks
1. Overview
Database systems are a key technology for the storage, management, manipulation, and retrieval of
structured data. They have an impact on the use of information technology in applications ranging from
banking, to travel bookings, to online shopping. In this assessment you will apply the skills and concepts that
you have learned about database systems in the course.
2. Assessment Criteria
This assessment will determine your ability to:
1. Independently solve problems by using database concepts taught in the course.
2. Understand the relational model.
3. Independently design a database using the ER model.
4. Write SQL queries for data analysis tasks.
5. Meet deadlines.
Seek clarification from your instructor, when needed, via specific discussion forums.

This assignment is worth 40 points, which accounts for 40% of the overall assessment for the course. The
assessment components and weights for the course are:
Assessment 1 Assessment 2 Assessment 3 Assessment 4
10% 30% 20% 40%
3. Learning Outcomes
• This assessment is relevant to all learning outcomes in the course guide.
4. Submission format
Submit your assignment via Canvas→Assignments→Assessment 4. You are required to submit a plain text
file (.sql) for Milestone 1 (due by 9:00am 12 April) and a pdf file (.pdf) for the final report (due by 23:59 4 June).
Both files must be named after your student number -- for example, S1234_milestone1.sql and
S1234_report.pdf. The .sql file must run in SQLite.
● It is your responsibility to correctly submit your files. Please verify that your submission is correctly
submitted by downloading what you have submitted to see if your file includes the correct content.
● Never leave submission to the last minute -- you may have difficulty uploading files.
● You can submit multiple times. However, if your last submission is after the due time, late penalties apply.
5. Academic integrity and plagiarism (standard warning)
Academic integrity is about honest presentation of your academic work. It means acknowledging the work of
others while developing your own insights, knowledge and ideas. You should take extreme care that you have:
Page 2 of 7
RMIT Classification: Trusted
• Acknowledged words, data, diagrams, models, frameworks and/or ideas of others you have quoted
(i.e., directly copied), summarised, paraphrased, discussed or mentioned in your assessment through
the appropriate referencing methods,
• Provided a reference list of the publication details so your reader can locate the source if necessary.
This includes material taken from Internet sites.
If you do not acknowledge the sources of your material, you may be accused of plagiarism because you have
passed off the work and ideas of another person without appropriate referencing, as if they were your own.
RMIT University treats plagiarism as a very serious offence constituting misconduct. Plagiarism covers a
variety of inappropriate behaviours, including:
• Failure to properly document a source
• Copyright material from the internet or databases
• Collusion between students
For further information on our policies and procedures, please refer to the University website.
6. Assessment declaration
When you submit work electronically, you agree to the assessment declaration.

7. Rubric/assessment criteria for marking
● Missing Milestone 1 submission (due 9:00am 12 April) results in a penalty of 20 marks. Missing Milestone
1 demonstration in Week 6 (12-16 April) results in a penalty of 20 marks.
● Please make sure you demonstrate your Milestone 1 at your respective practical class shown in your
timetable. It is not allowed to present your milestone at the lab session you are not enrolled in, unless you
get prior approval from the course coordinator.
● Failure to submit files in the required format results in a penalty of 10 marks. Examples include report not
in PDF, database file does not execute, SQL file not in plain text format, ER diagram not using notations in
the lecture slides.
● Late submission of the final project report results in a penalty of 4 marks for (up to) each 24 hours being
late. Submissions more than 5*24 hours late results in zero marks.
● If unexpected circumstances affect your ability to complete the assignment, you can apply for special
consideration.
o Requests for special consideration of within 7*24 hours please can be via emailing the course
coordinator directly with supporting evidence.
o Request for special consideration of more than 7*24 hours must be via the University Special
consideration: https://www.rmit.edu.au/students/student-essentials/assessment-and-
exams/assessment/special-consideration.
o Special Consideration that extends beyond the release of solutions (typically 1—2 weeks)
will automatically result in an equivalent assessment in the form of an online test and interview on
the same topics (time to be arranged by the course coordinator).
o More information on special consideration is available at
https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/special-
consideration

Page 3 of 7
RMIT Classification: Trusted
Criteria Marks
Application scenario 0 pts

Application scenario
not sensible.
1pts

Small application for
1-2 classes of
objects with basic
analysis
requirements.
2 pts

Medium application
for 3-4 classes of
objects with
reasonable analysis
requirements.
3 pts

Sizeable application
for >5 classes of
objects with
reasonable analysis
requirements.
4pts

Sizeable application
for >5 classes of
objects with
advanced analysis
requirements.
ER diagram 0 pts

Missing ER
diagram.
1-3 pts

Small database of
1-2 entity sets in the
ER diagram.
4-6 pts

Medium-sized
database of 3-4
entity types in the
ER diagram and
correct modelling.
7-9 pts

Sizable database of
>5 entity types in
the ER diagram but
some modelling
errors.
10 pts

Sizeable database
of > 5 entity types in
the ER diagram and
all correct
modelling.
Database
implementation
0 pts

No implementation.
Implementationdoes
not support design.

All tables contain <5
rows of data.
1-4 pts

Small database of
< 3 tables.

Implementationmost
ly does not support
the ER diagram.

Most tables contain
<5 rows of data.
5-7

Medium-size
database of
3-5 tables.

Implementationmost
ly supports the ER
diagram.

Most tables contain
6-10 rows of data.
8-10 pts

Sizeable database
of >5 tables with
limited data integrity
constraints.

Implementation
supports design.

Most tables have
>10 rows of data.
11 pts

Sizeable database
of
> 5 tables with all
required data
integrity constraints.

Implementation
supports design.

All tables have >10
rows of data.
Data analysis tasks
and SQL queries.
0 pts

No SQL queries.

Queries are not
based on the
database.

SQL queries mostly
do not run.


1-2 pts

Simple analysis with
simple SQL queries.


3-6 pts

Reasonable
analysis with
medium-level SQL
queries.


Most queries run
successfully.
7-10 pts

Reasonable
analysis with
medium-level SQL
queries (e.g. a
query with 2 to 3
joins and 2-3
conditions in the
WHERE clause;
most importantly it
has to be a
meaningful query in
real-world
scenarios)

All queries run
successfully.
11 pts

Advanced analysis
and complex SQL
queries (e.g. a
query with more
than four joins and
more than three
conditions in the
WHERE clause;
most importantly it
has to be a
meaningful query in
real-world
scenarios).

All queries run
successfully.
Report 0 pts
Unacceptable

Report
disconnected from
database or
nonsensical.
Writing is
unintelligible.

No or nonsensical
Discussion section.
1 pts
Poor

Some sections
missing or not
consistent.

Poorly written and
difficult to
understand.

Somewhat sensible
Discussion section.
2 pts
Acceptable

All sections are
present and
consistent with the
application scenario.


Mostly sensible
Discussion section.
3 pts
Good

Writing generally
flows well with some
minor grammar
errors.

Meaningful
Discussion section.
4 pts
Excellent

Writing flows well
and grammatically
correct.

Interesting
Discussion section.
Page 4 of 7
RMIT Classification: Trusted
8. Assignment.
This is an individual project. You are required to design and develop a database system for some data
analysis application that includes all stages from conceptual database design, relational database schema
design, implementation of the database and populate with data, and data analysis using SQL queries to
demonstrate usage of the database system. ER models must use the notations from the lecture slides. SQL
queries must use the SQL syntax of SQLite. You are required to design a scenario for database application.
To help you understand the expectation for the size of this project and the marking rubric, an example
scenario -- with respect to the assessment criteria -- is given in the Appendix section,
This project runs throughout the whole semester from when this specification is released. You are
supposed to work on your project and get feedback from tutors during the practical classes throughout the
semester. The table below gives you an approximate timeline for you to complete the project.
Application and database design
and initial implementation of
tables.
Weeks 3-6 Milestone 1 submission of SQL DDL for the initial
database due 9:00am 12 April.
Specifically, the .sql file must contain the following:
The DDLs used to construct the databases (i.e., a
sequence of CREATE TABLE statements with
PK/FK constraints that you think that need to be
preserved).

When populating data into the Database created
above, the students have the flexibility to choose the
tools that they are familiar with, such as
1. INSERT into xx values ....;
2. Use XML or CSV to populate data.
3. Etc.

Real Data vs Synthetic Data
Note: You can make your choice to crawl/download
data from real world and reorganize them to align
with the schema of your database. Alternatively, you
can generate synthetic data. It is highly
recommended that you go ahead to find some real
datasets which contain meaingful information, to
facilitate your query processing at later stage of your
project.
The milestone 1 demonstration will be taken in
practical classes in Week 6 (12-16 April). During the
practical class, you are asked to demonstrate how to
create a new database and show how to use the
submitted SQL file to create and populate the tables
using SQLite Studio.
The demo is strictly restricted to the students
registered in a practical class group.
For each group, the students registered in that
group will take priority to be marked first.
Please come to your registered session only.

Page 5 of 7
RMIT Classification: Trusted
Initial design of analysis and
implementation using SQL queries
Week 7
Refine and finalise database
design with the ER diagram and
the relational database schema in
SQLite.
Weeks 9-10
Refine and finalise analysisdesign
and SQL queries
Weeks 10-12
Report writing Weeks 12-13 Final submission due 23:59pm 4 June 2021, AEST
time, Friday, Week 13



Page 6 of 7
RMIT Classification: Trusted
You are required to submit a plain text file (.sql) for Milestone 1 and a pdf file (.pdf) for the final report on
Canvas; Both files must be named after your student number -- for example, S1234_milestone1.sql and
S1234_final.pdf. The .sql file must run in SQLite.
Milestone 1 submission:
• You are required to submit a .sql file of SQL DDL for your initial database schema by 9:00am 12 April
2021. Failure to do so incurs penalty of 20 marks.
• You are also required to demonstrate to your tutor the data in tables of your database during your
practical classes in Week 6 (12-16 April). Failure to do so incurs penalty of 20 marks.
Final report submission:
You are required to submit a final report of maximal 2 pages (including any references or appendices if
applicable), and the font size should be no smaller than 11pt. The report must include the following sections:
1) Overview. Describe the scenario for your database application. Give an overview of the final database,
including description of the classes of objects. Describe the system development process and explain if
the final database schema is different from the initial database design in Milestone 1.
2) Final database schema. Give the ER diagram for your database and the relational database schema.
Give the SQL DDL for the database tables.
3) Data Analysis Tasks. Give 10 data analysis tasks and the corresponding SQL queries based on your
database.
4) Discussion. Discuss limitations of your database system, and any further work that can enhance the
database application.
Page 7 of 7
RMIT Classification: Trusted
Appendix. The Beautiful House database system.
Application scenario.
Beautiful House (BH) is a real-estate agency that has offices throughout Melbourne. BH would like to build a
database to manage data about staff, offices and properties for Beautiful House. The database will be used for
business operation and data analysis applications.

This is a medium-sized database application. The initial database schema is as shown below.

Branch(branchNo, street_addr, suburb, phone_num, manager*)
Staff (emp_no, givename, surname, position)
Property(property_no, street_addr, suburb, type, num_rooms, rent, agent*)

Example application tasks.

1. Simple task - Give the contact phone number of a staff named “John Citizen”.
2. Medium-level task - List agents who manages more than three properties.
3. Advanced task – Analyse the performance of agents. Generate a table listing for EACH agent and
EVERY suburb, the number of properties s/he manages. An agent not managing any property in an
area should have zero.




欢迎咨询51作业君