Scope / Context / Framework

The database will be a central element of a computer application project. It will serve to store the data of this project, in accordance with its objectives and usage. The database structure must be designed based on the application's objectives, user needs, and the functionalities provided by this application.

It will therefore be essential to thoroughly analyze user needs as well as the requirements of the program or application that relies on this database.

Example: Hotel Reservation Management System

Application Objective
The application allows customers to book rooms online, employees to manage reservations, and management to analyze hotel performance.

Data stored in the database
•
Customers: Last name, first name, email, phone, reservation history
•
Rooms: Number, type (single, double, suite), price, status (available, occupied, under maintenance)
•
Reservations: Customer, reserved room, arrival and departure dates, status (confirmed, canceled, pending)
•
Payments: Payment method, amount, status (paid, pending, refunded)
•
Staff: Reception employees, housekeeping, managers

Requirements and functionalities
•
Online reservations: Customers can view room availability and make reservations
•
Availability management: Employees can update room status based on arrivals and departures
•
Notifications and reminders: Automatic sending of confirmation emails to customers
•
Statistics and reports: Tracking of room occupancy rates and revenue

In this case, the database is essential to ensure the proper functioning of the system by ensuring consistency of reservations, avoiding double bookings, and facilitating the daily management of the hotel.

The phases of a database design

1. Identify the exact goals of the system.

2. Talk with the users to identify the basic forms and reports.

3. Identify the data items to be stored.

4. Design the classes (tables) and relationships.

5. Identify any business constraints.

6. Verify the design matches the business rules.

Conceptual Overview

Analyze the current database. You identify your organization’s data requirements by reviewing the way your organization currently collects and presents its data and by conducting interviews with users and management to determine how they use the database on a daily basis.

Create the data structures. You establish tables by identifying the subjects that the database will track. Next, you associate each table with fields that represent distinct characteristics of the table’s subject and designate a particular field (or group of fields) as the primary key. You then establish field specifications for every field in the table.

Determine and establish table relationships. You identify relationships that exist between the tables in the database and establish the logical connection for each relationship using primary keys and foreign keys or by using linking tables. Then you set the appropriate characteristics for each relationship.

Determine and define business rules. You conduct interviews with users and management to identify constraints that must be imposed upon the data in the database. The manner in which your organization views and uses its data typically determines the types of constraints you must impose on the database. You then declare these constraints as business rules, and they will serve to establish various levels of data integrity.

Determine and establish views. You interview users and management to identify the various ways they work with the data in the database. When your interviews are complete, you establish views as appropriate. You define each view using the appropriate tables and fields and establish criteria for those views that must display a limited or finite set of records.

Review data integrity. This phase involves four steps. First, you review each table to ensure that it meets proper design criteria. Second, you review and check all field specifications. Third, you test the validity of each relationship. Fourth, you review and confirm the business rules.