Chap-11 Relational Databases

Class -12 C.S

What Is a Database?
A database is an organized collection of structured information, or data, typically stored electronically
in a computer system. A database is usually controlled by a database management system (DBMS).

What is Structured Query Language (SQL)?

SQL is a programming language used by nearly all relational databases to query, manipulate, and
define data, and to provide access control. SQL was first developed at IBM in the 1970s with Oracle

What is a MySQL database?

MySQL is an open source relational database management system based on SQL. It was designed and
optimized for web applications and can run on any platform. As new and different requirements
emerged with the internet, MySQL became the platform of choice for web developers and web-based
applications.

What is DBMS?
Database Management Systems (DBMS) are software systems used to store,
retrieve, and run queries on data. A DBMS serves as an interface between an end-
user and a database, allowing users to create, read, update, and delete data in the
database.

DBMS manage the data, the database engine, and the database schema, allowing
for data to be manipulated or extracted by users and other programs. This helps
provide data security, data integrity, concurrency, and uniform data administration
procedures.
 What is the purpose of Database Management System?
It is a collection of tools that enable users to create and manage databases. In
other words, it is general-purpose software that allows users to create,
manipulate, and design databases for a number of purposes.

Database systems are design to deal with large volumes of data. Data
management comprises both the construction of data storage systems and the
provision of data manipulation methods. Furthermore, the database system must
maintain the security of the information held despite system crashes or attempts
at unauthorized access. The system must avoid any unexpected effects if data is
to be shared across multiple users.

The database applications were built on top of the file system.

The goal of a database management system (DBMS) is to transform the

following:

1. Data into information.

2. Information into knowledge.

3. Knowledge of the action.

Characteristics of DBMS
 Firstly, It manages and stores information in a server-based digital repository.
 Secondly, It can logically and visibly represent the data transformation process.
 Automatic backup and recovery techniques are built into the database
management system.
 It has ACID features, which ensure that data is safe even if the system fails.
 It has the ability to make complex data connections more understandable.
 It’s utilise to help with data manipulation and processing.
 It is utilise to keep information safe.
 Lastly, It can examine the database from a variety of perspectives, depending on
the needs of the user.
Advantages of DBMS
Because information is so important in most organizations, computer scientists
have developed a large body of concepts and techniques for managing data. These
concepts and technique form the focus of this book.

 Data Independence.
 Efficient Data Access.
 Data Integrity and security.
 Data administration.
 Concurrent access and Crash recovery.
 Reduced Application Development Time.

Disadvantages Of DBMS
 Data redundancy and inconsistency.
 Difficult in accessing data.
 Data isolation.
 Data integrity.
 Concurrent access is not possible.
 Security Problems.
Application or Function of DBMS
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Online retailers: order tracking, customized recommendations
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
What is Relational Model?
Relational Model (RM) represents the database as a collection of relations. A
relation is nothing but a table of values. Every row in the table represents a
collection of related data values. These rows in the table denote a real-world entity
or relationship.

Relational Model Concepts in DBMS

1. Attribute: Each column in a Table. Attributes are the properties which
define a relation. e.g., Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format.
It is stored along with its entities. A table has two properties rows and
columns. Rows represent records and columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single
record.
4. Relation Schema: A relation schema represents the name of the relation
with its attributes.
5. Degree: The total number of attributes which in the relation is called the
degree of the relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS
system. Relation instances never have duplicate tuples.
9. Relation key – Every row has one, two or multiple attributes, which is
called relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope
which is known as attribute domain
Types Of Integrity Constraints In DBMS
1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints

Domain Constraints
Domain constraints can be violated if an attribute value is not appearing in the
corresponding domain or it is not of the appropriate data type.

Domain constraints specify that within each tuple, and the value of each attribute
must be unique. This is specified as data types which include standard data types
integers, real numbers, characters, Booleans, variable length strings, etc.

Example:

Create DOMAIN CustomerName

CHECK (value not NULL)

Key Constraints
An attribute that can uniquely identify a tuple in a relation is called the key of
the table. The value of the attribute for different tuples in the relation has to be
unique.

Example:

In the given table, CustomerID is a key attribute of Customer Table. It is most

likely to have a single key for one customer, CustomerID =1 is only for the
CustomerName =” Google”.

CustomerID CustomerName Status

1 Google Active
2 Amazon Active
3 Apple Inactive
Referential Integrity Constraints
Referential Integrity constraints in DBMS are based on the concept of Foreign
Keys. A foreign key is an important attribute of a relation which should be
referred to in other relationships. Referential integrity constraint state happens
where relation refers to a key attribute of a different or same relation. However,
that key element must exist in the table.

Example:

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:

Primary key
o It is the first key used to identify one and only one instance of an entity
uniquely. 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.C++
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.