Posted: January 31st, 2023

Assignment A1b – Database Design – Loyalty App System (LA) Team Assignment.

FIT2094-3171 Introduction to Databases
Semester 2, 2019
Assignment A1b – Database Design – Loyalty App System (LA)
Team Assignment.
Assignment weighting 20%
Lecturers in Charge: Brendon Taylor (CL), Jaco Pretorius (SA),
Jin Zhe (MY), Marc Cheong (CL)
1. Preambles
1. ⚠ Warning – pay special attention to the items marked in red and/or with a ⚠
symbol. Failure to adhere to these will result in marks deductions.
2. ⚠ Any attempt to cheat in this unit will be referred to the Faculty for
disciplinary action. As a reminder, Faculty and unit management are aware of
this and frequently detect and report cases of cheating every year.
2. Background
This task continues the work you have started in A1a by refining/extending the model you
developed and implementing it as a set of tables under your Monash Oracle database
account.
⚠IMPORTANT NOTE BEFORE STARTING.
Assignment A1b’s brief must be read in conjunction with the A1a brief – i.e. your final
model must encompass both sets of requirements.
In this business case simulation, the Client (“LoyaltyApp”) has considered all the
Monash expert opinions (via the “Client Forum” on Moodle), and have made several
extra directives/requirements set forth in this document which may override prior
design ‘draft’ decisions. Therefore, there are some extra requirements stated below
in S3.2, which clarify / disambiguate the questions posted on the Client Forum.
You may modify your A1a conceptual model in any manner you wish as you work through
A1b, provided your final model meets both sets of requirements.
COPYRIGHT 2019 MONASH UNIVERSITY Page 1 of 13
3. Further Design Choices
3.1. Redemptions, COP, and Gifts
Further discussions with the LoyaltyApp have revealed that customers who have sufficient
points can conduct Redemption transactions to gain Gifts. (These redemptions are quite
rewarding, which is why customers are encouraged to use LoyaltyApp! ). These
Redemptions are done online, and does not involve any staff or stores etc. The information
required to be stored in the LoyaltyApp backend can be found in Section 3.3.
These Gift types include e.g. “Free 14-day trip to Bali by Flight Centre Australia – 80000
points”, “Free toaster at Hirsch’s Johannesburg for 6000 points”, “Free coffee at Starbucks
Malaysia for 500 points” etc. Once a Redemption takes place, the customer may be
subject to a ‘cooling-off period’ (COP) which stops them from gaining any points and
pauses their VIP statuses (if any) for a set amount of time, to keep the program fair for
other customers. The COPs range from a period of 0 days (for small Gifts like “Free
coffee”) to 100 days (for large Gifts like “Free 14-day trip”).
LoyaltyApp needs to have recorded, for each redemption: what Gift type is redeemed; the
number of points spent (and the new balance); if it leads to a COP – and for those that do,
the number of days for which takes effect. When a COP is granted, its start date is set to
the date of the Redemption. which triggered the status. After ending the COP, the
customer needs to fill up a survey of their experience before restoring their regular
membership status – this has to be logged.
3.2. Business Rules and Choices
There are several business rules which LoyaltyApp Management needs to clarify, based
on database designers’ queries.
1. A payment card has a PCCN; but in the rare event that there are ‘collisions’
between PCCNs, LoyaltyApp requires the combination of PCCN and suitable bank
information as a compound key.
2. Due to privacy concerns, no customers can share a payment card — in other words,
for a particular card, only ONE customer can use it.
3. Recognising the business practices of similar modern web businesses (e.g. Didi,
Uber, AirTasker, AirBnB), LoyaltyApp has allowed the mobile phone number to
change. Hence, a surrogate key may be introduced ONLY in the logical model to
uniquely identify a customer. LoyaltyApp wants it to be standardised in the format
CUST_###### – e.g. the first Customer is CUST_000001 etc.
4. None of the following will be supported: split payments, online stores without a retail
outlet, cash transactions.
COPYRIGHT 2019 MONASH UNIVERSITY Page 2 of 13
⚠IMPORTANT NOTE ON SCOPE EXPANSION
LoyaltyApp has also recognised that some database expert teams might only have one
team member, whereas some other database teams might have two members.
Therefore they have consulted their Board of Directors and shareholders for the
following business decisions.
FOR TEAMS OF ONE: To keep the project on track, they have refined the scope
as follows.
Due to insufficient scope, LoyaltyApp does not support staff working at more than one
store. Therefore, no history needs to be kept for staff work records; with the proviso that
staff can freely switch between stores but only after they finish their contract with a
current store (i.e. no concurrent work at more than one store). A1a’s spec still holds, i.e.
only the staff needs to be logged in a Purchase.
In addition, for Rewards, the company directors assume that all stores must uniformly
have a given reward if it applies to their business. Say, for a Burger Reward – all stores
selling burgers must have the reward.
FOR TEAMS OF MORE THAN ONE: LoyaltyApp is impressed with the speed
and quality of your design!
After meeting their Board of Directors, they have now decided to allow staff to work at
more than one store (e.g. to support casual staff working at multiple venues in a single
day). However, to prevent conflict-of-interest, each allocation (or shift) at each store must
be logged, including the time a staff member started the shift, and the time they ended
the shift. This effectively creates a ‘history’ for auditing purposes. For transparency and
to prevent fraud, each staff member’s associated shift needs to be logged for each
Purchase.
Their Board of Directors also decided that for each Store, they can independently decide
if they want to have a given reward. Say for a Fuel Reward, not all petrol stations
(servos) might want to implement it. Therefore, there needs to be a mechanism to keep
track of which stores support which rewards.
3.3. Normalisation based on Internal Business Documents
LoyaltyApp Management have also provided three of their internal documents, depicted
below, which they make use of showing some of the data they wish to record:
A sample payment card report:
● The credit rating is derived from the number of transactions so far:
A (200+); B (100-199); C (50-99); D (< 50).
COPYRIGHT 2019 MONASH UNIVERSITY Page 3 of 13
● Features for verification: this indicates physical markers on the card that help staff
quickly verify the correct card is used. It can be a combination of all/any of the 4
features shown on the form.
● Note that LoyaltyApp has indicated that it needs flexibility in the design to be able to
add new, and remove current ‘payment card types’ and ‘features for verification’ as
circumstances change.
COPYRIGHT 2019 MONASH UNIVERSITY Page 4 of 13
Two sample customer reports:
● Note that one customer has more details than the other.
● Do consider both reports in your normalisation. See Section 5 for full explanation.
(continued next page)
COPYRIGHT 2019 MONASH UNIVERSITY Page 5 of 13
A customer Redemption COP report.
● This should be fairly self-explanatory.
COPYRIGHT 2019 MONASH UNIVERSITY Page 6 of 13
4. Moodle Forum (to simulate an industry client)
REMEMBER you must keep up to date with the Moodle A1b Client Forum where further
clarifications may be posted (this forum is to be treated as your client). Please be careful to
ensure you do not post anything which includes your reasoning, logic or any part of your
work to this forum, doing so violates Monash plagiarism/collusion rules.
You are free to make assumptions if needed however they must align with the details here
and in the assignment forums and must be clearly documented (see the required
submission files).
5. Tasks
ENSURE your name and ID as well as your team number are shown on every page of
any document you submit. If a document is a multipage document (such as the
normalisation), please also make sure you include page numbers on every page.
⚠ INSPECTIONS BY TUTORING/TA STAFF IN LABS/TUTES
As you work on this assignment task, your team will have to show the progress of the
assignment TO THE TEACHING STAFF IN THE LAB/TUTE AT LEAST ONCE.
Any submission without this will incur a grade penalty of 10 marks (a 10 mark
deduction).
Task to complete:
1. TASK 1:
Based on your A1a conceptual model and feedback from your tutor, using Lucid
Chart, prepare FULL conceptual model using Crows-Foot notation for LoyaltyApp
System. For this FULL conceptual model, you should include:
● Identifiers (key) for each entity
● All required attributes
● All relationships. Participation and Connectivity for all relationships must be
shown on the diagram; as well as clear labels.
Surrogate key must not be added and your model should follow ERD notation
standard used in this unit.
2. TASK 2:
Perform normalisation to 3NF for the data depicted in the sample internal
COPYRIGHT 2019 MONASH UNIVERSITY Page 7 of 13
documents/reports. Note that only one normalisation is required for the customer
report, you have been provided with two samples so you can appreciate some of the
variety which occurs.
The approach you are required to use is the same approach as shown in the
normalisation lecture and tutes/labs in the unit. The normalisation must begin by
representing the supplied documents as a single UNF form.
During normalisation, you must:
○ Not add surrogate keys to the normalisation.
○ You must include all attributes (you must not remove any attribute as
derivable)
○ Clearly show UNF, 1NF, 2NF and 3NF.
○ Clearly identify the Primary Key in all relations.
○ Clearly identify the partial and transitive dependencies (if they exist) in all 1NF
relations. You may use a dependency diagram or alternative notation (see the
normalisation tutorial sample solution for a possible alternative
representation).
○ If required, carry out attribute synthesis.
The attribute names used in your normalisation and those on your subsequent logical
model must be consistent i.e. the same name used on each for the same property.
3. TASK 3:
Based on your A1a conceptual model, your reading of this document (A1b) –
including the correct ‘SCOPE EXPANSION’ in S3.2 – and the normalisations you
carried out in TASK 1 above, prepare a logical level design for the LoyaltyApp
database.
○ The logical model must be drawn using the Oracle Data Modeler. The
information engineering or Crow’s foot notation must be used in drawing the
model. Your logical model must not show datatypes.
○ All entities depicted must be in 3NF
○ All attributes must be commented in the database (ie. the comments must
be part of the table structure, not simply comments in the schema file).
○ Check clauses/look up tables must be applied to attributes where
appropriate.
○ You MUST include the legend as part of your model.
○ Reminder: Note that you must clearly show your development history with
your tutor/lab teacher in class as you work on your model.
4. TASK 4:
Generate the schema for the database in Oracle Data Modeler and use the
schema to create the database in your Oracle account. The only edit you are
permitted to carry out to the generated schema file is to add header comment/s
containing your details (student name/id) and the commands to spool/echo your run
of the script.
○ Capture the output of the schema statements using the spool command.
○ Ensure your script includes drop table statements at the start of the script.
COPYRIGHT 2019 MONASH UNIVERSITY Page 8 of 13
○ Name the schema file as la_schema.sql.
6. Submission Requirements
Assignment A1B:
⚠Due: Week 9, Friday 27 September 2019, 11:55pm (at your campus’s timezone).
The following files are to be submitted by only ONE TEAM MEMBER and must exist,
along with the source documents from which they were generated:
1. A pdf document showing containing full conceptual model. Name it
la_conceptual_full.pdf. You are advised to refer to A1a on how to export pdfs from
LucidChart.
2. A pdf document showing your full normalisation of the sample internal
forms/documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the
file la_normalisation.pdf
3. A single page pdf file containing the final logical Model you created in Oracle Data
Modeller. Name the file la_logical.pdf. This pdf must be created via File – Data
Modeler – Print Diagram – To PDF File from within SQL Developer, do not use
screen capture.
4. A zip file containing your Oracle data modeler project (in zipping these files be sure
you include the .dmd file and the folder of the same name). Name the file
la_oraclemodel.zip.
○ Part of the assessment of your submission will involve your marker extracting
your model from this zip, opening it in SQL Developer Data Modeller,
engineering to a new Relational model and from this your marker will generate
a schema which will then be compared with your submitted schema (they must
be the same for your schema to be accepted).
○ ⚠ For this reason your model must be able to be opened by your marker
and contain your full model otherwise your TASK 3 will not be able to be
marked. No exceptions.
○ ⚠ For this reason, you MUST carefully check that your model is complete on
the University lab computers – ensure to take your submission archive, copy it to
a new temporary folder, extract your submission parts, extract your model and
ensure it opens correctly before submission.
⚠ CORRECT SUBMISSIONS, FILENAMES, AND FILE CONTENTS
It is your responsibility to ENSURE that the files you submit are the correct files –
we strongly recommend after uploading a submission, and prior to actually
submitting, that you download the submission and double check its contents.
COPYRIGHT 2019 MONASH UNIVERSITY Page 9 of 13
If you fail to ensure that the files are correctly working and named as per
specifications, tested on the University FIT labs, it is ultimately your responsibility
and you ACKNOWLEDGE that your marks will be lost.
⚠ WARNING: POLICY VIOLATIONS
Students have, in the past, tried to argue with unit staff about the above marks
deduction.
Unit staff have encountered students with often hostile arguments – e.g. “I just
renamed one file and it broke so it’s not my fault, you can’t deduct my marks”,
“despite this my schema clearly works and I clearly deserve the marks”. Such
abuse will NOT be tolerated.
5. A schema file (CREATE TABLE statements) generated by Oracle Data Modeller.
Name the file la_schema.sql
6. The output from SQL Developer spool command showing the tables have been
created. Name the file la_schema_output.txt
7. A pdf document containing any assumptions you have made in developing the
model or comments your marker should be aware of. Name the file
la_assumptions.pdf
These files must be zipped into a single zip file named
a1b-–.zip
e.g., a1b-xyz123-abc468.zip before the assignment due date/time.
Submit the ZIP to Moodle before the due date.
⚠ LATE SUBMISSIONS
Late submission will incur penalties as outlined in the unit guide (5 marks
deduction per day or part thereof).
A reminder to abide by the special consideration policies and abide by the correct
special consideration submission process specific to each campus. Remember that
Special Consideration is NEVER automatic, and remember to adhere to the timeframes
for submission. Also remember that Special Consideration is granted only ONCE and
cannot be extended.
Your assignment MUST show a status of “Submitted for grading” before it will be marked.
Please see the screenshot which follows.
COPYRIGHT 2019 MONASH UNIVERSITY Page 10 of 13
If your submission shows a status of “Draft (not submitted)” it will not be assessed and will
incur late penalties after the due date/time.
Please carefully read the documentation under “Assignment Submission” on the Moodle
Assessments page.
COPYRIGHT 2019 MONASH UNIVERSITY Page 11 of 13
High-level Marking Rubric
Please note that this high-level marking rubric is provided as a general guidance to students, which
includes (BUT IS NOT LIMITED TO) the following key result areas.
⚠ You are also required, at all times, to observe and implement the best practices as taught
in this unit, in addition to this rubric; this rubric is not an excuse to not implement them.
Outstanding (HD) Adequate (Range P – D) Not Adequate (N)
Identify the data
requirements to
support an
organisations
operations from
the supplied case
study and
expresses these
via a database
full conceptual
and logical model.
[55 marks]
All Loyalty App System
operations are supported:
● All/most required
relations identified.
● All relations are in 3NF
● All/most required
relationships have been
captured by placing FK in
correct relation
● All/most required
cardinality and
participation, as well as
labels, have been
captured
● All/most data types and
data integrity
requirements (Entity,
Referential, Domain)
have been correctly
identified
Some of Loyalty App
System operations are
supported:
● Majority of relations
identified.
● Majority of relations are
in 3NF
● Majority of required
relationships have been
captured by placing FK
in correct relation
● Majority of required
cardinality and
participation, as well as
labels, have been
captured
● Majority of data types
and data integrity
requirements (entity,
referential, domain)
have been correctly
identified
Few of Loyalty App System
operations are supported:
● None/few of relations
identified.
● Majority of relations are
not in 3NF
● None/few required
relationships have been
captured. Majority of
FKs are placed in
incorrect relations.
● None/few of required
cardinality and
participation, as well as
labels, have been
captured
● None/few of data types
and data integrity
requirements (entity,
referential, domain)
have been correctly
identified
Understand and
follow
normalisation
methodology
[30 marks]
All/majority of the
normalisation steps have
been correctly followed:
● All/most normalisation
processes are correct
● Dependency diagrams
have been provided and
match normalisation.
● Normalisation result is
correctly integrated into
logical model
Some of the normalisation
steps have been correctly
followed:
● Majority of
Normalisation
processes are correct
● Dependency diagrams
have been provided and
match normalisation in
the majority of
situations.
● Majority of normalisation
result is correctly
integrated into logical
model
Few of the normalisation
steps have been correctly
followed:
● Significant errors during
the Normalisation
processes
● Dependency diagrams
not provided or have
major errors
● Normalisation result is
not correctly integrated
into logical model
Able to generate
and modify
relational model
and schema given
a logical model in
SQL Developer.
[10 marks]
All/majority of the schema
generation processes have
been correctly followed:
● SQL Developer
Relational model
correctly generated from
the logical model
Some of the schema
generation processes have
been correctly followed:
● SQL Developer
Relational model
correctly generated from
the logical model
Few of the schema
generation processes have
been correctly followed:
● SQL Developer
Relational model not
correctly generated
from the logical model
COPYRIGHT 2019 MONASH UNIVERSITY Page 12 of 13
● All drop commands,
database comments and
spool command included
● No “extra” edit in schema
file
● The DDL script was
executed without errors.
● Some of drop
commands, database
comments and spool
command included
● The DDL script was
executed without errors.
● There is “extra” edit
(other than identity
information and set
echo/spool commands)
in schema file
● The DDL script was
executed with errors.
Able to correctly
use the required
notation
convention and be
consistent in its
usage.
[5 marks]
● All notations in the model
are consistent and follow
the Logical Model
standards.
● Most notations in the
model are consistent
and follow the Logical
Model standards.
● Few notations in the
model are consistent or
follow the Logical
Model standards.
Able to
demonstrate
consistent A1B
work by showing
it to TA staff at
least ONCE
(refer Section 5)
in the weekly
tute/lab.
If not followed, a grade
deduction of 10 marks
applied.
COPYRIGHT 2019 MONASH UNIVERSITY Page 13 of 13

Check Price Discount

Study Notes & Homework Samples: BCJ100 (2) Define due process, as required by the Bill of Rights »A study of the role of human rights in the fight against terrorism