database manangement system introduction

dbms intro

1. Database Management Systems(CS23332) II YEAR- III SEMESTER

2. 3

3. SYLLABUS UNIT-I DATABASE SYSTEMS AND SQL QUERY 9 Introduction – Purpose of Database Systems - View of Data –Database Architecture -Database Schema – Keys – Codd’s Rule –RDBMS- SQL: Data Definition – Domain types – Structure of SQL Queries - Modifications of the database –Set Operations–Aggregate Functions– Null Values- SQL Nested Sub q ueries –Complex Queries–Views – Joined relations. UNIT-II PL/SQL,DATAMODEL AND QUERYPROCESSING 9 PL/SQL:Functions,Procedures,Triggers,Cursors–DynamicSQL–RelationalAlgebra-TupleRelationalcalculus- Domain Relational Calculus–Entity Relationship Model–Constraints-Entity Relationship Diagram-Design Issues o f ER Model – Extended ERFeatures –Mapping ER Model to Relational Model– Query Processing –Heuristics for Query Optimization. UNIT-III NORMAL FORMS AND INDEXING 9 Motivation for Normal Forms – Functional dependencies – Armstrong’s Axioms for Functional Dependencies – Closure for a set of Functional Dependencies – Definitions of 1NF-2NF-3NF and BCNF – Multi valued Dependency 4NF - Joint Dependency- 5NF-File Organization-Indexing B+ tree - B-Tree UNIT-IV TRANSACTIONS 9 Transaction Concepts – ACID Properties – Schedules – Serializability – Transaction support in SQL – Need for Concurrency – Concurrency control –Two Phase Locking- Timestamp – Multi version – Validation and Snapshot isolation– Multiple Granularity l ocking –Deadlock Handling–Recovery Concepts –Recovery based on deferred and immediate update – Shadow paging – ARIES Algorithm UNIT-V NOSQL DATABASE 9 No SQL Database vs.SQL Databases – CAP Theorem –Migrating from RDBMS to No SQL – Mongo DB – CRUD Operations– Mongo DB Sharding – MongoDB Replication – Web Application Development using MongoDB with Python and Java.   4

4. Text Book(s): 1. Abraham Silberschatz , Henry F. Korth and S. Sudharshan , “Database System Concepts”, Seventh Edition, Mc Graw Hill, March 2019 . 2.P. J. Sadalage and M. Fowler, " NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence", Addison-Wesley Professional, 2013. Reference Books(s)/ Weblinks : 1.RamezElmasriandShamkantB.Navathe,“FundamentalsofDatabaseSystems”,SeventhEdition,Pearson Education, 2016. 2.C.J.Date,A.KannanandS.Swamynathan,“AnIntroductiontoDatabaseSystems”,EighthEdition,Pearson Education, 2006. 3.AtulKahate,“IntroductiontoDatabaseManagementSystems”,PearsonEducation,NewDelhi,2006. 4.StevenFeuersteinwithBillPribyl,”OraclePL/SQLProgramming”,sixthedition,Publisher:O'Reill2014. 5.MongoDB:TheDefinitiveGuide,3rdEdition,byKristinaChodorow,ShannonBradshaw,Publisher:O'Reilly Media,2019 6.ShashankTiwari,”ProfessionalNoSQL”,Wiley,2011. 7.DavidLane,Hugh.E.Williums,WebDatabaseApplicationswithPHPandMySQL,O’ReillyMedia;2nd edition, 2004 5

5. Unit 1 Introduction to Database Management Systems 6

6. Introduction to Data , Information, Meta Data Data: -Raw facts and figures that can be recorded. Information: Meaningful (processed) data is known as information. Meta Data : Data that describe the properties of other data is Know as Metadata . Actually, Metadata keeps the information of other data Example- 10,971,108 🡪Data 🡪 Chennai Population in 2020 🡪Information 7

7. Traditional File System A file system is a method for storing and organizing computer files and data in a hierarchical structure. It provides a way to store and manage data on hard drives, solid-state drives, and USB drives. It is like a virtual filing cabinet on your computer. The file method provides basic permission for controlling access to files and directories. Folders and sub-folders can be created for better management of files. It makes finding and accessing specific files easy and efficient. Examples: NTFS, FAT32, and ext4. 8

8.   File Processing System(Traditional Approach) In the traditional file-based approach, many organization exclusively used file processing systems to store and manage data. In this file processing system, each unit or department in an organization has its own set of separate files and the records are grouped based on their categories. The data in one file may not relate to the data in any other file.  9

9. File Processing Data Weakness Data redundancy-  Data redundancy occur when the same data items are stored in multiple files due to each unit or department in an organization has its own sperate files in the file processing system. For example, the Student contract file and the Student file store the same students’ names and addresses. Wastage of resources - Duplicating data in this way is a waste of storage space and people’s time. This is because file maintenance tasks require people to spend time in updating or deleting multiple files that contain the same data whenever the data is modified. It also Increases the chance of errors. If a student changes his or her telephone numbers, for example, the school must update the student contract file. If no change is made in all the files where the data stored, then inconsistencies among the files exist. 10

10. File Processing Data Weakness(Contd.) Isolated Data  – Isolated data increase the difficulty in accessing the data that stored in separate files in different departments. For example, to send an email to a teacher regarding the student's academic program progress, data is needed from both the Student name file and Student performance file. Sharing data from multiple and separate files is a complicated procedure especially when there are many files involved. 11

11. Introduction to Database Approach 12

12. Definition: Database, Database system Database: It is a collection of interrelated data. These can be stored in the form of tables. A database can be of any size and varying complexity. A database may be generated and manipulated manually or it may be computerized. 13

13. Definition: Database Management system Database System: It is computerized system, whose overall purpose is to maintain the information and to make that the information is available on demand Database Management System (DBMS): It is a collection of programs that enables user to create and maintain a database. In other words, it is general-purpose software that provides the users with the processes of defining , constructing and manipulating the database for various applications. 14

14. Purpose of Data base System Reduced Data Redundancy   – Most data or records are stored in only one file, which will greatly reduce duplicate data that might lead to confusion and mishandling.    Improved Data Integrity  – When users modify data in the database, they can directly make changes to one file instead of multiple files. Therefore, the mismatch in different copies of same data will not occur. This database approach increases the quality and data’s integrity by reducing the possibility of causing inconsistencies.   Shared Data  –  The data in a database is shared among by multiple users who have access to the system. Organizations that use databases typically have security settings to define who can access, add, modify, and delete the data in a database.   15

15. Purpose of Data Base System Easier Access  – The database approach allows nontechnical users to access, maintain and even develop smaller database without professional assistance.   Reduced Development Time  – It often is easier and faster to develop programs that use the database approach . 16

16. Difference between File System and DBMS 17

17. Views of Data DATA ABSTRACTION Hide irrelevant data Provide an abstract view of the data. Developers hide irrelevant data from the user This improves clarity and efficiency In DBMS, data abstraction is performed in layers which means there are levels of data abstraction   18

18. Views of Data “Schema”  and  “Instance ”  are key ideas in a database management system (DBMS) that help organize and manage data Instances An Instance is the state of an operational database with data at any given time. It contains a snapshot of the database. The instances can be changed by certain CRUD operations, such as like addition, and deletion of data. It may be noted that any search query will not make any kind of changes in the instances. Example: Let’s say a table teacher in our database whose name is School, suppose the table has 50 records so the instance of the database has 50 records for now and tomorrow we are going to add another fifty records so tomorrow the instance has a total of 100 records. This is called an instance 19

19. Views of Data Example: Let’s say a table teacher in our database named school, the teacher table requires the name, dob, and doj in their table so we design a structure as:   Teacher table name: String doj: date dob: date 20

20. Views of Data Schema Schema is the overall description of the database. The basic structure of how the data will be stored in the database is called schema. Schema is of three types: Logical Schema, Physical Schema and view Schema.  Logical Schema –  It describes the database designed at a logical level. Physical Schema –  It describes the database designed at the physical level. View Schema   – It defines the design of the database at the view level. 21

21. Quiz 1. What is a traditional file system? a) A database management system for storing and managing data b) A method of storing and organizing files on storage devices c) A network protocol for file sharing d) A programming language for file manipulation 2.Which of the following is a disadvantage of traditional file systems? a) Easy data sharing b) Lack of data redundancy c) High data security d) Data inconsistency 22

22. Quiz 3.What is a DBMS? a) A hardware system for managing databases b) A software system for managing databases c) A programming language for querying databases d) A protocol for transmitting data 4.Which of the following is not a type of database model? a) Hierarchical b) Network c) Relational d) Functional 23

23. Quiz 5.What is the primary function of a file management system? a) To manage and control file access b) To optimize database queries c) To provide relational data models d) To enforce ACID properties 6.What does the acronym ACID stand for in database transactions? a) Automatic, Consistent, Involuntary, Durable b) Atomicity, Consistency, Isolation, Durability c) Access, Control, Integrity, Dependency d) Attribute, Constraint, Index, Domain 24

24. Quiz 7.What is data abstraction in the context of databases? a) A method of physically storing data b) A process of hiding complex data structures from users c) A type of database schema d) A technique for optimizing queries 8.Which level of data abstraction provides a high-level view of the entire database? a) Physical level b) Logical level c) View level d) Conceptual level 25

25. Quiz 9.Which of the following statements is true about a database schema? a) It changes frequently b) It defines the logical design of the database c) It is a physical storage structure d) It contains the actual data of the database 10.What is the difference between a schema and an instance in a database? a) A schema is a subset of the database, while an instance is the entire database b) A schema is the database structure, while an instance is the data in the database at a given time c) An instance is the database structure, while a schema is the data in the database at a given time d) A schema and an instance are the same things 26

26. 27