Unit -I

1.Database-System Application
Q:What are the application areas of database?

◦ Sales: For customer, product, and purchase information.

◦ Accounting: For payments, receipts, account balances, assets and other
accounting information.
◦ Human resources:For information about employees,salaries, payroll, taxes,
and benefits, and for generation of paychecks.
◦ Manufacturing: For management of the supply chain and for tracking
production of items in factories,inventories of items in warehouse , and orders for
items.
◦ Online retailers: For sales data noted above plus online order tracking,
generation of recommendation lists, and maintenance of online product
evaluations.
• Banking and Finance
◦ Banking: For customer information, accounts, loans, and banking transactions.
◦ Credit card transactions: For purchases on credit cards and generation of
monthly statements.
◦ Finance: For storing information about holdings, sales, and purchases of
financial instruments such as stocks and bonds; also for storing real-time market
data.enable online trading by customers.
• Universities: For student information, course registrations, and grades (in
addition to standard enterprise information such as human resources and
accounting).
• Airlines: For reservationsand schedule information. Airlineswere among the first
to use databases in a geographically distributed manner.
• Telecommunication: For keeping records of calls made, generating monthly
bills, maintaining balances on prepaid calling cards, and storing information about
the communication networks.

2.Purpose of Database Systems

Q1:Explain the purpose of database management system

Q2:Describe the disadvantages of file processing system
File system has following disadvantages.
1.. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data Isolation
4. Integrity Problems
5. Atomicity Problems
6. Concurrent access anomalies
7. Security Problems
Data redundancy and inconsistency.
 Storing the information several time is called data redundancy.

1
  This leads to waste of storage space.

 For example both current and savings account may contain the
address of a customer. If we need to change any of the data, then we
need to change the data at all copies. If not, this will lead to inconsistency.

Difficulty in accessing data.

 In a file processing system, to access data differently we need to have
different programs.

 For example ,queries like “find the names of customers in a particular

postal-code” or “find all customers with bank balance >100000” requires
new programs to generate the answers required.

Data isolation.
Since all the datas are isolated from each other in different files, writing
new application programs to retrieve the appropriate data is difficult.
Integrity problems.
 The data values stored in the database must satisfy certain types of
consistency constraints.

 constraints like “bank balance for savings account should not fall below
Rs.1000”,”Account number must be not null” are difficult to implement
when new customers are added .

Atomicity problems.
 A computer system, like any other device, is subject to failure. In many
applications, it is crucial that, if a failure occurs, the data be restored to the
consistent state that existed prior to the failure.

 Consider a program to transfer Rs.500 from account A to the account B.

 If a system failure occurs during the execution of the program, it is

possible that Rs 500 was removed from Account A but was not credited
into Account B. This is an inconsistent database state.

 That is, the funds transfer must be happen in its entirety(fully) or not at all.
It is difficult to ensure atomicity in a conventional file-processing system.

Concurrent-access anomalies.
 Many systems allow multiple users to update the data simultaneously.

 concurrent update is possible and may result in inconsistent data.

 Consider account A has balance of Rs 10,000. If two department clerks

2
 debit the account balance (by say Rs 500 and Rs100, respectively) of
Account A almost exactly the same time, the result of the concurrent
execution of reading balance may leave the balance in an incorrect (or
inconsistent) state.

 Depending on which one writes the value last, the account balance of
department A may contain either Rs.9500 or Rs.9900, rather than the
correct value of Rs.9400.

Security problems.
 Not every user of the database system should be able to access all the
data.

 For example, in banking, payroll personnel needs to see employee details

not customer details

 Enforcing such security constraints is difficult.

View of Data
A database system is a collection of interrelated data and a set of programs that
allow users to access and modify these data.
A major purpose of a database system is to provide users with an abstract view
of the data. That is, the system hides certain details of how the data are stored
and maintained.

Data Abstraction
. The database system hides certain information of how the data are stored and
maintained.
To simplify the user interaction with the system, developers hides complexity in
different levels
Physical level.
The lowest level of abstraction describes how the data are actually stored. The
physical level describes complex low-level data structures in detail.
Logical level.
The next-higher level of abstraction describes what data are stored in the
database, and what relationships exist among those data.
The logical level describes the entire database in terms of simple structures.
The user of the logical level does not need to be aware of the complexity at
physical level.
This is referred to as physical data independence.
View level.
The highest level of abstraction describes only part of the entire database..
Many users of the database system do not need al this information; instead,

3
they need to access only a part of the database.
The view level of abstraction exists to simplify their interaction with the system.
The system may provide many views for the same database.
. For example, we may describe a record as follows:1
type instructor = record
ID :char (5);
name : char (20);
dept name : char (20);
salary : numeric (8,2);
end;
This code defines a new record type called instructor with four fields.Each field
has a name and a type associated with it.
At the physical level, an instructor, department, or student record can be de-
scribed as a block of consecutive storage locations.
At the logical level, each such record is described by a type definition,
At the view level, several views of the database are defined, and a database user
sees some or all of these views.
Instances and Schemas
 The collection of information stored in the database at a particular moment
is called an instance of the database.

 The overall design of the database is called the database schema.

Schemas are changed infrequently.

 Database systems have several schemas,

According to the levels of abstraction,

 The physical schema describes the database design at the physical level,
while the logical schema describes the database design at the logical
level.

 A database may also have several schemas at the view level, sometimes
called sub schemas, that describe different views of the database.

 logical schema -programmers construct applications by using the logical

schema.

.
Q:Explain the different data models
Data Models
 Underlying the structure of a database is the data model:

 A data model provides a way to describe the design of a database at the

physical, logical, and view levels.

There are a number of different data models that we shall cover in the text.The

4
data models can be classified into four different categories:
Relational Model.
 The relational model uses a collection of tables to represent both data and
the relationships among those data.

 Each table has multiple columns, and each column has a unique name.
Tables are also known as relations.

 The relational model is an example of a record-based model.

 Each table contains records of a particular type. Each record type defines
a fixed number of fields, or attributes. The columns of the table correspond
to the attributes in record based model.

 The relational data model is the most widely used data model.

Entity-Relationship Model.
 The entity-relationship (E-R) data model uses a collection of basic objects,
called entities, and relationships among these objects.

 An entity is a “thing” or “object” in the real world that is distinguishable from

other objects.

Object-Based DataModel.
 This led to the development of an object-oriented data model that can be
seen as extending the E-R model with notions of encapsulation, methods
(functions), and object identity.

 The object-relational data model combines features of the object-oriented

data model and relational data model.

Semi structured Data Model.

 The semi structured data model permits the specification of data where
individual data items of the same type may have different sets of
attributes.

 The Extensible Markup Language (XML) is widely used to represent semi

structured data.

Q:Explain the database languages

Database Languages
The data-definition and data-manipulation languages are part of database

5
languages..
I.Data-Manipulation Language
A data-manipulation language (DML) is a language that enables users to access
or manipulate data.
The types of access are:
• Retrieval of information stored in the database
• Insertion of new information into the database
• Deletion of information from the database
• Modification of information stored in the database
There are basically two types:
• Procedural DMLs require a user to specify what data are needed and how to get
those data.
• Decla rative DMLs (also referred to as nonprocedural DMLs) require a user to
specify what data are needed without specifying how to get those data.

II.Data-Definition Language
 We specify a database schema by Data Definition Language (DDL).

 The DDL is also to specify additional properties of the data.

 certain consistency constraints in DDL are.

Domain Constraints.
 A domain of possible values must be associated with every attribute

 (for example,integer types,character types,date/time types).

 Declaring an attribute to be of a particular domain acts as a constraint on

the values that it can take.

 Domain constraints are the most elementary form of integrity constraint.

 They are tested easily by the system whenever a new data item is entered
into the database.

Referential Integrity.
 There are cases where we wish to ensure that a value that appears in one
relation for a given set of attributes also appears in a certain set of
attributes in another relation (referential integrity).

 For example, the department listed for each course must be one that
actually exists.

 More precisely, the dept name value in a course record must appear in the
dept name attribute of some record of the department relation.

 Database modifications can cause violations of referential integrity.

6
  When a referential-integrity constraint is violated, the normal procedure is
to reject the action that caused the violation.

Assertions.
 An assertion is any condition that the database must always satisfy.

 Domain constraints and referential-integrity constraints are special forms

of assertions.

 And conditions like “Every department must have at least five courses ”can
be expressed as an assertion.

 When an assertion is created, the system tests it for validity.

 Database modifications should not violate assertion.

Authorization.
 We can differentiate the users by the type of access they are permitted on
various data values in the database.

 These differentiations are expressed in terms of authorization, the most

common authorizations are

 read authorization: Allows reading, but not modification of data;

 insert authorization:Allows insertion of new data, but not modification of

existing data;

 update authorization: Allows modification, but not deletion, of data;

 delete authorization:Allows deletion of data. We may assign the user all,

none, or a combination of these types of authorization.

UNIT-II ( Chapter 2- Relationl Databases)

Database schema
 Database schema is the logical design of the database.
 Relation schema represents the logical design of the relation.
7
Schema of banking database
Branch_schema(branch_name,branch_city,assets)
Customer_schema(customer_name,customer_street,customer_city)
Loan_schema(loan_number,branch_name,amount)
Borrower_schema(customername,loan_number)
Account_schema(account_number,branch_name,balance)
Depositor_schema (customer_name,account_number)

From the above schema ,the depositor schema describes the association
between customers and accounts .

The borrower schema describes the association between customers and loan.

Basic structure
Attributes:In Relational model we refer to the coloumn headers of a table as
attributes
Domain:set of permitted values for an attributes called as domains
Relation:Table name is mathematically referred as relation in Relational
model
Tuple:Row is referred as tuple in relational model

Keys
No two values in a relation are allowed to have exactly the same value for
two attributes. Keys ensures this.There are different types of keys ,
1.Super key
Set of one or more attributes taken collectively ,allow us to identify uniquely
a tuple
Example
{customer_id}
{customer_id,customer_name}
{cutomer_id,customer_name,street}
Candidate key
 Minimal super keys are called as candidate key. ie Super keys for
which no proper subset is a super key.
Example

8
 {customer_id}
 Only customer id is enough to identify the tuples uniquely.
 Candidate key is also called as Primary key

2.Foreign key
 A relation schema ,say r1 ,may include attributes which are the
primary key of another relation schema ,say r2.This attribute is called
foreign key.
 Then the relation r1 is called the referencing relation and r2 is called
the referenced relation.

Example
account
Account_number
Branch_name
Balance
depositor
Customer_name
Account number

 In the example given above account number in depositor is a foreign

key.
 All the account numbers in depositor must be part of the account
numbers in account relation.
Query language:
Query language is a language in which a user requests information from the
database.
It is categorized as
1.Procedural language- The user instructs the system to perform a sequence
of operations on the database to compute the desired result
Example:PL/SQL
2.Non procedural language-the user describes the desired information
without giving a specific procedure
Example:SQL

Relational algebra :
9
Definition:

It is created by Edger F.codd at IBM ,is a family of algebra with a well

founded semantics used for modeling the data stored in relational
databases,and defining queries on it.
Definition:

Q:Explain the fundamental relational algebra operations.

Fundamental Relational- algebra Operations

✓ They accept relations as their input and produce relations as their
output.
✓ There are two types of operations Unary and Binary

The fundamental operations of relational algebra are as follows −

 Select

 Project

 Union

 Set different

 Cartesian product

 Rename

Select Operation (σ)

 It selects tuples that satisfy the given predicate from a relation.
 It is denoted by The Greek letter σ .

Syntax
σp(r)
Where σ stands for selection predicate and r stands for relation.

10
 σbranch_name = "nagamalai" (loan)
Output − Selects tuples from loan where branch is 'nagamalai'.

Loan_number Branch_name Amount

L1 Nagamalai 2000

σbranch_name = "nagamalai"ᴧ amount>5000 (loan)

Output − Selects tuples from loan where branch is 'nagamalai' and
amount >5000.

Loan_number Branch_name Amount

L1 Nagamalai 10000

The operator ᴧ stands for and operation.

Project Operation (∏)
 It projects column(s) that satisfy a given predicate.
 It is denoted by the symbol ∏
Syntax − ∏A1, A2, An (r)

Where A1, A2 , An are attribute names of relation r.

Example

∏loan_number, amount(loan)

Output:selects coloumns loan_number and amount from loan

Loan_number Amount
L1 5000

composition of relational operations

Instead of giving a relation as the argument of the projection
operation,we can give an expression.
11
 ∏branch_name, amount( σbranch_name = "nagamalai"(loan))
Output:
Selects the columns branch_ name ,amount where branch name
is nagamalai

Loan_number amount

L1 2000

Union Operation (∪)

It performs binary union between two given relations.

It is denoted by the symbol U .

Syntax

rUs

Where r and s are either database relations or expressions.

Union operation requires two conditions to hold

1.The relations r and s must have same number of attributes

2.The domains of ith attribute of r and ith attribute of s must be the

same for all i
Example:

∏customername(borrower) U ∏customername(depositor)

Customer_name

Ravi
Priya

12
Rama

Set Difference (−)

The result of set difference operation is, tuples which are present in
one relation but are not in the second relation.
It is denoted by the symbol(-)

Example

∏customername(borrower) - ∏customername(depositor)

this expression selects the customers having only loan and no

deposit as follows.

Customer_name

Ravi
Priya

Cartesian Product (Χ)

Combines information of two different relations into one.

 Where r and s are relations.

 It is denoted by the symbol −.
Cartesian product between two relations r and s can be
represented by r Χ s .
Output − produces a relation, which shows all the customername
and branch_name.

πcutomer_name,branch_name(borrower X loan)

13
This expression takes cartesian product between two tables borrower
and loan and then applies the projection operation.

cutomer name branch name

Ravi kalavasal
Ravi nagamalai
Shree kalavasal
Shree nagamalai

Cartesian product wil leave many duplicate elements.Here same customer

names are appeared twice.

Rename Operation (ρ)

 The results of relational algebra are also relations but without
any name.
 The rename operation allows us to rename the output relation.
'rename' operation is denoted with small Greek letter rho ρ
 It is denoted by the notation ρ
Syntax

ρ x (E)

Where the result of expression E is saved with name of x.

Example
ρ newloan (∏branch_name, amount(loan))

Modification of the database

Deletion

14
in relational algebra a deletion is expressed in terms of set difference
operation
r←r-E
where r is a relation and E is a relational- algebra query.
Example
delete the records from the relation “loan” where branch name is
Nagamalai.
loan←loan- σbranch_name = "Nagamalai"(loan)

Delete all loans where amount is <50000

loan←loan- σamount >50000(loan)

Insertion

There are two ways to insert data into a relation

1. We can specify a tuple (row) to be inserted
2. We can specify a query .the result of the query gives the tuples
to be deleted.
In relational algebra insertion is expressed by

r←r U E
Example
loan←loan U {12,”nagamalai”,90000}
Updating
We can change a value in a tuple by updation operatin
This can be done by

∏F1,F2,……Fn(r)

15