CENG 302 Introduction to Database Management Systems Nihan Kesim

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

CENG 302 Instructor: Nihan Kesim Çiçekli Office: A308 Email: [email protected] Lecture Hours: Tue. 10:40-11:30 (IE102); Thu. 13:40-15:30 (IE102) Course Web page: http://www.ceng.metu.edu.tr/ nihan/ceng302 Teaching Assistant: Ali Anıl Sınacı 2

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

Grading Assignments 20 % Midterm 1 25 % Midterm 2 25 % Final Exam 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 CENG302) A Database Management System (DBMS) is a software package designed to store and manage databases.

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 ?

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.

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. 1

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) 1

Instance of Students Relation Students( sid: string, name: string, login: string, age: integer, gpa: real ) sid gpa 53666 53688 53650 name Jones Smith Smith login jones@cs 18 smith@ee 18 smith@math 19 age 3.4 3.2 3.8 1

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

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

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 1