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

Appendix A
Scenario
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 analyse the processes with a view of designing a computerised system for the company which will make the booking procedure easier to manage.

Sunshine Holiday Apartment Rentals advertises its properties through a brochureware website which allows potential customers to generate an enquiry by email for an apartment. The email is responded to with a quote for the rental property. This is created by looking at the availability for the apartment and also the rental charge for the weeks in question in the paper based files which are maintained for each property.

Rental charge is calculated based on a weekly rate. Discounts of 15% are given for a late booking (booked within four weeks of travel date) and a discount of 15% is given for bookings which are for three weeks or longer. Both discounts can be given 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 confirms this by email, the company sends out a copy of a booking form and details of ways to pay the deposit (or the full amount if it is a late booking). If the customer has any special requirements such as hiring high chairs 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 Sunshine Holiday Apartment Rentals confirms the booking to the customer and confirms 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 which holds details of all rentals where the deposit has been paid. These are held in order of the date when the final payment request is due to be sent.

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

The final payment for the rental is due four weeks before travel and Sunshine Holiday Apartment Rentals emails the customer with a reminder to pay the remaining amount, the booking is updated to show that this email has been sent and is 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.

After the holiday customers sometimes send in feedback on the apartment which is gratefully received.

APPENDIX 1 – MARKING AND GRADING GRID Information Management ASSIGNMENT 2013-14

Module Code:____ _______________ Lecturer:__________________________________ `Student: ________________________________________

Initial Design – forms and reports SQL Forms & reports Front End Testing Critical Evaluation
Task details

Design tasks.
ERD and Data Dictionary, friendly forms for maintaining all entities, well formatted customised reports   SQL requirements.
Create tables, relationships and keys.   Create queries   Friendly forms for maintaining . all entities. Easy to use reports.   Database functions through a front end and has complete navigation   Test log with evidence of all test results   Critical evaluation of project plan and submission of actual project plan.
100 marks         20 Marks
25 Marks       15 Marks            10 Marks          15 Marks        15 marks
80-100

Outstanding Outstanding… Correctly drawn and fully labelled ERD showing relationships including link phrases between entities
Data dictionary complete with no errors and extensive validation rules. Sensible
User friendly forms for maintain all entities. Consistent design across all forms including at least one sub form. All forms showing navigation buttons replacing usual navigational tools, an exit or return to main menu button present and standard font sizes, font types, colours etc. used.
Well formatted customised reports displaying all data required. All required reports present plus extra two reports which have been justified as relevant to the business. Extra report(s)(other than the reports above) included with business justification. Consistency of House style, font type, font size, colour etc must match form design.

No errors Outstanding…
Five entities containing all correct SQL statements for creating tables, setting up relationships and identifying keys.

Correct SQL for all queries.

No errors.
Outstanding…
Five entities containing a range of test data implemented as defined in Data Dictionary demonstrating a variety of error prevention techniques e.g. drop down boxes, range tests etc.

Outstanding…
System developed has full navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Main menu displays on clicking the database. Consistent professional house style developed and used throughout

No errors. A complete testing log showing all required tests with screen shots of results. All possible error messages produced during data entry must be tested.

No errors.
Outstanding…
Articulate and fluent academic writing style. No grammatical / spelling errors. Strict adherence to word count. Highly developed critical evaluation with outstanding business insight and application into the benefits of project planning. A good realistic actual project plan is submitted. Use of some references
.
No errors.
70-79

Excellent Excellent…
correctly drawn and fully labelled ERD showing relationships including link phrases between entities
Data dictionary complete and extensive validation rules.
User friendly forms for maintain all entities. Consistent design across all forms including at least one sub form. All forms showing navigation buttons replacing usual navigational tools, an exit or return to main menu button present and standard font sizes, font types, colours etc. used.
Well formatted customised reports displaying all data required. All required reports present plus extra two reports which have been justified as relevant to the business. Extra report(s) (other than the reports above) included with business justification the included with justification. Consistency of House style, font type, font size, colour etc must match form design.

Only a minor error. Outstanding…
Five entities containing all correct SQL statements for creating tables, setting up relationships and identifying keys.

Correct SQL for all queries.

No errors.
Excellent…
Five entities containing a range of test data that would allow a complete test of all areas of the system as defined in Data Dictionary demonstrating a variety of error prevention techniques e.g. drop down boxes, range tests etc.

Excellent…
System developed has full navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Main menu displays on clicking the database. Consistent professional house style developed and used throughout

Only a minor error. A complete testing log showing all required tests with screen shots of results. All possible error messages produced during data entry must be tested.

Only a minor error.
Excellent…
Articulate and fluent academic writing style. No grammatical / spelling errors. Strict adherence to word count. Highly developed critical evaluation with outstanding business insight and application into the benefits of project planning. A good realistic actual project plan is submitted. Use of some references
.
.
Only a minor error.
60-69

Very Good Very good…
correctly drawn and fully labelled ERD showing relationships including link phrases between entities
Data dictionary complete and extensive validation rules.
User friendly forms for maintain all entities. Consistent design across all forms including at least one sub form. All forms showing navigation buttons replacing usual navigational tools, an exit or return to main menu button present and standard font sizes, font types, colours etc. used.
User friendly forms for maintain all entities. Consistent design across all forms including at least one sub form. All forms showing navigation buttons replacing usual navigational tools, an exit or return to main menu button present and standard font sizes, font types, colours etc. used.

minimal Very good …
Five entities containing all correct SQL statements for creating tables, setting up relationships and identifying keys.

Correct SQL for all queries.

Minimal errors.
Very good…
Five entities containing a range of test data as defined in Data Dictionary demonstrating a variety of error prevention techniques e.g. drop down boxes, range tests etc.

Very good…
System developed has full navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Main menu displays on clicking the database. Consistent professional house style developed and used throughout

Minimal errors. A complete testing log showing all required tests with screen shots of results. All possible error messages produced during data entry must be tested.

Minimal errors Very good …
Articulate and fluent academic writing style. No grammatical / spelling errors. Strict adherence to word count. Highly developed critical evaluation with outstanding business insight and application into the benefits of project planning. A good realistic actual project plan is submitted. Use of some references
.

Minimal errors.
50-59

Good Good…
correctly drawn and fully labelled ERD showing relationships
Data dictionary complete and with no major omissions and most required validation rules.
User friendly forms for maintain all entities. Consistent design across all forms including at least one sub form. All forms showing some navigation buttons, an exit or return to main menu button present and standard font sizes, font types, colours etc. used.
Well formatted customised reports displaying all data required. All required reports present plus extra two reports which have been justified as relevant to the business. Consistency of House style, font type, font size, colour etc must match form design.

Only a few errors or omissions. Good…
Five entities containing all correct SQL statements for creating tables, setting up relationships and identifying keys.

Correct SQL for all queries.

Only a few errors or omissions.
Good…
Five entities containing a range of test data implemented as defined in Data Dictionary demonstrating variety of error prevention techniques e.g. drop down boxes, range tests etc.

Good…
System developed has full navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Main menu displays on clicking the database. Consistent professional house style developed and used throughout

Only a few errors or omissions. A complete testing log showing all required tests with screen shots of results.

Only a few errors or omissions.
Good…
Articulate and fluent academic writing style in the main. No grammatical / spelling errors. Strict adherence to word count. Well developed critical evaluation with outstanding business insight and application into the benefits of project planning. A good realistic actual project plan is submitted. Use of some references
.

Only a few errors or omissions.
40-49

Satisfactory Satisfactory…
Satisfactory ERD, Data dictionary complete for at least four entities.
Forms for maintaining at least four of the entities. Satisfactory form design.
A minimum of at least four reports showing all data required. Satisfactory report design.

Some errors. Satisfactory…
Four entities containing all correct SQL statements for creating tables, setting up relationships and identifying keys.

Correct SQL for at least four queries.

Some errors.
Satisfactory…
Four entities meeting minimum data requirements.

Satisfactory…
System developed has full navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Main menu displays on clicking the database. Consistent professional house style developed and used throughout

Some errors. A testing log showing a majority of required tests.

Some errors. Satisfactory…
Attempt at Articulate and fluent academic writing style. some grammatical / spelling errors. Strict adherence to word count. Well developed critical evaluation with outstanding business insight and application into the benefits of project planning. A realistic actual project plan is submitted. Use of a couple of references
.

Some errors.
30-39

Marginal Fail   Weak…
ERD and Data Dictionary attempted but with a good number or errors or omissions..
Evidence of at least 2 forms.
At least two reports present showing relevant data.
Weak..
Some entities present but less than four containing SQL statements for creating tables. Attempts at SQL for queries    Weak…
Some evidence of entering data.
Minimal evidence of testing.   Weak…….
System developed has some navigation and is easy to use. Includes back buttons and exit buttons where appropriate. Consistent style   Inadequate test log   Weak…
Minimal evidence of a critical evaluation.
15 – 29

Clear Fail Inadequate…
Very poor attempt at ERD and / or Data Dictionary.
Only basic form(s)created using relevant software wizard.
Minimal effort at producing reports.

Must see ASU Inadequate…
At least one entity present using SQL statements for creating tables.

Must see ASU   Inadequate…
Minimal test log or omitted.   Inadequate….
System developed has navigation can be used with some back and exit buttons. Some attempt to a house style
Inadequate test log   Inadequate…
Minimal critical evaluation.
1 – 14

Little or Nothing of merit Nothing of merit… No or little attempt at ERD and / or Data Dictionary.
No or little attempt at creating forms.
No or little attempt at creating reports

Must see ASU Nothing of merit…
No or little attempt at creating entities.

Must see ASU   Nothing of merit…
No or little attempt at creating a test log.   Nothing of merit….
Minimal front end   Nothing of merit…
No evidence of appropriate business application & insight.
No critical evaluation completed
This form is used by staff & students to provide feedback to assist students’ future work.