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
- Conceptual design
- Logical design
- 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 TABLEstatement
- 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
- programming languages use an API to implement SQL operations