Dinabandhu Mahavidayalaya Collage Routine of B.Sc II year. The student who have Honours in Mathematics and has the pass subject Physics and Computer Science.

DATABASE MANAGEMENT SYSTEM

v DATABASE MANAGEMENT SYSTEM :

·         Definition:

                                       A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the Database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.

§  Database-management system is widely use in Banking, Airlines, University, Credit Card Transaction, Telecommunication, Finance, Manufacturing, Sales, Human Resources.

·        Database System Vs File System:

• Data redundancy and inconsistency:

                                                              Since different programmers create the files and application programs over a long period, the various files are likely to have different formats and the programs may be written in several programming languages. Moreover, the same information may be duplicated in several places (files).

                                             Data inconsistency that is, the various copies of the same data may no longer agree.

• Difficulty in accessing data:

                                           The conventional file-processing environments do not allow needed data to be retrieved in a convenient and efficient manner.

             Example-

                           Suppose that one of the bank officers needs to find out the names of all customers who live within a particular postal-code area. The officer asks the data-processing department to generate such a list. Because the designers of the original system did not anticipate this request, there is no application program on hand to meet it. There is, however, an application program to generate the list of all customers. The bank officer has now two choices: either obtains the list of all customers and extract the needed information manually or ask a system programmer to write the necessary application program. Both alternatives are obviously unsatisfactory.

• Data isolation:

                        As the data are scattered in various files, and files may be in different formats, 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. For example, the balance of a bank account may never fall below a prescribed amount (say, $25). Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when new constraints are added, it is difficult to change the programs to enforce them.

• Atomicity problems:

                               A computer system, like any other mechanical or electrical device, is subject to failure. Consider a program to transfer $50 from account A to account B. If a system failure occurs during the execution of the program, it is possible that the $50 was removed from account A but was not credited to account B, resulting in an inconsistent database state. Clearly, it is essential to database consistency that either both the credit and debit occur, or that neither occur. That is, the funds transfer must be atomic. It is difficult to ensure atomicity in a conventional file-processing system.

• Concurrent-access anomalies:

                                                 For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data.

                                                Consider bank account A, containing $500. If two customers withdraw funds (say $50 and $100 respectively) from account A at about the same time, the result of the concurrent executions may leave the account in an incorrect (or inconsistent) state.

                                                 If the two programs run concurrently, they may both read the value $500, and write back $450 and $400, respectively. Depending on which one writes the value last, the account may contain either $450 or $400, rather than the correct value of $350.

• Security problems:

                           Not every user of the database system should be able to access all the data. For example, in a banking system, payroll personnel need to see only that part of the database that has information about the various bank employees. They do not need access to information about customer accounts. But, since application programs are added to the system in an ad hoc manner, enforcing such security constraints is difficult.

-       This is the measure disadvantage in file processing system over a database management system.

·        Data Abstraction:

                                   Many database system users are not computer trend developers hide the complexity form users throw severable table of abstraction to simplify users to interactions with the system. This is called data abstractor.

• 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 thus describes the entire database in terms of a small number of relatively simple structures.

• View level:

                 The highest level of abstraction describes only part of the entire database. Many users of the database system do not need all this information; instead, 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.

 

·         Data Model:

             Data model is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

Entity Relationship Model:

                                    The entity-relationship (E-R) data model is based on a perception of real world that consist of a collection of basic objects call Entity and relationships among these objects.

v  Database Legacy:

                      Flat file, Hierarchical & Network database are usually referred as Legacy Database. They represent the ways. People used to organize information in prehistoric times.

1.    Flat file Database:

             The flat file database was probably one of the earliest database management systems.

Problems of flat file database are:      Data redundancy, slow processing speed, error prone, retrieved.

 

         Hierarchical Database                                   Network Database                                         Relational Database

2.    Hierarchical Database:

                    It stores data in Hierarchical structures similar to that of a family tree, organization chart, or pyramid. It is based on “parents/child” paradigm in each parents could have many children but each child has one and only one parents.

In the hierarchical database model redundancy is greatly reduce compared with Flat file database model. The hierarchical database maintains one to many relationships very well.

3.    Network Database:

               It is also based on the concept of parents/child relationship but removes the restriction of one child having one and only one parents.

v  Data Integrity:

       Data integrity falls into three following categories:

1.     Entity Integrity:   Entity integrity ensures that row can be identifying by an attribute called the Primary key. The primary key cannot have a null value.

2.     Domain Integrity: Domain integrity refers to the range of valid entities for a given column. It ensure that there are only valid entities in the column.

3.     Referential Integrity: Referential integrity ensures that for every value of Foreign Key, there is matching value of primary key.

v  Database Languages:

                           A database system provides a data definition language to specify the database schema and a data manipulation language to express database queries and updates. In practice, the data definition and data manipulation languages are not two separate languages; instead they simply form parts of a single database language, such as the widely used SQL language.

Ø  Data-Definition Language:

                                                                    We specify a database schema by a set of definitions expressed by a special language called a data-definition language (DDL).

For instance, the following statement in the SQL language defines the account table:

create table account

(account-number char(10),

balance integer)

Execution of the above DDL statement creates the account table. In addition, it updates a special set of tables called the data dictionary or data directory.

                                             A data dictionary contains metadata—that is, data about data. The schema of a table is an example of metadata. A database system consults the data dictionary before reading or modifying actual data.               

                                            We specify the storage structure and access methods used by the database system by a set of statements in a special type of DDL called a data storage and definition language. These statements define the implementation details of the database schemas, which are usually hidden from the users.

                                              The data values stored in the database must satisfy certain consistency constraints. For example, suppose the balance on an account should not fall below $100. The DDL provides facilities to specify such constraints. The database systems check these constraints every time the database is updated.

Ø  Data-Manipulation Language:

                                                    Data manipulation is

         • The retrieval of information stored in the database

         • The insertion of new information into the database

 • The deletion of information from the database

               • The modification of information stored in the database

A data-manipulation language (DML) is a language that enables users to access or manipulate data as organized by the appropriate data model. There are basically two types:

• Procedural DMLs require a user to specify what data are needed and how to get those data.

• Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to get those data.

Declarative DMLs are usually easier to learn and use than are procedural DMLs. However, since a user does not have to specify how to get the data, the database system has to figure out an efficient means of accessing data. The DML component of the SQL language is nonprocedural.

v  The Query Processor:

                                The query processor components include

           • DDL interpreter:                

                                   It interprets DDL statements and records the definitions in the data dictionary.

           • DML compiler:                 

                                   It translates DML statements in a query language into an evaluation plan consisting of low- level instructions that the query evaluation engine understands.

                                                           A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.

         • Query evaluation engine:

                                  It executes low-level instructions generated by the DML compiler.

v  Database Users and Administrators:

                                   A primary goal of a database system is to retrieve information from and store new information in the database. People who work with a database can be categorized as database users or database administrators.

Ø  Database Users and User Interfaces

                                     There are four different types of database-system users, differentiated by the way they expect to interact with the system. Different types of user interfaces have been designed for the different types of users.

• Naive users:

                  Naïve users are unsophisticated users who interact with the system by invoking one of the application programs.

                    For example, a bank teller who needs to transfer $50 from account A to account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred, the account from which the money is to be transferred, and the account to which the money is to be transferred.

                    The typical user interface for naive users is a forms interface, where the user can fill in appropriate fields of the form. Naive users may also simply read reports generated from the database.

• Application programmers:

                     Application programmers are computer professionals who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development (RAD) tools are tools that enable an application programmer to construct forms and reports without writing a program.

• Sophisticated users:

                   Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They submit each such query to a query processor, whose function is to break down DML statements into instructions that the storage manager understands.

• Specialized users:

                   Specialized users are sophisticated users who write specialized database applications that do not fit into the traditional data-processing framework. Among these applications are computer-aided design systems, knowledge base and expert systems, systems that store data with complex data types (for example, graphics data and audio data), and environment-modelling systems.

v  Database Administrator:

                                         One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator (DBA). The functions of a DBA include:

• Schema definition:  

                             The DBA creates the original database schema by executing a set of data definition statements in the DDL.

• Storage structure and access-method definition

• Schema and physical-organization modification:

                             The DBA carries out changes to the schema and physical organization to reflect the changing needs of the organization, or to alter the physical organization to improve performance.

• Granting of authorization for data access:

                              By granting different types of authorization, the database administrator can regulate which parts of the database various users can access. The authorization information is kept in a special system structure that the database system consults whenever someone attempts to access the data in the system.

• Routine maintenance: Examples of the database administrator’s routine maintenance activities are:

ü  􀀀 Periodically backing up the database, either onto tapes or onto remote servers, to prevent loss of data in case of disasters such as flooding.

ü  Ensuring that enough free disk space is available for normal operations, and upgrading disk space as required.

ü  􀀀 Monitoring jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some users.

v  Storage Manager:

A storage manager is a program module that provides the interface between the low level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system. The storage manager translates the various DML statements into low-level file-system commands. Thus, the storage manager is responsible for storing, retrieving, and updating data in the database.

The storage manager components include:

• Authorization and integrity manager:

                            It tests for the satisfaction of integrity constraints and checks the authority of users to access data.

• Transaction manager:

                            It ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.

• File manager:

                    It manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

• Buffer manager:

                    It is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.

The storage manager implements several data structures as part of the physical system implementation:

• Data files: It stores the database itself.

• Data dictionary: It stores metadata about the structure of the database, in particular the schema of the database.

• Indices: It provides fast access to data items that hold particular values.

v  Transaction Manager:

                                It ensures that the database remains in a consistent (correct) state despite system failures and that concurrent transaction execution proceed without conflicting.

·         File Manager: It manages the allocation of space on disk storage & the data structures used to represent information stored on a disk

·         Buffer Manger: It is responsible for fetching data from disk storage into main memory & deciding what data to cache in the memory. The buffer manager is a critical part of the database, since it enables the database to handle data sizes that much larger than the size of main memory.

v  Entity:

          An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. Entities are the building block of database. We represent an entity using a rectangular box that contains the name of the entity.

v  Attributes:

               An attributes is a property of an entity that differentiates from other entity & provide information about the entity.                              In an E-R diagram we represent attributes as ellipse.

• Simple and Composite attributes:

                                         Simple attributes have been simple; that is, they are not divided into subparts.

                                         Composite attributes can be divided into subparts (that is, other attributes).

For example, an attribute name could be structured as a composite attribute consisting of first-name, middle-initial, and last-name. So name is a composite attributes.

• Single-valued and Multi-valued attributes:

                                                        When an attribute has a single value for a particular entity, it is called the single-value attribute.

                                                           When an attribute has a multiple value for a particular entity, it is called the Multi-value attribute. As example, a person can have several phones no. So phones no. attribute is Multi-valued attribute.

• Derived attribute:

                          The value for this type of attribute can be derived from the values of other related attributes or entities.

                                                        As example consider, the customer entity set has an attribute age, which indicates the customer’s age. If the customer entity set also has an attribute date-of-birth, we can calculate age from date-of-birth and the current date.

Ø  N.B:  An attribute takes a null value when an entity does not have a value for it. The null value may indicate “not applicable”—that is, that the value does not exist for the entity. For example, one may have no middle name. Null can also designate that an attribute value is unknown. An unknown value may be either missing (the value does exist, but we do not have that information) or not known (we do not know whether or not the value actually exists).

v  Relationship:

                   Relationship is used to establish a connection between a pair of logically related entities. It is a association between entities.

                                 Separate entities can have relationships with each other. For example- if student study various course the entity are student and course, while the relationship between them is represented by a diamond type.

	Student				Course

 

 

v  Mapping Cardinalities:

                                  Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set.

For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:

• One to one. An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. (See Figure 2.4a.)

• One to many. An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most one entity in A. (See Figure 2.4b.)

• Many to one. An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number (zero or more) of entities in A. (See Figure 2.5a.)

• Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A. (See Figure 2.5b.)

v  Keys:

       A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help uniquely identify relationships, and thus distinguish relationships from each other. So keys plays an important role maintain data integrity.

·         Candidate Key:  It is important to have an attribute in a table that uniquely identifies a row. So an attributes which identifies a row is called Candidates Key. This attributes has values that are unique.

                    Consider the table Vehicle

VEHICLE:

Serial#	Regn#	Description
023451	5602	Leyland
023452	4502	Volvo
023453	4513	Toyota

The value of attribute Serial#, Regn# & Description are unique in every row. Therefore all three are candidates key.

ü  Candidates key can also referred to as Surrogate Key

·         Primary Key:

                    The candidate key that we choose to identify each row uniquely is called the Primary key In the table Vehicle: If we choose Serial# to identify rows uniquely serial# is the primary key.

·         Alternative Key:

                          A candidate key that is not chosen as a primary key is an alternative key. In the table VEHICLE, if we choose Serial# as the primary key then Reg# & Description is the alternative key.

·         N.B:  It is important that we understood that a primary key is the only sure way to identify the row of a table. Hence an attribute key may have the null value. A null value is not to be permitted in a primary key. Since it would be difficult to uniquely identify rows containing null value.

·         Supper Key:

·         Composite Key:  

                         In a certain table, a single attribute cannot be used to identify rows uniquely so a combination of two or more attributes is used as a primary key. Such keys are called Composite Key.

·         Foreign Key:

                          When a primary key of one table appears as an attribute in another table, it is called the Foreign Key. In the second table a foreign key is used to relate two tables.

v  Weak Entity Set:

                     An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set.

                                        For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship.

                                        Although a weak entity set does not have a primary key, we nevertheless need a means of distinguishing among all those entities in the weak entity set that depend on one particular strong entity. The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. Hence the primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s discriminator.

v  Entity-Relationship Diagram:

                                             An E-R diagram can express the overall logical structure of a database graphically. E-R diagrams are simple and clear—qualities that may well account in large part for the widespread use of the E-R model. Such a diagram consists of the following major components:

• Rectangles, which represent entity sets

• Ellipses, which represent attributes

• Diamonds, which represent relationship sets

• Lines, which link attributes to entity sets and entity sets to relationship sets

• Double ellipses, which represent multi-valued attributes

• Dashed ellipses, which denote derived attributes

• Double lines, which indicate total participation of an entity in a relationship set

• Double rectangles, which represent weak entity sets