Database Design Fundamentals Chapter 2

Chapter 2: Database Design Fundamentals

• Database design is the process of determining the tables and columns needed to create a database.
• Understanding core database concepts and the process of normalization are critical for effective database design.

Objectives

• Understand the terms entity, attribute, and relationship.
• Understand relation and relational database.
• Understand functional dependence and recognize when one column is dependent on another.
• Know the concept of a primary key and be able to identify primary keys in tables.
• Design a database to meet specific requirements.
• Convert an unnormalized relation into first normal form.
• Convert tables from first normal form to second, then third normal form.
• Create an entity-relationship diagram to represent database design.

Introduction

• Database design determines the tables and columns comprising a database.
• Database concepts and normalization are essential.

Database Concepts

• Entity: person, place, thing, or event.
• Attribute: property of an entity (like a characteristic or description).
• Relationship: association between entities.
• Functional dependence: one attribute determines another attribute's single value.
• Primary key: unique identifier for a table.

Relational Database

• A collection of tables.

Relational Database (Continued)

• The provided example data (Figures 2-1, 2-1(continued), 2-1(continued), 2-1) shows sample data for Premiere Products. This includes details like sales representatives, customer information, and product information.

Entities, Attributes, and Relationships

• Entities (like nouns): represent things or concepts.
• Attributes (like adjectives): describe entities.
• Relationships: describe associations between entities.

Entities, Attributes, and Relationships (Continued)

• One-to-many relationship: one representative to many customers (for example). Implements by having a common table column.
• Repeating groups: multiple entries for a single location in an individual table.

ORDERS

• This example table (Figure 2-2) shows order data with repeating groups.
• Figure 2-3 presents the same data without the repeating groups

Entities, Attributes, and Relationships (Continued)

• A relation (table) is two-dimensional.
• Entries are single-valued.
• Each column has a distinct name.
• All values in a column are from the same attribute (data type).
• Order of columns does not matter.
• Each row is distinct and order of rows does not matter.

Entities, Attributes, and Relationships (Continued)

• Use shorthand to represent tables and columns in a concise format.
  • REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE)
  • CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUM)
  • ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM)
  • ORDER_LINE (ORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICE)
  • PART (PART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE)

Functional Dependence

• Attribute B is functionally dependent on attribute A if a value for A determines a single value for B.
• Sample data does not define functional dependency, but understanding users' policies and rules is critical.

Functional Dependence

• Figure 2-4 displays a REP table with a PAY_CLASS attribute.

Primary Keys

• A table's unique identifier is the primary key.
• A column (or combination of columns) is a primary key if all columns in the table depend on it and no subset of columns has this property

Database Design

• A database is designed based on specific requirements.
• Requirements are gathered through systems analysis.

Design Method

• Read requirements, identify entities, and name them.
• Identify unique identifiers for entities.
• Identify attributes for all entities.
• Determine functional dependencies among attributes.
• Use functional dependencies to identify tables.
• Place each attribute with the minimum attributes or combinations on which it depends.
• Identify relationships between tables.

Database Design Requirements

• For Premiere Products: store data about sales reps, customers, parts, orders, and order line items.
• Specific constraints, like only one customer per order, may also apply. The quoted price might differ from the final price agreed upon in the order.

Database Design Process Example

• Apply requirements through six steps in the database design method.

Normalization

• Identify potential problems.

• Correct problems using normalization techniques to eliminate redundant data.

• Goal is to convert unnormalized relations (with repeating groups) into normal forms.

Normalization (Continued)

• First Normal Form (1NF): eliminates repeating groups from unnormalized relations.
• Second Normal Form (2NF): improves upon 1NF by ensuring that non-key attributes fully depend on the entire primary key.
• Third Normal Form (3NF): improves upon 2NF by eliminating transitive dependencies.

First Normal Form (1NF)

• Relation is in 1NF if it does not contain any repeating groups.
• To convert an unnormalized relation to 1NF: expand the primary key to include the primary key of the repeating group; effectively eliminating the repeating group from the relation.
• Figure 2-7 presents an unnormalized example.
• Figure 2-8 provides a 1NF version of the same data.

Second Normal Form

• Redundancy leads to update anomalies (inconsistencies in data when adding, updating, or deleting records).
• A relation is in 2NF if it's in 1NF and no non-key attribute depends on only part of the primary key – all non-key attributes depend on the entire primary key.
• A primary key made up of a single field is automatically in 2NF.

Third Normal Form

• Update anomalies can still occur in 2NF.
• Determinant: an attribute (or set of attributes) that determines another attribute.
• Relation is in 3NF if it is in 2NF and the only determinants are candidate keys. Boyce-Codd Normal Form (BCNF) is a more precise name for 3NF.
• Figure 2-12 shows a Customer table converted to 3NF.

Diagrams for Database Design

• Graphical illustration of table structure.
• Entity-relationship (E-R) diagram: rectangles denote entities; arrows show relationships (one-to-many).
• Figure 2-13, 2-14, and 2-15 show different E-R diagrams.

Summary

• Definitions: entity, attribute, relationship, relation, functional dependence, primary key.
• Database design method.
• Normalization: unnormalized, 1NF, 2NF, 3NF.
• Entity-relationship (E-R) diagram

This quiz focuses on the essentials of database design, covering concepts such as entities, attributes, relationships, and normalization principles. You'll examine how to identify primary keys, understand functional dependence, and create entity-relationship diagrams. Test your knowledge on converting relations to different normal forms and the overall principles of effective database design.