Relational Data Modeling for Database Systems Part – 2

Relational Database Modeling Constrictions

Reliability Constrictions

Every single relational schema should fulfill the subsequent four (4) sorts of constrictions.

A. Area constrictions

Every single column or attribute A _i should be an atomic data from Area ( A _i ) for that column or attribute.

The column or attribute, labeled as Name in an instance of a BAD DESIGN, for the reason that from time to time an individual might need to search another individual by means of their last name only.

B. Key Constrictions

I. Super Key (SK) of Relation R: It is a group of columns or attributes, the Super Key (SK), of Relation R is such that no two (2) rows or tuples in any legal relational instance, r (R), will have the similar data for Super Key (SK). For that reason, any two (2) different rows or tuples, for an example T 1 and T 2 in r(R), T 1 [ SK ] ! = T2 [ SK ].

II. Key of Relation R: It is a nominal Super Key (SK). That is, a Super Key (SK), SK, of R such that the elimination of any column or attribute from SK will result in a group of columns or attributes which is not a Super Key (SK).

Example VEHICLE ( Vechicle_ID , License_No , Vechicle_Model_No , Manufacturer )

The above schema has two (2) keys:

K 1 = { Vechicle_ID , License_No }

K 2 = { License_No , Vechicle_Model_No }

Both K 1 and K 2 are Super Key (SK).

K 3 = { Vechicle_ID , Vechicle_Model_No } is a Super Key (SK).

However it is not a key. Now a question arises, that why K 3 is not a key.

When a relation has more than one (1) key, an individual can choose any one (1) randomly as the Primary Key (PK). Primary Key (PK) columns or attributes are emphasized in the schema:

VEHICLE ( Vechicle_ID , License_No , Vechicle_Model_No , Manufacturer )

C. Entity Integrity Constrictions

The Primary Key (PK) column or attribute, of any relational schema R in a database cannot have null data in any row or tuple. In other words, for every single table or relation in the database, there should be a Primary Key (PK); for every single Primary Key (PK), every single row or tuple in the table or relation should have non null data. This is for the reason that Primary Key (PK) is castoff to classify the discrete rows or tuples. Scientifically, T [ PK ] ! = NULL for any row or tuple T € r ( R ).

D. Referential Integrity Constrictions

Referential integrity constrictions are castoff to stipulate the associations among two (2) relations in a database.

Think through a referencing table or relation, R 1, as well as a referenced table or relation, R 2. Rows or tuples in the referencing tables or relation, R 1, have columns or attributes which are Foreign Keys (FK) that reference the Primary Key (PK) columns or attributes of the referenced table or relation, R 2. A row or tuple, T 1, in referencing table or relation R1 is said to reference a row or tuple, T 2, in referenced table or relation R2 when T 1 [ F K ] = T 2 [ P K ].

A referential integrity constriction can be shown in a relational database schema by means of a directed arrow from the referencing (Foreign) key to the referenced (Primary) key. An instance has been demonstrated as follows:

Entity Relationship (ER) to Relational Mapping

At the present it’s an appropriate time to introduce some comfortable techniques which will assist us in developing the Relational schemas from our Entity Relationship models. These easy techniques will be defined in the subsequent phases:

1. For every single consistent entity, E, in the Entity Relationship model, an individual need to form a table or relation R which will contain every simple columns or attributes of entity, E. Then, handpicked the Primary Key (PK) for E, as well as mark it.

2. For every single weak entity type, W, in the Entity Relationship model, along with the owner entity type, E, an individual need to form a table or relation R with all the columns or attributes of weak entity, W, as the columns or attributes of weak entity W, along with the Primary Key of E. Now in few occasions there will be some matching tuples or rows in the weak entity W which will be sharing identical owner rows or tuples, then an individual is necessitated to form an extra index columns or attribute in the weak entity W.

3. For every single binary table or relation type, R, in the Entity Relationship model, an individual needs to classify the contributing entity categories, which are among X and Y.

I. For one to one (1 to 1) relationship among X and Y

Select one (1) table or relation, say X. Take the Primary Key of another table or relation say T as a Foreign Key of X.

II. For one to many (1 to N) relationship among X and Y

Assume Y to be an entity on the N side of the relationship. Take the Primary Key (PK) of X as a Foreign Key (FK) in Y.

III. For many to many (N to N) relationship among X and Y

Form a fresh table or relation say – Z to signify relation, R. Take the Primary Keys (PK) of both, X as well as Y as Foreign Keys (FK) of Z.

4. For every single multi valued column or attribute C, form a different table or relation, R, which contains every columns or attributes corresponding to C, as well as the Primary Key (PK) column or attribute, K, of the table or relation which signifies the entity type or the relationship type which has C as a column or attribute.

5. For every single n-ary relationship type, where n > 2 (n greater than 2), form a different table or relation, X. Take account of Foreign Key (FK) columns or attributes in X, the Primary Keys (PK) of the table or relations signifying every single contributing entity types. Similarly take account of any simple columns or attributes of the n-ary relationship type as columns or attributes of X.

In the upcoming part we will be discussing the Relational Languages and the seven (7) different Relational Algebras which are Selection, Projection and Joining in details.