Database Concepts - Notes for Class 12 (ASSEB / AHSEC)
Chapter 11 - Database Concepts
Contents
Introduction to Database Concepts
Today, almost every organization works with a large amount of information. Schools maintain student records, banks maintain account details, hospitals maintain patient records, and online stores keep information about products and customers.
Handling all this information using ordinary files can become difficult. This is where a database becomes useful.
In this chapter, we will learn what a database is, why it is needed, advantages of database, different database models, important terms used in the relational model, keys, and the basics of relational algebra
What is a Database?
A database is an organized collection of related data that can be easily stored, accessed, managed and updated.
In simple words, a database is a place where related information is stored in an organized manner.
For example A school may maintain a database containing:
Here, all the information is related to students, so it forms a student database.
Why Do We Need a Database?
Consider a school having 2,000 students. If all student information is stored in separate paper files, finding the details of one student could take a lot of time.
A database solves this problem and makes such work much easier.
Main reasons for using a database
- To store a large amount of data
- To find information quickly
- To add new records easily
- To modify existing information
- To delete unwanted information
- To reduce unnecessary duplication of data
- To keep data organized
- To allow multiple users to access data
Advantages of a Database
A database provides several advantages over maintaining information using ordinary files.
- Easy access to data
Required information can be retrieved quickly.
- Reduces data redundancy
Unnecessary repetition of the same data can be reduced.
- Better data security
Access to sensitive information can be restricted to authorized users.
- Easy updating
Existing information can be changed without maintaining several separate copies.
- Data sharing
Multiple users can access the database according to their permissions.
- Better organization
Large amounts of information can be arranged systematically.
- Data consistency
Proper database design helps ensure that the same information does not have conflicting values in different places.
What is DBMS?
DBMS stands for Database Management System.
A DBMS is software used to create, store, organize, retrieve, update and manage data in a database.
What is RDBMS?
RDBMS stands for Relational Database Management System. It is a software used to create and manipulate a relational database. A relational database is a database that stores data in the form of rows and columns, i.e, table and creates links between the tables called relationships.
Examples of RDBMS are:
MySQL, Microsoft SQL Server,Oracle Database, etc.
What is a Data Model?
A data model is a collection of concepts used to describe the structure of a database, the relationships among data and the constraints on the data.
In simple words, a data model tells us how data is organized and how different pieces of data are related to one another.
Different data models organize information in different ways.
Some important data models are:
- Relational model
- Hierarchical model
- Network model
Relational Data Model
- Rows
- Columns
Hierarchical Data Model
The hierarchical data model organizes data in the form of a tree-like structure.
It follows a parent-child relationship. A parent can have multiple children, but each child generally has only one parent.
Network Data Model
The network data model organizes data as a network of records connected through relationships.Unlike the hierarchical model, a record can have relationships with multiple other records.This allows more complex relationships.
Example
Important Terms in the Relational Model
Before working with relational databases, we need to understand some important terms.
These include:
- Relation
- Attribute
- Tuple
- Domain
- Degree
- Cardinality
- View
- Key
Relation
A relation is a table consisting of rows and columns.
For example:
RollNo | Name | Marks |
101 | Rohan | 85 |
102 | Priya | 91 |
103 | Aman | 78 |
This table can be called the STUDENT relation.
In simple terms:
Relation = Table
Attribute
An attribute is a column of a relation that represents a particular property of an entity.
For example:
RollNo | Name | Marks |
Here:
- RollNo is an attribute.
- Name is an attribute.
- Marks is an attribute.
So, an attribute is basically a column.
Tuple
A tuple is a row or record in a relation.
For example:
RollNo | Name | Marks |
101 | Rohan | 85 |
102 | Priya | 91 |
The following is one tuple:
101, Rohan, 85
Therefore:
Tuple = Row / Record
Domain
A domain is the set of permitted values that an attribute can have.
For example, consider:
Marks
Its domain could be:
0 to 100
Similarly, the domain of Gender might contain values such as:
Male, Female, Other
The domain tells us what values are allowed for an attribute
Degree
The degree of a relation is the number of attributes (columns) in the relation.
Consider:
RollNo | Name | Class | Marks |
101 | Rohan | 12 | 85 |
102 | Priya | 12 | 91 |
There are four columns: RollNo, Name, Class, Marks
Therefore:
Degree = 4
Remember
Degree = Number of columns
Cardinality
The cardinality of a relation is the number of tuples (rows) in the relation.
In the previous example:
RollNo | Name | Class | Marks |
101 | Rohan | 12 | 85 |
102 | Priya | 12 | 91 |
There are 2 rows.
Therefore:
Cardinality = 2
Easy trick
Degree → Columns
Cardinality → Rows
What is a Key?
A key is an attribute or a group of attributes used to uniquely identify a record in a relation or to establish a relationship between tables.
Keys are extremely important in relational databases because they help identify records and connect related tables.
Types of keys are:
- Primary key
- Candidate key
- Alternate key
- Foreign key
Primary Key
A primary key is an attribute or combination of attributes that uniquely identifies each tuple in a relation.
A primary key:
- Must contain unique values.
- Cannot contain duplicate values.
- Cannot normally contain NULL values.
Example
Consider:
RollNo | Name | Class |
101 | Rohan | 12 |
102 | Priya | 12 |
103 | Aman | 12 |
RollNo can be used as the primary key because every student has a different roll number.
RollNo → Primary Key
Candidate Key
A candidate key is an attribute or set of attributes that can uniquely identify each record and is eligible to become the primary key.
There can be more than one candidate key in a table.
Example
Suppose an employee table contains:
EmployeeID | Name | |
E101 | Rohan | |
E102 | Priya |
If both EmployeeID and Email contain unique values, both can uniquely identify an employee.
Therefore:
Candidate Keys:
EmployeeID
One of them can be selected as the primary key.
Alternate Key
An alternate key is a candidate key that is not selected as the primary key.
Suppose:
Candidate Keys:
EmployeeID
If we choose:
EmployeeID → Primary Key
then:
Email → Alternate Key
Easy way to remember
Candidate keys
↓
One selected as Primary Key
↓
Remaining candidate keys
↓
Alternate Keys
Foreign Key
A foreign key is an attribute or set of attributes in one table that refers to the primary key of another table.
Foreign keys are used to create relationships between tables.
Example
Suppose we have two tables.
STUDENT
RollNo | Name |
101 | Rohan |
102 | Priya |
103 | Aman |
MARKS
RollNo | Subject | Marks |
101 | CS | 85 |
102 | CS | 91 |
103 | CS | 78 |
Here:
STUDENT.RollNo → Primary Key
MARKS.RollNo → Foreign Key
The foreign key in MARKS refers to the primary key of STUDENT.
This allows the two tables to be related
What is Relational Algebra?
Relational algebra is a procedural query language used to perform operations on relations (tables).
It provides a collection of operations that can be used to retrieve and manipulate data from relational databases.
Different Relational Algebra operations
Some important relational algebra operations are:
- Selection
- Projection
- Cartesian Product
- Union
- Set Difference
- Set Intersection
- Join
- Natural Join
Selection Operation
The selection operation is used to select rows from a relation that satisfy a given condition.
It is represented by the Greek letter: σ (sigma)
Projection Operation
The projection operation is used to select particular columns from a relation.
It is represented by: π (pi)
Cartesian Product
The Cartesian product combines every tuple of one relation with every tuple of another relation.
It is represented by: ×
Union
The union operation combines the tuples of two relations.
It is represented by: ∪
For union, the two relations must be union compatible. In simple terms, they should have the same number of attributes with compatible domains.
Set Difference
The set difference operation returns tuples that are present in the first relation but not in the second relation.
It is represented by: −
Set Intersection
The intersection operation returns the tuples that are common to both relations.
It is represented by: ∩
Join Operation
A join operation combines related tuples from two relations based on a specified condition.
Joins are especially useful when information is stored in different tables
Important Board Examination Points
- A database is an organized collection of related data
- A DBMS is software used to create, store, organize, retrieve, update and manage data in a database.
- A data model is a collection of concepts used to describe the structure of a database, the relationships among data and the constraints on the data.
- A key is an attribute or a group of attributes used to uniquely identify a record in a relation or to establish a relationship between tables.
- Relational algebra is a procedural query language used to perform operations on relations (tables).