Share article DBMS NOTES FOR 4th SEM DIPLOMA: DATA BASE ENVIRONMENT SYSTEM : 1.0 Introduction Data ...
DATA BASE ENVIRONMENT SYSTEM :
Data : Data is a piece of information which is fact. Data are known facts and figures that are recorded. Data have implicit meaning.
Data Base : Data base is a collection of inter related data. More or less stored permanently stored in a computer’s memory. The data base has the following implicit properties.
Data Base Management System : DBMS
DBMS is a collection of programs that enables users to create and maintain a data base. DBMS is a general purpose software system that provides the process of (i) Defining (ii) Constructing & (iii) manipulating database for applications.
Defining : It involves specifying the data type structures, and constraints for the data to be stored in the data base.
Constructing : It is the process of storing the data itself on some storage medium that is controlled by the DBMS.
Manipulating : It is a data base includes function as querying the database to retrieve specific data, updating the database to reflect changes.
1.2 Data Base System Environment :
Users : The layman or the end users whose job is to access the data base for querying, updating & generating reports.
Application programmers : They implement the specification (given by the system analysts) into program. They test, debug, document & maintain the transactions. They are also known as Software Engineers.
Application Programs : These are programs written by the Software Engineers to define, construct or to manipulate the data bas.
Queries : Queries are used to retrieve specific data, update the database. Queries are generally handled by end users.
Software to Process Queries / Programs :
The Applications or Queries are processed by the DBMS software. This software handles programs as well Queries & passes them to a format that is understood by the next level.
Software to Access Stored Data :
It receives the format from the software and interprets it. It generates a hardware interrupts to access the storage devices. These interrupts may be to store, list, manipulate or even to delete the data.
Stored Database : The Database is stored in storage devices.
File Oriented Approach :
The file processing system or File Management System is used to store data in a computerized data base. Before the advent of DBMS, the application programs are defined & maintained in a master file and other supporting transaction files. Hence one master file with one or more transaction files are used. Here each user defines & implements the files needed for specific applications as part of programming application. The draw backs of file oriented approach are following:
(1) It leads to Data redundancy : The same data may be present in more
than one file.
(2) Wasted Memory Space : Redundant or duplicate copies of the same data
results in wastage of storage space.
(3) Loss of data Integrity : Data redundancy leads to inconsistency problems.
It results that the same data item has a different value in different files.
(4) Difficulty in accessing data : To access data from the files, programming
language programs must be written.
(5) Information is available in reports : Information is available only through
reports, in between queries are not supported.
(6) Data Isolation : Data are accessed through reports. This results in data
(7) Inadequate Security : Only operating system level security is provided. In
depth security is not provided.
(8) High Maintenance Cost : Software maintenance cost will be very expense.
(9) Each data file of an application is a separate entity.
Data Base Approach :
In the data base approach, a single repository of data is maintained. The data is defined once & is accessed by various users.
The characteristics of the Database are :
(i) Self Describing Nature of the Data Base System :
The data base system contains definition or description of the data base structure & constraints. This definition is stored in the system catalog. The information stored in the catalog is called meta data, it describes the structure of the primary data base.
(ii) Insulation between Programs & Data, & Data Abstraction :
In DBMS it is not necessary to change all programs whenever a change is made to the structure of the data files. So Data base files are program data independence.
In data base approach, the detailed structure & organization of each file are stored in the catalog.
(iii) Support of Multiple Views of the Data :
Data base allows to have multiple views of data. It provides facilities for defining multiple views.
(iv) Sharing of Data and Multi user Transaction Processing :
The data base allow multiple users to access the data base at the same time. DBMS include concurrency control, that is if several users try to update the same data it should control such that result of the updates are correct. The applications of the multi user are called Online Transaction Processing OLTP.
In the database approach a single repository of data is maintained. The data is defined once and accessed by various users.
The characteristics of the database are
Self Describing Nature of the Database System : The database system contains definition or description of the database structure and constraints. This definition is stored in the system catalog. The information stored in the catalog is called meta data. It describes the structure of the primary database.
Insulation between programs, Data and data Abstraction: In DBMS it is not necessary to change all programs whenever a change is made to the structure of the data files. So database files are program data independence.
In data base approach, the detailed structure and organization of each file are stored in the catalog.
Support of multiple views of the Data: Database allows to have multiple views of data. It provides facilities for defining multiple views.
Sharing of Data and Multi user Transaction Processing: The data base allow multiple users to access the database at the same time. DBMS include concurrency control; it avoids the updating if several users try to update the same data. The application of the multi user is called on line transaction processing OLTP.
Actors On the Scene :
Actors on the scene are persons who defines, constructs and manipulates the data base. They are involved in the design, use & maintenance of a large data base. Such people involved in day-to-day use of a large database.
(i) Data base Administrators : DBA
DBA responsibility is to manage or administering the resources. The primary resources is the data base and the secondary resources is the DBMS and related software. The DBA is responsible authorizing access to the database for monitoring its use, for acquiring software & hardware resources as needed. The DBA is held responsibility for breach of security or poor system response time.
(ii) Data base Designers :
Data base designers are responsible for identifying the data to be stored in the data base. He is also responsible for choosing appropriate structures to represent & store this data. He has to communicate with all prospective data base users, to understand requirements. It helps in designing.
(iii) End Users : They are people who access the data base for querying, updating & generating reports. The data base is exists for the need of end users. The various causes of end users are
(a) Casual End Users : They occasionally access the data base. They are in need of different information each time. Such end users are middle or high level managers they use a sophisticated data base query language. They learn only a few facilities that are used repeatedly.
(b) Naïve or parametric end users : Their job is to constantly query & update the data base. They make use of more portion of data base. They always use standard type of queries & updates called canned transactions. Such transactions are programmed & tested. The various naïve users are Bank tellers, Reservation Clerks, for airlines, hotels, Railway station etc. They learn very few facilities provided by DBMS, they understand only the standard types of transactions.
(c) Sophisticated End Users : Such users are familiarize with the facilities of the DBMS. The sophisticated end users are engineers, scientists, business analysts & others. They learn most of the DBMS facilities.
(d) Stand alone Users : Such users maintain personal data bases by using ready made programs that provide easy to use menu or graphics based interfaces. They are proficient in using a specific software package.
(iv) System Analysts : They determine the requirements of end users and develop specifications for transactions that meet the requirement.
(v) Application Programmers : Application programmer implements the specifications in to programs. The specification are defined by the System Analysts. The application programmer will test, debug, document and maintain the transaction.
Workers Behind the Scene :
Such users are not interested in the data base itself. The various workers behind the scene are as follows:
i. DBMS System Designers and Implementers :
Such persons will design & implement the DBMS modules and interfaces as a software package. A DBMS is complex software with modules. The DBMS must interface with other system software, such as the operating system and compilers for various programming languages.
ii. Tool Developers : They design & implement tools. The software packages facilitate data base system design & use. Tools are package that are generally purchased separately. The various packages for data base design, performance monitoring, natural language or graphical interfaces, prototyping, simulation & test data generation.
Operators & Maintenance Personnel : They are responsible for the actual running & maintenance of the hardware & software.
Benefit of Using Data Base Approach :
The benefits of using data base approach are as follows :
i. Controlling Redundancy : The DBMS approach reduces redundancy, i.e. duplicates of data. This redundancy is storing the same data multiple times leads to several data.
ii. Restricting Unauthorized Access :
In multiple users share a data base, all users will not be authorized to access all information in the data base. Some users may be permitted only to retrieve data while others are may be permitted to retrieve & to update.
iii. Providing persistent storage for program objects & Data Structures :
Databases are used to provide persistent storage for program objects & data structures.
iv. Permitting Inferences & Actions using Rules :
Data base system allows defining the deduction rules for inference new information from the stored data base facts.
v. Providing Multiple User Interfaces :
The data base is used by various types of users. They include query languages for casual user, programming languages interfaces for application programmers; forms & command codes for parametric users; menu driven & natural language interfaces for stand alone interfaces. So the DBMS provides access to the multiple user interfaces.
vi. Representing Complex Relationship among Data :
The DBMS has the capability to represent a variety of complex relationships among the data as well as to retrieve & update related data easily & efficiently.
vii. Integrity Constraints : The DBMS must provide capabilities to define & enforce the constraints. The simplest type of integrity constraint involves specifying a data type for each data item.
ix. Providing Backup & Recovery : The DBMS must provide facilities to recover from hardware or software failures. The backup & recovery subsystem of DBMS is responsible for recovery.
The benefits of Data base Approach are as fallows :
(i) Potential for Enforcing Standards : The data base approach permits the DBA to define & enforce standards among data base users in a large Organization. It allows communications & co-operation among various departments, projects & users within the organization.
(ii) Reduced Application Development Time : The DBMS provides facilities to create new applications in short durations. The estimated time is less than to one-sixth to one-forth of that of traditional file system.
(iii) Flexibility : It is necessary to change the structure of a data base as requirements change. The DBMS allow certain types of changes to the structure of the data base without affecting the stored data & the existing applications programs.
(iv) Availability of Up-to-Date Information : A DBMS makes the data base available to all user. One user’s update is applied to the data base, all other users can immediately see this update.
Economies of Scale : The DBMS permits consolidation of data & applications, thus reducing the amount of wasteful overlap between activities of data processing personnel in different departments.
When not to Use a DBMS :
The overheads costs of DBMS are due to the following :
i. High initial investment in hardware, software & training.
ii. Burden for defining & processing data.
Overhead for providing security, concurrency control, recovery & integrity
The DBMS is used under following circumstances :
The data base & applications are simple, well defined & not expected to change
(ii) Multiple user access to data is not required.
2. DATA BASE SYSTEM CONCEPTS AND ARCHITECTURE : Chapter No. 2.
In early days of DBMS packages, the whole DBMS software packages are tightly integrated system. In modern days, DBMS software packages are modular in design with client server system architecture. Here large centralized mainframe computers are replaced by hundreds of distributed workstations & personal computers connected via Communication networks.
In basic client-server architecture, system functionality is distributed between two types of modules.
(i) Client Module
(ii) Server Module
Client Module : It will run on a user work station or personal computer. It handles user interaction & provides the user friendly interfaces such as forms or menu-based GUIs (Graphical User Interfaces). In client module application programs & user interfaces that access the data.
Server Module : The server module handles data storage, access, search, & other functions.
Data Models, Schemes & Instances :
Data Models : It is a collection of concepts that is used to describe the structure of a data base. It provides all necessary support to describe the data base. The structure of a data base mean the data types, relationship & constraints. The data models have set of basic operations for specifying retrievals & updates on the data base.
Categories of Data Models : Data models are classified based on the types of concepts.
(i) High level or Conceptual Data Models
(ii) Low level or Physical Data Models
(iii) Representational or Implementation Data Models
(i) High Level or Conceptual Data Models : This level provides concepts that are close to the way the users understand the data. This level is meant for the programmer not for end users. Conceptual data models use concepts such as entities, attributes & relationships.
(ii) Low level or Physical Data Models : It provides concepts that describe the details how the data is stored within the storage devices. This level is meant for the computer specialists not for end users.
(iii) Representational Data Models : This level provides concepts that is understood by end users. It provides details the way data is organized within the computer. It hides the details of data storage implemented on a computer system. Representational or Implementation data models are relational data model network or hierarchical models. SQL is standard language for relational data bases. This data model represents data by using record structures & hence is known as record based data models.
Physical data model describes how data is stored in the computer by representing information such as record formats, record ordering & access paths. An access path is a structure that makes the search particular data base records efficient.
Entity : Entity represents a real world object or concept. Example: employee, project.
Attribute : Attribute represents property. It describes an entity. Ex: employee’s salary.
Relationship : Relationship represents an interaction among the entities. It is among two or more entities. For example: works on relationship between an employee & a project.
Schemas, Instances & Data base State :
Data base Schema : The description of a data base is called data base schema. It is specified during data base design & is not expected to change frequently.
Schema diagrams are conventions for the displaying schemas. Below fig. shows a schema diagram :
A schema diagram displays name of records, data items types & few constraints.
Data Base State:
The actual data in a data base may change frequently. The data in the data base at a particular moment in time is called a data base state or snapshot. It is also known as current set of occurrences or instances in the data base.
DBMS Architecture & Data Independence :
Characteristics of Data base Approach :
(i) Insulation of programs & data
(ii) Support of multiple user views
(iii) Use of a catalog to store the data base description.
So, the DBMS architecture for data base systems is called the three schema architecture. Its goal is to separate the user applications & the physical data base. The three levels of DBMS :
1. Internal Level : It is internal schema. It describes the physical storage structure of the data base. It uses a physical data model & describes the complete details of data storage & access paths for the data base.
2. Conceptual Level : It describes the structure of the complete data base for community of users. This level hides the details of physical storage structures & concentrates on describing entities, data types, relationships user operations, constraints.
3. External or View Level : It describes the part of the data base that a user group is interested in & hides the rest of the data base from that user group.
The three schemas describes the data, the data is actually exists at the physical level. The user refers to its own external schema. The DBMS transform a request specified on an external schema. Hence, the DBMS transform a request specified on an external schema into a required== against the conceptual schema, & then onto a request on the internal schema for processing over the stored data base. This processing of transforming requests & results between
levels are called Mappings.
Data Independence :
Data independence is the capacity to change the schema at one level of a data base system without having to change the schema at the next higher level.
Two types of data independence.
(i) Logical Data Independence :
(ii) Physical Data Independence :
Logical Data independence : It is the capacity to change the conceptual schema without having to change external schemas or application programs. The conceptual schema is changed during data base expansion or reduction of data base.
Physical Data Independence : It is the capacity to change the internal schema without having to change the conceptual schemas. Changes to the internal schema is needed because some physical files are to be reorganized.
Data base Languages & Interfaces :
DBMS provide appropriate languages & interfaces for each category of users.
(a) Data Definition Language (DDL) : This language is used by the DBA & by data base designers to define both conceptual & internal schemas. DDL compiler function is to process DDL statements to identify descriptions of the schema. Constructs and schema description is stored in the DBMS catalog.
(b) Storage Definition Language (SDL) : It is used to specify the internal schema. The mappings between the two schemas are specified in DDL and SDL.
(c) View Definition Languages (VDL) : This language specify user views and their mappings to the conceptual schema.
(d) Data Manipulation Language (DML): This language allows to manipulate the data. The manipulations include retrieval, insertion, deletion and modification of the data.
Two types of DML :
(i) High level or non-procedural DML : It is used to specify data base operations. Such DML statements entered interactively from the terminal or to be embedded in a general purpose programming language.
(ii) Low-level Procedural DML : It is a procedural DML that is embedded in a general purpose programming language. Such DML retrieves individual records or objects from the data base and processes each separately. Hence, a programming language constructs such as looping, to retrieve a process each record from a set of records.
Any DML command either high level or low level are embedded in a HLL, that language is host language and the DML is known as data sublanguage.
In high level DML used in a stand alone interactive is called a query language.
DBMS Interfaces :
The user friendly interfaces provided by the DBMS are :
(i) MENU BASED INTERFACES FOR BROWSING : The interfaces present the user with lists of options called menus. Menus avoids the need to memorize the specific commands & syntax of a query language.
(ii) Forms based Interfaces : A form based interface displays a form to each user. Users has to fill out all the form entries to insert the new data. Forms are designed & programmed for naïve users as interfaces. DBMS provides various front end tools to design the forms.
2) Graphical User Interfaces : It displays a schema to the user in diagrammatic form. GUIs use both menus & forms. GUIs use pointing device such as mouse, to pick the parts of displayed schema diagram.
3) Natural Language Interfaces : Such interface receives the requests written in human language & tries to understand them. It processes this requests & submits to the DBMS.
4) Interfaces for Parametric Users : Parametric users have small set of operations that must be repeatedly performed. System analysts & programmers design & implement a special interface.
5) Interfaces for the DBA : The data base system contain privileged commands that are used by the DBA’s staff.
Database System Environment :
DBMS is a complex software system. The components of the DBMS are :
Stored Data base: The data base and DBMS catalog are stored on disk , access to the disk is controlled by the operating system which schedules disk input/output.
Storage Data Manager: It is a module of the DBMS which controls access to DBMS information i.e., stored on the disk. It uses basic operating system services for carrying out low level data transfer between the disk and computer data storage. But it also controls other aspects of data transfers such as handling buffers in main memory.
DDL Compiler: It processes schema definition, specified in the DDL(data definition language)and stores descriptions of the schemas in the DBMS catalog. The catalog contains information such as names of files, data items storage details of each file, mapping information among schemas and constraints.
Run Time Data base processor: Handles data base access at run-time, receives or update operations and carries them out on the data base.
Query Compiler: Handles high level queries that are entered interactively, it parses, analysis and compiles or interprets a query by creating data base access code and generates calls on the run-time processor for executing the code.
Pre Compiler: Extracts DML commands from an application program written in a host programming language.
DML Compiler: Accepts data manipulation language (DML) commands, compiles them into object code.
Host language compiler: Other than DML commands, remaining commands are sent to the host language compiler, generates the object code.
Canned Transaction: It contains the object code of the DML commands and other program codes, also includes calls to the run-time data base processor.
DBA: The responsibility of the DBA is to administering the data base and the secondary resource.
End users: This class of user’s job is to access the data base for querying, updating, and generating reports.
Application Programmer: Determines the requirements of the end users and develop specifications for canned transactions that meet these requirements.