Overview

• Notes on key points from "The Expert's Guide to Database Design" for future reference.
• The book has 9 chapters, but this covers through Chapter 3.

Chapter 1

POA vs. DOA

POA stands for Process Oriented Approach. Traditional system development was process-centric. Today, DOA (Data Oriented Approach) is the norm. With POA, data is managed separately for each process such as order receipt, purchase orders, and journaling. With DOA, all processes share the same data.

DB vs. DBMS

DB is a concept referring to a "collection of data." DBMS is the system used to manage a DB.

Major DBMSes

See the following reference. Oracle, MySQL, Microsoft SQL Server, and PostgreSQL are the four dominant RDBMSes.

db-engines.com

Three-Schema Architecture

• External schema (external model) = Views (the database as seen by users)
• Conceptual schema (logical data model) = Tables (the database as seen by developers)
• Internal schema (physical data model) = Files (the database as seen by the DBMS)

A two-schema architecture (only external and internal schemas) lacks flexibility in handling changes. The conceptual schema serves as a buffer layer. Logical data independence: external schema ⇔ conceptual schema. Physical data independence: conceptual schema ⇔ internal schema.

Chapter 2: Logical and Physical Design

Conceptual Schema and Logical Design

Steps in Logical Design

1. Entity extraction
2. Entity definition
3. Normalization
4. ER diagram creation

Internal Schema and Physical Design

Steps in Physical Design

1. Table definition
2. Index definition
3. Hardware sizing
4. Storage redundancy configuration
5. Physical file placement

Sizing

• Consider both capacity and performance perspectives
• 80% of database performance issues stem from storage I/O bottlenecks
• Data volume generally grows from the start of system operation
• Two performance metrics:
  • Processing time = "How many seconds does a specific process complete within?"
  • TPS (Transactions Per Second) = "How many operations can be processed per second?"
• Use data from similar systems or build a prototype to validate sizing
• Precise sizing is difficult, so apply a safety factor and use a highly scalable architecture

Redundancy Configuration

• Use at least RAID5. If budget allows, use RAID10.

Physical File Placement

There are 5 types of files:

1. Data files
2. Index files
3. System files
4. Temporary files
5. Log files

---

• Temporary files include expanded subquery data and sort data
• The DBMS does not update data files immediately upon receiving changes. Instead, it queues change requests in log files and applies them in batches. Log files have different names depending on the DBMS:
  • Oracle: REDO log
  • PostgreSQL, SQL Server, DB2: Transaction log
  • MySQL: Binary log
• Data files ①, index files ②, and system files ③ grow continuously
• Among the 5 file types, data files ① have the highest I/O volume, followed by index files ② and temporary files ④
• Ideally, place all files on separate disks (RAID groups) for I/O distribution, though this increases disk count
• A compromise is to consolidate low-I/O files: place system files ③ and log files ⑤ on one disk, and allocate separate disks for the rest

Backup Design — The three main backup methods:

1. Full backup
2. Differential backup
3. Incremental backup

---

• Incremental backup is the most efficient (no duplicate backups of the same data), but recovery requires more files, so recovery time is longer. It also has the lowest probability of complete data recovery (requires all backup files to be intact)
• Common approaches: "Full backup + Differential backup" or "Full backup + Incremental backup"
• Design backups considering: backup window (time available for backup), recovery window (time available for recovery), how many generations of data to retain, and how far back recovery must be possible

Recovery Design

The recovery procedure is strictly divided as follows. All three steps must be performed to restore the system to the state just before the failure:

1. Restore: Reconstruct the database from the full backup file
2. Recovery: Apply the differential (or incremental) backup transaction logs
3. Roll forward: Apply the transaction logs remaining on the database server

Chapter 3: Logical Design and Normalization

Table Components

• Table rows are also called records (sometimes called instances in other materials)
• Table columns are also called attributes
• Keys include primary keys and foreign keys
• The role of a foreign key is to enforce referential integrity constraints
• When foreign keys are set, it is better to delete data starting from child tables
• Alternatives include cascading deletion of "orphaned children" when a parent is deleted, or making the delete SQL fail with an error. This series of deletion behavior is called a "cascade"
• Columns used as keys should use fixed-length strings such as codes or IDs
• The following constraints can be set per column:
  • NOT NULL constraint: value must not be NULL
  • Unique constraint: no duplicate values
  • CHECK constraint: restricts allowable values. Example: "integers 1 through 5", "strings from 'Development', 'HR', 'Sales'"
• Apply NOT NULL constraints as much as possible in table definitions
• Table names and column names have the following constraints (some DBMSes support Japanese and special characters as non-standard extensions):
  • Allowed characters: half-width alphabetic characters, half-width numbers, underscores
  • First character must be alphabetic
  • No duplicate table names or column names within the same scope

What Is Normalization?

• The purpose of normalization is to prevent the same information from existing in multiple tables, which wastes space and complicates update operations. It eliminates data redundancy to prevent data inconsistencies.
• The result of normalization is called a normal form.
• Normal forms go up to level 5, but understanding through level 3 is sufficient for most purposes.
• Normalization basically means splitting tables.
• Normalization is a reversible operation: non-normal form ⇔ normal form
• The inverse of normalization is joining (JOIN)

First Normal Form (1NF)

• Each cell at the intersection of a row and column contains only a single value.

Second Normal Form (2NF)

• 2NF is achieved by eliminating partial functional dependencies (partial → full functional dependency)
• Partial functional dependency: a non-key column depends on only part of the primary key
• Resolved by splitting the table
• Why is partial functional dependency a problem?
  • When the primary key is unknown (NULL), you cannot register values for columns that depend on the primary key

Third Normal Form (3NF)

• 3NF is achieved by eliminating transitive functional dependencies (transitive → full functional dependency)
• Example: A table has employee ID, department code, and department name columns. The dependencies are {employee ID} → {department code} → {department name}. This is called a transitive functional dependency.
• Resolved by splitting the table

3.5NF, 4NF, 5NF

• These will be skipped.
• 3.5NF is formally known as Boyce-Codd Normal Form (BCNF).