MetaTutorials

MetaTutorials

The Tutor is Yours!

Codd's Rules in DBMS

Here, we will learn about the Codd's Rules of Database Management System. 
Edger Frank Codd (E.F. Codd) developed the set of thirteen (0 to 12) rules in the year 1985. He was a computer scientist who invented the Relational Model for database management. 

Sometimes, Codd's twelve rules are also known as Codd's Twelve Commandments.  Each relational database must follow or obey all these rules for the perfect Relational Database Management System (RDBMS). 
Till now, there is no such database is developed, which follows all the 13 rules. Even, popular database Oracle follows only 8.5 rules out of 13 rules. 

Following are the 13 (0 to 12) Codd's rules for the relational Database:

Rule 0: Foundation Rule:

This rule states that, if any database system is claimed as Relational DBMS, then that system must be able to manage the databases using all the relational capabilities. It means that only the relational features manage the Relational databases. 

Rule 1: Information Rule:

This rule states that the data or the information stored in the relational database must be represented in the table format. In the relational database, the data may be a user-data or meta-data. 

Rule 2: Guaranteed Access Rule:

This rule states that each data of the table must be accessible through the combination of the table name, the primary key of that table, and column or attribute of that data which to be accessed. In this rule, Dr. Codd especially talking about the term Primary key. 

Rule 3: Systematic Treatment of Null values:

This rule describes the term Null used in relational databases. Null is a value, which represents the missed and inapplicable information. If the data of any cell is missing, then that cell not be identified as Zero or empty, but that cell always represented by a NULL value.

Rule 4: Dynamic Online catalog based on the Relational Model:

This rule describes the metadata of the database, i.e., about the database structure. 
The structure of the database must be stored in the online catalog (Data Dictionary), which is accessed by authorized users using the same query language as used on the general database. 

Rule 5: Comprehensive Data Sub-Language Rule:

This rule states that the relational database system should be accessible by a language that supports the data definition, data manipulation, data security, integrity constraints, and all the transaction activities. 

Rule 6: View Updating Rule:

This rule states that all the views (virtual tables) which are theoretically updated must be updated by the relational database system. 

Rule 7: High-Level Insertion, Updation, and Deletion:

This rule states that the relational database system supports the insert, update, and delete operation on the single record as well as multiple records. It also states that the relational database supports the set operations like union, intersection, and minus operation.  

Rule 8: Physical Data Independence:

This rule states that changes occur at the physical location of the table should not affect the logical or external level of the system. 

Rule 9: Logical Data Independence:

This rule states that changes occur at the logical structure of the database should not affect the user view or view level. 

Rule 10: Integrity Independence:

This rule states that the relational database should use its integrity constraints on the data rather than using the external program or application. 
The integrity constraints must be stored in the data dictionary. The relational database system must support the primary key constraint and the foreign key constraint. 

Rule 11: Distribution Independence:

This rule states that the data distributed at the various locations should not reflect the user's view. And, the user should not be able to see that the data of the database is distributed. 

Rule 12: Non-Subversion Rule:

This rule states that, If any system has an interface that gives access to low-level records then that interface must not be able to bypass the integrity rules and constraints which are expressed in the high-level relational language. 

DBMS Aggregation

In the previous two articles, we have discussed DBMS Generalization and Specialization. Here, we will learn about the third concept of Enhanced ER model, whose name is Aggregation.


What is Aggregation?

Aggregation is a concept which is used when the ER model cannot represent the relationship between an entity and the relationship. 
In the aggregation process, the relationship between the two entities is treated as a single entity. 

Example

Let's suppose there are two entities in the Hospital database, whose names are Doctor and Patient
These two entities are related through a relationship set Checks
A doctor who checks the patients must require the instruments or tools. So there will be another relationship set Instruments.  
We need to connect the relationship set requires to an entity set Instruments and relationship set Checks. But, we can connect only entity set to a relationship set.

One relationship set cannot be related to another relationship set; for this, we need the aggregation concept. So, aggregation is needed when you express a relationship set with another relationship set. 

In the following ER diagram, the Checks (relationship set) will treat like a higher-level entity and can be associated with a relationship set requires

DBMS Specialization

In the previous article, we have discussed DBMS Generalization. Here, we will learn about the second concept of Enhanced ER model, whose name is Specialization.


What is Specialization?

Specialization is a term of DBMS, which is just like the top-down approach. It is a process which breaks the higher-level entity into two or more lower-level entities. 
It is an opposite process of generalization. In the generalization process, two or more lower-level entities are combined to form a new higher-level entity. 
This concept or process identifies a subset of an entity set which shares few different attributes. 

Example

Let's suppose, there is an entity in the School database, whose name is Teacher
The Teacher entity contains three attributes, whose names are Name, Age, and Salary. 

This Teacher entity can be further broken into three entities, i.e., Math_Teacher, English_Teacher, and Science_Teacher. These sub-entities are the three type of teacher working in the school, and all have common attributes which are associated with the parent entity Teacher.  

DBMS Generalization

In the previous two articles, we have already learned about the ER model. 
Now, we have to learn about the concepts of the Enhanced ER model. But, firstly we have to know about why Enhanced ER model is introduced. 

As the data is growing and the complexity increases, so the traditional ER model is difficult to handle the complexity of the data. So, DBMS provides the Enhanced ER model (Improvements with the traditional ER model) for managing the increasing complexity.  

Enhanced ER model includes the following three concepts:
  1. Generalization
  2. Specialization
  3. Aggregation
Here, we will learn about DBMS Generalization.

What is Generalization?

Generalization is a term of DBMS which is just like the bottom-up approach. It is a process that combines the common attributes of two or more lower-level entities to form a new higher-level entity. 
In this concept, a generalized entity can also combine with the lower-level entity to form a new higher-level entity. Generalization is the opposite of specialization. 
 

Implementation of Generalization

Following are the various steps which help to implement the concept of generalization:
Step 1: Firstly, we have to identify the real-world entities A and B of any X database. 
Step 2: After that, we have to examine the same attributes of entity A and B. 
Step 3: Now, we  have to define the new entity as the entity C with the common attributes of A and B entities. The new entity is termed as a Generalized entity. 
Step 4: After that, we have to define the relationship as R between the Entities. 

Example with Explanation 

Let's suppose we have two entities in the Program database, whose names are: Coder and Tester
The attributes of coder are Coder_ID, Name, Age, and Salary
The attributes of Tester are Tester_ID, Name, Age, and Salary
These two entities of the 'Program' database have three common attributes, i.e., Name, Age, and Salary
So, both the entities are combined to form a new generalized higher-level entity whose name is Employee
This new Employee entity contains the three attributes Name, age, and salary, which contain the values of all coder and tester of the Program database. 
As we can see in the following ER model, the common attributes (Name, Age, and Salary) of Coder and Tester are now associated with the Employee entity, which is in relationship with both the entities. 

Components of ER Diagram

The components of the ER diagram are the building blocks which help in creating the ER model. All the components have their own defined shapes or symbols which we have discussed in the previous article.
Following are the three main components of an ER diagram:
  1. Entity
  2. Attribute
  3. Relationship 
 Let's discuss each component one by one with their types. 

Entity

An entity can be a real-world object either living or non-living like a person, place, concept, object, or event. In the Entity-Relationship diagram, the entity is denoted by the rounded rectangle box. All the entities have their properties which are called attributes in DBMS. 
Following are the various examples of entity:
  • 'Student' can be an entity,
  • 'Employee' can be an entity, 
  • 'Account' can be an entity, 
  • 'Hospital' can be an entity, 
  • 'Account' can be an entity, 
  • 'Car' can be an entity,
  • Registration can be an entity, etc.
Note: In the database table, each tuple is an entity. 
For example, if we have an employee table, then each employee in that table is an entity. And, each employee is identified by the Employee_Id. 
Entity Type
Entity Type is a set of entities having common properties. 
In the above table, we can define the name of the table as the entity type, i.e., 'Student' is the entity type. Because the 'student' table is a collection of entities having the same attribute.
Entity Set
Entity Set is a group of entities of the same entity type.
Following are the two different types of entity:
  1. Weak Entity
  2. Strong Entity

Weak Entity

A Weak entity is that entity which does not contain key attribute and depends on the strong entity via the foreign key. It is identified by some other strong entity using an identifying relationship. 
In the Entity-Relationship diagram, a weak entity is represented by the double rectangle box. 

Strong Entity

A Strong entity is that entity that contains a primary key attribute. This type of entity uniquely identifies its every record using the key attribute. 
In the Entity-Relationship diagram, a strong entity is represented by the single rectangle box.  


Attribute

An Attribute is a component which shows the properties or characteristics of an entity in the entity-relationship model. we can also assign the domain or range to the attribute. 
In the entity-relationship diagram, attributes are represented by an oval or ellipse shape. In each diagram, every oval shape represents an attribute which is directly connected to an entity. 
Following are the different types of attribute in the er model:
  1. Simple Attribute
  2. Key Attribute
  3. Derived Attribute
  4. Composite Attribute
  5. Multi-valued Attribute

Simple Attribute

Simple attributes are those attributes of an entity, which cannot be divided further into smaller components. 
Following are the example of a simple attribute:
  • Roll no of the Student can be a simple attribute,
  • Id of the Employee can be a simple attribute, 
  • Salary of the Employee can be a simple attribute, 
  • Age and class of the student can be a simple attribute, etc. 

Key Attribute

Key attribute (Primary Key) is an attribute which uniquely identifies all the entities in an entity set. The values of this attribute are distinct.
In an Entity-Relationship diagram, this attribute is represented by the oval shape with the text underlined.
Following are the examples of key attribute:
  • Roll no of the Student is a key attribute,
  • Id of the Employee is a key attribute,
  • Account Number of the customer in the banks, etc. 


Derived Attribute

Derived Attributes are those attributes of an entity, which are based on other attributes and whose values are derived from other stored attributes. 
In an Entity-Relationship diagram, this attribute is represented by the dashed oval or ellipse shape.
Following are the examples of derived attribute:
  • Age (Derived from Date of birth) of a student of an employee,
  • Total or Average Marks of a student are derived attributes, etc. 


Composite Attribute

Composite Attributes are those attributes of an entity, which is a combination of more than one attributes
In the Entity-Relationship diagram, this attribute is represented by an ellipse comprising of other ellipses. 
Following are the examples of composite attribute:
  • Address of an Employee is a composite attribute,
  • The name of an Employee is a composite attribute, etc. 

Multi-valued Attribute

Multi-valued Attributes are those attributes of an entity, which contain more than one value. 
In the Entity-Relationship diagram, this attribute is represented by a double ellipse shape. 
Following are the examples of multi-valued attribute:
  • Mobile number of the Employee is a multi-valued attribute,
  • Email_ID and address of the employee also be a multi-valued attribute, etc.  

Relationship

A Relationship is also an important component of the ER model, which shows or describes the relationship between entities. 
Example: In the Entity-Relationship diagram, a relationship is represented by the diamond shape. 
Following are the four different types of relationship in the ER model:
  1. One-to-One Relationship 
  2. One-to-Many Relationship
  3. Many-to-One Relationship 
  4. Many-to-Many Relationship

One-to-One Relationship

When one entity of the entity set P is related to a single entity of entity set Q, then this type of relation is called a one-to-one relationship.
For example, An employee can work in only one department in an organization. 

One-to-Many Relationship

When one entity of the entity set P is related to more than one entity of entity set Q, then this type of relation is called a one-to-many relationship.
For example, One Employee can work in more than one department in an organization.

Many-to-One Relationship

When More than one entity of the entity set P is related to a single entity of entity set Q, then this type of relation is called a many-to-one relationship.
For example, More than one Employee can work in one department in an organization.

Many-to-Many Relationship

When more than one entity of the entity set P is related to more than one entity of entity set Q, then this type of relation is called a many-to-many relationship.
For example, More than one Employee can work in more than one department in an organization.

DBMS ER Model

ER Model is also known as the Entity-Relationship model or Entity Relationship Diagram. 
ER Model (Entity-Relationship Model) is a model that defines or describes the structure of the database. This model was designed by Peter Chen in the 1976 paper.  
It is a high-level model representing the relationship between the data in the database. It is considered as the best option at the view level which will helps the database administrators in the future to design the database easily. DBMS provides the ER diagram as a tool for representing the ER model. 

Components of ER Model

Following are the three major components of the Entity-Relationship model:
  1. Entity
  2. Attribute
  3. Relationship
If you want to learn the components of the ER model in detail, then click here

Why Use ER Model in DBMS

Following are the various reasons for using the ER Model in Database Management System:
  • The main reason is that it provides the graphical or conceptual view of any database. 
  • This model is used by the database administrators as a blueprint, which helps them to design or implement databases in the future.  
  • It helps to describe how the database tables are connected. 
  • This model also describes the entities and attributes. 

What is an ER Diagram

ER Diagram (Entity-Relationship Diagram) is a diagram which identifies the entities and relationship between those entity sets in the database. 
It allows users to draw the database logical structure. ER diagram represents the entities, attributes of an entity, and the relationship in an ER model. This diagram is widely used in designing the database. 

ER Diagram Notations or Symbols

Following are the basic symbols or notations which are used in creating a simple Entity-Relationship diagram:
1. Rectangle: This symbol represents the entity types in an ER diagram. 
2. Ellipse: This symbol indicates the attributes of an entity. 
3. Diamond: This symbol represents the relationship set. 
4. Lines: Lines are used to link attributes(s) to entity set(s) and entity set(s) to relationship set(s). 

Following are the advanced symbols or notations which are also used in creating a high-level Entity-Relationship diagram:

Advantages of ER Model

Following are the benefits or advantages of the Entity-Relationship model in DBMS:
  • Any user can easily build and understand the ER model for the database.
  • DBMS user can easily draw ER diagrams for any database if he/she knows the relationship between entities and attributes. 
  • Entity-Relationship models can be easily converted into other data models such as hierarchical, relational, and network data model.  
  • It is a communication tool that is so effective for database designers. 

Disadvantages of ER Model

Following are the limitations or disadvantages of the Entity-Relationship model in DBMS:
  • The main disadvantage of the ER model is that it is especially popular for designing high-level designs. 
  • As compared to other data models, this model represents the limited constraints and relationships. 
  • In the ER model, some information might be hidden or lost. 

DBMS vs File System

Here, we will learn about DBMS and File System and the differences between them in the tabular form.

What is File System?

File System (Often called as File Management or FS) is a technique which helps the users to store and manage the files in the various storage device such as hard disk, pen drive, and DVD.
It organizes the files in such a way that the user easily accesses or retrieves the data files. It is a simple and fast way to store various types of data files. We can easily store the videos, images, text, and audio files.
Operating System such as Microsoft Windows, Linux, and Mac has its own file system. NTFS is a file system, which is widely used in Windows OS.
The file system contains the different type of files which are grouped into directories. Directories may contain other directories or files.
In the file management system, the redundancy of data is high, which cannot be controlled easily. 
Following are the examples of the file system:
1. FAT (File Allocation Table)
2. GFS (Google File System)
3. HFS (Hierarchical File System)
4. NTFS (New Technology File System)
5. UDF (Universal Disk Format)

What is Database Management System (DBMS)?

Database Management System (DBMS) is a software, which helps the users to store and manage the interrelated data in the database easily. DBMS is a set of programs which manipulate the database.
Database Management Systems help the users to easily store, retrieve, delete, and update the data in the database.
As compared to the file management system, DBMS provides more features.
It removes the data redundancy from the database because of the 'Normalization' feature. It also provides high security to the data, so that unauthorized users can't access the data from the database.
Following are the examples of Database Management system (DBMS):
1. MySQL
2. Microsoft Access
3. Microsoft SQL Server
4. Oracle
5. PostgreSQL

Difference Between DBMS and File System 

File System (FS)
Database Management System (DBMS)
1. File System is a technique, which stores and manages the files in the various storage devices.
1. DBMS is a software, which stores and manages the data in the database.
2. In file management systems, the redundancy of data is more.
2. In DBMS, the redundancy of the data is less.
3. It does not provide the process of data recovery if the data is lost.
3. It provides the process of data recovery if the data is lost.
4. Inconsistency of the data is high in the file systems.
4. In DBMS, normalization concept keeps the data consistent.
5. As compared to DBMS, this system is less complex.
5. As compared to the file system, it is more complex to handle.
6. This system provides less security to the data.
6. This system provides high security to the data.
7. File system is less expensive as compared to DBMS.
7. The cost of DBMS is high as compared to file systems.
8. Data cannot be shared because the data is distributed in multiple files.
8. Data can be shared easily because the data is present at one location.
9. In the file system, the concurrency facility is not good.
9. DBMS provides concurrency using the locking techniques.
10. File system is less flexible than the DBMS.
10. DBMS is more flexible than the file system.
11. Examples of the file system are: NTFS, EXT GFS, HFS, etc.
11. Examples of DBMS are: MySQL, MS-SQL, Oracle, MS-Access, etc.