It uses a replica of the field specifications for the Primary key from which it was copied.

It draws its value from the Primary key to which it refers.

Establishing a Deletion Rule for Each Relationship

Restrict. The requested record cannot be deleted if there are related records.

Cascade. The requested record will be deleted as well as all related records.

Identifying the Type of Participation for Each Table

Mandatory. There must be at least one record in this table before you can enter any records into the other.

Optional. There is no requirement for any records to exist in this table before you can enter any records into the other table.

Relationship-Level Integrity

Made certain that the connection between two tables in a relationship is sound.

Ensured your ability to insert new records into each table in a meaningful manner.

Ensured your ability to delete an existing record without creating adverse affects.

Established a meaningful limit to the number of records that can be interrelated within the relationship.

What Are Business Rules?

A business Rule is statement that imposes some form of constraint on elements within a field specification for a particular field or on characteristics of a relationship between a specific pair of table.

Examples:

A Ship Date cannot be prior to an Order date for any given order.

A student cannot have more than two instruments checked out at the same time.

Types of Business Rules

Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database.

Application-oriented Business Rules are those that impose constraints that cannot be established by modifying a Field Specification or relationship diagram.

Determining and Establishing Views

There are three categories of Views: date Views, aggregate Views, and validation Views.

Data Views are used to examine and manipulate data from base tables.

Aggregate Views are used to display information that is the result of aggregating a particular set of data in a specific manner.

Validation Views are used to implement data integrity. When a Business Rule limits the range of values of a particular field, you can enforce the constraint just as easily with a validation View as you can with a validation table.

Using a View Specifications Sheet to Record the View

Reviewing and Refining Data Integrity

At the table level:
There are no duplicated fields in the table.
There are no calculated fields in the table.
There are no multi-valued fields in the table.
There are no multipart fields in the table.
There are no duplicated records in the table.
Every record in the table is identified by a Primary key value.
Each Primary key conforms to the Elements of a Primary key.

At the field level:
Each field conforms to the Elements of the Ideal Field.
A set of Field Specifications has been defined for each field.

At the relationship level:
The table relationship is properly established.
The appropriate deletion rule has been established.
The type of participation has been correctly identified.
The proper degree of participation has been established.

At the level of business rules:
Each rule imposes a meaningful constraint.
The proper category has been determined for the rule.
Each rule is properly defined and established.
The appropriate Field Specification elements or table relationship characteristics have been properly modified.
The appropriate validation tables have been established.
A Business Rule Specification sheet has been completed for each rule.

At the level of views:
Each View contains the base tables necessary to provide the required information.
Each View has been assigned the appropriate fields.
Each calculated field provides pertinent information or enhances the manner in which the data is displayed.
Each filter returns the appropriate set of records.
Each View is accompanied by a View Specification sheet.

There are four maim types of keys: candidate, primary, foreign, non-keys.
A key’s type determines its function within the table.

Elements of a Candidate Key

It must uniquely identify each record in the table.

It must contain unique values.

It cannot be null.

It cannot be a multi-part field.

It comprises a minimum number of fields necessary to define uniqueness.

Its value is not optional in whole or in part.

Its must directly identify the value of each field in the table.

Its value can only be modified in rare or extreme cases.

Artificial Candidate Keys

If none of the fields in a table, either singularly or as a set, qualifies as a Candidate key. “Artificial” is used in the sense that a Candidate key does not “naturally” occur in the table; you have to manufacture one.

Primary Keys
If you have a “simple” (single field) Candidate key and a Composite Candidate key, choose the “simple” Candidate key. It’s always best to use a Candidate key that contains the least number of fields.
Choose the Candidate key that uses a field that incorporates part of the table name within its name. For example, a Candidate key comprising Sales Invoice Number is the good choice for the SALES INVOICES table.

Elements of a Primary Key

It must uniquely identify each record in the table.

It must contain unique values.

It cannot be null.

It cannot be a multipart value.

It should contain the minimum number of fields necessary to define uniqueness.

It is not optional in whole or in part.

It must directly identify the value of each field in the table.

Its value can only be modified in rare or extreme cases.

Rules for Establishing a Primary Key

Each table must have one and only one Primary key.

Each Primary key within the database should be unique no two tables should have the same Primary key unless one of them is a subset table.

The four classification used to identify the table type are data, linking, subset, and validation.

Guidelines for Composing a Table Description

Include a definition statement that accurately identifies the table.

Include a statement that explains why this table is important to the organization.

Compose a description that is clear and succinct. Don’t be too brief or too verbose.

Do not include implementation-specific information in your table description, such as how or where the table is used.

Do not make the table description for one table dependent on the table description of another table.

Do not use examples in a table description.

Interviewing Users and Management

In order to define a good table description for each table, you’ll enlist the help of users and management to establish the table’s definition and importance.

Associating Fields with Each Table

Determine which fields best represent characteristics of the table’s subject and assign them to that table.

Guidelines for Creating a Field Names

Create a unique, descriptive name that is meaningful to the entire organization. There should be only one occurrence of a field name in the entire database. (There is only one exception: when a field is used to establish a relationship between two tables.)

Create a name that accurately, clearly, and unambiguously identifies the characteristic represented by the field.Bad Example: “Phone number”; Good Example: “Home Phone”.

Use the minimum number of words necessary to convey the meaning of the characteristic the field represents.Bad Example: “Date that employee was hired “; Good Example: “Date Hired”.

Do not use acronyms and abbreviations.Bad Example: “Address “; Good Example: “CustAddress”.

Do not use words that could confuse the meaning of the field name.Bad Example: “Digital Identification Code Number “; Good Example: “Identification Code”.

Do not use names that implicitly or explicitly identify more than one characteristic.Bad Example: “Type & Description “

Use the singular form of the name.

Elements of the Ideal Field

It represents a characteristic of the subject of the table. Subject can be an object or event.

It contains only a single value. Two problems rise with fields that contain more than one value (commonly known as multi-valued fields): data redundancy and difficulty in working with data in the field.

It cannot be deconstructed into smaller components (multi-part field).

It does not contain a calculated or concatenated value.

It is unique within the entire database structure.

It retains all of its characteristics if it appears in more than one table.

Resolving Multi-part Fields

Working with a multipart field is difficult because its value contains two or more distinct items. It’s hard to retrieve information from a multipart field, and it’s hard to sort a group the records in the table by the field’s value.

Resolving Multi-valued Fields

A multi-valued field can potentially store two or more occurrences of the same value. Fortunately, you will recognize a multi-valued field when you see one. The field’s name is often plural and it’s value almost invariably contains a number of commas, which serve to separate the various occurrences that exist within the value itself.

An important aspect of most every business is record keeping. In our information society, this has become an important aspect of business, and much of the world’s computing power is dedicated to maintaining and using databases.

Databases of all kinds pervade almost every business. All kinds of data, from emails and contact information to financial data and records of sales, are stored in some form of a database. The quest is on for meaningful storage of less-structured information, such as subject knowledge.

The most important steps of the relational database design:

Define mission statement and mission objectives for the database. The mission statement defines the purpose of the database. The mission objectives define the tasks that are to be performed by users against the data in the database.

Analyze the current database. You identify the data requirements of the organization by reviewing the way data is currently collected and presented 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 will be tracked by the database. Next you assign each table fields that best characterize its subject, and you designate a Primary key as well. Then you establish field specifications for every field in the table.

Determine and establish table relationship. You’ll identify relationships that exist between the tables in the database and then establish the logical connection for each relationship using Primary keys and Foreign keys, or linking tables. Finally, you’ll set the various characteristics for each relationship.

Determine and define Business Rules. Next you conduct interviews with users and management to identify constraints on the data in the database based on the way the organization views and uses its data. These constraints are then declared as Business Rules, which will serve to establish various levels of data integrity.

Determine and establish Views. Users and management are interviewed to identify the various ways they look at the data in the database. After these various perspectives have been identified, you establish them as Views. Each View is defined using the appropriate table or tables, and certain Views use criteria that limit the records that they display.

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 go over and confirm the business rules.