INTRODUCTION TO DBMS - College of Engineering and ...

1 DATABASE Engineering DATABASE Engineering LECTURE NOTES Prepared by Dr. Subasish Mohapatra Department of Computer Science and Application College of Engineering and Technology, Bhubaneswar Biju Patnaik University of Technology, Odisha SYLLABUS Course Code: PCCS4204 Subject Name: DataBase Engineering Credit:4 Module1: (12 Hrs) INTRODUCTION to database Systems, Basic concepts &Definitions, data Dictionary, DBA, File-oriented system vs. Database System, Database Language. Database System Architecture-Schemas, Sub Schemas & Instances, 3-level database architecture, data Abstraction, data Independence, Mappings, Structure, Components & functions of DBMS, data models, Mapping E-R model to Relational, Network and Object Oriented data models, types of Database systems, Storage Strategies: Detailed Storage Architecture, Storing data , Magnetic Disk, RAID, Other Disks, Magnetic Tape, Storage Access, File & Record Organization, File Organizations & Indexes, Order Indices, B+ Tree Index Files, Hashing Module2.

2 (16 Hrs) Relational Algebra, Tuple & Domain Relational Calculus, Relational Query Languages: SQL and QBE. Database Design :-Database development life cycle(DDLC),Automated design tools, Functional dependency and Decomposition, Dependency Preservation & lossless Design, Normalization, Normal forms:1NF, 2NF,3NF,and BCNF, Multi-valued Dependencies, 4NF & 5NF. Query processing and optimization: Evaluation of Relational Algebra Expressions, Query optimization. Module3: (12 Hrs) Transaction processing and concurrency control: Transaction concepts, concurrency control, locking and Timestamp methods for concurrency control.

3 Database Recovery System: Types of data Base failure & Types of Database Recovery, Recovery techniques Advanced topics: Object-Oriented & Object Relational Database, Parallel & Distributed Database, INTRODUCTION to data warehousing & data Mining Text Books: 1. Database System Concepts by Sudarshan, Korth (McGraw-Hill Education) 2. Fundamentals of Database System By Elmasari &Navathe- Pearson Education References Books: (1) An INTRODUCTION to Database System Bipin Desai, Galgotia Publications (2) Database System: concept, Design & Application by (Pearson Education) (3) Database management system by leon &leon (Vikas publishing House).

4 (4) Database Modeling and Design: Logical Design by Toby J. Teorey, Sam S. Lightstone, and Tom Nadeau, , 4th Edition, 2005, Elsevier India Publications, New Delhi (5) Fundamentals of Database Management System Gillenson, Wiley India CONTENTS LECTURE-1: INTRODUCTION to data LECTURE-2: DBMS LECTURE-3: 3 level Architecture of DBMS LECTURE-4: Elements of DBMS LECTURE-5: ER-MODEL LECTURE-6: ER-DIAGRAM: LECTURE-7: Advanced ER-Diagram: LECTURE-8: Conversion of ER-Diagram to Relational Database LECTURE-9: Record Based Logical Model LECTURE-10: RELATIONAL MODEL LECTURE-11: CONSTRAINTS LECTURE-12: FILE ORGANISATION LECTURE-13: INDEX LECTURE-14: Clustering Index LECTURE-15.

5 B+ Tree Index LECTURE-16: Hash File Organization LECTURE-17: Query Processing LECTURE-18: Evaluation of Expressions LECTURE-19 Relational Algebra LECTURE-20 Additional Operations LECTURE-21 Tuple Relational Calculus LECTURE-22 Structured Query Language (SQL) LECTURE-23 Nested Sub queries LECTURE-24 Integrity Constraints LECTURE-25 Query by Example (QBE) LECTURE-26 Relational Database Design LECTURE-27 Closure of a set of Functional Dependencies LECTURE-28 Loss less Decomposition LECTURE-29 Normalization LECTURE-30 Boyce-Code Normal Form (BCNF) LECTURE-31 Query Processing LECTURE-32 Query Optimization LECTURE-33 Transaction LECTURE-34 Problems due to locking LECTURE-35 Multiversion Technique Based on Timestamp Ordering LECTURE-36 Serializability LECTURE-37 Object Oriented Databases LECTURE-38 Parallel Database Module-1: LECTURE-1: INTRODUCTION to data INTRODUCTION : In computerized information system data are the basic resource of the organization.

6 So, proper organization and management for data is required for 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 are the known facts or figures that have implicit meaning.

7 It can also be defined as it is the representation of facts, concepts or instructions in a formal manner, which is suitable for understanding and processing. data can be represented in alphabets (A-Z, a-z), digits (0-9) and using special characters (+,-.#,$, etc) : 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.

8 File Oriented Approach: The traditional file oriented approach to information processing each application has a separate master file and its own set of personal file. In file oriented approach the program dependent on the files and files dependent upon the programs. Disadvantages of file oriented approach: 1) 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 present at multiple places for example a changed customer address may be reflected in single file but not else where in the system.

9 2) Difficulty in accessing data : The conventional file processing system do not allow data to be retrieved in a convenient and efficient manner according to user choice. 3) data isolation : Because data are scattered in various files 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.

10 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 the same file for transaction at same the time. 7) Security problems: There is no security provided in file processing system to secure the data from unauthoriz-ed user access. LECTURE-2: DBMS Database: A database is organized collection of related data of an organization stored in formatted way which is shared by multiple users.