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.

• Learning Objectives
  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 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.

• 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

Relational constraints in database design are critical rules enforced by a relational database management system (RDBMS) to ensure data integrity and consistency across tables. These constraints define the conditions that data must meet to be valid, preventing incorrect or inconsistent data from being entered. For example, primary key constraints ensure that each record in a table is uniquely identifiable by enforcing uniqueness and non-null values in the designated column(s). Similarly, foreign key constraints maintain referential integrity by ensuring that values in a child table’s foreign key column correspond to valid values in the parent table’s primary key or unique key. This prevents orphaned records, such as an order referencing a non-existent customer. By enforcing these rules, relational constraints safeguard the accuracy and reliability of data, reducing errors during data insertion, updates, or deletions.
Beyond primary and foreign key constraints, other relational constraints like not null, unique, and check constraints further enhance data consistency. A not null constraint ensures that specific columns cannot contain null values, guaranteeing that critical fields, such as a customer’s name, are always populated. Unique constraints prevent duplicate values in specified columns, such as email addresses, while allowing nulls where appropriate. Check constraints enforce domain integrity by restricting column values to a predefined range or condition, such as ensuring a product’s price is positive. Together, these constraints act as a protective framework, ensuring that the database adheres to the intended logical structure and business rules. This not only maintains data quality but also supports reliable querying and reporting, as the data remains consistent and trustworthy across related tables.
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.