Task5PassandCredit

Overview

• 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(usuallybycreating screen grabs)andsubmit
• Togetstarted,downloadthefilesDOCXandT05C.DOCXfromCanvas
• Pleasenote,ALLtasksineachsectionMUSTbecompletedforyoutosuccessfullycompletethePass and/or theCredit
• Whencomplete,generatethefilesPDFandT05C.PDF
• FinallylogintoDoubtfireandsubmitbothfilesintotheappropriateweekly

PassLevelTasks

Pass5a

Readthefollowingnarrative:

Petes Pools
have a number of swimming centres. Each centre has a name, establishmentdate, and a description. Clients(who have an ID and name) can join one or more of theseswimmingcentres.Asaclientjoinsacentre,thedatethattheclientjoinedistoberecorded.

• 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 beneatlyhand drawn or can be created in apackage such as Visio or draw.io. The symbols in the ERD must conform to the symbols usedthroughout all ERD lectures this semester. Clearly indicate strong and/or weak entities, allidentifiers andall relationship
• Scan,PhotographortakeascreenshotoftheERDandplaceitinthedocumentnamed

T05P.DOCX

Pass5b

• ConverttheERDthat you created in Pass 5a intoaRelational
• CopyandpastetherelationalschemaandplaceitinthedocumentnamedDOCX

Pass5c

• Using the Relational Schema you created in Pass 5b write the DDL Create Table statements for each Relation.
• Copy andpaste thecodeinto the document namedDOCX

Pass5d

• Explainwhatismeantby

Paste your answerintothedocumentnamedT05P.DOCX

Pass5e

• ThetablebelowisReplacethetable bydatapresentedin1NF.

• PasteyouranswerintothedocumentnamedDOCX

CreditLevelTasks

Credit5a

Youre now going to return to thePetes Poolscase (see Pass 5a). The business rules havechanged.Read thenewnarrative:

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

• Adjust your previous ERD. You should not have to create any new entities. Do not introduce anysurrogate keys. Indicate all identifiers. The correct identifier is crucial. An incorrect identifier mayprohibitcustomers fromre-subscribing tothesamemagazine
• ConverttheERDintoaRelational
• CreatesomeTestdata(inaTableformat noneedtowriteoutInsertstatements):

• Createthreesampleclientsandthreesampleswimming
• Createdatathatallowsclientstojoin,leaveandre-joinaswimming
  • Scan,PhotographortakeascreenshotoftheERDandplaceitinthedocumentnamed

T05C.DOCX

• CopyandpastetheRelationalschemaandplaceitinthedocumentnamedDOCX
• CopyandpastetheTestdataandplaceitinthedocumentnamedDOCX
  
  

Credit5b

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 beneatlyhand drawn or can be created in apackage such as Visio or draw.io. The symbols in the ERD must conform to the symbols usedthroughout all ERD lectures this semester. Clearly indicate strong and/or weak entities, allidentifiers andall relationship
• Scan,PhotographortakeascreenshotoftheERDandplaceitinthedocumentnamed
  DOCX
  
  

Credit5c

• Convert the ERD from Credit 5b into a Relational Schema
• Copy theRelational Schemainto the document namedDOCX
  
  

Credit5d

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

• CopyandpastethecodeintothedocumentnamedDOCX
  
  

Credit5e

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

PasteyouranswerintothedocumentnamedT05C.DOCX

Credit5f

• Consideradepartmentstoretransactions:

Product

Action

• WritethesetofSQLstatementstocompleteeachofthetransactionsintheActiontableandupdate Product table. Make sure you consult lecture slides and lecture recording explainingTransactions concepts.
• ExplainwhenthesaleorrefundoperationcangosmoothlyorgoExplaintheterms

transactioncommittedandtransactionroll-backinthecontextofyourdiscussion.

References

SQLandERDs

• Chapter 4http://proquest.safaribooksonline.com/book/databases/sql/9780321584069viaSwinburne library
• http://www.w3schools.com/sql/
• https://www.techonthenet.com/sql/
• Lecture9 of this unit