Database Management System (DBMS)
Following section shall be covered in this article:
- Advantages/Disadvantages of DBMS
- Characteristics of DBMS
- DBMS Architecture
- DBMS Data Models
- Entity Relationship Model
- Database Schema
- Data Independence
Database is collection of interrelated data where data can be easily accesses, managed and updated. For example, consider office department in school maintains details of the student such as name of the student, age, address, class etc. These all records are related with student stored in a database.
Database management System is collection of data and set of programs to access the data. It allows creation, definition and manipulation of database. DBMS provides protection and security to database. Some examples for popular dbms are MySQL, Oracle, Sybase, Microsoft Access and IBM DB2.
Applications of DBMS
- Banking: Banks uses database for customer accounts, loans and other transactions.
- Airlines: Airlines uses online database for reservation, arrival and departure of the planes ,schedule information etc.
- Education: Schools, colleges and universities use database for course details ,registration, examination details ,result etc.
- Credit card transactions: It is used to keep track of purchases on credit cards to generate statements.
- Telecommunications: Telecommunication department uses database to store information about telephone numbers, details of calls, generating bills etc.
- E-commerce: It uses online databases for buying and selling of products, online transaction processing, internet marketing etc.
- Finance: It uses database for storing information such as sales, stocks, bonds etc.
- Sales: it uses databases for storing product details, transaction and customer details.
- Human resources: Organizations use databases for storing information about employees , salaries, tax, salary checks etc.
Advantages of DBMS
- Controlling Data Redundancy: if database have more than one records of same type then it is called data redundancy. In DBMS, all data is stored in one single database file and that recorded data is placed in the database.
- Data Sharing: Data can be shared by authorized users of the organization among multiple users. Many users can be authorized to access the same data simultaneously. The remote users also can share the same data.
- Enforcing Data Integrity: In database approach, data integrity is much easier. In database, data is stored in the tables. A single database contains multiple tables which is easy to retrieve and update the data.
- Data Security: Data security is the protection of the database from unauthorized access. The DBMS ensures access of the database through authorized channel. To ensure security, DBMS provides security such as by giving user name and passwords.
- Ease of application development: The programmer needs to develop applications according to user specification. The other issues such as data integrity, security etc are handled by DBMS itself which makes application development easier.
- Multiple user interfaces: DBMS provides different types of user interfaces such as application program interfaces, graphical user interfaces which includes form style and menu driven interfaces. Form style interface provides user interaction with forms and menu driven interface provides user interaction with list of options called as menus.
- Backup and recovery: Most of the DBMS’s provide backup and recovery subsystems which creates automatically backup of data from hardware and software failures and restores the data if required.
- Data Independence: Data Independence keeps data separated from all programs that make use of it. In DBMS, database and application programs are separated from each other and we can easily change the database structure without modifying the application program.
- Reduced Maintenance: It can be easily maintainable due to centralized nature of the system.
Disadvantages of DBMS
- Cost of Data Conversion: When computer file based system replaced with database system, it must be converted to database file. It’s difficult to convert data file into database, so for this we have to take help of database designers along with application programmers and also we should take help of software which costs lot of money for developing the software.
- Complexity: Database system creates additional complexity and requirements. DBMS is extremely complex piece of software. The operation of database system with several users and databases is quite costly and demanding.
- Cost of Hardware and Software: To run DBMS software, we need high speed of data processor and large memory size and DBMS software also too high.
- Size: DBMS is large piece of software due to its complexity and breadth functionality. It occupies large space of disk and large memory to run the efficiently.
- Cost of staff training: DBMS is complex database system which is required to train the users all levels including programming, application development etc for which organization has to pay lot of amount for training staff to run the DBMS.
- Higher impact of failure : In most of the organizations, all data stored in a single database. Since all users and applications rely on the availability of the DBMS. If database is damaged due to electric failure or database corruption data may lost forever.
Characteristics of DBMS
- It reduces complex relationships between data.
- Enforces user defined rules and ensures data sharing.
- It provides different interfaces which user can manipulate the data.
- Integrated database helps in understanding relationship between data stored in different applications.
- It has automatic backup and recovery procedures.
- Provides clear and logical view of process that manipulates data.
- Changes made in the database schema should not affect the other levels.
- Controls data redundancy and control over security.
- It contains ACID properties which stands for Atomicity, Consistency, Isolation and Durability which maintains data in healthy state in case of failure.
DBMS is used by many users for various purposes. In large organizations, many people are involved in design, use and maintenance of database with hundreds of users. The people whose job is to maintain day-to-day use of large database, we call them as actors on the scene. Some of them are described as follows :
- Database Administrators(DBA): In database environment, the primary resource is database itself and the secondary resource is DBMS and related software. Administrating these resources is the responsibility of the database administrator. The DBA is responsible for authorizing access to the database, system license, monitoring its use, acquiring software and hardware resources as needed.
- Database Designers: these are responsible for identifying the data to be stored in a database for choosing structure to the database to represent the data. Database designers work on the design part of the database. Database designers communicate with database users in order to understand their requirements and create design as per user’s requirements. They decide what data should be kept and in what format data should be there, these are all decided by database designers. They interact with users and develop views of the database that meet requirements of users.
- End Users: End users are the people whose job is to access the database for querying, updating and generating the reports. There are several categories of end users: Casual end users access the database but they may need different information each time. Parametric end users make up sizable portion of the database and users. Sophisticated end users include engineers, scientists, business analysts and others familiarize with DBMS to implement their applications to meet their complex requirements. Standalone users maintain database by using readymade program packages which provide menu based or graphic based interfaces.
The basic client/server architecture was developed to deal with large number of PCs, work stations, database servers, web servers and other components are connected with network. The client/server architecture consists of many PCs and work stations which are connected via network .A client in this framework is a user machine which provides user interface and local processing.
The second approach i.e. two-tier architecture was taken by some object oriented DBMSs, where DBMS were divided between client and server in more integrated way. The server level may include data storage on disk pages, recovery and buffering of disk pages and other functions. The client level may include user interface, interaction with programming language compiler, recovery across multiple servers and other functions. It is simplicity and compatibility with existing system.
Three tier architecture adds intermediate layer between the client and server as shown below.
An end user sits on client tier i.e. web interface which is used by client to communicate with server through intermediate layer. All views are generated by applications which reside in the application tier.
The middle layer sometimes called application server or sometimes web server depending on the application. This tier is uses business roles that are used to access data from the server. For user application tier works as view of the database. The intermediate tier accepts requests from the client, processes the request and sends the commands to database server.
The database tier includes data management services which gives result for client via middle tier. Security is the best in the three tier architecture because the middle tier protects the database tier.
DBMS Data Models
Data Model is collection of concepts that can be used to describe the structure of the database. It describes relationship between different parts of the data. Data Models can be categorize according to types of concepts that describe structure of the database.
- Conceptual Data Model: It provides concepts that are close to many users which perceive the data.
- Physical Data Model: It provides concepts that describe how data is stored in the computer.
- Representational Data Model: It can hide details some details of data storage but it can be implemented on computer system directly.
Conceptual data model
is also known as high level data model that provide relationship between different entities. Conceptual data model is created by combining requirements from various sources like business analysts, business documents and end users. It is first step in constructing data model and which provides representation of business of the organization. It describes structure of the database and gives information of about main subject area of the organization. It comprises relationship between subject area and entities in the subject area.
Physical data model is also known as low level data model that describe how data is stored in the computer by representing information such as record formats, record orderings and access paths. It is used to create relationship between tables. It includes tables, columns, primary key, foreign key and relationship between tables. Database performance, physical storage etc are the important parameters of the physical model. An access path is structure that makes search for particular database records. It includes storage allocation of database system. An index is example of access path that allows direct access to data using index keyword.
Representational model is also known as implementation data model used frequently in traditional DBMSs. This model includes widely used relational data models such as network and hierarchical models. Representational model represent data by using record structures and hence are sometimes called record based data models .
Another new level called object data model group (ODMG) is representational data model which is closer t conceptual data model. They are particularly used in software engineering domain.
Entity Relationship Model
It is graphical representation of entities and their relationships to each other. An entity is a real world object that is distinguishable from other objects. It is frequently used for the conceptual design of database applications and much database design tools employ its concepts. It works around real world entity and association among them. It is relies on concepts of entities, attributes and relationships. It represents pictorial representation of entities and relationship between entities in the format called ER diagram. ER model describes data as entities, attributes and relationships as follows:
- Entity: It represents real world object ,such as employee or project that is described in the database.
- Attribute: It represents property of the entity, such as employee name, address or salary.
- Relationship: It represents an association between two or more entities, for example , relationship between a employee and a project.
An entity is real world object with an independent existence. It may be object with a physical independence such as person, car, employee etc or it may be an object with conceptual existence such as company, job etc.
Example for Entity
The properties of a entity is called as attributes, consider above example, STUDENT is an entity with properties name, student_number, class and student_address.
Types of Attributes:
- Simple attribute: The attributes which are not divisible are called simple attributes. For example, phone number is atomic value of 10 digits.
- Composite attributes: They can be divided into smaller parts which represent more basic attributes with independent attributes. For example, address can be divided into street, city, state, zip.
- Single valued attribute: This attribute contain single value for particular entity. For example, age is single valued attribute of a person.
- Multivalued attribute: It contain more than one values. For example, a person may contain more than one degree, phone number etc.
- Derived attribute: This attribute do not exist in physical database, but values are derived from other attributes present in the database. For example, age and birth date are attributes of a person. Age can be derived from data of birth.
- Null value: An entity may not have applicable value for an attribute. For example, apartment number applies to addresses that are in the apartment building and not to other type’s residences.
Relationship exists between two database tables when one table has foreign key that references to primary key of the other table and it is also associates between entities.
Following are types of relationships between tables.
- one-to-one relationship: In this type, a row in table A can have no more than one matching row in table B.
- one-to-many relationship: In this type, a row in table A can have more matching rows in table B but row in table B can have only one matching row in table A.
- many-to-many relationship: In this type, a row in table A can have many matching rows in table B and vice versa.
- many-to-one relationship: In this type, more than one entities from table A can be associated with at most one entity of table B but one entity from table B can be associated with more than one entity from table A .
The relational model represents the database as collection of relations. It is most popular data model in DBMS. In the relational model, all data must be stored in the form of tables and each relation consists of rows and columns. When a relation is thought of set of table values, each row in the table represents collection of related data values.
In the relational model, each row in the table represents real world entity or relationship. In this model, a row is called a tuple, a column header is called attribute and table is called a relation. Each row contains unique value and each column contains values from a same domain.
The description of database is called the database schema which is specified during database design and is not expected to change frequently. A displayed schema is called schema diagram. In relational database, the schema defines the tables, fields and relationships between fields and tables.
The schemas are generally stored in the data dictionary. The term often used to refer graphical representation of the database structure.
Schema diagram for database
Database schema is divided into two categories :
- Physical Database Schema: It is used to describe how data is stored in the secondary storage using database management system such as Oracle, RDBMS, Sybase etc.
- Logical Database Schema: It is data model expressed in terms of data management technology. It consists of table, attributes, their types and the constraints on the attributes if any.
The data in the database at particular moment is called database state. It is also called database instance. It is snapshot of database. It is also used to describe database environment including RDBMS software, table structure and other functionality. It is most commonly used when administrators describe multiple instances of the same database.
Data independence can be defined as capacity to change schema at one level of database system without having to change schema at the next higher level. There are two types of data independence:
- Logical data independence: It is capacity to change the conceptual schema without having to change external schemas or application programs. Logical data is data about database which stores information about how data is managed inside. We may change the conceptual schema to expand the database ,to change constraints, or to reduce the database .In the last, external schema refer to only the remaining data should not be affected.
- Physical data independence: It is capacity to change the internal schema without having to change the conceptual schema. Physical data independence is the power to change the physical data without impacting the schema or logical data. Physical data independence exists in most databases in which the exact location of data on disk, hardware details of storage encoding, compression, placement, merging of records and so on are hidden from the user.
Entity sets, Entity types, keys and Relationship types
Entity type is collection of entities that have same attributes. Each entity type is described by its name and attributes. Entity is an instance of entity type.
Types of Entities :
- Weak Entity: It is an entity that cannot be identified by its attributes. Its existence is depends on strong entity. It is indicated by double rectangle in the ER diagram. It contains partial key is represented by dashed underline.
- Strong Entity: It is an entity which has its own existence. It is represented by rectangle and contains primary key represented by underline. The primary key is the one of its attributes which uniquely identifies its member.
Keys are important part of relational database. They are used to identify relation between tables.
- Super key: It is set of attributes that identifies uniquely each record within relational database management system table.
- Candidate key: it6 is defined as set of fields from which primary key can be selected and can uniquely identify any database record without referring to any other data.
- Primary key: It is a key that uniquely identify each record in a table. Only one candidate key can be primary key.
Relationship types defines set of association among entity types. e.g. teaching.
Degree of Relationship Type: It is number of participating entity types. A relationship of degree one is called unary e.g. student, A relationship of degree two is called binary e.g. student, teachers, A relationship of degree three is called ternary e.g. student, teacher, subject.
Recursive Relationship: Each entity type that participates in a relationship type plays particular role in the relationship. A same entity participates more than once in different roles. E.g. Department plays role of department or employer.