辅导OS Information Modelling、讲解OS/CS 计算机编程、OS/CS编程辅导讲解

日期：2018-09-07 04:04

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

Page 2

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

Page 3

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.

<end of scenario>

SoftMart, 46 Gallium Street, Brisbane 4001

invoice#: 0501 date: 03/07/18

customer#: 001 customer name: Starcorp

address: 5 Sun St, St Lucia 4067

Item code Title Qty ordered Unit price Subtotal

WL

Q

SQL+

B123

WordLight

Quinquo

SQL plus

Blossom 123

5

1

5

1

650.00

400.00

1701.45

799.50

3250.00

400.00

8507.25

799.50

total amount due: $12956.75

SoftMart, 46 Gallium Street, Brisbane 4001

invoice#: 0502 date: 03/07/18

customer#: 002 customer name: Eastpac

address: 30 Beach Rd, Sandgate, 4017

Item code Title Qty ordered Unit price Subtotal

Q

TS

Quinquo

TextStar

4

4

400.00

500.00

1600.00

2000.00

total amount due: $3600.00

SoftMart, 46 Gallium Street, Brisbane 4001

invoice#: 0503 date: 10/07/18

customer#: 001 customer name: Starcorp

address: 5 Sun St. St Lucia 4067

Item code Title Qty ordered Unit price Subtotal

Q Quinquo 4 350.00 1400.00

total amount due: $1400.00

SoftMart, 46 Gallium Street, Brisbane 4001

Invoice#: 0504 date: 20/07/18

customer#: 003 customer name: Dr I.N. Stein

address: 7 Sesame St, St Lucia 4067

Item code Title Qty ordered Unit price Subtotal

B123

DL

Blossom 123

DataLight

1

1

799.50

700.00

799.50

700.00

total amount due: $1499.50

IAB201 Semester 2, 2018 – Assignment 1 – Information Modelling with ORM

Page 4

Your task is to design an information model in the ORM modelling language that suggests an effective and

efficient way for storing SoftMart’s data.

Complete the drawing of the ORM conceptual schema proposed below for the universe of discourse

specified in the scenario by performing steps 1-5 of the Conceptual Schema Design Procedure. Use only

those entity types and value types proposed below (do NOT introduce any new entity/value types). You may

realign or rearrange the entity and values types to suit the layout of your model.

Customer (.Nr)

CustomerName Invoice (.Nr) Date (.DMY)

UnitPrice (AUD:)

Item (.Code)

was issued to

Address PhoneNr

ListPrice (AUD:) Category (.Code)

Quantity

ItemTitle “LineItem”

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

Page 5

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

Page 6

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

Page 7

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 premark

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

Page 9

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.

Full Name Student No. Signature