Chapter 4: Physical Database Design and E-R Diagrams Study Guide

Physical Database Design

Focuses on how data is stored and accessed in a database.

Data Structures

Methods for organizing and storing data.

Flat Files

Can be ordered using various data structures.

Sequential Lists

Records are physically placed in the order they will be processed.

Linked Lists

Each data record has a pointer to another related record.

Indexes (Inverted Lists)

A separate table contains pointers to related records.

B-Trees

Multilevel indexes for both sequential and direct data access.

Sequence Set

Index containing an entry for every record in the file, usually by primary key value.

Index Set

Index pointing to groups of entries in the sequence set data.

Balanced Trees

All data records are equidistant from the top entry in the index set.

Tree Relationship

One or more 1:N relationships where each child record has only one parent record.

Simple Network

Collection of records and 1:N relationships among them.

Complex Network

Collection of records and N:M relationships among them.

Representing Trees

Sequential lists, linked lists, and indexes can represent trees.

Representing Complex Networks

Decomposition into trees or simple networks; using indexes; linked lists are generally not used by DBMS products for complex networks.

Key

A field that uniquely identifies a record; usually the primary key.

Secondary Keys

Used to access data based on fields other than the primary key.

Unique

Can only be represented with indexes.

Nonunique

Can be represented with linked lists and indexes.

Set

All records with the same value of a non-unique secondary key.

Good candidates for indexing.

- Columns Used in ORDER BY Clause

- Columns Usable Without Examining Table Data

- Columns Matching a Range of Values

- Join Columns

- Columns Compared for Equality

Multi-Column Indexing

Sorting by multiple columns.

Clustering

Records from two or more tables are stored in the same file physically, even if they are logically separate.

Implementation

Adds a table identifier to each record.

Decomposition

Splitting a table into two or more tables to improve query performance.

Partitions

Resulting tables from decomposition.

Horizontal Decomposition (Sharding)

Rows are split into multiple smaller tables.

Vertical Decomposition

Partitions have the same number of rows but only a subset of the columns, each containing a copy of the primary key of the original table.

IDEF1X

A variation of the entity-relationship (E-R) model that assumes a relational database will be used.

Unified Modeling Language (UML)

Structures and techniques for modeling and designing object-oriented programs (OOP) and applications.

Nonidentifying Connection Relationships

1:1 or 1:N relationships between non-ID-dependent entities, same as HAS-A relationships.

Identifying Connection Relationship

Same as ID-dependent relationships.

Nonspecific Relationships

Same as many-to-many (N:M) relationships.

Categorization Relationships

IS-A relationships; generic entity (supertype) and category entity (subtype).

Domains

A named set of values that an attribute can have.

Domain Types

Different classifications of domains.

Base Domain

Data type and possibly a value list or range definition.

Type Domain

Subset of a base domain or another type domain.

Strong Entities

Represent 1:1, 1:N, and N:M HAS-A relationships.

Weak Entities

A filled-in diamond is added to the line adjacent to the parent of the weak entity.

Subtypes

Represents IS-A relationships.

Visibility of Attributes and Methods

Indicates the access level of attributes and methods in UML.

Public attribute

`+` (plus sign) indicates a public attribute.

Protected attribute

`#` (number sign) indicates a protected attribute.

Private attribute

`-` (minus sign) indicates a private attribute.

Constraints and Methods

Constraints such as PrimaryKey, and methods such as GetName() and SetName().