DATABASE CONCEPTS
DATABASE
A database is a collection of
interrelated data DBMS(Database
Management System) is a collection
of software that enables you to store,
modify and extract information from a
database. Eg; Railway reservation
systems, Computerized library
systems
Advantages of
Database systems
Reduced data
redundancy(Duplication of data)
Controlled data inconsistency Shared
data Secured data Integrated data
Different users who
work on databse
systems
End User:A person who is not necessarily a
computer
expert but uses the database to retrieve
information. Eg; A customer who checks the
train details in a railway reservation system.
Application System Analyst: A person who
is concerned about all of the database of logical
level. i.e., what all data constitutes the
database, what are the relationship between the
data items, etc.
Physical Storage System Analyst: A
person who is concerned with the physical
implementation details of the database such as
which storage device, which storage technique
should be used etc.
Database
Abstraction
Internal Level (Physical Level) It
describes how the data is actually stored on
the storage medium.
Conceptual Level It describes what data
is stored in the database and the
relationships among them.
External Level(View Level) It is
concerned with the way in which the data
are viewed by end users.
Data Independence
The ability to modify one level with out
affecting the higher level scheme
definition.
Physical Data Independence The
ability to modify the physical level with
out affecting the conceptual level
Logical Data Independence The
ability to modify the conceptual level
with out affecting the view level
DATA MODEL A data model
organizes data elements and describes how they
related to one another.
Relational Data Model In Relational Data model,
data is organized into tables(i.e., rows and columns).
These tables are called relations.
Network Model In this model, data is
represented by collection of records that are
organized as graphs and relationship among them
are represented by links.
Hierarchical Model In this model, data is
represented by collection of records that are
organized as trees and relationship among them are
represented by links.
Relational Data
model
Relation : In Relational Data model, data is organized
into tables(i.e., rows and columns). These tables are
called relations.
Tuple: The rows of relation are called tuples.
Attributes: The columns of relation are called
attributes.
Degree: The number of attributes in a relation.
Cardinality: The number of rows in a relation.
Domain: The set of possible values that can be used
as a column value
View: A view is a temporary table which is derived from
other tables depending upon a condition.
Keys
Primary Key A primary key is an
attribute or the combination of
multiple attributes that can uniquely
identify each tuple/record in a table.
Table Employee Primary Key- Empid
EmpId EmpName Designation Salary 101 Ravi Manager
18000 102 Athul Accountant 12000 103 Preetha Clerk
10000
Candidate Key A candidate key is
the one that is capable of becoming a
primary key. i.e., a field or attribute that
has unique value for each row in the
relation. Candidate keys are the
candidates for the primary key position.
Table Student Candidate Keys-
AdmissionNo, Rollno
Rollno AdmissionN0 Name Class Marks 18 3065 Sneha
XII 465 9 2367 Arun XII 436 32 4219 Deepak XI 428
Alternate Key A candidate key that
is not the primary key is called
Alternate key. In the previous example,
if the attribute Rollno is
selected as the primary key then
AdmissionNo can be considered as
Alternate key.
Foreign Key A non-key attribute of a
table, whose values are derived
from the primary key of some other
table, is known as foreign-key of that
table. A Foreign key is used to represent
the relationship
between two tables.
Table Student Primary Key: Rollno
AdmissionN0 Rollno Name Class Marks 3065 18 Sneha
XII 465 2367 9 Arun XII 436 4219 32 Deepak XI 428
Table Library Primary Key: LibNo
LibN0 Rollno BookName IssuedDate
L13 9 Physics 10-02-2015
L23 32 Chemistry 11-02-2015
L33 8 Computer 12-02-2015
The table Library has a field Rollno
which the primary
key of the table Student. Hence Rollno
can be termed as Foreign key.
Relational Algebra
The Relational Algebra is a collection of
operations on
relations. The different operations are:
SELECT Operation
The Select operation selects tuples
from a relation that satisfy a given
condition. It is denoted by the symbol σ
(sigma). To select those tuples from
Students table whose marks are
less than 450, we write σ Marks<450
(Student)
which would produce the following output
Rollno AdmissionN0 Name Class Marks 9 2367 Arun
XII 436 32 4219 Deepak XI 428
PROJECT Operation
The Project operation selects
columns or attributes from a relation that
satisfy a given condition. It is denoted by
the symbol π (pi).
To select Name and Class columns from
Students table, we
produce the
write π Name,
following output
Name Class Sneha XII
Arun XII Deepak XI
Class (Student)
which would
To select Name and Marks columns
from Students
table whose marks are less than 450,
we write π Name, Marks
(σ Marks<450 (Student))
which would produce the following
output
Name Marks Arun 436 Deepak 428
CARTESIAN
PRODUCT Operation
The Cartesian Product of two tables
yields a table with
all possible combinations of the tuples of
the two tables operated upon. i.e.,it
concatenates all the tuples of the first
table with that of the second. The
number of columns of the resultant table
is the sum
of the degees of two relation and the
number of rows is the product of the
cardinality of the two relations. It is
denoted by the symbol X (cross)
Consider the example,
Student Instructor
StudNo StudName
Hosteler
Student X
S001 Meenakshi Y
Instructor
InstNo InstName Subject
S002 Radhika N
101 Lal English
S003 Abhinav N
102 Arora Maths
StudNo StudName Hosteler InstNo InstName Subject
S001 Meenakshi Y 101 Lal English S001 Meenakshi Y
102 Arora Maths S002 Radhika N 101 Lal English S002
Radhika N 102 Arora Maths S003 Abhinav N 101 Lal
English S003 Abhinav N 102 Arora Maths
UNION Operation
Union operation is a binary operation
that operates on
two tables which produces a third table
that contains the tuples from both the
tables. It is denoted by U.
For a union operation AUB to be valid, the
following two
conditions must satisfy: 1) The tables A and
B must be of the same degree. 2) The
domains of the ith attribute of A and the
ith attribute
of B must be the same.
Consider the example, Table STOCK
Table SALES
ItemNo Product Price
ItemNo Product Price
16 Mobile 8000
23 Fridge 12000
17 Oven 13500
27 TV 10000
19 Geyzer 14750
28 Laptop 30000
STOCK U SALES
ItemNo Product Price 23 Fridge 12000 27 TV 10000 28
Laptop 30000 16 Mobile 8000 17 Oven 13500 19 Geyzer
14750
SET
INTERSECTION
OPERATION
Set Intersection operation is a binary
operation that
operates on two tables which produces
a third table that contains the tuples that
are common to both the tables. It is
denoted by n.
Consider the example, Table STOCK
Table SALES
SALES
ItemNo Product Price
ItemNo Product Price
23 Fridge 12000
16 Mobile 8000
27 TV 10000
7 Oven 13500
28 Laptop 30000
19 Geyzer 14750
23 Fridge 12000
STOCK n
ItemNo Product Price 23 Fridge 12000
SET DIFFERENCE
OPERATION
The Set Difference Operation, denoted
by – (minus)
find tuples that are in one table but not in
another. A-B results in a table containing
those tuples in A but not in
B.
Consider the example, Table STOCK
Table SALES
ItemNo Product Price
ItemNo Product Price
16 Mobile 8000
23 Fridge 12000
7 Oven 13500
27 TV 10000
19 Geyzer 14750
28 Laptop 30000
23 Fridge 12000
STOCK - SALES
ItemNo Product Price 27 TV 10000 28 Laptop 30000
Functional
Dependency
A functional dependency occurs when
one attribute in a relation uniquely
determines another attribute. This can
be written as A->B which states B is
functionally dependent on A. Eg: In a
table listing employee characteristics
including SSN (Social Security Number)
and Name, it can be said that name is
functionally dependent upon SSN. i.e.,
SSN -> Name
SSN Name Salary S0001 Anish Misra 10000 S0002
Rahul Gupta 12000
