DMS unit 2-Relational Database model

DMS 22319 unit 2

Relational Model concept

Relational model can represent as a table with columns and rows.

Each row is known as a tuple.

Each column of the table has a name or attribute.

Domain: It contains a set of atomic values that an attribute can take.

Attribute: It contains the name of a column in a particular table. Each attribute Ai must
have a domain, dom(Ai)

Relational instance: In the relational database system, the relational instance is

represented by a finite set of tuples.

Relation instances do not have duplicate tuples.

Relational schema: A relational schema contains the name of the relation and name of
all columns or attributes.

Relational key: In the relational key, each row has one or more attributes. It can identify
the row in the relation uniquely.

Example: STUDENT Relation

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35

Laxman 33289 8583287182 Gurugram 20

Mahesh 27857 7086819134 Ghaziabad 27

Vidya Lunge 1
DMS 22319 unit 2

Ganesh 17282 Delhi 40

Mahesh 27857 7086819134 Ghaziabad 27

● In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are
the attributes.

● The instance of schema STUDENT has 5 tuples.

● t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

Properties of Relations

● Name of the relation is distinct from all other relations.

● Each relation cell contains exactly one atomic (single) value

● Each attribute contains a distinct name

● Attribute domain has no significance

● tuple has no duplicate value

● Order of tuple can have a different sequence

Dr (Edgar F Codd) EF Codd’s Rule for RDBMS:

Database Management System or DBMS essentially consists of a comprehensive set of
application programs that can be leveraged to access, manage and update the data,
provided the data is interrelated and profoundly persistent.
To achieve this, the following are the absolute must-haves:
● Data Modeling −
It is all about defining the structures for information storage.
● Provision of Mechanisms −

Vidya Lunge 2
DMS 22319 unit 2

To manipulate processed data and modify file and system structures, it is

important to provide query processing mechanisms.
● Crash Recovery and Security −
To avoid any discrepancies and ensure that the data is secure, crash recovery and
security mechanisms are must.
● Concurrency Control −
If the system is shared by multiple users, concurrency control is the need of the
hour.
Dr E.F.Codd, also known to the world as the :
● ‘Father of Database Management Systems’ had propounded 12 rules which are
in-fact 13 in number.
● The rules are numbered from zero to twelve.
● According to him, a DBMS is fully relational if it abides by all his twelve rules.
● Till now, only few databases abide by all the eleven rules. His twelve rules are
fondly called ‘E.F.Codd’s Twelve Commandments’.

Relational Database Management System

There is an unspoken rule in the jargon of Database Management Systems. As the
databases that implement all the E.F.Codd’s rules are scare, the unspoken rule has been
gaining traction.

● If a management system or software follows any of 5-6 rules proposed by

E.F.Codd, it qualifies to be a Database Management System (DBMS).
● If a management system or software follows any of 7-9 rules proposed by
E.F.Codd, it qualifies to be a semi-Relational Database Management System
(semi- RDBMS).
● If a management system or software follows 9-12 rules proposed by E.F. Codd, it
qualifies to be a complete Relational Database Management System (RDBMS).
Dr Edgar F Codd’s Twelve Commandments
Rule 0 − Foundation rule

Vidya Lunge 3
DMS 22319 unit 2

● Any relational database management system that is propounded to be RDBMS or

advocated to be a RDBMS--->
should be able to manage the stored data in its entirety through its relational
capabilities.(attribute should be correlated)
Rule 1 − Rule of Information
● Relational Databases should store the data in the form of relations.
● Tables are relations in Relational Database Management Systems.
● Be it any user defined data or meta-data, it is important to store the value as an
entity in the table cells.
Rule 2 − Rule of Guaranteed Access
● The use of pointers to access data logically is strictly forbidden.
● Every data entity which is atomic in nature should be accessed logically by using
a right combination of the name of table, primary key represented by a specific
row value and column name represented by attribute value.
Rule 3 − Rule of Systematic Null Value Support
● Null values are completely supported in relational databases.
● They should be uniformly considered as ‘missing information’.
● Null values are independent of any data type.
● They should not be mistaken for blanks or zeroes or empty strings.
● Null values can also be interpreted as ‘inapplicable data’ or ‘unknown
information.’
Rule 4 − Rule of Active and online relational Catalog
● In the Database Management Systems lexicon (the vocabulary of a person,
language, or branch of knowledge.), ‘metadata’ is the data about the database or
the data about the data.
● The active online catalog that stores the metadata is called ‘Data dictionary’.
● The so called data dictionary is accessible only by authored users who have the
required privileges and the query languages used for accessing the database
should be used for accessing the data of data dictionary.
Rule 5 − Rule of Comprehensive Data Sub-language

Vidya Lunge 4
DMS 22319 unit 2

● A single robust language should be able to define integrity constraints, views, data
manipulations, transactions and authorizations.
● If the database allows access to the aforementioned (denoting a thing or person
previously mentioned)ones, it is violating this rule.
Rule 6 − Rule of Updating Views
● Views should reflect the updates of their respective base tables and vice versa.
● A view is a logical table which shows restricted data.
● Views generally make the data readable but not modifiable.
● Views help in data abstraction.
Rule 7 − Rule of Set level insertion, update and deletion
● A single operation should be sufficient to retrieve, insert, update and delete the
data.
Rule 8 − Rule of Physical Data Independence
● End user operations are logically separated from physical storage and respective
access methods.
Rule 9 − Rule of Logical Data Independence
● End users can change the database schema without having to recreate it or recreate
the applications built upon it.
Rule 10 − Rule of Integrity Independence
● Integrity constraints should be available and stored as metadata in data dictionary
and not in the application programs.
Rule 11 − Rule of Distribution Independence
● The Data Manipulation Language of the relational system should not be
concerned about the physical data storage and no alterations should be required if
the physical data is centralized or distributed.
Rule 12 − Rule of Non Subversion
● Any row should obey the security and integrity constraints imposed.
● No special privileges are applicable.
● Almost all full scale DBMSs are RDMSs.

Vidya Lunge 5
DMS 22319 unit 2

● Oracle implements 11+ rules and so does Sybase.

● SQL Server also implements 11+ rules while FoxPro implements 7+ rules.
Keys in DBMS

● Keys play an important role in the relational database.

● It is used to uniquely identify any record or row of data from the table.

● It is also used to establish and identify relationships between tables.

For example:

In Student table, ID is used as a key because it is unique for each student.

In PERSON table, passport_number, license_number, SSN are keys since they are
unique for each person.

Types of key:

Vidya Lunge 6
DMS 22319 unit 2

1. Candidate key

● A candidate key is an attribute or set of an attribute which can uniquely identify a

tuple.

● The remaining attributes except for primary key are considered as a candidate key.
The candidate keys are as strong as the primary key.
● The minimal set of attributes that can uniquely identify a tuple is known as a
candidate key.
● The value of the Candidate Key is unique and non-null for every tuple.
● There can be more than one candidate key in a relation.

Vidya Lunge 7
DMS 22319 unit 2

For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the
attributes like SSN, Passport_Number, and License_Number, etc. are considered as a
candidate key.

2 .Primary key

● It is the first key which is used to identify one and only one instance of an entity
uniquely. An entity can contain multiple keys as we saw in PERSON table. The
key which is most suitable from those lists become a primary key.

● In the EMPLOYEE table, ID can be primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_Number and
Passport_Number as primary key since they are also unique.

● For each entity, selection of the primary key is based on requirement and
developers.
● There can be more than one candidate key in relation out of which one can be
chosen as the primary key.

Vidya Lunge 8
DMS 22319 unit 2

Eg:

Vidya Lunge 9
DMS 22319 unit 2

5 max

3. Super Key

● Super key is a set of an attribute which can uniquely identify a tuple.

● Super key is a superset of a candidate key.
● Adding zero or more attributes to the candidate key generates the super key.
● A candidate key is a super key but vice versa is not true.

Vidya Lunge 10
DMS 22319 unit 2

For example: In the above EMPLOYEE table, for(EMPLOEE_ID,

EMPLOYEE_NAME) the name of two employees can be the same, but their
EMPLYEE_ID can't be the same. Hence, this combination can also be a key.

The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

4. Foreign key

● Foreign keys are the column of the table which is used to point to the primary key
of another table.

● In a company, every employee works in a specific department, and employee and

department are two different entities. So we can't store the information of the
department in the employee table. That's why we link these two tables through the
primary key of one table.
● If an attribute can only take the values which are present as values of some other
attribute, it will be a foreign key to the attribute to which it refers.
● The relation which is being referenced is called referenced relation and the
corresponding attribute is called referenced attribute and the relation which

Vidya Lunge 11
DMS 22319 unit 2

refers to the referenced relation is called referencing relation and the

corresponding attribute is called referencing attribute.
● The referenced attribute of the referenced relation should be the primary key for
it.
● Primary Key of any given relation, Foreign Key can be NULL as well as may
contain duplicate tuples i.e. it need not follow uniqueness constraint.
● We add the primary key of the DEPARTMENT table, Department_Id as a new
attribute in the EMPLOYEE table.

● Now in the EMPLOYEE table, Department_Id is the foreign key, and both the
tables are related.

Vidya Lunge 12
DMS 22319 unit 2

Eg:

5.Composite Key:
A composite key is the DBMS key having two or more attributes that together can
uniquely identify a tuple in a table. Such a key is also known as Compound Key, where
each attribute creating a key is a foreign key in its own right.

Vidya Lunge 13
DMS 22319 unit 2

Ex:2

Foreign Key:

Vidya Lunge 14
DMS 22319 unit 2

Primary Key of any given relation, Foreign Key can be NULL as well as may
contain duplicate tuples i.e. it need not follow uniqueness constraint.
For Example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in
STUDENT relation.
It may be worth noting that unlike,
For Example,:
STUD_NO in STUDENT_COURSE relation is not unique.
It has been repeated for the first and third tuples.
However, the STUD_NO in STUDENT relation is a primary key and it needs to be
always unique and it cannot be null.

Eg:

Normalization

Vidya Lunge 15
DMS 22319 unit 2

● Normalization is the process of organizing the data in the database.

● Normalization is used to minimize the redundancy from a relation or set of

relations.

● It is also used to eliminate the undesirable characteristics like Insertion, Update

and Deletion Anomalies.

● Normalization divides the larger table into the smaller table and links them using
relationship.

● The normal form is used to reduce redundancy from the database table.

Example of normalizatiion:

Normalization is technique to remove or reduce redundancy

Table 1:Student_Data
SID Sname Age
1 Pinky 20
2 Dinky 25
1 Pinky 20

Duplicate records Pinky:is row level redundancy =repeat

Vidya Lunge 16
DMS 22319 unit 2

Table2:Stud_course_faculty_Data

SID Sname CID Cname FID Fname Salary

1 Pinky C1 DBMS F1 Dora 30000
2 Binky C2 DSU F2 Zora 40000
3 Minky C1 DBMS F1 Dora 30000
4 Zinky C1 DBMS F1 Dora 30000
5 Yinky C1 DBMS F1 Dora 30000

In this table:
SID is primary key,
no 2 rows are same ,but there is column level redundancy
check row 1,3,4:it is repeated

C1 DBMS F1 Dora 30000

Data has 3 types of anomaly:

insertion ,deletion and updation :DML
Insertion anomaly:
If SID 6 Jinky to be added we can add ,but if new course or new faculty is to be added,it
wont support as it has to be compulsory taken by some student since SID is the primary
key.
Table2:Stud_course_faculty_Data

Vidya Lunge 17
DMS 22319 unit 2

SID Sname CID Cname FID Fname Salary

1 Pinky C1 DBMS F1 Dora 30000
2 Binky C2 DSU F2 Zora 40000
3 Minky C1 DBMS F1 Dora 30000
4 Zinky C1 DBMS F1 Dora 30000
5 Yinky C1 DBMS F2 Zora 40000
6 Jinky
#% C6 Western F6 Mora 35000
music
C7 Indian F7 smit 50000
mytho

not allowed

Deletion anomaly:
Delete SID 2 it ,deletes all details of DSU and recovery will not be possible.
Updation anomaly:
If Zinky have changed name to booby it will change but if salary of F1 is changed to
50000Rs ,it will requires changes at multiple places though F1 is only one person.
So to avoid all this anomaly we decompose the table into 3 each having primary key.

Student_Details:

SID Sname

Vidya Lunge 18
DMS 22319 unit 2

1 Pinky
2 Binky
3 Minky
4 Zinky
5 Yinky
6 Zinky

Course_Details:

CID Cname
C1 DBMS
C2 DSU
C6 Western music
C7 Indian mytho

Faculty_Details:

FID Fname Salary

F1 Dora 30000
F2 Zora 40000
F6 Mora 35000

Types of Normal Forms

Vidya Lunge 19
DMS 22319 unit 2

Database normalization is the process of organizing the attributes of the database to

reduce or eliminate data redundancy (having the same data but at different places) .
Problems because of data redundancy
1. Data redundancy unnecessarily increases the size of the database as the same data
is repeated in many places.
2. Inconsistency problems also arise during insert, delete and update operations.
Functional Dependency
1. Functional Dependency is a constraint between two sets of attributes in relation to
a database.
2. A functional dependency is denoted by an arrow (→).
If an attribute A functionally determines B,
then it is written as A → B.
● For example,
● employee_id → name
● Means
● employee_id functionally determines the name of the employee.
As another example in a timetable database,
{student_id, time} → {lecture_room},
student ID and time determine the lecture room where the student should be.

Vidya Lunge 20
DMS 22319 unit 2

1. First Normal Form –

If a relation contain composite or multi-valued attribute, it violates first normal form

or
a relation is in first normal form if it does not contain any composite or multi-valued
attribute.
A relation is in first normal form if every attribute in that relation is singled valued
(atomic ) attribute.

● Example 1 – Relation STUDENT in table 1 is not in 1NF because of

multi-valued attribute STUD_PHONE.
● Its decomposition into 1NF has been shown in table 2.

Example2:

Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

Vidya Lunge 21
DMS 22319 unit 2

EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

7272826385,
14 John 9064738238 UP

20 Harry 8574783832 Bihar

7390372389,
12 Sam 8589830302 Punjab

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Example 2 –

Vidya Lunge 22
DMS 22319 unit 2

ID Name Courses
--------------------------------
1 Amit c1, c2
2 Eva c3
3 Monty c2, c3,c1

In the above table Course is a multi valued attribute so it is not in 1NF.

Below Table is in 1NF as there is no multi valued attribute

ID Name Course
----------------------------
1 Amit c1
1 Amit c2
2 Eva c3
3 Monty c2
3 Monty c3
3 Monty c1

*************************************************

Functional dependencies
Functional dependencies are the constraints that are derived from the meaning and
interrelationship of the data.
Definition −
Let R is a relation schema with n attributes A1, A2,…..An.
A functional dependency, denoted by X->Y,
between two sets of attributes X and Y that are subsets of R species a constraint on the
possible tuples that can form a relation state r of R.
Y depends on X or Y is determined by X or Y is functional dependent on X. Similarly X
determines Y.
● If X is the candidate key of R then X->Y for any subset of attributes Y of R.

Vidya Lunge 23
DMS 22319 unit 2

● If X->Y in R, it does not mean whether or not Y->X in R.

Example 1
Given below is an example of functional dependency in database management system
(DBMS) −
1. SSN->ENAME read as
SSN functionally defines ENAME
Or
SSN determines ENAME

2. PNUMBER->{PNAME,PLOCATION}
(PNUMBER determines PNAME and PLOCATION)

3. {SSN,PNUMBER}->HOURS
(SSN and PNUMBER combinedly determines HOURS)
Output
You will get the following result −

Example 2
Consider another example: BOOK table.

Bookid Bname Author Price

B1 Database Korth 400

Vidya Lunge 24
DMS 22319 unit 2

B2 Database Navathe 300

B3 Datastructures Patel 200

B4 Oops Patel 200

Here,
● Bookid->Bname {bookid determines Bname}
● Bname ->author {bookname does not determine author, because same bname has
different authors}
● Author->price {Author determines price}
Note − There is no algorithm to identify functional dependency. We have to use our
commonsense and judgment to identify functional dependency.

2. Second Normal Form –

To be in second normal form, a relation must be in first normal form and relation
must not contain any partial dependency.
A relation is in 2NF if it has No Partial Dependency,
i.e., no non-prime attribute (attributes which are not part of any candidate key) is
dependent on any proper subset of any candidate key of the table.,than table is not in
2NF
Partial Dependence:
Partial Dependency occurs when a non-prime attribute is functionally dependent on part
of a candidate key.
The 2nd Normal Form (2NF) eliminates the Partial Dependency.
Example
<StudentProject>

Vidya Lunge 25
DMS 22319 unit 2

StudentID ProjectNo StudentName ProjectName

S01 199 Kaliya Geo Location

S02 120 Oliya Cluster
Exploration
S02 bliya
125 Traffic system
In the above table, we have partial dependency; let us see how −
The prime key attributes are StudentID and ProjectNo, and

StudentID = Unique ID of the student

StudentName = Name of the student
ProjectNo = Unique ID of the project
ProjectName = Name of the project

As stated, the non-prime attributes i.e. StudentName and ProjectName should be

functionally dependent on part of a candidate key, to be Partial Dependent.
The StudentName can be determined by StudentID, which makes the relation Partial
Dependent.
The ProjectName can be determined by ProjectNo, which makes the relation Partial
Dependent.
Therefore, the <StudentProject> relation violates the 2NF in Normalization and is
considered a bad database design.
To remove Partial Dependency and violation on 2NF, decompose the tables −
<StudentInfo>

StudentID StudentName

S01 Katie
S02 Ollie

Vidya Lunge 26
DMS 22319 unit 2

S03

<ProjectInfo>

ProjectNo ProjectName

199 Geo Location

120 Cluster Exploration
125 traffic

Now the relation is in 2nd Normal form of Database Normalization

Partial Dependency –
If the proper subset of candidate key determines non-prime attribute, it is called partial
dependency.
Example 1 – Consider table-3 as following below.
STUD_NO COURSE_NO COURSE_FEE
1 C1 1000
2 C2 1500
1 C4 2000
4 C3 1000
4 C1 1000
2 C5 2000

{Note that, there are many courses having the same course fee. }
Here,
COURSE_FEE cannot alone decide the value of COURSE_NO or STUD_NO;
COURSE_FEE together with STUD_NO cannot decide the value of COURSE_NO;

Vidya Lunge 27
 DMS 22319 unit 2

COURSE_FEE together with COURSE_NO cannot decide the value of STUD_NO;

Hence,
COURSE_FEE would be a non-prime attribute, as it does not belong to the one only
candidate key {STUD_NO, COURSE_NO} ;

But,
COURSE_NO -> COURSE_FEE ,
i.e., COURSE_FEE is dependent on COURSE_NO, which is a proper subset of the
candidate key.
Non-prime attribute COURSE_FEE is dependent on a proper subset of the candidate
key, which is a partial dependency and so this relation is not in 2NF.

To convert the above relation to 2NF,

we need to split the table into two tables such as :
Table 1: STUD_NO, COURSE_NO
Table 2: COURSE_NO, COURSE_FEE
Table 1 Table 2
STUD_NO COURSE_NO COURSE_NO COURSE_FEE
1 C1 C1 1000
2 C2 C2 1500
1 C4 C3 1000
4 C3 C4 2000

NOTE: 2NF tries to reduce the redundant data getting stored in memory. For instance, if
there are 100 students taking C1 course, we dont need to store its Fee as 1000 for all the
100 records, instead once we can store it in the second table as the course fee for C1 is
1000.

Vidya Lunge 28
 DMS 22319 unit 2

● 3. Third Normal Form –

A relation will be in 3NF if it is in 2NF and not contain any transitive partial
dependency.
● 3NF is used to reduce the data duplication.
● It is also used to achieve the data integrity.
● If there is no transitive dependency for non-prime attributes, then the relation must
be in third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for
every non-trivial function dependency:

X → Y.

1. X is a super key.

2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

● a=b
● b=c
● a=c

Transitive dependency – :

● If A->B ….1)
● and B->C…….2)
● are two FDs
● then A->C is called transitive dependency.

Vidya Lunge 29
DMS 22319 unit 2

● Super key in the table above:

○ {EMP_ID},
○ {EMP_ID, EMP_NAME},
○ {EMP_ID, EMP_NAME, EMP_ZIP}....so on

Candidate key: {EMP_ID}

Non-prime attributes:
In the given table, all attributes except EMP_ID are non-prime.

Here,
EMP_STATE & EMP_CITY dependent on EMP_ZIP
And
EMP_ZIP dependent on EMP_ID.

EMP_ZIP --->EMP_STATE & EMP_CITY ….1)

EMP_ID-->.EMP_ZIP …….2)
So

EMP_ID --->EMP_STATE & EMP_CITY

Vidya Lunge 30
DMS 22319 unit 2

The non-prime attributes (EMP_STATE, EMP_CITY) is transitively

dependent on super key(EMP_ID).

It violates the rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new
<EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

Vidya Lunge 31
 DMS 22319 unit 2

○ 4.

Normal Description
Form

1NF A relation is in 1NF if it contains an atomic value.

Vidya Lunge 32
DMS 22319 unit 2

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes

are fully functional dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition

dependency exists.

4NF A relation will be in 4NF if it is in Boyce Codd normal form and has
no multi-valued dependency.

5NF A relation is in 5NF if it is in 4NF and not contains any join

dependency and joining should be lossless.

Vidya Lunge 33
 DMS 22319 unit 2

○ Boyce-Codd Normal Form (BCNF) –

A relation R is in BCNF, if R is in Third Normal Form and for every FD, LHS is super
key.
○ X –> Y, X is a super key.Boyce Codd normal form (BCNF)
● BCNF is the advance version of 3NF. It is stricter than 3NF.

● A table is in BCNF if every functional dependency

● X → Y, X is the super key of the table.

● For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Vidya Lunge 34
DMS 22319 unit 2

Example: Let's assume there is a company where employees work in more than one
department.

EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEP DEPT_TYPE EMP_DEPT_N

T O

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

Vidya Lunge 35
DMS 22319 unit 2

EMP_ID EMP_COUNTRY

264 India

364 UK

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

Dept type EMP_DEPT

Vidya Lunge 36
DMS 22319 unit 2

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:

For the first table: EMP_ID

For the second table: EMP_DEPT

For the third table: {EMP_ID, EMP_DEPT}

Fourth normal form (4NF)

● A relation will be in 4NF if it is in Boyce Codd normal form (BCNF) and has no
multi-valued dependency.

● For a dependency A → B,

Vidya Lunge 37
DMS 22319 unit 2

● Item no->item name, item price

Item no item name item price

1 burger 40

2 dosa 55

if for a single value of A, multiple values of B exists,

then the relation will be a multi-valued dependency.

Example

STUDENT

STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey

Vidya Lunge 38
DMS 22319 unit 2

22 computer dancing

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.

In the STUDENT relation, a student with STU_ID, 21 contains two courses, Computer
and Math and two hobbies, Dancing and Singing.

So there is a Multi-valued dependency on STU_ID, which leads to unnecessary

repetition of data.

So to make the above table into 4NF, we can decompose it into two tables:

STUDENT_COURSE

STU_ID COURSE

21 Computer

21 Math

21 Chemistry

34 Chemistry

74 Biology

59 Physics

Vidya Lunge 39
DMS 22319 unit 2

STUDENT_HOBBY

STU_ID HOBBY

21 Dancing

21 Singing

34 Dancing

74 Cricket

59 Hockey

Fifth normal form (5NF)/Project-join normal form (PJ/NF).

● A relation is in 5NF if it is in 4NF and not contains any join dependency and
joining should be lossless.

● 5NF is satisfied when all the tables are broken into as many tables as possible in
order to avoid redundancy.

● 5NF is also known as Project-join normal form (PJ/NF).

Example:tab1

Vidya Lunge 40
DMS 22319 unit 2

SUBJECT LECTURER SEMESTER

Computer Anshika Semester 1

Computer John Semester 1

Math John Semester 1

Math Akash Semester 2

Chemistry Praveen Semester 1

Math Akash Semester 1

Candidate key=(subject+lecturer+semester)

In the above table, :

● John takes both Computer and Math class for Semester 1 but he doesn't take Math
class for Semester 2.
● In this case, combination of all these fields required to identify a valid data.
● Suppose we add a new Semester as Semester 3 but do not know about the subject
and who will be taking that subject so we leave Lecturer and Subject as NULL.
● But all three columns together acts as a primary key, so we can't leave other two
columns blank.
● So to make the above table into 5NF, we can decompose it into three relations P1,
P2 & P3:

Vidya Lunge 41
DMS 22319 unit 2

P1

SEMESTER SUBJECT

Semester 1 Computer

Math
Semester 1

Semester 1 Chemistry

Semester 2 Math

P2

SUBJECT LECTURER

Vidya Lunge 42
DMS 22319 unit 2

Computer Anshika

Computer John

Math John

Math Akash

Chemistry Praveen

P3

SEMESTER LECTURER

Semester 1 Anshika

Semester 1 John

Semester 1 John

Vidya Lunge 43
DMS 22319 unit 2

Semester 2 Akash

Semester 1 Praveen

=======================================================
Structured Query Language.(SQL)

● SQL stands for Structured Query Language.

● It is used for storing and managing data in relational database management system
(RDMS).

● It is a standard language for Relational Database System.

● It enables a user to create, read, update and delete relational databases and tables.

● All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use
SQL as their standard database language.

● SQL allows users to query the database in a number of ways, using English-like
statements.

Rules:

SQL follows the following rules:

● Structure query language is not case sensitive.

● Generally, keywords of SQL are written in uppercase.

● Statements of SQL are dependent on text lines.

● We can use a single SQL statement on one or multiple text line.

Vidya Lunge 44
DMS 22319 unit 2

● Using the SQL statements, you can perform most of the actions in a database.

● SQL depends on tuple relational calculus and relational algebra.

SQL process:

● When an SQL command is executing for any RDBMS, then the system figure out
the best way to carry out the request and the SQL engine determines that how to
interpret the task.

● In the process, various components are included. These components can be

optimization Engine, Query engine, Query dispatcher, classic, etc.

● All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.

Parser in computer science:-

● In computer technology, a parser is a program, usually part of a compiler, that

receives input in the form of sequential source program instructions,
interactive online commands, markup tags, or some other defined interface
and breaks them up into parts (for example, the nouns (objects), verbs
(methods).

Vidya Lunge 45
DMS 22319 unit 2

Characteristics of SQL

● SQL is easy to learn.

● SQL is used to access data from relational database management systems.

● SQL can execute queries against the database.

● SQL is used to describe the data.

● SQL is used to define the data in the database and manipulate it when needed.

● SQL is used to create and drop the database and table.

● SQL is used to create a view, stored procedure, function in a database.

● SQL allows users to set permissions on tables, procedures, and views.

Advantages of SQL

Vidya Lunge 46
DMS 22319 unit 2

There are the following advantages of SQL:

High speed

Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.

No coding needed

In the standard SQL, it is very easy to manage the database system. It doesn't require a
substantial amount of code to manage the database system.

Well defined standards

Long established are used by the SQL databases that are being used by ISO and ANSI.M

Portability

SQL can be used in laptop, PCs, server and even some mobile phones.

Interactive language

SQL is a domain language used to communicate with the database.

It is also used to receive answers to the complex questions in seconds.

Multiple data view

Using the SQL language, the users can make different views of the database structure.

SQL Datatype

● SQL Datatype is used to define the values that a column can contain.

● Every column is required to have a name and data type in the database table.

Vidya Lunge 47
DMS 22319 unit 2

Datatype of SQL:

1. Binary Datatypes

There are Three types of binary Datatypes which are given below:

Data Description
Type

binary It has a maximum length of 8000 bytes. It contains fixed-length

binary data.

varbinar It has a maximum length of 8000 bytes. It contains variable-length

y binary data.

Vidya Lunge 48
DMS 22319 unit 2

image It has a maximum length of 2,147,483,647 bytes. It contains

variable-length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data From To Description

type

float -1.79E + 1.79E + It is used to specify a floating-point value e.g.

308 308 6.2, 2.9 etc.

real -3.40e + 3.40E + It specifies a single precision floating point

38 38 number

3. Exact Numeric Datatype

The subtypes are given below:

Data type Description

int It is used to specify an integer value.

Vidya Lunge 49
DMS 22319 unit 2

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:

Data Description
type

char It has a maximum length of 8000 characters. It contains Fixed-length

non-unicode characters.

varchar It has a maximum length of 8000 characters. It contains

variable-length non-unicode characters.

Vidya Lunge 50
DMS 22319 unit 2

text It has a maximum length of 2,147,483,647 characters. It contains

variable-length non-unicode characters.

5. Date and time Datatypes

The subtypes are given below:

Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestamp It stores the year, month, day, hour, minute, and the second value.

SQL Commands
● SQL commands are instructions.
● It is used to communicate with the database.
● It is also used to perform specific tasks, functions, and queries of data.

SQL can perform various tasks like create a table, add data to tables, drop the table,
modify the table, set permission for users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
DBMS SQL command
1. Data Definition Language (DDL)

Vidya Lunge 51
DMS 22319 unit 2

● DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
● All the command of DDL are auto-committed that means it permanently save all
the changes in the database.
● Here are some commands that come under DDL:

CREATE
ALTER
DROP
TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:
CREATE TABLE EMPLOYEE
(
Name VARCHAR2(20),
Email VARCHAR2(100),
DOB DATE
);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax
DROP TABLE table_name;

Example
DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database.

This change could be either to modify the characteristics of an existing attribute or
probably to add a new attribute.

Vidya Lunge 52
DMS 22319 unit 2

Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE
ALTER TABLE STU_DETAILS
ADD
(
ADDRESS VARCHAR2(20)
);

ALTER TABLE STU_DETAILS

ADD
(
10thpercent int
);

ALTER TABLE STU_DETAILS

MODIFY
(
NAME VARCHAR2(10)
);

ALTER TABLE STU_DETAILS

MODIFY
(
Courseid varchar2(3)
);

d. TRUNCATE:
It is used to delete all the rows from the table and free the space containing the
table.

Syntax:
TRUNCATE TABLE table_name;

Vidya Lunge 53
DMS 22319 unit 2

Example:
TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

● DML commands are used to modify the database.
● It is responsible for all form of changes in the database.
● The command of DML is not auto-committed that means it can't permanently
save all the changes in the database.
● They can be rollback.
● Here are some commands that come under DML:

INSERT
UPDATE
DELETE

a. INSERT: The INSERT statement is a SQL query.

It is used to insert data into the row of a table.

Syntax:
INSERT INTO TABLE_NAME
(
col1, col2, col3,.... col N)
VALUES
(value1, value2, value3, .... valueN)
);

Details:

Rollno name class

64 xx
65 yy 9th
66 zz 4th

Vidya Lunge 54
DMS 22319 unit 2

INSERT INTO details

(
rollno, name
VALUES
(64,’xx’)
);

INSERT INTO details

(
VALUES (65,’yy’,’9th’)
);

Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);

For example:
INSERT INTO javatpoint
(Author, Subject)
VALUES
("Sonoo", "DBMS");

b. UPDATE:
This command is used to update or modify the value of a column in the table.

Syntax:
UPDATE table_name
SET
[column_name1= value1,...column_nameN = valueN]
[WHERE CONDITION]

For example:
UPDATE students
SET
User_Name = 'Sonoo'

Vidya Lunge 55
DMS 22319 unit 2

WHERE Student_Id = '3'

Update p3
Set
Program code=’CO3I’;

Update CO_dept
Set
Program code=’CO3I
Where
Program code=’CO2I’’;

c. DELETE: It is used to remove one or more row from a table.

Syntax:
DELETE FROM table_name [WHERE condition];
Or
DELETE FROM table_name;

DESC tablename;

example:
DELETE FROM javatpoint
WHERE
Author="Sonoo";

Vidya Lunge 56
DMS 22319 unit 2

3. Data Control Language (DCL): ACCESS to Users for datablse

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:

Grant
Revoke

a. Grant: It is used to give user access privileges to a database.

Example
GRANT SELECT, UPDATE ON MY_TABLE
TO
USER1, USER2;

GRANT SELECT, UPDATE ON classP3

TO
U1, U2;

b. Revoke: It is used to take back permissions from the user.

Example
REVOKE SELECT, UPDATE ON MY_TABLE
FROM
USER1, USER2;

REVOKE SELECT, UPDATE ON classP3

TO
U1, U2;

Vidya Lunge 57
DMS 22319 unit 2

4. Transaction Control Language:wrt transaction

● TCL commands can only use with DML commands like INSERT, DELETE and
UPDATE only.
● These operations are automatically committed in the database that's why they
cannot be used while creating tables or dropping them.
● Here are some commands that come under TCL:

● COMMIT
● ROLLBACK
● SAVEPOINT

a. Commit:
Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;

Example:
DELETE FROM CUSTOMERS
WHERE
AGE = 25;
COMMIT;

b. Rollback:
Rollback command is used to undo transactions that have not already been saved to
the database.
Syntax:
ROLLBACK;

Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;

Vidya Lunge 58
DMS 22319 unit 2

c. SAVEPOINT:
It is used to roll the transaction back to a certain point without rolling back the
entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language (DQL)

DQL is used to fetch the data from the database.
It uses only one command:

SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used
to select the attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions
FROM TABLES
WHERE conditions;
For example:

SELECT emp_name
FROM employee ;

SELECT emp_name
FROM employee
WHERE age > 20;

SQL constraints are used to specify rules for data in a table.

Vidya Lunge 59
 DMS 22319 unit 2

SQL Create Constraints

Constraints can be specified when the table is created with the

1. CREATE TABLE statement, or

2. after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name

(
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....

);

SQL Constraints:

● SQL constraints are used to specify rules for the data in a table.
● Constraints are used to limit the type of data that can go into a table.
● This ensures the accuracy and reliability of the data in the table.
● If there is any violation between the constraint and the data action, the action is
aborted.
● Constraints can be column level or table level.
● Column level constraints apply to a column
● Table level constraints apply to the whole table.
● P10(rollno,name,age,%of1year)

The following constraints are commonly used in SQL:

Vidya Lunge 60
 DMS 22319 unit 2

1)NOT NULL - Ensures that a column cannot have a NULL value

SQL NOT NULL Constraint

● By default, a column can hold NULL values.

● The NOT NULL constraint enforces a column to NOT accept NULL values.
● This enforces a field to always contain a value, which means that you cannot
insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will
NOT accept NULL values when the "Persons" table is created:

Example:
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

2)UNIQUE - Ensures that all values in a column are different

SQL UNIQUE Constraint

● The UNIQUE constraint ensures that all values in a column are different.
● Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.
● A PRIMARY KEY constraint automatically has a UNIQUE constraint.
● However, you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

Vidya Lunge 61
 DMS 22319 unit 2

The following SQL creates a UNIQUE constraint on the "ID" column when the
"Persons" table is created:

CREATE TABLE Persons

(
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

SQL Server / Oracle / MS Access:/MySQL:

CREATE TABLE Persons

(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already created,
use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

EX:
ALTER TABLE Persons

Vidya Lunge 62
 DMS 22319 unit 2

ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple

columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

Ex:
ALTER TABLE Persons
ADD
CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT UC_Person;

3)PRIMARY KEY -
● A combination of a NOT NULL and UNIQUE.
● Uniquely identifies each row in a table.
● SQL PRIMARY KEY Constraint
● The PRIMARY KEY constraint uniquely identifies each record in a table.
● Primary keys must contain UNIQUE values, and cannot contain NULL values.
● A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).

● class_CO((enroll,roll,name ,location,mobile)
● P1:(enroll)
● P2:(enroll,roll)

Vidya Lunge 63
 DMS 22319 unit 2

● p3:(enroll,roll,name)
●

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons"
table is created:

MySQL:

CREATE TABLE Persons

(
ID int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int

);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY
constraint on multiple columns, use the following SQL syntax:

Vidya Lunge 64
 DMS 22319 unit 2

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person).

However, the VALUE of the primary key is made up of TWO COLUMNS (ID +
LastName).

4)Foreign KEY - Prevents actions that would destroy links between tables.

SQL FOREIGN KEY Constraint

Dept(parent)
Dept no dept name(primary key) dept location
1 co 2floor
2 ej 3floor
3 me

Society(child)

Vidya Lunge 65
DMS 22319 unit 2

Inst id (primary key) instname deptname(foreign)

I1 VESIT ej
I2 VESP ej
I3 VESP co
I4 VESP
I5 VESP me

The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the
PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary
key is called the referenced or parent table.

Look at the following two tables:

Persons Table

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

3 Pettersen Kari 20

Orders Table

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

3 22456 2

Vidya Lunge 66
 DMS 22319 unit 2

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID"
column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons"
table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the
foreign key column, because it has to be one of the values contained in the parent table

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the
"Orders" table is created:

MySQL:

CREATE TABLE Orders

(
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders

(
OrderID int NOT NULL PRIMARY KEY,

Vidya Lunge 67
 DMS 22319 unit 2

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders

(
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders"
table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

Vidya Lunge 68
 DMS 22319 unit 2

ALTER TABLE Orders

ADD
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders

DROP CONSTRAINT FK_PersonOrder;

5)CHECK - Ensures that the values in a column satisfies a specific condition

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a column it will allow only certain values for this
column.

If you define a CHECK constraint on a table it can limit the values in certain columns
based on values in other columns in the row.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the
"Persons" table is created. The CHECK constraint ensures that the age of a person must
be 18, or older:

Vidya Lunge 69
 DMS 22319 unit 2

MySQL:

CREATE TABLE Persons

(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),

Vidya Lunge 70
 DMS 22319 unit 2

Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created,
use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on

multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT CHK_PersonAge;

MySQL:

Vidya Lunge 71
 DMS 22319 unit 2

ALTER TABLE Persons

DROP CHECK CHK_PersonAge;

6)DEFAULT - Sets a default value for a column if no value is specified

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

Integrity Constraints

● Integrity constraints are a set of rules. It is used to maintain the quality of

information.

● Integrity constraints ensure that the data insertion, updating, and other processes
have to be performed in such a way that data integrity is not affected.

● Thus, integrity constraint is used to guard against accidental damage to the

database.

● UID=DML

Types of Integrity Constraint

Vidya Lunge 72
DMS 22319 unit 2

1. Domain constraints

● Domain constraints can be defined as the definition of a valid set of values for an
attribute.

● The data type of domain includes string, character, integer, time, date, currency,
etc. The value of the attribute must be available in the corresponding domain.

Example:

2. Entity integrity constraints=(primary key)

● The entity integrity constraint states that primary key value can't be null.

● This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.

● A table can contain a null value other than the primary key field.

Example:

Vidya Lunge 73
DMS 22319 unit 2

3. Referential Integrity Constraints(foreign key,2 table):

● A referential integrity constraint is specified between two tables.

● In the Referential integrity constraints, if a foreign key in Table 1 refers to the

Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2.

Example:

Vidya Lunge 74
DMS 22319 unit 2

4. Key constraints

● Keys are the entity set that is used to identify an entity within its entity set
uniquely.

● An entity set can have multiple keys, but out of which one key will be the primary
key. A primary key can contain a unique and null value in the relational table.

Example:

Vidya Lunge 75
DMS 22319 unit 2

Types of Operator

SQL operators are categorized in the following categories:

1. SQL Arithmetic Operators

2. SQL Comparison Operators

3. SQL Logical Operators

4. SQL Set Operators

5. SQL Bit-wise Operators

6. SQL Unary Operators

SQL Arithmetic Operators

● The Arithmetic Operators perform the mathematical operation on the numerical

data of the SQL tables.
● These operators perform addition, subtraction, multiplication, and division
operations on the numerical operands.

Following are the various arithmetic operators performed on the SQL data:

1. SQL Addition Operator (+)

2. SQL Subtraction Operator (-)

Vidya Lunge 76
DMS 22319 unit 2

3. SQL Multiplication Operator (+)

4. SQL Division Operator (-)

5. SQL Modulus Operator (+)

SQL Addition Operator (+)

● The Addition Operator in SQL performs the addition on the numerical data of
the database table.
● In SQL, we can easily add the numerical values of two columns of the same table
by specifying both the column names as the first and second operand.
● We can also add the numbers to the existing numbers of the specific column.

Syntax of SQL Addition Operator:

1. SELECT operand1 + operand2;

This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp _Id Emp_ Name Emp_Salary Emp _Monthlybonus

101 Tushar 25000 4000

102 Anuj 30000 200

● Suppose, we want to add 20,000 to the salary of each employee specified in the
table. Then, we have to write the following query in the SQL:

SELECT Emp_Salary + 20000 as Emp_New_Salary

FROM
Employee_details;

Vidya Lunge 77
DMS 22319 unit 2

o/p:

Emp_New_Salary

45000

50000

In this query, we have performed the SQL addition operation on the single column of the
given table.

● Suppose, we want to add the Salary and monthly bonus columns of the above
table, then we have to write the following query in SQL:
1. SELECT Emp_Salary + Emp_Monthlybonus as Emp_Total_Salary FROM
Employee_details;

In this query, we have added two columns with each other of the above table.

SQL Subtraction Operator (-)

The Subtraction Operator in SQL performs the subtraction on the numerical data of the
database table. In SQL, we can easily subtract the numerical values of two columns of
the same table by specifying both the column names as the first and second operand. We
can also subtract the number from the existing number of the specific table column.

Syntax of SQL Subtraction Operator:

1. SELECT operand1 - operand2;

Vidya Lunge 78
DMS 22319 unit 2

This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp Id Emp Name Emp Salary Tax

201 Abhay 25000 200

202 Sumit 30000 500

● Suppose we want to subtract 5,000 from the salary of each employee given in the
Employee_details table. Then, we have to write the following query in the SQL:

1. SELECT Emp_Salary - 5000 as Emp_New_Salary

FROM
Employee_details;

In this query, we have performed the SQL subtraction operation on the single column of
the given table.

● If we want to subtract the penalty from the salary of each employee, then we have
to write the following query in SQL:
1. SELECT Emp_Salary - Tax as Emp_Total_Salary
2. FROM
3. Employee_details;

SQL Multiplication Operator (*)

The Multiplication Operator in SQL performs the Multiplication on the numerical data
of the database table.

In SQL, we can easily multiply the numerical values of two columns of the same table
by specifying both the column names as the first and second operand.

Vidya Lunge 79
DMS 22319 unit 2

Syntax of SQL Multiplication Operator:

1. SELECT operand1 * operand2;

This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp Id Emp Name Emp Salary Penalty

201 Abhay 25000 200

202 Sumit 30000 500

● Suppose, we want to double the salary of each employee given in the

Employee_details table. Then, we have to write the following query in the SQL:

1. SELECT Emp_Salary * 2 as Emp_New_Salary

FROM
Employee_details;

In this query, we have performed the SQL multiplication operation on the single column
of the given table.

● If we want to multiply the Penalty column to Emp_Salary column of that

employee whose Emp_Id is 202, then we have to write the following query in
SQL:
1. SELECT Penalty * Emp_Salary as Penalty* Emp_Salary
2. FROM
3. Employee_details
4. WHERE Emp_Id = 202;

Vidya Lunge 80
DMS 22319 unit 2

In this query, we have multiplied the values of two columns by using the WHERE
clause.

SQL Division Operator (/)

The Division Operator in SQL divides the operand on the left side by the operand on the
right side.

Syntax of SQL Division Operator:

1. SELECT operand1 / operand2;

In SQL, we can also divide the numerical values of one column by another column of
the same table by specifying both column names as the first and second operand.

We can also perform the division operation on the stored numbers in the column of the
SQL table.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 25000

202 Sumit 30000

● Suppose, we want to half the salary of each employee given in the

Employee_details table. For this operation, we have to write the following query
in the SQL:
1. SELECT Emp_Salary / 2 as Emp_New_Salary
2. FROM
3. Employee_details;

Vidya Lunge 81
DMS 22319 unit 2

In this query, we have performed the SQL division operation on the single column of the
given table.

SQL Modulus Operator (%)

The Modulus Operator in SQL provides the remainder when the operand on the left side
is divided by the operand on the right side.

Syntax of SQL Modulus Operator:

1. SELECT operand1 % operand2;

This example consists of a Division table, which has three columns Number,
First_operand, and Second_operand.

Number First operand Second operand

1 56 4

2 32 8

3 89 9

4 18 10

5 10 5

● If we want to get the remainder by dividing the numbers of First_operand column

by the numbers of Second_operand column, then we have to write the following
query in SQL:
1. SELECT First_operand % Second_operand as Remainder
2. FROM
3. Employee_details;

Vidya Lunge 82
DMS 22319 unit 2

SQL Comparison Operators

The Comparison Operators in SQL compare two different data of SQL table and check
whether they are the same, greater, and lesser.

The SQL comparison operators are used with the WHERE clause in the SQL queries

Following are the various comparison operators which are performed on the data
stored in the SQL database tables:

1. SQL Equal Operator (=)

2. SQL Not Equal Operator (!=)

3. SQL Greater Than Operator (>)

4. SQL Greater Than Equals to Operator (>=)

5. SQL Less Than Operator (<)\

6. SQL Less Than Equals to Operator (<=)

SQL Equal Operator (=)

This operator is highly used in SQL queries. The Equal Operator in SQL shows only
data that matches the specified value in the query.

This operator returns TRUE records from the database table if the value of both
operands specified in the query is matched.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 30000

202 Ankit 40000

Vidya Lunge 83
DMS 22319 unit 2

203 Bheem 30000

204 Ram 29000

205 Sumit 30000

● Suppose, we want to access all the records of those employees from the
Employee_details table whose salary is 30000. Then, we have to write the
following query in the SQL database:
1.
2. SELECT * FROM Employee_details
3. WHERE Emp_Salary = 30000;

In this example, we used the SQL equal operator with WHERE clause for getting the
records of those employees whose salary is 30000.

SQL Equal Not Operator (!=)

The Equal Not Operator in SQL shows only those data that do not match the query's
specified value.

This operator returns those records or rows from the database views and tables if the
value of both operands specified in the query is not matched with each other.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

Vidya Lunge 84
DMS 22319 unit 2

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

● Suppose, we want to access all the records of those employees from the
Employee_details table whose salary is not 45000. Then, we have to write the
following query in the SQL database:
1. SELECT * FROM Employee_details
2. WHERE Emp_Salary != 45000;

In this example, we used the SQL equal not operator with WHERE clause for getting the
records of those employees whose salary is not 45000.

SQL Greater Than Operator (>)

The Greater Than Operator in SQL shows only those data which are greater than the
value of the right-hand operand.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

Vidya Lunge 85
DMS 22319 unit 2

205 Sumit 29000

● Suppose, we want to access all the records of those employees from the
Employee_details table whose employee id is greater than 202. Then, we have to
write the following query in the SQL database:
1. SELECT * FROM Employee_details
2. WHERE Emp_Id > 202;

Here, SQL greater than operator displays the records of those employees from the above
table whose Employee Id is greater than 202.

SQL Greater Than Equals to Operator (>=)

The Greater Than Equals to Operator in SQL shows those data from the table which
are greater than and equal to the value of the right-hand operand.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

Vidya Lunge 86
DMS 22319 unit 2

● Suppose, we want to access all the records of those employees from the
Employee_details table whose employee id is greater than and equals to 202. For
this, we have to write the following query in the SQL database:
1. SELECT * FROM Employee_details
2. WHERE Emp_Id >= 202;

Here,'SQL greater than equals to operator' with WHERE clause displays the rows of
those employees from the table whose Employee Id is greater than and equals to 202.

SQL Less Than Operator (<)

The Less Than Operator in SQL shows only those data from the database tables which
are less than the value of the right-side operand.

This comparison operator checks that the left side operand is lesser than the right side
operand. If the condition becomes true, then this operator in SQL displays the data
which is less than the value of the right-side operand.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

Vidya Lunge 87
DMS 22319 unit 2

● Suppose, we want to access all the records of those employees from the
Employee_details table whose employee id is less than 204. For this, we have to
write the following query in the SQL database:
1. SELECT * FROM Employee_details
2. WHERE Emp_Id < 204;

Here,SQL less than operator with WHERE clause displays the records of those
employees from the above table whose Employee Id is less than 204.

SQL Less Than Equals to Operator (<=)

The Less Than Equals to Operator in SQL shows those data from the table which are
lesser and equal to the value of the right-side operand.

This comparison operator checks that the left side operand is lesser and equal to the right
side operand.

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

Vidya Lunge 88
DMS 22319 unit 2

● Suppose, we want to access all the records of those employees from the
Employee_details table whose employee id is less and equals 203. For this, we
have to write the following query in the SQL database:
1. SELECT * FROM Employee_details
2. WHERE Emp_Id <= 203;

Here, SQL less than equals to the operator with WHERE clause displays the rows of
those employees from the table whose Employee Id is less than and equals 202.

SQL Logical Operators

The Logical Operators in SQL perform the Boolean operations, which give two results
True and False. These operators provide True value if both operands match the logical
condition.

Following are the various logical operators which are performed on the data stored
in the SQL database tables:

1. SQL ALL operator

2. SQL AND operator

3. SQL OR operator
4. SQL BETWEEN operator
5. SQL IN operator
6. SQL NOT operator
7. SQL ANY operator
8. SQL LIKE operator

SQL ALL Operator

The ALL operator in SQL compares the specified value to all the values of a column
from the sub-query in the SQL database.

This operator is always used with the following statement:

Vidya Lunge 89
DMS 22319 unit 2

1. SELECT,

2. HAVING, and

3. WHERE.

Syntax of ALL operator:

1. SELECT column_Name1, ...., column_NameN FROM table_Name WHERE

column Comparison_operator ALL (SELECT column FROM tablename2)

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Gurgaon

202 Ankit 45000 Delhi

203 Bheem 30000 Jaipur

204 Ram 29000 Mumbai

205 Sumit 40000 Kolkata

● If we want to access the employee id and employee names of those employees

from the table whose salaries are greater than the salary of employees who lives in
Jaipur city, then we have to type the following query in SQL.
1. SELECT Emp_Id, Emp_Name FROM Employee_details WHERE Emp_Salary >
ALL ( SELECT Emp_Salary FROM Employee_details WHERE Emp_City =
Jaipur)

Vidya Lunge 90
DMS 22319 unit 2

Here, we used the SQL ALL operator with greater than the operator.

SQL AND Operator

The AND operator in SQL would show the record from the database table if all the
conditions separated by the AND operator evaluated to True. It is also known as the
conjunctive operator and is used with the WHERE clause.

Syntax of AND operator:

1. SELECT column1, ...., columnN FROM table_Name WHERE condition1 AND

condition2 AND condition3 AND ....... AND conditionN;

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

● Suppose, we want to access all the records of those employees from the
Employee_details table whose salary is 25000 and the city is Delhi. For this, we
have to write the following query in SQL:

Vidya Lunge 91
DMS 22319 unit 2

1. SELECT * FROM Employee_details WHERE Emp_Salary = 25000 OR

Emp_City = 'Delhi';

● Here,SQL AND operator with WHERE clause shows the record of employees
whose salary is 25000 and the city is Delhi.

SQL OR Operator

The OR operator in SQL shows the record from the table if any of the conditions
separated by the OR operator evaluates to True. It is also known as the conjunctive
operator and is used with the WHERE clause.

Syntax of OR operator:

1. SELECT column1, ...., columnN FROM table_Name WHERE condition1 OR

condition2 OR condition3 OR ....... OR conditionN;

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

Vidya Lunge 92
DMS 22319 unit 2

● If we want to access all the records of those employees from the

Employee_details table whose salary is 25000 or the city is Delhi. For this, we
have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Salary = 25000 OR
Emp_City = 'Delhi';

Here, SQL OR operator with WHERE clause shows the record of employees whose
salary is 25000 or the city is Delhi.

SQL BETWEEN Operator

The BETWEEN operator in SQL shows the record within the range mentioned in the
SQL query. This operator operates on the numbers, characters, and date/time operands.

If there is no value in the given range, then this operator shows NULL value.

Syntax of BETWEEN operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM

table_Name WHERE column_nameBETWEEN value1 and value2;

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

Vidya Lunge 93
DMS 22319 unit 2

205 Sumit 40000 Kolkata

● Suppose, we want to access all the information of those employees from the
Employee_details table who is having salaries between 20000 and 40000. For
this, we have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Salary BETWEEN 30000
AND 45000;

Here, we used the SQL BETWEEN operator with the Emp_Salary field.

SQL IN Operator

The IN operator in SQL allows database users to specify two or more values in a
WHERE clause. This logical operator minimizes the requirement of multiple OR
conditions.

This operator makes the query easier to learn and understand. This operator returns those
rows whose values match with any value of the given list.

Syntax of IN operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM

table_Name WHERE column_name IN (list_of_values);

This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

Vidya Lunge 94
DMS 22319 unit 2

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

● Suppose, we want to show all the information of those employees from the
Employee_details table whose Employee Id is 202, 204, and 205. For this, we
have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Id IN (202, 204, 205);

Here, we used the SQL IN operator with the Emp_Id column.

● Suppose, we want to show all the information of those employees from the
Employee_details table whose Employee Id is not equal to 202 and 205. For this,
we have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Id NOT IN (202,205);
2.

Here, we used the SQL NOT IN operator with the Emp_Id column.

SQL NOT Operator

The NOT operator in SQL shows the record from the table if the condition evaluates to
false. It is always used with the WHERE clause.

Syntax of NOT operator:

1. SELECT column1, column2 ...., columnN FROM table_Name WHERE NOT

condition;

Vidya Lunge 95
DMS 22319 unit 2

This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

● Suppose, we want to show all the information of those employees from the
Employee_details table whose Cityis not Delhi. For this, we have to write the
following query in SQL:
1. SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' ;

In this example, we used the SQL NOT operator with the Emp_City column.

● Suppose, we want to show all the information of those employees from the
Employee_details table whose Cityis not Delhi and Chandigarh. For this, we
have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' AND
NOT Emp_City = 'Chandigarh';

In this example, we used the SQL NOT operator with the Emp_City column.

SQL ANY Operator

Vidya Lunge 96
DMS 22319 unit 2

The ANY operator in SQL shows the records when any of the values returned by the
sub-query meet the condition.

The ANY logical operator must match at least one record in the inner query and must be
preceded by any SQL comparison operator.

Syntax of ANY operator:

1. SELECT column1, column2 ...., columnN FROM table_Name WHERE

column_name comparison_operator ANY ( SELECT column_name FROM
table_name WHERE condition(s)) ;

SQL LIKE Operator

The LIKE operator in SQL shows those records from the table which match with the
given pattern specified in the sub-query.

The percentage (%) sign is a wildcard which is used in conjunction with this logical
operator.

This operator is used in the WHERE clause with the following three statements:

1. SELECT statement

2. UPDATE statement

3. DELETE statement

Syntax of LIKE operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM

table_Name WHERE column_name LIKE pattern;

This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

Vidya Lunge 97
DMS 22319 unit 2

201 Sanjay 25000 Delhi

202 Ajay 45000 Chandigarh

203 Saket 30000 Delhi

204 Abhay 25000 Delhi

205 Sumit 40000 Kolkata

● If we want to show all the information of those employees from the

Employee_details whose name starts with ''s''. For this, we have to write the
following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Name LIKE 's%' ;

In this example, we used the SQL LIKE operator with Emp_Name column because we
want to access the record of those employees whose name starts with s.

● If we want to show all the information of those employees from the

Employee_detailswhose name ends with ''y''. For this, we have to write the
following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Name LIKE '%y' ;
● If we want to show all the information of those employees from the
Employee_detailswhose name starts with ''S'' and ends with ''y''. For this, we have
to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Name LIKE 'S%y' ;

++++++++++++++++++++++++++++++++++++++++++++

Vidya Lunge 98