Subject Code & title : 6BUS1257 Databases For Strategic Decision Making
Weighting : 60% individual
Learning Outcomes: Knowledge and Understanding tested in this assignment:
principles to aid strategic decision making Evaluate the importance of data and analytics within an organisation and its different uses, inc. strategic decision making.
Learning Outcomes: Skills and Attributes tested in this assignment:
Develop, document and test a relational database as designed using data modelling techniques Use structured query language (SQL) to define the tables and develop queries to retrieve data from the database to aid with strategic decision making within organisations.
6BUS1257 Databases For Strategic Decision Making Assignment – Hertfordshire University UK.

Detailed Brief for Individual/Group Assessment :
Assignment Title: Sunshine holidays database development
Description of the assignment :
Sunshine Holiday Apartment rentals are a company that specialise in holiday apartment rentals in Europe. They originally started with a few apartments in Spain and have expanded to now have over 50 apartments for holiday lets around Europe. They have expanded rapidly over the last year and are finding it difficult to process bookings using their manual systems.

You have been called in to help with the design and implementation of a database management system for their bookings and the design and implementation of queries that they would like to run regularly.

The development team have identified the following database tables will be required
 Customer – to capture required customer details
 Booking – to capture details of an individual booking
 Property – to store all the relevant property information.
 Agent – to capture agent details who maintain the properties
 Feedback – to capture rating and feedback from renters

The tables and base attributes needed in the database for this project have been defined in Appendix A for you. The list is not exhaustive and has been limited to fulfil the business use of this data.

Sunshine holidays want to produce management information on properties, number of bookings and customers to help to increase the occupancy of holiday properties. They also want to use the data strategically to establish the best regions to expand their property holdings in.

You are to implement this database for Sunshine Holidays. You must use SQL to create the tables required. (Need screen shot evidence of the code written in SQL within Access and the successful creation of the tables). Ensure you identify all relevant keys as part of your table definition. This will include the definition of the primary and foreign keys where needed.

You will then need to check and recreate any relationships between the tables in Access. Appropriate data validation rules would need to be defined and implemented on attributes where needed. You are to enter sufficient data to ensure that the system can be fully tested in a realistic way. Ensure that the data entered allows all your validation to be tested. Appendix A has details of the entity relationship diagram and the tables and entities required. Appendix B has details of how the business operate.

6BUS1257 Databases For Strategic Decision Making Assignment – Hertfordshire University UK.

You will need to complete the following tasks for this assignment
a) Create a data dictionary for the tables and their attributes defining any validation rules you feel are required
b) Use SQL (From within Access) to create the tables. This will be done through the query function and SQL view and not using the front end of Access. No marks will be awarded if the front end is used to do this
c) Define appropriate validation rules and user friendly error messages, drop down lists and input masks as specified in the data dictionary. Create relationships between the entities making sure you enforce referential integrity to keep the data accurate so that any management reports contain correct information to base business decisions on. Ensure you identify all relevant key fields.
d) Enter data to populate the tables in Access. You need to think how much data would be suitable to fully test the database realistically. Minimum number of records identified below
o Customer – 20 records
o Booking – 30 records
o Property – 30 records
o Agent – 10 records
o Feedback – 20 records
e) Create appropriate SQL queries in Access (Using SQL view) for the queries below. Ensure screenshots of the SQL code is included within your submitted document.

1. List of customers sorted by postcode and surname so that Sunshine Holiday Apartment Rentals have an indication of where their customer base is
2. List of bookings per customer chosen by Customer Id.
3. Sunshine Holiday Apartment Rentals currently have a promotion to try and gain new bookings so would like a report that they can run for any input time period (based on start and end dates that they enter) to display all new bookings in that time period
4.Total revenue for each property, sorted by property id, with the properties with the most income

Scenario :
Holiday Apartment rentals
Sunshine Holiday Apartment rentals are a company that specialise in holiday apartment rentals in Europe. They originally started with a few apartments in Spain and have expanded to now have over 50 apartments for holiday lets around Europe. They have expanded rapidly over the last year and are finding it difficult to process bookings using
their manual systems. You have been called in to analyse their processes with a view of designing a new computerised system for them that will make their booking procedure easier to manage.

Sunshine Holiday apartment rentals advertise their properties through a brochure ware website which allows potential customers to generate an enquiry for an apartment. An email is generated for each enquiry which is responded to with a quote for the rental property. This is completed by looking at the availability for the apartment and also the rental amount for the weeks in question in the paper based files that are maintained for each property.

Rental amount is calculated based on a weekly rate. Discounts of 15% are given for a late booking (booked within 4 weeks of travel date) and a discount of 15% is given for bookings which are for 3 weeks or longer. Both discounts can be given concurrently for late bookings which are of a longer duration. Rental rates for apartments vary from £160 per week in low season to £320 in peak times.

If the customer wants to go ahead with the booking and confirm this by email, the company send out a copy of their booking form and details of ways to pay the deposit amount (or the full amount if it is a late booking). If the customer has any special requirements such as hiring out highchairs or cots, then information about these is also provided on the booking form by the customer. On receipt of the completed booking form and deposit amount,Sunshine Holiday apartment rentals confirm the booking to the customer and that the deposit has been received. The paper booking held for the customer is also updated to show that the deposit has been paid and the form moved into a separate folder that holds details of all rentals where the deposit has been paid. These are held in order of when the final payment request will need to be sent.

The company then forward the booking form with details of the property to the relevant agent. They work with a number of agents, each of which looks after several properties in each local area. The agent confirms receipt of the booking form and also separately confirms any special requests that might have been on the booking (cots, highchairs). Occasionally customers require airport transfers in which case the agent organises transfers for them.

The final payment for the rental is due 4 weeks before travel and Sunshine Holiday apartment rentals email the customer with a reminder to pay the remaining amount and the booking is updated to show that this email has been sent out and kept in the same folder until the payment is received. On receiving the remaining payment, the booking is updated and marked as fully paid and moved to a separate folder where all fully paid booking details are stored. A confirmation email is sent to the customer along with details of the agent, the meeting point and directions to the apartment.

On completion of the holiday rental, customers sometimes send in feedback on the rental which is gratefully received.

6BUS1257 Databases For Strategic Decision Making Assignment – Hertfordshire University UK.

Everyone gets involved in dealing with customers and paperwork depending on who is available at that moment. Dealing with new enquiries and progressing existing bookings has become a nightmare and is denting the companies’ reputation for customer service. Taking on extra staff is a problem because the present premises cannot accommodate
sufficient extra people, so a partial or total computer-based solution is preferred.

They would also like to be able to analysis bookings by property so that they can establish which the popular apartments are, and which could benefit from special offers or as featured properties on their website. It is hoped that by better access to data, it will become easy to analyse data about customers, bookings and properties and to provide
better service to customers.

ORDER This 6BUS1257 Databases For Strategic Decision Making Assignment NOW And Get Instant Discount

 

Read More :-

BM633 Strategic Agility Assignment – Buckinghamshire New University UK.