Posted: September 13th, 2017

Business with Information Systems

Business with Information Systems

Order Description

Evaluate the effectiveness of the project plan you created for the first assignment, where there were major discrepancies with the times allocated or sequence of events. Submit an updated project plan which reflects the actual timescales and activities conducted. The use of citations may be beneficial to back up what you are saying.

Assignment – Fashion Flair

Sunshine Holiday Apartment Rentals is a company which specialises in holiday apartment rentals in Europe. It started with a few apartments in Spain but has expanded and now has over fifty apartments for holiday lets around Europe.  It has expanded rapidly over the last year and is finding it difficult to process bookings using its 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.

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 very difficult and is having a negative effect on the company’s reputation. Taking on extra staff is not possible because the premises cannot accommodate sufficient extra people so a partial or total computer-based solution is preferred.

Managers would also like to be able to analyse 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 computerising this system it will become easy to analyse data about customers, bookings and properties and to provide better service to customers.

Task1

Evaluate the ERD and Normalised data developed earlier in the process of the development. Create  a fully labelled entity relationship diagram (ERD) and a data dictionary for each entity showing, field name, types, sizes, key fields and validation and verification rules where appropriate. Make sure any key and foreign keys are identified. The Entity relationship diagram should contain the following entities – Customer, Booking, Property, Agent and Feedback as a minimum. Please list any assumptions that you have made.

Task 2

Create the Database structure defined in Task 1.  You must use SQL to create the tables required. (Need screen shot evidence of the code and the successful creation of the tables) Ensure you identify all relevant keys and define the relationships between the tables using SQL.  You are to enter sufficient data to ensure that the system can be fully tested in a realistic way and also to produce the reports and forms as defined below. Ensure that the data entered allows all your validation to be tested and answer the business questions below in a meaningful way.

a)    Use SQL to create the tables

b)    Import the tables into Access and 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.

c)    Sketch on paper the input forms and output reports defined below in preparation for later implementation.  Evaluate the design and re-design as needed. Ensure that consistent layouts are used throughout. You need to consider what ‘house style’ is suitable including font type, size, colour and so on.

d)    Create the forms below according to your specification and enter sufficient data to test the system.   Test your validation rules as you go along to ensure they are working and capture screenshots of your error messages and provide these as part of your testing.

e)    Produce reports defined below as required and according to your design specification.  Use SQL to produce the queries (this will need to be SQL generated within the query design that should have screen shot evidence of the code).

f)    Develop a user friendly interface to your system to allow the operator to access and navigate around the functions of your database

g)    Create a test plan including a test log as evidence that it has been fully tested. Provide a selection of screenshots as evidence of testing, especially for validation rules.
Forms

You will need to have forms that allow data to be added, modified and deleted from all entities ensuring that the use of sub-forms is demonstrated at least once. Ensure consistent navigation buttons are implemented across all the forms.

Ensure that all forms and reports are accessible through a front end menu which should be displayed on opening the database

Reports

You will need the following printed reports

•    List of customers sorted by postcode and surname so that Sunshine Holiday Apartment Rentals  have an indication of where their customer base is
•    List of bookings per customer chosen by Customer Id.
•    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
•    Total revenue for each property, sorted by property id, with the properties with the most income listed first
•    List of all properties looked after by an agent selected by agent id
•    A report to show all properties based on their popularity with the most popular properties displayed first, within each country. This report is required to establish whether there are opportunities to expand the property range and what types of  properties are in demand.
•    Sunshine Holiday Apartment Rentals are looking to establish the frequency at which properties are booked and the average rental amount so that they can determine which their popular properties are. They would like to be able to run a report which provides the number of bookings each property has had and its overall average rental amount. The report will require the property id, name and location to be displayed with the total number of bookings and the average rental amount.  The report should display the highest average rental amount first.
•    Create 2 further  reports for the application that will help Sunshine Holiday Apartment Rentals  to improve their sales and help with their decision making process, justifying your choices for the reports and how they would help Sunshine Holiday Apartment Rentals

Task 3 500 -600 words

Evaluate the effectiveness of the project plan you created for the first assignment, where there were major discrepancies with the times allocated or sequence of events. Submit an updated project plan which reflects the actual timescales and activities conducted. The use of citations may be beneficial to back up what you are saying.