dbms tutorial

A Database is a collection of data which describes the activities of one or more related organisations. A database management system is a software for assisting and maintaining the large collections of data. Examples include Microsoft Access, Oracle, MySQL, FoxPro etc. 

DBMS has a wide range of applications in various sectors like banking, Finance, Telecom, Manufacturing etc. Traditional file systems have certain drawbacks that lead to the databases. Those are listed below: 

  1. Main memory will be limited and the data to be processed needs more space.
  2. Special application programs are needed for the specific needs of the users.
  3. Consistency issues arise during system crashes.

DBMS makes the preceding tasks easier. The main characteristics of the database approach are- 

  1. Self-describing nature of the database.
  2. Programs and data are insulated such that change in the data does not require a change in the program.
  3. Same data can be shown differently to different viewers.
  4. Data can be shared between the users, and transactions can be interleaved maintaining consistency.

Few important terminology related to database are listed below:

Metadata – the information stored in the catalog.

Data Abstraction – is the program data independence and program operation independence.

Database schema – describes the database. It is divided into 3 levels. External, Conceptual and Internal. 

EXTERNAL SCHEMA 

                                                                    |

CONCEPTUAL SCHEMA

                                                                    |

INTERNAL SCHEMA

Logical Data Independence – Ability to change the conceptual schema without any need to change the external schema.

Physical Data Independence – Ability of the database to change its internal schema without the need for changing its conceptual schema.

DBMS can be organised in various manners.

The different types of database management systems are-

  • Relational Database systems – Here all the data is organised in the form of tables called the relations. 
  • Hierarchical database systems – here the data is stored in the form of parent child relationships. These are confined to specific uses.
Types Of Database Management Systems
Types Of Database Management Systems
  • Network database systems – These also follow hierarchy but the relationships are many to many. 
Types Of Database Management Systems
  • Object oriented databases – Here the data is stored as objects and relationships exist between various objects. 
Types Of Database Management Systems

Actors/ Users:

The various types of users include:

  1. Application programmers
  2. Database Administrators
  3. End-users

RELATIONAL ALGEBRA

We all know that relational databases contain data in the form of relations. A relation might have an association with some other relations. E.F Codd had modelled data in the form of relations called the relational model. To get the information from the relational model, we need to use some formal language. The languages that are used for the relational model are Relational Algebra and Relational Calculus. Both these languages explain the concepts, but won’t implement them. Relational algebra is a procedural query language which explains how to perform a series of actions to get the required output. In contrast, the Relational Calculus is a non-procedural query language that specifies what the output should be without specifying the actions to be taken. Here we are going to discuss the relational algebra in detail.

To get the tasks done we need to perform operations. In Relational Algebra, we have two types of operations:

  1. Unary Relational Operations
  2. Binary Relational Operations.

As the name indicates, unary relational operations require one relation to operate with and the binary relational operations require two relations.

Unary Relational Operations

1. SELECT ( σ )

Syntax: σ p(R) where p is the predicate formula that we will apply on a relation R

The select operation can be applied on a single relation, to retrieve the rows from the table that satisfy the given predicate or condition. 

The predicate will be a formula that can include connectors like AND,OR, NOT. it can have operators like =, <,>, <=, >=, !=.

For example: Course relation is as below:

Course_idCourse_nameDuration(in years)exam_type
1001Diploma in Software engineering23
1002B.Tech in Software Engineering43
1003M.Tech in Software Engineering23
1004Integrated B.Tech in SE53
1005Certificate Course on SE12

Here the exam_type is a categorical variable with values 1=practical, 2=theory, 3= both

Input: σ exam_type=3 (Course)

Output:

Course_idCourse_nameDuration(in years)exam_type
1001Diploma in Software engineering23
1002B.Tech in Software Engineering43
1003M.Tech in Software Engineering23
1004Integrated B.Tech in SE53

2. PROJECT (π)

Syntax:   ∏ A1, A2, …An (R) 

The specified column values will be projected.

Duplications will be removed.

Example: ∏ course_id, course_name (Course)

Output: 

Course_idCourse_name
1001Diploma in Software engineering
1002B.Tech in Software Engineering
1003M.Tech in Software Engineering
1004Integrated B.Tech in SE
1005Certificate Course on SE

3. RENAME ( ρ )

Syntax : ρ(new_name, old_name)  

To change the name of the attributes of a relation.

Example: ρ(NEW_COURSE,COURSE)  

Binary Relational Operations

1. JOIN: 

Syntax:  R1 ⋈R2

Two or more relations can be joined based on some condition given.

It is nothing but the Cartesian Product followed by the Select operation.

Example: Course  ⋈ Instructor

The Instructor table is given below:

Course_idInstructor_ID
10012034
10023056
10031092
10044089
10050199

And the output is given below:

Course_idCourse_nameDuration(in years)exam_typeInstructor_ID
1001Diploma in Software engineering232034
1002B.Tech in Software Engineering433056
1003M.Tech in Software Engineering231092
1004Integrated B.Tech in SE534089
1005Certificate Course on SE120199

There are different types of joins and were dealt in detail in the later part of the tutorial.

2. DIVISION 

Syntax : R1 ÷ R2

Performing a Division operation is nothing but doing Project, Cartesian Product and Set Difference operations in order. 

This operation can be done only if the attributes in R2 are a proper subset of that of R1.

It includes the attributes that are in R1 but not in R2.

It includes those tuples from the relationR1 that are associated with every tuple in the relation R2.

Relational Algebra has some operations that were in the set theory. Those were listed below:

1. UNION (U)

Syntax: A U B

  1. Can be applied to two relations if both the relations have the same number of attributes.
  2. The domains of the attributes must be compatible.
  3. All the tuples from both the relations will be included in the result eliminating the duplicates

2. INTERSECTION ( ∩)

Syntax: A ∩ B

  1. Can be applied on two relations if they both have the same number of attributes 
  2. The domains of the attributes must be compatible.
  3. The tuples that are present in both the relations will be chosen for the output.

3. SET DIFFERENCE (-)

Syntax: A – B

  1. Includes only those tuples that are in the relation A but not in the relation B.
  2. The attribute names in relation A should match with the attribute names in relation B
  3. Both the relations should have the same number of attributes and also their domains must be compatible.
https://drek4537l1klr.cloudfront.net/sfregola/v-7/Figures/35image001.png

4. CARTESIAN PRODUCT or CROSS PRODUCT ( X )

Syntax: A X B

  1. To merge a row of a relation with a row in another relation.
  2. Stand-alone cartesian products are never useful in relational databases.

Few points to be noted regarding the above operations are:

a) Combination of the Relational Algebra Selection and Projection is nothing but the SQL SELECT command.

b) SQL SELECT does not remove the duplicate tuples whereas the relational algebra PROJECT will remove duplicate tuples.

c) Relational Algebra SELECT is a horizontal partition whereas the PROJECT is a vertical partition.

d) The SELECT operation is commutative whereas the PROJECT operation is not commutative

e) The UNION and the INTERSECTION are both commutative and associative, ie., 

AUB = BUA,  A∩ B = B∩ A (commutative) and (AU B) U C = A U (B U C), 

(A ∩ B)∩ C = A ∩(B ∩ C) (which is the associative rule).

The set of operations {σ, π, U, -, ρ,  X } is a complete set. Any relational algebra expression can be expressed as a sequence of the above operations. 

ER to Relational model

We often come across two different models, viz., the ER model and the Relational model. The ER model means the Entity-Relationship model. The Relational model tells us how the data is stored in the form of relations in the database. Then what is the need for the ER model? The ER model is the high-level conceptual data model that is required to systematically analyze the data requirements. Generally, the clients are not sure of how to model their data into tables. This ER model helps in visualizing their thoughts with the help of diagrams. This comes in the design phase of software development. It identifies what the real-world objects that exist in the system and their association with other objects are. In the ER model terminology, those objects are called the entities, and the association between them is called the relationship. In short, we can say that the ER model is the blueprint of the system that we are going to implement. The important concepts in the ER model are entities, attributes and relationships. The characteristics of the features of the entities are the attributes of the entities. Let us see how different types of entities, attributes and relationships are represented in the ER diagram.

Entity or Strong Entity is represented by a rectangle.  

Weak Entity is represented by a double rectangle. 

Key Attribute is represented by Ellipses with the name underlined

Multivalued Attribute is represented by Double ellipses

Derived attribute is represented by Dashed ellipses

Composite attribute is represented by ellipse connected to the other ellipses.

All the above are represented as below:

Figure C-7

Relationship is represented by a diamond and the cardinality is mentioned on the lines connecting the entities involved in the relationship.

https://lh4.googleusercontent.com/P3CUeZrlLKpzNGzq8S_Va_ScHS6xE9ONFFVEmVdQgV_skCSw1pH4yIYtxemQ9_hlNpBDv9KLhb2jteKHtWO_ozfAjaOaHrtKvlmIaGkVEe6uTHDojF_Vtggm2Pl0rJ3m-k9cBr8o

Weak Relationship is represented by a double diamond. For example: n3

Inheritance is represented by a triangle for generalisation and a circle for its implementation.

Aggregation is represented as Figure 7.20

Composition is represented as Figure 7.22

Now let us see how the ER model is mapped into the Relational model.

Step1: Strong Entities – For each strong entity, create one table (relation).

Include all the simple attributes of the strong entity as the columns of the relation.

Only the simple components of the composite attribute need to be included in the relation.

Choose the primary key of the above created relation.

Step 2: Weak Entities – For each weak entity, a new relation has to be created.

All the simple attributes need to be included.

Include the primary key of the owner entity as the foreign key of the newly created relation.

The primary key of the newly created table will be the combination of the primary key of the owner entity and the partial key of the weak entity.

The mapping order should be first the owner entity and then the weak entity.

Step 3: Binary 1:1 relationship 

For a relationship R, identify the participating relations R1 and R2.

In this relationship type, the mapping can be done in 3 ways.

a)  i) Choose one relation R1.

ii) The primary key of the other relation R2 should be included in the relation R1 as foreign key.

 iii) The entity that has a total participation in the relationship R should be taken as the relation 

b)  i) merge the relationship and the entities into one relation 

ii) this option is chosen when both the entities are in total participation.

c)  i) Create a new relation for the relationship R.

ii) Include the primary keys of both the relations in the relation.

 iii) The primary key of the new relation is the combination of the primary keys of the participating relations in the relationship.

Step 4. Binary 1:N Relationship 

For the relationship R with R1 and R2 as the participating relations with R1 on N and R2 on 1 side, do the following:

i) In R1 include the primary key of R2 as the foreign key.

ii) Any simple attributes of the relationship R are to be included in the relation R1.

Step 5: Binary M:N Relationship 

i) Create a new relation by including the primary keys of both the participating entities as the foreign key.

ii) The primary key of the new relation is the combination of the primary keys of the participating entities.

iii) All the simple attributes of the relationship are to be included in the new relation.

Step 6: Multivalued attributes 

A new relation R has to be created for each multivalued attribute A.

Also include the primary key of the relation/entity that has the attribute A in it, as the foreign key in R.

Step 7: N-ary relationship

A new relation S has to be created to represent the relationship R.

The primary keys of all the participating entities have to be included in S as the foreign key.

The primary key of the new relation is the combination of all the foreign keys.

SQL OVERVIEW

As the name indicates, SQL, Structured Query Language is a language used to question the database which is in a structured format. As relational databases store data in a structured format, SQL is used with RDBMS. RDBMS like Oracle, Ms Access, MySQL, Sybase etc. use SQL for data query operations. In this tutorial, we are going to cover all the important SQL commands and their usage. Please note that there will be slight variations in the syntax depending on the SQL dialect you are using, and hence syntax is not mentioned here.

All the commands in the SQL can be grouped into four categories, viz, Data Definition Language (DDL), Data Manipulation Language(DML), Transaction Control Language (TCL) and Data Control Language(DCL). DDL commands change the metadata of the database. DML commands are used to change the data in the database. DCL deals with the schema elements’ security aspects (such as tables, views, constraints, domains etc.). TCL commands are used for transactional purposes.

The usage of the various commands is summarised below:

DDL: 

  1. CREATE SCHEMA/DATABASE schema_name/database_name AUTHORIZATION user_name  – to create a schema or database giving privileges to a particular user_name.
  2. a) CREATE TABLE table_name (column1 datatype, column2 datatype, … ) 
    – to create an empty table with columns and data types as specified.
    b) CREATE TABLE new_table_name ( SELECT AS column1, column2, …. FROM old_table_name WHERE … ) 
    – to create a new table from an existing table that satisfies a condition in the WHERE clause and fills the new table with rows from the old table.
  3. CREATE DOMAIN domain_name AS  type_name 
    – creates a domain name for a particular data type.
  4. a)  ALTER TABLE table_name ADD column_name data_type 
    – to add a new column to an existing table
    b)  ALTER TABLE table_name DROP COLUMN column_name  
    – to drop a column from an existing table.
    c)  ALTER TABLE table_name RENAME TO new_tabel_name 
    –   to rename an existing table.
  5. DROP DATABASE/TABLE/INDEX/VIEW database_name/table_name/index_name/view_name 
    – to drop an existing database or table or index or view.
  6. TRUNCATE TABLE table_name
    – to remove the data from the table.

DML:

  1. a) INSERT  INTO table_name VALUES (val1, val2, val3…) 
    – to insert values into the table for all the attributes in the table.
    b) INSERT INTO table_name(col1, col2, col3, …. ) VALUES (val1, val2, val3….) 
    – to insert values into the table for the specified columns of the table. 
  2. a) DELETE FROM table_name  
    – to delete all the tuples from a table.
    b) DELETE FROM table_name WHERE condition  
    – to delete some tuples from a table that satisfies the given condition. 
  3. SELECT col1, col2,col3, … FROM table_name WHERE condition1 
    – outputs the selected columns from the given table that satisfy the condition.

TCL:

  1. SET TRANSACTION [ READ_WRITE | READ_ONLY ] 
    – to start a transaction with the operations that are allowed in it.
  2. COMMIT  
    – all the instructions before the commit command should be permanently stored in the database.
  3. a) ROLLBACK 
    – all the uncommitted transactions that are not saved in the database will be undone.
    b) ROLLBACK savepoint_name  
    – all the commands between the rollback command and the savepoint command will be undone.
  4. a) SAVEPOINT savepoint_name 
    – a name given to a particular point in the transaction which a transaction can be rolled back to that particular point instead of the entire transaction, if any unusual/abnormal operation occurs. 
    b) RELEASE SAVEPOINT savepoint_name 
    – to delete a savepoint that has been created and can no longer be used in a rollback command.

DCL:

  1. GRANT – to allow a specific user to have some privileges
  2. REVOKE – to take back the granted privileges that were granted

Some key points to be noted while using these commands are listed below:

  1. In the TRUNCATE command, the structure of the schema element is preserved as opposed to DROP command and hence DROP command cannot be rolled back.
  2. DELETE just removes the rows from the table and returns the number of rows deleted, unlike TRUNCATE which does not return the number of rows.
  3. For some of the commands above, SQL clauses will be used for row specific or column specific actions. Those include ORDER BY, GROUP BY, HAVING , DISTINCT, LIMIT etc.

Relational Database Design

For a database to be efficient, it should be designed properly. Some of the main aspects that we look into, while designing a database

  1. It should reflect the real-world structure of the problem.
  2. Redundancy should not be there.
  3. Users can efficiently access the database.
  4. Security and consistency issues should be taken care of.

In the relational database design, the data to be stored in the databases need to be organised as a collection of relations. In layman words, a relation is a table, the rows in it are called the tuples, and the column header is the attribute. The set of values that a particular attribute can have is the domain of that attribute. The degree or the arity of a relation is the number of attributes of its relation. Now, the characteristics of the relations are listed below:

  1. In relation, the ordering of the tuples doesn’t have any significance. This means that there is no row significance in the relational database.
  2. The ordering of the values within a tuple is important. This means that the order in which the attributes were declared during the creation of the relation that attributes ordering should be used while inserting the values in the relation.
  3. The tuples will have atomic values, i.e., values that cannot be divided further.
  4. There can be NULL values for some nonprime attributes. Here the NULL is either of the following-
    a) value is unknown
    b) value exists for that attribute but is not available(at present).
    c) that particular attribute does not apply to this tuple.
  5. The relation names must be unique.

The various constraints that are applied on relations can be categorised as below:

  1. Inherent Model based constraints
  2. Schema based constraints
  3. Other constraints

Inherent model based constraints: These are the implicit constraints that hold good for all the relations. Eg : The tuples in a relation must be unique; atomic values should be stored in the relations.

Schema based constraints: Various constraints come under this category. These include:
a) Domain constraints – the values of the attributes must be atomic.
b) Key constraints – this covers the constraints on all the types of keys that can be present in the relations, like the super key for the uniqueness constraint; key as the minimal super key; candidate key; primary key which is one among the candidate key.
c) Entity Integrity constraint – which specifies that the primary key cannot have null values.
d) Referential Integrity constraint – this is the foreign key constraint which means that the tuple in one relation must refer to an existing tuple in another relation. If both the referred and the referring relations are the same then it is called the self-referencing relation. 

Other constraints:

a) Semantic integrity constraint – eg: The supervisor’s salary should be more than his subordinate’s salary; 
b) Transition Constraint – these specify the state changes in the database. For example, the salary of an employee can only increase; the marital status can change from unmarried to married but not vice-versa.

Now, let us look at some of the operations that can violate some of the above mentioned constraints.

  • Insertion – this operation can violate 

a) Domain constraint – this occurs if the value entered for an attribute is not within the domain of the attribute ie., the data type or the range of values allowed.

b) Key constraint 

c) Entity integrity constraint

d) Referential integrity constraint

If any one of the above constraints is violated during the insertion into the relation, then the insertion operation is rejected, thereby indicating to the user that some values are entered incorrectly.

  • Deletion – this operation can violate referential integrity constraint only. This can be dealt in three ways:

a) To reject the delete operation.

b) To cascade the deletion operation by deleting all the tuples that were referred by the tuple that is being deleted. 

c) to modify the referencing attribute value that causes the violation.

  • Updation – This is nothing but deleting one or more values and then inserting new values. Hence, all the violations that were stated for deletion and the insertion operation will apply here. 

=======================================================

DATABASE NORMALISATION

The term Normalization has a great significance with respect to the relational databases. In general, Normalization means adjusting the values in such a way that they follow certain criteria. From the database perspective, Normalization means adjusting the attributes or columns into different tables or relations so as to minimize the data redundancy and eliminate various anomalies. In this regard, Edgar Codd had proposed the theory of Normalization and had laid down some rules. Those rules were called the Normal Forms. In this regard, he proposed the first three Normal forms and then Raymond F. Boyce proposed the Boyce Codd Normal Form. So the normal forms till now we stated were 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form), and BCNF(Boyce Codd Normal Form). Let us go check out what these rules are by diving deeper into these normal Forms.

1NF (First Normal Form)

Rules:

Attribute (Column) : Atomic (single value)

Record (Row ) : Unique

Attribute Domain should not vary.

For a table to be called as a relation, it should be in 1NF.

Example: 

Student_idStudent_nameStudent_phone_number
1001Aakash9967543782, 7865432901
1002Bhanu8765432891,6784932189
1003Chaitanya7659372801
1004Durga8754329282

In the above table, the attribute Student_phone_number is a multivalued attribute so this table is not in 1NF. For the the table to be in 1NF, we have to change the table as below:

Student_idStudent_nameStudent_phone_number
1001Aakash9967543782
1001Aakash7865432901
1002Bhanu8765432891
1002Bhanu6784932189
1003Chaitanya7659372801
1004Durga8754329282

Before proceeding to the other normal forms, we should understand the concept of keys and functional dependencies in the database. 

KEYS

A key can be composed of a single or a group of attributes. The main purpose of keys are:

  1. To uniquely identify the rows in a relation.
  2. To know the connection (relationship) between 2 or more relations in a database.

There are various types of keys in DBMS. The important ones are listed below:

  1. Primary Key – a candidate key
    – unique
    – non null
    – only one for a relation
  2. Super Key – unique
    – contains redundant attributes that are not needed to check the uniqueness
    – superset of candidate key 
  3. Candidate key – unique
    – minimal subset of super key
    – a relation can have many candidate keys
  4. Foreign Key – is a primary key in some other relation
    – can be null 
    – can be non-unique
  5. Alternate Keys – unique
    – candidate keys
    – not chosen as primary key

Note:
Prime Attributes: the attributes that are part of primary key.
Non-Prime attributes: the attributes that are not part of the primary key.

FUNCTIONAL DEPENDENCIES

Let R be a relation. Let r, the relation state, denote the set of tuples that are currently present in the relation R. The Functional Dependency FD between two groups of attributes X-> Y denotes x and Y from the relation R. The set of attributes Y are functionally dependent on the set of attributes X. The FD states a constraint that for any two tuples t1 and t2 that are present in ‘r’, with t1[x] = t2[x], then t1[y] must be equal to t2[y]. An FD is the property of the semantics of the attributes.

The various types of functional dependencies are 

1. Trivial FD  – X -> Y is a FD and Y is subset of X

2. Non Trivial FD – X-> Y is a FD and Y is not subset of X

3. Completely Non Trivial FD – X->Y is a FD and X intersection Y is null

4. Multivalued dependency – X->->y , two independent attributes are both dependent on a third attribute. Occur in a relation with three or more attributes.

5. Transitive Dependency – X->Z is a FD, and the following properties hold 

                                              a) X->Y

                                              b) Y is not functionally dependent on X

                                              c) Y -> Z

                                              Occur in a relation with three or more attributes.

6. Join Dependency (JD) – A generalization of multivalued dependency. Let R(A,B,C,D) decomposed to R1(A,B,C) and R2(C,D). If the join of R1 and R2 on C gives R, then JD exists.

Now let’s continue with the other Normal Forms.

2NF (Second Normal Form) 

Should be in 1NF.

Non-prime attribute if any in the relation is fully functionally dependent on the primary key.

Example : 

sidsnamecidcnamemarks
111Rahul1Maths55
111Rahul2Science46
112Raghav1Maths90
112Raghav2Science78
113Anup3Social40

Here the primary key is sid+cid.

But sid alone will give sname, and cid alone will give cname. Hence not in 2NF. 

Now split the attributes into different tables. 

sidsname 
111Rahul
112Raghav
113Anup
cidcname
1Maths
2Science
3Social
sidcidmarks
111155
111246
112190
112278
113340

3NF (Third Normal Form) 

Should be in 2NF.

There should not be a transitive dependency of non-prime attribute on the primary key.

Alternatively, if X->P is a FD then either. 

a) X is a superkey of R  or 
b) P is a prime attribute of R

Example: 

cidroom_numinstructor_iddept
101341cse
102382math
103563cse

Primary key {cid}

The debt is dependent on the instructor id which is not a prime attribute,  also instructor id is not the super key. Hence this table is split into 2 tables.

cidroom_numinstructor_id
101341
102382
103563
instructor_iddept
1cse
2math
3cse

BCNF ( Boyce Codd Normal Form)

Should be in 3NF.

If X-> P is a FD then X is a superkey of R.

student_idCityState
101MumbaiMaharashtra
102PanajiGoa

Primary key: student_id

We can get the city or the state from student_id. Also given the city alone we can get the state. Hence, not in BCNF. 

student_idCity
101Mumbai
102Panaji
CityState
MumbaiMaharashtra
PanajiGoa

For the real world databases, the highest normal form that is sufficient is BCNF. But let us have a glance about the other normal forms.

Beyond BCNF, there are other normal forms like 4NF, 5NF

4NF(Fourth Normal Form)

– Should be in BCNF

 – Multivalued dependency is not allowed

5NF(Fifth Normal Form)

– Should be in 4NF

– Join Dependency is not allowed

– Join should be lossless.

JOINS

A Join in the RDBMS implies retrieving the tuples of two relations that have an attribute in common and a condition applied to it. Here, the main emphasis is that only those tuples are retrieved that satisfy the join condition as opposed to the cartesian product or the cross product of the relational database in which all the combinations of tuples are included in the result. 

The different types of joins are: 

  1. Inner Join   – only the matching tuples appear in the result
    a) Theta join
    b) Equi join 
    c) Natural 
  2. Outer Join – all tuples in either or both the tables are included in the result.
    a) Left Outer
    b) Right Outer
    c) Full Outer 

Let’s dive into the details of all the above joins.

In the below definitions, we use two relations R and S on which the join operation is performed. 

The attributes Ai belong to relation R and the attributes Bj belong to relation S.

Theta Join: 

In the Theta join, the join condition can be any of the following: =, <, <=, >=, >, !=. This retrieves those tuples that satisfy the given condition.

Equi Join:

In the equi join, the join condition is always equal to =. Here, we always have one or more pairs of attributes that have identical values in every tuple.

Natural Join:

In the natural join, the second superfluous attribute present in the relations will be removed. In this case renaming of the join attributes should be done before the join operation to have the same name in both the relations.

Left Outer Join:

All the tuples from the left relation will be included in the result.

Only the matching tuples from the right relation are included in the result.

Right Outer Join:

All the tuples from the right relation are included in the result.

Only the matching tuples from the left relation are included in the result.

Full Outer Join:

All the tuples from the left and right relations where the condition is met are included. 

Returns null columns where there is no match.

Diagram

This brings us to the end of the DBMS Tutorial. We hope that you enjoyed this and were able to gain some valuable information from the same. If you wish to learn more such concepts, you can join Great Learning Academy’s Free Online Courses and learn the most in-demand skills today.

Also Read:
Top DBMS (Database Management System) Interview Questions

0

LEAVE A REPLY

Please enter your comment!
Please enter your name here

12 + 14 =