UnitI 10 Basic concepts Database Database Users Characteristics

Unit-I [10] Basic concepts, Database & Database Users, Characteristics of the database, Database concepts and architecture, Data Models, Schemas and instances, DBMS architectures and Data Independence, Database languages and Interface, Data modeling using the relationship approach. Source : DBMS by Silberschatz, Korth and Sudarshan

Database: It is a collection of inter-related relevant data stored together to serve multiple application. DBMS (Data Based Management System): It is a set of programs to access those data. • Database system are designed to manage large amount of data. • Storage of data is independent of program. • Controlled approach is used to add , delete, modify, retrieve data. • Goal of DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. • If data is shared , system must avoid anomalous results.

Database – System Application: v Banking v Airlines v Universities v Credit card transaction v Telecommunication v Finance v Sales v On-line retailers v Manufacturing v Human Resource

i. e Database is an important part of any organization. In early days very few people interact with database w/o knowing that he/she is interacting indirectly with database. e. g üairline reservation agents. üWhen you access an online bookstore and browse a book or music collection , you are accessing data stored in a database. üWhen you access a bank web site and retrieved your bank balance and transaction information, the information is retrieved from the bank’s database system.

Purpose of DBMS: File processing system e. g bank enterprise. Part of this enterprise , store that data in o. s operating system files. Separate application program for addition, deletion, modification, has to be written to manipulate the files. New application programs are needed if need increases. e. g in bank enterprise , if we want to extract those records which has balance > 10, 000 new file is permanently created which contains these records.

Disadvantages of File processing system: Data redundancy and inconsistency: Same information may be duplicated. Leads to higher storage and access cost. Change is reflected at all places. Difficulty in accessing data: On the spot if we need some information or list which is not required at the time of designing , to get it we need additional application program. Data isolation: Data is scattered. Difficulty in retrieving data.

Integrity Problems: Data values must satisfy some consistency constraint. If some constraint change it is difficult to change program to support that constraint. Atomicity problems: If electrical or mechanical failure occur during transaction of data. It is difficult to ensure atomicity in conventional file processing system. Concurrent-access anomalies: Due to concurrent execution Security Problems : Every user doesn’t able to access all data.

View of Data A major purpose of a database system is to provide users with an abstract view of data. Data abstraction : Physical level(lowest level) • How data is actually stored. • Describes Complex data structures in detail. • • Logical level(next higher level) What data are stored. What relationship exists among data. Describes database in small no. of simple structure. User of logical level do not need knowledge about complexity of database.

View level: • Highest level. • Many users of database system do not need all information only part of database in needed. • View level abstraction exists to simplify their interaction with system.

Relationship Among levels of abstraction View level View 1 Describe what data is stored. Describe what relationships exist among those data View 2 Logical level Physical level View n Highest level. Describe only part of database Simplifies interaction with system. System provide multiple views of same database. Lowest level Describe how data is stored. Describe complex low-level data structure

Database Users Goal of database is to retrieve information and store new information in database. People who work on database are database users or database administrator. 1> Naive Users: • Unsophisticated users. • interact by invoking one of the application program. e. g 1) bank teller invokes application program transfer to transfer amount from acc. A to acc. B. 2) If one wants to find his / her account balance on www Then one may access a form , enter account number the web server retrieves balance and sends back to user. Interface of this user is form. User has to fill fields in form. Can also read reports.

2> Application Programmers: • Computer professional who write application program. • RAD tools helps application programmer to construct forms and reports with minimum efforts. 3> Sophisticated User: • Interact w/o writing programs. • Form request using query language. • submits query to query processor. Query processor breaks down DML statements into instruction that storage manager understands. 4> Specialized Users: • writes specialized database applications. • applications are computer aided design system, knowledge base

System that store data with complex data types(e. g graphics, audio data). Instances and schemas: The collection of information stored in the database at a particular moment is called an instance of database. Overall design of database is called schema. Database schema corresponds to programming language type definition. A variable of a given type has a particular value at a given instance. Thus the value of a variable in programming language corresponds to an instance of a database schema. Database system have several schemas partitioned according to level of abstraction. At the lowest level is physical schema , at intermediate level is logical schema , at highest level are called sub schemas. A database have several schemas at view level.

Data Independence: The ability to modify a schema definition without affecting a Schema Definition in the next higher level is called data independence. 1. Physical Data independence: Modify physical schema w/o causing application program to be rewritten. This modification improves performance. 2. Logical data independence: Modify logical schema w/o causing application program to be rewritten. When logical structure of database is altered. Logical data independence is difficult to achieve than physical data independence.

Data Languages : Provides two different types of languages: one to specify database schema and other to express database queries and updates. 1) Data Definition Language: A database schema is specified by a set of definitions expressed by a special language called data definition language. The result of compilation of DDL statements is a set of tables stored in special file data dictionary or data directory. Metadata: Data dictionary contains metadata. This file is consulted before modifying and reading of actual data. Storage structure and access methods used by database are specified by set of definitions in a special DDL called data storage and definition language.

Compilation of these definitions results in a set of instructions to specify implementation detail. 2) Data Manipulation Language: Manipulation means retrieval , insertion, deletion, modification. This language enables user to access or manipulate data organized by various data models. Procedural DMLs : what data and how to get data. Nonprocedural DMLs : what data w/o specifying how to get data. Nonprocedural DMLs are easy to learn and most used but as this does not specify how to get data it generates a code which is not efficient. Query : is a statement that is used to retrieval of information Query language : portion which involves information retrieval.

Database Administrator Person having control over data and program is called DBA. Functions of DBA 1. Schema definition DBA 1. Storage structure and access method definition 2. Schema and physical organization modification 3. Granting of authorization for Data access 4. Integrity constraint specification

Data Models : Underlying the structure of a database is the data model: a collection of conceptual tools for describing data , data relationships , data semantics and consistency constraint. Classification of Data Models: 1) Relational Model 2) The E-R Model 3) Object-Based Data Model 4) Semistructured Data Model

Data Models fall into 3 Groups : 1) Object Based Logical Model: These models are used in describing data at logical level and view level. There are many different models as: i) Entity-Relationship Model. ii) Object Oriented Model. iii) Semantic data Model. iv) Functional Data Model. i) E-R Model: Based on perception of real world consisting of a collection of basic objects called entities and relationship between these objects.

e. g person is an entity and accounts are entities. Entities are describes in a set of attributes. Depositor relationship associates a customer with each account that she has. Entity set : set of all entities of the same type. Relationship set : set of all relationship of the same type. Overall logical structure of a database is shown by E-R diagram. Components of E-R diagram : • Rectangle : represents entity set • Ellipse : Represents attribute • Diamonds : represents set of relationships among a member from each of several entity set.

• Lines : link attribute to entity sets and entity sets relationships.

Entity Weak entiry Relationship Weak ER Attribute Derived Attribute Multivalued Attribute Key Attribute ___

Mapping Cardinalities : Which express the number of entities to which another entity can be associated via a relationship set. The Object Oriented Model: Based on a collection of objects. Object contains value of instance variable , bodies of code called methods that operate on object. Objects with same type of values and methods are grouped into classes. One object can access data of other object by invoking method of other object. This is called sending a message to object. External visible part – call interface. Not externally visible (internal part)- instance variable , method code.

e. g Consider an object representing bank account having instance variable acc_no , balance and method interest which adds interest to balance. If criteria of interest changes , only interest method we have to change according to new criteria in Object – Oriented Model. Semantic Data Model: E-R model is first semantic model. Emphasis on accurately modeling data relationships. Schema designed in this model is translated into one of the traditional model. Essentials of semantic model: Generalization : Objects of different types are combined to form a higher order object type. While combining only similarities between objects are considered.

Aggregation: Relationships between low level types can be considered as higher level type. Grouping or Association:

Record based Logical Model: Record based logical are used in describing data at logical and view levels. Used to specify the overall logical structure of database and provide a higher level description of the implementation. So named because the database is structured in fixed format records of several types. Each record type defines a fixed number of fields or attributes and each field is usually of fixed length. Three most widely used record based models are hierarchical models , relational models , network models. Relational Model: Relational model uses a collection of tables to represent both data and the relationships among those data.

Sample relational database comprising of 2 tables: bank customer and Accounts of those customer. Cust_name Social_security Cust_street Cust_city Acc_no John 192 -83 -7465 North Alto A-102 Smith 019 -32 -4533 M. G. New York A- 215 Alen 336 -20 -8756 Las Harrison A – 301 John 192 -83 -7465 North Alto A – 225 Linda 123 -90 -6238 Main Rye A – 225 Smith 019 -32 -4533 M. G New York A – 221 Jones 769 -45 -8760 Park Palo A – 501

acc_no balance A-102 500 A- 215 700 A – 301 900 A – 225 1000 A – 221 600 A – 501 800

Network Model : Data in network model are represented by collection of records and relationships among data are represented by links which can be viewed as pointers. John 192 -83 -7465 North Smith 019 -32 -4533 Alen Alto A-102 500 M. G. New York A-215 700 336 -20 -8756 Las A-301 900 Linda 123 -90 -6238 Main A-225 1000 Jones 769 -45 -8760 Park A-221 600 A-501 800 Harrison Rye Palo

Hierarchical Model: John 192 -83 -7465 … Smith 019 -32 -4533 … Alen 336 -20 -8756 Linda … 123 -90 -6238 Jones A-225 A-102 500 A-225 1000 A-215 … 769 -45 -8760 1000 A-301 900 700 A-221 600 A-501 800 …

Characteristics of DBMS: 1) Control over data redundancy 2) Sharing of Data 3) Maintenance of Integrity 4) Support for transaction control and recovery 5) Data Independence 6) Control over security 7) Processing Speed 8) H/W Independence

DBMS Architecture :

Database system partition into modules that deal with their responsibilities. Some functions of database system are provided by o. s. Functional components are divided into 2 parts: 1) Query Processor 2) Storage Manager

1) Query Processor: It helps the database system simplify and facilitate access to data. 2) Storage manager: Storage manager is a program module that provides the interface between the lower data stored in the database and the application programs and queries submitted to the system.

Query Processor DML Compiler Embedded DML Precompiler DDL Interpreter Query Evaluation engine Storage Manager Authorization & Integrity Manager Transaction manager File manager Buffer manager

Data Structures Data Files Data dictionary Indices Statistical Data

Mapping Cardinality n Express the number of entities to which another entity can be associated via a relationship set. n Most useful in describing binary relationship sets. n For a binary relationship set the mapping cardinality must be one of the following types: H One to one H One to many H Many to one H Many to many

Extended ER Features 1. 2. 3. 4. 5. Specialization Generalization Inheritance Aggregation Constraint on Generalization