DBMS Introduction
Chapter 1: DBMS INTRODUCTION
Data -> Field -> Record ->Database
Data is collection of bits that may represent character, number, date, audio, video, image etc.
Field is collection of data under a field name or column name. for example rollno,name, address, dob, email id, mobile no….
Record is collection of fields. Example student record, customer record, account record etc
Database is collection interrelated records stored together to serve multiple applications. For example student database is collection of student records.
DBMS : Database Management system
It is a software used to store and manage database. The primary goal of DBMS is to store and retrieve data, information in a convenient and efficient manner to make decisions.
Advantages of DBMS
1. Data redundancy - It reduces the data redundancy (duplicacy) to a large extent. The major problem of data redundancy is wastage of storage, effort, time and money. DBMS maintains and stores data at a centralized location. Sometimes some amount of redundancy is maintained as sound business policy (data backup) or technical reason (faster data access).
2. Data inconsistency - DBMS controls data inconsistency to a large extent. By controlling data redundancy, data inconsistency is also controlled.
3. Data Sharing - DBMS allows sharing of data. It also allows old and new applications to access data. Two different persons at same or different locations can view the same data for different purposes at the same time.
4. Data security – DBMS protects data against accidental or intentional unauthorized access.
5. Data Integrity – DBMS prevents invalid data to enter into database by imposing several integrity checks on data.
6. Data standards – DBMS ensures that all the data follow the applicable standards. It helps in data migration or interchange between systems.
7. Concurrency Control – DBMS ensures that database remains in consistent state despite concurrent execution of multiple transactions in the system.
8. Database recovery – It also ensures that system can recover from hardware and software failure without any harm to data. It unifies several data files into one database.
9. Query Processing – DBMS processes user queries, extracts data from database and returns results to user in a very fast manner.
Application areas of DBMS
1. Bank (customer information, transaction information, account and loan etc)
2. Railways (train information, reservation, cancellation, train schedule etc)
3. University ( course, examination results, registration etc)
4. Retail (Billing, sales, stocks, product information)
5. Stock market (shares information, sale and purchase of share and securities)
6. Telecommunication (call details, customer information, messages details , offers information etc)
7. Hospitals (patient information, treatment, test, doctors information)
8. Hotels (room information, billing, facilities, guest information etc)
9. Manufacturing (inventory, production , raw materials, finished goods, customers etc)
10. Insurance (policy details, premium, repayments etc)
DBMS Components
1. Query Processor
2. Storage Manager
Query Processor – It is responsible for translating query into low level instructions. It’s sub components are
(a) DML Compiler – (Data Manipulation Language) It translates DML statements into low level instructions.
(b) Embedded DML precompiler – It converts DML statements embedded in an application (written in java, vb) to normal procedure calls in the host language. It interacts with DML compiler to generate appropriate code.
(c) Query evaluation engine – It executes low level instructions given by DML compiler.
(d) DDL interpreter – (Data Definition Language) It interprets DDL statements and records them in a data dictionary containing metadata.
Storage Manager – It is a program module that provides the interface between the low level data stored in the database and the application program query. It is responsible for storing and retrieving data. It’s subcomponents are –
(a) Authorization and integrity manager – Authorization manager checks for the authority of users to access data. Integrity manager imposes integrity checks to prevent invalid data from entering the system.
(b) Transaction manager – It ensures that the database remains in a consistent state despite system failures or concurrent executions.
(c) File manager – It manages allocation of space on disk and the data structures needed to store information on disk.
(d) Buffer manager – It is responsible for fetching data into buffers created in main memory.
DBMS Data Structures
DBMS maintains several data structures to store data.
(a) Data Files – It stores the data itself.
(b) Data Dictionary – It stores metadata about the structure of database.
(c) Index- It provides fast access to data items stored in the database. It contains index key and location of record associated with index key items in the database.
(d) Statistical data – It is used by the query processor to select efficient ways of accessing data and solving a query. It contains statistical data about the database such as no of records containing address ranchi.
Database Users
I) Naïve Users – They are the users who interact with the database through forms, reports, menus written in some programming language such as java, vb etc.
II) Application programmers – They are the computer professionals who write applications.
III) Sophisticated users – They are the users who interact with the system using database query language such as SQL (structured query language). They have some internal knowledge of database.
IV) DBA (Database administrator) – DBA is the user who has central control over the database. The major responsibilities and functions of DBA are –
1. Database creation using DDL statements
2. Storage structure and access method definition using DDL statements
3. Modification of database design and physical organization of data
4. User creation and maintenance
5. Granting of rights to users
6. Revoking of right from users
7. Integrity constraints (rules) specification to prevent invalid data from entering the database
8. Routine maintenance work such as backup of database, upgrading disk space, monitoring transactions to ensure good performance, recovery from database failure end so on
Database Abstraction
DBMS doesn’t reveal every details to users. In fact hides certain details. Database Abstraction means hiding unnecessary details and revealing only necessary details to the user.
This enables smooth, easy and efficient user interaction. The complexity of interaction gets absorbed through several levels of abstraction.
Levels of Database Abstraction
1. Internal Level (Physical) - This level of abstraction is closest to the physical storage. It describes “how data” are actually stored on the storage using low level data structures.
2. Conceptual Level – It describes “what data” are actually stored in the database. It also describes the relationships existing among the data. In this level the database is described logically using simple data structures.
3. External Level (View) – This level is closest to the users. It is concerned with the way the data are viewed by the individual users. It describes various views of the same database. It also provides security mechanism to prevent users from accessing certain parts of the database.
Data Independence
The ability to modify database design in one level without affecting database design in the next higher is called data independence.
Physical data independence
It refers to the ability to modify the database design at the physical level without affecting the database design at the conceptual level.
Logical data independence
It refers to the ability to modify the database design at the conceptual level without affecting the database design at the view level.
It is more difficult to achieve logical database independence than the database independence because the views are heavily dependent upon the logical structures of the database.
DBMS architecture – client server architecture
DBMS follows client server architecture. It is usually partitioned into 2 or 3 parts.
Client requests services to server. Server system processes the requests of the client. Server is more powerful machine than client machine in terms of processing, memory and storage.
2 Tier Vs 3 Tier architecture
Distributed Databases
A distributed database is a database in which data is stored in multiple computers located at the same of different locations and interconnected through a computer network.
Features
1. Users are unaware of data fragments, data fragments distribution, data fragments name and data fragments locations.
2. Increased data reliability and availability.
3. Easier to expand database in future.
4. Allocation of data at location where they are needed most (greatest demand site).
5. Improved system performance as load is distributed among several processors.
6. Single site failure doesn’t affect the performance of system.
7. Difficult to manage distributed database.
8. Each and every database site needs to be secured.
9. Difficult to maintain data integrity over network.
10. Lack of data standards
11. Database design more complex (new issues and challenges of distributed databas)
12. Concurrency control and databases recovery requires a complex protocol.
13. Faster Query processing but slower database updates.
14. Additional softwares needed and OS must support distributed environment.
15. Initial cost high but gradually it becomes cost effective in the long run.
Note – Distributed databases becomes necessary as organization grows in size. It is suitable for organization which is spread over state, nation or globe.
Major Issues of Distributed Databases
1. Data Fragmentation
Data Fragmentation is the processing of fragmenting database into two or more data fragments.
Types of Data fragmentation
(a) Horizontal fragmentation – In this database is fragmented into data fragments consisting sets of rows. Each fragment consists of all the columns but selected rows. For example – customer database fragments on the basis of zone (east, west, north, south zone)
(b) Vertical fragmentation – In this database is fragmented into data fragments consisting sets of columns. Each fragment consists of all the rows but selected columns. For example – customer database fragments personal data for marketing department and financial data for accounts department.
(c) Mixed fragmentation – In this database is fragmented into data fragments both horizontally and vertically. Each fragment consists of but selected rows and columns. For example – customer database fragments on the basis of zone first and then on the basis of personal and financial data.
2. Data Replication
It deals with replicating data fragments. It can be of the following types –
a. No replication (no data fragments are replicated)
b. Full replication (all data fragments are replicated)
c. Partial replication (some fragments are replicated)
3. Data Allocation
It deals with allocation of data fragments to data sites. It requires careful matching of data fragment with its demand at a site. (DATA FRAGMENT SHOULD BE AT GREATEST DEMAND SITE)
Note - We have to properly analyze real life situation for handling data fragmentation, data replication and data allocation issues.