TUTORIALS on Fundamentals of Database Management Systems (FDBMS)

 

TUTORIAL on Fundamentals of Database Management System (FDBMS)

^{TUTORIAL I from UNIT 1}

Q1. List four applications which you have used, that most likely used a database system to store persistent data.

Q2. List four significant differences between a file-processing system and a DBMS.

Q3. Explain the concept of physical and logical data independence.

^Q4. List five responsibilities of a database-management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged.

Q5. What are the five main functions of a database administrator?

Q6. Explain three-schema Architecture. How does it help in designing the structure of a Database?

Q7. What is SQL? How do we classify the statements given in SQL for creating and accessing a database.

Q8. What are the characteristics of a DBMS? List down the advantages and disadvantages of using DBMS.

^{TUTORIAL II from UNIT 2}

Q1. What is E-R Model? How does it help a database designer in designing a database?

Q2. What is the role of a key in designing a database? Discuss the various types of keys used in maintaining the integrity of a database?

Q3. Write notes on Entity Set and Relationship Set. Differentiate Strong Entity Set from Weak Entity Set.

Q4. Develop an E-R Diagram for designing a database of a banking application.

Q5. What is relation? Differentiate between the terms: relation schema and relation instance.

Q6. What are domain constraints? Explain various domain constraints used in defining the schema of a database.

^{TUTORIAL III from UNIT 3}

Q1. Consider the following schemas in a DBMS as follows:

        (i)   Resort(resortNo, resortName, resortType, resortAddress, resortCity, numSuite)

        (ii)  Suite(suiteNo, resortNo, suitePrice)

        (iii) Reservation(reservationNo, resortNo, visitorNo, checkIn, checkOut, totalVisitor, suiteNo)

        (iv) Visitor(visitorNo, firstNmae, lastName, visitorAddress)

Write the query for the following:

        (a)  Write the SQL to list full details of all the resorts in Los Angels.

        (b)  Write the SQL to list full details of all resorts having number of suits more than 30.

        (c)  Write the SQL to list visitors in ascending order by their first name.

Solution:

        (a) SELECT * FROM Resort WHERE resortCity = "Los Angels";

        (b) SELECT * FROM Resort WHERE numSuite > 30;

        (c) SELECT * FROM Visitor ORDER BY lastName ASC;

Q2. Consider the following schemas in a DBMS as follows:

        (i)   Customer(customerName, customerStreet, customerCity)

        (ii)  Branch(branchName, branchCity, branchAssets)

        (iii) Account(accountNumber, branchName, accountBalance)

        (iv) Depositor(customerName, accountNumber)

Write the query for the following:

        (a)  Find all the bank customers having a loan, an account, or both at the bank.

        (b)  Find all customers who have both a loan and an account at the bank.

        (c)  Find all customers who have an account but no loan at the bank.

    Solution:

    (a) (SELECT customerName FROM depositor) 

            UNION 

          (SELECT customerName FROM borrower)

    (b) (SELECT DISTINCT customerName FROM depositor)

            INTERSECT

          (SELECT DISTINCT customerName FROM borrower)

    (c) (SELECT DISTINCT cusomerName FROM depositor)

            EXCEPT

         (SELECT cusomerName FROM borrower)

Q3. Consider the following schema of a Banking Application:

        (i)   customer(customerName, customerStreet, customerCity)

        (ii)  branch(branchName, branchCity, Assets)

        (iii) account(accountNumber, branchName, accountBalance)

        (iv) depositor(customerName, accountNumber)

Write a SELECT statement using a sub query that can be used to find the number of accounts each and every customer have with the bank.

Answer:

SELECT cust.customerName,

        (SELECT count(*) FROM account acc, depositor dep

        WHERE acc.accountNumber = dep.accountNumber AND

                       dep.customerName = cust.customerName) as Num_of_Accounts

FROM customer cust

Q4. Write DDL commands that can implement integrity constraints for the following:

        (i)   No two accounts can have the same account number

        (ii)  Every account number in the depositor relation must have a matching account number in the account relation

        (iii) The balance attribute of account table should not be null and can hold only value greater than zero (0)

Solution:

The following DDL commands create three relations belong the a database created for a banking application:

(a)  CREATE TABLE branch(branch_name CHAR(15), branch_city CHAR(30), assets NUMERIC(16,2), PRIMARY KEY(branch_name));

(b)  CREATE TABLE account(account_number CHAR(10), branch_name CHAR(15), balance NUMERIC(12,2), PRIMARY KEY(account_number), FOREIGN KEY(branch_name) REFERENCES branch, CHECK(balance>=0));

(c) CREATE TABLE depositor(customer_name CHAR(20), account_number CHAR(10), PRIMARY KEY(customer_name, account_number) FOREIGN KEY(customer_name) REFERENCES customer, FOREIGN KEY(account_number) REFERENCES account)

^{TUTORIAL IV from UNIT 4}

Q1. Apply Armstrong's Axioms for FD's.

Solution: Armstrong Axioms

Q2. Illustrate the types of Functional Dependency.

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant.
i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant set {roll_no, name}
Similarly, roll_no → roll_no is also an example of trivial functional dependency. 

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no
Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of {roll_no, name} 

3. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not dependent on each other.
i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional dependency.

For example,

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18
45	abc	19

Here, roll_no → {name, age} is a multivalued functional dependency, since the dependents name & age are not dependent on each other(i.e. name → age or age → name doesn’t exist !)

4. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant.
i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional dependency  

For example,

enrol_no	name	dept	building_no
42	abc	CO	4
43	pqr	EC	2
44	xyz	IT	1
45	abc	EC	2

Here, enrol_no → dept and dept → building_no, 
Hence, according to the axiom of transitivity, enrol_no → building_no is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

Q3. Evaluate 1NF, 2NF, 3NF and BCNF with an example.

Solution: Normalization of Databases using Normal Forms

Q4. Find the highest Normal Form of the relation F of functional dependencies for a relational schema R(A, B, C, D, E) F.D.=(A-->BC, CD-->E, B-->D, E-->A)

Reference: Finding the highest Normal Form of a Relation

^{TUTORIAL V from UNIT 5}

Q1. Elaborate different States of Traction with a neat sketch.

Q2. Elaborate Testing of Serializability and Precedence Graph of a Schedule.

Q3. Elaborate the following:

        (i) Lock        (ii) Time Stamp based Protocol        (iii) Validation based Protocol

Q4. Illustrate Shared and Exclusive Locking and 2-Phase Locking Protocol.

Continue reading →

The High Level Conceptual Model - ER Model

The ER model describes data as entities, relationships and attributes.  The basic concept introduced in ER model is an entity, which is a thing or object in the real world with an independent existence.  An entity may be an object with a physical existence (for example, a particular person, car, house or employee) or it may be an object with a conceptual existence (for instance, a company, a job or a university course).

Each entity has attributes - the particular properties that describe it.  For example, an EMPLOYEE entity may be described by the employee's name, age, address, job and salary.  Each entity (row in a table) will have a value for each of its attributes.

Attribute Types:

Attributes that define an entity may be classified into any one of the following types:

1. Simple or composite
2. Single-valued or multi-valued
3. Stored or Derived

Composite attributes are attributes that can be sub-divided into multiple simple attributes.  For example, the Address attribute of the EMPLOYEE entity can be subdivided into Stree_Address, City, State and Zip.  On the other hand, attributes that can be divisible further are called simple or atomic attributes.

Single Vs Multi-valued Attributes:

Attributes that can hold single value for describing an entity are called single-valued attributes.  Most of the attributes of an entity are of this type.  For example, age is a single-valued attribute of a person.  In some cases, an attribute can have a set of values for the same entity - for instance, qualification attribute of a person.  

One person may not have any qualification, another person may have obtained one degree, and a third person may have two or more degrees; therefore, different people can have different number of values for the qualification attribute.  Such attributes are called multivalued attributes.  A multivalued attributes may have lower and upper bounds to constrain the number of values allowed for each individual entity.

Stored Vs Derived Attributes:

In some cases, two (or more) attribute values are related - for example, the age and birth_date of a person.  For a particular person entity, the value of age can be determined from the current (today's) date and the value of that person's birth_date.  The age attribute is hence called a derived attribute and is said to be derivable from the birth_date attribute, which is called a stored attribute.

Some attribute values can be derived from related entities;  for example, an attriute number_of_employees of a DEPARTMENT entity can be derived by counting the number of employees related to (working for) that department.

Null Values:

In some cases, a particular entity may not have an applicable value for an attribute.  For example, the Apartment_Number attribute of an address applies only to addresses that are in apartment buildings and not to other types of residences, such as single-family homes.  Similarly, a College_Degree attribute applies only to people with college degrees.  For such situations, a special value called NULL is introduced.  The meaning of the value NULL is not applicable in this context.

An address of a single-family home would have NULL for its Apartment_Number attribute, and a person with no college degree would have NULL for College_Degrees.  NULL can also be used if we do not know the value of an attribute for a particular entity.  For example, if we do not know the home phone number of a person, then it indicates that the data is unknown.  

Entity Types and Keys

A database usually contains group of entities that are related to each other.  Each entity in a database refers to the table used for storing the details about a particular entity in the real world.  An entity is described using a name, a set of attributes and one or more constrains to be imposed on the entity.

An entity type refers to the schema used for describing the structure of an entity in a database.  It defines a collection (or set) of entities that have the same attributes.  For example, the employee entity in a company database has the schema that defines the structure for storing the information of hundreds of employees.  Each employee record in the employee table share the same set of attributes, but each entity (row) has its own value(s) for each attribute.

Keys are nothing but attributes (one or more) of an entity that distinguishes tuples within a given relation based on the values that are stored in them collectively.  That is, the values of attributes forming the key uniquely identify the tuple in a relation.  In other words, no two tuples in a realation are allowed to have exactly the same value for all attributes used for forming the key.  

A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation.  For example, the customer_id attribute of the relation customer is sufficient to distinguish one customer tuple from another.  Thus customer_id is a super key.  Similarly, the combination of customer_name and customer_id is a superkey for the relation customer.  But, the customer_name attribute of customer is not a super key, because several people might have the same name.

Candidate key is a super key for which no proper subset is a super key. A candidate key is also known as minimal super key.  It is possible that several distinct sets of attributes could serve as a candidate key.  For instance, in customer relation, both {customer_id} and {customer_name, customer_street} are candidate keys.  Where as {customer_id, customer_name} is not a candidate key, since the attribute customer_id along is a candidate key.

Primary key is a candidate key that is chosen by the database designer as the principal means of idneityfing tuples within a relation.  A key (whether primary, candidate or super) is a property of a relation that imposes certain constraint on the entire realtaion.  It makes sure that no two individual tuples have the same value on the key attributes at the same time.

The primary key must be chosen such that its attribute values are never, or very rarely, changed.  For instance, the address field of a person should not be part of the primary key, since it is likely to change.  Social-security numbers (SSN), on the other hand, are guaranteed to never change.  Similarly, Unique Identifiers (UID) generated by enterprises may be used as a primary key of a person within that particular organizataion.  

A foreign key in a relation is a super key of antoher realtaion with the help of which realtionship among the tuples of those two realations can be specified.  For instance, a relation schema, say r1, may include among its attributes the primary key of antoher relation schema, say r2.  This attribute is called a foreign key from r1, referencing r2.

Here is an example of foreign key:  the attribute branch_name of Account_schema is a foreign key from Account_schema referencing Bracnch_schema, since branch_name is the primary key of Branch_schema.  

Schema Diagram:

It is customary to list the primary key attributes of a relation schema before the other attributes; for example, the branch_name attribute of Branch_schema is listed first, since it is the primary key.

A database schema, along with primary key and foreign key dependencies, can be depicted pictorially, by schema diagram.  Here is a schema diagram for the banking enterprise:

In a schema diagram, each relation appears as a box, with the attributes listed inside it and the realation name above it.  If there are primary attributes, a horizontal line crosses the box, with the primary attributes listed above the line.  Foreign key dependencies appear as arrows from the foreign key attributes of the referencing relation to the primary key of the referenced relation.

Continue reading →

Database Design using Relational Model

A data model is a collection of conceptual tools used for describing data, data relationships, data semantics, and consistency constraints.  The most widely used data model introduced for designing a database system is relational data model.

The relational data model uses a collection of tables to represent both data and the relationships among those data.  A relational database is a database system that works on the principle defined in relational model.  

A relational database consists of a collection of tables, each of which is assigned a unique name.  Each table contains records of a particular entity and hence a table is also called as entity set.  Each table has multiple columns, and each column has a unique name.  The columns of the table correspond to the attributes of the record-type.

A row in the table represents a relationship among a set of values that belong to a particular entity.  Since a table is a collection of such relationships, there is a close correspondence between the concept of table and the mathematical concept of relation, from which the realational data model takes its name.

The Entity-Relationship (E-R) Model

E-R Model is a high-level conceptual data model, which is widely used in database design.  The E-R data model is based on the perception of the real world that consists of a collection of basic objects called entities, and of relationships among those objects.

An entity is a "thing" or "object" in the real world that is distinguishable from other objects.  For e.g., each person is an entity, which is tangible and bank accounts are entities that are intangible.  Entities are described in a database by a set of attributes.

A relationship is an association among several entities.  For e.g., a depositor relationship associates a customer with each account she/he has.  In addition to entities and relationships, the E-R model represents certain constraints to which the contents of the database must conform.

As an illustration, the above E-R Diagram represents the part of a database used in banking system with two entities namely customer and account.  Each of these entities have attributes of its own.  The attributes customer_id, customer_name, customer_street, customer_city are properties of a customer entity.  Similarly, account_number and balance describe one particular account in a bank and hence they become the attributes of the account entity.  The above E-R diagram also shows a relationship depositor between the entities - customer and account.

Database Design Process

The first step in database design is requirements collection and analysis.  During this step, the database designer interviews prospective database users to understand and document their data requirements.  The result of this step is a concisely written set of users' requirements.  These requirements should be specified as detailed as possible.

In parallel with specifying the data requirements, it is useful to specify the functional requirements of the application that will operate on data to be stored in the database.  These consist of user-defined operations (or transactions) that will be applied to the database, including both retrievals and updates.

Once the requirements have been collected and analysed, the next step is to create a conceptual schema for the database, using a high-level conceptual data model.  This step is called conceptual design.  Conceptual schema describes the data identified during analysis phase, using concepts provided in high-level data model such as entity types, relationships and constraints.

The higher-level data model such as E-R Model doesn't include implementation details, and hence they are easier to understand.  It can be used as a communication tool to communicate the database design with the non-technical users.  It can also be used as a reference to ensure that all user's data requirements are met and that the requirements do not conflict.

During or after the conceptual schema design, SQL Statements such as DDL and DML can be used to specify the high-level user queries and operations identified during functional analysis.  This phase of design process is called application program design.  This also serves to confirm that the conceptual schema meets all the identified functional requirements.

Logical and Physical Design

The next step in database design is the actual implementation of the database, using a commercial DBMS.  In this stage, the conceptual schema is transferred from the high-level data model into the implementation data model.  This step is called logical design or data model mapping; its result is a database schema in the implementation data model of the database.

The last step is the physical design phase, during which the internal storage structures, file organisations, indexes, access paths, and physical design parameters for the database files are specified.  This physical design is done by DBMS with the help of stored data manager, a functional component of the Database Management System.

In parallel with these activities, application programs are designed and implemented as database transactions corresponding to the high-level transaction specifications.

Continue reading →

Parallel and Distributed Database Systems

The architecture of a database system is greatly influenced by the underlying computer system on which it runs.  Generally, databases are stored and managed on computers having any one of the following three architectures:

1. Server Architecture
2. Parallel Architecture
3. Distributed Architecture

Server Architecture

In server architecture, computers are connected to a network that consists of one server system and multiple client systems.  In this architecture, functionality of the system is split between a server and multiplie clients.  The server satisfies the requests generated by client systems.  This division of work has led to the concept of client-server database systems.

Functionalities provided by client-server database systems can be broadly divided into two parts - the front end and the back end.  The front-end of a database system consists of tools such as SQL user interface, forms interfaces, report generation tools, and data mining and analysis tools.  Where as the back-end manages database related taks such as access structures, query evaluation and optimization, concurrency control, and recovery.  

The communication betwen the front end and the back end generally takes place through a common languaged called Structured Query Language (SQL).  Standards such as ODBC and JDBC were also developed to interface clients with servers.

Systems that deal with large numbers of users adopt a three-tier architecture, in which the front end is a Web browser which talks to an application server.  The application server, in turn, talks to the database server for storage and retrieval of data from the centralized database.

Parallel Architecture

In parallel archiecutre, processing takes place in multiple CPU of the same computer,  or multiple processors of various computers that run parallely.  Parallel processing with in a computer system allows database-system activities to be speeded up, allowing fast response to transactions, as well as more transactions per second.  Queries can also be processed in a way that exploits the parallelism offered by the underlying computer system.  This led to the development of parallel database systems.

Parallel systems improve processing and I/O speeds by using multiple CPUs and disks in parallel.  In parallel processing many operations are performed simultaneously, as opposed to serial processing, in which the computational steps are performed sequentially.  There are two main measures of performance of a database systems that makes use of parallel processing: through-put and response time.

Through-put refers to the number of taks that can be completed in a given time interval and response time refers to the amount of time it takes to complete a single task fromt the time it is submitted.  A system that proesses large transactions can imporve response time as well as throughput by performing subtaks of each transaction in parallel.

There are several architecture models for parallel machines.  The following are four architectures in which multiple processors are running parallely, and the resources such as memory, processor and databases are shared among them in four different ways:

1. Shared Memory Architecture:  In this architecture, all the processors share a common memory.
2. Shared Disk Architecture:  All the processors share a common set of disks and the shared-disks connected to this system are called clusters.
3. Shared Nothing Architecture:  In this kind of architecture, the processors share neither a common memory or common disk among themselves.
4. Hierarchical Architecture:  In this model of paralle processing, a hybrid architecture, which makes of more than one of the above mentioned architecture.

Distributed Architecture

In distributed architecure, the database is stored on several computers.  The computers connected to the distributed environment communicate with one another through various communication media, such as high-speed networks or telephone lines.  They do not share main memory or disks.  The computer may also vary in size and function, raning from workstations up to mainframe systems.  The computers in a distributed system are referred to as sites or nods.  

Distributed architecture looks similar to that of Shared Nothing Architecture in parallel systems.  The main differences between distributed architecture and  shared-nothing parallel architecture are the following:

1. Distributed systems are typically geographically separated
2. They are separately administered and
3. They have a slower interconnection

Another major difference is that, in a distributed database system, we differntiate between local and global transactions.  A local transaction is one that accesses data only from sites where the transaction was initiated.  Whereas, a gloabl transaction, either accesses data in a site different from the one at which the transaction was initiated, or accesses data in several different sites.

Continue reading →

Database Languages and Data Models

Structured Query Language (SQL) is the language understood by DBMS for defining and manipulating data in a database.  The statements used in SQL are non procedural, i.e., SQL statements require a user to specify what to be done on data without specifying how to get the work done on the data.

The statements provided in SQL can be categorized into four major groups, namely:

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML) and
3. Data Control Language (DCL)
4. Transaction Control Language (TCL)

The first set of statements defined in SQL are for specifying the database schema (structure) and is called Data Definition Language (DDL).  Using DDL, we can specify a database schema by defining tables, integrity constratins, assertions etc.  For instance, the following statement defines a table named account:

create table account(account_number char(10), balance integer);

Execution of the above DDL statement will create a table structure named account in the database for storing the details about bank accounts such as account_number and balance.  In addition, it updates the data dictionary (also called system catalogue) with the metadata of the account table.  

 

The data dictionary is considered to be a special type of database, which can only be accessed and updated by the database system itself before reading or modifying the actual data stored in the database.

 

Here is another example for DDL Statement that defines the department table:

 

create table department(dept_name char(20), building char(15), budget numeric(12,2));

The above DDL statement is able to create the department table with three columns: dept_name, building, and budget, each of which has a specific data type associated with it.

The DDL is also used to specify additional properties of data stored in the database.  For example, suppose the balance on an account should not fall below Rs. 500, it can be specified on the database by imposing constrains called consistency constraints.

 

The data values stored in the database must satisfy the consistency constrains imposed on the field in which it is stored.  The database system checks these constraints every time the database is updated.

Data Manipulation Language (DML):

DML refers to the set of SQL Statements that enable users to access or manipulate data organized in the database.  There are two types of DML Statements namely Procedural DML and Declarative DML:

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

Some of the operations that can be done using DML include the following:

• Insertion of new information into the database
• Retrieval of information stored in the database
• Modification of information stored in the database
  
• Deletion of information from the database

A query is one of the DML statements used for requesting the retrieval of information from a database.  A query takes as input several tables (possibly only one) and always returns a single table.

Here is an example of an SQL query that finds the names of all instructors in the History department:

select instructor.name from instructor where instructor.dept_name = "History";

The query specifies that those rows from the table instructor where the dept_name is History must be retrieved, and the name attribute of these rows must be displayed.  More specifically, the result of executing this query is a table with a single column labeled name, and a set of rows, each of which contains the name of an instructor whose dept_name, is History.

Queries may involve information from more than one table.  For instance, the following query finds the instructor ID and department name of all instructors associated with a department with budget of greater than Rs. 95,000:

select instructor.ID, department.dept_name from instructor, department where instructor.dept_name = department.dept_name and department.budget > 95000;

Data Models:

Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constrains.  A data model provides a way to describe the design of a database at the physical, logical and view levels.  The following are some of the data models used in database systems for describing and organizing data:

1. Network Data Model
2. Hierarchical Data Model
3. Relational Data Model
4. Object Data Model

Historically, the network data model and hierarchical data model preceded the relational data model.  These models were tied closely to the underlying implementation, and complicated the task of modeling data.  The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model.

Relational Data Model uses a collection of tables to represent both data and the relationships among those data.  Each table has multiple columns, and each column has a unique name.  Tables are also known as relations.

The relational model is an example of a record-based model.  Each table in the relational database contains records of a particular type.  Each record type defines a fixed number of fields, or attributes.  The columns of the table correspond to the attributes of the record type.

Object Oriented Programming (OOP), especially programming using C++ and Java has become the dominant software-development methodology.  This led to the development of an object-oriented data model that allows data to be stored as objects in the database.

Continue reading →

Components of a Database Management System

 A DBMS is a complex software system, which is made up of a set of software component modules.  The following figure shows various functional modules of a DBMS:

Fig. Component Modules of a DBMS and their interactions

The above figure showing the components of DBMS can be grouped into two parts: upper part and lower part.  Upper part includes various users of the database system and the interfaces used by them to interact with the DBMS.  Whereas the lower part of the figure shows various functional modules of the DBMS that are responsible for storage of data and processing of transactions (modication or updation of data).

At physical level, databases are stored in the form of files on hard disk.  Access to the hard disk is controlled by the Operating System (OS), and hence DBMS has to make use of the services provided by OS for read/write access of data from databases.  

In DBMS, the task of storage and retrieval of data from the database is handled with the help of Stored Data Manager, a higher-level module of the DBMS.  Stored data manager makes use of basic operating system services for carrying out low-level read/write operations between the disk and main memory.

Moreover, many DBMS have their own buffer management module to schedule disk read/write, because management of buffer storage has a considerable effect on the performance of storage and retrieval of data from database.  

Finally, the runtime database processor module is responsible for performing the following tasks:

1. Executes the privileged commands
2. Takes care of the executable query plans
3. Runs the canned transactions with runtime parameters

It does the above mentioned activities with the help of system catalog and updates it with statistics.

Inerfaces and Components in the Upper Part of DBMS:

Users of databases make use of some form of interface to interact with DBMS directly or indirectly.  The Database Administrator directly work with the DBMS and interact with it through commands - DDL Statements or Privileged Commands.  DDL Statements given by a DBA will be processed by the DDL Compiler to structure the database (schema) by storing the metadata in the System Catalog.

The System Catelog also called as Data Dictionary includes information such as the names and sizes of files, names and data types of data items, storage details of each file, mapping information among schemas, and constrains.

Casual Users work with the interactive interfae to formulate queries that can be processed by the DBMS with the help of Query Compiler and Query Optimizer.     Query compiler parses and validates the correctness (syntax) of the queries entered by the casual user and compiles them into an internal form.  The internal query is then subjected to query optimizer for query optimization.

The query optimizer is concerned with the rearrangement and possible reordering of operations, elimination of redundancies, and makes use of efficient search algorithms during the execution of SQL queries.  It consults the system catalog for statistical and other physical information about the stored data and generates executable code that performs the necessary operations for the query.

Application Programmers write programs in host languages such as C, C++ or Java that are submitted to the precompiler.  The precompiler extracts DML commands from an applicaiton program written in a host programming language.  These commands are sent to the DML Compiler for compilation into object code for database access.  The rest of the program is sent to the host language compiler.

The object code generated by the DML Compiler and the rest of the program are linked, forming a canned transaction whose executable code includes calls to the runtime database processor.  Canned transactions are executed repeatedly by parametric users via PCs or mobile apps; these users simply supply the parameters to the transactions.  Each execution is considered to be a separate transaction.

Role of a DataBase Administrator (DBA)

One of the main reasons for using DBMS (back-end) is to have central control of data that can be stored and retrieved from the disk by the application programs (called frond-ends).  A person who has such central control over the design and management of data in databases is called DataBase Administrator (DBA).  Some of the functions of DBA include the following:

1.  Defining or Modifying Database Schema:   It is the role of the DBA to create the database schema (structure of data to be stored in the database) by executing a set of commands called DDL - Data Definition Language.  He can also make modifications in the existing schema to reflect the changing need of the organization.

2.  Granting or Revoking 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 database that the DBMS consults whenever someone attempts to access the data in the system.

3.  Takes Care of the Routing Maintenance:  Some of the maintenance work done by the DBA are as follows:

• 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 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.

Continue reading →