Note for Database Management System - DBMS By Manjula Poojary

Topics ( 10 )

Text from page-1

DBMS notes and solved Tricky Question JNTUK Syllabus UNIT-I: An Overview of Database Management, Introduction- What is Database SystemWhat is Database-Why Database- Data Independence- Relation Systems and Others- Summary, Database system architecture, Introduction- The Three Levels of Architecture-The External Level- the Conceptual Level- the Internal Level- Mapping- the Database Administrator-The Database Management Systems- Client/Server Architecture. UNIT-II: The E/R Models, The Relational Model, Relational Calculus, Introduction to Database Design, Database Design and Er Diagrams-Entities Attributes, and Entity Sets-Relationship and Relationship Sets-Conceptual Design With the Er Models, The Relational Model ,Integrity Constraints Over Relations- Key Constraints –Foreign Key Constraints-General Constraints, Relational Algebra and Calculus, Relational Algebra- Selection and Projection- Set Operation, Renaming – Joins- Division- More Examples of Queries, Relational Calculus, Tuple Relational Calculus- Domain Relational Calculus. UNIT-III: Queries, Constraints, Triggers: The Form of Basic SQL Query, Union, Intersect, and Except, Nested Queries, Aggregate Operators, Null Values, Complex Integrity Constraints in SQL, Triggers and Active Database. UNIT-IV: Schema Refinement (Normalization) : Purpose of Normalization or schema refinement, concept of functional dependency, normal forms based on functional dependency(1NF, 2NF and 3 NF), concept of surrogate key, Boyce-codd normal form(BCNF), Lossless join and dependency preserving decomposition, Fourth normal form(4NF). UNIT-V: Transaction Management and Concurrency Control: Transaction, properties of transactions, transaction log, and transaction management with SQL using commit rollback and savepoint. Concurrency control for lost updates, uncommitted data, inconsistent retrievals and the Scheduler. Concurrency control with locking methods : lock granularity, lock types, two phase locking for ensuring serializability, deadlocks, Concurrency control with time stamp ordering : Wait/Die and Wound/Wait Schemes, Database Recovery management : Transaction recovery. UNIT-VI: Overview of Storages and Indexing, Data on External Storage- File Organization and Indexing – Clustered Indexing – Primary and Secondary Indexes, Index Data Structures, Hash-Based Indexing – Tree-Based Indexing, Comparison of File Organization

Text from page-2

UNIT-I: An Overview of Database Management, Introduction- What is Database SystemWhat is Database-Why Database- Data Independence- Relation Systems and Others- Summary, Database system architecture, Introduction- The Three Levels of Architecture-The External Level- the Conceptual Level- the Internal Level- Mapping- the Database Administrator-The Database Management Systems- Client/Server Architecture. Overview of DBMS What is data? Data is a collection of facts that could be recorded and stored. Eg. text,number,images, videos etc Generally it's raw and unprocessed.Data becomes information when it is processed, turning it into something meaningful. What is database?why database? Database: Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retrieval, insertion and deletion of data from it. The various reasons for which we require databases are: ● To manage large chunks of data: Yes, you can store data into a spreadsheet, but if you add large chunks of data into the sheet, it will simply not work. For instance: if your size of data increases into thousands of records, it will simply create a problem of speed. ● Accuracy: When doing data entry files in a spreadsheet, it becomes difficult to manage the accuracy as there are no validations present in it. ● Ease of updating data: With the database, you can flexibly update the data according to your convenience. Moreover, multiple people can also edit data at same time. ● Security of data: There is no denying the fact that your data is less secure in spreadsheets. Anyone can easily get access to file and can make changes to it. With databases you have security groups and privileges you set to restrict access. ● Data integrity: Data integrity also becomes a question when storing data in spreadsheets. In databases, you can be assured of accuracy and consistency of data due to the built in integrity checks and access controls. What is DBMS? What is the need of DBMS?

Text from page-3

Database Management System(DBMS): The software which is used to manage database is called Database Management System (DBMS). For Example, MySQL, Oracle etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks: Data Definition: It helps in creation, modification and removal of definitions that define the organization of data in database. Data Updation: It helps in insertion, modification and deletion of the actual data in the database. Data Retrieval: It helps in retrieval of data from the database which can be used by applications for various purposes. User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control and recovering information corrupted by unexpected failure. What are advantages of DBMS? 1. Reduction of redundancies or duplication of data,reduction of extra processing time to search the required data in large database. 2. Data independence and efficient access- That is generated and stored data should be kept separate from applications that use the data for computing and presentation. 3. Data integrity-It refers to the accuracy and consistency of data stored in a database 4. Reduced application development time-In case of a DBMS, development of a new application using the same database takes very little time 5. Data security-Data security is the protection of the database from unauthorized users. Only the authorized persons are allowed to access the database. 6. Conflict resolution-DBA resolves the conflicts among various users to access the same data file. 7. Data Administration-DBMS facilitates maintenance and administration of data by providing a common base for a large collection of data that is shared by several users. DBA ensures fine tuning of data representation ,periodic backups,ensures proper permissions of data access, monitoring all jobs. 8. Crash recovery-DBMS maintains continuous record(log) of the changes made to the data, so if any failure or system crash , it can restore the database to the previously stored consistent state. 9. Concurrent access-many users can access a single program concurrently(at the same time) as if their program running in isolation What are the disadvantages of DBMS? ● Cost of Hardware and Software of a DBMS is quite high which increases the budget of organization. ● Most database management systems are often complex systems, so the training for users to use the DBMS is required.

Text from page-4

● In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media. ● Use of the same program at a time by many users sometimes lead to the loss of some data. ● DBMS can't perform sophisticated calculations. What is the differences between DBMS and flat file system? DBMS Flat File Management System Multi-user access It does not support multi-user access Design to fulfill the need for small and large businesses It is only limited to smaller DBMS system. Remove redundancy and Integrity Redundancy and Integrity issues Expensive. But in the long term Total Cost of Ownership is cheap It's cheaper Easy to implement complicated transactions No support for complicated transactions What are the various users in DBMS environment? Users Application Programmers Task The Application programmers write programs in various programming languages to interact with databases Database Administrators(DBA) Database Admin is responsible for managing the entire DBMS system. He/She is called Database admin or DBA. End-Users The end users are the people who interact with the database management system. They conduct various operations on database like retrieving, updating, deleting, etc.