IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
Page 1
IAB201
Modelling Information Systems
Assignment 1 Handout
Due date: 7 September 2018 (11:59pm)
Weight: 30%
INSTRUCTIONS
• You must provide solutions for both tasks described in this handout. Marks are as indicated.
• This is an individual task. You must NOT work in groups/teams.
• You can use Microsoft Word or PowerPoint drawing tools, Microsoft Visio, or any other modelling
tool to develop models. For Visio, we recommend that you use the MS Visio template for drawing
ORM models available on the IAB201 Blackboard site.
• Assignment submissions are to be made via Blackboard and MUST be in a single PDF file.
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
TASK 1 (25 Marks)
For the scenario described below, complete Steps 1 – 5 of the Conceptual Modelling Design Procedure
(CMDP). Ensure each step of your solution is clearly and separately listed.
SCENARIO
SoftMart, a new Australian software retailer, maintains an information system to help with invoice and stock control. It
has so far made only a small number of sales. The details of the software items it has in stock are shown in Table 1.
Each software item is identified by an item code, but each also has a unique title. Each item belongs to one of exactly
three software categories, identified by codes (SS = spreadsheet, DB = database, WP = word processor); the full
names of these categories are not recorded. The list price of an item is the normal price at which the item is currently
sold. However, SoftMart may sell an item at less than the current list price (e.g. SoftMart may give a discount for bulk
orders or to favored clients, and the list price itself may change over time). There is no rule that enables the unit price
(i.e. the actual price charged for a copy of an item) to be derived from the current list price.
Itemcode Title Category Stock Qty List price
B123 Blossom 123 SS 8 799.50
DL DataLight DB 10 700.00
DB3 Database 3 DB 5 1999.99
Q Quinquo SS 6 400.00
SQL+ SQL plus DB 4 1890.50
TS TextStar WP 5 500.00
WL WordLight WP 10 700.00
Table 1: Stock Information
Each customer is identified by a customer number, but the combination of their name and address is also unique. For
simplicity, customer name is treated as a character string, and so is address. Customers have at most one phone number
recorded. Table 2 shows sample customer details. Customer details may be recorded before the customer actually
places an order. Once an order is placed, the items are issued to the customer together with an invoice. At the time the
data snapshot was taken for the output reports (Table 3), only four invoices had been issued. When a customer pays for
the items listed in an invoice, the date of payment is recorded. Each invoice is identified by its invoice number. Every
invoice is either paid in full or not at all.
Table 2: Customer Information
Customer# Name Address Phone
001
002
003
Starcorp
Eastpac
Dr I.N. Stein
5 Sun St, St Lucia 4067
30 Beach Rd, Sandgate 4017
7 Sesame St, St Lucia 4067
37650000
32691111
?
Invoice# Date Paid
0501 10/07/18
0502 20/07/18
0503 unpaid
0504 unpaid
Table 3: Invoice Information
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
Each of the four issued invoices is shown below. The invoice header giving the address of SoftMart is not stored. An
invoice includes a table of one or more rows, called "invoice lines". Each invoice line lists details about the order of one
or more units (copies) of a software item. For simplicity, assume that on a given invoice the same item can appear on
only one invoice line. For each invoice line, the item code, title, quantity of units ordered, and unit price are listed. The
total charge for the invoice line is displayed as a subtotal. The total charge for the whole invoice is displayed as the
amount due.
Step 1: Transform. familiar examples into elementary facts and apply quality checks
List the significant deep structure sentences that can be identified from the scenario description. For
example:
“The Invoice with InvoiceNr 0501 was issued to the Customer with CustomerNr 001”.
It is not necessary to list several fact type instances that describe the same fact type.
Step 2: Draw the fact types and apply a population check
Draw a fact type, including a sample fact table, for each of the deep structure sentences identified in Step 1.
See the example in the Week 2 lecture slides, slide 37, for a suggested layout.
For example, consider the only binary fact type shown in the diagram above. Note that this fact type is
introduced based on the example deep structure sentence proposed in the description of Step 1.
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
Perform. a population check by populating identified fact types with the fact instances from the scenario.
This can be accomplished by drawing fact tables.
For all the identified fact types discuss/demonstrate that they are indeed elementary by performing all the
necessary split and join operations on sample populations.
Finally, combine all of the identified fact types into a single ORM model.
Step 3: Check for entity types to be combined and note any arithmetic derivations
Discuss (in text) whether it makes sense to combine any of the entity types proposed in the diagram. If so,
describe the combination; if not, explain why not. In this discussion do not exceed the word limit of 300
words.
Introduce TWO arithmetically derivable fact types in your ORM model. You can use mathematical notation
or a textual description to specify derivation rules.
Step 4: Add uniqueness constraints and check the arity (length) of fact types
Introduce all necessary uniqueness constraints into your ORM model based on the scenario. For each
introduced uniqueness constraint, briefly explain the rationale behind your decision to include it in the
model.
Step 5: Add mandatory role constraints
Introduce all necessary mandatory role constraints into your ORM model based on the scenario. For each
introduced mandatory role constraint, briefly explain the rationale behind your decision to include it in the
model.
TASK 2 (5 Marks)
The conceptual schema diagram shown on the next page incorporates the use of various constraints to
enforce the rules of a particular universe of discourse. Apply each transaction shown in the table below the
diagram to the population shown in tables F1…F6 and indicate whether the transaction is accepted (A) or
rejected (R). If a transaction is rejected, indicate which constraint has been violated. Each of the transactions
applies to the same schema population shown in the tables. Treat each transaction as if it was the first to be
made with the proposed population.
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
TRANSACTION Accept/ Reject Constraint violated
Example: Add (d1, e3) to F3 R c6
Remove (c1,d1,e1) from F6
Add (c3,d3,e2) to F6
Add (c1,d2) to F5
Add (c2,d1) to F4
Add (c3,d1,e1) to F6
Remove (c2,d2) from F4
Add (a1,b2) to F1 and add ((a1,b2), e1) to F2
Remove (c2,d2,e2) from F6
Add ((a4,b2),e3) to F2
Add (c1,d2,e2) to F6
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
HAND-IN REQUIREMENT
Your solution of Assignment 1 must be submitted via Blackboard before Friday, 7 September 2018, 11:59pm.
The submitted file MUST be in PDF format.
Each submission must contain a declaration, signed by you, stating that it is your own original work (see
Appendix A for the template).
Your Assignment 1 report should be structured as follows:
• Cover page (including name, student number, and a completed declaration of original work)
• Answer to Task 1, consisting of a complete ORM model and the outputs of Steps 1-5 of the
Conceptual Schema Design Procedure that you have taken to develop the model, clearly separated
into each step.
• Answer to Task 2.
LATE SUBMISSIONS
According to QUT policies, submissions past the deadline will NOT be marked, and will thus attract a mark
of 0. Therefore, you are strongly advised to submit your report, even if it is only partially complete, by the
due date.
EXTENSIONS
According to QUT policies, an extension to the due date of the assignment may only be granted on
medical/compassionate grounds. Please see the Late assignments and extensions information on the unit’s
Blackboard Assessment page.
REVIEWS
We cannot remark assessments. All assessments will have detailed feedback explaining the reasons for the
marks allocated. If you require additional feedback or clarification, discuss it with your tutor. If you believe
that there is a component that has not been marked in accordance with the criteria sheet, you must
identify it in writing to your tutor.
ACADEMIC HONESTY
Any action or practice on your part which would defeat the purposes of assessment is regarded as
academic dishonesty. The penalties for academic dishonesty are provided in the Student Rules. For more
information consult the QUT Library resources for avoiding plagiarism.
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
Page 8
GETTING FEEDBACK
The teaching team will be available to answer specific questions about the assignment, but not to pre-
mark assignments. Lecturers and tutors will NOT read report drafts and review detailed models prior to
the submission of the assignment.
Detailed feedback will be provided in written form. when the marker returns the marked assignment back
to the students. Students are also welcome to discuss any queries about the feedback they will receive.
GENERAL INQUIRIES
For specific content-related inquiries about your assessments, please contact your tutor.
MARKING CRITERIA
Task 1: You will receive a maximum of 5 Marks for correct completion of each of Steps 1-5 of the
Conceptual Schema Design Procedure (see separate marking criteria sheet for more details).
Task 2: You will receive 0.5 Marks for each correct transaction (either A or R; for a rejected transaction you
are also required to correctly identify at least one violated constraint c1…c13).
IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM
APPENDIX A – DECLARATION TEMPLATE
By submitting this assignment, I am aware of the University rule that a student must not act in a manner
which constitutes academic dishonesty as stated and explained in the QUT Manual of Policies and
Procedures. I confirm that this work represents my individual effort. I declare that it does not contain
plagiarized material.