Analysis

During the analysis stage of developing a database you need to identify the following requirements:

• End-user requirements

• Functional requirements

The end users are the people who are going to be using the database. The end user requirements are the tasks they expect to do whilst using the database. They will be written from the perspective of the user.

For example:

• Performing a range of searches on a database.

• Sorting a database in a particular order.

• Using the database to perform calculations.

Functional requirements are processes and activities that the system has to perform in order to meet the end user requirements. It will also need to reference the information that the system has to contain to carry out its functions.

For example:

• The pieces of information that will be stored

• Uses simple/complex queries, simple/complex sorts, allowing calculations, using aggregate functions (MIN, MAX, COUNT, SUM, AVG) .

Why are they needed?

The requirements specify the design of the database., they identify the features to be implemented. In a database context you can think of a functional requirement as anything that will require a SQL operation to be carried out.  This allows the developers to evaluate whether the system is fit for purpose after development is complete.

A travel agency wants to store details of bookings in Scottish holiday resorts. The relational database will allow travel agents to view details of hotels and make bookings for customers.

Four entities are:

1. Hotel (used to store details of hotels in each resort)

2. Resort (used to store details of Scottish holiday resorts)

3. Customer (used to store customers details)

4. Booking (store details of hotel bookings)

The following are comments made by staff of what they want the database to do:

From the comments the following end-user requirements can be determined:

Travel agency staff should be able to perform searches to display:

• Full details of any booking.

• Availability of hotels in a particular resort, with specific facilities (meal plan or pool).

• Details of hotels in a particular resort.

• Details of hotels available for a specified star rating.

• Resorts that have train stations.

Staff should be able to sort results in order of ascending price and calculate:

• The total cost of any holiday booking.

• The number of hotels within a certain price range or available on a certain start date.

The relational database will have four tables: Hotel, Resort, Booking and Customer.

Each table will have a suitable primary key and any necessary foreign keys. The following fields are required:

• Hotel - hotel name, start of season date, check-in time, price per night, meal plan, swimming pool, star rating.

• Resort - resort name, resort type, train station.

• Customer - firstname, surname, address, town, postcode.

• Booking - start date, number in party, number of nights.

The relational database will use the following: 

• Simple/complex queries to search the database.

• A simple sort to order the query results.

• A calculation to work out the total cost of a booking.

• An aggregate function to work out the number of 4-star hotels located in resorts which have a train station.