Database Management Systems CS 3265/5265 Vanderbilt University PDF

Database Management Systems CS 3265/5265 Dr. Vikash Singh Introductions About Myself Vikash Singh, Ph.D. Education B Arch BIT Mesra, India, 2002 MS Arch, Mississippi State Univ, 2007 Ph. D., UNC Charlotte, 2015 Professional Experience Web design and development for 3 years Post-Doc...

Database Management Systems CS 3265/5265 Dr. Vikash Singh Introductions About Myself Vikash Singh, Ph.D. Education B Arch BIT Mesra, India, 2002 MS Arch, Mississippi State Univ, 2007 Ph. D., UNC Charlotte, 2015 Professional Experience Web design and development for 3 years Post-Doc at UNC Charlotte for 2 years Vanderbilt University (2017 onwards) Associate Professor of the Practice of Computer Science 3 Research and Teaching My Research Interests My research interests include Human-Computer Interaction, collaboration tools and computer science pedagogy. My research has mainly focused on team-based and video-centered active learning. Other courses I have taught in the recent past CS 4278/5278 – Principles of Software Engineering CS 3265/5265 – Database Management Systems CS 4287 – Principles of Cloud Computing CS 2201 – Data Structure CS 1101 and CS 1151 4 Course information TA's, their email addresses, and office hours will be posted on Brightspace Announcements in the next few days. TAs will hold office hours in-person/virtually on Zoom starting sometime this week. I’ll keep you posted. Please let me know if a TA does not show up for their scheduled office hours. Course Details Why this class? Knowingly or unknowingly we all are already using a variety of database services: e.g., Banking transactions, Facebook, Twitter, YouTube, Gmail, Google Docs, Search, Maps, DropBox, Box, Games, Netflix, MOOC platforms, Snapchat, Instagram, and many more. Virtually every app is backed by such services. All modern sciences such as Genomics, astronomy, medicine, meteorology etc. generate massive volumes of data We need to make sense of such data. Data growth will continue to outpace computation and the key bottleneck in the future: data processing Systems for data at scale: the core of modern computing What is this class about? This class is about databases. - A database is collection of related data. But often times we do not know how the data is stored/extracted/displayed on demand What is happening behind the scenes? How data is stored for millions of users? What are the technologies involved? Who are the stakeholders? What are the big challenges moving ahead? What is this class about? We also want to manage these databases in secure and efficient way which stores persistent data. Why persistent? Data lives on, beyond programs that operate on it, even on system shutdown and power failure. Can’t store data in memory we have to rely on stable storage (disk, flash). Universal symbol of databases. What is this class about? This course is about the understanding how the data storage works We will drill down inside data management systems Provides an “insider’s view” i.e., how this should be done for better performance Yet programming assignments and class project will provide the “user view” i.e., hands-on experience on interacting with the system. Focus will be mainly on Relational database systems. We will use MySQL for this course. Many other Non-relational database systems exist: Graphs, Document stores, Key value stores etc. What is this class about? Topics Relational Model Overview, Relational Algebra SQL Queries – Foundational and advanced Data Manipulation Referential Integrity Conceptual, Logical and Physical Database Design ER Diagrams and UML representation of Databases What is this class about? Topics Functional dependencies and Normalization Indexing and Views Concurrency, Transaction Secure SQL Programming Stored Procedures, Triggers Database Administration Selected Topics depending on schedule (NoSQL, OLAP etc.) Course Logistics Textbooks Required ZyBooks 1.Sign in or create an account at learn.zybooks.com 2.Enter zyBook code: VANDERBILTCS32655265SinghFall2024 3.Subscribe Textbooks Recommended/Optional Murach’s MySQL 3rd Edition "MySQL" Not the similarly titled "PHP and MySQL". How will this class work? Two lectures per week. Supplemental reading material on ZyBooks and Web Generally Two ICA per week – to be completed during class. Coding exercise/ Top Hat/ ZyBooks Each Homework covers a few lectures – Generally every week/other week. Exams and Projects to assess the understanding of materials. Grading Criteria Percentage of total Component grade In-class activity + Attendance 20% Homework 15% Projects 20% Exams – 2 midterm (10 each) + Final (15) 35% ZyBooks 10% Attendance Policy Attendance in class is mandatory. You will be counted absent if you walk in the class after the first 3-4 minutes of class and will not get credit for class work. Missing class work will not be made up because of interviews or travel without prior approval. Attendance is also directly related to your in class activities. Two lowest scores on ICAs will be dropped. If you are absent, you will get a grade of zero for that ICA. Why SQL? Why MySQL? Response Both are industry standard You gain competency in one flavor of SQL with MySQL Additional rationale: MySQL is free => no worries about fees Learn at our own pace; ok to make mistakes Vanderbilt Honor System All academic work done is governed by the Vanderbilt Honor Code System. Read and understand the course's Academic Honesty Policy from the syllabus. Do not share and copy work in any form with any student or website (past, present, future). Minimum penalty is an “F” in the course. The Honor council will prosecute any reported incidences. Use of AI tools is restricted. For any individual work, the work must be your own, i.e., No copying, No collaboration, etc When working in group, ensure that you work as a team and work with dedication; I should not hear complaints that only one person is putting all the effort Your final reports should discuss how you split the work Hardware/Software See Syllabus for laptop configuration requirements Mac/Windows Mac chip – Intel/Apple Have enough space in your laptop throughout the semester 15-20 GB Install DB related software – Wait for instructions WAMP/MAMP MySQLWorkbench What is a database? Collection of related data used for a specific purpose Examples of databases Accounts database Payroll database Students database Amazon products database Airline reservation database What is a DBMS? A software package to implement and maintain databases In other words, a program that allows us to efficiently manage a large database and allows it to persist over long periods of time. Examples DB2 (IBM) SQL Server ( Microsoft) Oracle MySQL PostgreSQL Client/Server System Relational Data model A database is an organized collection of structured information, or data, typically stored electronically in a computer system. Database is collection of relations (tables). Relational Data model A relation is a set of tuples. Each tuple can have many attributes. Relation is a table with rows and columns In SQL we refer relation as table. Tuples à Rows Attributes à Columns Relation Example: A relation of auto parts supplier schema Supplier (Supplier-number:integer, Supplier-name:string) instance Supplier-number Supplier-name 1 ABC ltd 2 XYZ corp Hans Rosling Exercise http://www.youtube.com/watch?v=jbkSRLYSojo Hans Rosling Exercise 1. Dr. Rosling concludes that the analysis involved plotting “120,000 numbers.” Explain where the 120,000 count came from. 2. List the attributes that you believe must be stored in a database that supports this analysis (and perhaps similar analyses). 3. Give candidate (tentative) relational schema (at least two) for a database that would support this and similar analyses. (Relational schema can have multiple tables (schema).