Task 1 tables and pictures on jpg file

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.

Use table 1 picture here

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.

Use Table 2 and 3 here

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.

Use Invoice picture here

Your task is to design an information model in the ORM modelling language that suggests an effective and efficient way for storing SoftMarts 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.

Use picture with Lineitem and all those here

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.

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 F1F6 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.

Use Last 2 pictures at the bottom of jpg here

Phone Invoice& Date Paid TASK 1 Sarcorp Sun St, St Lucia 4067 Eastpae30 Beach Rd, Sandgae4017 32691111 Dr IN. Strin Sesame St. St L4067? 7650000 0501 002 200/18 0503 unpaid 0504peid 1007/18 002 003 Take 2: Customer Information Table 3: Imvice Anformation Itemcode B123 DL DB3 List price 799.50 700.00 1999.99 400.00 1890.50 500.00 700.00 Title Category Blossom 123 DataLight Database 3 SoftMart, 46 Gallium Street, Brisbane 4001 0501 001 8 invoice#: Customer#: date: 07/03/88 DB customer name: address: Starcorp 5 Sun St, St Luria 4067 DB SQL- I SQL plus DB WP WP Irem code Iitery orderedUnitpriceSubiotal 650.00325000 400.00400.00 170145 8507.25 799.50 79950 TextStar WordLight WL WordLight WL 10 SOLt SQL plus B123 Blossom 123 total anount due: S12956.75 TASK 2 0 Add (FS Remove (24) eaR Ald (alb)te Fl and all) to F2 Phone Invoice& Date Paid TASK 1 Sarcorp Sun St, St Lucia 4067 Eastpae30 Beach Rd, Sandgae4017 32691111 Dr IN. Strin Sesame St. St L4067? 7650000 0501 002 200/18 0503 unpaid 0504peid 1007/18 002 003 Take 2: Customer Information Table 3: Imvice Anformation Itemcode B123 DL DB3 List price 799.50 700.00 1999.99 400.00 1890.50 500.00 700.00 Title Category Blossom 123 DataLight Database 3 SoftMart, 46 Gallium Street, Brisbane 4001 0501 001 8 invoice#: Customer#: date: 07/03/88 DB customer name: address: Starcorp 5 Sun St, St Luria 4067 DB SQL- I SQL plus DB WP WP Irem code Iitery orderedUnitpriceSubiotal 650.00325000 400.00400.00 170145 8507.25 799.50 79950 TextStar WordLight WL WordLight WL 10 SOLt SQL plus B123 Blossom 123 total anount due: S12956.75 TASK 2 0 Add (FS Remove (24) eaR Ald (alb)te Fl and all) to F2