RDBMS
UNIT 1
What is the Relational Model?
The relational model for database management is an approach to
logically represent and manage the data stored in a database. In
this model, the data is organized into a collection of two-dimensional
inter-related tables, also known as relations. Each relation is a collection
of columns and rows, where the column represents the attributes of an
entity and the rows (or tuples) represent the records.
The use of tables to store the data provided a straightforward, efficient,
and flexible way to store and access structured information. Because of
this simplicity, this data model provides easy data sorting and data
access. Hence, it is used widely around the world for data storage and
processing.
Let's look at a scenario to understand the relational model:
Consider a case where you wish to store the name, the CGPA attained,
and the roll number of all the students of a particular class. This
structured data can be easily stored in a table as described below:
Relational Model Example
As we can notice from the above relation:
Any given row of the relation indicates a student i.e., the row of the table
describes a real-world entity.
The columns of the table indicate the attributes related to the entity. In
this case, the roll number, CGPA, and the name of the student.
Relational Model Concepts
As discussed earlier, a relational database is based on the relational
model. This database consists of various components based on the
relational model. These include:
Relation: Two-dimensional table used to store a collection of data
elements.
Tuple: Row of the relation, depicting a real-world entity.
Attribute/Field: Column of the relation, depicting properties that
define the relation.
Attribute Domain: Set of pre-defined atomic values that an attribute
can take i.e., it describes the legal values that an attribute can take.
Degree: It is the total number of attributes present in the relation.
Cardinality: It specifies the number of entities involved in the relation
i.e., it is the total number of rows present in the relation. Read more
about Cardinality in DBMS.
Relational Schema: It is the logical blueprint of the relation i.e., it
describes the design and the structure of the relation. It contains the
table name, its attributes, and their types:
TABLE_NAME(ATTRIBUTE_1 TYPE_1, ATTRIBUTE_2 TYPE_2, ...)
For our Student relation example, the relational schema will be:
STUDENT(ROLL_NUMBER INTEGER, NAME VARCHAR(20), CGPA
FLOAT)
Relational Instance: It is the collection of records present in the
relation at a given time.
Relation Key: It is an attribute or a group of attributes that can be
used to uniquely identify an entity in a table or to determine the
relationship between two tables. Relation keys can be of 6 different
types:
Candidate Key
Super Key
Composite Key
Primary Key
Alternate Key
Foreign Key
Highlights:
A Relation is a collection of rows (tuples) and columns (attributes).
In a relation, the tuples depict real-world entities, while the attributes
are the properties that define the relation.
The structure of the relation is described by the relational schema.
Relational keys are used to uniquely identify a row in a table or to
determine the relationship between two tables.
Keys
o Keys play an important role in the relational database.
o 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, ID is used as a key in the Student table because it is
unique for each student. In the PERSON table, passport_number,
license_number, SSN are keys since they are unique for each person.
Types of keys:
1. Primary key
o It is the first key used to identify one and only one instance of an
entity uniquely.
o An entity can contain multiple keys, as we saw in the PERSON
table. The key which is most suitable from those lists becomes a
primary key.
o In the EMPLOYEE table, ID can be the primary key since it is
unique for each employee. In the EMPLOYEE table, we can even
select License_Number and Passport_Number as primary keys
since they are also unique.
o For each entity, the primary key selection is based on requirements
and developers.
2. Candidate key
o A candidate key is an attribute or set of attributes that can
uniquely identify a tuple.
o Except for the primary key, the remaining attributes are considered
a candidate key. The candidate keys are as strong as the primary
key.
For example: In the EMPLOYEE table, id is best suited for the primary
key. The rest of the attributes, like SSN, Passport_Number,
License_Number, etc., are considered a candidate key.
3. Super Key
Super key is an attribute set that can uniquely identify a tuple. A super
key is a superset of a candidate key.
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
o Foreign keys are the column of the table used to point to the
primary key of another table.
o Every employee works in a specific department in a company, and
employee and department are two different entities. So, we can't
store the department's information in the employee table. That's
why we link these two tables through the primary key of one table.
o We add the primary key of the DEPARTMENT table,
Department_Id, as a new attribute in the EMPLOYEE table.
o In the EMPLOYEE table, Department_Id is the foreign key, and
both the tables are related.
5. Alternate key
There may be one or more attributes or a combination of attributes that
uniquely identify each tuple in a relation. These attributes or
combinations of the attributes are called the candidate keys. One key is
chosen as the primary key from these candidate keys, and the remaining
candidate key, if it exists, is termed the alternate key. In other
words, the total number of the alternate keys is the total number of
candidate keys minus the primary key. The alternate key may or may not
exist. If there is only one candidate key in a relation, it does not have an
alternate key.
For example, employee relation has two attributes, Employee_Id and
PAN_No, that act as candidate keys. In this relation, Employee_Id is
chosen as the primary key, so the other candidate key, PAN_No, acts as
the Alternate key.
6. Composite key
Whenever a primary key consists of more than one attribute, it is known
as a composite key. This key is also known as Concatenated Key.
For example, in employee relations, we assume that an employee may
be assigned multiple roles, and an employee may work on multiple
projects simultaneously. So the primary key will be composed of all three
attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So
these attributes act as a composite key since the primary key comprises
more than one attribute.
7. Artificial key
The key created using arbitrarily assigned data are known as artificial
keys. These keys are created when a primary key is large and complex
and has no relationship with many other relations. The data values of the
artificial keys are usually numbered in a serial order.
For example, the primary key, which is composed of Emp_ID, Emp_role,
and Proj_ID, is large in employee relations. So it would be better to add a
new virtual attribute to identify each tuple in the relation uniquely.
INTEGRITY RULES IN DBMS
DBMS systems, integrity rules, which take a prime place, are
designed to ensure that the quality of data is always
high, with no inconsistencies or errors . The set of principles,
also known as the integrity rules or constraints, helps to manage the data
stored in the system in the right way and determine whether it’s suitable
for certain purposes.
What are Integrity Rules?
Integrity normally represent the pre-existing
rules
conditions, or constraints, of data stored in the database
that will ensure the data is valid and consistent. These
rules, defining permissible values, relationships, and
data operations enclosed in the database system, ensure
the accuracy and reliability of the data to be used for its
purposes. Rules for integrity are considered barriers towards the
possibility of some operations over the database, such as deletion,
modification, or insertion of data whose accuracy is doubtful or
inconsistent, thus enabling the database to be of high quality.
Types of Integrity Rules
Entity Integrity: Also called business key integrity, this rule
proclaims that each row in a table must contain some unique data,
which is known as the business key. Through this verification,
a primary key
What is a Primary Key?
A primary key in a table that uniquely identifies each row
and column or set of columns in the table. The primary key
is an attribute or a set of attributes that help to uniquely
identify the tuples(records) in the relational table.
will be ensured of non-duplication, which also implies that no null
values are allowed in the primary key column, thus uniquely
identifying each instance of an entity in the table.
Referential Integrity: Referential integrity guarantees the
consistent pass of key reference constraints between data through
main table and foreign keys.
Example: STUD_NO, as well as STUD_PHONE both, are candidate
keys for relation STUDENT but STUD_NO can be chosen as the
primary key (only one out of many candidate keys).
This rule means that for every foreign key value in a child table,
that value has to possess the same primary key value in the parent
table. This is the only way to maintain foreign keys and make father
child relationships work properly.
Implementation of Integrity Rules
Integrity rules are typically implemented through a combination of
mechanisms within the DBMS, including:
Constraints: Constraints including a primary key constraint, a
foreign key constraint, a unique constraint, and a check constraint
are to maintain the integrity rules at the schema level.
Triggers: Triggers are database elements that are activated on
the fly to conduct given activities (executed on insert, update, or
delete operations, for example) on specific database tables.
Invoking triggers provides leverage to impose tougher restrictions,
thus enabling the enforcement of more advanced integrity rules or
conducting validation operations, which are a more complex kind of
comparison beyond checks of constraints.
Stored Procedures: Stored procedures are precompiled sets
of SQL operations that specify business logic and data processing
operations, and they are stored in a database. They may need to be
applied to the forces of rule-following and data consistency tasks
during the events of transactions.