Task 5 Pass and Credit

Overview

114046019367500

In this final set of P and C tasks for the unit youre going to do further activities on ERDs, and SQL.

For submission, its same process as the other the other tasks: complete tasks, document them (usually by creating screen grabs) and submit online.

To get started, download the files T05P.DOCX and T05C.DOCX from Canvas

Please note, ALL tasks in each section MUST be completed for you to successfully complete the Pass and/or the Credit Task.

When complete, generate the files T05P.PDF and T05C.PDF

Finally log into Doubtfire and submit both files into the appropriate weekly tasks.

Pass Level Tasks

102743019177000

Pass 5a

Read the following narrative:

Petes Pools have a number of swimming centres. Each centre has a name, establishment date, and a description. Clients (who have an ID and name) can join one or more of these swimming centres. As a client joins a centre, the date that the client joined is to be recorded.

Draw an ERD based on this case study. Fully expand all M:M relationships into M:1 relationships. Do not introduce any surrogate keys. The ERD may be neatly hand drawn or can be created in a package such as Visio or draw.io. The symbols in the ERD must conform to the symbols used throughout all ERD lectures this semester. Clearly indicate strong and/or weak entities, all identifiers and all relationship names.

Scan, Photograph or take a screenshot of the ERD and place it in the document named

T05P.DOCX

Pass 5b

Convert the ERD that you created in Pass 5a into a Relational Schema.

Copy and paste the relational schema and place it in the document named T05P.DOCX

Pass 5c

Using the Relational Schema you created in Pass 5b write the DDL Create Table statements for each Relation.

Copy and paste the code into the document named T05P.DOCX

Pass 5d

Explain what is meant by Normalisation.

Paste your answer into the document named T05P.DOCX

Pass 5e

The table below is unnormalised. Replace the table by data presented in 1NF.

CustIdName Phone CarRegoMakeModelStartDate ReturnDate125 John Coles 0401112233 1AU8HK

1LM3AB Mazda 3 Hyundai i30 31/08/2020

14/11/2020 7/09/2020

21/11/2020

278 Erin Trump 0466121455 1AU8HK

1KA2CA

1CZ8JK

1AU8HK Mazda 3 Toyota Camry Mazda 3

Mazda 3 12/09/2020

1/10/2020

10/11/2020

26/11/2020 19/09/2020

8/10/2020

12/11/2020

1/12/2020

721 Emma Knox 0423544117 1LM3AB Hyundai i30 10/09/2020 13/09/2020

Paste your answer into the document named T05P.DOCX

Credit Level Tasks

89344519240500

Credit 5a

Youre now going to return to the Petes Pools case (see Pass 5a). The business rules have changed. Read the new narrative:

Clients may now join and leave and/or re-join any swimming centre. As a result you need to record such information.

Adjust your previous ERD. You should not have to create any new entities. Do not introduce any surrogate keys. Indicate all identifiers. The correct identifier is crucial. An incorrect identifier may prohibit customers from re-subscribing to the same magazine again.

Convert the ERD into a Relational Schema.

Create some Test data (in a Table format no need to write out Insert statements) :Create three sample clients and three sample swimming centres.

Create data that allows clients to join, leave and re-join a swimming centre.

Scan, Photograph or take a screenshot of the ERD and place it in the document named

T05C.DOCX

Copy and paste the Relational schema and place it in the document named T05C.DOCX

Copy and paste the Test data and place it in the document named T05C.DOCX

Credit 5b

Read the following narrative:

Tinys Motorcycle Maintenance is a specialist Harley Davidson mobile mechanic service. Tiny travels to the customers (who have an Id and a name) chosen place of service. The customer can have more than one motorcycle that Tiny services. Each motorcycle has a Vehicle Identification Number (VIN), a Make, a Model, a Year of manufacture, and an engine capacity measured in cubic centimetres (CC). Each motorcycle during its life can be owned by many different customers.

When Tiny services a motorcycle he records the date, location of the service, and the Total cost of the service. Tiny also records the tasks he performs (e.g. change engine oil). Each task has a different Hourly Rate.

The spare parts that need replacing are also recorded, together with the price for the part and quantity used (e.g. oil filter and sparkplugs). Many of the tasks performed and the parts required are the same when servicing different motorcycles.

The Total cost for the service is calculated based on cost for each tasks performed (time taken X Hourly Rate), and the cost of parts required (quantity X unit price).

Draw an ERD based on this case study. Fully expand all M:M relationships into M:1 relationships. Introduce surrogate keys where required. The ERD may be neatly hand drawn or can be created in a package such as Visio or draw.io. The symbols in the ERD must conform to the symbols used throughout all ERD lectures this semester. Clearly indicate strong and/or weak entities, all identifiers and all relationship names.

Scan, Photograph or take a screenshot of the ERD and place it in the document namedT05C.DOCX

Credit 5c

Convert the ERD from Credit 5b into a Relational Schema

Copy the Relational Schema into the document named T05C.DOCX

Credit 5d

Using the Relational Schema you created in Credit 5c write the DDL Create Table statements for each Relation.

Copy and paste the code into the document named T05C.DOCX

Credit 5e

Refer back to the pass 5i task. You were given the table below and replaced it with 1NF. Starting with that solution present the data in 3NF.

CustIdName Phone CarRegoMakeModelStartDate ReturnDate125 John Coles 0401112233 1AU8HK

1LM3AB Mazda 3 Hyundai i30 31/08/2020

14/11/2020 7/09/2020

21/11/2020

278 Erin Trump 0466121455 1AU8HK

1KA2CA

1CZ8JK

1AU8HK Mazda 3 Toyota Camry Mazda 3

Mazda 3 12/09/2020

1/10/2020

10/11/2020

26/11/2020 19/09/2020

8/10/2020

12/11/2020

1/12/2020

721 Emma Knox 0423544117 1LM3AB Hyundai i30 10/09/2020 13/09/2020

Paste your answer into the document named T05C.DOCX

Credit 5f

Consider a department store transactions:

Product

ProdIDBrand Description PricePerOneQtyInStockG43546 Gucci Leather mid-heel pump 1050.00 12

Action

ActionIDActionDateTimeAction ProdIDProdQtyProdCost1008 21/01/2021 Purchase G43546 2 2100.00

1026 23/01/2021 Return G43546 -1 1050.00

Write the set of SQL statements to complete each of the transactions in the Action table and update Product table. Make sure you consult lecture slides and lecture recording explaining Transactions concepts.

Explain when the sale or refund operation can go smoothly or go wrong. Explain the terms

transaction committed and transaction roll-back in the context of your discussion.

References

114046019240500

SQL and ERDs

Chapter 4 http://proquest.safaribooksonline.com/book/databases/sql/9780321584069 via Swinburne library

http://www.w3schools.com/sql/https://www.techonthenet.com/sql/Lecture 9 of this unit

Task 5 Credit Submission

Student Number:Student Name:

Credit 5a