database management system

Slide 1: 

Database Management System

Chapter 1. Introduction : 

Chapter 1. Introduction Basic Concepts. What is Data? What is Database? What is Database Management System? Purpose & Advantages of DBMS. Data Models. DBMS Architecture Three Level Architecture Overall Architecture Various Components of DBMS.

Slide 3: 

Basic Concepts :- What is Data? “Data is a collection of facts from which conclusion may be drawn.” In computer science, “data is anything in a form suitable for use with a computer.” Data is often distinguished from programs. A program is a set of instructions that detail a task for the computer to perform. In this sense, data is thus everything that is not program code. Cont….

Slide 4: 

What is Database? “A database is a collection of data that is organized so that its contents can easily be accessed, managed, and updated.” “A database is a collection of data, typically describing the activities of one or more related organizations.” “Database is a structured collection of records or data that is stored in a computer system.” Cont…

Slide 5: 

What is Database Management System? A Database Management System (DBMS), or simply a Database System (DBS) consist of : A collection of interrelated and persistent data (usually referred to as the database (DB)). A set of application programs used to access, update and manage that data (which form the data management system (MS)). The goal of a DBMS is to provide an environment that is both convenient and efficient to use in : Retrieving information from the database. Storing information into the database. Cont….

Slide 6: 

Databases are usually designed to manage large bodies of information. This involves Definition of structures for information storage (data modeling). Provision of mechanisms for the manipulation of information (file and systems structure, query processing). Providing for the safety of information in the database (crash recovery and security). Concurrency control if the system is shared by users. Count….

Slide 7: 

2.1 Purpose of DBMS :- In the early days, database applications were built directly on top of file systems Drawbacks of using file systems to store data: Data redundancy and inconsistency - Multiple file formats, duplication of information in different files 2. Difficulty in accessing data - Need to write a new program to carry out each new task 3. Data isolation — multiple files and formats 4. Integrity problems Integrity constraints (e.g. account balance > 0) become“ buried” in program code rather than being stated explicitly - Hard to add new constraints or change existing ones Count…

Slide 8: 

2.1 Purpose of DBMS :- (Count…) 5. Atomicity of updates - Failures may leave database in an inconsistent state with partial updates carried out - Example: Transfer of funds from one account to another should either ` complete or not happen at all 6. Concurrent access by multiple users - Concurrent accessed needed for performance - Uncontrolled concurrent accesses can lead to inconsistencies Example: Two people reading a balance and updating it at the same time 7. Security problems - Hard to provide user access to some, but not all, data

Slide 9: 

2.2 Advantages of DBMS :- Database Management System (DBMS) aids in storage, control, manipulation and retrieval of data. This article lists the advantages of database management systems. Database is a software program, used to store, delete, update and retrieve data. A database can be limited to a single desktop computer or can be stored in large server machines, like the IBM Mainframe. There are various database management systems available in the market. Some of them are Sybase, Microsoft SQL Server, Oracle RDBMS, PostgreSQL, etc. Cont…

Slide 10: 

The database management systems are warehouses of information, where large amount of data can be stored. The common examples in commercial applications are inventory data, personnel data, etc. It often happens that a common man uses a database management system, without even realizing, that it is being used. The best examples for the same would be the address book of a cell phone, digital diaries, etc. Both these equipments store data in their internal database. Cont… 1. Data Warehouses

Slide 11: 

2. Defining Attributes :- The unique data field in a table is assigned a primary key. The primary key helps in the identification of data. It also checks for duplicates within the same table, thereby reducing data redundancy. There are tables, which have a secondary key in addition to the primary key. The secondary key is also called 'foreign key'. The secondary key refers to the primary key of another table, thus establishing a relationship between the two tables.  Count…

Slide 12: 

3. Systematic Storage :- The data is stored in the form of tables. The table consists of rows and columns. The primary and secondary key helps to eliminate data redundancy, enabling systematic storage of data.  4. Changes to schema :- The table schema can be changed and it is not platform dependent. Therefore, the tables in the system can be edited to add new columns and rows without hampering the applications, which depend on that particular database.  Count…

Slide 13: 

5. No Language Dependence :- The database management systems are not language dependent. Therefore, they can be used with various languages and on various platforms.  6. Table Joins :- The data in two or more tables can be integrated into a single table. This enables to reduce the size of the database and also helps in easy retrieval of data.   7. Multiple Simultaneous Usage :-  The database can be used simultaneously by a number of users. Various users can retrieve the same data simultaneously. The data in the database can also be modified, based on the privileges assigned to users.  Count…

Slide 14: 

8. Data Security :- Data is the most important asset. Therefore, there is a need for data security. Database management systems help to keep the data secured.   9. Privileges :- Different privileges can be given to different users. For example, some users can edit the database, but are not allowed to delete the contents of the database.  10. Abstract View of Data and Easy Retrieval :- DBMS enables easy and convenient retrieval of data. A database user can view only the abstract form of data; the complexities of the internal structure of the database are hidden from him. The data fetched is in user friendly format.  Count…

Slide 15: 

11. Data Consistency :- Data consistency ensures a consistent view of data to every user. It includes the accuracy, validity and integrity of related data. The data in the database must satisfy certain consistency constraints, for example, the age of a candidate appearing for an exam should be of number data type and in the range of 20-25. When the database is updated, these constraints are checked by the database systems.

Slide 16: 

3. Data Models :- A Collection of tools for describing : Data Data Relationship Add Semantics Data Constraints Relational Model Entity Relationship Model (for Database Design) Object base Data Model (for Object-Oriented) Semistructured Data Model (XML) Other Older Models : Network Model Hierarchical Model

Slide 17: 

3.1 Relational Data Model :- This Model uses a collection of tables to represent both data and the relationship among those data. Each table has multiple columns and each column has a unique name. It is an example of Record-Base a model. Database is structured in fix-format records of several types. This is the most widely used Data Model

Example of Simple relational Model : 

Example of Simple relational Model

Slide 21: 

3.2 The Entity Relationship Model :- It is based on the real world that consists of a collection of Basic Object called “Entity”. An Entity is a Thing or Object in the real world that distinguishable from other objects. For example a person is an entity and bank account can be consider as an entity. Entities are describe in database by a set of Attributes. A Relationship is an association among several entities.

Slide 23: 

3.3 Object-Base Data Model :- It can be seen an extending the (E-R) Model with notations of: Encapsulation Methods (Functions) and Object identity Combines the features of Object-Oriented Data Model and Relational Data Model.

Slide 25: 

3.4 Semistructured Data Model It permits the specification of data where individual data item of the same type may have different set of attributes. The Extensible Markup Language (XML) is widely use to represent the Semistructured Data.

Slide 26: 

3.5 Hierarchical Data Model :- In this model data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list.

Slide 27: 

A hierarchical database consists of the following: It contains nodes connected by branches. The top node is called the root. If multiple nodes appear at the top level, the nodes are called root segments. The parent of node nx is a node directly above nx and connected to nx by a branch. Each node (with the exception of the root) has exactly one parent. The child of node nx is the node directly below nx and connected to nx by a branch. One parent may have many children.

Slide 28: 

By introducing data redundancy, complex network structures can also be represented as hierarchical databases. This redundancy is eliminated in physical implementation by including a 'logical child'. The logical child contains no data but uses a set of pointers to direct the database management system to the physical child in which the data is actually stored. Associated with a logical child are a physical parent and a logical parent. The logical parent provides an alternative (and possibly more efficient) path to retrieve logical child information.

Slide 29: 

3.6 Network Data Model :- This model organizes data using two fundamental constructs, called records and sets. Records contain fields, and sets define one-to-many relationships between records: one owner, many members.

Slide 30: 

Access to the database was not via SQL query strings, but by a specific set of API's, typically for FIND, CREATE, READ, UPDATE and DELETE. Each API would only access a single table (dataset), so it was not possible to implement a JOIN which would return data from several tables. It was not possible to provide a variable WHERE clause. The only selection mechanism available was read all entries (a full table scan). read a single entry using a specific primary key. read all entries on a child table which were associated with a selected entry on a parent table Any further filtering had to be done within the application code.

Slide 31: 

It was not possible to provide an ORDER BY clause. Data was presented in the order in which it existed in the database. This mechanism could be tuned by specifying sort criteria to be used when each record was inserted, but this had several disadvantages: Only a single sort sequence could be defined for each path (link to a parent), so all records retrieved on that path would be provided in that sequence. It could make inserts rather slow when attempting to insert into the middle of a large collection, or where a table had multiple paths each with its own set of sort criteria.

Slide 32: 

4. DBMS Architecture :- One of the many tasks that DBAs must handle is choosing the correct DBMS to use for each new application being developed There are many aspects to selecting the proper type of DBMS - there are different types of each DBMS for different architectures and purposes. Final architecture must be based on the business needs of the organization, not be made by a single person or group, but by a team consisting of business experts and IT experts The DBMS selected is appropriate for the nature and type of processing plan to implemented. There are basically four levels of DBMS architecture that can be selected: Enterprise DBMS Departmental DBMS Personal DBMS Mobile DBMS Count…

Slide 33: 

Enterprise DBMS :- It is designed for scalability and high performance. It must be capable of supporting very large databases, a large number of concurrent users, and multiple types of applications. The enterprise DBMS will run on a large-scale machine, typically a mainframe or a high-end Unix, Linux, or Windows NT machine. Furthermore, an enterprise DBMS offers all of the “bells and whistles” available from the DBMS vendor. Multi-processor support, support for parallel queries, clustering, and other advanced DBMS features will be core components of an enterprise DBMS.

Slide 34: 

2. Departmental DBMS :- sometimes referred to as a workgroup DBMS, supports small to medium sized workgroups within an organization, and typically runs on a Unix, Linux, or Windows 2000 (or NT) server. The dividing line between a departmental database server and an enterprise database server is gray. Hardware and software upgrades often can allow a departmental DBMS to tackle tasks that previously could only be performed by an enterprise DBMS. The steadily falling cost of departmental hardware and software components further contributes to lowering TCO and helping to enable a workgroup environment to scale up to serve the enterprise.

Slide 35: 

3. Personal DBMS :- is designed to be used by a single user, typically on a low- to medium-powered PC platform. Lotus Approach, Microsoft Access and dBase are examples of personal database software. Of course, the major DBMS vendors also market personal versions of their more high-powered solutions, Personal Oracle and DB2 Everyplace for example. Sometimes the low cost of a personal DBMS causes misguided attempts to choose a personal DBMS for a departmental or enterprise solution. But do not be lured by the low cost. A personal DBMS product is suited only for small scale projects and should not be used to deploy multi-user applications.

Slide 36: 

4. Mobile DBMS :- is a specialized version of a departmental or enterprise DBMS. It is designed to be used by remote users who are not usually connected to the network. The mobile DBMS enables local database access and modification on a laptop or handheld device, such as a Palm PDA or PocketPC. Furthermore, the mobile DBMS provides a mechanism for synchronizing remote database changes to a centralized, enterprise or departmental database server.

Slide 37: 

Three-Level Architecture :- A commonly used views of data approach is the three-level architecture suggested by ANSI/SPARC (American National Standards Institute/Standards Planning and Requirements Committee). ANSI/SPARC produced an interim report in 1972 followed by a final report in 1977 The three levels of the architecture are three different views of the data: External - individual user view Conceptual - community user view Internal - physical or storage view Count…

Slide 39: 

Two-Tier Client-Server Client manages main business and data processing logic and user interface. Server manages and controls access to database.

Two-Tier Client Server Architecture : 

Two-Tier Client Server Architecture

Slide 41: 

Three-Tier C-S Architecture Client side presented two problems preventing true scalability: ‘Fat’ client, requiring considerable resources on client’s computer to run effectively. Significant client side administration overhead. By 1995, three layers proposed, each potentially running on a different platform.

Slide 42: 

Three-Tier C-S Architecture User interface layer – runs on client. Business logic and data processing layer – middle tier runs on a server (application server). DBMS – stores data required by the middle tier. This tier may be on a separate server (database server).

Slide 43: 

Three-Tier C-S Architecture Advantages: ‘Thin’ client, requiring less expensive hardware. Application maintenance centralized. Easier to modify or replace one tier without affecting others. Separating business logic from database functions makes it easier to implement load balancing. Maps quite naturally to Web environment.

Three-Tier Architecture : 

Three-Tier Architecture

Slide 45: 

 Overall Architecture :- A DBMS is typically run as a back-end server in a (local or global) network, offering services to clients directly or to application servers.

Slide 47: 

Components of DBMS :- Hardware Can range from a PC to a network of computers. Software DBMS, operating system, network software (if necessary) and also the application programs. Data Used by the organization and a description of this data called the schema. People Includes database designers, DBAs, application programmers, and end-users. Procedure Instructions and rules that should be applied to the design and use of the database and DBMS.

Slide 48: 

Data Definition Language :- (DDL) The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Specific notation for defining the Data schema Example : Create Table Account ( Acc_No Char(10), Balance Integer ) DDL compiler generates a set of tables stored in a data dictionary Data Dictionary contains Metadata (Data about Data) Database Schema Data Storage and Definition Language Specifies the storage structure and access methods used Integrity Constraints Domain Constraints Referential Integrity Assertion Authorization

Slide 49: 

Data Manipulation Language :- (DML) Language for accessing and manipulating the data organized by the appropriate data model DML also known as query language Data Manipulation is: retrieval of information from the database insertion of new information into the database deletion of information in the database modification of information in the database Two Classes of Languages : Procedural : What Data is Required? How to get those Data? Declarative (Nonprocedural): What Data is Required? Without specifying How to get those Data? SQL is the most widely used Query Language.

Slide 50: 

End of Chapter-1