Difference between revisions of "Database Design"

From Colettapedia
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., interchangeable
+
** 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.

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

  1. Conceptual Data Model - technology independent. Identify entities.
  2. Logical Data Model - documents structures that can be implemented in databases. Descriptions of tables, columns, OO classes
  3. 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

  1. Strategic - do it before
  2. Systems analysis - do it during