Key Concepts in Relational Database Design

Steps in designing a relational database

Requirements and Specification, Conceptual Design, Logical Design.

Importance of database design

Foundation for good applications, communication tool, both an art and a science.

Data model

Simplified abstractions of real-world events or conditions that represent data characteristics, relationships, constraints, and transformations.

Entities in a data model

Object of interest (e.g., person, place, thing).

Attributes in a data model

Characteristics of an entity (e.g., name, age).

Relationships in a data model

Associations between entities (e.g., one-to-many).

Business rules

Brief, precise descriptions of policies, procedures, or principles within an organization.

What is a database schema

Logical view of the database (changes infrequently)

Three-Schema Architecture

Internal Schema: Physical storage details. Conceptual Schema: Structure and constraints for the whole database. External Schemas: User views.

Logical Data independence

Change conceptual schema without changing external schemas.

DBMS languages

DDL: Data Definition Language (specifies schema). DML: Data Manipulation Language (retrievals and updates).

Types of data models

Network, Hierarchical, Relational, Object-Oriented, Object-Relational.

ERD

Entity Relationship Diagram: Graphical representation of entities and their relationships.

Types of relationships in an Entity Relationship Diagram

One-to-Many (1:M), Many-to-Many (M:N), One-to-One (1:1).

What is a database state

Actual data stored at a particular moment (changes with updates).

Physical Data Independence

Change internal schema without changing conceptual schema.