3 Database Management System
Aditya Tripathi
I. Objectives
The main objective of this module is to:
• Introduce the concepts of data, information and knowledge.
• Introduce to the students, the meaning and characteristics of Information Retrieval(IR).
• Provide definitions and describe various concepts, components and designing Issues of IR system.
• Introduce to the students features and importance of DBMS and brief overview of SQL.
• Familiarize the reader about different data models.
• Introduce the functions and designing criteria of IR system for storing and retrieving the information.
• Brief students about various standards and protocols which are used in IR.
II. Learning Outcome
After reading this module:
• The students will gain the knowledge of Data, Information, knowledge also understand the importance of information in the era of ICT
• The students will gain the knowledge of IR and also understand the benefits and issues information retrieval system for accessing and maintaining information.
• The students will gain knowledge of database, it’s properties and types(i.e. Hierarchical Database, Relational Database, etc.)
• The reader will gain the knowledge of ACID (Atomicity, Consistency, Isolation, and Durability) property, which is very useful in the processing of database transactions.
• The students will understand the various data models. For instance E-R model to describe the data and relationship with other entities, etc.
• The reader will gain the knowledge of different database languages( like DDL,DML,DCL, etc) and their uses.
• Student is updated about various information storage and retrieval systems (e.g. user interface system, Query processing system, Database model system, etc) for storage and retrieval of information.
• The reader will understand “what exactly Retrieval Status Value indicates?” and different methods/models which are used for evaluating the retrieval status value.
• The reader will also gain the knowledge of vector space model, probabilistic model, Boolean model which are used in IR.
III. Structure
1. Introduction
1.1 Database Management Systems
1.2 Advantages of DBMS’
2. ACID Property
3. Data Models
3.1 Object-based logical model
3.2 Record-based logical model
4. DBMS Languages
4.1 Data Definition Language (DDL)
4.2 Data Manipulation Language (DML)
4.3 Data Control Language (DCL)
4.4 Brief Overview about Structured Query Language (SQL)
5. References
1. Introduction
A database is the large, persistent, integrated collection of dynamic data that provides some operations to describe, establish, manipulate and access this data. ‘Data’ is derived from the latin word ‘datum’ meaning (thing) ‘given’ [wikipedia]. The main aim of a database is recording and maintaining information. The Chambers Science and Technology Dictionary defines the database as “a collection of structured data independent of any particular application”. The Macmillan Dictionary of Information Technology defines database as “a collection of interrelated data stored so that it may be accessed by users with simple user-friendly dialogues.” Traditional database are organized by fields records and file;
File, Records and Fields:
• File – A file is a collection of records. A file contains many records. In computer data is stored inside a file.
• Record- It is a group of related information. Record is the unit of information in a database. It is generally what users want while searching a database. Each unit of information in a database is known as record. For example, a book card of library catalogue which describes title, author subject etc. of the book. A record is composed of fields and subfields.
• Field- It is the collection of simple data values like integer, real text etc. In other words we can say that field is a pointer to record. Fields are the elements of information or particular segments which form the records. The items described in the book card of a library catalogue such as title, author etc. are the examples of field.
For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address and telephone number.
Hence, Database is a collection of structured records. However, the structure of a record is predefined so that data can be easily searched or accessed. Normally, records in database are arranged in a systematic order and bear unique identification. A record contains structured data. The format of the data stored in the record is predefined and uniform across all the records. This helps in locating the data in the records as well as in the database. The data structure is defined through field definition while designing the database. The fields contain the data and hence they are the leaf element in database structuring. The simplest form of a database is a Table.
Fig.1: Sample Table
1.1 Database Management Systems
What is DBMS (Database Management System)?
A database management system (DBMS) is a computer program designed to manage a database, a large set of structured data and run operations on the data requested by numerous users. It is a collection of interrelated data and a set of programs to access or manipulate data. It consists of related data within an implicit meaning and hence is a database. A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. According to the ANSI/SPARC DBMS Report (1977), a DBMS should be envisioned as a multi-layered system:
1.2 Advantages of DBMS
i. Data independence: The DBMS can provide an abstract view of the data insulated application codes from details of data representation and storage
ii. Efficient data access: DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiency. This feature is especially important if data is stored on external storage device.
iii. Data integrity and security: DBMS can enforce integrity constraints on the data, if data is accessed through DBMS. It also enforces access controls that govern what data is visible to different classes of users.
iv. Data administration: A DBMS facilitates maintenance and administration task for a large collection of data shared by several users. A good database administrator can effectively shields end-users from the chores of fine-tuning the data representation, periodic back-ups, etc.
v. Reduced application development time: A database management system can reduce the application development time of database as well as maintenance of huge collection of data.
2. ACID Property
ACID stands for Atomicity, Consistency, Isolation, and Durability. In context of databases, ACID is the property that guarantees the reliable processing of database transaction. This property is defined by Jim Gray in 1970s and he also developed the technologies for automatic achievement of these properties.
• Atomicity: It is an all-or-none proposition
• Consistency: It guarantees that database never leaves the transaction in half-finished state.
• Isolation: It keeps transaction separated from each other until they are finished.
• Durability: It guarantees that the database will keep track of pending changes in such way that the server can recover from an abnormal termination.
The database developers always keep some rules and characteristics in their mind while developing the transaction system. The developers of the components that comprise the transaction are assured that these characteristics are in place. They do not need to manage these characteristics themselves.
3. Data Models
Data Model consists of components which describes data, data relationships, data semantics and constraints. There are basically two types of data models which are further divided into different models:
3.1 Object-based logical model
a) Entity-relationship model
The Entity – Relationship model is based on entities and their relations. An entity can be anything living or non-living. In other words, an entity is any kind of object. For example, table, chair, students, teachers etc. Each entity in the world has certain characteristics or features which are known as Attributes. Same attributes can be manifested in more than one entity or objects. Such group of entities with same kind of attributes make a Set of Entities. For example, a class room of students is a Set of Entities as it carries students who have same kind of competency level, they study same subjects and they are offered same course. Each student of the class will also have a role number, name, age and so on.
Attributes are expressed as fields of a database. These attributes of the students could be simple attributes as it holds plain value like the name of a person or the class role number. However, some attributes may have more than one part for example, the Name attribute of a student will have Surname and Forename. Such attributes are known as Composite attributes. The Age attribute of a student can be calculated from his Date of Birth attribute. Such attributes which can be automatically calculated are known as Derived attributes.
The entities are related with each other and the basis of relation are attributes. For example, Highschool is a class of a school which has other classes like, 6th, 7th, Intermediate and so on. Hence, the class Highschool is an entity. Each student of the class Highschool is also an entity. Thus students are part of class Highschool and hence there is a relation between two entities class and student. When the relationship of entities are same for example all the students of class Highschool are part of class Highschool and this is the basis of relation among students, it forms a Relationship Set. Hence, the Relationship Set is same of type of relation based on same kind(s) of attributes among the entities.
In a relationship there may be one to one or many participants. The numbers of participants define the Degree of relationship. If there are two sets of participants they can be related from set one to set two by one-to-one, one-to-many, many-to-one or many-to-many.
Following is the Entity-relationship model of national parks.
Fig.3: Entity-relation model for national parks database (source. Database Management System by R.G. Healey)
b) Object-oriented model
In this model information is organized as objects where each object has a number of attributes. An object can be an entity- physical or abstract. Each object has certain attributes and functions associated with it.
The concept of Object-oriented approach has been derived from the real world. It conceptualizes the form of Class with certain attributes. Objects to be the member of these classes and hence, inheriting the properties of the class. The whole idea of data storage is to create a kind of hierarchy of classes and subclasses holding entities. An object can be part of a class or more than one class hence showing single inheritance or multiple inheritances.
The advantage of this approach is that data can be reused and the attributes and functions can be inherited. One need not define the same thing every time. Only the object should be referred to the class which has the attribute. Attributes can be simple value, complex value reference to other objects or methods.
Following is the example of Object-oriented model in a library depicting different types of users and their privileges
Fig.4: Object Oriented Model of Patrons in a Library
In the given figure there is a class named Patron. The Patron class has privileges given in black boxes. There are three subclasses to this class Patron they are, Teacher, Research Scholar and Student. An instance or member of class Teacher can borrow 10 books at a time where as members of class Research Scholar and Students can borrow 5 and 2 books respectively. However, the members of all three subclasses inherit the privileges of class Patron as this class is a super class.
c) Functional model
The Functional model of database has three layered architecture. The user interacts with the top level of the system which contains the procedures and routines. In this layer there are several functions defined that actually manipulates the stored data. This layer may have several inter related files which form the basis of a Database Management System (DBMS). Applications are built over this layer. Beneath this the next layer is Structure of the Database. It includes Database definition and used data types associated with the attributes or fields. This layer is responsible of structure of records and data storage. The last layer of the system is Content. Content is the soul of the system which is stored in fields of records. The content of the field can be text, numeric, alpha-numeric or the object as defined in the structural layer.
Fig.5: Functional Model
3.2 Record-based logical model
a) Relational model
Fig.6: Database of Book Circulation in a Library using Relational Model
In the Relational Model of database there are different tables. These tables store data about different entities. In the above example there are two tables, one storing data about Book and other storing data of User. Each book stored in the Book table is uniquely identified by Book Id field. Similarly, each user is uniquely identified by User Id. The third table is Circulation table. This table stores data of Book Id of an issued book and borrower’s User Id with Due date. Circulation table is connected with Book table using Book Id field and also connected with User table with User Id field. The Book Id field fetches the details of an issued book from Book table using the Book Id as key and fetches the borrower’s details using User Id as key from User table.
b) Hierarchical model
Hierarchical model is a way to store data in a database where records are stored in a hierarchy. The records contain fields and fields contain the data or the value of the field. Hierarchical model represents Parent child relationship. There may be one parent but there can be one or more than one child for a parent. This model represents that there is a single root through different branches that have emmerged and have parent child relationship.
Fig.7: Hierarchical Model in a University
c) Network Model
Network model generates a structure showing inter relationship of nodes irrespective of hierarchy. Though it looks like a hierarchical model but unlike hierarchical model it has one child and many parents relationship. In other words one node or a record can be linked with one or more records. In the following example of a library catalogue it is shown that one book can be written by more than one Authors. Further the figure shows that content of a book may belong to more than one subject. Similarly, an author can write book on different subjects or topics.
Fig.8: Network model showing relation of Authors and Books with subjects
4. DBMS Languages
There are different languages provided by database system to specify the database schema and to express the queries and updates of the database.
4.1 Data Definition Language (DDL)
It is a database language which specifies the schemas of the database.DDL can execute a statement as well as update the special set of tables called data dictionary or data directory. A data dictionary consists of metadata (data about data). Reading and modification of data is based on data dictionary.DDL basically defines the structure of the database and brings out the relationship between records and indexing strategies, forms the links between the logical and physical view of the data. Schema is the logical structure of database and subschema is the database utilized by the users for an application program. DDL is used to construct the subschema and more than one subschema may be operated by a single database at one time.
4.2 Data Manipulation Language (DML)
It provides a set of procedural commands to process the data. It also provides the linkage between logical view of data and its physical location. DML is used to access the data with its logical names rather than physical storage locations. The DML generally supports several high-level programming languages.
4.3 Data Control Language (DCL)
It is a subset of Structured Query Language that allows control access to database objects and data.
4.4 Brief Overview about Structured Query Language (SQL)
Structured Query Language (SQL) is a declarative programming language designed to create, transform and retrieve information from the database. It was developed by IBM in the early 70’s. It is used for creating and querying relational database management system. SQL uses a set of commands to manipulate the data in a database. It can insert, modify, and delete data within the database.
5. References
1. Choudhury, G.G., Introduction To Modern Information Retrieval
2. Robbins, Robert J. ,Database Fundamentals, Johns Hopkins University
3. Ramakrishnan, Raghu , Gehrke, Johannes and Derstadt, Jeff [et. al…], Database Management System: Solution Manual; 3rd edition, University of Wisconsin, Madison, WI, USA, Cornell University, Ithaca, NY, USA.
4. Ramakrishnan, Raghu and Gehrke, Johannes ,Databse Management System; 2nd edition, University of Wisconsin, Madison, WI, USA, Cornell University, Ithaca, NY, USA.
5. Silberschatz, Korth and Sudarshan, (1997), Database System Concepts
6. http://pic.dhe.ibm.com/infocenter/analytic/v2r1m0/index.jsp?topic=%2Fcom.ibm.discovery.es.ta. doc%2Fiiysalgstopwd.htm
7. http://pic.dhe.ibm.com/infocenter/analytic/v2r1m0/index.jsp?topic=%2Fcom.ibm.discovery.es.ta. doc%2Fiiysalgstopwd.htm
- http://www.comp.lancs.ac.uk/computing/research/stemming/general/
- Haithcoat, Tim, Relational Database Management Systems: Database Design and GIS, University of Missouri, Columbia.
- Healey, R.G., Database Management Sysytem
- http://en.wikipedia.org/wiki/ACID
- http://blog.sqlauthority.com/2007/12/09/sql-server-acid-atomicity-consistency-isolation- durability/
- http://www.techterms.com/definition/user_interface
- http://veegantechnologies.com/sequential-files/
- http://home.iitj.ac.in/~ramana/ch10-storage-2.pdf
- http://coronet.iicm.edu/dm/scripts/lesson06.pdf