MODULE II

21CS53 Database Management Systems

MODULE III
Contents
Normalization
• Database Design Theory – Introduction to Normalization using
Functional and Multivalued Dependencies:
• Informal design guidelines for relation schema,
• Functional Dependencies, Normal Forms based on Primary Keys,
• Second and Third Normal Forms
• Boyce-Codd Normal Form,
• Multivalued Dependency and Fourth Normal Form,
• Join Dependencies and Fifth Normal Form.

SQL
• SQL data definition and data types,
• Schema change statements in SQL,
• specifying constraints in SQL,
• retrieval queries in SQL, INSERT, DELETE, and UPDATE statements
in SQL, Additional features of SQL

Functional Dependencies
[Link]
[Link]

Functional dependency means some attributes determine the other. For example, FD: X → Y,
this means X determines Y. Here X is the determinant and Y is the Dependent. There are 4types
of dependency, trivial, nontrivial, multi-valued, and transitive functional dependency.

Trivial Dependency - means X → X, that is X determines X. Example FD: X → Y is a trivial

dependency if Y is a subset of X.

Example, {Roll No, Name} → Name, here Name is the subset of determinant set.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

1
 MODULE II
21CS53 Database Management Systems

Non-Trivial Dependency - FD: X → Y is a nontrivial dependency only if 𝑋 ∩ 𝑌 = ∅.

For Example SID → SName, SID → Phone No

Multi-valued Dependency - FD: X → YZ is a multi valued dependency( since there are two
attributes in the right side), only if there should not be any functional dependency between Y
to Z and Z to Y

Transitive Functional Dependency - FD: X → Y, FD: Y → Z,= then FD: X → Z

Example, SID → SName , SName → PhoneNo, then SID → Phone No

Properties of Nontrivial Dependencies

• Reflexivity - FD: X → X, FD: SID → SID

• Augmentation - FD: X → Y, FD: XZ → YZ,
Example SID → SName then, SID, Phone → Sname, Phone No
• Transitivity - FD: X → Y, FD: Y → Z,= then FD: X → Z
Example, SID → SName , SName → PhoneNo, then SID → Phone No
• Union – If X → Y, X → Z, the X → YZ
• Decomposition – if X → YZ the X → Y and X → Z
• Pseudo transitivity – if X → Y the YZ → W , then XZ → W
• Composition - X → Y, W → Z, then X 𝑊 → YZ

Attribute closure of the set

[Link]

In database management systems (DBMS), attribute closure refers to the set of all
attributes that can be functionally determined from a given set of attributes, according to a set
of functional dependencies.

The closure of a set of attributes with respect to a set of functional dependencies is the set of
all attributes that can be derived from the original set by applying those functional
dependencies. It represents all the attributes that can be determined (or inferred) based on the
given functional dependencies.

The closure of an attribute set X under a set of functional dependencies F, denoted as X+, is
the smallest set of attributes such that:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

2
 MODULE II
21CS53 Database Management Systems

1. X+ contains all attributes in X.

2. If Y is in X+, and Y -> Z is in F, then Z is also in X+.

In simpler terms, attribute closure involves repeatedly applying the functional dependencies to
determine all the attributes that can be derived from the given set of attributes.

For example, let's say we have the following functional dependencies:

• A -> B
• B -> C
• D -> E

If we want to find the closure of the attribute set {A}, we would start with {A}. Then, we apply
the functional dependencies:

• Since A -> B, we add B to the set: {A, B}.

• Since B -> C, we add C to the set: {A, B, C}.

Now, {A, B, C} is the closure of {A} under the given functional dependencies.

Attribute closure is important in database design, particularly in the process of normalization,

where it helps in identifying keys, candidate keys, and determining the normal forms of
relations.
Example of finding attribute closure
[Link]

Consider the relation, R (A, B, C, D, E), FD: { A →B, B →C, C →D, D →E}, Find all the
attribute closures.

Attribute closure of A- 𝑨+

A →A, so A belongs to 𝑨+ (Reflexive Property)

A →B, B →C means A →C (Transitive property), So C belongs to 𝑨+

A →C, , C →D means, A →D, So D belongs to 𝑨+

A →D, D →E, means, A →E, So E belongs to 𝑨+

𝑨+ = { 𝑨, 𝑩, 𝑪, 𝑫, 𝑬}

Attribute closure of B- 𝑩+

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

3
 MODULE II
21CS53 Database Management Systems

B →B, so B belongs to 𝑩+ (Reflexive Property)

B →C, C belongs to 𝑩+

B →C, C →D, means B →D so D belongs to 𝑩+ (transitive)

B →D, D →E, means B →E so E belongs to 𝑩+ (transitive)

𝑩+ = { 𝑩, 𝑪, 𝑫, 𝑬}

Attribute closure of C- 𝑪+

C →C, means C belongs to 𝑪+

C →D, D →E, means C → E 𝒎𝒆𝒂𝒏𝒔 𝑬 𝒃𝒆𝒍𝒐𝒏𝒈𝒔 𝒕𝒐 𝑪+

𝑪+ = {C, D, E}

Attribute closure of D- 𝑫+

D →D, means D belongs to 𝑫+

D →E, means E belongs to 𝑫+

𝑫+ = {D, E}

Attribute closure of E- 𝑬+

E →E, means E belongs to 𝑬+

𝑬+ = {𝑬}

Attribute closure of - 𝑨𝑫+

AD →A, AD →D means A, D belongs to 𝐴𝑫+

Now focus on the set {A, D}

{A →B, B →C, C →D, D →E} – now see the FD in the question

𝐴𝐷+ = {A, D, B, C, E}

Attribute closure of - 𝐴𝐵𝐶 + = {A, B, C,D, E}

Attribute closure of - 𝐶𝐷 + = {C, D,E}

Next, we will discuss how to find the super key using the context of Attribute closure

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

4
 MODULE II
21CS53 Database Management Systems

Super key means whose closure contains all the attributes of the given relation. In the
above example, A is a super key, and AD is a super key. Also, if we attach any attribute to the
super key, definitely that will be a super key. That is AB, ABC, ABCD, ABCDE, ABE, ACE,
AE, AE,AD, …. Together there will be 16 Super keys all will be super keys.
Next, we will find the Candidate Key – Candidate Key means no proper subset of it
should be a Super Key. Hence in this example, A is the only candidate Key.

Example 2: Consider the relation, R(A, B, C, D, E), FD: { A →B, D →E}, Find all the attribute
closures.
Solution:

𝐴+ ={A, B}

𝐵 + ={ B}

𝐷+ ={ D, E}

𝐸 + ={E},

𝐴𝐶 + ={A,C, B},

𝐴𝐸 + ={A, E, B},

𝐴𝐵𝐶𝐷𝐸 + ={A, B, C, D, E} ---Super key

𝐴𝐵𝐶 + = {A, B, C}

𝐴𝐶𝐷𝐸 + = {A, C, D, E, B} ---Super key

𝐴𝐶𝐷+ = {A, C, D, B, E} ---ACD is a Super key and also a candidate key.

Example 2(Solution in a Different Way): Consider the relation, R (A, B, C, D, E), FD: {A →B,
D →E}, Find the candidate key

Solution: 𝐴𝐵𝐶𝐷𝐸 + = {A, B, C, D, E}. Since A →B, A is enough to reach to B, so we discard

B. Consider ={A, C, D, E}. Since D →E, D is enough to reach E, so discard E. Now Consider
{A, C, D}. 𝐴𝐶𝐷+ = {A, C, D, B, E} ---ACD is a Super key and also a candidate key.

Example 3 : Consider the relation, R (A, B, C, D, E), FD: {A →B, B →C , C →A }, Find the
candidate key.

ABCD+ = {A, B, C, D}, A →B so discard B.

Consider {A,C,D}, Since A determine C , discard C.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

5
 MODULE II
21CS53 Database Management Systems

Consider {A, D}. Hence AD+ is the Super key, also Since from C we can reach A, we can
replace A with C also. Hence candidate keys are AD and CD.
Example – Check whether the two relations are equivalent or not
[Link]

F={A → 𝐶, 𝐴𝐶 → 𝐷, 𝐸 → 𝐴𝐷, 𝐸 → 𝐻} 𝑎𝑛𝑑 𝐺 = { 𝐴 → 𝐶𝐷, 𝐸 → 𝐴𝐻}

Solution: Two relations are equivalent if 𝐹 ⊆ 𝐺 𝑎𝑛𝑑 𝐺 ⊆ 𝐹, then 𝐹 ≡ 𝐺

First, we have to start from F and We will find the closure of LHS of F by looking at the RHS
of G.

𝐴+ = {𝐴, 𝐶, 𝐷}, 𝐴𝐶 + = {A, C, D}, 𝐸 + = {𝐸, 𝐴, 𝐻, C, D}}--------(1)

Next we find the closure of all in F , by looking at the relations in F

𝐴+ = {𝐴, 𝐶, 𝐷}, , 𝐴𝐶 + = {𝐴, 𝐶, 𝐷}, 𝐸 + = {𝐸, 𝐻, 𝐴, 𝐷, 𝐶},} -------------(2)

(1) And (2) are same, hence 𝐹 ⊆ 𝐺 ----------(3)

Next we will start from G, and write all the closures by looking at F.

𝐴+ = {𝐴, 𝐶, 𝐷}, 𝐸 + = {𝐸, 𝐴, 𝐻, 𝐶, 𝐷}------------(4)

Next we find the closure of all in G , by looking at the relations in G

𝐴+ = {𝐴, 𝐶, 𝐷}, 𝐸 + = {𝐸, 𝐴, 𝐻, 𝐶, 𝐷}------------(5)

(4) And (5) are same, hence 𝐺 ⊆ 𝐹 ------(6)

Combining (3) and (6), 𝐹 ≡ 𝐺

Example: -𝑅(𝐴, 𝐶, 𝐷, 𝐸, 𝐻), 𝐸 = {𝐴 → 𝐶, 𝐴𝐶 → 𝐷, 𝐸 →→→→ 𝐴𝐷, 𝐸

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

6
 MODULE II
21CS53 Database Management Systems

Introduction to Normalization

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

7
 MODULE II
21CS53 Database Management Systems

[Link]
Normalization is a technique to remove Redundancy (means duplication) ot to reduce the
redundancy. There are two Types of redundancy, they are Row level redundancy and column
level redundancy. For example consider the below table,

STUDENT SID SName Age

1 Ram 30
2 Rani 25
1 Ram 30

In the above table first and last rows are the same, there is a duplication, that is called the row
level redundancy. This can be removed by fixing SID as a primary k(Since primary key does
not allow null values and it is the unique identification of a tuple). Next we will discuss the
column level redundancy. For example, consider the below table,

STUDENT SID SName CID FID CName FName Salary

1 Ram C1 F1 DBMS John 3000
2 Rani C2 F2 JAVA Bob 2500
3 Nithin C1 F1 DBMS John 3000
4 Arnav C1 F1 DBMS John 3000

If we have a closer look at the table few column values are repeated. F1, DBMS, John, 3000
are repeated thrice in the table. That is called column level redundancy. So we will face three
issues(Anomalies)

• Insertion Anomalies
• Deletion Anomalies
• Updation Anomalies

Insertion Anomalies – I wanted to add a tuple with SID 5, then we can add it easily
to the database, and there will not be any issue. But the university has introduced a new
course MBBS and the university wants to add the CName, FID, FName, and Salary to
this table, which is not possible since SID is the primary key and no student has been
admitted to this course yet, so data cannot be inserted to this database. This is called
Insertion Anomy.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

8
 MODULE II
21CS53 Database Management Systems

Deletion Anomalies – If we want to remove the details of the row DID =1, which
is possible and easily can be done. But if we want to delete the details where SID =2,
then the only one student who studied the course JAVA, and while we delete the record
of SID 2, actually we will lose the entire information about the faculty details of JAVA,
which cannot be recovered later. This is called the deletion Anomy.

Updation Anomalies – Update the SName of Arnav to Anirudh is possible. But if

we say update the salary of the faculty from 3000 to 400 where the FID is F1. In this
case, it will be updated all three places. But actually, we want to change the salary only
once. This is called the update Anomy.

Informal design guidelines for relation schema

[Link]

We need to design the database in such a way that it should not contain any redundancy
and data should be in proper format and easily accessible. So to achieve that, there are 4
guidelines. They are,

• Semantics of the attributes

• Reducing the redundant values in the tuples
• Reducing the null values in the tuples
• Disallowing the possibility of generating spurious tuples

The semantics of the attributes – These attribute definitions help in understanding the
meaning and purpose of each attribute within the context of the Student entity. Let's describe
the semantics of the attributes in the ER diagram. Here the titles Roll No, Name, DOB, and
Age all give a clear picture of the values stored in the column.

1. Roll No: This attribute represents the unique identifier assigned to each student
enrolled in the system. It serves as the primary key for the Student entity.
2. Name: This attribute represents the name of the student. It stores the textual
information representing the student's name.
3. DOB (Date of Birth): This attribute stores the date of birth of the student. It
represents the specific date on which the student was born.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

9
 MODULE II
21CS53 Database Management Systems

4. Age: This attribute is derived from the Date of Birth attribute. It represents the age of
the student, calculated based on the current date and the student's date of birth.

Reducing the redundant values in the tuples - This will save the storage space and we can
avoid the three anomalies.
Reducing the null values in the tuples – Design the base relation Schemas well, so that no
insertion, deletion, and updation anomalies will occur. Better to reduce the null values in the
tables. For Example, if 10 % of the employees have an office space and others are working
from home, then in the Emp_ office Space column most of the entries will be null. In such a
case better to have another table called EMP_OFFICE, rather than filling the data in the
EMPLOYEE table.
Disallowing the possibility of generating spurious tuples – Spurious tuples means that are
not in the original relation, but generated by some natural join of decomposed sub relation

First Normal Forms

[Link]
To convert a database schema into the First Normal Form (1NF), you need to ensure that all
attributes contain atomic values and that there are no repeating groups or arrays in the table.
Here are the rules to convert to 1NF:
Atomic Values: Ensure that each attribute contains only atomic (indivisible) values. If an
attribute contains multiple values or a composite value, you need to decompose it into separate
attributes.

• Unique Column Names: Make sure that each column (attribute) has a unique name
within the table.
• Eliminate Repeating Groups: If an attribute contains multiple values that apply to the
same entity, create a separate table for those multi-valued attributes.
• Identify a Primary Key: Define a primary key for each table. The primary key
uniquely identifies each row in the table.
• Ensure Each Cell Contains a Single Value: Each cell in the table should contain a
single value, not a list or a set of values.
Here's how you can apply these rules to convert a database schema into 1NF:

• Identify attributes that contain multiple values or composite values and decompose
them into separate attributes.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

10
 MODULE II
21CS53 Database Management Systems

• Ensure that each column (attribute) has a unique name within the table.
• Create separate tables for multi-valued attributes, using the primary key of the original
table as a foreign key in the new table.
• Remove any repeating groups or arrays within the table, ensuring that each cell contains
a single value.

Converting a database schema to the First Normal Form (1NF) can be achieved in several ways,
depending on the structure of the original schema and the nature of the data. Here are three
common methods to convert a database schema to 1NF:
1. Flattening: In this method, you flatten the multi-valued attributes or nested structures
into a single table by duplicating the primary key and associating each instance of the
multi-valued attribute with its corresponding primary key. This approach is suitable
when the multi-valued attributes are not dependent on each other.
2. Creating Separate Tables: Another approach is to create separate tables for each
multi-valued attribute. Each table contains a foreign key that references the primary key
of the original table. This method is suitable when the multi-valued attributes have a
one-to-many relationship with the main entity.
3. Composite Attributes: For composite attributes, you decompose them into individual
attributes, each representing a component of the composite attribute. This ensures that
each attribute contains atomic values. This approach is suitable for attributes that have
sub-components, such as addresses or names.

Let's illustrate each method with an example:

Original Schema : Student (Roll_No, Name, Subjects)

Method 1: Flattening: Student (Roll No, Name, Subject1, Subject2, Subject3, ...)

Here Each instance of the multi-valued attribute "Subjects" is represented as a separate column
in the table.

Method 2: Creating Separate Tables – The "Subjects" attribute is moved to a separate table
with a foreign key referencing the "Student" table.

Student (Roll No, Name)

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

11
 MODULE II
21CS53 Database Management Systems

Subjects (Roll_No, Subject)

Method 3: Composite Attributes - The composite attribute "Address" is decomposed into

individual attributes: DNo, Street, and City. Roll no has become the foreign key for the Address
table.
Student (Roll No, Name)
Address (Roll No, DNo, Street, City)
Each of these methods achieves 1NF by ensuring that each attribute contains atomic values and
there are no repeating groups or nested structures in the schema. The choice of method depends
on the specific characteristics of the data and the requirements of the database design

Second Normal Forms

[Link]
[Link]

Second Normal Form (2NF) is a database normalization rule that helps ensure data integrity by
removing partial dependencies. A relation is in 2NF if it meets the following conditions:

1. It is in 1NF: This means that the table has a primary key and all its attributes are
atomic (indivisible).

2. No partial dependencies: Every non-prime attribute (attributes not part of any

candidate key) must be fully functionally dependent on the primary key.

Example – Check whether the given relation I in second normal form or not, R(A, B, C, D, E,
F)FD={ A → 𝐵, 𝐵 → 𝐶, 𝐶 → 𝐷, 𝐷 → 𝐸}

Solution – ABCDEF+ = {A, B, C, D, E, F}

A → 𝐵, 𝑚𝑒𝑎𝑛𝑠 𝑤𝑒 𝑐𝑎𝑛 𝑑𝑒𝑙𝑒𝑡𝑒 𝐵, 𝐵 → 𝐶, 𝑚𝑒𝑎𝑛𝑑 𝑑𝑒𝑙𝑒𝑡𝑒 𝐶, 𝐶 → 𝐷,

𝑚𝑒𝑎𝑛𝑠 𝑤𝑒 𝑐𝑎𝑛 𝑑𝑒𝑙𝑒𝑡𝑒𝑑 𝐷, 𝐷 → 𝐸 𝑚𝑒𝑎𝑛𝑠 𝑤𝑒 𝑐𝑎𝑛 𝑑𝑒𝑙𝑒𝑡𝑒 𝐸 . So AF is the Super Key and
also it is a candidate key. Next, we have to see if there are any more Candidate keys or not. For
the check A or F is there on the right-hand side of the relation or not? Since it is not there, so
AF is the only candidate key. Next, we have to check if is there any partial dependency or not.
The subset of the candidate key is A and F. So non-prime attributes are B, C, D, and E. Next,

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

12
 MODULE II
21CS53 Database Management Systems

we have to see whether A or F determines B, C, D, or E. Since A → 𝐵 , there is a partial

dependency, so the above relation is not in the Second NF.
Example – Check whether the given relation is in the second normal form or not, R(A,
B, C, D), FD={ 𝑨𝑩 → 𝑪𝑫, 𝑪 → 𝑨, 𝑫 → 𝑩
Solution: ABCD+ = {A, B, C, D}

𝐴𝐵 → 𝐶𝐷, means 𝐴𝐵 → 𝐶 and 𝐴𝐵 → 𝐷, so we can remove C and D. Hence AB is a super

key and also a candidate key. Next, we have to see if is there any other candidate key or not.
The subset of the candidate keys are A and B. For that see either A or B is present in the right-
hand side in any of the relations. Since B is there on the right-hand side of the relation, we can
replace B with D. Hence AD is also a candidate Key. Hence AB and AD are the candidate keys.
Also since A appears in the RHS of the relation we can replace AB by AC. Hence AB, AC, and
AD are the candidate keys. The proper subset of it is {A, B, C, D}, So there is no non-prime
attribute in this case. Hence there is no partial dependency here. Hence the given relation is in
2NF
Example – Check whether the given relation is in the second normal form or not, R(A,
B, C, D), FD={ 𝑨 → 𝑩, 𝑩 → 𝑪, 𝑪 → 𝑫},
Solution: Here A is the super key, and candidate key also. Also there is no proper subset of it ,
So there is no partial dependency in this case , Hence the given relation is in 2NF.
NOTE: If the candidate key has only a single attribute, then there will not be any partial
dependency and it will be in 2NF.

Third Normal Form

[Link]

Third Normal Form (3NF) is a concept in database normalization, which is a process used
to organize a relational database into a more efficient and less redundant structure.
Normalization aims to reduce data redundancy and improve data integrity.

To understand 3NF, let's briefly review the previous normal forms, to be in the third normal
form, it should be in the second Normal form, and also there should not be any transitivity in
the relation. Or in turn, we can say that a non-prime attribute should not determine a non-prime
attribute.

Note: To check the transitivity, easy way is LHS of any relation should be a candidate key
or RHS of the relation is a prime attribute

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

13
 MODULE II
21CS53 Database Management Systems

To achieve 3NF, you need to eliminate any transitive dependencies by breaking down tables
and creating new ones to ensure that each non-key attribute depends only on the primary key.
This often involves splitting tables and creating relationships between them.

Example: Consider R(A, B, C, D) FD= {AB → 𝑪, 𝑪 → 𝑫} check whether the relation is in

third normal form or not.

Solution: 𝐴𝐵𝐶𝐷 + = {𝐴, 𝐵, 𝐶, 𝐷}, 𝑠𝑖𝑛𝑐𝑒 AB → 𝑪, 𝑪 → 𝑫 , we can remove C and D. Hence AB

is a candidate key. Next, we have to see if is there any other candidate key in the relation or
not. The subset of candidate keys are A and B, since they do not appear in the right-hand side
of any relation, there is no other candidate key in this relation. Next, we check the transitivity.
Here the transitivity exists, so this relation is not in the third normal form.
The easiest way to check the transitivity exists is, A table is in 3ND if and only if one among
the following condition holds 1) either the candidate key should be present on the left-hand
side of the relation 2) The RHS of the relation should be a prime attribute
either the candidate key should be present on the left-hand side of the relation or the RHS of
the relation should be a prime attribute, then the transitivity exists. Here transitivity exists in
the relation so it is not in third normal form

Example: Consider R(A, B, C, D) FD= {AB → 𝑪𝑫, 𝑫 → 𝑨} check whether the relation is
in third normal form or not.

Solution: First we find the Candidate key. : 𝐴𝐵𝐶𝐷+ = {𝐴, 𝐵, 𝐶, 𝐷} , since AB →

𝐶𝐷, 𝑤𝑒 𝑐𝑎𝑛 𝑟𝑒𝑚𝑜𝑐𝑒 𝐶 𝑎𝑛𝑑 𝐷. So, AD is a candidate key. Next, we have to check is there any
other candidate key. A and B are prime attributes, since A appears in the RHS of the relation
we can replace A with D. Hence AD is also a candidate key. Hence candidate keys are AB and
AD. Next, we have to see if the transitivity exists or not. Here C is the non-prime attribute,
which can not be determined by any other non-prime attributes. Hence the relation is in 3NF.
Here it satisfies candidate key appears in the LHS of a relation, and a prime attribute appears
in the RHS, then there will not be any transitivity. Hence, in this case, this relation is in the
third normal form.

Example: Consider R(A, B, C, D, E, F), FD= {AB → 𝑪𝑫𝑬𝑭, 𝑩𝑫 → 𝑭} check whether the
relation is in third normal form or not.
Solution: The candidate key is AB and there is no other candidate key present in this relation.
Prime attributes are C, D, E, and F. 𝐵𝐷 → 𝐹 , means
, 𝑎 𝑛𝑜𝑛 𝑝𝑟𝑖𝑚𝑒 𝑎𝑡𝑡𝑟𝑖𝑏𝑢𝑡𝑒𝑠 𝑑𝑒𝑡𝑒𝑟𝑚𝑖𝑛𝑒 𝑎 𝑛𝑜𝑛 𝑝𝑟𝑖𝑚𝑒 𝑎𝑡𝑡𝑟𝑖𝑏𝑢𝑡𝑒𝑠, hence this relation is not in
3NF

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

14
 MODULE II
21CS53 Database Management Systems

Boyce Codd Normal Form BCNF

[Link]

BCNF stands for Boyce-Codd Normal Form, which is an advanced level of database
normalization beyond the Third Normal Form (3NF). BCNF is named after Raymond Boyce
and Edgar Codd, who introduced it as an enhancement to the previous normalization concepts.

BCNF deals with certain types of anomalies that may still exist in 3NF. Specifically, it
addresses situations where non-trivial dependencies exist between attributes that are not
candidate keys. These dependencies can lead to anomalies such as insertion, update, and
deletion anomalies.

The condition to be in BCNF is 1) It should be in 3ND

2) In each Functional dependency, LHS should be the Super key

Example - R(A, B, C), FDs= { A→ 𝑩, 𝑩 → 𝑪, 𝐂 → 𝑨}, Which normal form appear here

Candidate keys are A, B, and C and each appears in the LHS so it is in BCNF

Example – Check which highest normal form appear in the relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸), 𝐹𝐷 =
{𝐴 → 𝐵𝐶𝐷𝐸, 𝐵𝐶 → 𝐴𝐶𝐸, 𝐷 → 𝐸}

Solution : First find the candidate key. Is the candidate key. Next we have to see is there any
other candidate key or not. V 𝐵𝐶 → 𝐴𝐶𝐸, A is on the right-hand side of the relation. BC →
A, BC → E, BC → C. Hence A can be replaced with BC. Hence A and BC are the candidate
keys. , BC → ACE , means , BC → A, , BC → C, , BC → E. AC n and AB are Super Keys, But
it is not the candidate key since the proper subset of it it is a candidate key

Hence A and BC are the candidate keys. It is better to start with

With the highest normal form rather than starting with the lowest normal form. First, we check
if is it in BCNF or not. In all the FDs, the left-hand side should be a candidate key, which
doesn’t show here for the last relation, so it I not in BCNF. Next, we have to check if is it in
3rNF. Here prime attributes are A, B, and C, and nonprime attributes are D, and E, and D → 𝐸,
which is a non-prime attribute determines non-prime attributes, so this is not in 3NF. Next, we
will check for 2NF. That is there should not be any partial dependencies. (Partial dependency
m- That is any proper subset of the candidate key determines the non-prime attribute) Here

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

15
 MODULE II
21CS53 Database Management Systems

non - prime attributes are D, E and the Subset of the candidate key does not determine them.
Hence the given relation is in 2NF.

Example: What is the highest normal form present in the relation 𝑅( 𝐴, 𝐵, 𝐶, 𝐷, 𝐸), FD={AB
→ 𝐶𝐷𝐸, 𝐷 → 𝐴}
Solution: AB is the candidate key. Prime attributes are A and B. Next, we have to see if is
there any other candidate key or not. Check whether A or B appears in the r RHS of any relation
or not. , 𝐷 → 𝐴. So A can be replaced with D. Hence BD is also a candidate key. Hence prime
attributes are B and D. AB → 𝐶𝐷𝐸 𝑚𝑒𝑎𝑛𝑠 AB → 𝐶 , AB → 𝐷, 𝑎𝑛𝑑 AB →
𝐸, 𝐻𝑒𝑛𝑐𝑒 𝐴𝐵 𝑎𝑛𝑑 𝐵D are the candidate keys. First, we will check for BCNF. Since in the
second FD, LHS is not a candidate key, it is not in BCNF. Next, we will check for 3NF. Here
prime attributes are A, B, and D and non-prime attributes are C and E. Here no non-prime
attributes determine another non-prime attribute, So it is in 3NF ‘ Also we can say that among
the relations LHS is the candidate key or any prime attribute is there in the RHS of the relation,
hence it is in 3NF

4th Normal Form – 4NF

[Link]
o A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued
dependency.
o For a dependency A → B, if for a single value of A, multiple values of B exist, then the
relation will be a multi-valued dependency.
o 4NF ensures data consistency, minimizes redundancy, and improves database
performance.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

16
 MODULE II
21CS53 Database Management Systems

SQL

SQL data definition and data types

[Link]

Each column in a database table is required to have a name and a data type. An SQL
developer must decide what type of data will be stored inside each column when creating a
table. The data type is a guideline for SQL to understand what type of data is expected inside
each column, and it also identifies how SQL will interact with the stored data.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

17
 MODULE II
21CS53 Database Management Systems

• Binary – Used to store the images

• Bit, Tiny, Small(int), Big(int), Decimal – all vary in their capacity – that is the
range that they can support
• Char – Fixed Size – once you have fixed it you can’t change it
• Varchar – We can modify the size of the Data Type
• Text – To store alphanumeric values- that is to store both numbers and the
alphabet together. For example, to store the Pan Number, the data type should
be Text
• The common data types that can be used in Oracle, SQL Server, and MySQL
are float, int (Integer), Varchar, Char, Date, Time, and DateTime.
Schema change statements in SQL
[Link]
This can be used to alter a Schema by adding, or dropping tables, attributes, constraints
and other schema elements. This can be done when the database is operational and does not
require recompilation of the data schema. Drop and Alter are the two schema change
statements.

Drop Command
• Drop command can be used to drop the named schema elements such as tables, domains
or constraints.
• One can drop the Schema itself For Example, if the whole schema is no longer needed
DROP SCHEMA command can be used. There are two drop behaviour options
CASCADE and RESTRICT. For example, if we no longer wish to keeptrack of

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

18
 MODULE II
21CS53 Database Management Systems

dependents in the company databse(Schema COMPANY, Relations EMPLOYEE,

DEPENDENT, ) we can get rid off the DEPENDENT table by using,
DROP TABLE DEPENDENT CASCADE
Before dropping Schema, if you want to remove all the elements, domains in the table,
the CASCADE option is used.
If the restrict option is chosen instead of CASCADE, a table is dropped only if it is not
referenced in the constraints.
ALTER command – To alter the table by dropping or adding a column(attribute),
changing a column definition and adding and dropping table constraints.
For Example, if we want to add an attribute for keeping track of the employees in the
EMPLOYEE table by adding a column JOB in the COMPANY schema.
ALTER TABLE [Link] ADD COLUMN JOB VARCHAR(20)
ALTER TABLE [Link] DROP COLUMN SALARY
ALTER TABLE [Link] MODIFY COLUMN JOB VARCHAR(10)

we will be discussing schema and how to create, alter, and drop the schema.
• Create Schema

A schema is usually a collection of objects. The objects can be tables, triggers,

views, procedures, etc. A database may have one or more schemas. SQL Server provides
a feature of pre-defined schemas. The names of pre-defined schemas are very similar to
that of built-in schemas. A user can create a schema using the syntax mentioned below.

Authorization is a keyword that provides authority over the schema. He/she has
the control over the resources that can be accessed and security can also be provided. The
name of the owner must be provided under owner name.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

19
 MODULE II
21CS53 Database Management Systems

Output –
The GO command executes the statement and a new schema is created.

• Alter Schema
Alter is generally used to change the contents related to a table in SQL. In the case of
SQL Server, an alter schema is used to transfer the securable/contents from one
schema to another within the same database.

• target_schemaname is the name of the schema in which the object/contents

should be transferred.
• TRANSFER is a keyword that transfers the contents from one schema to the
other.
• entity _type is the contents or kind of objects that are to be transferred.
• securable_name is the name of the schema in which the object is present.

Example –
A table named university has two schemas Student and Lecturer. If suppose, the
marks of the students have to be transferred to the lecturer schema, the query is as
follows –

This way, the marks are transferred to the lecturer's schema.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

20
 MODULE II
21CS53 Database Management Systems

Drop schema
drop_schema is used when the schema and its related objects have to be completely
banished from the database including its definition.

IF EXISTS is optional yet if a user wants to check whether a schema exists in the database
or not. Schema name is the name of the schema in the database.
Example –

• Student is a schema that is actually present in the university database.

• The schema is dropped from the database along with its definition.
specifying constraints in SQL
[Link]

SQL constraints are used to specify rules for data in a table. Constraints can be
specified when the table is created with the CREATE TABLE statement, or 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,
and table-level constraints apply to the whole table.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

21
 MODULE II
21CS53 Database Management Systems

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value

• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

Example: NOT NULL

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
SQL NOT NULL on ALTER TABLE

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

ALTER TABLE Persons

ALTER COLUMN Age int NOT NULL;

My SQL / Oracle (prior version 10G):

ALTER TABLE Persons

MODIFY COLUMN Age int NOT NULL;

Oracle 10G and later:

ALTER TABLE Persons

MODIFY Age int NOT NULL;

SQL UNIQUE Constraint on CREATE TABLE

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

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

22
 MODULE II
21CS53 Database Management Systems

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

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

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

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

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:

ALTER TABLE Persons

ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use
the following SQL syntax:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

23
 MODULE II
21CS53 Database Management Systems

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

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).

SQL PRIMARY KEY on CREATE TABLE

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

MySQL:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

24
 MODULE II
21CS53 Database Management Systems

CREATE TABLE Persons (

ID int NOT NULL,
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:

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)
);

SQL PRIMARY KEY on ALTER TABLE

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

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD PRIMARY KEY (ID);

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

25
 MODULE II
21CS53 Database Management Systems

To allow the naming of a PRIMARY KEY constraint, and for

defining a PRIMARY KEY constraint on multiple columns, use the
following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

DROP a PRIMARY KEY Constraint

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

MySQL:

ALTER TABLE Persons

DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT PK_Person;

SQL FOREIGN KEY Constraint

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:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

26
 MODULE II
21CS53 Database Management Systems

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:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

27
 MODULE II
21CS53 Database Management Systems

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,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow the 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)
);
Note: )[----( FK_PersonOrder is both the table name together is PersonOrder)

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);

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

28
 MODULE II
21CS53 Database Management Systems

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:

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;

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:

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,

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

29
 MODULE II
21CS53 Database Management Systems

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),
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:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

30
 MODULE II
21CS53 Database Management Systems

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons

DROP CHECK CHK_PersonAge;

SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the
"Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using
functions like GETDATE():

CREATE TABLE Orders (

ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT on ALTER TABLE

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

MySQL:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

31
 MODULE II
21CS53 Database Management Systems

ALTER TABLE Persons

ALTER City SET DEFAULT 'Sandnes';

SQL Server:

ALTER TABLE Persons

ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;

MS Access:

ALTER TABLE Persons

ALTER COLUMN City SET DEFAULT 'Sandnes';

Oracle:

ALTER TABLE Persons

MODIFY City DEFAULT 'Sandnes';

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons

ALTER City DROP DEFAULT;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT;

SQL Server:

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT;

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

32
 MODULE II
21CS53 Database Management Systems

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two ways:

• Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

• If you are adding values for all the columns of the table, you do not need to specify the
column names in the SQL query. However, make sure the order of the values is in the
same order as the columns in the table. Here, the INSERT INTO syntax would be as
follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

Demo Database
Below is a selection from the Customers table used in the examples:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

33
 MODULE II
21CS53 Database Management Systems

The following SQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,

Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns. The following SQL statement will
insert a new record, but only insert data in the "CustomerName", "City", and "Country"
columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

Result

Insert Multiple Rows

It is also possible to insert multiple rows in one statement. To insert multiple rows of data, we
use the same INSERT INTO statement, but with multiple values:

INSERT INTO Customers (CustomerName, ContactName, Address, City,

PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

34
 MODULE II
21CS53 Database Management Systems

21', 'Stavanger', '4006', 'Norway'),

('Greasy Burger', 'Per Olsen', 'Gateveien
15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1
0AA', 'UK');

How to Test for NULL Values

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We
will have to use the IS NULL and IS NOT NULL operators instead.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL
This will display all the records where there is no null values

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
SELECT CustomerName, ContactName, Address FROM Customers
WHERE Address IS NULL;
Since there are no null values in the table on these columns the result is empty.

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values). The
following SQL lists all customers with a value in the "Address" field. (Since none of the records
are empty – the count is shown as 91)

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

35
 MODULE II
21CS53 Database Management Systems

SELECT CustomerName, ContactName, Address

FROM CustomersWHERE Address IS NOT NULL;

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Demo Database
Below is a selection from the Customers table used in the examples:

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

36
 MODULE II
21CS53 Database Management Systems

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the ContactName to "Juan" for all records where
country is "Mexico":

UPDATE Customers
SET ContactName='Juan WHERE Country='Mexico';

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

37
 MODULE II
21CS53 Database Management Systems

The following SQL statement deletes the customer "Alfreds Futterkiste" from
the "Customers" table:

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

38
 MODULE II
21CS53 Database Management Systems

It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact

DELETE FROM table_name;

DELETE FROM Customers;

Delete a Table
To delete the table completely, use the DROP TABLE statement:

Remove the Customers table:

DROP TABLE Customers;

The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers"
table.

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

39
 MODULE II
21CS53 Database Management Systems

Dr. Ranjini. P. S, [Link]., M. Phil, Ph. D, M. Tech in Data Science & Machine Learning,
Professor, Department of Artificial Intelligence & Data Science,
Don Bosco Institute of Bangalore.

40