LESSON-8 E-R MODELLING BY RULES (Cont’d; Ashraf, Khan, and Mir)
LESSON-8 E-R MODELLING BY RULES (Cont’d; Ashraf, Khan, and Mir)
8.1 Definitions/ Rules
Definition: Paradigm of Business System Modelling consists of two major breakdown of application system, one is holding explicitly the business transaction effects (insert delete and update) in right perspective and appropriate context, another key component is the business operational behaviour which causes the transactional effect above noted. The right perspective of storing the transactional facts and events allow the end-user to derive the desired information or knowledge instantly in explicit presentably the way he/she perceives.
Definition: A Transaction means a logical unit of work. The transaction processing carries out the predefined integrity checks on individual filed values, and records their
appropriate effects what is called inserts/ deletes/ updates intact.
Definition: Concurrent Transactions processing allows to execute transaction simultaneously in multi-tasking and time sharing environment.
Definition: On Line Transaction Processing (OLTP) allows concurrent online transaction processing (without getting involved in issues of concurrency, locking and task management) in any environment including; client-server or 3-tier or more.
Comments: E-R modeling tools uses tables because they are Simple, Precise, Flexible, and Concise.
Definition: Table is a two dimensional graphic representation of data consisting of columns and rows. Real-world facts or objects are physically stored as a set of rows in a table.
Rule: Table name must be unique within a system.
Rule: Column name must be unique within a table.
Rule: Columns except Date and Time must be non-decomposable.
Rule: Column Order; is arbitrary.
Rule: Rows Order; is arbitrary.
Definition: A domain is fundamental type of information managed to define column characteristics.
Rule: Two columns are based on the same domain if corresponding values in each column.
Definition: A fully qualified column name is the form; tablename.qualifier_domain
Definition: A null value is a missing entry in a column of a table.
Rule: Column marked Not null (NN) must not contain null value.
Rule: A duplicate value is a value (in a column) or group of values (in more than one column) of a row that exactly equal another value (in another row) or group of values (in another row) in the same columns.
Rule: Columns or group of columns marked Non Duplicate (ND) must not contain duplicate values in the rows, and no component should be null. ND implies NN.
Rule: Primary Key values must ND, as whole single or composite PK.
Rule: Existing Primary Key in case of composite part thereof must not be modified.
Rule: Primary Key that are assigned (by some algorithm) by the system are indicated by the letter SA (system assigned) under the PK column(s).
Definition: A primary key (PK) is a column or group of (non-superfluous) columns that insure the uniqueness (ND) of rows within a table. A primary key that consists of more than one column is a composite primary key.
Rule: Primary key indicated by the letter PK under the appropriate column for single PK, and columns for composite PK. If more than one column are marked PK that means composite PK. The PK implies ND, however these column(s) values are used by the system to identify row in order to add (single instance)/ update/ delete row or retrieve row that is beyond the scope of the characteristics of ND.
Rule: Every table must have a PK that may be single or composite, and should be unique with the database.
Rule: Primary Key must never be null even their component(s) in case of composite PK.
Definition: A Foreign Key is a column, or group of columns which shares the same domain(s) respectively of the Primary Key that is referred in the child table.
Definition: The Parent & Child relationship of tables (entities) defines the flow dynamics of facts in the E-R model tables, to map the business transactions. Normally, an instance exists in the parent table (where the Foreign Key domain(s) constituted the Primary Key) that is referred by the child.
Definition: Referential Integrity means that every occurrence of a Foreign Key must refer to existing occurrence of a Primary Key.
Rule: Foreign Key is indicated by the letter FK under the appropriate column(s).
Rule: A child entity can have multiple parent entities (a set of parent level entities), in that case there would be as many FKs in the child entities as parents entities, and each FK column of a child entity inherits from one and only one parent entity.
Rule: If a entity has at-least one column marked FK that is a child entity of some parent entity, however, in the parent child flow it could have its own child entities. It adds further complexities in case one-to-one with unary cardinality and various combinations of optional and mandatory.
Rule: The two entities A and B can have one only one association for life time, as such the associations types one one-to-one, one-to-many, and many-to-many are mutually exclusive.
Rule: Entire Primary Key value (single or composite) of the parent table is referred as the Foreign Key in the child table.
Rule: Foreign Key values (single or composite) must not be null, and should exist in the table where the Foreign Key domains constitute the Primary Key.
Definition: An association (parent and child) between the two or more entities (entities of interest to the user community) about which the system is to maintain, correlate and display information.
Definition:The indicated associations occur in three forms; one-to-one, one-to-many, and many-to-many. In one parent child relationship one of these occurs.
8.2 One-to-One Association
Definition: One-to-one association occurs when two entities between A and B where each occurrence of entity A is related to at-most one occurrence of entity B (viewing from parent side), and in each occurrence of entity B (from child side) is related to one
and only one occurrence of entity A.
Designing Rule: One-to-one associations are modeled by placing the primary key of parent entity as FK with an additional constraint No-duplicate (ND) in the child entity.
Comment: One-to-one associations are relatively rare.
Definition: The choice of an entity to be taken as parent or child is not arbitrary. How to workout the parent and child entity?
Rule: The practitioner looks at the occurrence of which entity is assigned to whom. For instance, if occurrence of B is assigned to at-most one occurrence of A, then B is the child entity and A is the parent entity.
That would require PK of A to put as FK in B. If occurrence of A is assigned at-most one to occurrence B, in that case the PK of B will appear as FK of A, and B will be parent and A will be the child.
Rule: A wrong choice of parent and child will create design anomalies, (in the form of FK as Null where FK is the king pin of joining the two tables by JOIN operation).
Definition: Mandatory instance means an occurrence must exist, Optional instance means an occurrence may exist.
Comments: In the scenario of parent and child entities relationship, the practitioner is also required to pin down exactly which one is applicable;
one-to-one association (mandatory to optional)
one-to-one association (mandatory to mandatory)
one-to-one association (optional to mandatory)
8.3 One-to-Many Association
Definition: One-to-many association occurs when two entities between A and B where each occurrence of entity A is related to at-most many occurrence of entity B (viewing from parent side), and in each occurrence of entity B (viewing from child side) is
related to one and only one occurrence of entity B.
Designing Rule: One-to-many associations are modeled by placing the primary key of parent entity as FK in the child entity.
Comment: One-to-many associations are relatively common.
Definition: The choice of an entity to be taken as parent or child is not arbitrary.
How to workout the parent and child entity?
Child Entity Rule: The practitioner identifies which entity is assigned to whom in occurrence.
For instance, if occurrence of B is assigned to at-most many occurrence of A, then B is the child entity and A is the parent entity.
That would require PK of A to put as FK in B. If occurrence of A is assigned at-most many to occurrence B, in that case the PK of B will appear as FK of A, and B will be parent and A will be the child.
Rule NULL FK: A wrong choice of parent and child will create design anomalies, (in he form of FK as Null where FK is the king pin of joining the two tables by JOIN operation).
Definition: Mandatory instance means an occurrence must exist, Optional instance means an occurrence may exist.
Comment: In the scenario of parent and child entities relationship, the practitioner is also required to pin down exactly which one is applicable;
one-to-many association (mandatory to optional)
one-to-many association (mandatory to mandatory)
one-to-many association (optional to mandatory)
8.4 Many-to-Many Association
Definition: Many-to-many association occurs when two entities between A and B where each occurrence of entity A is related to zero, one or more occurrence of entity B (viewing from parent side), and each occurrence of entity B (viewing from child side) is related to zero, one or more occurrences of entity B.
Many-to-many associations are modeled by defining a new table with a composite primary key. The components of the new primary key are the primary keys of the entity A and entity B. Both the components of the new primary keys are also individual
foreign keys.
Comment: Many-to-many association is quite common.
Comment: Many-to-Many associations may occur with unary, binary, ternary, nary relationships, which are defined the same as above noted many-to-many association, and are designed the same way as above.
Definition: The choice of an entity to be taken as parent or child is not arbitrary.
How to workout the parent and child entity?
Guideline: Since many-to-many associations are symmetrical, so the order the column in constituting composite PK is immaterial.
In many-to-many associations the practitioners are required to present such entities in a most familiar fashion, as they are most commonly maintained, say, student_course registration as the registration table.
Practitioner’s Rule: Occurrence of entity A assigned to many occurrence of entity B, as well as, occurrence of entity B assigned to many occurrence of entity A.
Rule-1: An associative entity is configured with composite PK for uniqueness of a row occurrence, and all it components as FK, commonly independently, however under the situations these could be marked as composite FK, if the parent entity has composite PK.
Rule-2: Associative entities are always model many-to-many association, and many-to-many associations are always modelled in associative entity.
Instance Definition: Mandatory instance means an occurrence must exist, Optional instance means an occurrence may exist.
Comments: In the scenario of parent and child entities relationship, the practitioner is also required to pin down exactly which one is applicable;
many-to-many association (mandatory to optional)
many-to-many association (mandatory to mandatory)
many-to-many association (optional to mandatory)
Comments: In the above scenario, the professionals are also required to interpret many of both sides, in terms of count, as constraint on them.
*****
Click To MAIN PAGE: OBJECT- ORIENTED RELATIONAL MODELLING E-MONOGRAPH, By J. Ashraf, M. Khan, and H. Mir
*****
MESSAGE: DEDICATED TO ANONYMOUS COMBATANTS OF KNOWLEDGE
مکتب علم الل مہد منل لحد Learning continues from birth to death
FOR PROMOTION OF LEARNEDNESS SHARE WITH FRIENDS ABOUT;
- CAUSE AND EVENTS – SPATIAL And TEMPORAL RESPECTIVELY: https://be4gen.wordpress.com/2012/11/16/cause-and-events-spatial-temporal-respectively/
- WHETHER ‘BRAIN-POWER’ OR ‘HEART-IMPULSE’ PLINTHS ‘HUMAN GEN’? http://sunedu.wordpress.com/2012/09/17/whether-brain-power-or-heart-impulse-plinths-human-gen/
No comments:
Post a Comment