Lecture_1_ Introduction to Databases and Database System Applications.pptx.pdf

Database Management Systems Introduction Database Management Systems Course Outline Unit 1: Introduction to Database Management Database System Applications, Purpose, View of data, Database Languages, Database design, Introduction to databases, Database application architecture, Users and...

Database Management Systems Introduction Database Management Systems Course Outline Unit 1: Introduction to Database Management Database System Applications, Purpose, View of data, Database Languages, Database design, Introduction to databases, Database application architecture, Users and Administrators, E-R Model, reducing ER to a relational schema. Structure of relational databases, Database schema, and its constraints, Keys 14 Hours Unit 2: Relational Model and Database Design Relational operations (Algebra), Unary Operations - Unity, Binary, Aggregate Functions, Grouping, SQL ,overview, Data definition, Structure of SQL queries, Additional Basic Operations, Set Operations, Null Values, Aggregate Functions, Nested Subqueries, Database Modification, Join expressions, Views, Triggers, Functions, and Procedures, Introduction to strategies of Query processing and Query optimization. 14 Hours 2 Database Management Systems Course Outline Unit 3: Advanced Design Concepts and Implementation Functional Dependencies, Inference Rules, Closure, Equivalence, Minimal Cover Normal Forms Based on Primary Keys (1NF, 2NF, and 3NF), General Definitions of Second and Third Normal Forms Boyce-Codd Normal Form, Properties of Relational Decompositions, Overview of Higher Normal Forms. 14 Hours Unit 4: Advanced Databases Database transactions, Concurrency control, Locking, Recovery, Database Security, Introduction to NoSQL databases, Document database (MongoDB), Key-Value database (DynamoDB), Graph databases (Neo4j), Wide-column store database (HBase) 14 Hours 3 Database Management Systems Course Outline Lab/Hands-on sessions Draw an ER diagram for a given problem statement Conversion of an ER diagram into Relational schema DDL – create, constraints, alter, rename, drop, truncate table, Views. DML – Insert, Update, Delete, Transactions - commit, rollback, savepoint SQL - Set operators: union, intersect, minus. SQL – Aggregate functions. SQL – Joins: inner, outer; Sub queries: correlated and uncorrelated. SQL – Creating Functions and Procedures SQL – Creating Triggers and Cursors XML- Database access. NoSQL database queries High-level programming language accessing a database using an API. Tools/ Languages: MySQL Workbench, Python, ERwin, Any other tool for ER modeling 4 Database Management Systems Textbooks Textbook 1 (TB1) Textbook 2 (TB2) 5 Database Management Systems Course Evaluation Policy : Tentative DBMS- 5 credits course 6 Database Management Systems Unit 1: Introduction to Database Management Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 7 Database Management Systems Introduction ➔ Have you ever wondered on how a large e-commerce website like Amazon is able to retrieve the products that you would want to purchase? ➔ Nowadays social media (eg: Instagram) one of the most active platforms where people post their day to day activities etc. how and where is all these information stored? By typing a person's name we can get to see their entire profile, posts that person has posted how is this possible? ➔ In a bank, multiple transactions take place everyday. Even if 2 transactions are happening simultaneously from account, still the money in the accounts is properly deducted and credited how does this happen? 8 Database Management Systems Introduction ➔ Suppose you visit your doctor for a general checkup. Now by typing in your name or your ID, all your medical history details would be visible to the doctor. How does this happen? ➔ When you search for a specific topic on Google, how does the search engine quickly retrieve relevant web pages from millions of websites and present them to you? 9 Database Management Systems Introduction All the above questions have a single answer and that is DATABASE Before going ahead and understanding what database exactly and how is designed and used for all the other application, let us try answering a few more questions: What do you mean by Data? How is it different from Information? In current world, there is a lot of data that is available, how are all these stored or organised so that we can get the required information? 10 Database Management Systems What Do you mean by Data & Information? Data Data represents the raw material, like individual puzzle pieces, that serves as the foundation for knowledge. Data is a raw and unorganized fact that is required to be processed to make it meaningful. Information When data is processed, organized, structured, or presented in a given context to make it useful is called information. Data is a collection of facts, while information puts those facts into context It is organized and is utilized by humans in some significant way to make decisions and draw some conclusions Data doesn’t depend on information, Information depends on data 11 Database Management Systems What is a Database? Database A database is a collection of related data representing some aspect of the real world, also called the mini-world or the universe of discourse (UoD). It is a logically coherent collection, meaning it is not just a random assortment of data but is organized with inherent meaning and structure. Databases are designed, built, and populated for a specific purpose, catering to the needs of applications or systems that interact with them. They can vary in size and complexity, ranging from small databases used by individual applications to large-scale enterprise databases handling vast amounts of data. Could you think of what do these individual terms mean w.r.t student information Database? 12 Database Management Systems What is a Database 13 Database Management Systems Database Management System Database Management System A modern database system is a complex software system whose task is to manage a large, complex collection of data. 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 Database systems are used to manage collections of data that are: ○ Highly valuable ○ Relatively large ○ Accessed by multiple users and applications, often at the same time. Let us break these statements down further and understand them in the context of the student information database 14 Database Management Systems Student Information Database 15 Database Management Systems Database Management System Database Management System A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications ○ Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database. By defining the structure and data types, the DBMS ensures the consistency and integrity of the stored data. The "Students" table may include fields such as "Student ID" (numeric), "Name" (text), "Email" (text), "Date of Birth" (date), and "Major" (text). The "Courses" table may have fields like "Course ID" (numeric), "Course Name" (text), "Credits" (numeric), and "Instructor" (text). 16 Database Management Systems Database Management System Database Management System A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications ○ Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS. It organizes the data in a way that allows for efficient retrieval and manipulation. The DBMS ensures data is securely stored and can be accessed by authorized users or applications. The DBMS may store the Student Information Database on a server with appropriate data storage capacity and performance capabilities. 17 Database Management Systems Database Management System Database Management System A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications ○ Manipulating a database includes functions such as querying the database to retrieve specific data, updating the database to reflect changes in the mini world, and generating reports from the data. It ensures that any changes made to the database follow predefined constraints and maintain data consistency. A university administrator may use SQL queries to retrieve specific information, such as "Retrieve all students enrolled in Computer Science courses." The DBMS processes the query, retrieves the relevant data from the database, and presents it to the user. Allows authorized users to update the database, such as adding new students or updating grades. 18 Database Management Systems Database Management System Database Management System A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications ○ Sharing a database allows multiple users and programs to access the database simultaneously, provided they have the necessary permissions Faculty members, administrative staff, and students can access relevant information concurrently, as long as they have the necessary permissions. For example, faculty members can access student details for their courses, while administrators can update and maintain the overall database. 19 Database Management Systems Questions Some questions to revise 1) How do you define data, and how does it differ from information in the context of database management systems? 2) List out the data types you would take to define a database for an online bookstore. 3) Explain the role of a DBMS in managing highly valuable and relatively large collections of data. Why is this important for organizations? 4) What are some of the common operations performed on a database? How do these operations interact with the data stored in the database? 20 Database Management Systems How are databases used? Some online resources - https://dev.mysql.com/doc/ https://www.oracle.com/in/database/what-is-database/ https://docs.oracle.com/cd/E17952_01/index.html https://downloads.mysql.com/docs/mysql-tutorial-excerpt-8.0-en.a4.pdf 21 THANK YOU S Nagasundari Department of Computer Science and Engineering [email protected] 22