Database Management Systems: Concepts and Relational Model, Study notes of Database Programming

Download Database Management Systems: Concepts and Relational Model and more Study notes Database Programming in PDF only on Docsity!

BIJU PATNAIK UNIVERSITY OF TECHNOLOGY,

ODISHA

Lecture Notes

On

Prepared by,

Dr. Subhendu Kumar Rath,

BPUT, Odisha.

DBMS

Prepared by: Dr. Subhendu Kumar Rath

DBMS: Basic Concepts

1. Introduction
2. Disadvantages of file oriented approach
3. Database
4. Why Database
5. Database Management System(DBMS)
6. Function of DBMS
7. Advantages of DBMS and disadvantage of DBMS
8. Database Basics
9. Three level architecture of DBMS
10. Database users
11. Database language
12. 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.

Prepared by: Dr. Subhendu Kumar Rath

1. It must be well organized
2. it is related
3. It is accessible in a logical order without any difficulty
4. It is stored only once for example: consider the roll no, name, address of a student stored in a student file. It is collection of related data with an implicit meaning. Data in the database may be persistent, integrated and shared.

Persistent: If data is removed from database due to some explicit request from user to remove. Integrated: A database can be a collection of data from different files and when any redundancy among those files are removed from database is said to be integrated data. Sharing Data: The data stored in the database can be shared by multiple users simultaneously with out affecting the correctness of data.

Why Database:

In order to overcome the limitation of a file system, a new approach was required. Hence a database approach emerged. A database is a persistent collection of logically related data. The initial attempts were to provide a centralized collection of data. A database has a self describing nature. It contains not only the data sharing and integration of data of an organization in a single database.

A small database can be handled manually but for a large database and having multiple users it is difficult to maintain it, In that case a computerized database is useful. The advantages of database system over traditional, paper based methods of record keeping are:  compactness : No need for large amount of paper files  speed : The machine can retrieve and modify the data more faster way then human being  Less drudgery : Much of the maintenance of files by hand is eliminated  Accuracy: Accurate,up-to-date information is fetched as per requirement of the user at any time.

Database Management System (DBMS): A database management system consists of collection of related data and refers to a set of programs for defining, creation, maintenance and manipulation of a database.

Function of DBMS:

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

1. Defining database schema : it must give facility for defining the database structure also specifies access rights to authorized users.
2. Manipulation of the database: The dbms must have functions like insertion of record into database updation of data, deletion of data, retrieval of data
3. Sharing of database: The DBMS must share data items for multiple users by maintaining consistency of data.
4. Protection of database: It must protect the database against unauthorized users.
5. 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:

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

A schema is a logical data base description and is drawn as a chart of the types of data that are used. It gives the names of the entities and attributes and specify the relationships between them.

A database schema includes such information as :

 Characteristics of data items such as entities and attributes.  Logical structures and relationships among these data items.  Format for storage representation.  Integrity parameters such as physical authorization and back up policies.

A subschema is derived schema derived from existing schema as per the user requirement. There may be more then one subschema create for a single conceptual schema.

Three level architecture of DBMS :

A database management system that provides three level of data is said to follow three- level architecture.  External level  Conceptual level  Internal level External level :

External level

Conceptual level

View user

View User

View User n

Mapping supplied by DBMS

Conceptual view

Mapping supplied by DBMS/OS

Internal level

Prepared by: Dr. Subhendu Kumar Rath

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:

1. Storage allocation e.g: B-tree,hashing
2. access paths eg. specification of primary and secondary keys,indexes etc
3. 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.

Prepared by: Dr. Subhendu Kumar Rath

3) Data control language(DCL): This language enables user to grant authorization and canceling authorization of database objects.

Elements of DBMS:

DML pre-compiler:

It converts DML statement embedded in an application program to normal procedure calls in the host language. The pre-complier must interact with the query processor in order to generate the appropriate code.

DDL compiler:

The DDL compiler converts the data definition statements into a set of tables. These tables contains information concerning the database and are in a form that can be used by other components of the dbms. File manager:

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

Database manager:

A database manager is a program module which provides the interface between the low level data stored in the database and the application programs and queries submitted to the system. The responsibilities of database manager are:

1. Interaction with file manager : The data is stored on the disk using the file system which is provided by operating system. The database manager translate the the different DML statements into low-level file system commands. so The database manager is responsible for the actual storing,retrieving and updating of data in the database.
2. Integrity enforcement: The data values stored in the database must satisfy certain constraints(eg: the age of a person can't be less then zero).These constraints are specified by DBA. Data manager checks the constraints and if it satisfies then it stores the data in the database.
3. Security enforcement: Data manager checks the security measures for database from unauthorized users.
4. Backup and recovery: Database manager detects the failures occurs due to different causes (like disk failure, power failure,deadlock,s/w error) and restores the database to original state of the database.
5. Concurrency control: When several users access the same database file simultaneously, there may be possibilities of data inconsistency. It is

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

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

1. external, conceptual and internal database description
2. description of entities , attributes as well as meaning of data elements
3. synonyms, authorization and security codes
4. database authorization The data stored in the data dictionary is called meta data. DBMS STRUCTURE:

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

Naïve user Application programers

On line user DBA

Application programs

System calls Ddl compiler

Application prog obj code

Dml precomplier Query processor Ddl compiler

Database manager

File manager

Data file

Data dictionary

DBMS

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

b. Consistency constraints may not be satisfied, account balances could go below the minimum allowed, employees could earn too much overtime (e.g.,hours > 80) or, airline pilots may fly more hours than allowed by law. c. Unauthorized users may access the database, or users authorized to access part of the database may be able to access parts of the database for which they lack authority. For example, a high school student could get access to national defense secret codes, or employees could find out what their supervisors earn. d. Data could be lost permanently, rather than at least being available in a consistent state that existed prior to a failure. e. Consistency constraints may be violated despite proper integrity enforcement in each transaction. For example, incorrect bank balances might be reflected due to simultaneous withdrawals and deposits, and so on. Q. What are five main functions of a database administrator?

Answer: Five main functions of a database administrator are:

• To create the scheme definition
• To define the storage structure and access methods
• To modify the scheme and/or physical organization when necessary
• To grant authorization for data access
• To specify integrity constraints

Q. List six major steps that you would take in setting up a database for a particular enterprise. Answer: Six major steps in setting up a database for a particular enterprise are:

• Define the high level requirements of the enterprise (this step generates a document known as the system requirements specification.)
• Define a model containing all appropriate types of data and data relationships.
• Define the integrity constraints on the data.
• Define the physical level.
• For each known problem to be solved on a regular basis (e.g., tasks to be carried out by clerks or Web users) define a user interface to carry out the task, and write the necessary application programs to implement the user interface.
• Create/initialize the database.

EXERCISES:

1. What is database management system
2. What are the disadvantage of file processing system

Prepared by: Dr. Subhendu Kumar Rath

3. State advantage and disadvantage of database management system
4. What ate different types of database users
5. What is data dictionary and what are its contents
6. What are the function of DBA
7. What are the different database languages explain with example.
8. Explain the three layer architecture of DBMS.
9. Differentiate between physical data independence and logical data independence
10. Explain the function of data base manager
11. Explain meta data

Prepared by: Dr. Subhendu Kumar Rath

Basic concepts:

The E-R data model employs three basic notions : entity sets, relationship sets and attributes. Entity sets:

An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. For example, each person in an enterprise is an entity. An entity has a set properties and the values for some set of properties may uniquely identify an entity. BOOK is entity and its properties(calles as attributes) bookcode, booktitle, price etc. An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all persons who are customers at a given bank, for example, can be defined as the entity set customer. Attributes:

An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set.

Customer is an entity and its attributes are customerid, custmername, custaddress etc.

An attribute as used in the E-R model , can be characterized by the following attribute types. a) Simple and composite attribute:

simple attributes are the attributes which can’t be divided into sub parts eg: customerid,empno composite attributes are the attributes which can be divided into subparts. eg: name consisting of first name, middle name, last name address consisting of city,pincode,state b) single-valued and multi-valued attribute: The attribute having unique value is single –valued attribute eg: empno,customerid,regdno etc. The attribute having more than one value is multi-valued attribute eg: phone-no, dependent name, vehicle

c) Derived Attribute:

The values for this type of attribute can be derived from the values of existing attributes eg: age which can be derived from (currentdate-birthdate) experience_in_year can be calculated as (currentdate-joindate)

d) NULL valued attribute: The attribute value which is unknown to user is called NULL valued attribute.

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

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.

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

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

customer (^) borro loan

college (^) has principal

Department (^) Works Faculty in

1 1 M 1

Prepared by: Dr. Subhendu Kumar Rath, BPUT.

• The weak entity set must have total participation in the identifying relationship. Example: Consider the entity type dependent related to employee entity, which is used to keep track of the dependents of each employee. The attributes of dependents are : name ,birthrate, sex and relationship. Each employee entity set is said to its own the dependent entities that are related to it. How ever, not that the ‘dependent’ entity does not exist of its own., it is dependent on the employee entity. In other words we can say that in case an employee leaves the organization all dependents related to without the entity ‘employee’. Thus it is a weak entity.

Keys: Super key: A super key is a set of one or more attributes that taken collectively, allow us to identify uniquely an entity in the entity set. For example , customer-id,(cname,customer-id),(cname,telno) Candidate key: In a relation R, a candidate key for R is a subset of the set of attributes of R, which have the following properties:

• Uniqueness: no two distinct tuples in R have the same values for the candidate key
• Irreducible: No proper subset of the candidate key has the uniqueness property that is the candidate key. Eg: (cname,telno) Primary key: The primary key is the candidate key that is chosen by the database designer as the principal means of identifying entities with in an entity set. The remaining candidate keys if any, are called alternate key.

Prepared by: Dr. Subhendu Kumar Rath

ER-DIAGRAM:

The overall logical structure of a database using ER-model graphically with the help of an ER-diagram. Symbols use ER- diagram:

composite attribute

entity

Weak entity

attribute

Multi valued attribute

Derived attribute

Key attribute

Relationship

Identifying Relationship

One-to - one One-to - many

many-to - one many-to - many

1 m

m 1 m n

Total participation Partial participation