ECS DBMS Second Year Past Paper (2024-25) PDF

DATABASE MANAGEMENT SYSTEM SECOND YEAR ECS (Semester – III) OEEC11 (Open Elective) (2024-25) Scheme Course Outcome Syllabus Syllabus Syllabus Books for reference Korth, Slberchatz,Sudarshan, Database System Concepts, 6th Edition, McGraw Hill Elmasri...

DATABASE MANAGEMENT SYSTEM SECOND YEAR ECS (Semester – III) OEEC11 (Open Elective) (2024-25) Scheme Course Outcome Syllabus Syllabus Syllabus Books for reference Korth, Slberchatz,Sudarshan, Database System Concepts, 6th Edition, McGraw Hill Elmasri and Navathe, Fundamentals of Database Systems, 5th Edition, Pearson education Raghu Ramkrishnan and Johannes Gehrke, Database Management Systems, TMH ISE Assessment Topics to be covered 1.1 Characteristics of Database systems 1.2 File System Vs. Database systems  Definitions Data is a known fact that have implicit meaning. Database is a collection of related data represents some aspect of the real world logically coherent collection (not a random collection) designed, built & populated for a specific purpose DBMS is a software system that facilitate user to design and maintain database. Database System - together the database and DBMS software. Database + DBMS = Database System DBMS : Definition A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.  Application of DBMS Sector Use of DBMS Banking For customer information, account activities, payments, deposits, loans, etc. Airlines, Railways For reservations and schedule information. Universities, Colleges For student information, course registrations, colleges and grades. Telecommunications It helps to keep call records, monthly bills, maintaining balances, etc. Finance For storing information about stock, sales, and purchases of financial instruments like stocks and bonds. Sales Use for storing customer, product & sales information. HR For information about employees, salaries, payroll, deduction, generation of paychecks, etc. EXAMPLE OF DATABSE Popular DBMS Software Structured Databases: Unstructured Databases: MySQL No SQL Microsoft Access MongoDB Oracle PostgreSQL SQLite IBM DB2 LibreOffice Base MariaDB Microsoft SQL Server etc. Typical DBMS Functionality DBMS provides Facilities to: Define and specify data types, structures & constraints for the data to be stored in the database Construct or Load the initial database contents on a secondary storage medium. Manipulate the database: Retrieval: Querying, generating reports Modification: Insertions, deletions and updates to its content Accessing the database through Web applications Processing and Sharing by a set of concurrent users and application programs–yet, keeping all data valid and consistent. Typical DBMS Functionality Other features: Protection or Security measures to prevent unauthorized access ”Active” processing to take internal actions on data Presentation and Visualization of data Maintaining the database and associated programs over the life time of the database application „ called database, software, and system maintenance File System File system is basically a way of arranging the files in a storage medium like hard disk. File system organizes the files and helps in retrieval of files when they are required. File systems consists of different files which are grouped into directories. The directories further contain other folders and files. File system performs basic operations like management, file naming, giving access rules etc. Example of File systems are NTFS (New Technology File System) EXT (Extended File System) Typical File system Example - University Record Management Consider part of a university organization that keeps information about all instructors, students, departments, and course offerings. One way to keep the information on a computer is to store it in operating-system files. To allow users to manipulate the information, the system has a number of application programs that manipulate the files, including programs to: Add new students, instructors, and courses. Register students for courses and generate class rosters. Assign grades to students, compute grade point averages (GPA), and generate transcripts Programmers develop these application programs to meet the needs of the university Example - University Record Management New application programs are added to the system as the need arises. For example, suppose that a university decides to create a new major, the university creates a new department and creates new permanent files (or adds information to existing files) to record information about all the instructors in the department, students in that major, course offerings, degree requirements, and so on. The university may have to write new application programs to deal with rules specific to the new major. New application programs may also have to be written to handle new rules in the university. Thus, as time goes by, the system acquires more files and more application programs to extract records from, and add records to, the appropriate files. Example - University Record Management This typical file-processing system is supported by a conventional operating system. The system stores permanent records in various files, and it needs different application programs to extract records from, and add records to, the appropriate files. Keeping organizational information in a file-processing system has a number of major disadvantages: Characteristics of traditional files system The data of certain companies or organizations were kept as “Files”. The files stored in different departments were independent of each other, which caused severe data redundancy. Those files were developed using programming languages like COBOL, C, and C++. Each file includes information for a particular department or region, such as the library, tuition, and students’ exams. The traditional file system is way less flexible than DBMS and has many disadvantages. The maintenance of those files was also of high cost. Each of the units of “Files” used to be known as “Flat Files”. Problems Or Disadvantages of traditional File system OR Purpose of DBMS Data redundancy and data inconsistency Difficulty in accessing data Data isolation Integrity problems Data Dependency Atomicity problems Concurrent-access anomalies Security problems 1. Data Redundancy and Inconsistency Data redundancy exists when the same data are stored at different places. Uncontrolled duplication of data is not desirable for several reasons, such as: 1. Duplication is wasteful. It costs time and money to enter the data more than once. 2. It takes up additional storage space, again with associated costs. Data redundancy may cause data inconsistency. Data inconsistency occurs when different and conflicting versions of the same data appear in different places. 2. Difficulty in accessing the data The conventional file-processing environments do not allow needed data to be retrieved in a convenient and efficient manner. Need to write a new program to carry out each new task. More responsive data-retrieval systems are required for general use. 3. Data Isolation To make a decision, a user might need data from two or more separate files. First, the files were evaluated by analysts and programmers to determine the specific data required from each file and the relationships between the data and then applications could be written in a programming language to process and extract the needed data. Imagine the work involved if data from several files was needed. 4. Integrity Problem The data values stored in the database must satisfy certain types of consistency constraints. Suppose the university maintains an account for each department, and records the balance amount in each account. Suppose also that the university requires that the account balance of a department may never fall below zero. Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when new constraints are added, it is difficult to change the programs to enforce them. The problem is compounded when constraints involve several data items from different files. 5. Data Dependency In file processing system, files and record are described by specific physical formats that are coded into the application program by programmers. If the format of a certain record changes, the code in each file containing that format must be updated. In other words, application programs are data dependent. The change in the physical representation of data requires modification of application programs. 6. Atomicity A computer system, like any other device, is subject to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure. Example account transfer: Consider a program to transfer Rs.500 from the account A to the account B. If a system failure occurs during the execution of the program, it is possible that the $500 was removed from the A but was not credited to the balance account B, resulting in an inconsistent database state. It is difficult to ensure atomicity in a conventional file-processing system. 7. Concurrent Access anomalies When multiple users access the same piece of data at same interval of time then it is called as concurrency of the system. When two or more users read the data simultaneously there is no problem, but when they like to update a file simultaneously, it may result in a problem. Consider department A, with an account balance of Rs. 10,000. If two department clerks debit the account balance (by say Rs. 500 and Rs 100, respectively) of department A at almost exactly the same time, the result of the concurrent executions may leave the budget in an incorrect (or inconsistent) state. If the two programs run concurrently, they may both read the value $10,000, and write back $9500 and $9900, respectively. Depending on which one writes the value last, the account balance of department A may contain either $9500 or $9900, rather than the correct value of $9400. 8. Security Not every user of the database system should be able to access all the data. For example, in a university, payroll personnel need to see only that part of the database that has financial information. They do not need access to information about academic records. But, since application programs are added to the file-processing system in an ad hoc manner, enforcing such security constraints is difficult DBMSs VS File Processing Why do we need a DBMS? Why not just use files to store data? DBMS Vs File System DBMS File System DBMS is a collection of data. In DBMS, the user is File system is a collection of data. In this system, not required to write the procedures. the user has to write the procedures for managing the database. DBMS gives an abstract view of data that hides the File system provides the detail of the data details. representation and storage of data. DBMS provides a crash recovery mechanism, i.e., File system doesn't have a crash mechanism, i.e., if DBMS protects the user from the system failure. the system crashes while entering some data, then the content of the file will b lost. DBMS provides a good protection mechanism. It is very difficult to protect a file under the file system. DBMS contains a wide variety of sophisticated File system can't efficiently store and retrieve the techniques to store and retrieve the data. data. DBMS takes care of Concurrent access of data using In the File system, concurrent access has many some form of locking. problems like redirecting the file while other deleting some information or updating some information. Advantages of DBMS Controlling Redundancy Restricting Unauthorized Access Providing persistent storage for program objects and data structures Providing backup and recovery Providing Multiple user Interface Representing complex relationship among data Enforcing Integrity constraints Characteristics of DBMS 1. Real World Entity Data management systems have been designed keeping in mind the needs of business organizations. Modern DBMS are more realistic and uses real world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use student as entity and their age as their attribute. 2. Self-Describing Nature Before DBMS, traditional file management system was used for storing information and data. There was no concept of definition in traditional file management system like we have in DBMS. A DBMS should be of Self- Describing nature as it not only contains the database itself but also the metadata. A metadata (data about data) defines and describes not only the extent, type, structure and format of all data but also relationship between data. This data represent itself that what actions should be taken on it. Characteristics of DBMS(cont..) 3. Support ACID Properties Any DBMS is able to support ACID (Atomicity, Consistency, Isolation, and Durability) properties. It is made sure in every DBMS that the real purpose of data should not be lost while performing transactions like delete, insert and update. Let us take an example; if an employee name is updated then it should make sure that there is no duplicate data and no mismatch of employee information. Characteristics of DBMS(cont..) 4. Concurrent Use of Database There are many chances that many users will be accessing the data at the same time. They may require altering the database system concurrently. At that time, DBMS supports them to concurrently use database without any problem. With the help of concurrency, economy of the system can be increased. For Example, employees of railway reservation system can book and access tickets for passengers concurrently. Every employee can see on his own interface that how many seats are available or bogie is fully booked. Characteristics of DBMS(cont..) 5. Insulation Between Data and Program Program-data independence provides a big relief to database users. In traditional file management system, structure of data files was defined in the application programs so user had to change all the programs that are using that particular data file. But in DBMS, structure of data files is not stored in the program but it is stored in system catalogue. With the help of this, internal improvement of data efficiency or any changes in the data do not have any effect on application software. Characteristics of DBMS(cont..) 6. Transactions Transactions are set of actions that are done to bring database from one consistent state to new consistent state. Traditional file-based system did not have this feature. Transaction is always atomic that means it can never be further divided. It can only be completed or uncompleted. For example, A person wants to credit money from his account to another person’s account. Then transaction will be complete if he sends money and other guy receives his money. Anything other than this can lead to an inconsistent transaction. Characteristics of DBMS(cont..) 7. Data Persistence Persistence means if the data is not removed explicitly then all the data will be maintained in DBMS. Any data stored in the DBMS can never be lost. If system failure happens in between any transaction then it will be rolled back or fully completed, but data will never be at risk. Characteristics of DBMS(cont..) 8. Backup and Recovery There are many chances of failure of whole database. At that time no one will be able to get the database back and for sure company will be in a big loss. The only solution is to take backup of database and whenever it is needed, it can be stored back. A database must have this characteristic to enable more effectiveness. 9. Data Integrity Integrity ensures the quality and reliability of database system. It protects unauthorized access of database and makes it more secure. It brings only consistence and accurate data into the database. Characteristics of DBMS(cont..) 10. Multiple Views Users can have multiple views of database depending on their department and interest. DBMS support multiple views of database to the users. For example, a user of teaching department will have different view and user of hostel department will have different. This feature helps users to have somewhat security because users of other department cannot access their files. 12. Security DBMS provides security to the data stored in it because all users have different rights to access database. Some of the user can access the whole database while other can access a small part of database. For example, a computer network lecturer can only access files that are related to computer subjects but HOD of the department can access files of all subject that are related to their department. Topics to be covered 1.3 Three schema Architecture and Data Independence 1.4 DBMS Architecture 1.5 Applications of DBMS Three Schema Architecture (IMP) The goal of the three-schema architecture of a DBMS is to separate the user applications and the physical database. In this architecture, schemas can be defined at the following three levels: 1. Internal Level: The internal level has an internal schema which describes the physical storage structure of the database. The internal schema is also known as a physical schema. It uses the physical data model. It is used to define that how the data will be stored in a block. The physical level is used to describe complex low-level data structures in detail. 2. Conceptual Level: Conceptual level has a conceptual schema. The conceptual schema describes the design of a database at the conceptual level. Conceptual level is also known as logical level. The conceptual schema describes the structure of the whole database. Three Schema Architecture (Contd..) The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data. In the conceptual level, internal details such as an implementation of the data structure are hidden. Programmers and database administrators work at this level. 3. External Level: The external or view level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group A high-level data model or an implementation data model can be used at this level. Three Schema Architecture (Contd..)  Views of Data OR Data abstraction (IMP) The main objective of three schema architecture of DBMS is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained. We can define three levels of data abstraction: 1. Physical Level abstraction 2. Logical Level abstraction 3. View Level abstraction Fig: Level of abstraction Views of Data OR Data abstraction (Contd..) Physical Level abstraction: This is the lowest level of abstraction that describes how the data are actually stored. The physical level describes complex low-level data structures in detail. For example, the access methods like sequential or random access and file organization methods like B+ trees, hashing used for the same. Usability, size of memory, and the number of times the records are factors which we need to know while designing the database. Suppose we need to store the details of an employee. Blocks of storage and the amount of memory used for these purposes is kept hidden from the user. Views of Data OR Data abstraction(Contd..) Logical Level of abstraction: The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures. Although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. For example Database designer, who must decide what information to keep in the database, use the logical level of abstraction. Views of Data / Data abstraction (Contd..) View Level abstraction: This is the highest level of abstraction that describes only part of the entire database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. Example: If we have a login-id and password in a university system, then as a student, we can view our marks, attendance, fee structure, etc. But the faculty of the university will have a different view. He will have options like salary, edit marks of a student, enter attendance of the students, etc. The view level of abstraction exists to simplify every user’s interaction with the system. The system may provide many views for the same database. Data Independence (IMP) Data independence can be explained using the three-schema architecture. Data independence refers characteristic of being able to modify the schema at one level of the database system without altering the schema at the next higher level. There are two types of data independence: 1) Logical data independence 2) Physical data independence Data Independence (Contd..) Logical Data Independence: Logical data independence refers to the characteristic of being able to change the conceptual schema without having to change the external schema. Logical data independence is used to separate the external level from the conceptual view. If we do any changes in the conceptual view of the data, then the user view of the data would not be affected. Logical data independence occurs at the user interface level. Data Independence (Contd..) Physical data independence : Physical data independence can be defined as the capacity to change the internal (or physical) schema without having to change the conceptual schema. If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected. Physical data independence is used to separate conceptual levels from the internal levels. Physical data independence occurs at the logical interface level. DBMS system architecture (IMP) Components of DBMS System architecture 1) DBMS Users 2) Query Processor 3) Storage manager 1. DBMS Users Application programmers: Application programmers are computer professionals who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development (RAD) tools are tools that enable an application programmer to construct forms and reports without writing a program. Sophisticated users: Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They submit each such query to a query processor, whose function is to break down DML statements into instructions that the storage manager understands. DBMS Users (Contd..) Naïve users : Naive users are unsophisticated users who interact with the system by invoking one of the application programs that have been written previously. For example, a bank teller who needs to transfer $50 from account A to account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred, the account from which the money is to be transferred, and the account to which the money is to be transferred. DBMS Users (Contd..) Database Administrator: Coordinates all the activities of the database system. The database administrator has a good understanding of the enterprise’s information resources and needs. Database administrator's duties include: Schema definition: The DBA creates the original database schema by executing a set of data definition statements in the DDL. Storage structure and access method definition. Schema and physical organization modification: The DBA carries out changes to the schema and physical organization to reflect the changing needs of the organization, or to alter the physical organization to improve performance. Granting user authority to access the database: By granting different types of authorization, the database administrator can regulate which parts of the database various users can access. Specifying integrity constraints. Monitoring performance and responding to changes in requirements. 2. Query Processor The query processor will accept query from user and solves it by accessing the database. Parts of Query processor: DDL interpreter This will interprets DDL statements and fetch the definitions in the data dictionary. DML compiler This will translates DML statements in a query language into low level instructions that the query evaluation engine understands. A query can usually be translated into any of a number of alternative evaluation plans for same query result DML compiler will select best plan for query optimization. Query evaluation engine This engine will execute low-level instructions generated by the DML compiler on DBMS. 3. Storage Manager A storage manager is a program module which acts like interface between the data stored in a database and the application programs and queries submitted to the system. Thus, the storage manager is responsible for storing, retrieving and updating data in the database. Components of storage Manager are: Authorization and integrity manager: Checks for integrity constraints and authority of users to access data. Transaction manager: Ensures that the database remains in a consistent state although there are system failures.  File manager: Manages the allocation of space on disk storage and the data structures used to represent information stored on disk. Buffer manager: It is responsible for retrieving data from disk storage into main memory. It enables the database to handle data sizes that are much larger than the size of main memory. Data structures implemented by storage manager. Data files: Stored in the database itself. Data dictionary: Stores metadata about the structure of the database. Indices: Provide fast access to data items. Types of DBMS Architecture The architecture of DBMS depends on the computer system on which it runs. For example, in a client-server DBMS architecture, the database systems at server machine can run several requests made by client machine. There are three types of DBMS architecture: 1. Single tier architecture 2. Two tier architecture 3. Three tier architecture Types of DBMS Architecture (Contd..) 1. Single Tier Architecture: In this type of architecture, the database is readily available on the client machine, any request made by client doesn’t require a network connection to perform the action on the database. For example, lets say you want to fetch the records of employee from the database and the database is available on your computer system itself, so the request to fetch employee details will be done by your computer and the records will be fetched from the database by your computer as well. This type of system is generally referred as local database system.  DBMS Architecture (Contd..) 2. Two tier architecture In two-tier architecture, the Database system is present at the server machine and the DBMS application is present at the client machine, these two machines are connected with each other through a reliable network as shown in the above diagram. Whenever client machine makes a request to access the database present at server using a query language like sql, the server perform the request on the database and returns the result back to the client. The application connection interface such as JDBC, ODBC are used for the interaction between server and client. DBMS Architecture (Contd..) 3. Three tier architecture In three-tier architecture, another layer is present between the client machine and server machine. In this architecture, the client application doesn’t communicate directly with the database systems present at the server machine, rather the client application communicates with server application and the server application internally communicates with the database system present at the server. Database Users Database users are categorized based up on their interaction with the database. These are seven types of database users in DBMS. 1. Database Administrator (DBA) : Database Administrator (DBA) is a person/team who defines the schema and also controls the 3 levels of database. The DBA will then create a new account id and password for the user if he/she need to access the database. DBA is also responsible for providing security to the database and he allows only the authorized users to access/modify the data base. DBA is responsible for the problems such as security breaches and poor system response time. 1. DBA also monitors the recovery and backup and provide technical support. 2. The DBA has a DBA account in the DBMS which called a system or superuser account. 3. DBA repairs damage caused due to hardware and/or software failures. 4. DBA is the one having privileges to perform DCL (Data Control Language) operations such as GRANT and REVOKE, to allow/restrict a particular user from accessing the database. 2.Naive / Parametric End Users : Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they frequently use the database applications in their daily life to get the desired results. For examples, Railway’s ticket booking users are naive users. Clerks in any bank is a naive user because they don’t have any DBMS knowledge but they still use the database and perform their given task. 3. System Analyst : System Analyst is a user who analyzes the requirements of parametric end users. They check whether all the requirements of end users are satisfied. 4. Sophisticated Users : Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database. They can develop their own database applications according to their requirement. They don’t write the program code but they interact the database by writing SQL queries directly through the query processor. 5. Database Designers : Data Base Designers are the users who design the structure of a database which includes tables, indexes, views, triggers, stored procedures, and constraints that are usually enforced before the database is created or populated with data. He/she controls what data must be stored and how the data items to be related. It is responsibility of Database Designers to understand the requirements of different user groups and then create a design which satisfies the need of all the user groups. 6. Application Programmers : Application Programmers or simply Software Engineers, are the back-end programmers who write the code for the application programs. They are the computer professionals. These programs could be written in Programming languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc. Application programmers design, debug, test, and maintain 7. Casual Users / Temporary Users : Casual Users are the users who occasionally use/access the database but each time when they access the database they require the new information, for example, Middle or higher level manager. 8. Specialized users : Specialized users are sophisticated users who write specialized database application that does not fit into the traditional data- processing framework. Among these applications are computer aided-design systems, knowledge-base and expert systems etc. DBA – Database Administrator A Database Administrator (DBA) is individual or person responsible for controlling, maintenance, coordinating, and operation of database management system. Managing, securing, and taking care of database system is prime responsibility. They are responsible and in charge for authorizing access to database, coordinating, capacity, planning, installation, and monitoring uses and for acquiring and gathering software and hardware resources as and when needed. Their role also varies from configuration, database design, migration, security, troubleshooting, backup, and data recovery Importance of Database Administrator (DBA) : Database Administrator manages and controls three levels of database like internal level, conceptual level, and external level of Database management system architecture and in discussion with comprehensive user community, gives definition of world view of database. It then provides external view of different users and applications. Database Administrator ensures held responsible to maintain integrity and security of database restricting from unauthorized users. It grants permission to users of database and contains profile of each and every user in database. Database Administrator also held accountable that database is protected and secured and that any chance of data loss keeps at minimum. Role and Duties of Database Administrator (DBA) Decides hardware – They decides economical hardware, based upon cost, performance and efficiency of hardware, and best suits organization. It is hardware which is interface between end users and database. Manages data integrity and security – Data integrity need to be checked and managed accurately as it protects and restricts data from unauthorized use. DBA eyes on relationship within data to maintain data integrity. Database design – DBA is held responsible and accountable for logical, physical design, external model design, and integrity and security control. Database implementation – DBA implements DBMS and checks database loading at time of its implementation. Query processing performance – DBA enhances query processing by improving their speed, performance and accuracy. Tuning Database Performance – If user is not able to get data speedily and accurately then it may loss organization business. So by tuning SQL commands DBA can enhance performance of database. Commonly asked questions 1) Define DBA. Discuss role of DBA. 2) Explain overall architecture of DBMS in detail. 3) Explain types users of the database system with suitable examples and responsibilities of DBA. 4) Differentiate between File system and database system with example. 5) Write a short note on Data Independence. End of Module1