Presented By

Dr O Rama Devi

Professor,

Dept. of AI&DS, LBRCE

Topics discussed

• Definitions/Terminologies.
• DBMS definition & functionalities.
• Properties of the database.
• Example of a university database

��

�

Definitions�Data: Raw, unprocessed facts� Ex:25,Suresh,Bangalore�Information: processed Data� Ex:The age of suresh is 25.�Database: Collection of related data� Ex: Online Banking system, Library Management system�Metadata: The Database Definition��

Database Management System

• Definition: Collection of programs that enable users to create and maintain the database
• Functionalities:

Define: Specifying the data type, structures and constraints for the data to be stored

Construct: Process of storing data on some storage medium.

Manipulate: Querying the database to retrieve specific data, updating database and generating reports.

Share: Allow multiple users and programs to access the database concurrent

​

���

Properties of Database

• A Database represents some aspects of real world(miniworld)
• A database is a logically coherent collection of data with some inherent meaning.
• A database is designed ,build and populated with data for a specific purpose.

​

​

University Database- Stores student and course information

Student

Course

Grade Report

Difference between File System and DBMS

.

Characteristics of Database Management Systems

Topics discussed:

• Self-describing Nature of a Database System.
• Insulation between Programs and Data, and Data Abstraction.
• Support of Multiple Views of the Data.
• Sharing of Data and Multiuser Transaction Processing

1. Self-describing Nature of a Database System

• Database systems – Data base +Meta data(DB Definition)

Stored in :DB Catalog

Used by :DBMS Software & Database USERS

• DBMS software must work equally well with any no.of database applications
• In Traditional file processing, Data definition---part of application program ---work with only one specific DB

Relations

An Example of database catalog

Columns

Student

Course

Grade Report

University Database- Stores student and course information

An Example of Database catalog

ii. Insulation between Programs and Data, and Data Abstraction.

In Traditional file processing----structure of data files is embedded in the application program.

​

In Database Approach ----Structure of data files is stored in the database catalog----separate from the access programs

(Program data Independence)

Internal Storage format for a Student Record

• The Characteristics that allow program data independence is called data Abstraction.
• DBMS allows the users with conceptual representation of data
• Data Model—type of data abstraction—provides conceptual representation

A database has many users, each of whom may require a different view of database.

A view---subset of database---contain virtual data derived from database(not explicitly stored)

iii. Support of Multiple Views of the Data.

A Multiuser DBMS allows multiple users to access the database at the same time

​

DBMS must include concurrency control

OLTP(Online Transaction Processing)---major part of database applications

​

DBMS must enforce several transaction properties

1) Isolation 2)Atomicity

iv. Sharing of Data and Multiuser Transaction Processing

Advantages of DBMS

• Data redundancy and inconsistency: Redundancy is the concept of repetition of data i.e. each data may have more than a single copy. The file system cannot control the redundancy of data as each user defines and maintains the needed files for a specific application to run. There may be a possibility that two users are maintaining the data of the same file for different applications. Hence changes made by one user do not reflect in files used by second users, which leads to inconsistency of data. Whereas DBMS controls redundancy by maintaining a single repository of data that is defined once and is accessed by many users. As there is no or less redundancy, data remains consistent.

.

• Data sharing: The file system does not allow sharing of data or sharing is too complex. Whereas in DBMS, data can be shared easily due to a centralized system
• Data concurrency: Concurrent access to data means more than one user is accessing the same data at the same time. Anomalies occur when changes made by one user get lost because of changes made by another user. The file system does not provide any procedure to stop anomalies. Whereas DBMS provides a locking system to stop anomalies to occur.
• Data searching: For every search operation performed on the file system, a different application program has to be written.

While DBMS provides inbuilt searching operations. The user only has to write a small query to retrieve data from the database.

​

• Data integrity: There may be cases when some constraints need to be applied to the data before inserting it into the database. The file system does not provide any procedure to check these constraints automatically. Whereas DBMS maintains data integrity by enforcing user-defined constraints on data by itself.
• System crashing: In some cases, systems might have crashed due to various reasons. It is a bane in the case of file systems because once the system crashes, there will be no recovery of the data that’s been lost. A DBMS will have the recovery manager which retrieves the data making it another advantage over file systems.

• Data security: A file system provides a password mechanism to protect the database but how long can the password be protected? No one can guarantee that. This doesn’t happen in the case of DBMS. DBMS has specialized features that help provide shielding to its data.
• Backup: It creates a backup subsystem to restore the data if required..
• Interfaces: It provides different multiple user interfaces like graphical user interface and application program interface.
• Easy Maintenance: It is easily maintainable due to its centralized nature.

Database Users

These are seven types of data base users in DBMS.

1. Database Administrator (DBA) :

Database Administrator (DBA) is a person/team who defines the schema and also controls the 3 levels of database.

The DBA will then create a new account id and password for the user if he/she need to access the data base.

DBA is also responsible for providing security to the data base and he allows only the authorized users to access/modify the data base.

DBA also monitors the recovery and back up and provide technical support. The DBA has a DBA account in the DBMS which called a system or super user account.

DBA repairs damage caused due to hardware and/or software failures.

2. Naive/Parametric End Users:

Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they frequently use the data base applications in their daily life to get the desired results. For examples, Railway’s ticket booking users are naive users. Clerks in any bank is a naive user because they don’t have any DBMS knowledge but they still use the database and perform their given task.

3. System Analyst :

System Analyst is a user who analyzes the requirements of parametric end users. They check whether all the requirements of end users are satisfied.

4. Sophisticated Users :

Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database. They can develop their own data base applications according to their requirement. They don’t write the program code but they interact the data base by writing SQL queries directly through the query processor.

5. Data Base Designers:

Data Base Designers are the users who design the structure of data base which includes tables, indexes, views, constraints, triggers, stored procedures. He/she controls what data must be stored and how the data items to be related.

6. Application Programmers :

Application Programmers are the back end programmers who writes the code for the application programs. They are the computer professionals. These programs could be written in Programming languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc.

​

7. Casual Users / Temporary Users :

Casual Users are the users who occasionally use/access the data base but each time when they access the data base they require the new information, for example, Middle or higher level manager.

Data Model

Data Model gives us an idea that how the final system will look like after its complete implementation. It defines the data elements and the relationships between the data elements. Data Models are used to show how data is stored, connected, accessed and updated in the database management system.

Some of the Data Models in DBMS are:

• Hierarchical Model
• Network Model
• Entity-Relationship Model Relational Model
• Object-Oriented Data Model
• Object-Relational Data Model

​

Hierarchical Model

This model organizes the data in the hierarchical tree structure.

The hierarchy starts from the root which has root data and then it expands in the form of a tree adding child node to the parent node.

This model easily represents some of the real-world relationships like food recipes, sitemap of a website etc.

Example: We can represent the relationship between the shoes present on a shopping website in the following way ::

Features of a Hierarchical Model

1. One-to-many relationship: The data here is organised in a tree-like structure where the one-to-many relationship is between the datatypes. Also, there can be only one path from parent to any node. Example: In the above example, if we want to go to the node sneakers we only have one path to reach there i.e through men's shoes node.

2. Parent-Child Relationship: Each child node has a parent node but a parent node can have more than one child node. Multiple parents are not allowed.

3. Deletion Problem: If a parent node is deleted then the child node is automatically deleted.

4. Pointers: Pointers are used to link the parent node with the child node and are used to navigate between the stored data.

Example: In the above example the 'shoes' node points to the two other nodes 'women shoes' node and 'men's shoes' node.

​

Advantages of Hierarchical Model

• It is very simple and fast to traverse through a tree-like structure.
• Any change in the parent node is automatically reflected in the child node so, the integrity of data is maintained.

​

Disadvantages of Hierarchical Model

• Complex relationships are not supported.
• As it does not support more than one parent of the child node so if we have some complex relationship where a child node needs to have two parent node then that can't be represented using this model.
• If a parent node is deleted then the child node is automatically deleted.

Network Model

.This model is the same as the hierarchical model, the only difference is that a record can have more than one parent.

It replaces the hierarchical tree with a graph.

Example: In the example below we can see that node student has two parents i.e. CSE Department and Library

Features of a Network Model

1. Ability to Merge more Relationships: In this model, as there are more relationships so data is more related. This model has the ability to manage one-to-one relationships as well as many-to-many relationships.
2. Many paths: As there are more relationships so there can be more than one path to the same record. This makes data access fast and simple
3. Circular Linked List: The operations on the network model are done with the help of the circular linked list. The current position is maintained with the help of a program and this position navigates through the records according to the relationship.

Advantages of Network Model

• The data can be accessed faster as compared to the hierarchical model. This is because the data is more related in the network model and there can be more than one path to reach a particular node. So the data can be accessed in many ways.
• As there is a parent-child relationship so data integrity is present. Any change in parent record is reflected in the child record.

Disadvantages of Network Model

• As more and more relationships need to be handled the system might get complex. So, a user must be having detailed knowledge of the model to work with the model.
• Any change like updation, deletion, insertion is very complex.

Entity-Relationship Model

Entity-Relationship Model or simply ER Model is a high-level data model diagram.

In this model, we represent the real-world problem in the pictorial form to make it easy for the stakeholders to understand.

It is also very easy for the developers to understand the system by just looking at the ER diagram. We use the ER diagram as a visual tool to represent an ER Model.

ER diagram has the following three components:

• Entities: Entity is a real-world thing. It can be a person, place, or even a concept.

Example: Teachers, Students, Course, Building, Department, etc are some of the entities of a School Management System.

• Attributes: An entity contains a real-world property called attribute. This is the characteristics of that attribute.

Example: The entity teacher has the property like teacher id, salary, age, etc.

• Relationship: Relationship tells how two attributes are related. Example: Teacher works for a department.

Example:

​

In the above diagram, the entities are Teacher and Department. The attributes of Teacher entity are Teacher Name, Teacher_id, Age, Salary, Mobile Number. The attributes of entity Department entity are Dept_id, Dept_name. The two entities are connected using the relationship. Here, each teacher works for a department.

Features of ER Model

• Graphical Representation for Better Understanding: It is very easy and simple to understand so it can be used by the developers to communicate with the stakeholders.
• ER Diagram: ER diagram is used as a visual tool for representing the model.
• Database Design: This model helps the database designers to build the database and is widely used in database design.

Advantages of ER Model

• Simple: Conceptually ER Model is very easy to build. If we know the relationship between the attributes and the entities we can easily build the ER Diagram for the model.
• Effective Communication Tool: This model is used widely

by the database designers for communicating their ideas.

• Easy Conversion to any Model: This model maps well to the relational model and can be easily converted relational model by converting the ER model to the table. This model can also be converted to any other model like network model, hierarchical model etc.

Disadvantages of ER Model

• No industry standard for notation: There is no industry standard for developing an ER model. So one developer might use notations which are not understood by other developers.
• Hidden information: Some information might be lost or hidden in the ER model. As it is a high-level view so there are chances that some details of information might be hidden.

Relational Model

Relational Model is the most widely used model. In this model, the data is maintained in the form of a two-dimensional table. All the information is stored in the form of row and columns. The basic structure of a relational model is tables. So, the tables are also called relations in the relational model.

Example: In this example, we have an Employee table

Features of Relational Model

• Tuples: Each row in the table is called tuple. A row contains all the information about any instance of the object. In the above example, each row has all the information about any specific individual like the first row has information about John.
• Attribute or field: Attributes are the property which defines the table or relation. The values of the attribute should be from the same domain. In the above example, we have different attributes of the employee like Salary, Mobile no,etc.

​

Advantages of Relational Model

• Simple: This model is more simple as compared to the network and hierarchical model.
• Scalable: This model can be easily scaled as we can add as many rows and columns we want.
• Structural Independence: We can make changes in database structure without changing the way to access the data. When we can make changes to the database structure without affecting the capability to DBMS to access the data we can say that structural independence has been achieved.

Disadvantages of Relational Model

• Hardware Overheads: For hiding the complexities and making things easier for the user this model requires more powerful hardware computers and data storage devices.
• Bad Design: As the relational model is very easy to design and use. So the users don't need to know how the data is stored in order to access it.

This ease of design can lead to the development of a poor database which would slow down if the database grows.

But all these disadvantages are minor as compared to the advantages of the relational model. These problems can be avoided with the help of proper implementation and organization

​

Object-Oriented Data Model

The real-world problems are more closely represented through the object-oriented data model. In this model, both the data and relationship are present in a single structure known as an object. We can store audio, video, images, etc in the database which was not possible in the relational model (although you can store audio and video in relational database, it is adviced not to store in the relational database). ). In this model, two are more objects are connected through links. We use this link to relate one object to other objects. This can be understood by the example given below.

In the above example, we have two objects Employee and Department. All the data and relationships of each object are contained as a single unit. The attributes like Name, Job title of the employee and the methods which will be performed by that object are stored as a single object. The two objects are connected through a common attribute i.e. the Department_id and the communication between these two will be done with the help of this common id.

Database Schema, Instance and State

• Database Schema:
• Includes descriptions of the database structure, data types, and the constraints on the database.
• Schema Diagram:
• An illustrative display of (most aspects of) a database schema.
• Schema Construct:
• A component of the schema or an object within the schema, e.g., STUDENT, COURSE.
• Database State:
• Distinction
• The database schema changes very infrequently.
• The database state changes every time the database is updated.
• Schema is also called intension.
• State is also called extension.

​

Example of a Database Schema

Database Schema and Instances

The description of a database is called the database schema, which is specified during database design and is not expected to change frequently.

​

• A schema diagram displays only some aspects of a schema, such as the names of record types and data items, and some types of constraints.
• The data in the database at a particular moment in time is called a database state or Instance.
• Database schema defines the variable declarations in tables that belong to a particular database, the values of these variables at a particular moment of time is called data instance.
• The distinction between database schema and database state is very important
• The DBMS stores the descriptions of the schema constructs and constraints—also called the meta-data

Three Schema Architecture

A database system is a collection of interrelated files and a set of programs that allow users to access and modify these files. It provides users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained.

Developers hide the complexity from users through several abstraction, to simplify users’ interactions with the system.

The data in the DBMS is described at three levels of abstraction

• Physical Level (Internal Schema)
• Logical Level (Conceptual Schema)
• View Level (External Schema)

​

Physical Level

The lowest level of abstraction describes how the data are stored. The physical level has an internal schema which describes the physical storage structure of the database.

Logical Level

• Logical level describes what data are stored in the database, and what relationships exist among those data.
• Logical level has a conceptual schema, which describes the structure of the whole database for a community of users.

​

The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.

View level

• This is the highest level in data abstraction.
• This level includes number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group.

​

Data Independence

• Data Independence can be defined as the capacity to change the schema at one level of a database system without having to change the schema at the next higher level.
• Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. This data is independent but mapped to each other.
• There are two types of Data Independence
• Logical Data Independence
• Physical Data Independence

Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs.

Physical data independence is the capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas need not be changed as well.

• Defines DBMS schemas at three levels:
• Internal schema
• Conceptual schema: entities, data types, relationships, etc
• External schemas at the external level to describe the various user views.
• Usually uses the same data model as the conceptual schema.
• Data Independence:
• Logical Data Independence: ability to change conceptual schema without effecting appl. programs
• Physical Data Independence: ability to change internal schema without effecting upper layers

Three-Schema Architecture

Database System Structure

A database system 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.

Storage manager

• A storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
• Thus, the storage manager is responsible for storing, retrieving, and updating data in the database. The storage manager components include:

Authorization and integrity manager: which tests for the satisfaction of integrity constraints and checks the authority of users to access data.

Transaction manager : which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.

File manager: which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

Buffer manager: which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.

The storage manager implements several data structures as part of the physical system implementation:

• Data files: which store the database itself.
• Data dictionary: which stores metadata about the structure of the database, in particular the schema of the database.
• Indices: which provide fast access to data items that hold particular values.

The Query Processor

The query processor components include

• DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands.
• A query can usually be translated into any of a number of alternative evaluation plans that all give the same result.
• The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.
• DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.
• Query evaluation engine, which executes low-level instructions generated by the DML compiler

​

Centralized and Client-Server DBMS Architectures

• Centralized DBMS:
• Combines everything into single system including- DBMS software, hardware, application programs, and user interface processing software.
• User can still connect through a remote terminal – however, all processing is done at centralized site.

A Physical Centralized Architecture

Basic 2-tier Client-Server Architectures

• Specialized Servers with Specialized functions
• Print server
• File server
• DBMS server
• Web server
• Email server
• Clients can access the specialized servers as needed

Logical two-tier client server architecture

Clients

• Provide appropriate interfaces through a client software module to access and utilize the various server resources.
• Clients may be diskless machines or PCs or Workstations with disks with only the client software installed.
• Connected to the servers via some form of a network.

(LAN: local area network, wireless network, etc.)

DBMS Server

• Provides database query and transaction services to the clients
• Relational DBMS servers are often called SQL servers, query servers, or transaction servers
• Applications running on clients utilize an Application Program Interface (API) to access server databases via standard interface such as:
• ODBC: Open Database Connectivity standard
• JDBC: for Java programming access
• Client and server must install appropriate client module and server module software for ODBC or JDBC
• See Chapter 9

Two Tier Client-Server Architecture

• A client program may connect to several DBMSs, sometimes called the data sources.
• In general, data sources can be files or other non-DBMS software that manages data.
• Other variations of clients are possible: e.g., in some object DBMSs, more functionality is transferred to clients including data dictionary functions, optimization and recovery across multiple servers, etc.

Three Tier Client-Server Architecture

• Common for Web applications
• Intermediate Layer called Application Server or Web Server:
• Stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server
• Acts like a conduit for sending partially processed data between the database server and the client.
• Three-tier Architecture Can Enhance Security:
• Database server only accessible via middle tier
• Clients cannot directly access database server

Three-tier client-server architecture