Introduction to Database Design and Development

Fundamental Definitions and Concepts

  • Database: An organized collection of related data stored and accessed electronically.
  • Database Management System (DBMS): General-purpose software package used to facilitate the creation, maintenance, and manipulation of a computerized database.
  • Database System: The combined entity of the DBMS software, the stored data, and sometimes the associated applications.
  • Mini-world: The specific part of the real world about which data is stored (e.g., university student grades).
  • MetaData: "Data about data" or self-describing definitions stored within the system.

Types of Databases and Software

  • Relational Databases: Data organized into tables with unique keys (e.g., MySQL, PostgreSQL, Oracle, Microsoft SQL Server).
  • NoSQL Databases: Handle unstructured data without fixed schemas (e.g., MongoDB, Cassandra, Couchbase).
  • NewSQL Databases: Combine NoSQL scalability with SQL consistency (e.g., Google Spanner, CockroachDB).
  • In-Memory Databases: Store data in RAM for high-speed access (e.g., Redis, Memcached).
  • Distributed Databases: Data stored across multiple physical locations (e.g., Amazon DynamoDB).

Core Functions and Advantages of DBMS

  • Functions: Handles data storage, data integrity (constraints), security (access control), backup and recovery, and concurrency control for simultaneous access.
  • Advantages: Controlled redundancy, data consistency, sharing among multiple users, and program-data independence.
  • Disadvantages: System complexity, large software size, high cost, and potential for large-scale system failures.

The Relational Model and SQL

  • Relational Model: Devised by Edgar Codd circa 1970; represented by relations (tables), tuples (rows), and attributes (columns).
  • SQL (Structured Query Language): The standard language for relational databases involving Data Definition Language (DDL) and Data Manipulation Language (DML).
  • DML Statements:
    • INSERT: Adds data.
    • UPDATE: Modifies data.
    • DELETE: Removes data.
    • SELECT: Retrieves data.
  • SQL Syntax Example: SELECT lastName FROM Customers;.

Database Design Process

  1. Requirements Analysis: Identifying data needs with stakeholders.
  2. Conceptual Design: Creating high-level Visual representations like Entity-Relationship Diagrams (ERD) or UML.
  3. Logical Design: Mapping the ER model to a specific DBMS type (e.g., Relational) and applying normalization.
  4. Physical Design: Determining storage structures, access methods, indexing, and partitioning.
  5. Implementation: Using DDL to create the schema and DML to populate the database.
  6. Testing and Refinement: Stress testing and validation.

Normalization and Integrity

  • Normalization Goals: Eliminate redundancy and prevent Update, Insert, and Delete anomalies.
  • First Normal Form (1NF): Atomic values; no repeating groups.
  • Second Normal Form (2NF): 1NF plus no partial functional dependencies (all non-key attributes depend on the entire primary key).
  • Third Normal Form (3NF): 2NF plus no transitive dependencies (attributes depend only on the primary key).
  • Key Types:
    • Primary Key (PK): Unique identifier for a record.
    • Foreign Key (FK): A link in one table to the PK of another to enforce referential integrity.
    • Composite Key: A PK made of multiple columns.

Advanced System Concepts

  • ACID Properties for Transactions:
    • Atomicity: All or nothing execution.
    • Consistency: Database remains in a valid state.
    • Isolation: Transactions are independent during execution.
    • Durability: Committed data survives system failures.
  • Data Abstraction Levels:
    • Physical: How data is stored on disk.
    • Logical: What data is stored and its relationships.
    • View: How specific users see the data.
  • Data Independence: The ability to change the schema at one level (Logical or Physical) without affecting the levels above it.