Day 13: Data Modeling, E-R, Relational Model, Keys

Hello Dear Students,
  Hope you all are doing good...

Today we are going to cover our data modeling topic with E-R diagrams, Relational Model, and various types of keys in detail...

Let's get started.....

E-R Diagram - E-R diagram is basically Entity-Relationship diagram in which the relationship between various entities are shown in the form of diagram which is easy to understand and use. An entity refers to any object about which the data is being collected such as Employee, Customers, these are some entities about which we collect the data as the attributes. The relationship between the entities can be like, Author writes Book, where Author and Book are the entities, and Writes is the relationship between them. So this can be shown diagrammatically which is known as Entity-Relationship diagram. 

Symbols of E-R Diagram are-

1.  Rectangle shape defines the Entity in it.
2.  Diamond shape defines the Relationship between the entities.
3.  Double rectangle(Rectangle within another rectangle) defines the Weak entity.
4.  The lines connecting two shapes defines the link between them.
5.  Ellipse defines the attributes.
6.  The underline in ellipse shape text, attributes defines that it includes the primary key.
7.  Double ellipse shows the multi-valued attributes.

The above are 3 different symbols of E-R diagram. E represents Entity, R represents Relationship, A represents Attributes.
Weak entity means the entity which have not sufficient attributes and thus not forms a primary key and is dependent on another entity for its identification and is represented by double rectangle. The strong entity is the entity which forms a primary key and is represented by rectangle symbol.

Relational Model - The relational model is the model in which the data is represented in the form of the tables. The tables basically organizes the records and the fields. This model is easy to understand and use. The relational database is the database (collection of data) which is in the form of tables. The various tables are there for every entity. The software which manages the relational database is known as Relational DataBase Management System.

RDBMS - RDBMS stands for Relational DataBase Management System. It is basically used to manage the relational database which is basically in the form of tables. It is collection of 2-dimensional tables which are known as Relations. The rows are known as Tuples and the columns are Attributes. Some names you should know in RDBMS. 

 Informal Names

Formal Names
 Row, RecordsTuple 
 Column, FieldAttribute 
Number of rows   Cardinality         
 Number of columns Degree 
Set of legal values  Domain

Basically the Relational Database design can be formed with above terminologies which we understand in brief-

  • Relation - Relation is table as the relational database is in the forms of tables so the formal name is Relation. The table name is basically the entity name. A relation is set of tuples and attributes(rows and columns). Each relation has a unique name to it and it also not contains any duplicate tuples. 
  • Tuple - Tuple refers to the rows of the table of relational database. For example, in Customer table, the tuple can be (01, Ram, 01-01-2020), (09, Shyam, 05-01-2020), and so on.. All the tuples have same format and it should not contain duplicate values in all entries.
  • Attribute - Attribute refers to the columns of table of relational database. For example, above Customer table has attributes, (Cust_ID, Cust_Name, Cust_Dateofvisit). The attributes can not have same name, the data types are also defined for each attribute such as, character, numeric data types, etc..
  • Cardinality - Cardinality refers to the number of rows(tuples) in a table. In above, the Cardinality is 3. As there are insertion and deletion operations so the cardinality is of one time only.
  • Degree - Degree refers to the number of columns(attributes). One attribute refers to the Unary relation, 2 attributes refers to the Binary relation, 3 attributes refers to the Ternary relation and n attributes refers to n-ary relation. In above example of Customer relation, the degree is 3 thus Customer relation is said to be Ternary relation.
  • Domain - Domain refers to the true/possible values such as in Gender, there can be possible values such as Male/Female. Thus it is limited to the true/possible values only.



Keys are basically used for the constraints purpose and there are various keys which have their own functionalities which are as follows-

  • CANDIDATE KEY - The candidate key is formed by the attributes of the relation. The attributes which contains the unique values of tuples can form a candidate key. A candidate key cannot contain a null value. For example, in the above example of Customer relation, the Cust_ID can form a candidate key which contains unique values of tuples in the attribute. The Cust_Name, Cust_Dateofvisit cannot have candidate key because it can contain repeated values.However, a relation can have two or more candidate keys and a relation must have atleast one candidate key in it. It basically contains the irreducability property in which two such attributes doesnot form a candidate key in a proper subset in which they individually cannot create uniqueness. 

Customer relation
 Cust_IDCust_Name Cust_Dateofvisit Cust_Product 
 01Ram  01-01-2020Pen 
 09Shyam  05-01-2020 Pencil 
 11Rishi  05-01-2020 Scale 
 12John  07-01-2020 Pen 

In the above relation of Customer, Cust_ID forms a candidate key as the value of tuples are unique. The irreducability property is that the proper subset{(Cust_Dateofvisit, Cust_Product)}, individually doesnot create the uniqueness thus when combined it also not satisfies the irreducability property.

  • SUPER KEY - The super key is the key in which attributes contains the unique values of tuples, it is similar to the candidate key but the only difference is that it doesnot satisfy the irreducability property. Example, 
Customer relation
 Cust_IDCust_Dateofvisit Cust_Product 
 01 01-01-2020Pen 
 09 05-01-2020 Pencil 
 11 05-01-2020 Scale 
 12 07-01-2020 Pen 

The above example shows the Customer relation. In this, Cust_ID contains unique values so, every combination with the Cust_ID also contains uniqueness in this. If we combine, {(Cust_ID,Cust_Dateofvisit)}, {(Cust_ID,Cust_Product)}, {(Cust_ID,Cust_Dateofvisit,Cust_Product)}, then these also contains uniqueness but this donot satisy the irreducability property.  
However, the super key is not needed in the real world database.

  • PRIMARY KEY - Primary key is an attribute which contains the unique values of the tuples in a relation. Primary key is chosen from one of the candidate keys and it should not be Null. Primary key is the most usable key and it defines the uniqueness. If we choose the Primary key once on an attribute then it is life time and not changed. So , one have to take proper care while choosing primary key on attributes. 

Customer relation
 Cust_IDCust_Name Cust_Dateofvisit Cust_Product 
 01Ram  01-01-2020Pen 
 09Shyam  05-01-2020 Pencil 
 11Rishi  05-01-2020 Scale 
 12John  07-01-2020 Pen 

In above example, the Customer relation is there in which one can specify the primary key on Cust_ID as it may be same, but one cannot form primary key on other attributes as it will change and not unique with the passage of time with insertion, deletion operations. The value must not be Null in primary key.

  • COMPOSITE KEY - Whenever a primary key consists of more than one attribute then it is known as the composite key. It is also known as Concatenated key. It must not contains null values and is unique. 

  • ALTERNATE KEY - The uniquely identified attributes are known as the Candidate key and from that candidate key, primary key is chosen. When primary key is chosen from the candidate key then the other candidate key which is not chosen as primary key are known as the alternate keys. In simple words,
"Total no.of Alternate Key= Total no.of candidate keys - Total no.of primary keys."

  • FOREIGN KEY - Foreign key is basically the identifier which is used to reference another table. In very simple words to understand, the foreign key the common attributes of 2 or more tables. Suppose there are 2 tables, and both tables contains atleast same attribute name then it is foreign key. In formal words, a foreign key is an attribute/set of attributes which may acts as primary key of another relation(table) to which it is joined by a relation.
Considering two tables,

Employee relation
 Emp_IDEname Dept_no 
 001 Ram D1
 002 Shyam D2
 003 Rishi D3

Department relation
 Dept_no Dname
 D1 computer 
 D2 physics
 D3 chemistry

In above 2 relations, Emp_ID in Employee relation and Dept_no in department relation acts as primary key and the Dept_no which is same in both relations thus acts a foreign key which reference Dept_no as a primary key. For existence of foreign key, there must be a primary key in relation or in other relation.

  • ARTIFICIAL KEY - We add primary key on unique attributes, the primary key is life long so if we want to do insertion/deletion then it becomes difficult, So, there is concept of artificial key where we add an artificial attribute tuples for uniqueness. For example,

Customer relation
Cust_Name Cust_Dateofvisit Cust_Product 
Ram  01-01-2020Pen 
Shyam  05-01-2020 Pencil 
Rishi  05-01-2020 Scale 
John  07-01-2020 Pen 

Here, the primary key can be on Cust_Name but after some insertions there can be repetition in the attribute Cust_Name, so we add an artificial attribute which is as follows-

Customer relation
 Cust_IDCust_Name Cust_Dateofvisit Cust_Product 
 01Ram  01-01-2020Pen 
 09Shyam  05-01-2020 Pencil 
 11Rishi  05-01-2020 Scale 
 12John  07-01-2020 Pen 

Here, we add an artificial Cust_ID attribute for uniqueness and for the primary key.

Best of Luck Students,
  Do share,subscribe and comments if you like our efforts...