Database Reference
In-Depth Information
Normalized
Relational
Data Model
Real-world
Information
Random
Tables
Objectives:
• Create well-structured relations
• Avoid data redundancies
• Ensure that the data model conforms to
relational rules
• Ensure data manipulation will not have problems
or anomalies
Steps:
• Create random tables or relations
• Normalize the tables
Figure 10-1
Informal design of relational data model.
statement of information requirements for which you need to create a relational
data model:
Assignment of employees to projects
Employees work in departments. Information about the employees such as name,
salary, position, and bonus amount must be represented in the data model. The model
should include names of the departments and their managers. Project numbers and
project descriptions are available. The model must represent the start date, end date,
and hours worked on a project for each employee. New employees are not assigned to
a project before they finish training.
Examine the information requirements. Clearly, your data model must represent
information about the employees and their project assignments. Also, some infor-
mation about the departments must be included. Compared to other real-world
information requirements, the information about employee-project assignments
being modeled here is very simple. With this set of information requirements, you
need to come up with two-dimensional tables. Let us say that you are able to put
the data in the form of tables and also express the relationships within the tables.
If you are able to do this, then you are proceeding toward creating the relational
data model.
Looking at the simplicity of the information requirements, it appears that all of
the data can be put in just one table. Let us create that single table and inspect the
data content. Figure 10-2 represents this single table showing sample data values.
Inspect the PROJECT-ASSIGNMENT table carefully. To uniquely identify each
row, you have to assign EmpId and ProjNo together as the primary key. At first
glance, you note that the table contains all the necessary data to completely repre-
sent the data content of the information requirements. The table contains columns
and rows. Review each column. It represents an attribute, and the column name
represents the name of the attribute. Now look at the rows. Each row represents
one employee, a single instance of the entity represented by the table. So far, the
table looks like it qualifies to be part of a relational data model.
Search WWH ::
Custom Search