CSC 440 Database Management Systems Section 1 Acknowledgment

CSC 440 Database Management Systems Section 1 Acknowledgment: Slides borrowed from Dr. Rada Chirkova. This presentation uses slides and lecture notes available from http: //www-db. stanford. edu/~ullman/dscb. html#slides Spring 2007 CSC 440: Database Management Systems

Scope of this Course n n n Directed at computer science undergraduate students Introduces concepts and theory Requires design and development of a database application Implementation-specific details are not the focus of the course — you learn those on your own Intensive Spring 2007 CSC 440: Database Management Systems 2

Database System Environment Users / Programmers DATABASE Application Programs / Queries SYSTEM DBMS SOFTWARE Processing Queries / Programs Accessing Stored Database Definition (Meta -Data) Spring 2007 CSC 440: Database Management Systems Stored Database 3

Contents n n n n Introduction Database programming basics Data modeling: entity-relationship approach Relational data model Relational algebra SQL Constraints and triggers Transactions, security, and authorization in SQL Spring 2007 CSC 440: Database Management Systems 4

Contents (cont’d) n n n Data-storage and index structures Recovery Concurrency control Query execution Distributed databases Spring 2007 CSC 440: Database Management Systems 5

Prerequisites n n n CSC 316 (Data structures for computer scientists) Knowledge of discrete mathematics and predicate logic Sufficient ability to program in Java or a willingness to acquire it through self-study Spring 2007 CSC 440: Database Management Systems 6

Course Textbook n n Database Systems: The Complete Book, by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, Prentice Hall. Bundled with Gradiance software for homeworks Spring 2007 CSC 440: Database Management Systems 7

Instructor n n n Peng Ning, associate professor of Computer Science Email policy pning@ncsu. edu Phone (919) 513 -4457 Office: EBII-3258, Centennial Campus Office hours: Tuesdays and Thursdays 9: 4510: 45 am, or by appointment Spring 2007 CSC 440: Database Management Systems 8

Teaching Assistant § Ms. Yao Liu § Office hours: Tuesdays and Thursdays, 4 pm - 5 pm § Office: EBII-3323 § Email: yliu 20 AT ncsu. edu Spring 2007 CSC 440: Database Management Systems 9

Now Tell Us about Yourself § Interview your neighbor and have him/her interview you § Introduce your neighbor to the class Spring 2007 CSC 440: Database Management Systems 10

Course Website http: //courses. ncsu. edu/csc 440/lec/001/ n Tour of web sites n Important: course announcements: u on the web site n Learning objectives and lecture slides on the web n Spring 2007 CSC 440: Database Management Systems 11

Assignments n n Reading assignments: see course web page u Chapter 1 and Section 8. 5 now Eight homework assignments u All work is to be done individually unless otherwise specified. u For the collaborative problems, you may form teams of 2 -3 members (of students in this class) to cooperate only on those problems. After discussing the problems, please write up your answers individually. Indicate the names of the other members in your team, if any. Spring 2007 CSC 440: Database Management Systems 12

Project n n n All students are required to complete a course project The details will be announced on the course website You need to start forming project teams u Talk to your classmates u Use the message board Spring 2007 CSC 440: Database Management Systems 13

Grading Quizzes n Assignments n Project (demo day April 23) n Midterm (February 28 in class) n Final exam (April 29, 8 -11 am) n Spring 2007 CSC 440: Database Management Systems 5% 10% 22% 30% 33% 14

Why the Homeworks Are Important Spring 2007 CSC 440: Database Management Systems 15

Self-Study Responsibilities n n n Some of the topics are important but are either quite straightforward or not a main focus of this course. These topics will be identified as self-study topics on the course web page. Your knowledge of them will be evaluated as appropriate through exams, homework, programming assignments, or the project. Spring 2007 CSC 440: Database Management Systems 16

Miscellaneous n n Rules: The NC State University and Department of Computer Science rules regarding academic honesty apply Regrade policy: see syllabus Spring 2007 CSC 440: Database Management Systems 17

Discussion Discuss the syllabus in pairs n Ask me questions (later if you prefer) n Spring 2007 CSC 440: Database Management Systems 18

Setting Goals and Expectations n n Write your goals for the semester (2 -3 phrases) Write your expectations of me as a teacher (2 -3 phrases) Anonymously: hand in rumors, if any, you have heard about the course or about me. We will discuss the rumors next time. You may also ask me questions. Spring 2007 CSC 440: Database Management Systems 19

Topic #1: A Brief Introduction to DBMS: The Big Picture Spring 2007 CSC 440: Database Management Systems 20

Group Discussion n n What do you know already about the topic? u We’ll see next week! What questions do you have about the course content? Spring 2007 CSC 440: Database Management Systems 21

An Example of a Database STUDENT: Name Student. Number Class Major Smith Brown Green White 17 8 24 37 GRADE_RPRT: Student. No 1 2 2 1 CSC ECE CSC Grade 17 17 24 CSC 742 CSC 316 CSC 742 AB C- 8 Spring 2007 Course CSC 742 B+ CSC 440: Database Management Systems 22

Database: What It Is n Coherent collection of data with inherent meaning u n About an aspect of the world u n Random assortment of data is not a database Changes in the world are reflected in the database Fit to use for its intended purpose u Somebody is going to use the database Spring 2007 CSC 440: Database Management Systems 23

Group Discussion n Come up with 2 -3 examples of databases Spring 2007 CSC 440: Database Management Systems 24

Database System Environment Users / Programmers DATABASE Application Programs / Queries SYSTEM DBMS SOFTWARE Processing Queries / Programs Accessing Stored Database Definition (Meta -Data) Spring 2007 CSC 440: Database Management Systems Stored Database 25

Database Management System n n Specialized software Buy, install, set up for particular application Available for PC’s, workstations, mainframes, supercomputers Is expected to: Spring 2007 CSC 440: Database Management Systems 26

Database Management System n n Specialized software Buy, install, set up for particular application Available for PC’s, workstations, mainframes, supercomputers Is expected to: u u Allow users to create new databases (schema) Give users the ability to query/modify the data Support the storage of very large amounts of data Control access to data from many users at once Spring 2007 CSC 440: Database Management Systems 27

Database Management System (cont’d) n n Major vendors/products: u Oracle u IBM (DB 2) u Microsoft (SQL Server, Access) Powerful tool for providing efficient, convenient, and safe multi-user storage of and access to massive amounts of persistent data Spring 2007 CSC 440: Database Management Systems 28

Example: Banking System § Data = information on accounts, customers, balances, current interest rates, transaction histories, etc § Massive § Persistent § Multi-user Spring 2007 CSC 440: Database Management Systems 29

Example (2 of 4) n Jane at ATM 1: withdraw $100 from account #55 u Get balance from database u If balance > 100 then t balance : = balance – 100 t dispense cash t put new balance into database Spring 2007 CSC 440: Database Management Systems 30

Example (3 of 4) n n n John at ATM 2: withdraw $50 from account #55 u Get balance from database u If balance > 50 then t balance : = balance – 50 t dispense cash t put new balance into database Initial balance = 100 Final balance = ? ? Spring 2007 CSC 440: Database Management Systems 31

Example (4 of 4) § Safe § Convenient § Efficient Spring 2007 CSC 440: Database Management Systems 32

DBMS Components § Storage manager: § Stores on disk: data, metadata, indexes, logs § Query processor: § Parses queries, optimizes by selecting query § plan, executes the plan on the data Transaction manager: § Logs database changes to support recovery after system crashes § Supports concurrent execution of transactions Spring 2007 CSC 440: Database Management Systems 33

DBMS Structure in More Detail Strategy Selector User Transaction Concurrency Control Lock Table Query Parser Transaction Manager Buffer Manager Recovery Manager File Manager M. M. Buffer Statistical Data Log Indexes User Data Spring 2007 User System Data CSC 440: Database Management Systems 34

People DBMS implementor: builds systems n Database designer: sets up schema, loads data n Database user: queries/modifies data n You in the course project n Spring 2007 CSC 440: Database Management Systems 35

Less Traditional Applications Real-time, historical data and queries, “active” databases n Distributed, heterogeneous databases n Scientific data n Spring 2007 CSC 440: Database Management Systems 36