Lesson 1

This module reviews the basic concepts and terminology of database design.
Even if you are an experienced database designer, take a few moments to work through this module to reinforce your knowledge of the concepts and vocabulary we will use in the course.

After completing the lessons in this module, you should be able to:

1. List the steps in the database life cycle (DBLC)
2. Explain why an ER diagram is translated into relational notation
3. Describe the characteristics of tables and table types
4. List the rules for table columns and rows
5. Describe column domains and domains that support calculations
6. Describe and explain the purpose of null values
7. Differentiate key and descriptor columns
8. Define and identify single-field and composite primary keys
9. Describe all-key relations
10. Define and identify foreign keys
11. Define referential integrity

The next lesson reviews the steps in the database life cycle.

The relational database model improves on the restriction of a hierarchical structure, not completely abandoning the hierarchy of data. Any table can be accessed directly without having to access all parent objects. The key is to know what to look for.
If you want to find the address of a specific employee, you have to know which employee to look for, or you can simply examine all employees. You do not have to search the entire hierarchy from the company downward, to find a single employee. Another benefit of the relational database model is that different tables can be linked together, regardless of their hierarchical position. Obviously, a relationship should exist between the two tables, but you are not restricted by a strict hierarchical structure. Therefore, a table can be linked to both any number of parent tables and any number of child tables.

Here is the purpose and function of relational constraints in database design ¹ ²: Purpose:

• Ensure data integrity and consistency
• Maintain relationships between tables
• Enforce business rules and data validation
• Prevent data redundancy and inconsistencies

Function:

1. Domain Constraints: Ensure atomic values and data type consistency
2. Key Constraints (Uniqueness Constraints): Ensure unique values for each tuple
3. Entity Integrity Constraints: Ensure primary keys are not null
4. Referential Integrity Constraints: Maintain consistency between related tables
5. NOT NULL: Ensure a column value cannot be empty
6. UNIQUE: Ensure a column value cannot be duplicated
7. PRIMARY KEY: Ensure each row can be uniquely identified
8. FOREIGN KEY: Link tables based on primary key values
9. CHECK: Enforce custom constraints on a column
10. DEFAULT: Set default values for a column when no value is provided

Many of today's businesses rely on their database systems for accurate,up-to-date information. Without those repositories of mission-critical data, most businesses are unable to perform their normal daily transactions, much less create summary reports that help management make strategic corporate decisions. To be useful, the data in a database must be accurate, complete, and organized in such a way that data can be retrieved when needed and in the format desired. Well-written database application programs, whether they

1. execute locally,
2. run over a local area network, or
3. feed information to a website

are fundamental to timely and accurate data retrieval. However, without a good underlying database design, even the best program cannot avoid problems with inaccurate and inconsistent data.