The Three Schema Architechture of Relational database

In relational database system, the three-level architecture describes how users view the data. It separates physical storage details from users and applications. It provides data abstraction and data independence.

Why we need this architecture?

The main reasons for three-level architecture is:

Data abstraction

• Users can access data without knowing the physical storage details.

Data Independence

It means changes in one level does not affect the other levels.

• Logical data independence – It means changes in logical schema (tables, relations) does affect the users or applications.
• Physical data independence – changes in the physical storage (disk, indexes) does not affect the logical tables or relations.

Multiple Views of Same Database

• Different users can view database according to their need and privileges’.

For example, an admin can see tables and he can create a new one if needed, but a common user can only see limited view of data provided by the application, they are using.

Security and Consistent Data

The three-level architectures provides security and data consistency.

1. The physical data access is restricted.
2. Since, DBMS manages everything from one location, the data is consistent, and there are no duplicate records across multiple copies of the database.

Internal level with internal schema

The internal level is the lowest level of data abstraction. It deals with the physical implementation of the database in a computer system. In other words, it is the physical level.

Storage Structure

It describes how data is organized and stored on a disk storage. There are two main concerns;

1. Efficient use of disk space.
2. Minimum disk I/O

It is because the disk is slower than the random access memory (RAM) of a computer.

DBMS decides:

1. how records are placed in files.
2. structure of records, variable length or fixed length.
3. how data blocks are arranged.

Only files are part of a file system, where records and blocks are low level details are part of disks. The operating system manage files, file system, blocks and records.

Fast Search and Retrieval of Data

A fast search and retrieval of data is primary concern of physical storage. It is achieved through Indexing. The indexes are added as additional structures at physical level.

The most popular indexing types are:

• B+ trees
• Hash indexes
• Clustered and non-Clustered indexes

The solution is reading data without scanning the whole disk and reducing the disk I/O.

Buffer Management

Buffer is a temporary storage memory, usually, in RAM to hold data, while transferring between to locations or devices. In DBMS, buffer management is an important task.

The buffer pool can hold page table and memory pages.

DBMS do not interact with disk every time. It uses a buffer pool to access data from memory, than accessing disk which is slower process.

The primary concern is to decide which page should remain in memory and which dirty page should sent back to the disk.

Pages are managed with page replacement algorithms such as LRU( Least Recently Used). This directly affect the performance of the database.

Data Security

Data security exists at various levels. At the physical level, the data security is concerned with physical security of infrastructure, servers, etc. This is secured by giving access to only authorized people. Not everyone get the same access permissions. The DBA gets full access to the database.

The main concern for physical security are;

1. Data Files
2. Disks
3. The Backup Disks and Files.

Data files are secured using various encryption techniques. The access control can limit the access to only authorized people to read, write, or modify the data files. Sometimes the files are immediately destroyed if they are no longer required.

Data disk security includes methods like Self-Securing Disks (SSDs), password protection, full disk encryption like FileVault, BItLocker, etc.

Backups are useful incase of loss of data or disks. There two ways to keep backup.

• Local backup
• Offsite back

In either case, backup disks and files need own protection using encryption, access control, and isolating the backup locations.

Even all of these cannot prevent database crash or failures. Next section discuss the integity and recovery of data.

Data Integrity and Recovery

When the database is crashed, our concern is to prevent corruption of physical data and recover the database and any lost data.

This is achieved through transaction logs:

1. Write-Ahead-Log (WAL) – Database transactions do not write anything to database because a failure can corrupt database during writing process. Therefore, database transactions follow integrity , atomicity, and durability principles( ACID Properties) and write in the WAL files before they commit the information permanently to the database.
2. Checkpoints – A checkpoint in the log files, is a marker that all memory pages are written to the disk and database is consistent. It is used to recover the database to a consistent state.
3. Redo/Undo Logs – Redo log store all committed transactions and new values of the data. The Undo logs contains previous versions of data. Database can rollback to previous version if the current transaction fails.

Conceptual Level with Conceptual Schema

In the three-level architecture, conceptual level sits between the external level and the internal level. It describes the logical structure of the complete database regardless of its physical implementation.

Conceptual level is the overall view of the database and it represents all the information going to be represented in the database.

The conceptual level represents;

1. What data is stored.
2. What is the relationship between data
3. Constraints on the data

For example;

Entities – Student , Course, Department

Attributes – StudentID, Name, Grade

Relationship – Students enroll for Courses

This level is usually represented using schemas or models such as Entity-Relationship Models or Relational Model.

What are the main concerns at conceptual level

The main concerns at conceptual level are listed below:

• Logical structure
• Relationship between data
• Data integrity constraints
• Data independence
• Security and Authorization

Logical Structure

The DBMS at conceptual level must clearly define:

1. Tables (relations)
2. Attributes (columns)
3. Keys ( primary key, foreign keys)

The tables with columns are completely different from how physical records are stored. The keys such as primary key and foreign keys ensures that records are not duplicate. The table allows duplicate data if key is not defined.

Example.

Student Table (Student_id, Name, Department_id)

Relationship between data

The table row represents an instance of an entity. Table is entity type , set of all similar entities.

The relationship between data is how entities are connected. These relationships ensure that the database captures real world structures.

For example.

Student enrolls in a Course

Department offers a Course

Data Integrity Constraints

The data in the database must follow rules and precise structure. The correct data is valid, consistent across database, and meaningful according to the constraints.

A consistent data is that which follows all rules, even though duplicate records exist in the database tables.

Common constraints includes:

1. Primary key to uniquely identify records.
2. Foreign key to maintain relationships.
3. Domain constraints to ensure valid values for attributes.

For example:

In Student Table, Student_id cannot be NULL due to constraint NOT NULL on that field. Student_id can be a Primary Key that uniquely identify the Student Table, therefore, NOT NULL make sure that all students have an ID.

Data Independence

The most important job of conceptual level is to hide the physical storage details from the users. If the storage structure change, a disk, or index, it does not affect the conceptual level. The conceptual schema does not change.

This is called logical data independence.

Security and Authorization

At conceptual level, we are dealing with different types of data. The data is modified, read and deleted. In short, there are many database operations on this data.

Conceptual level decides

1. Who can access which data?
2. User permissions.

For example,

Students can view their Grades, however, only administrator can modify the Grades.

What is the role of a Conceptual Schema ?

The conceptual schema is the blueprint of the database. It acts like a bridge between external level(user views), conceptual level (logical structure), and internal level (physical storage) of the database.

The overall organization of the data is independent of the physical storage.

External or View level

The external level is the top-layer of the three-schema DBMS architecture. It does not show full database, only relevant part of data to a single user or an application. This is called Views.

For example,

There can be a Student view with fields – Student Name and Grade.

There can be one view for Admin with following fields – Student_id, Name, Grade, Fees.

It is same database , and tables structure but different views for different people.

What are the main concerns of External level?

External level interacts with users. It has different concerns than conceptual and internal level.

1. Data abstraction
2. Security and Access Control
3. User-Specific Views
4. Data Independence

Data Abstraction

The main goal of external views is to provide necessary data to the users. Users don’t need to see the internal database structures or physical implementation of DBMS.

Hiding the important database details is the first priority of external views.

For example.

A student only need to see the marks obtained, they don’t need to see the table design, or change anything in the database. A view is generated with only student ID, Name and Marks.

This indicates the security and authorization problems which is discussed in the next section.

Security and Access Control

Security and authorization is most important here. Unauthorized users should not get access to information. The admin protects the views with secured passwords. Every user get their own password and only they are authorized to view the data.

All sensitive data is hidden. Users get only authorized information.

User-Specific Views

User-specific view is customized representation of database that contains only the data a particular user (or role) need.

The views are filtered vertically, then only relevant columns of tables are shown.

Similarly, view are also filtered horizontally, where user can see only specific records.

For example.

Students can see only own records.

Manager can see only his department records.

Data Independence

Change in the internal or conceptual level does not affect the external view. The users will never notice any change happening in database. This is called external data independence.

Summary

How to Add a Constraint and Remove a Constraint From a Relation?

Constraints are like rules in relation so that you cannot violate them. There are plenty of constraint types that we use. In this article, we will add a check constraint and delete it from a relation.

The general process to add or remove a constraint from a Relation is as follows.

STEP 1:

In this step, you create EMPLOYEE relation and put a check constraint on Gender.

CREATE TABLE EMPLOYEE (EMPNO NUMBER(5) PRIMARY KEY, ENAME VARCHAR2(20), GENDER CHAR(2), SALARY NUMBER(7,2));

STEP 2:

Here we add the constraint to the table .

ALTER TABLE EMPLOYEE ADD CONSTRAINT CHK CHECK (GENDER IN( 'M','F'));

STEP 3:

Now, you are going to delete the constraint, but before you drop the constraint, let us show you, how to get the constraint name first.

It is a necessary step if you are not the DBA who created the constraint on the relationship and you cannot delete the constraint unless you have appropriate permission.

SELECT  CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'you table name';

In the above command, you only need to change the “Your Table Name’ with a table name that exists. In this example, EMPLOYEES.

STEP4:

Now that I have the name of the constraint , We can delete the constraint using following command.

ALTER TABLE EMPLOYEE DROP CONSTRAINT CHK;

Summary

Following the above four steps can help you to identify and remove the constraints set on your database tables. Note that these commands are performed on Oracle 10g, therefore, you must be careful while running the same on other database systems. The command and procedure may be different.

Understanding GROUP BY and HAVING Clause

We use SELECT-FROM-WHERE kind of statement to run queries that get us specific rows from a relation. In the select statement, WHERE is the condition that returns specific rows from a relation.

What if we want to group the information? The GROUP BY and HAVING clause helps you to group the resultant rows by a specific column. GROUP BY and HAVING clause is used with aggregate functions like Count, Max, Min, Sum, etc;

This is easy to understand with an example.

Step 1:

Create a relation for students – Student ( rollno, name, age);

CREATE TABLE STUDENT (ROLLNO NUMBER(5) PRIMARY KEY,NAME VARCHAR2(30), AGE NUMBER(3), GRADE CHAR(2));

STEP 2:

Enter details of at least 10 students in to the STUDENT relation.

INSERT INTO STUDENT VALUES(50001,'DHANUSH',39,'A'); 
INSERT INTO STUDENT VALUES(50002,'RAJNI',29,'D'); 
INSERT INTO STUDENT VALUES(50003,'VIKRAM',32,'S'); 
INSERT INTO STUDENT VALUES(50004,'VADIVEL',25,'D'); 
INSERT INTO STUDENT VALUES(50005,'PRASAD',37,'S'); 
INSERT INTO STUDENT VALUES(50006,'SHANKAR',21,'A'); 
INSERT INTO STUDENT VALUES(50007,'RAM',33,'A'); 
INSERT INTO STUDENT VALUES(50008,'KARTICK',22,'C'); 
INSERT INTO STUDENT VALUES(50009,'SHAKTI',35,'B'); 
INSERT INTO STUDENT VALUES(50010,'SIMBU',20,'B');

To check the entered value in the table, you can run following query against the STUDENT relation.

SELECT * FROM STUDENT.

 STEP 3:

You can run two simple queries to understand the difference between a query without group by and a query with GROUP BY clause. First, you want a student with minimum age from the STUDENT relation using an aggregate function MIN().

SELECT MIN(S.age) FROM STUDENT S;

You can add WHERE clause, but we want query without any conditions. It will produce the following results.

You can see that there is only one person in an entire student relation whose minimum age is returned.Suppose you want to write a query to get the minimum age of student by student Grade. It means this statement “What is the minimum age of students who got A’s ?”  or “What is the minimum age of students who got B’s?” and so on.

Let’s run the following query that will get you the minimum age grouped by student GRADE.

SELECT S.GRADE, MIN(S.age) FROM STUDENT S GROUP BY S.GRADE;

What is the Difference ?

In the first query, we treated the entire STUDENT relation as one group and that’s why we only have a single value for the whole relation.In the second query, we grouped STUDENT relation by student grade and for each of these grade got minimum age.

The HAVING clause

The HAVING clause is qualification for the Group By clause. It means you are putting more conditions on the resulting rows from GROUP BY clause.

SELECT S.GRADE, MIN(S.age) FROM STUDENT S GROUP BY S.GRADE;

You know that it will return a list of minimum age by student grade, but suppose you want to see only minimum age that are greater than or equal to 30.

SELECT S.GRADE, MIN(S.age) FROM STUDENT S GROUP BY S.GRADE HAVING MIN(S.age) >= 30;

The result is only one row whose minimum age is greater than or equal to 30 but grouped by student grade.

Group by cannot have duplicate values and the general format of the query is given below.

SELECT [DISTINCT] select list 
FROM from-list 
WHERE qualification 
GROUP BY group-list 
HAVING group-qualification

Source: Database Management Systems – Raghu Ramakrishan

You want to understand the GROUP BY and HAVING clause, then create similar examples and run them against the relations.

🎁 Get the SQL Starter Kit (Free for Students)

Build your SQL foundation with these three essential PDFs:

1. SQL Basics Explained
2. MySQL Installation Guide
3. SQL Practice Questions (20 problems)

This starter kit is free, but requires a quick signup so we can send the PDFs directly to your inbox.

👉 Sign up and receive the download link instantly

JOIN Concepts in Relational Database Management System

JOIN put condition (selections and projections) on a CROSS-PRODUCT from two or more tables and the result is a smaller relation than the CROSS-PRODUCT

In DBMS, JOIN is an important operation in relational algebra and it extracts useful information from joining two or more relations. In this lab, you will create two tables TEACHER and STUDENT. A STUDENT studies under one or more TEACHER for different subjects, so student and teacher have a One-to-Many relationship.

Step 1: Create Table TEACHER and STUDENT.

Before we dive into the JOIN concept let’s create relations – TEACHER and STUDENT. You have to use the following SQL commands to do that. In the relations, TEACHER, and STUDENT, the TID and SID are primary keys respectively.

CREATE TABLE T (TID NUMBER(5) PRIMARY KEY , CNAME VARCHAR2(30));

CREATE TABLE STUDENT ( SID NUMBER(3) PRIMARY KEY, SNAME VARCHAR2(30), TNAME VARCHAR2(25), GRADE CHAR);

Step 2: Insert values into both the tables as follows

The next step is to insert values into both relations. First, you need to enter values for the TEACHER relation using following command,

INSERT INTO TEACHER VALUES(10001,'RAMESH'); 
INSERT INTO TEACHER VALUES(10002,'KIRAN'); 
INSERT INTO TEACHER VALUES(10003,'JOHN'); 
INSERT INTO TEACHER VALUES(10004,'PETER'); 
INSERT INTO TEACHER VALUES(10005,'FRODO');

An instance of TEACHER relation is given below.

Insert values into the STUDENT table as follows.

INSERT INTO STUDENT VALUES(501,'ANIL KUMAR' ,'RAMESH','S'); 
INSERT INTO STUDENT VALUES(502,'RAJESH KAPOOR','KIRAN','D'); 
INSERT INTO STUDENT VALUES(503,'SUBBARAJ','JOHN','A'); 
INSERT INTO STUDENT VALUES(504,'NAGESH ','PETER','C'); 
INSERT INTO STUDENT VALUES(505,'RAM PRASAD','FRODO','A'); 
INSERT INTO STUDENT VALUES(506, 'JASSE'); 
INSERT INTO STUDENT VALUES(507,'MADHU');

An instance of relation STUDENT is shown below, you must get similar results. For each student there is a teacher associated who taught a course.

The course relation is not required at this moment because these two relations are sufficient to understand the JOIN concepts in DBMS.

Cross-Product (Cartesian Product)

A Cross-Product is a product of two or more relations and is denoted by T ⨯ S. Suppose the relation Teacher has 5 tuples and the relation Student has 4 tuples, then the Cross-Product will have 5 x 4 = 20 tuples. Cross-Product is a JOIN without any conditions.

The Cross-Product operation results in a very large relation with multiple tuples. The JOIN operation accepts some conditions and applies them to the Cross-Product and result is a relational instance you want.

Depending on the condition applied you get different types of JOIN.

Note: A Cross-Product is also called NATURAL JOIN.

Equi-Join

SELECT * FROM TEACHER T, STUDENT D WHERE T.TNAME = D.TNAME;

This will return all the rows that are common in both teacher and student table.
i.e. t.tname = d.tname.

Left-Outer Join

In this join type, you get all rows that are common to both tables, (EQUI-JOIN) + remaining row from the left side table.

for example

SELECT T.TID,T.TNAME,S.SNAME, S.GRADE FROM TEACHER LEFT OUTER JOIN STUDENT ON T.TNAME = S.TNAME;

In the above command the TEACHER is left table and student is right table.

Right-Outer Join

In this type of JOIN, you get all row that are common to both table (EQUI-JOIN) + REMAINING ROWS from right side table.

SELECT S.SID, S.SNAME, T.TNAME FROM TEACHER T RIGHT OUTER JOIN STUDENT S ON T.TNAME = S.TNAME;

The student table is right hand side table, and you get most of your column from right hand side table. It means that the query returns rows that are in both table + rows in student table only.

🎁 Get the SQL Starter Kit (Free for Students)

Build your SQL foundation with these three essential PDFs:

1. SQL Basics Explained
2. MySQL Installation Guide
3. SQL Practice Questions (20 problems)

This starter kit is free, but requires a quick signup so we can send the PDFs directly to your inbox.

👉 Sign up and receive the download link instantly

Converting an E-R Model into Relational Model in DBMS

In this lesson you will learn to convert er model into relational model. First step in designing a database is to create an entity-relationship model. Then the entity-relationship model is converted into a relational model.

The relational model is nothing but a group of tables or relations that create for your database.

Read: DBMS Basics
Read: Basic DML commands
Read: Basic DDL commands

This article you will learn to convert an entity-relationship model into a relational model using an example. You will understand the process of modeling the real-world problem into an entity-relationship diagram and then convert that entity-relationship diagram into a relational model.

Problem Definition

In this example problem, you will create a database for an organization with many departments. Each department has employees and employees have dependents. To create a database for the company, read the description and identify all the entities from the description of the company.

• The company organized into departments and departments have employees working in it.
• Attributes of Department are dno, dname. Attributes of Employee include eno, name, dob,gender, doj, designation, basic_pay, panno, skills. Skills are multi-valued attribute.
• The Department has a manager managing it. There are also supervisors in Department who supervises a set of employees.
• Each Department enrolls a number of projects. Attributes of Project are pcode, pname. A project is enrolled by a department. An employee can work on any number of projects on a given day. The Date of employee work in-time and out-time has to keep track.
• The Company also maintains details of the dependents of each employee. Attributes of dependent include dname, dob, gender and relationship with the employee.

Model an entity-relationship diagram for the above scenario.

Solution – Converting ER model into Relational model

From the real-world description of the organization, we were able to identify the following entities. These entities will become the basis for an entity-relationship diagram or model.

Convert to Relational Model

The next step in the database design is to convert the ER Model into the Relational Model. In the Relational Model, we will define the schema for relations and their relationships. The attributes from the entity-relationship diagram will become fields for a relationship and one of them is a primary field or primary key. It is usually underlined in the entity-relationship diagram.

An entity in a relational model is a relation. For example, the entity Dependent is a relation in the relational model with all the attributes as fields – eno, dname, dob, gender, and relationship.

Here is the Relational Model for above diagram of the company database. This the result after converting ER model into relational model.

References

Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke, and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).

Understanding Basic SQL Queries in DBMS

Data Manipulation Language ( DML ) is the language used to maintain the database in a DMBS. The SQL (Structured Query Language) is the most popular language to manipulate databases.

DML Commands

The DML commands perform following tasks on a database.

• INSERT
• SELECT
• DELETE
• UPDATE

The SQL being a DML can do above task without any problem. Other than inserting, deleting or querying database, the SQL has advanced commands to change the schema of relations or create  new relations.

It is used for maintaining the database and they are called Data Definition Language (DDL) commands. You can create table, delete a table and do other operations to maintain the structure of your database.

INSERT command

The INSERT command helps insert data into the relation. For example, you can insert values in the ‘MANAGER’ relation as follows.

The schema for Manager relation is given in the previous post about DDL commands.

SELECT command

The SELECT command helps query the database and retrieve the tuples from one or more relations.

There are two types of query – Selection(σ)  and Projection(π).

Selection(σ)

This unary operator selects all the tuple in a relation that meet specific condition using normal conditional operators and logical operator such as AND, OR and NOT.

For example

, will return all the tuples that have an age greater than 30.

Projection(π)

This unary operator selects columns, but you can add selection with those retrieved columns.

For example

will return two column – age and salary.

Rename operator(ρ)

The rename operator will rename the existing field of a relation to a different specified name. Alter is a DDL command that modifies the relation name.

UPDATE command

The UPDATE command modifies the value for one or more tuple in the relation. In the following example, we have an updated salary of a manager whose salary is less than 20000.

The result of the update is as follows.

DELETE command

The DELETE will delete the tuple from the relation. Here is an example of DELETE.

The following figure shows the relation Manager after the delete operation. Note that one entry is already deleted successfully.

🎁 Get the SQL Starter Kit (Free for Students)

Build your SQL foundation with these three essential PDFs:

1. SQL Basics Explained
2. MySQL Installation Guide
3. SQL Practice Questions (20 problems)

This starter kit is free, but requires a quick signup so we can send the PDFs directly to your inbox.

👉 Sign up and receive the download link instantly

Basic Data Definition Language(DDL) Commands

The Data Definition Language( DDL ) commands are used for creating new or modify existing schema of a relation. You can also set constraints such as key constraints, foreign key constraints, etc on a relation.

CREATE TABLE Command

This command creates a new relation or table. You must specify the fields and data type for each field while using this command.

ALTER TABLE Command

This is most important of DDL commands because it allows us to modify the table anytime.
Here are some examples of ALTER TABLE command

Adding a primary key for the table. A primary key uniquely identifies a tuple in a relation.

Adding a foreign key for the table. A foreign key in a relation refer to primary key of another relation and cannot be null called the Referential Integrity.

In the above example, we receive an error because the attribute ‘DEPTNO’ is not set as primary key in relation to ‘EX_DEPT’.

After adding ‘DEPTNO’ as primary key we are able to set the foreign key for ‘MANAGER’ relation.

Adding a new column in a Table. In the following example, we are adding ‘PCODE‘ in the ‘MANAGER’ table.

Removing a column from a relation or table. In the following example, we are removing the ‘PCODE’ column from the ‘MANAGER’ table.

DESC <Table Name>

The ‘DESC’ command helps view the schema of the relation.

DROP TABLE <table name>

This command will drop the table permanently.

🎁 Get the SQL Starter Kit (Free for Students)

Build your SQL foundation with these three essential PDFs:

1. SQL Basics Explained
2. MySQL Installation Guide
3. SQL Practice Questions (20 problems)

This starter kit is free, but requires a quick signup so we can send the PDFs directly to your inbox.

👉 Sign up and receive the download link instantly

Normalization in DBMS

Designing a database structure without proper planning would cause duplicate data and problem updating the database. This will result in an inconsistent database. The process of normalization is to make an efficient database design that allow faster and efficient access while maintaining the accuracy.

What is Normalization?

Normalization is a database design technique that cuts down data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies. Normalization rules divide larger tables into smaller tables and attach them using relationships. Normalization in SQL’s objective is to get rid of redundant (repetitive) data and ensure data is stored logically.

Normalization is the process of reducing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update irregularities. So, it helps to minimize the extra in relations Normal forms are used to eliminate or cut down excess in database tables.

Normalization is used for mainly two purposes,

•       Eliminating redundant(useless) data.

•       Verifying data dependencies make sense that is data is logically stored.

What is a KEY in SQL?

A KEY in SQL is a value used to recognize records in a table uniquely. An SQL KEY is a single column or addition of multiple columns used to uniquely spot rows or tuples in the table. SQL Key is used to point out duplicate information, and it also helps establish a relationship between multiple tables in the database.

Note: Columns in a table that are NOT used to identify a record distinctive are called non-key columns.

What is a Primary Key?

A Primary Key is the minimal set of attributes of a table that has the task of uniquely identifying the rows, or we can say the tuples of the given particular table.

A primary key of a relation is one of the possible candidate keys which the database designer thinks it’s primary. It may be selected for favorable, performance, and many other reasons. The choice of the possible primary key from the candidate keys depends upon the following conditions. Rules for defining the Primary key are:-

• Two rows can’t have the same primary key value
• It must be for every row to have a primary key value.
• The primary key field cannot be false.
• The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Why do we need Primary Key?

Suppose for any identification we need a different feature that makes it special(different) from others. Everyone has a unique specialty that makes us judge and verifies them based on that quality one has in them.

 Similarly, for differentiating a table from others and for identifying them we need a special key control that can be used to validate the records of that table and maintain uniqueness, consistency, and completeness.

Also, for joining any two tables in the relative database systems, a Primary key and another table’s Primary key are known as a Foreign Key, which plays an important role.

So, we can use this Primary Key while creating a table or change it in the database. If a Primary Key is already present in the table, then the server or system will not allow inserting a row with the same key. It helps to improve the security of database records.

What is the Foreign Key?

FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data constancy and allow navigation between two different instances of an organization. It acts as a double verification between two tables as it references the primary key of another table.

Foreign Key references the primary key of another Table! It helps connect your Tables

• A foreign key can have a different name from its primary key

• It ensures rows in one table have relative rows in another, Unlike the Primary key, they do not have to be special from others. Most often they don’t have.

• Foreign keys can be invalid even though primary keys cannot be empty.

Example of Foreign Key

Consider two tables Student and Department having their particular attributes as shown in the below table structure: –

In the tables, one attribute, you can see, is common, that is Std_Id, but it has different key constraints for both tables. In the Student table, the field Std_Id is a primary key because it is uniquely identifying all other fields of the Student table.

 On the other hand, Std_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Std_Id attribute.

Difference Between Primary key & Foreign key

Following are the main difference between the primary key and foreign key:

Primary Key      

• Helps you to uniquely identify a record in the table.
• Primary Key never accepts clear values. 
• The primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index.       
• You can have the single Primary key in a table.

Foreign Key

• It is a field in the table that is the primary key of another table.
• A foreign key may accept multiple empty values.
• A foreign key cannot automatically create an index, grouped or non-grouped. However, you can manually create an index on the foreign key.
• You can have multiple foreign keys on a table.

What is the Composite key?

COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns ensures uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.

The difference between the compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not be a part of the foreign key.

What is a Candidate’s Key?

CANDIDATE KEY in SQL is a set of attributes that uniquely identify substrings in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Advantages of Normalization:

• Normalization helps to cut down the data redundancy.
• Greater overall database organization.
• Data consistency within the database.
• Much more workable database design.
• Applies the concept of relational integrity.

Disadvantages of Normalization:

• You cannot start building the database before knowing what the user needs.
• The performance degrades when normalizing the relations to higher normal forms, that is 4NF, 5NF.
• It is very time-consuming and difficult to normalize relations of a peak degree.
• Careless decomposition may lead to a bad database design, leading to serious problems.

Database Normal Forms

Here is a list of Normal Forms in SQL:

• 1NF (First Normal Form)
• 2NF (Second Normal Form)
• 3NF (Third Normal Form)
• BCNF (Boyce-Codd Normal Form)
• 4NF (Fourth Normal Form)
• 5NF (Fifth Normal Form)
• 6NF (Sixth Normal Form)

1st Normal Form (1NF)

It is Step 1 in the Normalization procedure and is considered the most basic requirement for getting started with the data tables in the database. If a table in a database is not capable of forming a 1NF, then the database design is considered to be poor. 1NF proposes a scalable table design that can be extended easily to make the data retrieval process much simpler.

For a table in its 1NF,

1. Data Atomicity is maintained. The data present in each attribute of a table cannot contain more than one value.
2. For each set of related data, a table is created and the data set value in each is identified by a primary key applicable to the data set.

The multiple values from EMP_PHONE made atomic based on the EMP_ID to satisfy the 1NF rules.

2^nd Normal Form (2NF)

The basic prerequisite for 2NF is:

1. The table should be in its 1NF,
2. The table should not have any partial dependencies

Partial Dependency: It is a type of functional dependency that occurs when non-prime attributes are partially dependent on part of Candidate keys.

Example:

• If manager details are to be fetched for an employee, multiple results are returned when searched with EMP_ID, to fetch one result, EMP_ID and PROJECT_ID together are considered as the Candidate Keys.
• Here the manager depends on PROJECT_ID and not on EMP_ID, this creates a partial dependency.
• There are multiple ways to get rid of this partial dependency and cut off the table to its 2nd normal form, one similar method is adding the Manager information to the project table as shown below.

3rd Normal Form (3NF)

3NF ensures referential integrity, cut down the duplication of data, cuts down data anomalies, and makes the data model more informative. The basic prerequisite for 3NF is,

1.      The table should be in its 2NF, and

2.      The table should not have any transitive dependencies

Transitive dependency: It occurs due to a resulting relationship within the attributes when a non-prime attribute has a functional dependency on a prime attribute.

Example:

• In Table the PERFORMANCE_SCORE depends on both the employee and the project that he is associated with, but in the last column, HIKE depends on PERFORMANCE_SCORE.
• The hike changes with performance and here the attribute performance score is not a primary key. This forms a transitive dependency
• To get rid of the transitive dependency created, and to satisfy the 3NF, the table is broken as illustrated below:-

Boyce-Codd Normal Form BCNF

BCNF deals with the anomalies that 3 NF fails to address. For a table to be in BCNF, it should satisfy two conditions:

1.      The table should be in its 3 NF form

2.      For any dependency, A à B, A should be a super key i.e. A cannot be a non-prime attribute when B is a prime attribute.

Example:

The EMP_ID and PROJECT_ID together can fetch the Department details associated with the employee. i.e.

• EMP_ID + PROJECT_ID → DEPARTMENT
• (Candidate keys)
• DEPARTMENT which is not a super key is dependent only on PROJECT_ID but not dependent on EMP_ID
• Department → Project_ID
• (Non-prime attribute) (prime attribute)

To make this table satisfy BCNF, we need to break the table as shown below:-

Here the department table is created such that each department id is unique to the department and project-related to it.

It is very important to ensure that the data stored in the database is meaningful and the chances of anomalies are minimal to zero. Normalization helps in reducing data redundancy and helps make the data more meaningful.

Normalization follows the principle of ‘Divide and Rule’ wherein the tables are divided until a point where the data present in them makes actual sense. It is also important to note that normalization does not fully get rid of the data redundancy rather its goal is to minimize the data redundancy and the problems associated with it.

Summary

So, you have learnt the normalization process for designing an efficient database. In most of the cases, the 3rd normal form is enough to create an excellent structure and we never goes to a higher normal form.

It is because of the normal forms a proper functional dependency is maintained.

Locks in Database Management

Previously, you learned about the concurrency mechanism in database. You know that the concurrency is maintained in a serialized manner, giving the impression that there is indeed some concurrency. The locking technique has to do a lot with this achievement of efficient access to database.

What is a Lock?

A lock is a variable associated with a data item that describes the status of the item concerning possible operations that can be applied to it. Accordingly, there is one lock for each data item in the database. Locks are used as a means of synchronizing the access by concurrent transactions to the database item.

Locking protocols are used in database management systems as a means of concurrency control. Many transactions may request a lock on a data item simultaneously. So, we require a mechanism to manage the locking requests made by transactions. Such a  process is called Lock Manager.

It relies on the process of message passing where transactions and lock managers exchange messages to handle the locking and unlocking of data items.

Concurrency control protocols can be broadly divided into two categories −

• Lock based protocols
• Timestamp based protocol

Lock-based Protocols

Database systems equipped with lock-based protocols use a method by which any transaction cannot read or write data until it acquires an appropriate lock on it. Locks are of two varieties:

• Binary Locks − A lock on a data item can be in two states; it is either locked or unlocked.
• Shared/exclusive − This type of locking method differentiates the locks based on their uses. If a lock is obtained on a data item to perform a write operation, it is an exclusive lock.

Allowing more than one transaction to write on the same data item would lead the database into an incompatible state. Read locks are divided because no data value is being changed.

Types of Locks

Several types of locks are used in concurrency control. To implement locking concepts gradually, we need to talk about binary locks, which are simple but restrictive and so are not used in practice.

 After it shared/exclusive locks, which provide more general locking capabilities and are used in practical database locking schemes.

What are Binary Locks?

A binary lock can have two states or values locked and unlocked.

A distinct lock is related to each database item A. If the value of the lock on A is 1, item A cannot gain access by a database operation that requests the item.

If the lock value on A is 0 then the item can be accessed when requested. We refer to the current value of the lock associated with item A as LOCK (A).

 There are two operations, lock item and unlock item are used with binary locking A transaction requests access to an item A by first issuing a locked item (A) operation. If LOCK (A) = 1, the transaction is forced to wait. If LOCK (A) = 0 it is set to 1 (the transaction locks the item) and the transaction is allowed to access item A. 

When the transaction is through using the item, it issues an unlock item (A) operation, which assigns LOCK (A) to 0 (unlocks the item) so that A may be accessed by remaining transactions. Hence binary lock imposes mutual exclusion1 on the data item.

Rules of Binary Locks

Incase the simple binary locking scheme described here is used, every transaction must obey the following rules:

• A transaction must supply the operation lock_item (A) before any read_item (A) or write, item operations are performed in T.
• A transaction T must supply the operation unlock_item (A) after all read_item (A) and write_item (A) operations are completed in T.
• A transaction Twill does not supply a lock _item (A) operation if it already holds the lock on Item A.
• A transaction will not issue an unlock _item (A) operation unless it already holds the lock on item A.
• The lock manager module of the DBMS can implement these rules. Between the Lock_item (A) and unlock_item (A) operations in transaction T, is said to hold the lock on item A. 

At most one transaction can hold the lock on a particular item. Thus no two transactions can access the same item together.

Merits of Binary Locks

• They are simple to execute since they are effectively mutually exclusive and establish isolation perfectly.

• Binary Locks request less from the system since the system must only keep a record of the locked items. The system is the lock manager subgroup which is a feature of all DBMSs today.

Disadvantages of Binary Locks

As discussed earlier, the binary locking scheme is too restrictive for database items, because at most one transaction can hold a lock on a given item. So, a binary locking system cannot be used for practical purposes.

One of the methods to ensure isolation of property in the transactions is to require data items to be accessed in a mutually exclusive manner. That means, that while one transaction is accessing a data item, no other transaction can make changes to that data item.

So, the most common method used to implement requirements is to allow a transaction to access a data item only if it is currently holding a lock on that item.

Thus, the lock on the operation is required to ensure the isolation of the transactions.

How Do Shared Locks Function?

• Shared locks survive when two transactions are granted read access.
• One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
• Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released. There is no difference with the shared lock because nothing is being updated.
• Shared locks last if they need to last; it depends on the level of the transaction that holds the lock.
• Shared locks keep up read integrity. They check whether a record is not in process of being updated during a read-only request.
• Shared locks can also be used to stop any kind of updates of record.
• It is represented by Lock-S which is a read-only lock.
• S-lock is appealed using Lock-S instruction.

Example:

Consider a case where initially A=100 and two transactions are reading A. If one of the transactions wants to update A, in that case, the other transaction would be reading the wrong value.

However, the Shared lock prevents it from updating until it has finished reading!

Exclusive Locks

We should allow several transactions to access the same item A if they all access A’ for reading purposes only. However, if a transaction is to write an item A, it must have total access to A

. For this purpose, a different type of lock called a multiple-mode lock is used. In this scheme, there are exclusive or read/write locks are used.

Example:

Consider a transaction(T2) that requires updating the data item value A. The following steps take place when lock protocol is applied to this transaction.

• T2 will acquire an exclusive lock on the data item A
• Read the current value of data item A
• Change the data item as required. In the example illustrated, a value of 50 is subtracted from the data item A
• Write the updated value of the data item
• Once the transaction is finished, the data item will be unlocked.

Differences between Shared Lock and Exclusive Lock

Shared Lock:

• Lock mode is a read-only operation.                                   
• Shared lock can be placed on objects that do not have an exclusive lock already placed on them.
• Prevents others from updating the data.                         
• Provided when the transaction wants to read an item that does not have an exclusive lock.     
• Any number of transactions can hold a shared lock on an item.    
• S-lock is appealed using lock-S instruction.    

Exclusive lock:

• Lock mode is read as well as write operation.
• Exclusive lock can only be placed on objects that do not have any other kind of lock.
• Prevents others from reading or updating the data.
• Provided when transaction wants to update unlocked item.
• Exclusive lock can be observed by only one transaction.
• X-lock is sought using lock-X instruction.

Locking operations

There are three locking operations called read_lock(A), write_lock(A) and unlock(A) represented as lock-S(A), lock-X(A), unlock(A) (Here, S indicates shared lock, X indicates exclusive lock) can be performed on a data item. appealed

A lock related to an item A, LOCK (A), now has three possible states: “read-locked”, “write-locked,” or “unlocked.” A read-locked item is also called a share-locked item because various transactions are allowed to read the item, whereas a write-locked item is caused exclusive-locked. Hence, a single transaction exclusively holds the lock on the item.

Compatibility of Locks

Suppose that there are A and B two different locking modes. If a transaction T1 requests a lock of mode on item Q on which transaction T2 currently holds a lock of mode B.

 If the transaction can be granted the lock, despite the presence of the mode Block, then we say mode A is compatible with mode B. Such a function is shown in one matrix as shown below:

The graphs show that if two transactions only read the same data object they do not conflict, but if one transaction writes a data object and another either reads or writes the same data object, then they dispute with each other.

A transaction requests a shared lock on data item Q by executing the lock-S(Q) instruction. Similarly, an exclusive lock is appealed through the lock- X(Q) instruction. A data item Q can be free via the unlock(Q) instruction.

To access a data item, transaction T1 must first lock that item. If the data item is already locked by another transaction in an opposite mode, the concurrency control manager will not allow the lock until all opposed locks held by other transactions have been released. Thus, T1 is made to wait until all opposed locks held by other transactions have been released.

There are four types of lock protocols available are: –

Simplistic Lock Protocol

Simplistic lock-based protocols allow transactions to obtain a lock on every object before a ‘write’ operation is performed. Transactions may unlock the data item after completing the ‘write’ operation.

Pre-claiming Lock Protocol

Pre-claiming protocols estimate their operations and create a list of data items on which they need locks. Before starting execution, the transaction requests the system for all the locks it needs beforehand.

If all the locks are allowed, the transaction executes and releases all the locks when all its operations are over. If all the locks are not allowed, the transaction rolls back and waits until all the locks are granted.

Two Phase Locking 2PL

This locking protocol divides the implementation phase of a transaction into three parts. In the first part, when the transaction starts working, it seeks permission for the locks it requires.

The second part is where the transaction obtains all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot order any new locks; it only releases the acquired locks.

Two-phase locking has two phases, one is increasing, where all the locks are being acquired by the transaction; and the second phase is decreasing, where the locks held by the transaction are being released.

To claim an exclusive (write) lock, a transaction must first obtain a shared (read) lock and then upgrade it to an exclusive lock.

Strict Two-Phase Locking

The first phase of Strict-2PL is the same as 2PL. After obtaining all the locks in the first phase, the transaction continues to execute normally.

 But in contrast to 2PL, Strict-2PL does not release a lock after using it. Strict-2PL holds all the locks until the commit point and releases all the locks at a time.

Strict-2PL does not have to cascade termination as 2PL does.

Timestamp-based Protocols

The most used concurrency protocol is the timestamp-based protocol. This protocol uses either system time or a logical counter as a timestamp.

Lock-based protocols manipulate the order between the conflicting pairs among transactions at the time of execution, whereas timestamp-based protocols start working as soon as a transaction is created.

Every transaction has a timestamp related to it, and the ordering is determined by the age of the transaction. A transaction created at 0002 clock time would be older than all other transactions that come after it. For example, any transaction ‘y’ entering the system at 0004 is two seconds younger and the priority would be given to the older one.

In addition, every data item is given the latest read and write-timestamp. This lets the system know when the last ‘read and write’ operation was performed on the data item.

Timestamp Ordering Protocol

The timestamp-ordering protocol checks serializability among transactions in their conflicting read and writes operations. This is the responsibility of the protocol system that the various pair of tasks should be executed according to the timestamp values of the transactions.

Let’s assume there are two transactions T1 and T2. Suppose the transaction T1 has entered the system at 007 times and transaction T2 has entered the system at 009 times. T1 has the higher priority, so it executes first as it is entered the system first.

The priority of the older transaction is higher that’s why it executes first. To decide the timestamp of the transaction, this protocol uses system time or logical counter.

• The timestamp of transaction T_i is represented as TN(T_i).
• Read time-stamp of data-item X is represented by R-timestamp(X).
• Write time-stamp of data-item X is shown by W-timestamp(X).

Timestamp ordering protocol works as follows −

• If a transaction Ti issues a read(X) operation −

• If TN(Ti) < W-timestamp(X)
  • Operation rejected.
• If TN(Ti) >= W-timestamp(X)
  • Operation executed.
• All data-item timestamps are updated.

• If a transaction Ti issues a write(X) operation −

• If TN(Ti) < R-timestamp(X)
  • Operation rejected.
• If TN(Ti) < W-timestamp(X)
  • Operation rejected and Ti rolled back.
• Otherwise, the operation is executed.

Concurrency control is important in DBMS for handling the simultaneous execution of transactions among various databases.

 Lock Based Protocols being an essential member of the concurrency control technique enforces isolation among the transactions, preserves and maintains the reliability of the database, and resolves the disputes of read-write and write-read operations.

 In addition to Lock-based Protocols, concurrency control can also be achieved via methodologies such as Timestamp Protocol, Multiverse concurrency Protocol, and Validation Concurrency Protocols.

Advantages and Disadvantages of Timestamp Ordering protocol:

• Timestamp Ordering protocol ensures serializability since the precedence graph is as follows:

• TS protocol ensures freedom from deadlock which means no transaction ever be in waiting.
• But the schedule may not be modified back and may not even be cascade-free.

Summary

You have learned about different types of lock and their efficiencies, drawbacks in this article. The idea of the efficient lock is to maintain faster access and keep the database consistent. This is done when the transaction is kept atomic and isolated.

Concurrency Control

The concurrency control concept comes under the Transaction in the database management system (DBMS). It is a procedure in DBMS which helps us control two simultaneous processes to execute without conflicts among every other, these conflicts occur in multi-user systems.

Concurrency explains executing multiple transactions at a time. It is required to increase time efficiency. If many transactions try to access the same data, then irregularity arises. Concurrency control is required to maintain consistent data.

For example, if we take ATMs and do not use concurrency, multiple persons cannot draw money at a time in different places. Hence, we need concurrency.

Advantages

The advantages of concurrency control are: –

• Waiting time will be decreased.
• Response time will gradually decrease.
• It increases Resource Utilization.
• System accuracy & Efficiency is increased.

Control Concurrency

The simultaneous execution of transactions over shared databases can create several data integrity and consistency problems.

For example, if too many people are logging in to the ATMs, serial updates and synchronization in the bank servers should manifest whenever the transaction is done, if not it gives wrong information and wrong data in the database.

Main problems with using Concurrency

The problems which arise while using concurrency are as follows −

• Updates will be lost – If a single transaction does some changes, and another transaction removes that change. One transaction empties the updates of another transaction.

For example:

Consider the below diagram in which two transactions T_X and T_Y, are performed on the same account A where the balance of account A is $300.

• At time t1, transaction T_X reads the value of account A, i.e., $300 (only read).
• At time t2, transaction T_X deducts $50 from account A which becomes $250 (only deducted and not updated/written).
• Alternately, at time t3, transaction T_Y reads the value of account A that will be $300 only because T_X didn’t update the value yet.
• At time t4, transaction T_Y adds $100 to account A which becomes $400 (only added but not updated/written).
• At time t6, transaction T_X writes the value of account A that will be updated as $250 only, as T_Y didn’t update the value yet.
• Similarly, at time t7, transaction T_Y writes the values of account A, so it will write as done at time t4 which will be $400. Then the value written by T_X is lost, i.e., $250 is lost.

As a result, data becomes incorrect, and database sets to inconsistent.

•  Dirty read problem – The variable which updated in one transaction, at the same time another transaction has started and deleted the value of the variable there the variable is not getting updated or committed that has been done on the first transaction this gives us false values or the previous values of the variables this is a major problem.

For example:

Consider two transactions T_X and T_Y in the below diagram executing read/write operations on account A where the available balance in account A is $300:

At time t1, transaction T_X reads the value of account A, i.e., $300.

At time t2, transaction T_X adds $50 to account A which becomes $350.

At time t3, transaction T_X writes the newly refurbished value in account A, i.e., $350.

Then at time t4, transaction T_Y reads account A which will be read as $350.

Then at time t5, transaction T_X rollbacks because of a server problem, and the value changes back to $300 (as initially).

But the value for account A remains $350 for transaction T_Y as committed, which is the dirty read and therefore known as the Dirty Read Problem.

Inconsistent retrievals − One transaction is updating multiple different variables, another transaction is in the process to update those variables, and the problem that occurs is the inconsistency of the same variable in various instances.

Concurrency control techniques

The concurrency control techniques are as follows –

Locking

Lock guarantees exclusive use of data items to a current transaction. It first gains the data items by acquiring a lock, after completion of the transaction it releases the lock.

Types of Locks

The variety of locks is as follows: –

• Shared Lock [Transaction can read only the data item values]
• Exclusive Lock [Used for both reading and writing data item values]

Time Stamping

The timestamp is a unique identifier created by DBMS that indicates the relative starting at the time of a transaction. Whatever transaction we are doing stores the starting time of the transaction and denotes a specific time.

This can be created using a system clock or logical counter. This can be implemented whenever a transaction is started. Here, the logical counter in addition after a new timestamp has been assigned.

Optimistic

It is based on the belief that conflict is rare, and it is more efficient to allow transactions to proceed without implementing delays to ensure serializability.

Summary

You are now familiar with concepts of transaction management and concurrency. The concurrency control management of database employs various techniques that ensures that database access smooth and efficient. The concurrency control also makes sure that the database is always in a consistent state.