INF10002/INFf60009 database analysis and design

Tasks 1

Sponge Cake Shopping Tours

Sponge Cake Tours is a small organisation than run shopping tours throughout Melbourne. They have been running for about 3 years. The organisations plans to increase marketing and create more tours and also expand into regional Victoria as well as other Australian capital cities.

Sponge Cake currently store all of their data in a spreadsheet (a sample is provided on Blackboard).

To assist the organisation, it is believed they need a database solution developed for them. Sponge Cake would like you to create an ERD, Relational Schema and Sample Database Tables for them.

Other teams of students will develop a web interface to the database, a rich user experience, analytical data tools and so on.

Part 1a. (worth up to 4 marks out of 20)

Sponge Cake conduct a number of different tours to shopping precincts of Melbourne. Each Tour has an ID and a description. A tour (such as the West Tour) may run many times in a year. Each time it is run it is called an Event. The West Tour currently runs an event once a month (with an additional event or two closer to Christmas period). Each event has a fee. Fees have usually increased by around 10% each year

Sponge Cake have a number of customers. Each customer has a name, id and gender.

Sponge Cake customers make bookings for tour events. Payment is made by credit card. (you do not have to worry about recording credit card details etc.). The date that the a booking is made is also recorded.

• Create an ERD based on these details.
• Convert the ERD into a Relational Schema
• The ERD must be drawn using a drawing package such as Visio. The symbols used in the ERD must match those used in lectures this semester.
• Do not introduce any surrogate keys into your solution.

Part1b. (worth up to 4 marks out of 20)

• Build a database using MS Access or Oracle via ISQL Jr and SQL statements.
• Add the sample data (from the spreadsheet into the tables. Only use data from the areas labelled Customers, Tour Event, Tours and Bookings. Ignore the other data).

Note: Your database solution must use a single column primary key in each table. This may require you to utilize surrogate keys.

Additional Data:

You must also add a yourself as a customer. Use your name and student id.

Part 1c. (worth up to 2 marks out of 20)

Run queries that list the data in each of the tables

Finally

Copy and Paste a screenshot your ERD into the file named DTASK2.DOCX

Copy and Paste your Relational Schema into the file named DTASK2.DOCX X

Copy and Paste the result set of each of the queries into the file named DTASK2.DOCX

Part 2a. (worth up to 4 marks out of 20)

While customers attend a tour event and visit shops & warehouses, the customers often purchase products. Sponge Cake want to record what products were purchased by customers.

The example spreadsheet shows the quantities of each product that were purchased by each customer on a tour event. Some customers do not purchase any products, some customers purchase one product while other customers order many products while attending the tour event.

Each of the products belongs to a single product category.

Recording these details allows Sponge Cake to determine which category of products are most sought after, which tours are most popular, how much each customer is spending on each tour and so on.

• Copy the above ERD and make changes to include product purchase information.
• Reflect these changes in the Relational Schema.
• Do not introduce any surrogate keys into your solution.

Part 2b. (worth up to4 marks out of 20)  Make a Copy the previous database.

• Implement the changes described in your ERD and relational schema
• Add the sample data (from the spreadsheet into the tables. Use data from the areas labelled Products, Categories, Purchases).

Note: Your database solution must use a single column primary key in each table. This may require you to utilize surrogate keys.

Additional Data:

Add additional data to the database so that the customer with your details makes tour event bookings and makes purchases.

Part 2c. (worth up to 2 marks out of 20)

Run build and execute queries that show the following:

• How much money has been spent by every Sponge Cake customer.
• The total sales of each product.
• The total sales of each Sponge Cake tour event.  The total sales by product category.