CS4433 Database Systems

CS4433 Database Systems

CS4433 Database Systems Introduction Welcome to CS4433 Course Website: www.cs.okstate.edu\~eakbas\db2020.html Many thing about the course can be found here Syllabus, policies, schedules, slides, projects, resource Make sure you check the course website periodically The class will also have a Canvas page which will be used to post assignments, grades and for sending out announcements. Please read the class syllabus, policies, and lecture schedule;

ask if you have questions No recitations for this class ! Please make good use of office hours of TAs/Instructor 2 Teaching Staff Instructor: Esra Akbas Research interest Generally, data mining, big data, machine learning, and artificial intelligence Specifically, graph mining, information and social network analysis Brief history Bilkent University (Turkey, MS, 2012) Florida State University (Ph.D. 2017)

Oklahoma (Assistant professor at OSU starting from Aug. 2018) TA: Farhan Tanvir Grading (Project, Assignment, Exam) 3 You Tell Me - Why Are You Taking this Course? Database http://www.youtube.com/watch?v=Q2GMtIuaNzU Big data http://www.youtube.com/watch?v=LrNlZ7-SMPk Are you interested more in being An IT guru at Goldman-Sachs or Boeing? A system developer at Oracle or Google?

A data scientist at Facebook or Uber? A DB pro or researcher in Microsoft research or IBM research? A professor exploring the most exciting, and fastest growing area in CS? 4 In Industry 5 In Science Turing Awardees The ACM A.M. Turing Award is an annual prize given by the Association for Computing Machinery (ACM) to an individual selected for contributions "of lasting and major technical importance to the computer field" CHARLES BACHMAN, 1973

- Known for his work in the early development of database management systems. JAMES GRAY, 1998 -For seminal contributions to database and transaction processing research EDGAR CODD, 1981 -Invented Relational model (RM), the theoretical basis for relational databases and relational database management systems. MICHAEL STONEBRAKER, 2014

-Research and products are central to many relational database systems 6 Course Description Study the fundamentals of relational database management systems (RDBMS) Learn the principles of designing and developing applications using relational database systems. Materials include, but are not limited to the ER (Entity-Relationship) model and its mapping to the relational data model the algebraic language in the relational data model and its relationship to the commercial relational query language: SQL

database design that conforms to different normal forms, database applications, database indexing, transaction management, recovery and other 7 CS4433 Goal 1. How to use a database system? Conceptual data modeling, the relational and other data models, database schema design, relational algebra, and the SQL query language 2. How to design and implement a database system? Indexing, transaction processing, and crash recovery

3. How to create an elementary e-commerce a web8 Prerequisite CS 2133, or equivalent Good programming skill Project will require lots of programming Need C++, Java, PHP, or Python to do a good job at talking with DB You or your project group picks the language 9 Textbook Database Systems: The Complete Book. 2nd edition http://infolab.stanford.edu/~ullman/dscb.html References

Database Management Systems Database system concepts Fundamentals of Database Systems An Introduction to Database Systems 10 Course Format Three 50-min lectures/week Lecture slides are used to complement the lectures, not to substitute the textbook! Four assignments planed (20%) Individual work 1 day late submission-%70 More than 1 - 0

A programming project (35%) Teamwork (1-3 students) Multi-stage tasks involving a lot of programming One midterm (15%) and one final (30%) Check dates and make sure no conflict! 11 Project A database-driven Web-based information system Select a real-world application that needs databases as backend systems Design and build it from start to finish Your choice of topic: useful, realistic, databasedriven, Web-based Requirement

Team work (one to three people) all members receive same grading, and if one drops out, the others pick up the work Will be done in stages you will submit some deliverables at the end of each stage 12 How to Get the Most out of CS4433? Read and think before class welcome to ask questions before class! Study and discuss with your peers discuss readings to enhance understanding Use lectures to guide your study use it as a roadmap for whats important

lectures are starting points they do not cover everything you should read Participate actively in your project 13 Data Management Evolution Jim Gray: Evolution of Data Management. IEEE Computer 29(10): 38-46 (1996): Manual processing: -- 1900 Mechanical punched-cards: 1900-1955 Stored-program computer-- sequential record processing: 1955-1970 Online navigational network DBs: 1965-1980 many applications still run today! Relational DB: 1980-1995

Post-relational and the Internet: 199514 Database Management System (DBMS) System for providing EFFICIENT, CONVENIENT, and SAFE MULTI-USER storage of and access to MASSIVE amounts of PERSISTENT data 15 Capabilities of DBMS Persistent Storage Data Structures for efficient access to very large amounts of data

Programming Interface More than reading and writing of files. Access and modify data through a query language Transaction Management- ACID a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc Atomicity : each transaction is treated as a single "unit Consistency: any data written to the database must be valid according to all defined rules, Isolation: controls how and when changes are made and if they become visible to each other, users, and systems Durability: completed transactions will survive permanently

16 Example: Banking System Data Information on accounts, customers, balances, current interest rates, transaction histories, etc. MASSIVE many gigabytes at a minimum for big banks, more if keep history of all transactions, even more if keep images of checks -> Far too big for memory PERSISTENT data outlives programs that operate on it 17

Example: Banking System SAFE: from system failures from malicious users CONVENIENT: simple commands to - debit account, get balance, write statement, transfer funds, etc. also unpredicted queries should be easy EFFICIENT: don't search all files in order to - get balance of one account, get all accounts with low balances, get large transactions, etc. massive data! -> DBMS's carefully tuned for performance 18

Multi-user Access Many people/programs accessing same database, or even same data, simultaneously -> Need careful controls Alex @ ATM1: withdraw $100 from account #007 get balance from database; if balance >= 100 then balance := balance - 100; dispense cash; put new balance into database; Bob @ ATM2: withdraw $50 from account #007 get balance from database; if balance >= 50 then balance := balance - 50; dispense cash; put new balance into database;

Initial balance = 200. Final balance = ?? 19 Can we use file systems like text file? 20 Why File Systems Wont Work Storing data: file system is limited size limit by disk or address space when system crashes we may lose data Password/file-based authorization insufficient Query/update: need to write a new C++/Java program for every new query need to worry about performance

Concurrency: limited protection need to worry about interfering with other users need to offer different views to different users (e.g. registrar, students, professors) Schema change: entails changing file formats need to rewrite virtually all applications Thats why the notion of DBMS was motivated! 21 DBMS Architecture User/Web Forms/Applications/DBA query Query Parser

transaction DDL commands Transaction Manager DDL Processor Concurrency Control Logging & Recovery Query Rewriter Query Optimizer Query Executor

Records Indexes Buffer Manager Storage Manager Storage Buffer: data, indexes, log, etc Lock Tables Main Memory data, metadata, indexes, log, etc CS411

22 Data Structuring: Model, Schema, Data Data model conceptual structuring of data stored in database E.g. arrays, objects in C or Java Data: set of records ex:, for a university system, student table each with student-ID, name, address, courses, photo ex: airplane system: data is graph where nodes represent cities, edges represent airline routes Schema versus data schema: describes how data is to be structured, defined at

set-up time, rarely changes (also called "metadata") A set of attributes with a name for a relation Student(Id, Name, Address, Curse,..) data is actual "instance" of database, rows of relations(tables) changes rapidly Each row has a component(value) for each attribute of relation defined in schema 23 Schema vs. Data Schema: name, name of each field, the type of each field Students (Sid:string, Name:string, Age: integer, GPA: real) A template for describing a student Data: an example instance of the relation

Sid Name Age GPA 0001 Alex 19 3.55 0002

Bob 22 3.10 0003 Chris 20 3.80 0004

David 20 3.95 0005 Eugene 21 3.30 24 Characteristics of Databases

Data - stored in tables - rows and columns Each row in a table stores data about an occurrence or instance of the thing of interest. A database may have multiple tables A database stores data and relationships. 25 Data in Tables ? Any problem? 26 Related Tables

27 Components of a Database System How does application communicate with DBMS? 28 Components of a Database System : SQL 29 SQL Data definition language (DDL) commands for setting up schema of database CREATE, DROP, ALTER Data Manipulation Language (DML)

Commands to manipulate data in database: Select, INSERT, DELETE, MODIFY Also called "query language Does not affect schema 30 Examples Create another column e.g. address DDL Insert another student information DML Sid Name

Age GPA 0001 Alex 19 3.55 0002 Bob

22 3.10 0003 Chris 20 3.80 0004 David 20

3.95 0005 Eugene 21 3.30 31 People of DBMS DBMS user: queries/modifies data DBMS application designer set up schema, loads data,

DBMS administrator user management, performance tuning, DBMS implementer: builds systems 32 What You Need To Learn 33 Questions Any questions? 34

Recently Viewed Presentations

  • Back Safey & Safe Lifting

    Back Safey & Safe Lifting

    Use cranes, hoists, lift tables, and other lift-assist devices whenever you can. Test the weight of an object before lifting by slightly pushing with hand or foot. Get help if it's too heavy for you to lift by yourself. PPT-024-04....


    Economics comes from the Greek word Oikonomia. - Oikos(means a household) + Nomos(means management). So, it means household management. Aristotle. described economics as a household management that means the problems of food, shelter, clothing, education, treatment etc. for family members...
  • HR Forum October 2, 2019 UFHR preeminence through

    HR Forum October 2, 2019 UFHR preeminence through

    Jack Causseaux, 294-3558. Diversity & Inclusion Award Contact - Florida Bridgewater-Alford, 846-3903. UFHR . preeminence through people. Training & Organizational Development. Gator Business Administrator Services (GBAS) Nov 20th. 8:30am - 3:30pm.
  • Thoughts from a different planet Godfrey Rust, FRBR

    Thoughts from a different planet Godfrey Rust, FRBR

    Title: Slide 1 Created Date: 5/3/2005 8:22:41 PM Document presentation format: On-screen Show Other titles: Arial Tahoma Times New Roman Trebuchet MS Microsoft Sans Serif Courier New Wingdings Default Design Slide 1 Slide 2 Slide 3 Slide 4 Slide 5...
  • Marketing: Real People, Real Choices_Ninth Edition

    Marketing: Real People, Real Choices_Ninth Edition

    Width of product mix must be considered. Product lines in mix usually have some things in common. LECTURE NOTES: The product mix is the total set of products a firm offers for sale. Developing a product mix strategy requires consideration...
  • Efficient Valuation for Large Portfolios of VA - cs.toronto.edu

    Efficient Valuation for Large Portfolios of VA - cs.toronto.edu

    AMMCS-CAIMS Talk - Wilfrid Laurier University. Consists of two phases. Accumulation Phase. Withdrawal Phase. Account Value Year 3 Year 6 Year 9 Year 12 Year 15 Year 18 100000 115000 120000 125000 121000 120000 115000 118000 122000 130000 125000 118000...
  • the CATCHER in the RYE

    the CATCHER in the RYE

    He remember luce as the guy that was supposed to be hs student advisor when he was a Whooton but the only thing he ever did was give him and all the boys sex talks. So Holden tries to engage...
  • Farm Management - Department of Agricultural Economics

    Farm Management - Department of Agricultural Economics

    Farm Management Chapter 2 Management and Decision Making Chapter Outline Functions of Management Strategic Farm Management Tactical Decision Making Characteristics of Decisions The Decision-Making Environment in Agriculture Chapter Objectives To understand the functions of management To present the steps in...