Reference no: EM13918945

Assignment - Further Development of an ER Diagram, Database

Implementation and Queries

Objectives:

• To analyse and comprehend a provided ER diagram

• To update the ER diagram using provided specifications

• To create and update normalized relations of the data from the provided ER

• To create and update Database Schema

• To implement a database based on the provided ER diagram

• To write required SQL statements to query the database

Project Specification
AS Adventure Sports (ASAS) is a business that organises outdoor activities e.g. rock climbing, abseiling, mountain biking and surfing for individuals and organisations. You have been asked by Peter, its managing director to design a database to assist them with managing their booking, customer and equipment provider information. The majority of the design has been completed, however there are some changes that they would like made to the system. Using the specifications from the previous assignment, the ER diagram and files provided, update and implement the system using the following information.

Original Specification

ASAS would like to store information regarding their customers. Customers can either be individuals or group customers. Group customers may include corporate, government, school or religious groups. For individual customers they would like to store the contact name, address details (including their location, postal and delivery address details), email address and phone numbers (mobile, home, work). For group customers they would like to store the business name, a contact name, address details (including their location, postal and delivery address details), email address, website URL, and phone numbers (mobile, work). Additionally, for their corporate customers they would like to store the business name, business type (markets in which they operate) and number of employees. For government customers they would also like to store the department name, level of government (local, state, federal) and number of employees. For school groups they would also like to store the type of school (private, public, religious) and number of students and for religious groups, the denomination.

ASAS provides equipment for their outdoor activities. They would like to record both the detail of the equipment available and also record what has been used (and the quantity) for each activity. The equipment provided is quite varied in type and size but may include complete items for example mountain bikes, surfboards or climbing equipment or smaller items for example ropes, carabiners, connectors or harnesses that make up those larger items. Sometimes in the case of group items e.g. climbing gear, the group item is used and in other cases individual pieces are used for the activity. All available pieces of equipment should be recorded in the one table with the relationship between group and individual items included.

The equipment is hired from equipment providers. One piece of equipment may be available for hire from more than one provider and a provider might hire out one or many pieces of equipment. So in addition to recording the equipment available for hire, ASAS would like to record what provider has this equipment available and also keep a register of their providers, including such details as provider identifier, name, contact name, address details (location, postal) and phone numbers (mobile, home, work).

As well as providing equipment for the activity, ASAS provides trainers to assist with the activity and records these details. The trainers are provided by hiring them from trainer providers and an activity may have trainers assigned from one or more trainer providers. ASAS would like to keep a record of these trainer providers and the names and details of the trainers available for hire. For trainer providers the information recorded should include provider identifier, name, contact name, address details (location, postal) and phone numbers (mobile, home, work). For the trainers the information recorded should include trainer provider, identifier, christian name, surname and mobile phone. It is a competitive field so a trainer may be hired out by more than one trainer provider. A trainer may have certain skills that make them more attractive for some events than others. These skills e.g. ‘abseiling', ‘wind surfing', ‘biking', ‘lifesaving' should be recorded separately as desirable skills and then be available for assigning to trainers as details of the trainer are entered/changed.

For each activity ASAS would like to store details such as where the activity is to be held, the customer who they are organising the activity for, the date and time of the activity, where the activity will be held, the type of activity and how many people will be at the activity. As already mentioned, details are also recorded of the equipment used (and quantity) for the activity as well as the trainers used. Each activity will have one trainer who takes on the role of activity manager.

Additional Requirements

ASAS would like to be able to work out the price and direct costs of activities they conduct for clients. The price charged to the customer is based on a percentage markup over the total costs of the activity. The total costs are made up of the equipment used (determined by ASAS based on the type of activity and the numbers attending), the hiring costs of the trainers involved (the number needed is determined by ASAS and advised to the customer) and fixed costs for the activity e.g. entry permits. Other costs of operation such as insurance can be ignored for this assignment.

With respect to the price, a percentage markup is required to be stored. This markup is based on the type of activity. To simplify our example, this markup does not change over time. ASAS though want to have some flexibility to apply a discount percentage to customers for a particular activity(s). This discount is included at ASAS's discretion and may be a reward for returning customers, for example. It is subtracted from the markup percentage rather than applied after the markup calculation.

With respect to costs of equipment used, ASAS negotiates hiring charges for this equipment with each provider and charges remain current for a period of time. The period of time is recorded as a date, which is the commencement date of its application. In the initial costing of the activity for a customer, ASAS estimate the equipment required, choose the provider(s) for that equipment and fill in the anticipated number of items needed. The provider's charge for that item of equipment for that period then becomes the cost of that item of equipment. The total of all estimated equipment used multiplied by its relevant item charge becomes the cost of equipment charged to the customer. When the job has been completed, the actual quantity of each item of equipment hired (which may be different to the estimate) is recorded separately so that a comparison can be made between the estimated and actual quantities used for an activity.

With respect to hiring costs of trainers, they are all paid based on number of hours worked. The number of hours worked are estimated and allocated for each activity and an hourly rate is applied based on the rate set by the trainer provider. The database should allow for an effective date for these rates and thus allow changes over time to the amount paid per hour. ASAS estimate the number of hours of each trainer used and record this. When the job has been completed, the actual hours worked are recorded.

With respect to direct fixed costs for activities, ASAS would like to have a reference table with predetermined categories and another table recording totals of any of these costs against an activity. They would like to be able to record an estimated total cost for that category and then be able to record the actual cost after the activity is completed.

ASAS understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record these.

As an example, imagine that St. Snodgrass School commissioned ASAS to conduct a Year 9 adventure activity - kayaking down the Glenelg River, Victoria. St. Snodgrass will have 200 students taking part in the activity. ASAS charges a 20% markup for Kayaking events but St. Snodgrass is a repeat customer and ASAS decided to include a 5% discount. ASAS estimated the following equipment was needed:
• 50 kayaks @ $100/day = $5000;
• 100 paddles @ $10/day = $1000;
• 50 lifejackets @ $5/day = $250;
• 5 eprbs @ $50/day = $250;
• 2 marquees @ $200/day = $400;
• 5 first-aid kits @ $50/day = $250;
Total Estimated Equipment Hire Costs = $7150

It was estimated that four trainers would be needed and they were selected. The activity was expected to take eight hours. The rate of pay for two of these trainers was $50.00 per hour .The rate for the other two was $60.00 per hour. The total estimated wages costs were calculated to be:
• 2 trainers - ($50 * 8) * 2 = $800
• 2 trainers - ($60 * 8) * 2 = $960
Total Wages Costs = $1760

A cost for permit fees for use of the National Park of $200 for the day were also included.

The price charged to St Snodgrass was therefore $10476.50:
• Total Costs = $7150 + $1760 + $200 = $9110
• Price = $9110 * 1.15 = $10476.50.

When the actual activity took place, all estimates about equipment usage were correct. However, two trainers (paid $50 per hour) needed to work an extra hour each on cleanup tasks. The estimate of permit fees was incorrect due to changes in legislation (a price rise of $20 was included) and a fine of $200 was imposed by the Department of Conservation for damage to some trees at the base site. In the working database, the actual quantities of equipment used and hours worked would be entered, the actual permit fee costs for the job would be entered and a record created for the job to record the fine imposed (with an estimated cost of $0). The actual cost of the job was therefore increased by $320 (($50 * 2 * 1hr) + $20 + $200). The gross margin on the job was therefore $1046.50 ($10476.50 - ($9110 + $320)) or 11.1%.

You will need to incorporate the above details in the updated ER diagram and database and then use that database to generate the reports detailed below.

Required Reports

When defining select queries, you are required to adhere to the following output formatting conventions:

• where applicable, names of people should be printed as GivenNameFamilyName (e.g. John Smith) in a column labelled NAME

• where applicable, addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS

• You are not allowed to create temporary queries to answer these queries

You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an "unusual" approach. The following reports are required (2 marks each):

1. A list of the names and phone numbers (including description of phone type) of all customers in alphabetical order by contact name. You should think about what happens when a phone

2. A list of all school customers and their contact details (contact name, email and all phone details).

3. List all provider equipment details where the equipment description begins with a K(either upper case or lower case).

4. A list of all customers (id and name) together with their activity(s) including date, time and type of the activity and the percentage markup less discount percentage for the activity.

5. A list of the contact details (names, postal address and phone number) of all of the providers who supplied equipment for more than one activity.

6. List each activity (activityID is sufficient) and all of the details of trainers who have worked on the activity, including the trainer provider id and name. Provide details sorted by trainer provider.

7. Display a list of equipment(id and description) that has had a unit charge update in the last 3 months.

8. Provide a list of all activities, including customer name, activity ID, date, time, location for which actual quantities used have been greater than allocated quantities. Include details of the equipment to which this applies as well as the cost difference.

9. Display the itemised actual equipment cost of all items for activities held between January and June 2013 (based on activity date).

10. Increase by 2% the markup percentage of all types of activities.

11. List the equipment with the highest quantity of actual usage in the database.

12. Provide a list of all activities, including customer name, activity id, date, time, location for which actual hours worked have been greater than allocated hours. Include details of the cost difference and total change in cost for the activity for the difference in hours.

13. Provide a list of all trainers (and their details), who have worked as an activity manager. Include the number of activities they have managed.

14. Using the MIN function, provide the details of the activity with the lowest difference between the estimated and actual hired equipment costs.

15. Using a sub-query and the NOT EXISTS statement, list all distinct equipment records (equipment ID and description) where the equipment has been used in an activity for which there are no other activities of that type.

Implementation of the Database

Create, insert and query statements

You are required to use SQL statements to create the database, the tables and insert sufficient data to test the required reports. You will be given two files to commence with (reflecting the database at the end of Assignment1)and these must be updated to include the changes detailed above.

1. Create a text file called YourStudentId-Create.sql (format xxxxxxx-Create.sql) for example 2225991-Create.sql that will:

a. Create a database called ASASYourStudentID (egABC30011111)

b. Creates all of the required tables including primary keys, foreign keys and their relationships.

2. Create a text file called YourStudentId-Insert.sql (format xxxxxxx-Insert.sql) for example 2225991- Insert.sql that will:

a. Insert sufficient data into each table you have created to test the queries. Sample data has been provided, however, you will need to include further data to test all of the required reports.

b. You are required to include your name as one of the customers; you can provide fake details for the address but you are required to include your full name and use your student number as one of the phone numbers for this customer.

3. Create a text file called YourStudentId-Queries.sql (format xxxxxxx-Queries.sql - for example 2225991-Queries.sql) that contains all of the queries to display the required reports.