Introduction to SQL
What is SQL?
SQL stands for Structured Query Language.
It is a language used to communicate with the database.
SQL is used to store, retrieve, manipulate, and manage data in database system .
Introduction to Data
DATA:- Data is a raw fact that describes the attributes or properties of an object or entity.
Each of these properties can hold specific data (values) that describe the student. Here's an example
DATABASE
What is a Database?
Database:- A Database is a place or a medium which is used to store data.
Modified Definition
A Database is a place or medium used to store data in a systematic and organized manner, so that it can be easily
accessed and managed when needed.
CRUD Operations in a Database
The most basic operations you perform on a database are called CRUD operations. These stand for:
Operation Description
C – Create/ Insert Insert new data into the database
R – Read/ Retrieve Retrieve or access existing data
U – Update/ Modify Modify or change existing data
D – Delete/ Drop Remove data from the database
DBMS – Database Management System
DBMS (Database Management System):
➢ It is a software which is used to manage/maintain the database.
➢ It provides security for our database.
➢ We use query language to communicate or interact with DBMS.
➢ DBMS stores the data in the form of files.
RDBMS (Relational Database Management System):
➢ It is a software which is used to manage/maintain the database.
➢ It provides security for our database.
➢ We use structured query language to communicate or interact with
DBMS.
➢ DBMS stores the data in the form of tables.
Difference Between DBMS and RDBMS
DBMS (Database Management RDBMS (Relational Database
S.No
System) Management System)
1. Stores data in file format Stores data in table (rows and columns)
format
Uses query language (general term) Uses Structured Query Language (SQL)
2.
for communication to communicate with the database
Basic security and authorization Provides advanced security and
3.
features authorization features
Difficult to perform CRUD operations
4. (Create, Read, Update, Delete) as Easy to perform CRUD operations
compare to RDBMS.
Data can be related/connected using keys
5. Data cannot be connected/related
and constraints
Suitable for handling large volumes of
6. Best suited for small amount of
data
Provides higher-level security and access
7. Provides low-level security control
Does not support relationships Supports relationships using primary
8.
between data entities keys, foreign keys, etc.
RDBMS - Relational Database Management System (Summary Notes)
1. RDBMS Works on Relational Model
• A relational model stores data in the form of tables (rows and columns).
• It was proposed by E. F. Codd in 1970.
• RDBMS is based on:
o Relational Model
o E. F. Codd Rules
RULES OF E.F. CODD:
RULE 1:- Data entered into a cell must be single valued data.
EID ENAME PHONE_NO
1 RAVI 9876543210
2 PRIYA 8765432109,9123456780
3 ANKITA 9012345678
Following Rule 1 (Atomic Value - Each cell has only one value)
EID ENAME PHONE_NO ALTERNATE_NO
1 RAVI 9876543210
2 PRIYA 8765432109 9123456780
3 ANKITA 9012345678
RULE 2:-In RDBMS we can store everything in the form of tables including meta data.
➢ Meta Data: Data about the data.
OR
Details about the data
RULE 3:-According to E.F. CODD rules data can be stored in multiple tables,
if needed we can establish connection between the tables using key attributes.
RULE 4:- In RDBMS data can be validated by 2 steps.
i) By assigning datatypes to a column.
ii) By assigning constraints to a column.
MULTIPLE JOIN CONDITION:-
EMP_NO ENAME MGR EMPNO ENAME MGR
1. SMITH 3
1. SMITH 3
2. ALLEN 4
2. ALLEN 4
3. WARD 2
3. WARD 2
4. SCOTT
4. SCOTT
EMPLOYEE E2
MANAGER TABLE
EMPLOYEE E1
EMP TABLE
EMPNO ENAME MGR
1. SMITH 3
2. ALLEN 4
3. WARD 2
4. SCOTT
E3
MANAGERS MANAGER TABLE
WAQTD ENAME & HIS MANAGER’S NAME & MANAGER’S MANAGER
NAME.
SELECT E1. ENAME, E2. ENAME, E3. ENAME
FROM EMP E1, EMP E2, EMP E3
WHERE E1.MGR = E2. EMPNO AND E2.MGR = E3. EMPNO;
ASSIGNMENT
1. WAQTD EMPLOYEE NAME AND HIS MANAGER'S NAME AND MANAGER'S
MANAGER NAME IF EMPLOYEE IS WORKING AS CLERK
SELECT E1.ENAME,E2.ENAME,E3.ENAME
FROM EMP E1,EMP E2,EMP E3
WHERE E1.MGR=E2.EMPNO AND E2.MGR=E3.EMPNO AND E1.JOB='CLERK';
2. WAQTD EMPLOYEE NAME & HID DNAME AND MANAGER'S DESIGNATION AND
HIS LOCATION IF MANAGER WORKS IN DEPT 10 OR 20
3. WAQTD NAME OF THE EMP AND MANAGERS SALARY ALONG WITH
MANAGER'S MANAGER HIREDATE IF MANAGER'S MANAGER EARN MORE THAN
2300
4. WAQTD EMP NAME AND MANAGER'S HIREDATE IF EMPLOYEE WAS HIRED
BEFORE 1982 AND MANAGER'S MANAGER IS NOT EARNING ANY COMMISSION.
5. WAQTD EMP NAME & HIS DEPT NAME AND MANAGERS NAME & HIS DNAME
AND MANAGERS NAME & HIS DNAME WHERE MANAGER'S MANAGER EARNS
MORE THAN MANAGER SALARY
CO - RELATED SUB QUERY: -
" A query written inside another query such that the outer query and the inner
query are Dependent on each other, this is known as Co-Related Sub-Query ".
WORKING PRINCIPLE:
FINAL OUTPUT OUTER QUERY OUTPUT(PARTICALLY)
INPUT
INPUT
OUTPUT INNER QUERY
➢ Let us consider two queries inner and outer query respectively,
➢ Outer query executes first but partially
➢ The partially executed output is given as an input to the inner Query
➢ The inner query executes completely and generates an output
➢ The output of inner query is fed as an input to the Outer query and Outer
Query produces the result.
➢ Therefore, we can state that the outer query and the inner query both are
INTERDEPENDENT (dependent on each other).
NOTE:
i. In co-related sub query Join condition is a must, and
must bewritten only in the Inner Query.
ii. Co-Related sub query works with the principles of
both SUBQUERY & JOINS.
WAQTD DNAME FOR ALL THE EMPLOYEES.
SELECT DNAME
FROM DEPT
WHERE D. DEPT NO IN (SELECT E. DEPTNO
FROM EMP
WHERE D. DEPTNO = E. DEPTNO);
DEPT EMP
DNAME DEPTNO ENAME DEPTNO
ACCOUNTING 10 TURNER 20
SALES 20 SMITH 10
OPERATION 30 WARD 20
RESEACRCH 40 ALLEN 30
10=20 (F)20=20(T) 30=20(F) 40=20 (F)
10=10(T) 20=10(F) 30=10(F) 40=10(F)
10=20(F) 20=20(T) 30=20(F) 40=20(F)
10=30(F) 20=30(F) 30=30(T) 40=30(F)
10 IN 10(T) 20 IN 10(F) 30 IN 10(F)
10 IN 20(F) 20 IN 20,20(T) 30 IN 20(F)
10 IN 30 (F) 20 IN 30 (F) 30 IN 30 (T)
RESULT TABLE
DNAME
ACCOUNTING
SALES
OPERATIONS
DIFFERENCE BETWEEN SUB QUERY & CO-RELATED SUBQUERY
SUB QUERY CO-RELATED SUB QUERY
Inner query executes first Outer query executes first
Outer query is dependent on inner query Both are interdependent
Join condition is not mandatory Join condition is mandatory and must
be written in inner query
Outer query executes Once Outer query executes twice
WAQTD DNAME IN WHICH THE EMPLOYEES ARE WORKING.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO);
WAQTD DNAME IN WHICH EMPLOYEES ARE NOT WORKING.
SELECT DNAME
FROM DEPT
WHERE DEPTNO NOT IN (SELECT DEPTNO
FROM EMP
WHERE EMP.DEPTNO=DEPT.DEPTNO);
EXISTS:
➢ Exists Operator is a Unary Operator (One Operand) which can accept only
one operand towards RHS and that operand has to be co related sub query.
➢ Exists Operator returns true if the Sub Query returns Any value other than
Null.
WAQTD DNAME IN WHICH EMPLOYEES ARE WORKING.
SELECT DNAME
FROM DEPT
WHERE EXISTS (SELECT DEPTNO
FROM EMP
WHERE EMP.DEPTNO=DEPT.DEPTNO);
NOT EXISTS:
➢ NOT EXISTS operator is a unary operator (one operand) which can accept
one operand towards RHS and that operand has to be a co-related sub
query.
➢ Not Exists Operator returns true if the Sub Query returns NULL.
WAQTD DNAME IN WHICH EMPLOYEES ARE NOT WORKING.
SELECT DNAME
FROM DEPT
WHERE NOT EXISTS (SELECT DEPTNO
FROM EMP
WHERE EMP.DEPTNO=DEPT.DEPTNO);
To find nth MAXIMUM & nth MINIMUM salary:
1. TO FIND THE MAXIMUM SALARY
SELECT SAL
FROM EMP E1
WHERE (SELECT COUNT (DISTINCT SAL)
FROM EMP E2
WHERE E1.SAL < E2.SAL) = N-1;
WAQTD 4TH MAXIMUM SALARY
SELECT SAL 1000
FROM EMP E1
WHERE (SELECT COUNT (DISTINCT SAL)
FROM EMP E2
WHERE E1.SAL < E2.SAL) = 4-1;
nd th
WAQTD 2 , 5 , 8th MAX SALARY
SELECT SAL
FROM EMP E1
WHERE (SELECT COUNT (DISTINCT SAL)
FROM EMP E2
WHERE E1.SAL < = E2.SAL) IN (2-1,5-1,8-1);
2. TO FIND THE MAXIMUM SALARY
SELECT SAL
FROM EMP E1
WHERE (SELECT COUNT (DISTINCT SAL)
FROM EMP E2
WHERE E1.SAL > E2.SAL) = N-1;
Q. WAQTD 4TH MINIMUM SALARY
SELECT SAL 3500
FROM EMP E1
WHERE (SELECT COUNT (DISTINCT SAL)
FROM EMP E2
WHERE E1.SAL>E2.SAL) IN 4-1;
1. WAQTD 5TH MAX SALARY
2. WAQTD 3RD MIN SALARY
TH TH
3. WAQTD 2ND ,5 , 8 MIN SALARY
TH
4. WAQTD 7TH MIN & 6 MIN SALARY
5. WAQTD 5TH MAX & 5TH MIN SALARY
DATA DEFINITION LANGUAGE
“DDL is used to construct an object in the database and deals with that structure
of object / table / entity.
TYPES OF DDL STATEMENTS:
• Create
• Rename
• Alter
• Truncate
• Drop
NOTE: DDL STATEMENTS ARE AUTO-COMMIT STATEMENTS
1. CREATE: It is used to build or construct the table in the
database.
SYNTAX: CREATE TABLE TABLE_NAME (
CN1 DATATYPE [CONSTRAINTS],
CN2 DATATYPE [CONSTRAINTS],
……
……
CNn DATATYPE [CONSTRAINTS]);
EX: STUDENT TABLE
CREATE TABLE STUDENT (
SID NUMBER (10) PRIMARY KEY,
SNAME VARCHAR (15) NOT NULL,
PH_NO NUMBER (10) UNIQUE NOT NULL CHECK (PH_NO>0)
CHECK (LENGTH(PH_NO) =10));
NOTE: TO DESCRIBE THE TABLE
SYNTAX: DESC TABLE_NAME;
2. RENAME: It is used to change the name of the existing table.
SYNTAX: RENAME EXISTING_TABLE_NAME TO NEW_TABLE_NAME;
EX: RENAME STUDENT TO STUDENTS;
3. ALTER: It is used to modify the structure of the table.
SUCH AS:
• To add the column
• To remove the column
• To rename the column
• To modify the datatype
• To modify the constraints
1. To add the column:
SYNTAX: ALTER TABLE TABLE_NAME
ADD COL_NAME DATATYPE CONSTRAINT;
EX: ALTER TABLE STUDENTS
ADD MAIL_ID VARCHAR (15) UNIQUE NOT NULL;
2. To remove the column:
SYNTAX: ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME;
EX: ALTER TABLE STUDENTS
DROP COLUMN MAIL_ID;
3. To rename the column:
SYNTAX: ALTER TABLE TABLE_NAME
RENAME COLUMN EXISTING_COLNAME TO NEW_COLNAME;
EX: ALTER TABLE STUDENTS
RENAME COLUMN PH_NO TO MOB_NO;
4. To modify the datatype:
SYNTAX: ALTER TABLE TABLE_NAME
MODIFY COL_NAME NEW_DATATYPE;
EX: ALTER TABLE STUDENTS
MODIFY SNAME VARCHAR (20);
5. To modify the constraints:
SYNTAX: ALTER TABLE TABLE_NAME
MODIFY COL_NAME EXISTING_DATATYPE
NEW_CONSTRAINT (NULL/NOT NULL);
EX: ALTER TABLE STUDENTS
MODIFY MAIL_ID VARCHAR (20) NULL;
HOW TO ADD THE CONSTRAINT:
SYNTAX: ALTER TABLE TABLE_NAME
ADD CONSTRAINT CON_REF_NAME
CONSTRAINT_TYPE(COL_NAME);
EX: ALTER TABLE STUDENTS
ADD CONSTRAINT UNI_MAIL UNIQUE(MAIL_ID);
TO DROP THE CONSTRAINT:
SYNTAX: ALTER TABLE TABLE_NAME
DROP CONSTRAINT CON_REF_NAME;
EX: ALTER TABLE STUDENTS
DROP CONSTRAINT UNI_MAIL;
HOW TO ASSIGN A FOREIGN KEY?
EX: CUSTOMER – CHILD TABLE PRODUCT – PARENT TABLE
CID CNAME PH_NO PID PID PNAME PRICE
1 ARJUN 9876660000 11 WATCH 25000
2 AKASH 7868804567 12 SHOES 10000
STEP 1: ADD THE COLUMN FROM PRODUCT TABLE INTO
CUSTOMER TABLE.
SYNTAX: ALTER TABLE TABLE_NAME
ADD COL_NAME DATATYPE;
EX: ALTER TABLE CUSTOMER
ADD PID NUMBER (5);
STEP 2: ASSIGN A FOREIGN KEY
SYNTAX: ALTER TABLE TABLE_NAME
ADD CONSTRAINT CON_REF_NAME FOREIGN
KEY(COLNAME) REFERENCES
PARENT_TABLE_NAME(COLNAME);
EX: ALTER TABLE CUSTOMER
ADD CONSTRAINT FK_PID FOREIGN KEY (PID) REFERENCES
PRODUCT(PID);
SYNTAX - 2: TO ADD A FOREIGN KEY:
CONSTRAINT PID _FK FOREGIN KEY (PID) PRODUCT (PID);
-- Step 1: Create Teacher Table
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(50)
);
-- Step 2: Create Student Table with FK while creating
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
student_age INT,
teacher_id INT,
CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
4. TRUNCATE: It is used to remove all the records/data from the table
Permanently (but structure of the table will be their).
SYNTAX: TRUNCATE TABLE TABLE_NAME;
EX: TRUNCATE TABLE CUSTOMER;
5. DROP: It is used to remove table from the
database.
SYNTAX: DROP TABLE TABLE_NAME;
EX: DROP TABLE CUSTOMER;
Bin Folder
CUSTOMER
CID CNAME PH_NO DROP Bin$123abc
1 ARJUN 9876660000 Address
2 AKASH 7868804567
TO RECOVER THE TABLE FROM BIN FOLDER:
FLASHBACK: it is used to restore the table from the bin folder.
SYNTAX: FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
EX: FLASHBACK TABLE CUSTOMER TO BEFORE DROP;
TO DELETE THE TABLE FROM BIN FOLDER:
PURGE: it is used to delete the table from the bin folder permanently.
SYNTAX: PURGE TABLE TABLE_NAME;
EX: PURGE TABLE CUSTOMER;
DATA MANIPULATION LANGUAGE
DML is used to manipulate the table / object by performing insertion, updating
and deletion of records.
TYPES OF DML STATEMENTS:
• Insert
• Update
• Delete
1. INSERT: It is used to create / insert the records into the table.
SYNTAX-1: INSERT INTO TABLE_NAME VALUES (V1, V2, V3……, Vn);
EX: INSERT INTO CUSTOMER VALUES
(1,’ARYA’,9988776655, A@GMAIL.COM’);
SYNTAX -2: INSERT INTO TABLE_NAME VALUES (&COL1, &COL2,
&COL3 .......&COLn);
EX: INSERT INTO CUSTOMER VALUES (&CID, &CNAME, &PH_NO);
2. UPDATE: It is used to modify / update the existing values in the table.
SYNTAX: UPDATE TABLE_NAME
SET COL_NAME=VALUE, COL_NAME=VALUE
[WHERE <FILTER_CONDITION>];
EX: UPDATE CUSTOMER
SET PH_NO=0987654321
WHERE CNAME = ‘ARYA’;
3. DELETE: It is used to remove / delete the particular record from the table.
SYNTAX: DELETE
FROM TABLE_NAME
[ WHERE<FILTER_CONDITION>];
EX: DELETE
FROM CUSTOMER
WHERE CNAME=’ARYA’;
REMEMBER: we can delete all records with the help of delete statement
but without passing any condition.
NOTE:
1. DML STATEMENTS ARE NOT AUTO COMMITTED (ARE N’T AUTO SAVED).
2. DDL STATEMENTS ARE AUTO COMMITTED (ARE AUTO SAVED).
TRANSACTION CONTROL LANGUAGE
“It is used to control the transaction done on the database.”
TRANSACTION: The DML operations performed on the database is called as
transaction such as insertion, updating and deletion of records.
3 STATEMENTS
• Commit
• Rollback
• Save point
1. COMMIT: It is used to save the records / transaction in the database.
SYNTAX: COMMIT;
INSERT INTO EMP VALUES (1, ‘Arya’);
INSERT INTO EMP VALUES (2, ‘Arjun’);
INSERT INTO EMP VALUES (3, ‘Akash’);
COMMIT;
2. ROLLBACK: It is used to get back to the latest saved
location. SYNTAX: ROLLBACK;
2. SAVEPOINT: It is used to give the restoration point or mark the position or
checkpoints.
SYNTAX: SAVEPOINT SAVEPOINT_NAME;
EX: SAVEPOINT S1;
ROLLBACK To SAVEPOINT_NAME;
SAVEPOINT_NAME; EX: SAVEPOINT S1;
10 RECORDS ARE INSERTED - SAVEPOINT S1;
10 RECORDS ARE INSERTED – SAVEPOINT S2;
10 RECORDS ARE INSERTED – SAVEPOINT S3;
COMMAND – ROLLBACK TO S3;
ALL 30 RECORDS WILL BE DISPLAYED.
ROLLBACK TO S2;
FIRST 20 RECORDS WILL BE DISPLAYED.
ROLLBACK TO S1.
FIRST 10 RECORDS WILL BE DISPLAYED.
DATA CONTROL LANGUAGE
“It is used to control the dataflow between the users.”
We have 2 statements:
• GRANT
• REVOKE
1. GRANT: it is used to give the permission to user.
SYNTAX: GRANT SQL_STATEMENTS
ON TABLE_ NAME
TO USER_NAME;
REVOKE: it is used to take back the permission given to the user.
SYNTAX: REVOKE SQL_STATEMENTS
ON TABLE_NAME
FROM USER_NAME;
SINGLE ROW FUNCTIONS
1. LENGTH:
“It is used to count the no. of characters in the given string.”
SYNTAX: LENGTH (‘STRING’)
EX: LENGTH (‘QSPIDERS’)
SELECT LENGTH
(‘QSPIDERS’) FROM DUAL; O/P: 8
NOTE:
DUAL TABLE: It is a DUMMY table which has one column and one row
Which is used to display the output.
Example:
WAQ to count the number of characters present in 'SMITH'.
SELECT LENGTH (ENAME)
FROM EMP
WHERE ENAME='SMIITH' O/P: LENGTH(ENAME)
5
2. CONCAT:
"It is used to join the given two strings “
SYNTAX: CONCAT ('string1', 'String2')
Example:
SELECT CONCAT ('MR ', ENAME)
FROM EMP
WHERE ENAME='SMIITH'; O/P: MR SMIITH
3. UPPER ():
"It is used to convert a given string to upper case."
SYNTAX: UPPER(‘STRING’)
Example: SELECT UPPER(‘spring’)
FROM DUAL; O/P: SPRING
4. LOWER ():
"It is used to convert a given string to lower case "
SYNTAX: UPPER(‘STRING’)
Example: SELECT LOWER(‘SPRING’)
FROM DUAL; O/P: spring
5. INITCAP ():
" It is used to convert a given string to initial capital letter
case.”
SYNTAX: INITCAP(‘STRING’)
Example: SELECT INITCAP(‘moon’)
FROM DUAL; O/P: Moon
6. REVERSE ():
“It is used to reverse the given string”
SYNTAX: REVERSE (‘STRING’)
Example: SELECT REVERSE(‘moon’)
FROM DUAL; O/P: noom
Other important SRF
• REVERSE(),
•
• SUBSTR()
• INSTR(),
• REPLACE()
• ROUND(),
• TRUNC()
• MONTHS_BETWEEN()
• LAST DAY()
• TO_CHAR()
• NVL()
1. Attributes in DBMS
Key Attribute
An attribute that is used to uniquely identify records in a table is called a key
attribute.
Example: In the EMP table, EMPNO, PH_NO, MAIL_ID, AADHAR,
PAN_CARD are key attributes.
Non-Key Attribute
All attributes in a table that are not used to uniquely identify records are known as
non-key attributes.
Example: ENAME, SAL, HIREDATE, JOB, COMM, DOB, DOJ.
Prime Key Attribute
Among all key attributes, the one chosen to uniquely identify records in a table is
called the primary key or prime key attribute.
Example: EMPNO in the EMP table.
Non-Prime Key Attribute
All key attributes except the prime key are known as non-prime key attributes.
Example: PH_NO, MAIL_ID, AADHAR, PAN_CARD in EMP table when
EMPNO is the primary key.
Composite Key
When two or more attributes together are used to uniquely identify records, it is
called a composite key.
Used when no single attribute alone can identify records.
Example: (ENAME, HIREDATE) together may act as a composite key.
Super Key
A super key is a set of one or more attributes that can uniquely identify records in
a table.
It includes the primary key and other combinations of key attributes.
Example: {EMPNO}, {EMPNO, PH_NO}, {PH_NO, MAIL_ID} (if they identify
records uniquely).
Foreign Key
A foreign key is an attribute in one table that refers to the primary key of another
table, establishing a relationship between the two tables.
Example: DEPTNO in EMP table refers to DEPTNO in DEPT table.
2. Functional Dependency
A functional dependency occurs when the value of one attribute depends on
another.
It is represented as A → B, meaning A determines B.
Types of Functional Dependency
Total Functional Dependency
If a single attribute determines all other attributes in the table, it is called total
functional dependency.
Example: EMPNO → ENAME, SAL, JOB
Partial Functional Dependency
When a part of a composite key determines another attribute, it is called partial
functional dependency.
Example: In relation R1(A, B, C, D), if (A, B) is the composite key, and A → C,
then it's a partial dependency.
Transitive Functional Dependency
When a non-key attribute depends on another non-key attribute, which is itself
dependent on a key, it is called transitive functional dependency.
Example: In CUSTOMER(CID, CNAME, PINCODE, CITY),
CID → PINCODE, and PINCODE → CITY
So, CID → CITY is transitive.
3. Normalization
Normalization is the process of organizing data to eliminate redundancy and
anomalies by identifying and applying functional dependencies.
Redundancy
Unnecessary repetition of data values.
Anomalies
Problems caused by redundancy during Insert, Update, or Delete operations.
Database normalization rules
Database normalization process is divided into following the normal form:
First Normal Form (1NF)
1NF (First Normal Form) Rules
• Each table cell should contain a single value.
• Each record needs to be unique.
Example:
Sample Employee table, it displays employees are working with multiple
departments.
Employee Age Department
Melvin 32 Marketing, Sales
Edward 45 Quality Assurance
Alex 36 Human Resource
Employee table following 1NF:
Employee Age Department
Melvin 32 Marketing
Melvin 32 Sales
Edward 45 Quality Assurance
Alex 36 Human Resource
Second normal form(2NF)
A table is said to be in 2NF if:
1. Table is in 1NF
2. It has no Partial Dependency, i.e., no non-prime attribute is dependent on
any proper subset of any candidate key of the table.
First we will understand what are Prime and Non-prime attributes.
Prime attribute − An attribute, which is a part of the candidate key, is known as a
prime attribute.
Non-prime attribute − An attribute, which is not a part of the candidate key, is
said to be a non-prime attribute.
For example, we have following table which is having employee data.
Above table is in 1NF as all columns are having atomic values. Here
Emp_Id and Dept_Id are the prime attributes. As per 2NF rule Emp_Name and
Dept_Name must be dependent upon both prime attributes, but here Emp_name
can be identified by Emp_Id and Dept_Name can be identified by Dept_Id alone.
So here partial dependency exists. To make this relation in 2NF we have to break
above table as:
Third normal form(3NF)
For a relation to be in Third Normal Form it must satisfy the following −
1. It must be in Second Normal form
2. No non-prime attribute is transitively dependent on prime key attribute.
For example, we have below table for storing employee data.
In above relation Emp_Id is the only prime key attribute.
Now If we see City can be identified by Emp_Id as well as ZIP. ZIP is not a
prime attribute, and also it is not a super key. So we hold below 2 relationships
here.
Emp_Id -> ZIP (ZIP can be identified by Emp_Id) ZIP ->
City (City can be identified by ZIP)
Therefore, below transitive dependency is true for above relation. Emp_Id -> ZIP -
> City
To convert this relation into 3NF we wil break this into 2 relations as:
What are transitive functional dependencies?
A transitive functional dependency is when changing a non-key column, might
cause any of the other non-key columns to change
Consider the table Changing the non-key column Full Name may change
Salutation.
The entity should be considered already in 2NF and no column entry should be
dependent on any other entry (value) other than the key for the table.
If such an entity exists, move it outside into a new table.
3NF is achieved are considered as the database is normalized.
Boyce and Codd Normal Form (BCNF)
Boyce and Codd Normal Form is a higher version of the Third Normal form.
This form deals with certain type of anomaly that is not handled by 3NF. A 3NF
table which does not have multiple overlapping candidate keys is said to be in
BCNF. For a table to be in BCNF, following conditions must be satisfied:
• R must be in 3rd Normal Form
• and, for each functional dependency ( X → Y ), X should be a super Key.
