Relational Data Modeling for Database Systems Part – 1
Introduction
The ideology of the relational data model was 1st defined by Dr. E. F. Codd in the year 1970, June in a paper titled as "A Relational Model of Data for Large Shared Data Banks‘, in his paper Dr. E. F. Codd planned the todays modern world relational data model for the database systems. The widely used data models of that time were hierarchical as well as network, or even the simple flat file data structures. The Relational Database Management Systems (RDBMS) rapidly turn out to be very popular, particularly for their easiness of usage as well as flexibility in the organization. Furthermore, a number of state of the art vendors, like Microsoft, Oracle has accompanied the Relational Database Management Systems (RDBMS) with a collection of influential application improvement as well as end user products and given that a full solution.
In the previous series of articles it was observed that by what method to translate a disorganized textual explanation of data necessities into a theoretical design, by means of the Entity Relationship (ER) diagrams. The benefit of Entity Relationship (ER) diagrams is that they strengthen an individual to classify information necessities which are covertly recognized, on the other hand not clearly written down in the novel explanation. In this series of article we will try to see in what way to alter this Entity Relationship (ER) diagram into a Logical design of a relational database, this logical model is known as Relational Model too.
Relational Data Modeling Perceptions
An individual should signify a relation as a Table by means of Columns (known as Attributes) as well as Rows (Known as Tuple). Each and every column (attribute) of the table (relation) has a title.
• Area: A group of atomic data which a column or attribute can take.
• Column (Attribute): A label of a column (attribute) for a specific table (relation). Each and every single information is kept in tables (relations). Every single column (attribute), C i / A i should have an area, Area ( C I ) / Area ( A I ).
• Relational Schema: The plan of one (1) table (relation), comprising of the title of the table (the name of the relation), as well as the labels of every columns or attributes.
Consider the following illustration for further understanding:-
Example: EMPLOYEE (EID, Name, Contact_No, Department_ID, Working_Hours)
• Degree of a Relation: It is the number of columns (attributes) in the relational schema.
• Row or Tuple, T, of R ( A 1 , A 2 , A 3 , … , A n ): An ORDERLY group of data < V 1 , V 2 , V 3 , … , V n >, where every V i is a data from Area ( C i ) / Area (A i ).
• Relational Instance, r ( R ): A group of rows or tuples; hence, r ( R ) = { T 1 , T 2 , T 3 , … , T n }.
Here,
· A 1 , A 2 , A 3 , … , A n means – Attribute 1, Attribute 2, Attribute 3, … , Attribute N.
· V 1 , V 2 , V 3 , … , V n means – Value 1, Value 2, Value 3, … , Value N.
· T 1 , T 2 , T 3 , … , T n means – Tuple 1, Tuple 2, Tuple 3, … , Tuple N.
A graphical illustration would be as follows:-
Few Points to Consider
1. The rows or tuples in an instance of a relation are does not reflected the ordering sequence. Letting the rows or tuples in a dissimilar order do not changes the table (relation).
2. As soon as the schema, R ( A 1 , A 2 , A 3 , … , A n ) is demarcated, the data V i in every single row or tuple T, should be well organized as T = < V 1 , V 2 , V 3 , … , V n >
Characteristic of Relations
The characteristic of database relations are as follows:
• The relation title is unique from every other relation.
• The every single cell of relation is comprised of precisely one (1) atomic (single) data.
• The every single column or attribute has a separate and unique label.
• The data of a column or attribute are all from the similar area.
• The sequence of column or attributes has no importance.
• The every single row or tuple is different; there are no identical rows or tuples.
• The sequence of rows or tuples has no importance, in theory.
Different Relational Keys in Database
There are two (2) types of keys in relational databases. The 1st key is the classifying key, which is the Primary Key (PK); it is the key idea, on other hand two (2) additional keys are Super Key (SK) and Candidate Key (CK), they are of associated ideas. The 2nd type of key is the Foreign Key (FK).
Primary Key (PK)
The Primary Key (PK) of a table (relation) is a candidate key particularly nominated to be the key for the table (relation). In other words, it is a selection and there can be one (1) candidate key chosen to be the Primary Key (PK) only.
Super Key (SK)
The Super Key (SK) is a group of columns or attributes whose data can be castoff to exclusively classify a row or tuple within a table or relation. The table or relation might have more than one (1) Super Key (SK); however it at all times has at least one (1) Super Key (SK), the group of columns or attributes which frame the table or relation.
Candidate Key (CK)
The Candidate Key (CK) is a Super Key (SK) which is nominal; that is there is no appropriate subset which is itself a Super Key (SK). The table or elation might have more than one (1) Candidate Key (CK); in addition to the dissimilar Candidate Key (CK) can have a different number of columns or attributes. In other words, an individual should not understand ‘nominal’ to signify the Super Key (SK) with the smallest number of columns or attributes. The Candidate Key (CK) is consisted of two (2) characteristics:-
(i) In every single row or tuple of relation R, the data of CK exclusively classify that row or tuple (identifying the exclusivity).
(ii) No appropriate subset of CK has the exclusivity property (irreducibility).
Association Among Primary Key (PK), Super Key (SK) and Candidate Key (CK)
The association among the keys is as follows:
Super Key ⊇ Candidate Key ⊇ Primary Key
Foreign Key (FK)
The columns or attributes within one (1) table or relation which matches a Candidate Key (CK) of another table or relation. The table or relation might have more than a few Foreign Keys (FK), which are linked with diverse target tables or relations.
Foreign Key (FK) let the end users to link data in one (1) table or relation to data in another table or relation. Without Foreign Keys (FKs) the database will be a gathering of unconnected tables or relations.
In the upcoming part we will be discussing which are the different Relational Database Modeling Constrictions present in Relational Database Management Systems (RDBMs) now-a-days. Mainly the Reliability Constrictions which is further sub-divided into Area constrictions, Key Constrictions, Entity Integrity Constrictions and Referential Integrity Constrictions are the different Relational Database Modeling Constrictions present. Moreover, we will be discussing how to perform Entity Relationship (ER) to Relational Mapping.