Reference no: EM132839147

CIS017-1 Computer Systems Structure - University of Bedfordshire

Assignment - Design and Implement a Database

Learning Outcome 1: Demonstrate the following knowledge and understanding:

Comprehend the underpinning technologies of data communications and computer networking while appraising the basic system structures and the principles around data modification.

Learning Outcome 2: Demonstrate the following skills and abilities:
- Utilise problem-solving skills to put together information from different sources in order to design, develop and implement a databases or computer network.

• Investigate a given busines-related case study / scenario
• Determine the database requirements for the scenario
• Apply conceptual / logical modelling techniques to create an Entity Relationship Model and a fully normalised set of tables
• Implement and modify a database using a set of SQL commands.
• Edit and manipulate database records based on requirements given to you
• Successfully detect, describe and analyse the main cause of faults within data structures.

Case Study

Consider the following scenario:

A hotel would like to provide an online service for customers to book rooms and to check their upcoming trips. A database needs to be created to store customer data (name, address, email, telephone number) and booking information (start and end date, double or single room) as well as room information, indicating whether a room is a single or double room. Each booking must be assigned to exactly one single or double room as indicated in the booking.

Customers who are not registered yet need to register (providing their address, phone number, email address and credit card information). A registered customer should be able to book a room, ie. provide the check-in and check-out date and if a double or single room is required. Customers should also be able to view their bookings and change or cancel them if needed. All these operations are only possible after a customer signs in (which means they must be registered). Your system should also check if the requested room (single or double room) is available for the time of stay. After a booking is made, hotel managers must assign an appropriate room to each booking, matching the customer's demand regarding single or double room. To determine whether a room is available during the requested time period, the hotel manager should be able to see existing bookings per room. Hotel managers must also sign in before any operation can be performed.

Figure 1: Hotel Booking System Use Case Diagram

As a guide, the functionality of the Hotel Booking System is shown in the Use Case Diagram in Figure 1, taking the different user roles (customer and hotel manager) into account.

Tasks

1. Entity Relationship Model (ERM)

Create an Entity Relationship Model (ERM) for the Hotel Booking System.

You are allowed to use any ERM modelling notation but it is suggested that you use a version of the Oracle ERM notation as described during the Unit's lectures.

It also is suggested that you create your ER Diagram using Visual Paradigm Community Edition software which is a free download. You should not use Visio or equivalent software.

Your ER Diagram should include all identified entities and the relationships between them. Relationship should include consideration of both cardinality and optionality, and a textual description of each relationship should be included.

You should also create a list of your Entities.

Primary and Foreign keys and other attributes for each entity should be included in this list.

Your Entity Relationship Model should be included in your report.

2. Normalisation

Create a normalized set of tables for the scenario for the given data.
The steps in the normalization from UNF to 3NF should be shown.

Your set of normalized tables should be included in your report.

3. Physical Table Design (including Data Dictionary)

You should now be able to create a list of the tables to be implemented in your selected RDMS.

Create a set of ‘Skeleton tables' which should show the table name, primary key field, foreign key field(s), and all other attributes. (This will be RDMS system independent).

Create a Data Dictionary for all tables and attributes.

For each attribute show the datatype, length, and other properties. You may want to include any other information such as data entry constraints.

The Data Dictionary should be part of your physical design ie. dependent on the selected RDMS.

Your skeleton tables and data dictionary should be included in your report.

4. Implementation of the Database

For this last practical task, you can use almost any RDMS - MySQL, SQLite, Oracle, Oracle Application Express (APEX) but you should not use MS Access.

Write SQL statements to create your database and table structure including Primary Key and Foreign Key constraints.

Write SQL statements to insert at least two rows of data in each table. Further records can be added manually.

You should create screenshots of the implementation to show the successfully created tables - structure and data, and the results of running your queries.

It may be possible (depending on platform and version) to also produce a screenshot of the set of tables created with the relationships between them.

5. Query Design and Implementation

Use your SQL skills to design a set of relevant SQL queries and demonstrate your knowledge of SQL. Five (5) or six (6) queries will be sufficient but should include querying from multiple tables, using aliases, summary queries and grouping. The more complex and relevant the query is the more marks you are likely to achieve.

Write SQL statements to create the queries you have designed. Run and test the queries.

Your SQL queries should be included in your report, and you should include an explanation of the purpose of the query.

6. Report

You will need to submit a word-processed report with evidence, explanation and discussion of the above tasks.

This must be a professionally presented report with appropriate headers and footers (including filename, page numbers) and heading and subheadings. Any diagrams included must have a figure number and caption. A table of contents is optional.

Suggested sections are:

Introduction
Scenario

Tasks:
• Entity Relationship Model
• Normalization
• Physical Table Design (including Data Dictionary)
• Implementation of the Database
• Query Design and Implementation
• Discussion / Critical Analysis / Reflection

Attachment:- Design and Implement a database.rar