Relational Database Management Systems Lecture PDF

Relational Database Management Systems Ashish Kumar Dept. of CSE Manipal University Jaipur 1  Course Evaluation Component Duration Details Weightage Offline Class Test – 2 10-20...

Relational Database Management Systems Ashish Kumar Dept. of CSE Manipal University Jaipur 1  Course Evaluation Component Duration Details Weightage Offline Class Test – 2 10-20 min TBD (1 before MTE+1 after 8 MTE) Regular Assignment-2: 2 --- --- 4+3 = 7 (1 before MTE+1 after MTE) MOOC Certificates: 2 --- Through Out (OA DFO + Database Semester 10 Design) Attendance Rubrics: 85% - 100% : 5 marks 80% - 84% : 4 marks 5 77% - 79% : 3 marks 75% - 76% : 2 marks < 75% : 0 marks MTE - I 1.5 hour TBD 30 End Semester 3 hours TBD 40 2 Books Text Books: H. F. Korth, S. Sudarshan & A. Silverschatz, Database System Concepts, Tata McGraw Hill, New Delhi Elmasri & Navathe, Fundamentals of Database Systems, Addison & Weisely, New Delhi. Reference Books: Connolly and Begg, Database Systems, LPE, Prentice Hall Publications C. J. Date, Database Systems, Prentice Hall of India, New Delhi. Raghu Ramakrishnan, Database Management Systems (2nd Ed), McGraw Hill. Martin Gruber, Understanding SQL, BPB Publication, New Delhi. 3 Course Faculty Details Ashish Kumar Associate Professor, Dept. of CSE, Manipal University Jaipur Contact No. - +91-8233244583 Email – kumar. [email protected] 4 Course Conduct Maintain 75% attendance. If you are in class then only attendance will be yours. Submit your assignment & quizzes within given time period. It has your 30 marks. 5 What is Data? Data is a set of values of qualitative or quantitative variables; restated, pieces of data are individual pieces of information. Data is measured, collected and reported, and analyzed, whereupon it can be visualized using graphs or images. Data as a general concept refers to the fact that some existing information or knowledge is represented or coded in some form suitable for better usage or processing. Raw data, i.e. unprocessed data, is a collection of numbers, characters; data processing commonly occurs by stages, and the "processed data" from one stage may be considered the "raw data" of the next. 6 What is Data Processing? Any operation or set of operations performed upon data, whether or not by automatic means, such as collection, recording, organization, storage, adaptation or alteration to convert it into useful information. Broadly, the collection and manipulation of items of data to produce meaningful information. 7 Data Processing System A data processing system may involve some combination of: Conversion converting data to another format. Validation – Ensuring that supplied data is "clean, correct and useful." Sorting – "arranging items in some sequence and/or in different sets." Summarization – reducing detail data to its main points. Aggregation – combining multiple pieces of data. Analysis – the "collection, organization, analysis, interpretation and presentation of data.". Reporting – list detail or summary data or computed information. 8 What is Information? A collection of data which conveys some meaningful idea is information. It may provide answers to questions like who, which, when, why, what, and how. OR The raw input is data and it has no significance when it exists in that form. When data is collated or organized into something meaningful, it gains significance. This meaningful organization is information. OR Observations and recordings are done to obtain data, while analysis is done to obtain information 9 Traditional File Based Systems Predecessor to the DBMS. A file system is a hierarchical description of the folders on a drive and information about the files inside them. It handles the movement, creation and deletion of those folders and files. A collection of application programs that perform services for the end-users such as the production of reports. Each program defines and manages its own data. File based systems were developed as better alternatives to paper based filing systems. 10 File System v/s DBMS Advantages Disadvantages FMS Simpler to use Typically no multi-user Less expensive access Limited to smaller databases Limited functionality Decentralization of data Redundancy and integrity issues DB Greater flexibility Difficult to learn MS Greater processing Packaged separately power from the OS Ensures data integrity Requires skilled Supports simultaneous administrators access Expensive Provides backup and 11 What is database? A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization. It is the collection of schemas, tables, queries, reports, views and other objects. In other words, A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. The data is typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Often abbreviated DB. 12 What is DBMS? A software system that enables users to define, create, maintain, and control access to the database. DBMS contains information about a particular enterprise Collection of interrelated data Set of programs to access the data An environment that is both convenient and efficient to use 13 Database Applications Database Applications: Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives 14 Purpose / Benefits of Database Systems In the early days, database applications were built directly on top of file systems Drawbacks of using file systems to store data: Data redundancy and inconsistency  Multiple file formats, duplication of information in different files Difficulty in accessing data  Need to write a new program to carry out each new task Data isolation — multiple files and formats Integrity problems  Integrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly 15 Purpose / Benefits of Database Systems Drawbacks of using file systems (cont.) Atomicity of updates  Failures may leave database in an inconsistent state with partial updates carried out  Example: Transfer of funds from one account to another should either complete or not happen at all Concurrent access by multiple users  Concurrent accessed needed for performance  Uncontrolled concurrent accesses can lead to inconsistencies  Example: Two people reading a balance and updating it at the same time Security problems  Hard to provide user access to some, but not all, data Database systems offer solutions to all the16 Thank You 17