Database Design
Jump to navigation
Jump to search
- Database design - the logical design of the base data structures used to store data.
Contents
Database Models
Hierarchical
- Organizes data into tree structure.
- Parent and Child Data segments
- 1:N mapping between record types
- Child data node can have only one parent
Network
- Based on mathematical set theory
- More than one parent per child
- The "set construct": An owner record type, a set name, and a member record type.
Relational
- Defines data structures, storage and retrieval operations, integrity constraints
- Organized into tables with following properties:
- Values are atomic
- Each row is unique
- Column values are of the same kind
- Sequence of rows and columns is insignificant, i.e., no default sorting
- each column has a unique name
- Designate certain fields as keys to optimize searching of that field
- join operation: take values from two different tables matched by common field
- Robust transaction and performance management features
- A complex data structure must be flattened out to fit into tables or joined together from those tables, which takes overhead
- base structures
- table - can have any number of rows, columns specified by schema, cells are intersection, row id'ed by unique key
- views - a virtual table computed or collated dynamically
- Provides different way to see data rendered form "the base table" or "the underlying table"
- The result set of a stored query on the data. A pre-established query command is kept in the database dictionary
- Not part of the physical schema
- Can represent a subset of data, limiting degree of exposure, permissions, etc
- Can join and simplify multiple tables into a single virtual table, aggregate tables
- Hide complexity of data
Object/Relational Model
- Adds new object storage capabilities to relational: traditional fielded data, plus complex objects
- SQL and Oracle are here
Object-Oriented Model
- Manip DB using C++, Java, no need to translate calls into SQL
- Less code, more natural data modelling.
- one-to-one mapping of DB objects and OO programming instances
Semi-structured Model
- Flexible, when you don't know the schema ahead of time.
- The schema is in the data, "self-describing"
- Modelled in terms of graphs which contain lables which give semantics to its underlying structure
Associative Model
- Divides data into entities (items), and associations (links)
- Source, verb and target
Data Modelling
- elements, structure of elements, relationships between elements
Data modelling Steps
- Conceptual Data Model - technology independent. Identify entities.
- Logical Data Model - documents structures that can be implemented in databases. Descriptions of tables, columns, OO classes
- Physical Data model - organizes data into tables, accounts for access, performance and storage details. Doubles, int. The physical means to store the data. Partitions, CPUs tablespaces. IO data structures.
- Data definition language - converts schema to database.
Data Modelling strategies
- Strategic - do it before
- Systems analysis - do it during