Relational Database Design
Physical Data Model

• Extracting data from operational systems;
• Moving it into data warehouse structures;
• Reorganizing and structuring the data for analysis purposes; and
• Moving it into reporting structures called data marts. 

• Relational database design; and
• Dimensional, or star schema design.

• Conceptual data model  is created at the information management strategy stage.  It contains key entities and relationships and presents a high level look at all of the entities within an organization.

• Enterprise data model is an entity relationship diagram which builds on the conceptual data model and adds additional details;

• Logical data model, is a fully attributed entity relationship diagram (ERD), which shows each entity, its relationship to other entitles and specifies the applicable business rules;

• Dimensional model, represents facts and dimensions, which are required to store dimension data in an efficient manner for reporting and analysis purposes; and

• Physical data model, is the final representation of the relational database design structures that will be generated from the model. It contains the detailed specifications for the database design and, in a model driven environment, the modeling tool will generate the data definition language (DDL) that is used to create the database structure. The physical model can be used to generate:

• Databases;
• Tablespaces
• Files;
• Instances;
• Tables, columns and datatypes;
• Constraints;
• Partitions;
• Indexes; and
  
• Views.

• Primary key constraint e.g. customer_number might be defined as a primary key. This means that in must also be NOT NULL and must be unique--Attempts to insert a duplicate customer_number will be rejected by the RDBMS;
• NOT NULL constraint means that a value must be inserted when a row is created e.g. if customer_account_number is defined as NOT NULL then attempts to insert a row without a customer_account_number will be rejected;
• Default is another type of constraint the is used to insert a default value for a column whenever a row is inserted into the table and no value is entered for the column;
• Check constraint is used to ensure that a value inserted in a column meets a predefined value e.g. transaction amount must be less than 100,000;
• Unique constraint specifies that the value in a column may only occur once in the table e.g. in a country name table, a country name can only occur once; and
  
• Foreign key constraint is defined when two tables are related and the value in one table is dependent upon finding a value in a related table e.g. address table may include a country code column which is a foreign key relationship, or constraint, to country code which is in country name table. Attempts to insert a country code in the address table, that does not exist in the country name table, will fail.

• A bitmap index might be created when there are few distinct values in a column and are quite useful for static data;
  
• A cluster index is created for tables that  are frequently accessed together. The data in the tables is stored together to minimize inserts and retrievals and the related columns of the table are called the cluster key. The cluster key is indexed using a cluster index and it's value is only stored once for the multiple tables in the cluster;
• Hash clusters use a hashing function on the row's cluster key to determine the physical location where the row will be stored.