NAVODAYA VIDYALAYA SAMITI, NOIDA

E-CONTENT PREPARATION FOR

CLASS XI
CHAPTER- 7

DATABASE CONCEPTS

PREPARED BY:
1
ASHOK KUMAR SHARMA, PGT CS
JNV SHIMOGA, KARNATAKA
DATABASE

? A database is a collection of interrelated

data.
? It can serve multiple applications.

? It is computer based recordkeeping system.

? It not only allows to store but also allows us

modification of data as per requirements.

2
FILE SYSTEM

? A file can be understood as a container to

store data in a computer. Files can be stored
on the storage device of a computer system.
Contents of a file can be texts, computer
program code, comma separated values
(CSV), etc. Likewise, pictures, audios/videos,
web pages are also files.
? Files stored on a computer can be accessed
directly and searched for desired data.
? But one has to write computer programs for
efficient access of data from files. 3
DBMS

? DBMS refers to Database Management

System
? It is a software that can be used for creating,
storing, manipulating, maintaining and
retrieving data from database by users or
application programs.
? The DBMS serves as an interface between
the database and end users or application
programs.
? Some examples of open source and
commercial DBMS include MySQL, Oracle, 4
PostgreSQL, SQL Server, Microsoft Access,
MongoDB.
NEED OF DBMS
File system becomes difficult to handle when
number of files increases and volume of data
also grows. Following are some of the
advantages of database:
? Database reduces redundancy
Redundancy means same data are duplicated in
different places (files). It removes redundancy as
the data are stored at one place and all the
application refers to the centrally maintained
database.
? Database controls inconsistency
Data inconsistency occurs when same data
maintained in different places do not match. By
controlling redundancy, the inconsistency is also
controlled.
? Database facilitates sharing of data
5
Data stored in the database can be shared
among several users.
NEED OF DBMS
? Database ensures security
Data are protected against accidental or
intentional disclosure to unauthorized person
or unauthorized modification.
? Database maintains integrity
It enforces certain integrity rules to ensure
the validity or correctness of data.
? Database enforces standard
Database is maintained in a standard format
which helps data interchange or migration of
data between two systems.
6
KEY CONCEPTS IN DBMS
? Database Schema
▪ Database Schema is the design of a database.
▪ Database schema is also called the visual or
logical architecture as it tells us how the data are
organised in a database.
? Data Constraint
▪ It is certain restrictions or limitations on data to
ensure its validity and consistency.
? Meta-data or Data Dictionary
▪ The database schema along with various
constraints on the data is stored by DBMS in a
database catalogue or dictionary, called meta- 7
data.
▪ A meta-data is data about the data.
KEY CONCEPTS IN DBMS
? Database Instance
The state or snapshot of the database at any
given time is the database instance
? Database Query
A query is a request to a database for obtaining
information in a desired way.
? Data Manipulation
▪ Modification of database
▪ It consists of three operations viz. Insertion,
Deletion or Updating.
? Database Engine
▪ Database engine is the underlying component or
set of programs used by a DBMS 8
RELATIONAL DATA MODEL

? In a relational database model, data is

organized into tables (i.e. rows and columns).
? These tables are also known as relations.
? A row in a table represents relationship
among a set of values.
? A column represents the field/attributes
related to relation under which information
will be stored.
? For example: If we want to store the details
of students then Roll_No, Class, Section etc.
will be the column/attributes and the 9
collection of all the column data will become
a Row/Attribute.
COMPONENTS COMPRISING A
TABLE

? Data Item: smallest unit of named data. It

represent one type of information and often
referred to as a field or column information

? Record: collection of data items which

represent a complete unit of information

? Table: collection of all Rows and Columns.

10
COMMONLY USED TERMINOLOGIES
IN RELATIONAL DATA MODEL
The Relational Model was developed by E. F.
Codd of the IBM in 1970 and is the most widely
used Database Model.

? Relation / Table
A Relation/Table is a tabular structure arranged
in rows and columns. It has the following
properties:
▪ All items in a column are homogeneous i.e.
they are of the same data type
▪ Each column is assigned a unique name and
has an atomic(indivisible) value.
11
COMMONLY USED TERMINOLOGIES IN
RELATIONAL DATA MODEL

▪ No attribute can have many data values in

one tuple .
▪ All rows of a relation are distinct i.e. no two
identical rows are present in a relation.
▪ Ordering of rows or columns are immaterial.
▪ A special value “NULL” is used to represent
values that are unknown or non-applicable to
certain attributes.

12
COMMONLY USED TERMINOLOGIES
IN RELATIONAL DATA MODEL
? Domain
It is a set of values from which an attribute can take a
value in each row. Usually, a data type is used to
specify domain for an attribute.
? Tuple / Entity / Record
Each row of data in a relation (table) is called a tuple.
? Attribute / Field
The columns of a relation are the attributes which
are also referred as fields.
? Degree
The number of attributes in a relation is called the
Degree of the relation
? Cardinality
The number of tuples in a relation is called the
Degree of the relation 13
DATABASE TABLE/RELATION

STUDENT Table/Relation
Name
Columns / Attributes

Relation
Table /
ROLL NAME CLASS
01 VIJETHA 12
02 SINDHU 11
Rows /
Tuples

03 GAUTHAMI 12

Records Data Item 14

KEYS IN A RELATIONAL DATABASE

The tuples within a relation must be distinct.

It means no two tuples in a table should have
same value for all attributes. That is, there
should be at least one attribute in which data
are distinct (unique) and not NULL. That way,
we can uniquely distinguish each tuple of a
relation. So, relational data model imposes
some restrictions or constraints on the values
of the attributes and how the contents of one
relation be referred through another relation.
These restrictions are specified through
different types of keys.
15
KEYS IN A RELATIONAL DATABASE
? Primary Key
▪ A set of one or more attribute(s) that can
uniquely identify a record in the relation is
called Primary Key.
▪ There can be only one primary key in a table.
▪ A primary key accepts only distinct (non-
duplicate) values and cannot be left blank.
▪ E.g. Student ID No, Student Admission No,
Aadhar Card No etc. qualifies as Primary Key
as the values of all these are unique.

16
KEYS IN A RELATIONAL DATABASE
? Candidate Key
▪ A table can have one or more attributes that
takes distinct values. Any of these attributes
can be used to uniquely identify the tuples in
the relation. Such attributes are called
candidate keys.
▪ A Primary Key is one of the candidate keys.
▪ A table may have more than one candidate
keys but definitely has one and only one
primary key.

17
KEYS IN A RELATIONAL DATABASE
? Candidate Key
Candidate Keys

STUDENT
Stud_ID Roll_No Name Stream Email_ID
200601 01 Nikitha Science [email protected]
200607 03 Srinidhi Commerce [email protected]
200647 18 Sahana Science [email protected]

? In the above given table Stud_ID, Roll_No,

Email_ID can uniquely identify a row in the table
and hence they are candidate keys.
18
KEYS IN A RELATIONAL DATABASE
? Composite Primary Key
▪ If no single attribute in a relation is able to
uniquely distinguish the tuples, then more than
one attribute are taken together as primary key.
Such primary key consisting of more than one
attribute is called Composite Primary key.
CLASS SECTION TOT_STRENGTH
X A 32
XII A 30
XII B 28

▪ Here a composite Primary Key (CLASS,SECTION)

can be formed together as either of these
column in isolation cannot be used to uniquely 19
identify each row in the table.
KEYS IN A RELATIONAL DATABASE
? Alternate Keys
▪ Only one of the Candidate keys is selected as
the primary key of a table. All other candidate
keys are called Alternate keys.
▪ A Candidate Key which is not a primary key is
an Alternate Key.
▪ Candidate Keys – Primary Key = Alternate
Key(s).
▪ E.g. In the table STUDENT mentioned in Page
No. 18, if Stud_ID is selected as the Primary
Key then Roll_No and Email_ID will become
the Alternate Keys. 20
KEYS IN A RELATIONAL DATABASE
? Foreign Key
▪ A foreign key is used to represent the
relationship between two tables.
▪ It is a non-key attribute whose value is derived
from the Primary key of another table.
▪ Foreign key column will check for the presence
of value in Primary key of another table, if
present then only entry will be allowed
otherwise data will be rejected.

21
KEYS IN A RELATIONAL DATABASE
▪ In some cases, foreign key can take NULL
value if it is not the part of primary key of the
foreign table.
▪ The table containing the foreign key is called
the child table, and the table containing the
candidate key is called the referenced or
parent table.

22
KEYS IN A RELATIONAL DATABASE
ORDERS
ORDER_ID ORDER_NO CUST_ID

1 1255 3 Child
2 1690 3 Table
3 1810 2
4 2210 1

CUSTOMER
CUST_ID NAME ADDRESS
Parent
1 HECTOR MADRID
Table
2 MITCHELLE LONDON
3 AUGUSTA COLARADO

▪ From the above given tables we can observe that the CUST_ID
column of ORDERS table is deriving its value from CUST_ID of
CUSTOMER table. So we can say that the CUST_ID of
ORDERS table is a foreign key whose value is dependent upon
the Primary key column CUST_ID of table CUSTOMER. 23
REFERENTIAL INTEGRITY
? The property of a relational database which
ensures that no entry in a foreign key column
of a table can be made unless it matches a
primary key value in the corresponding column
of the related table is called referential
integrity.
? It also ensures that the user don’t accidentally
delete or change the related data.
? Referential integrity can be applied when:
▪ The master table’s column is a Primary Key or
has a unique index.
▪ The related fields have the same data type. 24

▪ Both tables must belong to the same

REFERENTIAL INTEGRITY
? The following rules must be followed if the
referential integrity is enforced using Foreign
Key:

▪ We cannot enter a value in Child Table which is

not present in Master Table’s Primary key
column. However, NULL values can be entered
in foreign key.
▪ We cannot delete a record from Master Table if
matching record exists in related table/Child
Table.
▪ We cannot modify or change the Primary Key
value in Master table if its matching record is 25
present in related table/Child Table.
THANK YOU

26