D426 Chapter 4B — Implementing Designs & Normalization

WGU D426 zyBooks Ch 4, sections 4.10–4.15 (4.14 Boyce-Codd normal form didn't print). Key terms verbatim plus concept, comparison and scenario cards. Not official WGU or zyBooks material.

Stable (primary key guideline)

Primary key values should not change. When a primary key value changes, statements that specify the old value must also change. Furthermore, the new primary key value must cascade to matching foreign keys. (§4.10)

Simple (primary key guideline)

Primary key values should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string. (§4.10)

Meaningless (primary key guideline)

Primary keys should not contain descriptive information. Descriptive information occasionally changes, so primary keys containing descriptive information are unstable. (§4.10)

Strong table

A strong entity becomes a strong table. The primary key must be unique and required, and should be stable, simple, and meaningless. (§4.10)

Artificial key

An artificial key is a simple primary key created by the database designer. Usually artificial keys are integers, generated automatically by the database as new rows are inserted to the table. Artificial keys are stable, simple, and meaningless. (§4.10)

Weak table

A weak entity becomes a weak table. A weak table has a foreign key that references the identifying table and implements the identifying relationship. (§4.10)

Supertype table

A supertype entity becomes a supertype table. A supertype entity that has an identifying attribute is implemented like a strong entity. A supertype entity that has an identifying relationship, rather than an identifying attribute, is implemented like a weak entity. (§4.10)

Subtype table

A subtype entity becomes a subtype table: The primary key is identical to the supertype primary key. The primary key is also a foreign key that references the supertype primary key. (§4.10)

Depends on (column A depends on column B)

Column A depends on column B means each B value is related to at most one A value. Columns A and B may be simple or composite. 'A depends on B' is denoted B → A. | Ex: PassengerNumber → PassengerName, since each passenger number always has the same name. (§4.13)

Functional dependence

Dependence of one column on another is called functional dependence. Functional dependence reflects business rules. Ex: "Each student receives one letter grade in a course" indicates the Grade column depends on the composite column (StudentID, CourseCode). | It cannot be inferred from values in a table at one point in time, because future updates may alter the data. (§4.13)

Multivalued dependence

Multivalued dependence and join dependence entail dependencies between three or more columns. However, multivalued and join dependencies are complex, uncommon, and not discussed in this material. | Fourth normal form eliminates multivalued dependencies. (§4.13)

Join dependence

Multivalued dependence and join dependence entail dependencies between three or more columns. However, multivalued and join dependencies are complex, uncommon, and not discussed in this material. | Fifth normal form eliminates join dependencies. (§4.13)

Redundancy

Redundancy is the repetition of related values in a table. Ex: In the Booking table, above, (222, Elvira Yin) is repeated. Redundancy causes database management problems. When related values are updated, all copies must be changed, which makes queries slow and complex. If copies are not updated uniformly, the copies become inconsistent and the correct version is uncertain. (§4.13)

Normal forms

Normal forms are rules for designing tables with less redundancy. Normal forms are numbered, first through fifth. An additional normal form, Boyce-Codd, is an improved version of third normal form. The six normal forms comprise a sequence, with each successive normal form allowing less redundancy. (§4.13)

First normal form

Every cell of a table contains exactly one value. A table is in first normal form when, in addition, the table has a primary key. | Corollaries: every non-key column depends on the primary key, and the table has no duplicate rows. (§4.13)

Second normal form

A table is in second normal form when all non-key columns depend on the whole primary key. In other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is automatically in second normal form. (§4.13)

Third normal form

Redundancy can occur in a second normal form table when a non-key column depends on another non-key column. Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material. (§4.13)

Fourth normal form

Fourth and fifth normal forms eliminate multivalued and join dependencies, respectively. Since multivalued and join dependencies are complex and uncommon, fourth and fifth normal forms are primarily of theoretical interest. | Fourth normal form eliminates multivalued dependencies and comes right after Boyce-Codd in the sequence; it isn't described in this material. (§4.13)

Fifth normal form

Fourth and fifth normal forms eliminate multivalued and join dependencies, respectively. Since multivalued and join dependencies are complex and uncommon, fourth and fifth normal forms are primarily of theoretical interest. | Fifth normal form eliminates join dependencies and is the last of the six normal forms, so it allows the least redundancy; it isn't described in this material. (§4.13)

Normalization

Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form. | It is the last step of logical design. In E. F. Codd's original paper on the relational model, normalization meant achieving first normal form; over time it has come to mean achieving higher normal forms. (§4.15)

Boyce-Codd normal form

In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique. | It eliminates all dependencies on non-unique columns and, in practice, is the most important normal form; database designers usually normalize tables to it. (§4.13, §4.15)

Denormalization

Denormalization means intentionally introducing redundancy by merging tables. Denormalization eliminates join queries and therefore improves query performance. Denormalization results in first and second normal form tables and should be applied selectively and cautiously. (§4.15)

Table diagram marks (●, R, U, bracketed U, arrow): what each means and its CREATE TABLE keyword

● = primary key column → PRIMARY KEY. Primary keys are always required and unique, so R and U are unnecessary after it. | R = required → NOT NULL. | U = unique → UNIQUE. | Columns bracketed together and followed by U are unique together → a separate clause such as UNIQUE (ColA, ColB). | No R, U, or bullet → the column may be NULL and is not unique. | Arrow = foreign key: it starts at the foreign key and points to the table containing the referenced primary key. (§4.10, §4.12)

Strong table primary key: simple vs composite vs artificial key

It must be unique and required; stable, simple, and meaningless are desirable but not necessary. | Simple primary keys are best for strong tables. If no simple primary key is available, a composite primary key may be selected, though a composite with a meaningful column that may change makes a poor key. | Alternatively, the designer creates an artificial key (step 5B), more often when the database system has tools that generate new artificial key values automatically. (§4.10)

Weak table: how is its primary key chosen?

It depends on the cardinality of the identifying relationship. | Usually the weak entity is plural: the primary key is the composite of the foreign key and another column. | Occasionally it is singular: the primary key is the foreign key only. | With several identifying relationships, the primary key includes one foreign key for each identifying relationship, plus an additional column if necessary for uniqueness. (§4.10)

PK cascade and FK restrict: what are they, and which new tables usually get them?

The usual referential integrity actions: cascade on primary key update and delete; restrict on foreign key insert and update. | They're usually specified on the foreign key of a weak table, a subtype table (where the foreign key implements the IsA relationship), a new table for a many-many relationship, and a new table for a plural attribute. | Diagrams may optionally show them as 'PK cascade' and 'FK restrict' next to the arrow. (§4.10, §4.11, §4.12)

Implement entities (Table 4.10.1): steps 5A–5D

5A) Implement strong entities as tables. | 5B) Create an artificial key when no suitable primary key exists. | 5C) Implement weak entities as tables. | 5D) Implement supertype and subtype entities as tables. | This step creates the initial table design and specifies primary keys. The design is augmented as relationships and attributes are implemented, and the final SQL stabilizes as tables are reviewed for normal form. (§4.10)

Many-one relationship: where does the foreign key go, and how is it named?

The foreign key goes in the table on the 'many' side and refers to the table on the 'one' side. If the entity on the 'one' side is required, the foreign key column is also required. | Name: the referenced primary key's name with an optional prefix, usually derived from the relationship name, that clarifies its meaning. Ex: the ArrivesAt relationship to AirportCode gives ArrivalAirportCode. (§4.11)

One-one relationship: how is it implemented?

As a foreign key that can go in the table on either side. Usually it's placed in the table with fewer rows, to minimize the number of NULL values. | The foreign key refers to the table on the opposite side, the column is unique, and it's required if the entity on the opposite side is required. | It's named like any foreign key (the book's AirportAddressID takes its prefix from the table name instead of the relationship name). (§4.11)

Many-many relationship: how is it implemented?

As a new weak table: | 1) Two foreign keys, referring to the primary keys of the related tables. | 2) Primary key: the composite of the two foreign keys. | 3) The related tables identify it, so PK cascade and FK restrict rules are usually specified. | Occasionally an attribute that describes the relationship becomes a column of the new table. Name: the related table names with an optional qualifier in between, usually derived from the relationship name. (§4.11)

Implement relationships (Table 4.11.1): steps 6A–6C

6A) Implement many-one relationships as a foreign key on the 'many' side. | 6B) Implement one-one relationships as a foreign key in the table with fewer rows. | 6C) Implement many-many relationships as new weak tables. | Identifying relationships already became foreign keys in 'implement entities'; this step converts all other relationships into foreign keys or tables. (§4.11)

Plural attribute: how is it implemented?

Singular attributes stay in the initial table; a plural attribute moves to a new weak table: | 1) It contains the plural attribute and a foreign key referencing the initial table. | 2) Primary key: the composite of the plural attribute and the foreign key. | 3) The initial table identifies it, so PK cascade and FK restrict rules are specified. | 4) Name: the initial table name followed by the attribute name. | With a small, fixed maximum, it can be several columns in the initial table instead, but a new table simplifies queries and is usually the better solution. (§4.12)

Attribute types: how do they determine column data types?

Standard attribute types are listed during conceptual design. During logical design an SQL data type is defined for each, and both are documented in the glossary. Each attribute name includes a standard attribute type as a suffix, and that type determines the column's data type. Ex: Code → CHAR(3) and Name → VARCHAR(30), so AirportCode is CHAR(3) and CityName is VARCHAR(30). (§4.12)

Foreign key constraints from relationship cardinality: when is the column required, and when unique?

Required (NOT NULL): if the table referenced by the foreign key implements a required entity. | Unique (UNIQUE): if the table containing the foreign key implements a singular entity. (§4.12)

Implement attributes (Table 4.12.1): steps 7A–7D

7A) Implement plural attributes as new weak tables. | 7B) Specify cascade and restrict rules on new foreign keys in weak tables. | 7C) Specify column data types corresponding to attribute types. | 7D) Enforce relationship and attribute cardinality with UNIQUE and NOT NULL keywords. | Afterward, the database is completely specified as CREATE TABLE statements. The final step, 'review tables for third normal form', ensures tables don't contain redundant data. (§4.12)

What causes redundancy?

A dependence on a column that is not unique. Ex: (222, Elvira Yin) repeats in the book's Booking table because PassengerName depends on PassengerNumber, which is not unique there. | Boyce-Codd normal form eliminates all dependencies on non-unique columns. (§4.13)

The six normal forms in order, and what each rules out

First → second → third → Boyce-Codd → fourth → fifth; each successive normal form allows less redundancy. | 1NF: every cell has one value, and the table has a primary key. | 2NF: no non-key column depends on part of a composite primary key. | 3NF (informally): no non-key column depends on another non-key column. | Boyce-Codd: if A depends on B, B must be unique. | 4NF and 5NF: eliminate multivalued and join dependencies; primarily of theoretical interest. (§4.13, §4.15)

First normal form: four common definitions, and which are equivalent

1) The table has a primary key. 2) Every non-key column depends on the primary key. 3) The table cannot have duplicate rows. 4) Every cell contains exactly one value. | The first three are equivalent. The last is different: it is true of any relational table and allows duplicate rows and no primary key. (§4.13)

Normalizing to Boyce-Codd normal form: the three steps

1) List all unique columns, simple or composite. Composite columns must be minimal: remove any columns not necessary for uniqueness. | 2) Identify dependencies on non-unique columns, which are either external to all unique columns or contained within a composite unique column. | 3) Eliminate dependencies on non-unique columns: if A depends on non-unique B, remove A from the original table and create a new table containing A and B. B is the new table's primary key and a foreign key in the original table. No information is lost, since the data relating A and B is recorded in the new table. (§4.15)

Applying normal form (Table 4.15.1): steps 8A–8C

8A) Identify dependencies on non-unique columns. | 8B) Eliminate redundancy by decomposing tables. | 8C) Consider denormalizing tables in reporting databases. | As tables and keys are specified, the designer reviews each table for Boyce-Codd normal form. (§4.15)

Syntax to add or drop a foreign key on an existing table (LAB hints)

ALTER TABLE ChildTable ADD FOREIGN KEY (ColumnName) REFERENCES ParentTable(ColumnName) ON DELETE SET NULL ON UPDATE CASCADE; | ALTER TABLE ChildTable DROP FOREIGN KEY ConstraintName; | LAB 4.16 uses SET NULL on delete and CASCADE on update; LAB 4.17's IsA foreign keys use ON DELETE CASCADE ON UPDATE CASCADE. (§4.16, §4.17)

Strong vs weak vs subtype table: where does each primary key come from?

Strong table: its own unique, required column (ideally stable, simple, and meaningless), a composite key, or an artificial key. | Weak table: includes the foreign key to the identifying table, plus another column when the weak entity is plural (the foreign key alone when it is singular). | Subtype table: identical to the supertype primary key and also a foreign key referencing it, which implements the IsA relationship. (§4.10)

Many-many table vs plural-attribute table: keys and names

Many-many: two foreign keys; primary key = composite of the two foreign keys; name = the related table names, with an optional qualifier from the relationship name. | Plural attribute: one foreign key plus the attribute; primary key = composite of the plural attribute and the foreign key; name = the initial table name followed by the attribute name. | Both are new weak tables with PK cascade and FK restrict rules. (§4.11, §4.12)

Second vs third normal form ('the whole key' vs 'nothing but the key')

Second: no non-key column depends on part of a composite primary key, so all depend on the whole key. A table with a simple primary key is automatically in second normal form. It eliminates some redundancy. | Third: in addition, no non-key column depends on another non-key column (nothing but the key). It eliminates most redundancy. | Both are cumulative: a second normal form table is also in first, and a third normal form table is also in first and second. (§4.13)

Third normal form vs Boyce-Codd normal form

Third (informal): all non-key columns depend on the key, the whole key, and nothing but the key. | Boyce-Codd: if column A depends on column B, then B must be unique, which eliminates all dependencies on non-unique columns. | Boyce-Codd is an improved version of third normal form, allows less redundancy, and in practice is the most important normal form. (§4.13, §4.15)

Normalization vs denormalization

Normalization: decomposes a table into two or more tables in higher normal form to eliminate redundancy, usually to Boyce-Codd, which is ideal for frequent inserts, updates, and deletes. | Denormalization: intentionally merges tables, introducing redundancy, to eliminate join queries. It results in first and second normal form tables, suits reporting databases where changes are infrequent, and should be applied selectively and cautiously. (§4.15)

Scenario: A game studio's Tournament entity has TournamentTitle (required, unique, long, and renamed by sponsors each season) and PrizeAmount; nothing else is unique. What primary key should the Tournament table get?

Create an artificial key (step 5B), e.g. an integer TournamentID generated automatically as rows are inserted. TournamentTitle is unique and required, so it could serve, but it is long (not simple), descriptive (not meaningless), and renamed (not stable). Artificial keys are stable, simple, and meaningless. (§4.10)

Scenario: In a library database, Copy is a weak entity identified by Book (primary key BookID), and each book has many copies numbered 1, 2, 3 within the book. What are the Copy table's foreign key and primary key?

Foreign key BookID references Book and implements the identifying relationship. Copy is plural in that relationship, so the primary key is the composite (BookID, CopyNumber). The foreign key usually gets cascade on primary key update and delete, restrict on foreign key insert and update. (§4.10)

Scenario: A clinic database has supertype Staff (primary key StaffID) and subtype Nurse with LicenseNumber and UnitCode. How is the Nurse table keyed?

Nurse's primary key is StaffID, identical to the supertype primary key. StaffID is also a foreign key referencing Staff. It implements the IsA relationship and usually has cascade on primary key update and delete, restrict on foreign key insert and update. LicenseNumber and UnitCode are ordinary Nurse columns. (§4.10)

Scenario: Each bike-share Ride starts at exactly one Dock (relationship StartsAt), and each dock has many rides. Dock's primary key is DockID. Where does the foreign key go, what's a good name, and is it required or unique?

In Ride, the 'many' side, referring to Dock. Name it after the referenced primary key with a prefix from the relationship name, e.g. StartDockID. Every ride starts at a dock, so Dock is required and StartDockID is NOT NULL. It is not unique, because Ride is not singular: a dock has many rides. (§4.11, §4.12)

Scenario: Each clinic has exactly one manager (an Employee), each employee manages at most one clinic, and there are far more employees than clinics. How is this one-one relationship implemented?

Put the foreign key in Clinic, the table with fewer rows, to minimize NULL values, e.g. ManagerEmployeeID referencing Employee. It is UNIQUE (an employee manages at most one clinic, so Clinic is singular) and NOT NULL (every clinic has a manager, so Employee is required). (§4.11, §4.12)

Scenario: In a game studio, each Developer works on many Games and each Game has many Developers; HourQuantity records a developer's hours on a game. How is this relationship implemented?

As a new weak table, e.g. DeveloperGame, with foreign keys DeveloperID and GameID referring to the related tables. Its primary key is the composite (DeveloperID, GameID), with PK cascade and FK restrict rules. HourQuantity describes the relationship, so it becomes a column of the new table. (§4.11)

Scenario: A game studio's Game entity has a plural attribute PlatformCode, since a game ships on several platforms. How is PlatformCode implemented?

In a new weak table GamePlatformCode (initial table name followed by the attribute name) containing PlatformCode and a foreign key GameID referencing Game. Primary key: (GameID, PlatformCode), with PK cascade and FK restrict rules. Columns like Platform1Code and Platform2Code in Game only suit a small, fixed maximum, and the new table simplifies queries. (§4.12)

Syntax to implement a table diagram as CREATE TABLE. Ex: Truck (● TruckID, TruckName R, PermitNumber R U, CuisineName, plus LotCode and SpotNumber unique together); glossary: ID and Number → INT, Name → VARCHAR(30), Code → CHAR(3)

CREATE TABLE Truck ( TruckID INT PRIMARY KEY, TruckName VARCHAR(30) NOT NULL, PermitNumber INT NOT NULL UNIQUE, CuisineName VARCHAR(30), LotCode CHAR(3), SpotNumber INT, UNIQUE (LotCode, SpotNumber) ); | Bullet → PRIMARY KEY, R → NOT NULL, U → UNIQUE; unmarked columns may be NULL and are not unique; the composite unique constraint needs its own clause. (§4.12)

Scenario: A bike-share Ride table currently shows that every ride on bike 17 started at dock 4. Can you conclude that StartDockID depends on BikeID?

No. Functional dependence cannot be inferred from values in a table at one point in time; future rides may alter the data. Dependence reflects business rules, so check the rule (must each bike always start at the same dock?) rather than today's rows. (§4.13)

Scenario: Visit (● PatientID, ● VisitDate, PatientName, ReasonText), where PatientName depends on PatientID alone. Which normal form is violated, and what's the fix?

Second normal form: PatientName depends on part of the composite primary key, so (PatientID, PatientName) repeats for every visit. Fix: move PatientName to a new Patient table with primary key PatientID. PatientID stays in Visit, as part of its primary key and as a foreign key to Patient. (§4.13, §4.15)

Scenario: A library's Member table (● MemberID, MemberName, BranchCode, BranchName) has BranchCode → BranchName, and each branch has many members. What normal form issue is this, and what's the fix?

BranchName depends on BranchCode, a non-key column that is not unique, so (BranchCode, BranchName) repeats. Member is in second normal form (simple primary key) but not third ('nothing but the key') or Boyce-Codd. Fix: move BranchName to a Branch table with primary key BranchCode. BranchCode stays in Member as a foreign key. (§4.13, §4.15)

Scenario: NurseShift (● ShiftID, NurseID, ShiftDate, NurseName), where (NurseID, ShiftDate) is also unique and NurseID → NurseName. Is the table in Boyce-Codd normal form?

No. NurseName depends on NurseID, which is not unique: it is contained within the composite unique column (NurseID, ShiftDate). So (NurseID, NurseName) repeats on every shift. Fix: remove NurseName into a new Nurse table with primary key NurseID. NurseID stays in NurseShift as a foreign key, and every dependency is then on a unique column. (§4.15)

Scenario: A food-truck chain copies sales data nightly into a reporting database that is queried all day and rarely updated, and analysts complain about slow multi-table joins. What does the book suggest?

Consider denormalizing (step 8C): merge tables to eliminate join queries and improve query performance. Redundancy is acceptable in a reporting database because changes are infrequent, and it makes processing faster and queries simpler. Merged tables end up in first or second normal form, so denormalize selectively and cautiously. The order-taking database, with frequent inserts, updates, and deletes, stays in Boyce-Codd normal form. (§4.15)

Scenario: A clinic loads a lab vendor's file into a table that has duplicate rows and no primary key. Is that allowed, and what should happen next?

In practice databases allow it, but such tables are usually temporary and are not in first normal form, which requires a primary key. When the data moves to a permanent table, the duplicate rows are removed and a primary key is created. (§4.13)