Create ERD and use Microsoft Access to create Data Model Transformation

  1. Part A — Entity-Relationship Diagrams

(50 marks) (50 marks)

Draw an Entity-Relationship Diagram (ERD) for the data storage requirements described in the case study below.

The case study will contain conflicting, ambiguous, vague, and missing information. Seek clarification and/or make reasonable assumptions to complete the requirements: your assumptions and clarifications need to support all details of the ERD that are not already provided by the case study. Document any assumptions you make and clarifications you obtain in an appendix named‘Assumptions and Clarifications’. There are two options:

  •  Seek clarification about the case study by searching the forums. If your question has not already been discussed, please post your question.
  •  Make reasonable assumptions about missing or ambiguous information in the case study. However, marks may be lost for unreasonable assumptions. For example, it is unreasonable to assume that people only ever drive around with one passenger in their car.Notes:
    • Your ERDs should be neat, clear and easy to read.
    • Please follow the modelling standard used in the text book and lecture slides.The Case StudyWe are creating a web service/app that is a rideshare service to chauffeur kids to events such as school or extra-curricular activities. You will need to record information about the cars, the drivers, the kids and the trips that they make. When one person decides to drive somebody else’s kids, they both need to give permission, and these permissions need to be recorded. Please specify the data storage requirements.

COIT11237 – Assignment 2 Specifications Page: 1.

(50 marks)

Number

Requirement

1

Car trips need to be recorded. Trips allow people to attend events at the trip stops.

  •  Example: Jamie taxis his kid Ben from home to Freshwater school each morning at7:50am. Jamie then ends his trip by returning home.
  •  Example: Karen drives her child Thomas from her home to Freshwater school eachmorning at 7:50am. Karen then proceeds to her work.
  •  Make an assumption about how many locations a trip can consist of.

2

The system records the information used to determine which trips are optional “taxi trips”. That is, record who needs to attend which stops.
 Example: Jamie does not need to be the person who taxis Ben to school. In fact,

Jamie would be happy if somebody else could take Ben. However, Karen feels compelled to drop Thomas off each morning.

3

If an optional trip overlaps with another trip, and there is room in the car, then the system will offer the (optional) taxi trip to the other driver. Do not record the offers made, just the acceptance of the offers. You do not need to figure out which trips are optional. You only need to record the information that will be needed to determine which trips are optional taxi trips.

  •  Example: Karen lives near Jamie, and Karen’s son, Thomas, goes to the same school as Jamie’s kids. Since Jamie’s trip is an optional taxi trip, and Karen has room, then Karen is asked whether she would be willing to take Jamie’s kids. The system records Karen’s acceptance of the offer.
  •  Make an assumption about how to calculate “room in the car”.

4

Two stops overlap another if their locations are close and both parties are at their stop within 10 minutes of each other. Record enough information so that we can determine which stops overlap.
 The specifications of locations are not crucial at this stage. Make your own

assumption about what ‘close’ means.

5

Permission is sought from the (original) driver when their trip is offered to another driver. Do not record the offers made, just the acceptance of permission.
 Example: Jamie gives his permission for Karen to take Ben.

6

Once we have permission from both the original and new drivers, the system will assign the trip to the new driver.
 Example: since Jamie and Karen have accepted the offer for Karen to take Jamie’s

kids, the system will record that Karen’s trip is a substitute for Jamie’s trip.

How You Are Marked for the Entity-Relationship Diagram (ERD)

You need to ensure you pay attention to the following aspects when developing your ERD:

  • Correct entities are identified
  • Correct unique identifiers
  • Appropriate attributes for each entity
  • Appropriate notation
  • Correct relationships and cardinalities (min/ max cardinalities)
  • Assumptions/ clarifications support all details provided in the ERD

(6 marks) (6 marks) (8 marks) (10 marks) (10 marks) (10 marks)

COIT11237 – Assignment 2 Specifications

Page: 2.

Part B — Data Model Transformation (50 marks)

Implement the ER model you developed in Part A as a relational database design.
1. Transform your ER model into a relational database design. (25 marks)

Note: Document the tables in your design using the Relation Notation shown below.Customer(CID, Name, Street, Suburb, State, PostCode, DiscountPercent, Email)

SalesOrder(SOID, CID, Number, FullPrice, Discount, FinalPrice, TotalPaid, Status) foreign key (CID) references Customer(CID)

Provide a brief explanation about cascades for each foreign key.

How You Are Marked for the relational database design

Please pay attention to the following aspects when developing your ERD:

page3image3702672page3image3699136

  • Correct tables and primary keys
  • Correct foreign keys
  • Correct cascades
  • Documentation of cascades

2. Implement enough of the database to demonstrate the following. You do not need to implement the entire database.

  1. primary key constraints: provide five examples
  2. foreign key constraints: provide five examples
  3. cascading delete: provide one example
  4. validation rule with a validation text: provide one example
  5. table constraint (a constraint that compares more than one column): provide oneexample. You will need to check the SQL Server Compatibility (ANSI 92) for yourdatabase before you write an SQL alter table query. (5 marks)

You need to include a brief documentation (in a word document) that highlights where the above items are demonstrated in your database, e.g. where the primary key constraints are. Marks may be lost if your documentation is missing, unclear or not concise.

How and what to submit

Submit your assignment on the unit web site. Please submit two files:

  1. A Microsoft Word document containing:
    • Your Entity Relationship Diagram (ERD) for Part A
    • Your reasonable assumptions/points of clarification for Part A (if you have any)
    • Documentation for Part B.
  2. A Microsoft Access file containing the tables and features you have implemented to support the requirements for Part B.

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more
error: Content is protected !!