Chapter 1
1 Introduction to File and
Database Systems
Introduction to Files and Databases
File: A file system is a method for storing and
organizing computer files the data they contain to make it easy to find
and access them.
1
How data is stored in flat files ?
Data is stored in flat files as records.
Records consist of various fields which are delimited by a space,
comma, pipe, any special character etc.
End of records and end of files will be marked using any
predetermined character set or special characters in order to
identify them.
Example: Storing employee data in flat files
Data Storage Model for an Organization in File and Database
system
1
Drawbacks in File System
&
Advantages in DBMS
Data Redundancy and Inconsistency
1
Same information may get duplicated in several files.
Redundancy leads to higher storage and access cost
It leads to data inconsistency –various copies of same data
may no longer agree.
Example of File Processing of an
auto repair shop
1
Example of an auto repair shop that uses two separate file-oriented
systems: a Job Records System (with a JOB data file) and an
Employee Records System (with a MECHANIC data file). Notice
that three items of information must be duplicated in both data files.
Example of File Processing of an
auto repair shop
1
Example of a database design for the auto repair shop that links
two data tables and avoids duplication. Notice that the Mechanic No
field provides a link between the two tables, and information can be
accessed from either table.
Drawbacks in File System
&
Advantages in DBMS
Data isolation
1
Data is scattered in various files.
Hence writing new application programs to retrieve the
appropriate data is difficult.
Drawbacks in File System
&
Advantages in DBMS
Example of File Processing of an auto repair shop –Data
Isolation
1
Drawbacks in File System
&
Advantages in DBMS
Concurrent –access anomalies
1
File system allow multiple users to access and update the
same piece of data simultaneously. However this
concurrent updates may result in inconsistent data.
Drawbacks in File System
&
Advantages in DBMS
Data Security
1
Its very difficult to maintain and give proper secure
mechanism for data in file processing system
not all users should have access to all data .
Drawbacks in File System
&
Advantages in DBMS
Integrity problem
The data values stored in the file system must satisfy
1
certain types of Consistency constraints (logical rules) .
The accounts department needs to list the details of all the
employee whose salary field value must be less than
10,000. We have to handle this through program code in
file processing systems. But in database we can declare the
integrity constraints along with definition itself.
Drawbacks in File System
&
Advantages in DBMS
Atomicity problem
1
Ensuring that a system failure during a database update
does not leave the database in an inconsistent state .
It is difficult to ensure atomicity in a conventional file-
processing system.
Drawbacks in File System
&
Advantages in DBMS
Atomicity problem
1
Transferring $100 from Account A to account B. If a
failure occurs during execution there could be situation like
$100 is deducted from Account A and not credited in
Account B.
Data:
Raw facts; building blocks of information
Unprocessed information
Information:
1 Data
Database:
processed to reveal meaning
Database is a collection of inter-related data, that can be
processed by one or more application systems.
Database Management System:
Database Management System is a collection of interrelated
data and set of programs to access those data.
1
DBMS Applications
Banking: all transactions (withdraw, deposit, etc.)
1
Airlines : reservations, schedules
Universities : registration, grades
Human Resources : employee records, salaries, tax deductions
Manufacturing : Production, inventory, orders, supply chain
Advantages of DBMS
Controlling redundancy in data storage
1
Sharing of data among multiple users.
Restricting unauthorized access to data.
Enforcing integrity constraints on the database.
Disadvantages of DBMS
Complexity
Size
1
Cost of DBMS
Performance
Higher impact of a failure
Latest trends in Database
Multimedia Database – digital images, audio, video,
Animation and graphics
1
Example – Multimedia Database are:
Image search: [Link] (filter image by color)
Search engine for sounds: [Link]
Applications of Multimedia Database are:
Streaming data – Live Telecast
Interactive video – You tube
Digital Libraries
Image retrieval
Latest trends in Database
Distributed Database – Cloud Computing services
Used in social Networks – Face book, LinkedIn,
1
MySpace, Twitter
Mobile & Embedded Database
Washing Machine, TV, Mobile phone (Eg. Android phone
with SQLite database)
Database Systems used in Enterprise
Oracle
SQL Server
1
DB2
Sybase
MySQL
PostgreSQL
Teradata
Informix
Ingres
Microsoft Access
MongoDB
SQLite
Most Popular Database Systems used in Enterprise
Oracle
SQL Server
1
MySQL
PostgreSQL
Microsoft Access
SQLite
History of Oracle
In 1988, Oracle version 6.0 was released. It provided row-level locking,
hot backup and PL/SQL as main features.
1
By 1989, Oracle moved to new headquarters
in Redwood Shores, California.
In 1990, they released Oracle Applications Release 8, which included
account software for client/server
In 1992, they released Oracle 7.0. It provided better performance,
administrative utilities, application development tools, security
features, stored procedures, triggersand declarative integrity.
In 1995, Oracle became the first major company to announce
a comprehensive internet strategy.
By 1999, Oracle realized that "Internet Changes Everything". Oracle
released Oracle 8i and Oracle Applications 11i.
History of Oracle
In 2000, Oracle9iAS was released - AS(Application Server)
runs on middle tier in 3-tier Client/Server architecture boosting
the performance.
1 In 2001, Oracle9i was released. It allows Oracle to run
on RAC (Real Application Cluster), which is a collection of
low-cost servers.
In 2003, Oracle10g was released, where g stands for Grid
computing, which servers computing power across the
enterprise as a utility, automatically shifting processing load
based on demand.
In 2007, Oracle has released Oracle11g. The new version
focused on better partitioning, easy migration etc.
Oracle 12c version Features
Oracle is latest version is Oracle 12c, which would be a cloud
based database management system.
VARCHAR2 length up to 32767
1 Manage large chunk of data. Current version of databases
allows only up to 4000 bytes in a single varchar2 cell.
So developers has to either use CLOB or XML data types
which are comparatively slower that varchar2 processing.
Boolean in SQL
As of Oracle 11g Boolean is not a supported data type in
SQL ,but 12c we can enjoy the Boolean feature.
Oracle 12c version Features
Invisible columns -When a column is defined as invisible, the
column won’t appear in generic queries, unless the column is
explicitly referred to in the SQL statement .
1 SQL> CREATE TABLE emp (eno number(6), ename name
varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
Top N Query and Fetch and offset Replacement to
Rownum
With the release of Oracle 12c, has introduced the new
SQL syntax to simplify fetching the first few rows. The
new sql syntax "Fetch First X Rows only" can be used.