Difference between revisions of "Database Design"
Jump to navigation
Jump to search
(Created page with "==Database Models== ===Hierarchical=== * Organizes data into tree structure. * Parent and Child Data segments * '''1:N mapping''' between record types * Child data node can ha...") |
|||
Line 1: | Line 1: | ||
+ | * Database design - the logical design of the base data structures used to store data. | ||
==Database Models== | ==Database Models== | ||
===Hierarchical=== | ===Hierarchical=== | ||
Line 15: | Line 16: | ||
** Each row is unique | ** Each row is unique | ||
** Column values are of the same kind | ** Column values are of the same kind | ||
− | ** Sequence of rows and columns is insignificant, i.e., | + | ** Sequence of rows and columns is insignificant, i.e., no default sorting |
** each column has a unique name | ** each column has a unique name | ||
* Designate certain fields as '''keys''' to optimize searching of that field | * Designate certain fields as '''keys''' to optimize searching of that field | ||
Line 21: | Line 22: | ||
* Robust transaction and performance management features | * 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 | * ''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=== | ===Object/Relational Model=== | ||
* Adds new object storage capabilities to relational: traditional fielded data, plus complex objects | * Adds new object storage capabilities to relational: traditional fielded data, plus complex objects | ||
Line 35: | Line 47: | ||
* Divides data into '''entities''' (items), and '''associations''' (links) | * Divides data into '''entities''' (items), and '''associations''' (links) | ||
* Source, verb and target | * 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 |
Revision as of 17:48, 17 March 2014
- 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