Data Model

Data Model:

Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system. A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. It describes the design of database to reflect entities, attributes, relationship among data, constrains etc.

It involves planning about tables, their columns, mapping between the tables, how they are structured in the physical memory etc. A data model helps to put the real world requirement into a design. This makes the developer to understand the relationship between various objects in the database. It helps to highlight any drawbacks of the plan and correct it at the design stage itself.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific, hence they were prone to introduce lots of duplication and update anomalies.

Features / Benefits of Data Model:

1.      It provides a means to represent a real world object in to computer – usable form.

2.      It captures and represents associations and relationships among the real world objects.

3.      It defines how the objects in the application interact in logical terms.

4.      It allows the database designer to capture static and dynamic organization and flow of information within a modeled enterprise. 

Depending on the levels of data, data models are divided into 3 categories:

·        Object Based Data Models:

o   Object Oriented Model

o   Entity Relational Model

·        Record based Data models:

o   Relational Data Model

o   Network Data Model

o   Hierarchical Model

       ·        Physical Data Model

-profshardulp.patil@gmail.com

Architecture of DBMS

Architecture of DBMS:

The Architecture of most of commercial dbms are available today is mostly based on this ANSI-SPARC database architecture. In this architecture the database schemas can be defined at three levels. The 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. The 3-tier provides data abstraction; which hides the low level complexities from end users.

There are following three levels or layers of DBMS architecture:

·        External Level

·        Conceptual Level

·        Internal Level

The above diagram describes three-tier architecture. Mapping is the process of transforming request response between various database levels of architecture. In External / Conceptual mapping, DBMS transforms a request on an external schema against the conceptual schema. In Conceptual / Internal mapping, it is necessary to transform the request from the conceptual to internal levels. The objective of three-level architecture is to separate each user's view of the database from the way the database is physically represented.

·        External Level or View level:

At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different user’s view of the database. External level is the one which is closest to the end users. This level deals with the way in which individual users view data. Individual users are given different views according to the user's requirement. Each view describes the database part that a particular user group is interested and hides the remaining database from that user group.

For Example, FACULTY of a college is interested in looking course details of students, STUDENTS are interested in looking all details related to academics, accounts, courses and hostel details as well.

·        Conceptual Level or Logical level:

This level describes what data is stored in the database and the relationships among the data. The middle level in the three level architecture is the conceptual level. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations. The conceptual level represents: All entities, their attributes, and their relationships.

For example, in student database the entity is student. An attribute is a characteristic of interest about an entity. For example, in case of student database Roll No, Name, Class, Address etc. are attributes of entity student.

·        Internal level or Storage level:

It is the physical representation of the database on the computer. This level describes how the data is stored in the database. The internal level is the one that concerns the way the data are physically stored on the hardware. It covers the data structures and file organizations used to store data on storage devices. It interfaces with the operating system access methods to place the data on the storage devices, build the indexes, retrieve the data, and so· on.

The internal level is concerned with storage space allocation for data and indexes, record descriptions for storage (with stored sizes for data items), record placement, data compression and data encryption techniques.

Advantages of Three-tier Architecture:

· The main objective of it is to provide data abstraction.

·  Same data can be accessed by different users with different customized views.

· The user is not concerned about the physical data storage details.

· Physical storage structure can be changed without requiring changes in internal structure of the database as well as users view.

· Conceptual structure of the database can be changed without affecting end users.

-profshardulp.patil@gmail.com
x

Data Abstraction

Data Abstraction:

Database systems are made-up of complex data structures. Database is full of data and records. What we see in rows and columns is quite different when it reaches the memory. What we see is the actual data. But when they are stored in the memory like disks or tapes, they are stored in the form of bits. But any users will not understand these bits. User needs to see the actual data to understand. But all the details about the data stored in the memory are not necessary for the users. User needs only little information that he is interested or wants to work with. To ease the user interaction with database, the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction.

Data abstraction is a process of representing the essential features without including implementation details. Many database-systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system.

There are mainly three levels of data abstraction:

Physical Level / Internal Level:

Logical Level / Conceptual Level:

View Level / External Level:

Physical Level / Internal Level:

This is the lowest level in data abstraction. This level describes how the data is actually stored in the physical memory like magnetic tapes, hard disks etc. In this level the file organization methods like hashing, sequential, B+ tree and access methods like sequential or random access are comes into picture. Usability, size of memory, and the number of times the records are factors which we need to know while designing the database.

Logical Level / Conceptual Level:

This is the middle level of 3-level data abstraction. It describes the actual data stored in the database in the form of tables and relates them by means of mapping. This level will not have any information on what a user views at external level. This level will have all the data in the database.

View Level / External Level:

The highest level of abstraction describes only part of the entire database. The variety of information stored in a large 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 users will have different views here, based on their levels of access rights. For example, student will not have access to see Lecturers salary details, one employee will not have access to see other employees details, unless he is a manager.

Example: Let’s say we are storing customer information in a customer table. At physical level these records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory. These details are often hidden from the programmers.

At the logical level these records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented. The programmers generally work at this level because they are aware of such things about database systems.

At view level, user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them.

-profshardulp.patil@gmail.com

Advantages & Disadvantages of DBMS

Features / Advantages of DBMS:

1.      Self-Describing Nature:

A database system is referred to as self-describing because it not only contains the database itself, but also metadata which defines and describes the data and relationships between tables in the database. This information is used by the DBMS software or database users if needed. This separation of data and information about the data makes a database system totally different from the traditional file-based system in which the data definition is part of the application programs.

2.      Redundancy Control:

In non-database systems (traditional computer file processing), each application program has its own files. In this case, the duplicated copies of the same data are created at many places. In DBMS, all the data of an organization is integrated into a single database. The data is recorded at only one place in the database and it is not duplicated.

3.      Elimination of inconsistency:

When the same data is duplicated and changes are made at one site, which is not propagated to the other site, it gives rise to inconsistency and the two entries regarding the same data will not agree. At such times the data is said to be inconsistent.

By controlling the data redundancy, the data inconsistency is eliminated. If a data item appears only once, any update to its value has to be performed only once and the updated value (new value of item) is immediately available to all users. 

4.      Sharing of Data:

In DBMS, data can be shared by authorized users of the organization. The database administrator manages the data and gives rights to users to access the data. Many users can be authorized to access the same piece of information simultaneously. The remote users can also share same data. Similarly, the data of same database can be shared between different application programs.

5.      Maintenance of Integrity:

Since DBMS is a central system, so standard can be enforced easily may be at Company level, Department level, National level or International level. The standardized data is very helpful during migration or interchanging of data.

6.      Control over Security:

Data security is the protection of the database from unauthorized users. Only the authorized persons are allowed to access the database. Some of the users may be allowed to access only a part of database i.e., the data that is related to them or related to their department.

DBMS allows different levels of access to different users based on their roles. In the school database, individual students will have access to their data alone, while their teachers will have access to all the students whom they are teaching and for the subjects that they are teaching. Class teacher will be able to see the reports of all the students in that class, but not other classes.

7.      Transaction Control / Data Atomicity:

DBMS makes sure either the transaction is fully complete or it is rolled back to the previous committed state. It does not allow the system to be in a partially committed state.

A transaction in commercial databases is referred to as atomic unit of work. For example, when you purchase something from a point of sale (POS) terminal, a number of tasks are performed such as;

·        Company stock is updated.

·        Amount is added in company's account.

·        Sales person's commission increases etc.

All these tasks collectively are called an atomic unit of work or transaction. These tasks must be completed in all; otherwise partially completed tasks are rolled back.

8.      Data Independence:

The separation of data structure of database from the application program that is used to access data from database is called data independence. In DBMS, database and application programs are separated from each other. The DBMS sits in between them. You can easily change the structure of database without modifying the application program.

9.      Concurrency Control:

DBMS provide access to multiple users to access the database at the same time. It has its own mechanism to have concurrency accesses and hence avoid any incorrect data in the system. For example, if both users attempt to perform update operation on the same record, then one may overwrite the values recorded by the other.

10.   Backup and Recovery:

In a computer file-based system, the user creates the backup of data regularly to protect the valuable data from damaging due to failures to the computer system or application program. It is a time consuming method, if volume of data is large. Most of the DBMSs provide the 'backup and recovery' sub-systems that automatically create the backup of data and restore data if required.

Disadvantages of DBMS:

1.      Complexity:

The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end-users must understand this functionality to take full advantage of it. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization.

2.      Size:

The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently.

3.      Higher impact of a failure:

The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the availability of the DBMS, the failure of any component can bring operations to a halt.

4.      Cost of DBMS:

The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost.

5.      Additional Hardware costs:

The disk storage requirements for the DBMS and the database may necessitate the purchase of additional storage space. Furthermore, to achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure.

6.      Cost of Conversion:

       In some situations, the cost of the DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology.

-profshardulp.patil@gmail.com

x

Functions of DBMS

Functions of DBMS: 

DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. The most important functions of Database Management System are:

1.      Data Dictionary Management:

DBMS stores definitions of the data elements and their relationships (metadata) in a data dictionary. The DBMS uses the data dictionary to look up the required data component structures and relationships which relieves you from coding such complex relationships in each program. Additionally, any changes made in a database structure are automatically recorded in the data dictionary, thereby freeing you from having to modify all of the programs that access the changed structure.

2.      Data Storage Management:

The DBMS creates and manages the complex structures required for data storage, thus relieving you from the difficult task of defining and programming the physical data characteristics. It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.

3.      Data Transformation and Presentation:

The DBMS transforms entered data in to required data structures. By using the data transformation and presentation function the DBMS can determine the difference between logical and physical data formats. That is, the DBMS formats the physically retrieved data to make it conform to the user’s logical expectations.

For example, imagine an enterprise database used by a multinational company. An end user in England would expect to enter data such as July 11, 2009, as “11/07/2009.” In contrast, the same date would be entered in the United States as “07/11/2009.” Regardless of the data presentation format, the DBMS system must manage the date in the proper format for each country.

4.      Security Management:

Security Management is another important function of the Database Management System. The DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) the user can perform. This is especially important in multiuser database systems.

5.      Multiuser Access Control:

To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising the integrity of the database. Multiuser access control is a very useful tool in a DBMS, it enables multiple users to access the database simultaneously without affecting the integrity of the database.

6.      Backup and Recovery Management

The DBMS provides backup and data recovery to ensure data safety and integrity. Current DBMS systems provide special utilities that allow the DBA to perform routine and special backup and restore procedures.

Recovery management deals with the recovery of the database after a failure, such as a bad sector in the disk or a power failure. Such capability is critical to preserving the database’s integrity.

7.      Data Integrity Management

The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. The data relationships stored in the data dictionary are used to enforce data integrity. Ensuring data integrity is especially important in transaction-oriented database systems.

8.      Database Access Languages and Application Programming Interfaces:

The DBMS provides data access through a query language. A query language is a non-procedural language—one that lets the user specify what must be done without having to specify how it is to be done. Structured Query Language (SQL) is the default query language and data access standard supported by the majority of DBMS vendors.

9.      Database Communication interfaces:

This refers to how a DBMS can accept different end user requests through different network environments. For example, the DBMS might provide access to the database via the Internet through the use of Web browsers such as Mozilla Firefox or Microsoft Internet Explorer. In this environment, communications can be accomplished in several ways.


-profshardulp.patil@gmail.com

Introduction to DBMS

Database:

Definition -

“A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.”

Database is actually a place where related piece of information is stored and various operations can be performed on it. Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information. Mostly data represents recordable facts. Data helps in producing information, which is based on facts. For example, if we have data about marks obtained by all students, we can then conclude about toppers and average marks. Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated, expanded and deleted as new information is added. In computing, databases are sometimes classified according to their organizational approach. Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.

The main feature of data in a database is:

1. It must be well organized

2. It is related

3. It is accessible in a logical order without any difficulty

4. It is stored only once.

Database Management System:

            A DBMS is software that allows creation, definition and manipulation of database. DBMS is actually a tool used to perform any kind of operation on data in database. DBMS also provides protection and security to database. It maintains data consistency in case of multiple users. Here are some examples of popular dbms, MySql, Oracle, Sybase, Microsoft Access and IBM DB2 etc.

Definition:

“A database management system (DBMS) refers to the technology for creating and managing databases. Basically DBMS is a software tool to organize (create, retrieve, update and manage) data in a database.”

“A database management system consists of collection of related data and refers to a set of programs for defining, creation, maintenance and manipulation of a database.”

The main aim of a DBMS is to supply a way to store up and retrieve database information that is both convenient and efficient. By data, we mean known facts that can be recorded and that have embedded meaning.

Needs of DBMS:

1. To share data of all kinds between users, between tools working on them, and usually between different computers. All should be able to work with the same data.

2. For persistent storage and storage must be reliable.

3. Provide more efficient access to large amounts of data, through indexing and other optimization.

4. A DBMS can be used for communication between different users and applications. Accurate, up-to-date information is fetched as per requirement of the user at any time.

-profshardulp.patil@gmail.com

Components of DBMS

Components of DBMS:

DBMS have several components, each performing very significant tasks in the database management system environment. Below is a list of components within the database and its environment.

1. Data:

Data is the most important component of the DBMS. DBMS exists to collect, store, process and access data, the most important component. The database contains both the actual or operational data and the metadata. User Data consists of table of data columns are attribute and rows are record. Metadata means description of structure of database. It means data about data.

2. Hardware:

Consists of a set of physical electronic devices such as computers, I/O devices, storage devices, etc., this provides the interface between computers and the real world systems.

3. Software:

This is the set of programs used to control and manage the overall database. This includes the DBMS software itself, the Operating System, the network software being used to share the data among users, and the application programs used to access data in the DBMS.

4. Users:

The users are the people who manage the databases and perform different operations on the databases in the database system. There are four kinds of people who play different roles in database system.

· DBA (Database Administrator)

· Database Designers

· End Users

·  Application Programmers

      5. Procedures:

Procedures refer to the instructions and rules that help to design the database and to use the DBMS. The users that operate and manage the DBMS require documented procedures on hot use or run the database management system. These may include procedure to install the new DBMS, to log on, to use to make backup copies to change the structure to generate the reports of data retrieved from database, etc.

6.  Database Access Language:

This is used to access the data to and from the database, to enter new data, update existing data, or retrieve required data from databases. The user writes a set of appropriate commands in a database access language, submits these to the DBMS, which then processes the data and generates and displays a set of results into a user readable form.

-profshardulp.patil@gmail.com