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

Data Dictionary / Metadata

Data Dictionary / Metadata:

A metadata (also called the data dictionary) is the data about the data. It is the self-describing nature of the database that provides program-data independence. It is also called as the System Catalog. It contains all the information about the data objects.  It is like storing all up-to-date information about the objects like tables, columns, index, constraints, functions etc.

Data Dictionary consists of the following information:

·        Name of the tables in the database

·        Constraints of a table i.e. keys, relationships, etc.

·        Columns of the tables that related to each other

·        Owner of the table

·        Last accessed information of the object

·        Last updated information of the object

Data dictionary is used to actually control the database operation, data integrity and accuracy. Metadata is used by developers to develop the programs, queries, controls and procedures to manage and manipulate the data. Metadata is available to database administrators (DBAs), designers and authorized user as on-line system documentation. This improves the control of database administrators (DBAs) over the information system and the user's understanding and use of the system.

Data dictionary may be either active or passive. An active data dictionary (also called integrated data dictionary) is managed automatically by the database management software. It is consistent with the current structure and definition of the database. Most of the relational database management systems contain active data dictionaries that can be derived from their system catalog.

The passive data dictionary (also called non-integrated data dictionary) is the one used only for documentation purposes. Data about fields, files, people and so on, in the data processing environment are.

Functions of the data dictionary:

•   It defines the data objects of each user in the database. As we know, we cannot remember all the tables, views, constraints, etc., so the user can easily search them when any Data Definition Language (DDL) fired, then the database searches for the data dictionary. The DBMS software updates the object.

•   It gives us the report about the data and the resources which the objects are using.

•  It allows seeing the tables and views to those users who have the proper rights to access the database. Thus, it controls the access of the database.

Advantages of the data dictionary

•   The data dictionary used so that we can remove the duplicity in the data definition.

•   As it provides documentation. So it is a valuable reference for any organization.

•   It helps the analysts in simplifying the structure so that it can meet the requirement of the data of the system.

•     It helps in improving the communication between the user and the system analyst.

•   Maximum of the database management system contains the data dictionary as a standard feature.

•   Through this, new database administrators can easily understand the database of the system.

•   The database administrator can easily track any problem in the database.

-profshardulp.patil@gmail.com

Users of DBMS

Users of DBMS:

Database users are the one who really use and take the benefits of database. There will be different types of users depending on their need and way of accessing the database.

1.      Database Administrator (DBA)

2.      Application programmers

3.      System Analyst

4.      End users

1.      Database Administrator (DBA):

Database Administrator (DBA) is the person or a group of persons who makes the strategic and policy decisions regarding the data of the enterprise, and who provide the necessary technical support for implementing these decisions. Therefore, DBA is responsible for overall control of the system at a technical level. In database environment, the primary resource is the database itself and the secondary resource is the DBMS and related software administering these resources is the responsibility of the Database Administrator (DBA).

2.      Application programmers:

As its name shows, application programmers are the one who writes application programs that uses the database. These users write application programs to interact with the database. Application programs can be written in some programming language such a C++,VB, ASP, .Net, JAVA or some higher level fourth generation language. Such programs access the database by issuing the appropriate request, typically a SQL statement to DBMS. These programs meet the user requirement and made according to user requirements. Retrieving information, creating new information and changing existing information is done by these application programs.

They interact with DBMS through DML (Data manipulation language) calls. And all these functions are performed by generating a request to the DBMS. If application programmers are not there then there will be no creativity in the whole team of Database.

3.      System Analyst:

System Analyst determines the requirement of end users, especially naive and parametric end users and develops specifications for transactions that meet these requirements. System Analyst gathers information according to user requirements needed to develop a solution on it. System Analyst plays a major role in database design, its properties; the structure prepares the system requirement statement, which involves the feasibility aspect, economic aspect, technical aspect etc. of the system.

4.      End users:

End users are those who access the database from the terminal end. End users are the users, who use the applications developed. End users need not know about the working, database design, the access mechanism etc. They just use the system to get their task done. There are basically two types of end users:

A.    Casual User:

These users have great knowledge of query language. Casual users access data by entering different queries from the terminal end. They do not write programs but they can interact with the system by writing queries.

      B.     Naive User:

Any user who does not have any knowledge about database can be in this category. Their task is to just use the developed application and get the desired results.     

-profshardulp.patil@gmail.com

Schema, Subschema and Instance

Schema:

Design of a database is called the schema. A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It is the collection of named objects. The schema contains names of tables, columns of each table, datatype, triggers, functions, views, stored procedures and other objects are included in the schema. A database schema can be represented by using the visual diagram. That diagram shows the database objects and relationship with each other. A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modeling. The changes in a schema are not applied so frequently, but occasionally changes need to be applied as the requirements of application changes.

         For Example: In the following diagram, we have a schema that shows the relationship between three tables: Course, Student and Section. The diagram only shows the design of the database, it doesn’t show the data present in those tables. Schema is only a structural view (design) of a database as shown in the diagram below.

Schema is of three types: Physical schema, logical schema and view schema.

              The design of a database at physical level is called physical schema. It describes how the data stored on the disk or the physical storage.

           Design of database at logical level is called logical schema, programmers and database administrator work at this level. At this level data can be described as certain types of data records gets stored in data structures.

               Design of database at view level is called view schema. This generally describes end user interaction with database systems.

Subschema:

            A subschema provides a view of the database as seen by an application program. This view is often a subset of the complete schema definition. A subschema is used at run time to provide the DBMS with a description of those portions of the database that are accessible to the application program. The subschema allows the user to view only that part of the database that is of interest to him. The subschema defines the portion of the database as seen by the application programs and the application programs can have different view of data stored in the database.

              The subschema identifies the areas, records, elements, and sets which are accessible. The subschema identifies the Data Manipulation Language (DML) functions which can be performed. The different application programs can change their respective subschema without affecting other's subschema or view.

Instance:

         The data stored in database at a particular moment of time is called instance of database. It is a snapshot where the current state or occurrence of a database is framed at that moment. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. Instance changes very frequently, whenever data is removed or added in the database.

              For example: We have a table student in the database, today the table has 10 records, so today the instance of the database has 10 records. We are going to add another 10 records in this table by tomorrow so the instance of database tomorrow will have 20 records in table. In short, at a particular moment the data stored in database is called the instance, that changes over time when we add or delete data from the database.

Difference between Schema and Instance:

• A schema is the design representation of a database whereas instance is the snapshot of a database at a particular moment.
• Instance changes very frequently, whenever data is removed or added in the database. As against, the changes in schema occurs rarely.

-profshardulp.patil@gmail.com

Data Independence

Data Independence:

A major objective for three-level architecture is to provide data independence. Data independence refers characteristic of being able to modify the schema at one level of the database system without affecting the schema at the next higher level. It saves time and cost required when the database is modified or altered.

Types of Data independence

The data independence is classified as two types and they are as below:

· Logical Data Independence:

Logical data independence indicates that the conceptual schema can be changed without affecting the existing external schema. Logical data independence is used to separate the external level from the conceptual view. The user view of the data would not be affected by any changes to the conceptual view of the data. These changes may include insertion or deletion of attributes, altering table structures entities or relationships to the logical schema etc. The change would be absorbed by the mapping between the external and conceptual levels. Logical data independence also insulates application programs from operations such as combining two records into one or splitting an existing record into two or more records.

· Physical Data Independence:

Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema. Modifications at the internal levels are occasionally necessary to improve performance. If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected. Physical data independence is used to separate conceptual levels from the internal levels. The change would be absorbed by the mapping between the conceptual and internal levels.

x

Advantages of Data independence

1.      Ability of improving performance

2.      Alterations in data structure does not requires alterations in application programs

3.      Implementation details can be hidden from the users

4.      Reduction of incongruity

5.      Tractability in improvement of system

6.      Affordable prices of maintaining system

7.      Providing the best services to the users

8.      Permit users to focus on general structure

9.      Enforcement of standards

10.   Improvement of security

1    11.  The state of being undamaged or undivided can be improved

-profshardulp.patil@gmail.com

Structure of DBMS

Structure of DBMS:

DBMS (Database Management System) acts as an interface between the user and the database. The user requests the DBMS to perform various operations (insert, delete, update and retrieval) on the database. The components of DBMS perform these requested operations on the database and provide necessary data to the users. A DBMS is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components.

1.      Query Processor:

Query Processor translates statements in a query language into low-level instructions the database manager understands. The Query Processor simplifies and facilitates access to data. The Query processor includes the following component.

· DML Pre-compiler: DML pre-compiler converts DML statements embedded in an application program to normal procedure calls in a host language.  It also attempts to transform user's request into an equivalent query language but more efficient form. The Pre-compiler must interact with the DML compiler to generate the appropriate code.

· DML Compiler: The DML compiler translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. The DML compiler also performs query optimization, which is it picks the lowest cost evaluation plan from among the alternatives. Query evaluation engine executes low level instructions generated by the DML compiler.

· DDL Interpreter: It interprets the DDL statements and records them in a set of tables containing meta data or data dictionary. It interprets DDL statements and records the definitions in the data dictionary.

·       · Query Evaluation Engine: It executes low-level instructions generated by the DML compiler.

2.      Storage Manager :

Storage Manager provides the interface between the low-level data stored in the database and application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system. The storage manager is responsible for storing, retrieving, and updating data in the database.

· Authorization and Integrity Manager: It tests for the satisfaction of integrity constraints checks the authority of users to access data.

· Transaction Manager: It ensures that the database remains in a consistent state despite the system failures and that concurrent transaction execution proceeds without conflicting.

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

· Buffer Manager: It is responsible for fetching data from disk storage into main memory and deciding what data to cache in memory.

3.      Data Structure / Storage :

Following data structures are required as a part of the physical system implementation.

· Data Files: It stores the database.

· Data Dictionary: It stores metadata (data about data) about the structure of the database.

· Indices: Provide fast access to data items that hold particular values.

·Statistical Data: It stores statistical information about the data in the database. This information is used by query processor to select efficient ways to execute query.

-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