Download DBMS: Basic Concepts and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DBMS: Basic Concepts
- Introduction
- Disadvantages of file oriented approach
- Database
- Why Database
- Database Management System(DBMS)
- Function of DBMS
- Advantages of DBMS and disadvantage of DBMS
- Database Basics
- Three level architecture of DBMS
- Database users
- Database language
- Database structure Introduction: In computerized information system data is the basic resource of the organization. So, proper organization and management for data is required fro organization to run smoothly. Database management system deals the knowledge of how data stored and managed on a computerized information system. In any organization, it requires accurate and reliable data for better decision making, ensuring privacy of data and controlling data efficiently. The examples include deposit and/or withdrawal from a bank,hotel,airline or railway reservation, purchase items from supermarkets in all cases, a database is accessed. What is data: Data is the known facts or figures that have implicit meaning. It can also be defined as it is the representation of facts ,concepts or instruction in a formal manner, which is suitable for understanding and processing. Data can be represented in alphabets(A-Z, a-z),in digits(0-9) and using special characters(+,-.#,$, etc) e.g: 25, “ajit” etc. Information: Information is the processed data on which decisions and actions are based. Information can be defined as the organized and classified data to provide meaningful values. Eg: “The age of Ravi is 25” File: File is a collection of related data stored in secondary memory.
File Oriented approach: The traditional file oriented approach to information processing has for each application a separate master file and its own set of personal file. In file oriented approach the program dependent on the files and files become dependent on the files and files become dependents upon the programs **Disadvantages of file oriented approach:
- Data redundancy and inconsistency:** The same information may be written in several files. This redundancy leads to higher storage and access cost. It may lead data inconsistency that is the various copies of the same data may longer agree for example a changed customer address may be reflected in single file but not else where in the system. 2) Difficulty in accessing data : The conventional file processing system do not allow data to retrieved in a convenient and efficient manner according to user choice. 3) Data isolation : Because data are scattered in various file and files may be in different formats with new application programs to retrieve the appropriate data is difficult. 4) Integrity Problems: Developers enforce data validation in the system by adding appropriate code in the various application program. How ever when new constraints are added, it is difficult to change the programs to enforce them. 5) Atomicity: It is difficult to ensure atomicity in a file processing system when transaction failure occurs due to power failure, networking problems etc. (atomicity: either all operations of the transaction are reflected properly in the database or non are) 6) Concurrent access: In the file processing system it is not possible to access a same file for transaction at same time 7) Security problems: There is no security provided in file processing system to secure the data from unauthorized user access. Database: A database is organized collection of related data of an organization stored in formatted way which is shared by multiple users. The main feature of data in a database are:
- Defining database schema : it must give facility for defining the database structure also specifies access rights to authorized users.
- Manipulation of the database: The dbms must have functions like insertion of record into database updation of data, deletion of data, retrieval of data
- Sharing of database: The DBMS must share data items for multiple users by maintaining consistency of data.
- Protection of database: It must protect the database against unauthorized users.
- Database recovery: If for any reason the system fails DBMS must facilitate data base recovery. Advantages of dbms: Reduction of redundancies: Centralized control of data by the DBA avoids unnecessary duplication of data and effectively reduces the total amount of data storage required avoiding duplication in the elimination of the inconsistencies that tend to be present in redundant data files. Sharing of data: A database allows the sharing of data under its control by any number of application programs or users. Data Integrity: Data integrity means that the data contained in the database is both accurate and consistent. Therefore data values being entered for storage could be checked to ensure that they fall with in a specified range and are of the correct format. Data Security: The DBA who has the ultimate responsibility for the data in the dbms can ensure that proper access procedures are followed including proper authentication schemas for access to the DBS and additional check before permitting access to sensitive data. Conflict resolution: DBA resolve the conflict on requirements of various user and applications. The DBA chooses the best file structure and access method to get optional performance for the application. Data Independence:
Data independence is usually considered from two points of views; physically data independence and logical data independence. Physical data Independence allows changes in the physical storage devices or organization of the files to be made without requiring changes in the conceptual view or any of the external views and hence in the application programs using the data base. Logical data independence indicates that the conceptual schema can be changed without affecting the existing external schema or any application program. Disadvantage of DBMS:
- DBMS software and hardware (networking installation) cost is high
- The processing overhead by the dbms for implementation of security, integrity and sharing of the data.
- centralized database control
- Setup of the database system requires more knowledge, money, skills, and time.
- The complexity of the database may result in poor performance. Database Basics: Data item: The data item is also called as field in data processing and is the smallest unit of data that has meaning to its users. Eg: “e101”,”sumit” Entities and attributes: An entity is a thing or object in the real world that is distinguishable from all other objects Eg: Bank,employee,student Attributes are properties are properties of an entity. Eg: Empcode,ename,rolno,name Logical data and physical data : Logical data are the data for the table created by user in primary memory. Physical data refers to the data stored in the secondary memory. Schema and sub-schema :
The external level is at the highest level of database abstraction. At this level, there will be many views define for different users requirement. A view will describe only a subset of the database. Any number of user views may exist for a given global or subschema. for example , each student has different view of the time table. the view of a student of Btech (CSE) is different from the view of the student of Btech(ECE).Thus this level of abstraction is concerned with different categories of users. Each external view is described by means of a schema called schema or schema. Conceptual level : At this level of database abstraction all the database entities and the relationships among them are included. One conceptual view represents the entire database. This conceptual view is defined by the conceptual schema. The conceptual schema hides the details of physical storage structures and concentrate on describing entities , data types, relationships, user operations and constraints. It describes all the records and relationships included in the conceptual view. There is only one conceptual schema per database. It includes feature that specify the checks to relation data consistency and integrity. Internal level : It is the lowest level of abstraction closest to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods to be used by the database. The internal view is expressed by internal schema. The following aspects are considered at this level:
- Storage allocation e.g: B-tree,hashing
- access paths eg. specification of primary and secondary keys,indexes etc
- Miscellaneous eg. Data compression and encryption techniques,optimization of the internal structures. Database users : Naive users : Users who need not be aware of the presence of the database system or any other system supporting their usage are considered naïve users. A user of an automatic teller machine falls on this category.
Online users : These are users who may communicate with the database directly via an online terminal or indirectly via a user interface and application program. These users are aware of the database system and also know the data manipulation language system. Application programmers : Professional programmers who are responsible for developing application programs or user interfaces utilized by the naïve and online user falls into this category. Database Administration : A person who has central control over the system is called database administrator. The function of DBA are :
- creation and modification of conceptual Schema definition
- Implementation of storage structure and access method.
- schema and physical organization modifications.
- granting of authorization for data access.
- Integrity constraints specification.
- Execute immediate recovery procedure in case of failures
- ensure physical security to database **Database language :
- Data definition language(DDL) :** DDL is used to define database objects .The conceptual schema is specified by a set of definitions expressed by this language. It also give some details about how to implement this schema in the physical devices used to store the data. This definition includes all the entity sets and their associated attributes and their relation ships. The result of DDL statements will be a set of tables that are stored in special file called data dictionary. 2) Data manipulation language(DML) : A DML is a language that enables users to access or manipulate data stored in the database. Data manipulation involves retrieval of data from the database, insertion of new data into the database and deletion of data or modification of existing data. There are basically two types of DML: procedural : Which requires a user to specify what data is needed and how to get it. non-rocedural: which requires a user to specify what data is needed with out specifying how to get it.
responsible of database manager to control the problems occurs for concurrent transactions. query processor: The query processor used to interpret to online user’s query and convert it into an efficient series of operations in a form capable of being sent to the data manager for execution. The query processor uses the data dictionary to find the details of data file and using this information it create query plan/access plan to execute the query. Data Dictionary: Data dictionary is the table which contains the information about database objects. It contains information like
- external, conceptual and internal database description
- description of entities , attributes as well as meaning of data elements
- synonyms, authorization and security codes
- database authorization The data stored in the data dictionary is called meta data. DBMS STRUCTURE: Naïve user Application On line user DBA programers Application System calls Ddl compiler programs Application prog Dml precomplier Query processor Ddl compiler obj code Database manager File manager DBMS Data file Data dictionary Q. List four significant differences between a file-processing system and a DBMS. Answer: Some main differences between a database management system and a file- processing system are:
- Both systems contain a collection of data and a set of programs which access that data. A database management system coordinates both the physical and the logical
access to the data, whereas a file-processing system coordinates only the physical access.
- A database management system reduces the amount of data duplication by ensuring that a physical piece of data is available to all programs authorized to have access to it, where as data written by one program in a file-processing system may not be readable by another program.
- A database management system is designed to allow flexible access to data (i.e., queries), whereas a file-processing system is designed to allow predetermined access to data (i.e., compiled programs).
- A database management system is designed to coordinate multiple users accessing the same data at the same time. A file-processing system is usually designed to allow one or more programs to access different data files at the same time. In a file-processing system, a file can be accessed by two programs concurrently only if both programs have read-only access to the file. Q.Explain the difference between physical and logical data independence. Answer:
- Physical data independence is the ability to modify the physical scheme without making it necessary to rewrite application programs. Such modifications include changing from unblocked to blocked record storage, or from sequential to random access files.
- Logical data independence is the ability to modify the conceptual scheme without making it necessary to rewrite application programs. Such a modification might be adding a field to a record; an application program’s view hides this change from the program. Q. List five responsibilities of a database management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged. Answer: A general purpose database manager (DBM) has five responsibilities: a. interaction with the file manager. b. integrity enforcement. c. security enforcement. d. backup and recovery. e. concurrency control. If these responsibilities were not met by a given DBM (and the text points out that sometimes a responsibility is omitted by design, such as concurrency control on a single-user DBM for a micro computer) the following problems can occur, respectively: a. No DBM can do without this, if there is no file manager interaction then nothing stored in the files can be retrieved.
- State advantage and disadvantage of database management system
- What ate different types of database users
- What is data dictionary and what are its contents
- What are the function of DBA
- What are the different database languages explain with example.
- Explain the three layer architecture of DBMS.
- Differentiate between physical data independence and logical data independence
- Explain the function of data base manager
- Explain meta data
CHAPTER-
ER-MODEL
Data model: The data model describes the structure of a database. It is a collection of conceptual tools for describing data, data relationships and consistency constraints and various types of data model such as
- Object based logical model
- Record based logical model
- Physical model Types of data model:
- Object based logical model a. ER-model b. Functional model c. Object oriented model d. Semantic model
- Record based logical model a. Hierarchical database model b. Network model c. Relational model
- Physical model Entity Relationship Model The entity-relationship data model perceives the real world as consisting of basic objects, called entities and relationships among these objects. It was developed to facilitate data base design by allowing specification of an enterprise schema which represents the overall logical structure of a data base. Main features of ER-MODEL:
- Entity relationship model is a high level conceptual model
- It allows us to describe the data involved in a real world enterprise in terms of objects and their relationships.
- It is widely used to develop an initial design of a database
- It provides a set of useful concepts that make it convenient for a developer to move from a baseid set of information to a detailed and description of information that can be easily implemented in a database system
- It describes data as a collection of entities, relationships and attributes.
Relationship sets: A relationship is an association among several entities. A relationship set is a set of relationships of the same type. Formally, it is a mathematical relation on n>=2 entity sets. If E1,E2…En are entity sets, then a relation ship set R is a subset of {(e1,e2,…en)|e1Є E1,e2 Є E2..,en Є En} where (e1,e2,…en) is a relation ship. customer (^) borro loan Consider the two entity sets customer and loan. We define the relationship set borrow to denote the association between customers and the bank loans that the customers have. Mapping Cardinalities: Mapping cardinalities or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. Mapping cardinalities are most useful in describing binary relationship sets, although they can contribute to the description of relationship sets that involve more than two entity sets. For a binary relationship set R between entity sets A and B, the mapping cardinalities must be one of the following: one to one: An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. Eg: relationship between college and principal 1 1 1 1 college (^) has principal One to many: An entity in A is associated with any number of entities in B. An entity in B is associated with at the most one entity in A. Eg: Relationship between department and faculty 11 M 1 Department Works Faculty in
Many to one: An entity in A is associated with at most one entity in B. An entity in B is associated with any number in A. 1 M Course Teach Faculty es Many –to-many: Entities in A and B are associated with any number of entities from each other. 1 M Customer Depos Account it More about entities and Relationship: Recursive relationships: When the same entity type participates more than once in a relationship type in different roles, the relationship types are called recursive relationships. Participation constraints: The participation constraints specify whether the existence of any entity depends on its being related to another entity via the relationship. There are two types of participation constraints Total : .When all the entities from an entity set participate in a relationship type , is called total participation. For example, the participation of the entity set student on the relationship set must ‘opts’ is said to be total because every student enrolled must opt for a course. Partial: When it is not necessary for all the entities from an entity set to particapte ion a relationship type, it is called participation. For example, the participation of the entity set student in ‘represents’ is partial, since not every student in a class is a class representative. Weak Entity: Entity types that do not contain any key attribute, and hence can not be identified independently are called weak entity types. A weak entity can be identified by uniquely only by considering some of its attributes in conjunction with the primary key attribute of another entity, which is called the identifying owner entity. Generally a partial key is attached to a weak entity type that is used for unique identification of weak entities related to a particular owner type. The following restrictions must hold:
- The owner entity set and the weak entity set must participate in one to may relationship set. This relationship set is called the identifying relationship set of the weak entity set.
ER-DIAGRAM:
The overall logical structure of a database using ER-model graphically with the help of an ER-diagram. Symbols use ER- diagram: entity Weak entity attribute Multi valued attribute Derived attribute Key attribute 1 1 One-to -one m 1 many-to -one composite attribute Relationship Identifying Relationship 1 m One-to -many m n many-to -many Total participation Partial participation
