Note for Database Management System - DBMS by harika emany

Topics ( 11 )

Text from page-1

____________________________________D Daattaabbaassee M Maannaaggeem meenntt SSyysstteem mss SYLLABUS : Database Fundamentals: DBMS Characteristics & Advantages, Database Environment, Database Users, Database Architecture, Data Independence, Languages, Tools and Interface in DBMS, DBMS types. Data Modeling: ER Model, Notation used in ER Diagram, Constraint, Types, Relationships in ER Model and other considerations in designing ER diagram. Enhanced ER data Model, EER Diagram, Specialization and Generalization, Lattice, Union, Disjoint Properties, Constraints and relationships, Other issues in designing EER diagrams, Algorithms for ER to relational mapping. SQL: Data Definition and other languages in SQL, Creating tables and Data types, Constraints, DML statements, Functions and writing SQL statements using nested sub queries, complex queries, joining relations, Embedded SQL- Writing functions and procedures with PL/SQL, Relational Model, Relational Algebra, Operators in relational algebra. Normalization: Guidelines for good database design, Normalization- Normal Forms, First, Second, Third Normal Forms, BCNF, Multi value and join dependencies, 4th and 5th normal forms. Decomposition algorithms for normalization. File and storage structures: File storage, Index structures, Indexing and hashing. Query Processing: Issues in query processing, simple algorithms for insert, project, join and other operators. Transaction Processing: Transaction processing issues, Transaction states, problems during multiple transactions processing, ACID properties, system log, Concurrency control techniques: binary locks, exclusive locks, Lock based techniques, Timestamp based techniques, versioning in locks, Multi-version locking techniques. Textbooks: "Fundamentals of data base systems" 4th eddition Elmasri Navathe, publisher: addison-wesley Reference books: 1. Database management systems -3rd, illustrated mcgraw-hill raghu ramakrishnan 2. Database management system by korth. Publisher: tata mcgraw hill by abraham silberschatz, henry f. Korth and s. Sudarshan 3. ?An introduction to database systems?, C j Date,6th edition, addison wesley longman inc1999. :: DATABASE AND DATABASE USERS 1 ********TVSN KLU ECM*********

Text from page-2

____________________________________D Daattaabbaassee M Maannaaggeem meenntt SSyysstteem mss 1. Define the term data? A collection of known facts can be recorded and which have implicit meaning, is known as Data. Example: Stud_name, Emp_Id, Emp_Photo, Movie_clip, etc. 2. Define the term Database? A Database is a collection of related data, used for specific purpose by one or more groups of users and can be stored in auxiliary storage with minimum redundancy. The Database must be organized and managed so that users can search for, retrieve and update the data as they need. Example: Student [ Stud_id, Stud_name, Course_join, Fee_paid, Date_of_Join ] Employee [ Emp_id, Emp_name, Emp_desig, Emp_sal, Emp_dept ] 3. Define the term DBMS? A DBMS is a collection of programs that enables users to create and maintain Database. The DBMS facilitates the process of defining, constructing and manipulating Databases. 4. Define the term Metadata? The definition of data is known as metadata. It includes the structure of each file, the type and storage format of each data item and various constraints on the data. Example: Employee Emp_id number(2) Primary Key, Emp_name varchar2(20) not null, Emp_desig varchar2(15) not null, Date_join date, Emp_sal number(9,2) check (emp_sal between 1000 and 35000) 5. Explain the characteristics of the Database approach? The main characteristics of the Database approach are: a. Self defining nature of the Database system. b. Insulation between programs and data and data abstraction. c. Support of multiple vies of data. d. Sharing of data and multi user transaction processing. a) Self defining nature of the Database system The fundamental characteristic of the Database approach is defining the metadata, which means that definition of data and its domain. This metadata can be placed in a centralized 2 ********TVSN KLU ECM*********

Text from page-3

____________________________________D Daattaabbaassee M Maannaaggeem meenntt SSyysstteem mss - location called repository or catalog. This repository is used by DBMS software and also by Database users who need information about the Database structure. In traditional file processing, a data definition is a part of the application programs themselves. So these programs are constrained to work with only one specific Database, whose structure is declared in the application programs. 1. The file processing software can access only specific Databases, whereas DBMS software can access number of Databases by extracting Database definitions from the repository. 2. In file processing software, when a request can made by a user, the exact location of that record was known by the application program and then locate that record, whereas in DBMS the DBMS software refers the repository to determine the structure of the Database and then find the record location. b) Insulation between programs and data and data abstraction In traditional file processing system, the structure of data file is included in the programs, so any changes to the structure of a data file may require changing all programming that access that file. But in DBMS, the access program don’t require changes the structure of the file, because the structure of Database or data file is stored in repository which is centralized location to all access programs of the programs. This property is known as program data independence. In object oriented Databases, users can define operations on data as part of the Database definitions. An operation can be in two parts. The interface and implementation. The interface includes the operation name and the data types of its arguments. The implementation (function/method) is specified separately and can be changed without effecting the interface. The user application programs can operate on data by invoking the operations with their arguments rather than how the operations are implemented. This is called program-operation independence. This characteristic that allows both program data independence and program operation independence is called as data abstraction. c) Support multiple views of data A view is a subset of Database or it may contain virtual data that may derive from a base table or Database. A multi user DBMS whose users may have several applications must provide facilitate for defining multiple views on one Database. d) Sharing of data and multi user transaction processing 3 ********TVSN KLU ECM*********

Text from page-4

____________________________________D Daattaabbaassee M Maannaaggeem meenntt SSyysstteem mss A Client/Server or multiuser DBMS must allow multiple users to access the Database at the same time. For this the DBMS must include concurrency control software to ensure that several users trying to update the same data. These updations are maintained by the concurrent control software. Also these types of applications are referred as OLAP applications. 6. Explain the advantages of using DBMS? For good DBMS software, there are several advantages. They are: a) Controlling redundancy. b) Restricting unauthorized access. c) Providing persistent storage for program objects and data structures. d) Permitting interfering and actions using rules. e) Providing multiple user interfaces. f) Representing complex relationships and data. g) Enforcing integrity constraints. h) Providing backup and recovery. a. Controlling redundancy Redundancy means, storing of same data multiple times in multiple files. This leads several problems. 1. If any data item needs to update, it need to update multiple times. 2. Storage space was waster when a system have redundancy. This may creates problem when we use large Databases. 3. Due to redundancy the data consistency was in very high rate. In DBMS, due to we have repository/catalog, the data can be stored at a centralized location, utmost redundancy was eliminated/controlled. When the redundancy was controlled, it will be improved the Database engine’s performance. b. Restricting unauthorized access When we have multiple user environments, typically the Client/Server environment, the users share a Database. In those, some users are fully authorized and some users are partial authorized. Some users may permitted on only retrieving or selection operation, some users may permitted to update and others may permitted both operations. To restrict this type of accessing, the DBMS has given an account name and passwords for each user group. Also the DBMS should provide a security and authorization subsystem. Some users called DBA may only have fully privileged user groups. c. Providing persistent storage for program objects and data structure Databases can be used to provide persistent storage for programs and the data Structures (data members/data objects). For ordinary Databases the DBMS used to store 4 ********TVSN KLU ECM*********