Business Requirements Analysis Creating Views

You have already learned how to create efficient, error-free tables that enable users to

1. store,
2. update, and
3. delete

records without making any unintended changes to the table data. Next three modules show you how to create information by extracting data out of the tables.

During the first phase of the Database Life Cycle, Requirements Analysis^[1], you interviewed potential users of the database with the purpose of discovering what business objects would need to be represented in the database and to find out what type of information users would want to derive from that data. This depends on the business objects in the database and each user's job responsibilities. A rental car company might require a database modeling these objects:

A customer-service representative would require access to tables storing information about available vehicles, special offers, details of specific rentals, and customers. Customer-service reps also could be allowed to enter data from accident reports, although the company might choose to limit which reps would be able to examine accident reports. Once you have determined what information users need, you can create views^[2] to present that information. A view is a database construct that limits the database objects a user can interact with and can contain calculated fields, which are not allowed in base tables.

In our discussion of database elements, we pointed out that you should not perform calculations in a base table because the field containing the calculated value would not derive its value from a single source. By performing a mathematical operation on the contents of two or more fields, it would derive its value from at least two sources, and violate Codd's first rule of fully relational databases: "All information in a relational database is represented explicitly at the logical level in exactly one way, by values in tables." Because calculations are not, strictly speaking, values in tables, calculations violate the rule. Views, however, are not base tables, so they can contain calculations. (A view creates a virtual table but is not in itself a virtual table.) Some examples of calculations of interest to rental car agency branch managers include:

1. What percentage of their fleet is currently available for rent?
2. Which makes and models of car have required the most maintenance?
3. What percentage of drivers exceeded their agreement's mileage limitations?
4. Which car is the most popular?
5. What are total revenues?
6. What is the total labor cost?
7. What is the total maintenance cost?
8. What is the profit?

The next lesson discusses how user views are stored in a database.

Before moving on to the next lesson, click the Exercise link below to reinforce your understanding of requirements analysis and user views.
User Views - Exercise

[1]requirements analysis: The stage in the database design cycle when designers find out everything they can about the data the client needs to store in the database and the conditions under which that data needs to be accessed.

[2] View: A database object that enables you to define the columns and rows that a specific user can see. A view can also serve as a tool for enforcing security within your database.