Download Brief notes on Database Management System and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
UNIT I
Overview of Database Management System: Introduction, file-based system, Drawbacks of file-Based System ,Data and information, Database, Database management System, Objectives of DBMS, Evaluation of Database management System, Classification of Database Management System, DBMS Approach, advantages of DBMS, ANSI/SPARC Data Model, data models, Components and Interfaces of Database Management System. Database Architecture, Situations where DBMS is not Necessary, DBMS Vendors and Their Products. UNIT II Entity-Relationship Model: Introduction, the building blocks of an entity relationship diagram, classification of entity sets, attribute classification, relationship degree, relationship classification, reducing ER diagram to tables, enhanced entity-relationship model (EER model), generalization and specialization, IS A relationship and attribute inheritance, multiple inheritance, constraints on specialization and generalization, aggregation and composition, entity clusters, connection traps, advantages of ER modeling. UNIT III Relational Model: Introduction, CODD Rules, relational data model, concept of key, relational integrity, relational algebra, relational algebra operations, advantages of relational algebra, limitations of relational algebra, relational calculus, tuple relational calculus, domain relational Calculus (DRC). QBE. UNIT IV Structured Query Language: Introduction, History of SQL Standard, Commands in SQL, Data Types in SQL, Data Definition Language, Selection Operation, Projection Operation, Aggregate functions, Data Manipulation Language, Table Modification Commands, Table Truncation, Imposition of Constraints, Join Operation, Set Operation, View, Sub Query, Embedded SQL, UNIT V PL/SQL: Introduction, Shortcoming in SQL, Structure of PL/SQL, PL/SQL Language Elements, Data Types, Operators Precedence, Control Structure, Steps to Create a PL/SQL, Program, Iterative Control, Cursors, Steps to create a Cursors, Procedure, Function, Packages, Exceptions Handling, Database Triggers, Types of Triggers.
CONVENTIONAL FILE PROCESSING SYSTEM:-
The information system can be either a conventional file processing system or a database system. In the conventional file processing system, each and every subsystem of the information system will have its own set of files. As a result, there will be duplication of data between various subsystems of the information system. The concept of conventional file processing system is ` The different inputs and outputs of these applications are Application1 input is file1 and file2 and output is Report1 and Report Application2 input is file1 and file3 and output is Report3, Report4 and Report5. Drawback of conventional file processing system:- In File System, files are stored in different places. There is no relation between the files. Files are developed by different persons on different locations. The list of drawbacks of this system is
- Uncontrolled data redundancy
- Inconsistency of data
- Inflexibility of the system
- Limited data sharing
- Poor enforcement of standards
- Concurrent access
- Low programming productivity
- No Security
- No Backup & Recovery facilities Report1 Report Application File 1 File 2 Report3 Report Application File 1 File 3 Report
Poor enforcement of standards:- Different applications are developed by different persons, each person will follow its own standards of defining field name, field width, and field type. This will create a serious difficulty while modifying programs, sometimes there will be serious errors due to mismatch of fields. Limited Concurrent Access:- In FS, Concurrent Access means multiple users can access the data simultaneously. But File system does not support multiple users to access the data. Limited Security:- File System has no password protection to the data. So unauthorised persons can access the data. So it has limited security. No Backup & Recovery Facilities:- File System has no backup and recovery facilities. Backup means the original data will be stored in different device. If the original data was removed then the backup data will be stored in original place is called recovery. Low programmer productivity:- Programmer productivity is a measure of time taken to develop an application. Programming productivity is always depends on developing time. Programming productivity is inversely proportional to developing time. File system has so many drawbacks, these are data redundancy, inflexibility, and poor enforcement standards and excessive maintains effort, the programmer productivity will become lower.
DATABASE SYSTEM:-
Database is a collection of related file. File is a collection of records. Record is a collection of fields. Database Management System (DBMS):- DBMS is a collection of Computer programs to process the database. It is a collection of database, database utilities (DBMS software), and data dictionary/ directory, operated by user groups developed by application developers and administrated by database administrator. Advantages of Database system/Approach:- Database is a collection of related files. These files are shared the data each other. So database has so many advantages. These are Controlled data redundancy Consistency of data Flexibility of the data High data sharing Concurrent Access High Security High Backup & Recovery Facilities Better enforcement of standards Increased programmer productivity Reduced data redundancy:- The database has a single centralized database. The data will be shared between the files. The same data will not be appeared between the files, then redundancy will be minimized. Student Sno Sname Address 1000 Jhansi Rajam 1001 Keerthi^ Tekkali 1002 Sahithi Tekkali Sno MM PM CM 1000 88 87 55 1001 90 60 70 1002 67 56 67
Database has so many advantages like controlled data redundancy, consistent of data etc. So programming productivity is always high. Limitations of DBMS:- Database Systems are so complex, difficult and time-consuming to design. Substantial hardware and software start up costs. Damage to database affects virtually all application programs. Extensive conversion costs in moving form a title-based system to database. Initial training required for all programmers and users. COMPONENTS OF DATABASE MANAGEMENT SYSTEM:- DBMS consists of database, DBMS utilities, data dictionary, application developers, users and Database Administrator. DBMS:- These are like ORACLE, Sybase, FOCUS, IDMS, IMS, etc. These are the database languages used to develop different applications. Many of these languages belong to fourth generation language (4GLs), which have many different features. In these database languages, data is separated from programs. These are menu based programs These are very expensive than file systems. Database :- It is a collection of related files. In database system has an integrated single database; all the problems of convention file processing system will be minimized. The Database is removed the redundancy, consistent results, flexible, data sharing between the files and high programming productivity.
Data dictionary:- It is also called as Data Directory. It is a data about data in the database. It is also called as metadata. This will contain information like Total Number of files in the database Names of the files Relationship between the files It also specifies field name, field type and field size for each file. Application developers:- These are the qualified programmers/analysts. They can write and develop programs. These programs are developed under the guidance of DBA. These programs are used by user groups. User groups:- These are group of end users. They can use the programs developed by the Application developers. Database Administrator :- It is the apex body. It controls and coordinates all the software entities, database entities, application developers and user groups. DBMS Utilities User Groups Application Developers Database Administrator Data Dictionary/ Data Directory Database
- Natural Language Interfaces : These interfaces accept requests written in English or some other language to "understand" them. A natural language interface usually has its own "schema," which is similar to the database conceptual schema. If the interpretation is successful, the interface generates a high-level query corresponding to the natural language request and submits it to the DBMS for processing; otherwise, a dialogue is started with the user to clarify the request.
- Interfaces for Parametric Users : Parametric users, such as bank tellers, often have a small set of operations that they must perform repeatedly. Systems analysts and programmers design and implement a special interface for each known class of naive users. Usually, a small set of abbreviated commands is included, with the goal of minimizing the number of keystrokes required for each request.
- Interfaces for the DBA : Most database systems contain privileged commands that can be used only by the DBA's staff. These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, and reorganizing the storage structures of a database.
ANSI SPARC MODEL:- LEVELS OF DATABASE OR DATA ABSTRACTION
It stands for American National Standard Institute, Standards Planning And Requirements Committee. It is an abstract design standard for a DBMS, proposed in 1975. A DBMS is a collection of interrelated files and a set of programs. Data abstraction allows users to access and modify these files. It has 3 levels of architectures. These are
1. Physical Level:- It is also called as Internal Level. It is lowest level of abstraction. It describes how the data are actually stored on storage devices. It provides the internal view of physical storage of data. It also describes complex low-level data structures, file structures and access methods. It also deals with Data Compression and Encryption techniques. Database designer or system programmer uses it. 2. Logical Level:- It is also called as conceptual level. The next higher level of abstraction. It describes what data are actually stored in the database and what relationships exist among the data. It describes a small number of simple structures. Database administrators use logical level of abstraction. Application developers works on this level. 3. View Level:- It is the highest level of abstraction. It also known as an External level. It describes only part of entire database. End users need to access only part of the database. It is closest to users.
Logical data independence is more difficult to achieve than the physical data independence, since application programs are mainly depend on logical structure of the data. The concept of data independence is similar to the concepts of abstract data types in programming languages. Schema:- An overview of all the files in a database is called as Schema. The schema defines the tables, the fields in each table, and the relationships between fields and tables. Schemas are generally stored in a data dictionary. It can be divided into 3 types. These are
1. View schema:- The Total files in a view level are called as a conceptual or view schema. It is also called as a sub-schema. It tells about the concepts and their relationships 2. Logical schema:- The Total files in a logical level are called a logical schema. It tells about a map of entities and their attributes. 3. Physical schema:- The total files in a physical level are called a physical schema. It tells about a particular implementation of a logical schema.
Data Models in DBMS:- It is a collection of tools or techniques used for describing data, data relationships, database rules and database constraints (limits). They are classified into 3 types. These are
- Object based Data Model
- Record based Data Model
- Physical based Data Model 1. Object base Data Model:- These are used in describing data and data relationships. Entity-Relationship Data Model, Semantic Data Model, Binary Model and Object Oriented Data Models are examples of this type. 2. Record base Data Model:- These models are used to specify the overall logical structure of the database. It can be divided into 3 types. These are o Hierarchical Data Model o Network Data Model o Relational Data Model 3. Physical base Data Model:- These models are used to specify the storage structure of the database and their access mechanisms. It is used to specify the stored record formats, database security, back-up and recovery methods. Unifying Model and frame Model are examples of this type.
i) Simple Network Data Model:- It consists of a set of files with one-to-one and one-to-Many pair-wise associations. Many-to-Many association is not permitted in this model. We can easy to implementation the object based data model to simple network data model. All the associations are one-to-many type. Hence, it is very easy to implement. ii) Complex Network Data Model:- It has at least one many-to-many association. This will make the model very difficult to implement. iii) Limited Network Data Model:- In this model, each and every file of the simple network data model will be divided into master file and transaction file. This representing the relationships from master file to transaction files. Eg:- NDBMS Disadvantages:- It is very difficult to understand. Any change to an existing NDBMS is very difficult. Any simple change can result in a complicated and time consuming process. It needs extra memory to store the address of the linked list node. Relational Data Model:- It was proposed by Dr. E.F.Codd. It uses the concept of relations to represent each and every file. Relations are Two-Dimensional Tables. It is easy to implement and easy to simplification in the operations to manipulating the data. This is most popular data model. It is simple to implement. Its terminologies are similar to object base data model.
It uses the primary key and secondary key to connect any two files. Normalization Theory is used to design the object based data model. Many of the database languages are oriented handling relational data model. A RDM consists of relations with to connect them by key fields. A relation has some attributes. They are The relation is represented in rows and columns. Each column of the relation is called as attribute. Each row in the relation is called as tuple. Each relation can have one unique columns i.e. primary key. Each relation can have n-columns and n-tuple. Each relation is preceded by the name of that relation. The fields of the relations are separated by commas and placed within the parentheses of the relation. STUDENT (StudNo, Sname, Special) ENROLLMENT (StudNo, Subcode, marks) SUBJECT (Subcode, Subname, Maxmarks, Faccode) FACULTY (Faccode, Fname, Dept) StudNo Sname Special 100 Keerthi Computer 101 Jhansi Maths 102 Sahithi Chemistry StudNo Subcode marks 100 C100 135 101 C101 145 102 C102 150 Subcode Subname Maxmarks Faccode C100 Computers 150 F C101 Maths 150 F C102 Physics 150 F Faccode Fname Deptname F100 Tarak Computer F101 Ramesh Physics F102 Ravi Chemistry
ENTITY-RELATIONSHIP MODEL:-
It was introduced by Chen in 1976 and modified by James Martin. It is the best example of Object based data model. It defines the conceptual view of a database. It is a systematic way of describing and defining a database. It consists of mainly Entities, Attributes, Relationships and ER diagrams. Entity: - An Entity is a thing or an object or a file. Entity in the E-R model corresponds to a Table. An entity is a collection of attributes and its values. An entity set is a collection of similar types of entities. It can be represented in Rectangle form. Example:- STUDENT, SUBJECT, CUSTORMER etc., It can be represented in 5 types of attributes. These are
- Strong Entity
- Weak Entity
- Super class Entity
- Sub class Entity
- Recursive Entity 1. Strong Entity:- An Entity does not depend on other entities is called as Strong entities. It contains a primary key column. It can be represented by a rectangle form. 2. Weak Entity:- An Entity depends on other entities is called as weak entity. It does not contain primary key. It can be represented by a double outlined rectangle.
STUDENT SUBJECT CUSTOMER
STUDENT EMP
3. Super Class Entity:- An entity can be developed to other entities is called as super class entity. 4. Sub Class Entity:- An entity can be derived from other entity is called as sub class entity. It gains the properties of entity. 5. Recursive Entity:- It is an entity with foreign key referencing to same entity or itself. It is also called self referential relationship entity type. Attributes: - It is lowest level of data item in an Entity. It is the name of the column or field. It gives the characteristics of the entity. It can be represented in “Ellipse” or “Oval” form. Example:- Studno, Sname are attributes of STUDENT entity. Empno, Ename, job, sal are attributes of EMPLOYEE entity. It can be divided into 9 types. These are 1. Simple attribute 2. Compound attribute 3. Single Valued attribute 4. Multivalued attribute 5. Stored attribute 6. Derived attribute 7. Key attribute 8. Non key attribute 9. Complex attribute
DEPT FACULTY
STUDNO
