Note for Database Management System - DBMS By Umesh Kumar

Topics ( 13 )

Text from page-1

MCA III SEMETEr NOTES ON rELATIONAL DATABASE MANAGEMENT SYSTEMS (OrACLE) MCA II YEAr III SEMETEr (SUBCOD-303) Mr. UMESH KUMAr, MCA DEPArTMENT OF COMPUTEr SCIENCE AND INFOrMATION TECHNOLOGY GUrU GHASIDAS VISHWAVIDYALAY, BILASPUr BILASPUr,(C.G),KONI ,CHHATTISAGArH 495009 Mr. UMESH KUMAr, MCA (GGU) PAGE | 1

Text from page-2

Relational Data Base Management System 1. Overview of Database Management : Data, Information and knowledge, Increasing use of data as a corporate resource, data processing verses data management, file oriented approach verses database oriented approach to data management; data independence, database administration roles, DBMS architecture, different kinds of DBMS users, importance of data dictionary, contents of data dictionary, types of database languages. Data models: network, hierarchical, relational. Introduction to distributed databases. 2. Relational Model : Entity - Relationship model as a tool for conceptual design-entities attributes and relationships. ER diagrams; Concept of keys: candidate key, primary key, alternate key, foreign key; Strong and weak entities, Case studies of ER modelling Generalization; specialization and aggregation. Converting an ER model into relational Schema. Extended ER features. 3. Structured Query Language : Relational Algebra: select, project, cross product different types of joins (inner join, outer joins, self join); set operations, Tuple relational calculus, Domain relational calculus, Simple and complex queries using relational algebra, stand alone and embedded query languages, Introduction to SQL constructs (SELECT…FROM, WHERE… GROUP BY… HAVING… ORDERBY….), INSERT, DELETE, UPDATE, VIEW definition and use, Temporary tables, Nested queries, and correlated nested queries, Integrity constraints: Not null, unique, check, primary key, foreign key, references, Triggers. Embedded SQL and Application Programming Interfaces. 4. Relational Database Design : Normalization concept in logical model; Pitfalls in database design, update anomalies: Functional dependencies, Join dependencies, Normal forms (1NF, 2NF, 3NF). Boyce Codd Normal form, Decomposition, Multi-Valued Dependencies, 4NF, 5NF. Issues in physical design; Concepts of indexes, File organization for relational tables, Denormalization. 5. Introduction to Query Processing and Protecting the Database & Data Organizations: Parsing, translation, optimization, evaluation and overview of Query Processing. Protecting the Data Base -Integrity, Security and Recovery. Domain Constraints, Referential Integrity, Assertion, Triggers, Security & Authorization in SQL. Mr. UMESH KUMAr, MCA (GGU) PAGE | 2

Text from page-3

Unit-1 Overview of Database Management: Data The row facts are called as data  The word “row” indicates that they have not been processed Ex- for example 55 is data or ram is data. Information The processed data is knows as information Ex- marks 56 then it becomes information. Difference between Data and Information. Data Information 1. Raw facts Processed data 2. It is in unorganizated from It is in organizated order 3. Data doesn’t help decision making Information help in decision processes process making Codd’s Rules for relational DBMS   E.F codd was a computer scientist who invented the relational model for database management. Codd’s rules actually define what quality a DBMS requires in order to become a relational database management system(RDBMS) Rule zero-: Rules 1: Information rule Rules 2: Guaranteed Access Rules 3: Systematic Treatment of NULL Rules 4: Active Online Catalog Rules 5: powerful and well-Structured Language Rules 6: View updating rule Rules 7: Relational Level Operation Rules 8: physical Data Independence Rules 9: Logical Data Independence Rules 10: Integrity Independence Rules 11: Distribution Independence Rules 12: Non-subversion Rule Database-:  It is a collection of interrelated data of an enterprise in a particular subject. Mr. UMESH KUMAr, MCA (GGU) PAGE | 3

Text from page-4

 It is a collection of data in an organized manner in a persistence media so that storing and retrieving data will be easier.  Size of the database is not fixed & it can vary.  This can be stored in the form of tables.  A database can be generated and maintained manually or by computer.  Database is set of files containing application data.  A database may be generated and manipulated manually or it may be computerized.  This data needs to be inserted, deleted, updated, and extracted for any valid reason.  Example -: Dictionary, Telephone Directory, Library Card Catalog. DBMS-:     It is collection of program (software), which is used to create manipulate (insert, retrieve, delete, update) data in database. It also facilitates the process of defining, constructing, manipulating and sharing database among various users and application. A DBMS is a 4th generation language. Example-: Oracle, SQL, server, MS-Access. Advantages of DBMS-: 1. Data independence.:- DBMS allow dynamic changes at one level of database without affecting other levels. 2. Efficient Data Access. 3. Data Integrity and Security 4. Data administration. 5. Concurrent access and crash recovery 6. Reduced application development time:7. Shard Data-: DBMS allows data sharing between no of users and programs. Disadvantage-:    Cost-: The cost of purchasing or developing the software , cost of upgrading the hardware to store the DBMS and allow it to run and the cost of migration from a separate application environment to an integrate one is very high. Complexity of backup and recovery-: backup & recovery operations are fairly complex in a DBMS environment. Problems with centralization:- As the data is accessible from a single source failure of the central system disrupts the entire system. But that can be avoided by using distributed database Mr. UMESH KUMAr, MCA (GGU) PAGE | 4