CENG 302 Introduction to Database Management Systems Nihan

CENG 302 Introduction to Database Management Systems Nihan Kesim Çiçekli email: nihan@ceng. metu. edu. tr URL: http: //www. ceng. metu. edu. tr/~nihan/ceng 302 1

CENG 302 • • Instructor: Nihan Kesim Çiçekli Office: A 308 Email: nihan@ceng. metu. edu. tr Lecture Hours: Tue. 10: 40 -11: 30 (IE 102); Thu. 13: 40 -15: 30 (IE 102) • Course Web page: http: //www. ceng. metu. edu. tr/~nihan/ceng 302 • Teaching Assistant: Ali Anıl Sınacı 2

Text Books and References 1. Raghu Ramakrishnan, Database Management Systems, Mc. Graw Hill, 3 rd edition, 2003 (text book). 2. R. Elmasri, S. B. Navathe, Fundamentals of Database Systems, 4 th edition, Addison-Wesley, 2004. 3. A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, Mc. Graw Hill, 4 th edition, 2002. 3

Grading • • Assignments Midterm 1 Midterm 2 Final Exam 20 % 25 % 30 % 4

Grading Policies • Policy on missed midterm: – no make-up exam • Lateness policy: – Late assignments are penalized up to 10% per day. • All assignments are to be your own work. 5

Course Outline • • • Introduction to Relational Database Management Systems The Relational Data Model Relational Algebra SQL QBE Entity-Relationship Model Relational Database Design: Normalization Secondary Storage Devices Sequential Files Indexed Sequential Files Hashing 6

What Is a DBMS? Ø A very large, integrated collection of data. Ø Models real-world enterprise. – Entities (e. g. , students, courses) – Relationships (e. g. , Tarkan is taking CENG 302) Ø A Database Management System (DBMS) is a software package designed to store and manage databases. 7

Why Study Databases? ? ? Ø Shift from computation to information – – at the “low end”: scramble to webspace (a mess!) at the “high end”: scientific applications Ø Datasets increasing in diversity and volume. – – Digital libraries, interactive video, Human Genome project, EOS project. . . need for DBMS exploding Ø DBMS encompasses most of CS – OS, languages, theory, “AI”, multimedia, logic 8

Why Use a DBMS? Ø Data independence and efficient access. Ø Reduced application development time. Ø Data integrity and security. Ø Uniform data administration. Ø Concurrent access, recovery from crashes. 9

Data Models Ø A data model is a collection of concepts for describing data. Ø A schema is a description of a particular collection of data, using the given data model. Ø The relational model of data is 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. 10

Example: University Database Ø Conceptual schema: – – – Students(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) 11

Instance of Students Relation Students( sid: string, name: string, login: string, age: integer, gpa: real ) sid 53666 53688 53650 name Jones Smith login jones@cs smith@ee smith@math age 18 18 19 gpa 3. 4 3. 2 3. 8 12

Levels of Abstraction Ø Many external schemata, single conceptual(logical) schema and physical schema. – External schemata describe how users see the data. – Conceptual schema defines logical structure – Physical schema describes the files and indexes used. External Schema 1 External Schema 2 External Schema 3 Conceptual Schema Physical Schema * Schemas are defined using DDL; data is modified/queried using DML. 13

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! 14

These layers must consider concurrency control and recovery Structure of a DBMS Ø A typical DBMS has a layered architecture. Ø This is one of several possible architectures; each system has its own variations. Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB 15