Database Management Notes

Database Management Overview

  • Instructor: Frank Gao, Foster School of Business
  • Course Code: IS 445 A

Data Modeling

  • Understanding the structure of data is crucial in database design.
  • Focuses on the relational schema that connects different data entities.

Learning Roadmap: Steps in Database Design

  1. Develop a Conceptual Schema (ERD)
    • ERD: Entity-Relationship Diagram, visually represents data relationships.
  2. Convert ERD to Relations (Tables)
    • Relations correspond to entity types in the ERD.
  3. Normalization
    • Process of organizing tables to reduce redundancy and dependency.

Understanding Relations (Tables)

  • Definition: A relation is a named, two-dimensional table of data consisting of:
    • Rows (records)
    • Columns (attributes/fields)
  • Differences: A relation in databases should not be confused with relationships in E-R models.

Six Requirements for a Table to Qualify as a Relation

  1. Unique Name: Each table must have a unique identifier.
  2. Atomic Values: Each attribute must hold atomic (indivisible) values, not multivalued.
  3. Unique Rows: No duplicate rows are allowed. Each row must be distinct.
  4. Unique Column Names: Columns must always have unique names within the table.
  5. Irrelevant Column Order: The order of columns should not impact the meaning of the data.
  6. Irrelevant Row Order: The sequence of rows should not impact the meaning.

Mapping Entities & Attributes to Relations

  • Entities correspond to Relations: Each entity in the ERD maps to a table.
  • Rows & Columns: Row instances are mapped to entity instances, and columns represent attributes.
    • Example: For an Employee entity, fields like Employee ID, Name, Dept_Name, and Salary become corresponding columns in a table.

Relation Notation

  • Textual Representation:
    • Example: Employee1(employee_ID, name, dept_name, salary)
  • Graphical Notation: Visual representation of relations for clearer understanding.

Mapping Simple, Composite, and Multivalued Attributes

  • Simple Attributes: Directly map to relational fields.
    • Example: Customer ID mapped to a Customer table.
  • Composite Attributes: Break these into multiple columns (e.g., address can be split into Street, City, State).
  • Multivalued Attributes: Create a new relation to accommodate multiple values; for example, a Volunteer could have multiple Phone numbers stored in a separate Volunteer_skill table linked by a foreign key.

Primary Key and Foreign Key Concepts

  • Primary Key: Unique identifier for records in a table. Indicated with an underline in notation.
  • Foreign Key: A column that creates a link between two tables, referencing primary keys from another table.

Referential Integrity

  • Ensures consistency across tables; a foreign key must correspond to an existing primary key to maintain data integrity.

Examples of Mapping Relationships to Relations

  • One-to-One Relationship: Both tables can have foreign keys linking back to each paired record.
  • One-to-Many Relationship: The primary key from the one side becomes a foreign key on the many side.
  • Many-to-Many Relationship: Requires an associative table that holds primary keys from both combined tables.

Handling Unary Relationships

  • Unary Relationships: Can exist within the same entity, requiring a recursive foreign key (e.g., Employee manages another Employee).

Supertype/Subtype Relationships

  • Structure: Create a table for supertype and for each subtype. Supertype attributes join with subtype attributes to maintain a structured relationship.

Class Examples of ERD to Relations

  • Converting specific entity examples (like Movies and Theaters) into relational tables with a focus on key attributes and relationships helps solidify understanding of concepts.

Practice Exercises

  • Includes exercises to reinforce skills in converting ERDs to relational schemas, providing hands-on experience with the material.