Databse Systems

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel

Content, examples and diagrams are taken from that book.

UNIT-I

Chapter-I : DATABASE SYSTEMS

Data: Data consists of raw facts, which the computer stores and reads. Data can consist of letters, numbers, sounds or images etc. that have some meaning in the user environment. Data are the raw material from which information is generated.

Information: When data has been processed to give it more meaning, it is called as information.

Database: An organized collection of logically related data usually designed to meet the information needs of multiple users in an organization.

Database Management System: (DBMS) is a software tool used to define, create, maintain and provide controlled access to the database.

DBMS software stores data structures, relationship between those structures and the access paths to those structures in a central location.

Q) How the data is organized within a database?

Ans: To help you visualize how a database stores data, think about a typical address book.

Fields: Each field contains a specific type of information such as first name, last name, phone number, email etc…

Records: Record is a collection of related fields.

Ex: All information about one person in an address book.

Tables: A complete collection of records makes a table

Ex: Contacts table

	FirstName	LastName	Company	Address	City	State	Pincode
Record1
Record2

Q) Why the database is important?

Ans: If you keep list of all your business customers in a database, you can

• You can sort the customers by pincode.
• Create a simple onscreen entry form that even your technically unskilled employee can use successfully

You can manipulate data in almost anyway you want.

Files, File Systems & Problems With File System Data Management.

Manual filing system works well when the number of items stored is quite small and they are only needed to be stored and retrieved.

A manual filing system crashes when cross referencing and processing of information in the files is carried out.

Limitations or disadvantages of File Processing Systems.

Program data dependence: File descriptions are stored within each program that access a given file.

In the invoicing system program access both the inventory pricing file and the customer master file.

Therefore this program contains a detailed description for both these files.In the below figure both the customer master file is contained in both the order filing system and invoicing system. Suppose it is decided to change the customer address field length in the records in customer master file from 20 to 30 characters. For this, each related program have to be modified.

Duplication of data or Redundancy of data: in the below figure, order filing system contains the inventory master file, the invoicing system contains inventory pricing file. Inventory master, inventory pricing file contains product descriptions and quantity. There is duplication of data which requires additional storage space.

         Orders Department                                                                   Accounting Department

Inconsistent data: The redundancy in storing the same data multiple times leads to data inconsistency when an update is applied to some of files but not to other.

Limited data sharing: In the file processing system, users have little opportunity to shared data outside their applications.

Lengthy development times: Developing an application by using the file systems is very skilled activity. The programmers has to write many programs for supporting file opening, file closing and iterative logic for representing operations, this is very lengthy process.

Incompatible file formats: Since the structure of files is embedded in application,  the structure is dependent on application programming languages.

Ex: structure of file generated by COBOL is different from ‘C’ programming language.

The application programmer has to develop software to convert the files to some common format for processing. This may be time consuming and expensive.

Fixed Queries: Any  query or report needed by organization has to be developed by the application programmer.

Lack of security: All users could see all data and no security and authorization subsystem.

No recovery and back up system:Data could be lost in case of hardware or software failure.

All the data is stored in disk files and accessed according to access methods (sequential, direct etc..) provided by file system and chosen by application programmer.

Q: What is a database system and What are the advantages of database systems?

Database System: Database and DBMS software together is called a database system.

Program data independence: DBMS allows certain types of changes to the structure of the database without affecting the stored data and the existing application.

Improved data sharing: The DBMS helps create an environment in which end users have better access to more data and better managed data.

Improved data security: The DBA uses security and authorization subsystem provided by DBMS to create accounts and to specify account restrictions. The DBMS will enforce these restrictions automatically.

Better Data Integration: The DBMS presents integrated view of the operations and clear view of the whole system. It becomes much easier to see how actions in one segment of the company affect other segments.

Minimized data inconsistency: Data inconsistency is also reduced in a properly designed database as such a database doesn’t allow different versions of same data in different places.

Ex: company’s sales department stores salesman name as ‘Bill Brown’ and the same person name is stored as ‘William G Brown’ in company’s HR department.

Improved Data access: A query is a question or specified request issued to DBMS for data manipulation.

An Adhoc query is a spur of the moment question. The DBMS sends back an answer ( called the query result set) to the application

For Ex: How many of our cutomer have balances of Rs. 3000 or more?

The DBMS gives quick answers to adhoc queries.

Improved Decision Making: Better managed data and improved data access makes it possible to generate better quality information on which better decisions are based.

Increased end- user productivity: The availability of data and tools that transform data into information allows end user to make quick decisions that can make the difference between success and failure in global economy.

Database system environment:

Database system environment is made up of five major parts. They are hardware, software, people, procedures and data.

Hardware: Hardware refers to all of the system’s physical devices.

For ex: computers (micro computers, servers etc..), storage devices, printers, networking devices (hubs, switches etc…) and other devices ( ATMS, ID readers etc..)

Software: Three types softwares are needed to make the database system function fully.

1. Operating system software: manages all hardware components and makes it possible for all other software to run on the computers. Ex: UNIX, Microsoft windows.
2. DBMS Software: manages the data within the database system.
   Ex: SQL server, Oracle, DB2, My SQL
3. Application programs and utility software: are most commonly used to access data found within the database to generate reports, tabulations and other information for decision making. For Ex: All DBMS vendors provide GUI’s to create database structures, control database access and monitor database operations.
4. People includes all users of database system. On the basis of their job functions, five types of users can be identified.
   1. System administrators: looks after database system general operations.
   2. Database administrators (DBA) manages the DBMS and ensures that the database is functioning properly.
   3. Database Designers or Database architects design database structure. The determination of what data are to be entered into the database and how the data are to be organized is an important part of database designer’s job.
   4. System analysts and programmers design and implement the application programs. They design and create the data entry screens, reports and procedures through which end users access and manipulate the database’s data.
   5. End users are the people who use the application programs to run the organisation’s daily operations. For Ex: clerks, managers, supervisors and directors.
   High level end users uses the information obtained from the database to make decisions.
5. Procedures: Procedures play an important role in a company. They enforce the standards by which the business is conducted within the organization and with customers. Procedures are also used to ensure that there is an organized way to monitor and audit both the data that enter the database and the information generated through the use of that data.
6. Data: are the raw materials from which information is generated. Data covers the collection of facts stored in the database.

DBMS Functions:

DBMS performs several functions that guarantee the integrity and consistency of the data in database. They are

1. Data dictionary management: The DBMS stores definitions of the data elements and their relationships in a data dictionary. The DBMS provide data abstractions and it removes structural and data dependency from the system.
2. Data Storage management: The DBMS creates and manages the complex structures required for data storage, thus you need not define and program the physical data characteristics. It also provide storage for on-screen definitions, report definitions, data validation etc..
3. Data transformation and presentation: The DBMS must manage the data in proper format for each country.
4. Security Management: DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access? , which data operations (read, add, delete or modify) the user can perform. This is important in multi user database system.
5. Multi user access control: To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database.
6. Back up and recovery management: to ensure data safety and integrity. DBMS provide special utilities that allow DBA to perform back up and restore procedures. Recovery management deals with the recovery of database after a failure, such as bad sector in disk or power failure.
7. Data integrity management: DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency.
8. Data access languages and application programming interfaces: The DBMS provide access through a query language. A query language is a non procedural language that lets the user specify what must be done without having to specify how it is to be done. Example of query language is SQL
9. Database communication interface: DBMS accepts end user requests from multiple, different network environments.

Disadvantages of DBMS

1. Increased Costs: Database system requires hardware, software and highly skilled people. The cost of maintaining these.
2. Management Complexities: The database system hold important data that are accessed from multiple sources, security issues may occur.
3. Frequent Updates: must perform frequent updates an d apply latest patches and security measures to all components. These increases personnel training costs.
4. Vendor Dependence: due to heavy investment in technology and personnel training, companies do not change database vendors. As a result, vendors donot offer pricing point advantages to existing customers.
5. Frequent Updates / Replacement Cycles: DBMS vendors frequently upgrade their products by adding new functionality i.e, upgrade versions of software. Some of these versions require hardware upgrades and training to users costs money.