Introduction to Database Management Systems

1. Introduction to Database Management Systems

2. Information • Instructor: Csilla Farkas • Office: Swearingen 3A43 • Office Hours: Monday, Wednesday 2:30 pm – 3:30 pm, electronically, or by appointment • Telephone: 576-5762 • E-mail: farkas@cec.sc.edu • Class homepage: http://www.cse.sc.edu/~farkas/csce520-2013/csce520.htm CSCE 520

3. Prerequisite • CSCE 245 (CSCE 240, EECE 352) or • GEOG 563 or • Permission of the instructor CSCE 520

4. Text books • J. D. Ullman and J. Widom: A First Course in Database Systems, 3rd edition, Prentice Hall, ISBN: 013600637X • R. Sunderraman: Oracle 10g Programming (or Oracle 10i Programming), A Primer, Addison Wesley Longman, Inc., ISBN: 0321463048 CSCE 520

5. Grading • Tests: Test 1: 20%, Test 2: 35% • Homework Assignments: 45% • Includes assignments related to the lectures • Includes hands-on database implementation • Includes demo of Oracle implementation • Total score: 100 • Final grade: 90 < A, 87 < B+ <=90, 80< B <= 87, 77 < C+ <= 80, 68 < C <= 77, 62 < D+ <= 68, 52 < D <= 62, F <= 52 CSCE 520

6. Course Policies • Code of Student Academic Responsibility • Incompletes CSCE 520

7. APOGEE Students • Access recorded lectures • Contact instructor via • Phone (during office hours) • Email • Or any time at a prearranged time • Need to attend exams as scheduled • Need to arrange for Oracle demo CSCE 520

8. Tentative Schedule • Week 1: Introduction • Week 2: ER model • Week 3: Relational model • Week 4: Project overview and Oracle accounts • Week 5: Relational Algebra • Week 6: SQL • Week 7: Relational Design • Week 8: Constraints • Week 9: Transactions • Week 10: Logical query languages • Week 11: Data storage and Data representation • Week 12: Index structures • Week 13: System Failures • Week 14: Concurrency control • Week 15: Summary and Review CSCE 520

9. Test Schedule • Test 1: Early October • Test 2: December 9, 4:00 pm CSCE 520

10. Questions?

11. Chapter 1: Introduction • Evolution of Database Management Systems • Overview of Database Management Systems • Database-System Design CSCE 520

12. Database Management System (DBMS) • Databases touch all aspects of our lives • DBMS: • Collection of interrelated data • Set of programs to access the data • Convenient and efficient processing of data • Database Applications CSCE 520

13. Evolution of Database Systems • Early days: database applications built on top of file systems • Drawbacks of using file systems to store data: • Data redundancy and inconsistency • Difficulty in accessing data • Atomicity of updates • Concurrency control • Security • Data isolation — multiple files and formats • Integrity problems CSCE 520

14. Abstraction • View level: application programs hide details of data types. • Logical level: What is the data? type employee = recordname : string;address : string; salary: real;end; • Physical level: How the data is stored? CSCE 520

15. Data Models • A collection of tools for describing • Data • Relationships among data items • Semantics of stored data • Database constraints CSCE 520

16. Data Models • Entity-Relationship model • Relational model • Other models: • Network • Hierarchical • Object-oriented • Semi-structured • Steaming data CSCE 520

17. Database Management Systems • Smaller and smaller systems • Past: large and expensive DBMS • Present: DBMS in most personal computers • More and more data stored • Past: few MB • Present: terabyte (1012 bytes), petabyte (1015 bytes) Data Tsunami CSCE 520

18. Database Users • Users are differentiated by the way they interact with the system • Database Administration: responsible for the structure or schema of the database (DDL), coordinates all activities regarding the database • Application programmers – interact with system through DML calls • Sophisticated users – form requests in a database query language • Naive users – invoke one of the permanent application programs that have been written previously CSCE 520

19. Data Definition Language (DDL) • Defines the database schema and constraints • DDL compiler data dictionary • Metadata – data about data CSCE 520

20. Data Manipulation Language (DML) • Accessing and manipulating the data • DML – query language • Query Languages • Procedural – user specifies what data is required and how to get those data • Nonprocedural – user specifies what data is required without specifying how to get those data • SQL: nonprocedural query language CSCE 520

21. Transaction Management • Transaction: unit of work to be executed atomically and in isolation from other transactions • Transaction-manager: ensures that the database remains in a consistent • system failures • transaction failures • Concurrency-control: interaction among the concurrent transactions to ensure consistency CSCE 520

22. ACID Properties • Atomicity: all-or-nothing of the transaction’s effect will take place • Consistency: each transaction leaves the system in a consistent state • Isolation: each transaction must appear to be executed as if no other transactions are executed at the same time • Durability: effect of a transaction must never be lost after the transaction is completed CSCE 520

23. Transaction Processing • Logging: • Log manager • Recovery manager • Concurrency control • Multiple transactions • Locking protocols • Deadlock resolution CSCE 520

24. Database System Studies • Design of the database • What to store, structure, semantics • Functionality requirement • Trade offs • Security • Database programming • How to express database operations, capability requirements, etc. • Database implementation • Query, transaction processing, storage, efficiency CSCE 520

25. Next Class: Relational Database Modeling • A First Course: Chapter 2 (2.1, 2.2, 2.3) CSCE 520