Database Management Systems INFS 614-001 Fall 08

1. Database Management SystemsINFS 614-001Fall 08 Lecture One - Introduction Instructor: Carlotta Domeniconicarlotta@cs.gmu.edu http://www.cs.gmu.edu/~carlotta/teaching/INFS-614-s08/info.html

2. Outline • Course syllabus • Course Schedule • Homeworks & exams • Satisfaction of prerequisites • Strictly enforced: GMUHONOR CODE applies! • Introduction to DB & DBMS • Outline of the entire course material

3. Front matters • To communicate with me: • Email: carlotta@cs.gmu.edu, I will try to reply promptly. • Office hours: by appointment. • Sign up for your Mason account. You may forward all your Mason emails to your favorite email address.

4. Front matters (cont.) • GTA: • Huaming Liu and Chun-Kit Ngan • Email: hliu5@gmu.edu • Office hours: Tuesday, 4-6pm (Huaming) • Room: 330 -ST2.

5. Front matters (cont.) • Required textbooks: • Database Management Systems, 3rd ed. by Raghu Ramakrishnan & Johannes Gehrke, McGraw-Hill. • Oracle 9i Programming: a Primer, by Rajshkhar Sunderraman, Addison Wesley, ISBN 0-321-19498-5 • On-Line Course Resources: http://www.cs.gmu.edu/~carlotta/teaching/INFS-614-f08/info.html • You are required to read all the material there. The content will be updated frequently. So check the web site periodically, at least once every week, and every time before class!

7. Submission and Grading • Late submissions are not accepted: no exceptions! • No make-up exams! • On-time: within 5 minutes after the class begins. • Important: your homeworks must run properly under the Oracle system in the labs. • Final grades: • homework assignments (20%) • Project (15%) • midterm exam (25%) • final exam (40%)

8. Honor Code System • GMU honor Codehttp://honorcode.gmu.edu/ • For this class • Homeworks & exams require individual work. Study groups are encouraged, but homeworks’ solutions and write up must be individual. • Exams: individual effort, closed books • Satisfaction of prerequisites: • Honor code invoked.

9. Satisfaction of prerequisites • Prerequisites (strictly enforced) • INFS-501 (Discrete mathematics) • INFS-515 (Computer architecture/organization) • INFS-590 (Program Design and data structures) Specifically: • Good background in discrete mathematics (e.g., set theory, mathematical logic, relations and functions); • Programming (good knowledge of either C, C++ or Java); • Data structures and algorithms, Computer architecture, and Operating systems.

10. Satisfaction of prerequisites For INFS/SWE/ISA students: • Consult your letter of acceptance. It specifies your status with respect to these foundation courses. For each course, it must be that either • You were waved from the course (the evidence should be either in the acceptance letter or in a subsequent official document). • You took the course and received a grade of B or better.

11. Satisfaction of prerequisites • For non-IS/SWE/ISA students, MUST DO THE FOLLOWING (by next week): • Consult the description of each of the prerequisite courses in the university catalog. • For each course, provide a list of one or more courses taken, that cover the subjects of that course, as follows: course-number, course-title, institution, year, final-grade; • Syllabus of each course taken; • Copy of transcripts that shows equivalent classes taken (with grade B or better); • Current status;

12. Useful links for your computing needs • http://labs.ite.gmu.edu/ (click on FAQ’s) for IT&E computing labs, IT&E cluster account, and Oracle DBMS information. • http://cs.gmu.edu/~ami/teaching/infs614/current/oracle.html for information on our particular computing environment.

13. Introduction to DB and DBMS

14. What is a Database? • Database : A very large, integrated collection of data. • Data : Known facts about the real-world that can be recorded and have implicit meaning; • A database models real-world scenarios : • Entities • Relationships between entities

15. University Database • Information about university environment • Relationships : • Students’ enrollment course • Faculty teaching courses • Use of classroom for course • Prerequisite courses • Entities : • Students • Faculty • Courses • Classrooms

16. What is a DBMS? • A Database Management System (DBMS)is a software package designed to store, provide access and manage databases • One DBMS, many databases; • Database System: A database and a DBMS. • Why use a DBMS?

17. A simple problem: address list • Solution 1: a blank notebook, entries recorded with a pen, in time order. • Advantages: simple, private, reliable, space efficient. • Disadvantages: • Hard to search; • Hard to add information (e.g., e-mail); • Hard to update information; • Hard to extract information (print Christmas cards); • Integrity and consistency (Mary Jones: see P. Jones address, P. Jones-Smith entry); • Loosing it is a catastrophe!

18. Solution 2: a loose-leaf notebook with n entries per page • Better: • Can keep it sorted by key; • Insertions & deletions can be done; • Same as Sol. 1 in other aspects: • No search by other keys (e.g., phone number).

19. Solution 3: Text format,managed by text editor • Advantages: • Free format; • “Unlimited” size; • Easily copied (for backup); • Easily shared; • Sub-string searchable; • Easy Update. • Disadvantages: • Change requirements?

20. Database Concepts: Record Organization, Keys, Indexes Complications with Solution 3 • File gets very large • Search gets slow and possibly imprecise. (E.g., search for “Elm Street” may yield “Wilhelm Street”) • Solution: structure entries into records with fields and add indexes over fields.

21. Database Concepts: Consistency, Normalization, Foreign Keys Complication 2: need to separate families from addresses • Why? • People move; • Might forget to update addresses; • Want space economy: single point of update; • Important to separate for applications: 1 Christmas card per residence! • Solution: two files (one: people, one: addresses). How do we link them? How many residences a person can have?

22. Database Concepts: Relationships; Cardinality Complication 3: multiple association • People own, rent, manage residences • May want to impose constraints in the number of residences per person or vice versa. • Examples: Many to many (rich people); Many to one (single family); One to many (Builder); One to one (legal residence)

23. Database Concepts: Data Abstraction; Data Evolution Complication 4: dynamic nature of the data • Add new information: • Cards sent and received • Zip+4 • Requirements: • Adding fields • Summarizing

24. Database Concepts: Query languages; Query optimization Complication 5: Ad hoc analysis and retrieval • Example: • Find who sent me cards over the past 5 years, but received less than 3 cards from me. • Requirements: • A language • An implementation of retrieval functions (correct and efficient).

25. Database Concepts: Joins; Views; Security Complication 6: Sharing • Different users, different organizations • Other family members want to see names and addresses together • You don’t want to give update access over your business contacts to anybody. • Solutions: • Use stored queries as ”windows” or “views” over the database. • Ability to “reunite” data from different files. • Data not selected by the query is “not there” • Permissions

26. Database Concepts: Referential Integrity; Weak entity sets Complication 7: Required existence of associated data • Examples: • Can’t send Christmas card to somebody without an address • Names are not unique; only when associated with residence. • Solution: • Don’t insert a name if there is no address and vice versa • Or tolerate multiple non-unique names

27. Database Concepts: Transactions; ACID properties; Recovery Complication 8: Multiple updates in an all or none basis • Examples • Two households merge (marriage) • Need to change residences (or other data) for a group of people • Computer crashes in the middle of updates • Solution • Illusion of updates being done simultaneously • Commit or rollback an entire chunk of work

28. Database Concepts: Durability; Consistency (ACID); Recovery Complication 9: computer crashes • Will I have my data after the crash? • Uncorrupted? • Consistent? • Solution: • Make sure data is available uncorrupted at a point in the past (checkpoint)

29. Database Concepts: Multimedia databases; Query by content Complication 10: multimedia • Pictures, Audio, Text, … • Requirements: • Ability to store new data types • Content search • Integration with text and numeric data

30. Database Concepts: Data Warehousing; Data Mining Complication 11: You become President! … • Of something …(US, Corporation, Local chapter of charity, your household) • Your address list grows exponentially • You realize some of the information is useful! • Examples: • zip codes in states where there are less than 5% difference in Rep./Dem. Votes in 2004? • Which combinations of products sold best last year?

31. Files vs. DBMS • Application must store large datasets between main memory and secondary storage (e.g., buffering, page-oriented access, etc.); • Special code to answer different queries; • Must protect data from inconsistency due to multiple concurrent users; • Crash recovery; • Security and access control.

32. Why Use a DBMS? Easier and More Efficient • Data independence and efficient access; • Reduced application development time; • Data integrity and security; • Uniform data administration; • Concurrent access, recovery from crashes.

33. Data Models • A data modelis a collection of concepts for describing data. • Aschemais a description of a particular collection of data, using a given data model. • The relational model of datais the most widely used model today. • Main concept: relation, basically a table with rows and columns. • Every relation has a schema, which describes the columns, or fields.

34. Relational Model • The main concept is a relation: • A table with rows and columns • Each row in the table is called a tuple

35. Relational Model (cont.) • The relationschema specifies: • name of the relation, • name of each attribute (column,field) and its type. Every attribute has an atomic type. Relation Name Student(sid:string, login:string, age:integer, gpa:real); Attribute Name • A Relation (Relation instance): a set of tuples.

36. Levels of Abstraction View 1 View 2 View 3 • Many views, single conceptual (logical) schemaand physical schema. • Views describe how users see the data. • Conceptual schema defines logical structure • Physical schema describes the files and indexes used. Conceptual Schema Physical Schema • Schemas are defined using DDL (Data Definition Language); • Data is modified/queried using DML (Data Manipulation Language).

37. Levels of Abstraction • Conceptual Schema : the data is described through the data model. It describes structure and constraints for the whole database. • External Schema : how the users see and use the data. Many views of the data. • Physical schema : describes the physical structure of the DB • Mappings among schema levels are also needed. Programs and applications refer to an external schema, and are mapped by the DBMS to the conceptual schema for execution. Conceptual, External Schemas are defined using Data Definition Language (DDL) : specification for defining the database schema

38. Example: University Database • Conceptual schema: • Student (sid: string, name: string, login: string, age: integer, gpa: real) • Courses (cid: string, cname: string, credits: integer) • Enrolled (sid: string, cid: string, grade: string) • Physical schema: • Relations stored as unordered files. • Index on first column of Students… • External schema (View): • Course_info (cid: string, enrollment: integer)

39. Data Independence • Applications insulated from how data is structured and stored. • Logical data independence: Protection from changes in logical structure of data. • Physical data independence: Protection from changes in physical structure of data. • One of the most important benefits of using a DBMS!

40. Easy Manipulation & Access of the Information • A DBMS enables users to create , update and query the data through Data Languages • Data Definition Language (DDL) • Specification notation to create the Database schema • Data Manipulation Language (DML) • A language for manipulating the data : updating the data and accessing the data • The portion of a DML that allows to access the information through formulating queries is called the Query Language Query : Request for retrieving data stored in a DBMS.

41. Concurrency Control • Concurrent execution of user programs is essential for good DBMS performance • Because disk accesses are frequent, and relatively slow, it is important to keep the cpu not idle by working on several user programs concurrently. • Interleaving actions of different user programs can lead to inconsistency: e.g., check is cleared while account balance is being computed. • DBMS ensures such problems don’t arise: users can pretend they are using a single-user system.

42. Concurrency Control (cont.) Example: • One course still has space for one more student. • Two students are trying to enroll in that course at the same time. • The DBMS executes the two requests in a serial order. • Thus, only one student will be enrolled.

43. Transaction • An execution of a DB program • Key concept is transaction, which is an atomicsequence of database actions (reads/writes). • ACID properties • A – Atomicity • C – Consistency • I – Isolation • D – Durability • How: log and concurrency control sub-system

44. Scheduling Concurrent Transactions • DBMS ensures that execution of {T1,…, Tn} is equivalent to some serial execution T1’…Tn’. • Before reading/writing an object, a transaction requests a lock on the object, and waits till the DBMS gives it the lock. All locks are released at the end of the transaction. (Strict 2PL locking protocol.) • Idea: If an action of Ti (say, writing X) affects Tj (e.g., reads X), one of them, say Ti, will obtain the lock on X first and Tj is forced to wait until Ti completes; this effectively orders the transactions.

45. Ensuring Atomicity • DBMS ensures atomicity (all-or-nothing property) even if system crashes in the middle of a transaction. • Idea: Keep a log (history) of all actions carried out by the DBMS while executing a set of transactions: • Before a change is made to the database, the corresponding log entry is forced to a safe location. (WAL – Write-Ahead Log – protocol); • After a crash, the effects of partially executed transactions are undone using the log.

46. The Log • The following actions are recorded in the log: • Ti writes an object: the old and new value. • Log record must go to disk before the changed page! • Ti commits/aborts: a log record indicating this action. • Log records chained together by the transaction id, so it’s easy to undo a specific transaction (e.g., resolve a deadlock) • All log related activities are handled transparently by the DBMS.

47. Structure of a DBMS • A typical DBMS has a layered architecture • Each layer is composed of several modules • The architecture varies from vendor to vendor

48. Main cost of using aDBMS • High initial investment and possible need for additional hardware. • Overhead for providing generality, security, recovery, integrity and concurrency control. When aDBMS may be unnecessary • If the Database and application are simple, well-defined and not expected to change. • If there are stringent real-time requirements, that may not be met due to DBMS overhead. • If access to data by multiple users is not required.

49. Database Users • End users (or DB application users) • DB application programmers (more precisely, they are DBMS users) • E.g. smart webmasters • This course is mostly to learn how to (start to) be a DB application programmer. • Database administrator (DBA) • Designs logical /physical schemas • Handles security and authorization • Data availability, crash recovery • Database tuning as needs evolve Must understand how a DBMS works!

50. Summary • DBMS used to maintain, query large datasets. • Benefits include recovery from system crashes, concurrent access, quick application development, data integrity and security. • Levels of abstraction give data independence. • We will learn how to • Set up a database • Design (ERD and Relational Model), and refine (Relational Normalization Theory) • Use to query the database • Relational Algebra and SQL