'Data Model' provides the overall data architecture and layout. It establishes all the entities, their interrelationships, their attributes and all the allowables and not-allowables in their values. To ensure a proper data quality, it is important that firstly an organization has a comprehensive data model, as part of the Data Quality Program. A Data model should drive the database design and validations to ensure that any data, which is not in line with the data model does not enter into the organization.
Data Entity Primary Key –
Every physical entity (table) has to have a primary key OR unique identifier defined. This includes all the 'master tables' and the 'Transaction tables'. Typically Customer ID, Supplier ID, Product ID, Diagnosis ID etc. are primary keys in the master tables. Order No., Registration ID, Policy ID, Credit card ID, Job Card Number etc. are the typically the primary keys of transaction tables.
Foreign Key OR referential integrity across data entity relationships –
Any two tables relate with each other through the foreign keys. This ensures that the link exists across the two tables. For example one cannot have the order details added in the order table, if the supplier ID (Foreign key for the order table) mentioned in the records is not existing in the supplier master table (where supplier ID is the primary key)
Mandatory vs. optional Data Entity Attribute
Value in a column can be mandatory (it has to have some value) OR optional (it may OR may not have a value)
Entity Relationship Cardinality between Data Entities
ER model Cardinality is can be - 'One-to-one' cardinality- a state can have only one capital.
- 'One to many' cardinality- a state can have many cities.
- 'Many to one' cardinality- all the branches can be linked to only one head office.
- 'Many to many' cardinality- a product can be sold through many channels and a channel can sell many products.
Data Model Insert Rules for Data Entities
- Dependent- The dependent insert rule permits insertion of child entity instance, only if matching parent entity already exists. Automatic. The automatic insert rule always permits insertion of child entity instance. If matching parent entity instance does not exist, it is created.
- Nullify- The nullify insert rule always permits the insertion of child entity instance. If a matching parent entity instance does not exist, the foreign key in child is set to null.
- Default- The default insert rule always permits insertion of child entity instance. If a matching parent entity instance does not exist, the foreign key in the child is set to previously defined value.
- Customized- The customized insert rule permits the insertion of child entity instance, only if certain customized validity constraints are met.
- No Effect- This rule states that the insertion of child entity instance is always permitted. No matching parent entity instance need exists, and thus no validity checking is done.
Data Model Delete Rules for Data Entities
- Restrict- The restrict delete rule permits deletion of parent entity instance, only if there are no matching child entity instances.
- Cascade- The cascade delete rule always permits deletion of a parent entity instance and deletes all matching instances in the child entity.
- Nullify- The nullify delete rule always permits deletion of a parent entity instance. If any matching child entity instances exist, the values of the foreign keys in those instances are set to null.
- Default- The default rule always permits deletion of a parent entity instance. If any matching child entity instances exist, the value of the foreign key is set to a predefined default value.
- Customized- The customized delete rule permits deletion of a parent entity instance, only if certain validity constraints are met.
- No Effect- The no effect delete rule always permits deletion of a parent entity instance. No validity checking is done.
|
