DBMS & SQL NOTES
Database: A database is a collection of related data which represents some aspect of the real
world. A database system is designed to be built and populated with data for a certain task.
Database Management System (DBMS) is a software for storing and retrieving users' data while
considering appropriate security measures. It consists of a group of programs which manipulate
the database. The DBMS accepts the request for data from an application and instructs the
operating system to provide the specific data. In large systems, a DBMS helps users and other
third-party software to store and retrieve data.
Database management systems were developed to handle the following difficulties of
typical File-processing systems supported by conventional operating systems.
1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation – multiple files and formats
4. Integrity problems
5. Atomicity of updates
6. Concurrent access by multiple users
7. Security problems
ER diagram:
● ER diagram or Entity Relationship diagram is a conceptual model that gives the
graphical representation of the logical structure of the database.
● It shows all the constraints and relationships that exist among the different components.
● An ER diagram is mainly composed of following three components- Entity Sets,
Attributes and Relationship Set.
● Roll_no is a primary key that can identify each entity uniquely.
, ● Thus, by using a student's roll number, a student can be identified uniquely.
Entity Set:
An entity set is a set of the same type of entities.
● Strong Entity Set:
A strong entity set is an entity set that contains sufficient attributes to uniquely
o
identify all its entities.
o In other words, a primary key exists for a strong entity set.
o Primary key of a strong entity set is represented by underlining it.
● Weak Entity Set:
o A weak entity set is an entity set that does not contain sufficient attributes to
uniquely identify its entities.
o In other words, a primary key does not exist for a weak entity set.
o However, it contains a partial key called a discriminator.
o Discriminator can identify a group of entities from the entity set.
o Discriminator is represented by underlining with a dashed line.
Relationship:
A relationship is defined as an association among several entities.
● Unary Relationship Set - Unary relationship set is a relationship set where only one
entity set participates in a relationship set.
● Binary Relationship Set - Binary relationship set is a relationship set where two entity
sets participate in a relationship set.
● Ternary Relationship Set - Ternary relationship set is a relationship set where three
entity sets participate in a relationship set.
● N-ary Relationship Set - N-ary relationship set is a relationship set where ‘n’ entity sets
participate in a relationship set.
Cardinality Constraint:
Cardinality constraint defines the maximum number of relationship instances in which an entity
can participate.
● One-to-One Cardinality - An entity in set A can be associated with at most one entity in
set B. An entity in set B can be associated with at most one entity in set A.
● One-to-Many Cardinality - An entity in set A can be associated with any number (zero or
, more) of entities in set B. An entity in set B can be associated with at most one entity in
set A.
● Many-to-One Cardinality - An entity in set A can be associated with at most one entity in
set B. An entity in set B can be associated with any number of entities in set A. ●
Many-to-Many Cardinality - An entity in set A can be associated with any number (zero or
more) of entities in set B. An entity in set B can be associated with any number (zero or
more) of entities in set A.
Attributes:
Attributes are the descriptive properties which are owned by each entity of an Entity Set.
Types of Attributes:
● Simple Attributes - Simple attributes are those attributes which cannot be divided
further. Ex. Age
● Composite Attributes - Composite attributes are those attributes which are composed
of many other simple attributes. Ex. Name, Address
● Multi Valued Attributes - Multi valued attributes are those attributes which can take
more than one value for a given entity from an entity set. Ex. Mobile No, Email ID ●
Derived Attributes - Derived attributes are those attributes which can be derived from
other attribute(s). Ex. Age can be derived from DOB.
● Key Attributes - Key attributes are those attributes which can identify an entity uniquely
in an entity set. Ex. Roll No.
Constraints:
Relational constraints are the restrictions imposed on the database contents and
operations. They ensure the correctness of data in the database.
● Domain Constraint - Domain constraint defines the domain or set of values for an
attribute. It specifies that the value taken by the attribute must be the atomic value from
its domain.
● Tuple Uniqueness Constraint - Tuple Uniqueness constraint specifies that all the tuples
must be necessarily unique in any relation.
● Key Constraint - All the values of the primary key must be unique. The value of the
primary key must not be null.
● Entity Integrity Constraint - Entity integrity constraint specifies that no attribute of
primary key must contain a null value in any relation.
● Referential Integrity Constraint - It specifies that all the values taken by the foreign key
Database: A database is a collection of related data which represents some aspect of the real
world. A database system is designed to be built and populated with data for a certain task.
Database Management System (DBMS) is a software for storing and retrieving users' data while
considering appropriate security measures. It consists of a group of programs which manipulate
the database. The DBMS accepts the request for data from an application and instructs the
operating system to provide the specific data. In large systems, a DBMS helps users and other
third-party software to store and retrieve data.
Database management systems were developed to handle the following difficulties of
typical File-processing systems supported by conventional operating systems.
1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation – multiple files and formats
4. Integrity problems
5. Atomicity of updates
6. Concurrent access by multiple users
7. Security problems
ER diagram:
● ER diagram or Entity Relationship diagram is a conceptual model that gives the
graphical representation of the logical structure of the database.
● It shows all the constraints and relationships that exist among the different components.
● An ER diagram is mainly composed of following three components- Entity Sets,
Attributes and Relationship Set.
● Roll_no is a primary key that can identify each entity uniquely.
, ● Thus, by using a student's roll number, a student can be identified uniquely.
Entity Set:
An entity set is a set of the same type of entities.
● Strong Entity Set:
A strong entity set is an entity set that contains sufficient attributes to uniquely
o
identify all its entities.
o In other words, a primary key exists for a strong entity set.
o Primary key of a strong entity set is represented by underlining it.
● Weak Entity Set:
o A weak entity set is an entity set that does not contain sufficient attributes to
uniquely identify its entities.
o In other words, a primary key does not exist for a weak entity set.
o However, it contains a partial key called a discriminator.
o Discriminator can identify a group of entities from the entity set.
o Discriminator is represented by underlining with a dashed line.
Relationship:
A relationship is defined as an association among several entities.
● Unary Relationship Set - Unary relationship set is a relationship set where only one
entity set participates in a relationship set.
● Binary Relationship Set - Binary relationship set is a relationship set where two entity
sets participate in a relationship set.
● Ternary Relationship Set - Ternary relationship set is a relationship set where three
entity sets participate in a relationship set.
● N-ary Relationship Set - N-ary relationship set is a relationship set where ‘n’ entity sets
participate in a relationship set.
Cardinality Constraint:
Cardinality constraint defines the maximum number of relationship instances in which an entity
can participate.
● One-to-One Cardinality - An entity in set A can be associated with at most one entity in
set B. An entity in set B can be associated with at most one entity in set A.
● One-to-Many Cardinality - An entity in set A can be associated with any number (zero or
, more) of entities in set B. An entity in set B can be associated with at most one entity in
set A.
● Many-to-One Cardinality - An entity in set A can be associated with at most one entity in
set B. An entity in set B can be associated with any number of entities in set A. ●
Many-to-Many Cardinality - An entity in set A can be associated with any number (zero or
more) of entities in set B. An entity in set B can be associated with any number (zero or
more) of entities in set A.
Attributes:
Attributes are the descriptive properties which are owned by each entity of an Entity Set.
Types of Attributes:
● Simple Attributes - Simple attributes are those attributes which cannot be divided
further. Ex. Age
● Composite Attributes - Composite attributes are those attributes which are composed
of many other simple attributes. Ex. Name, Address
● Multi Valued Attributes - Multi valued attributes are those attributes which can take
more than one value for a given entity from an entity set. Ex. Mobile No, Email ID ●
Derived Attributes - Derived attributes are those attributes which can be derived from
other attribute(s). Ex. Age can be derived from DOB.
● Key Attributes - Key attributes are those attributes which can identify an entity uniquely
in an entity set. Ex. Roll No.
Constraints:
Relational constraints are the restrictions imposed on the database contents and
operations. They ensure the correctness of data in the database.
● Domain Constraint - Domain constraint defines the domain or set of values for an
attribute. It specifies that the value taken by the attribute must be the atomic value from
its domain.
● Tuple Uniqueness Constraint - Tuple Uniqueness constraint specifies that all the tuples
must be necessarily unique in any relation.
● Key Constraint - All the values of the primary key must be unique. The value of the
primary key must not be null.
● Entity Integrity Constraint - Entity integrity constraint specifies that no attribute of
primary key must contain a null value in any relation.
● Referential Integrity Constraint - It specifies that all the values taken by the foreign key
