Data Base Management Systems (DBMS) 10CS54 DATABASE MANAGEMENT SYSTEMS Subject Code: 10CS54 I.A. Marks : 25 Hours/Week : 04 Exam Hours: 03 Total Hours : 52 Exam Marks: 100 PART - A UNIT – 1 6 Hours Introduction: Introduction; An example; Characteristics of Database approach; Actors on the screen; Workers behind the scene; Advantages of using DBMS approach; A brief history of database applications; when not to use a DBMS. Data models, schemas and instances; Three- schema architecture and data independence; Database languages and interfaces; The database system environment; Centralized and client-server architectures; Classification of Database Management systems. UNIT – 2 6 Hours Entity-Relationship Model: Using High-Level Conceptual Data Models for Database Design; An Example Database Application; Entity Types, Entity Sets, Attributes and Keys; Relationship types, Relationship Sets, Roles and Structural Constraints; Weak Entity Types; Refining the ER Design; ER Diagrams, Naming Conventions and Design Issues; Relationship types of degreehigher than two. UNIT – 3 8 Hours Relational Model and Relational Algebra : Relational Model Concepts; Relational Model Constraints and Relational Database Schemas; Update Operations, Transactions and dealing with constraint violations; Unary Relational Operations: SELECT and PROJECT; Relational Algebra Operations from Set Theory; Binary Relational Operations : JOIN and DIVISION; Additional Relational Operations; Examples of Queries in Relational Algebra; Relational Database Design Using ER- to-Relational Mapping. UNIT – 4 6 Hours SQL – 1: SQL Data Definition and Data Types; Specifying basic constraints in SQL; Schema change statements in SQL; Basic queries in SQL; More complex SQL Queries. PART - B UNIT – 5 6 Hours SQL – 2 : Insert, Delete and Update statements in SQL; Specifying constraints as Assertion and Trigger; Views (Virtual Tables) in SQL; Additional features of SQL; Database programming issues and techniques; Embedded SQL, Dynamic SQL; Database stored procedures and SQL / PSM. UNIT – 6 6 Hours Database Design – 1: Informal Design Guidelines for Relation Schemas; Functional ependencies; Normal Forms Based on Primary Keys; General Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form Dept of CSE,GCEM Page 1 Data Base Management Systems (DBMS) 10CS54 UNIT – 7 6 Hours Database Design -2: Properties of Relational Decompositions; Algorithmsfor Relational Database Schema Design; Multivalued Dependencies andFourth Normal Form; Join Dependencies and Fifth Normal Form; Inclusion Dependencies; Other Dependencies and Normal Forms UNIT – 8 8 Hours Transaction Management: The ACID Properties; Transactions and Schedules; Concurrent Execution of Transactions; Lock- Based Concurrency Control; Performance of locking; Transaction support in SQL; Introduction to crash recovery; 2PL, Serializability and Recoverability; Lock Management; Introduction to ARIES; The log; Other recovery- relatedstructures; The write-ahead log protocol; Checkpointing; Recovering from aSystem Crash; Media Recovery; Other approaches and interaction withconcurrency control. Text Books: 1. Elmasri and Navathe: Fundamentals of Database Systems,5th Edition, Pearson Education, 2007.(Chapters 1, 2, 3 except 3.8, 5, 6.1 to 6.5, 7.1, 8, 9.1, 9.2 except SQLJ, 9.4, 10) 2. Raghu Ramakrishnan and Johannes Gehrke: Database Management Systems, 3rd Edition, McGraw-Hill, 2003. (Chapters 16, 17.1, 17.2, 18) Reference Books: 1. Silberschatz, Korth and Sudharshan: Data base System Concepts, 6th Edition, Mc-GrawHill, 2010. 2. C.J. Date, A. Kannan, S. Swamynatham: An Introduction to Database Systems, 8th Edition, Pearson Education, 2006 Dept of CSE,GCEM Page 2 Data Base Management Systems (DBMS) 10CS54 INDEX SHEET Contents Page No Unit 1 Databases and Database Users 2-19 1.1 Introduction 1.2: An Example: 1.3: Characteristics of the Database Approach: 1.4: Actors on the Scene 1.5: Workers Behind the Scene 1.6: Capabilities/Advantages of DBMS's 1.7: A Brief History of Database Applications 1.8: When Not to Use a DBMS Unit 2 ENTITY.1 Data Models, Schemas, and Instances 20-35 2.1-RELATIONSHIP MODEL 2.1.2: Schemas, Instances, and Database State 2.2 DBMS Architecture and Data Independence 2.2.1: Three-Schema Architecture 2.3 Database Languages and Interfaces 2.3.1 DBMS Languages 2.3.2 DBMS Interfaces 2.4 Database System Environment 2.5 Centralized and Client/Server Architectures for DBMS's 2.6 Classification of DBMS's 2.7.Modeling Using the Entity-Relationship Model Dept of CSE,GCEM Page 3 Data Base Management Systems (DBMS) 10CS54 2.8 Entity-Relationship (ER) Model 2.8.1 Entities and Attributes 2.8.2 : Entity Types, Entity Sets, Keys, and Domains 2.8.3 Initial Conceptual Design of COMPANY database 2.9 Relationship Types, Sets, Roles, and Structural Constraints 2.9.1:Ordering of entity types in relationship types 2.9.2 Degree of a relationship type 2.9.3 Constraints on Relationship Types 2.9.4 Attributes of Relationship Types 2.10 Weak Entity Types UNIT 3 The Relational Data Model and Relational Database Constraints 36-57 and Relational Algebra 3.1 Relational Model Concepts 3.1.2 Characteristics of Relations 3.1.3 Relational Model Notation 3.2 Relational Model Constraints and Relational Database Schemas 3.2.1 Domain Constraints 3.2.2 Key Constraints 3.2.3 Relational Databases and Relational Database Schemas 3.2.4 Entity Integrity, Referential Integrity, and Foreign Keys 3.3 Update Operations and Dealing with Constraint Violations 3.3.1 Insert 3.3.2 Delete 3.3.3 Update: 3.3.4 Transactions and dealing with constraints Dept of CSE,GCEM Page 4 Data Base Management Systems (DBMS) 10CS54 3.4 Relational Operation 3.5 Relational algebra operation Set theory Operations 3.6 JOIN Operations 3.7 Additional Relational Operations 3.8 Examples of Queries in Relational Algebra 3.9 Relational Database Design Using ER-to-Relational Mapping UNIT 4 SQL The Relational Database Standard 58-69 4.1 Data Definition, Constraints, and Schema Changes in SQL2 4.2 Basic Queries in SQL 4.3 More Complex SQL Queries UNIT 5 SQL The Relational Database Standard 70-80 5.1 Update Statements in SQL 5.2 Views in SQL 5.3 Additional features 5.4 Database Programming 5.5 Embedded SQL 5.6 Dynamic SQL 5.7 Database stored procedures and SQL/PSM UNIT-6 Data Base design-1 81-94 6.1 Informal design guidelines for relation schemas 6.1.1 Semantics of relations attributes 6.2. Inference Rules 6.3 Normalization 6.3.1 First Normal Form (1NF) 6.3.2 Second Normal Form (2NF) Dept of CSE,GCEM Page 5 Data Base Management Systems (DBMS) 10CS54 6.3.3 Third Normal Form (3NF 6.4 Boyce-Codd Normal Form (BCNF) UNIT-7 Data base design 2 95-102 7.1 Properties of relational decomposition 7.2 Algorithms for Relational Database Schema Design 7.2.1 Decomposition and Dependency Preservation 7.2.2 Lossless-join Dependency 7.3 Multivolume Dependencies and Fourth Normal Form (4NF) 7.3.1 Fourth Normal Form (4NF) 7.4 Join Dependencies and 5 NF 7.5 Other dependencies: 7.5.1 Template Dependencies 7.5.2 Domain Key Normal Form UNIT 8 Transaction Processing Concepts 103-110 8.1 Introduction to Transaction Processing 8.2 Transactions, Read and Write Operations 8.3 Why Concurrency Control Is Needed 8.4 Why Recovery Is Needed 8.5 Transaction and System Concepts 8.6 The System Log 8.7 Desirable Properties of Transactions 8.8 Schedules and Recoverability 8.10 Characterizing Schedules Based on Recoverability Dept of CSE,GCEM Page 6 Data Base Management Systems (DBMS) 10CS54 Dept of CSE,GCEM Page 7 Data Base Management Systems (DBMS) 10CS54 UNIT 1 Databases and Database Users Subject Code : 10CS54 IA Marks : 25 No. of Lecture Hours/Week : 04 Exam Hours : 03 Total No. of Lecture Hours : 52 Exam Marks : 100 Databases and Database Users 1.1 Introduction 1.2: An Example: 1.3: Characteristics of the Database Approach: 1.4: Actors on the Scene 1.5: Workers Behind the Scene 1.6: Capabilities/Advantages of DBMS's 1.7: A Brief History of Database Applications 1.8: When Not to Use a DBMS Dept of CSE,GCEM Page 8 Data Base Management Systems (DBMS) 10CS54 Unit 1 Databases and Database Users 1.1 Introduction Importance: Database systems have become an essential component of life in modern society, in that many frequently occurring events trigger the accessing of at least one database: bibliographic library searches, bank transactions, hotel/airline reservations, grocery store purchases, online (Web) purchases, etc., etc. Traditional vs. more recent applications of databases: The applications mentioned above are all "traditional" ones for which the use of rigidly- structured textual and numeric data suffices. Recent advances have led to the application of database technology to a wider class of data. Examples include multimedia databases (involving pictures, video clips, and sound messages) and geographic databases (involving maps, satellite images). Also, database search techniques are applied by some WWW search engines. Definitions The term database is often used, rather loosely, to refer to just about any collection of related data. E&N say that, in addition to being a collection of related data, a database must have the following properties:  It represents some aspect of the real (or an imagined) world, called the miniworld or universe of discourse. Changes to the miniworld are reflected in the database. Imagine, for example, a UNIVERSITY miniworld concerned with students, courses, course sections, grades, and course prerequisites.  It is a logically coherent collection of data, to which some meaning can be attached. (Logical coherency requires, in part, that the database not be self-contradictory.)  It has a purpose: there is an intended group of users and some preconceived applications that the users are interested in employing. To summarize: a database has some source (i.e., the miniworld) from which data are derived, some degree of interaction with events in the represented miniworld (at least insofar as the data is updated when the state of the miniworld changes), and an audience that is interested in using it. An Aside: data vs. information vs. knowledge: Data is the representation of "facts" or "observations" whereas information refers to the meaning thereof (according to some interpretation). Knowledge, on the other hand, refers to the ability to use information to achieve intended ends. Dept of CSE,GCEM Page 9 Data Base Management Systems (DBMS) 10CS54 Computerized vs. manual: Not surprisingly (this being a CS course), our concern will be with computerized database systems, as opposed to manual ones, such as the card catalog-based systems that were used in libraries in ancient times (i.e., before the year 2000). (Some authors wouldn't even recognize a non-computerized collection of data as a database, but E&N do.) Size/Complexity: Databases run the range from being small/simple (e.g., one person's recipe database) to being huge/complex (e.g., Amazon's database that keeps track of all its products, customers, and suppliers). Definition: A database management system (DBMS) is a collection of programs enabling users to create and maintain a database. More specifically, a DBMS is a general purpose software system facilitating each of the following (with respect to a database):  definition: specifying data types (and other constraints to which the data must conform) and data organization  construction: the process of storing the data on some medium (e.g., magnetic disk) that is controlled by the DBMS  manipulation: querying, updating, report generation  sharing: allowing multiple users and programs to access the database "simultaneously"  system protection: preventing database from becoming corrupted when hardware or software failures occur  security protection: preventing unauthorized or malicious access to database. Given all its responsibilities, it is not surprising that a typical DBMS is a complex piece of software. A database together with the DBMS software is referred to as a database system. (See Figure 1.1, page 7.) 1.2 : An Example: UNIVERSITY database in Figure 1.2. Notice that it is relational! Among the main ideas illustrated in this example is that each file/relation/table has a set of named fields/attributes/columns, each of which is specified to be of some data type. (In addition to a data type, we might put further restrictions upon a field, e.g., GRADE_REPORT must have a value from the set {'A', 'B', ..., 'F'}.) The idea is that, of course, each table will be populated with data in the form of records/tuples/rows, each of which represents some entity (in the miniworld) or some relationship between entities. Dept of ISE, SJBIT Page 10