Database Design and Programming


A database design is a specification of database objects such as tables, columns, data types, and indexes.

Design Phases

  • 3 phases
    1. Conceptual design
    2. Logical design
    3. Physical design

Conceptual design phases specifies database requirements without regard to a specific database system.

  • aka analysis, data modeling, requirements definition
  • requirements are represented as:
    • entities
    • relationships
    • attributes
  • entities, relationships, and attributes are depicted in ER diagrams

Logical design phase implements database requirements in a specific database system.

  • in a relational database system,
    • converts entities, relationships, and attributes into tables, keys, and columns
    • a key is a column used to identify individual rows of a table
    • specified in SQL with CREATE TABLE statement
  • depicted in a table diagram
    • more detailed than ER diagram
  • called a database schema

Physical design phase adds indexes and specifies how tables are organized on storage media.

  • e.g., rows of a table may be sorted on the values of a column and stored in sort order
  • logical and physical design affect queries differently
    • logical design affects the query result
    • physical design affects query processing speed but never the result

Data independence is the principle that physical design never affects the query result.

  • aka information independence
  • allows database designers to tune query performance without changes to application programs

Programming

  • SQL lacks programming features
    • most SQL implementations are not object-oriented
  • SQL is usually combined with a general purpose programming language
    • programming languages use an API to implement SQL operations
      • e.g., connecting to database, executing queries, and retrieving results