CS 464/564Introduction to Database Management SystemInstructor: Abdullah MueenLECTURE 1: INTRODUCTION

About MeProf. Abdullah MueenOffice: B01 Travelstead Hall (Building 65 in Campus Map)Office Hours: Monday 10:30-12:00, Tuesday 10:30-12:00; other times by appointmentEmail: mueen@cs.unm.eduOffice Phone: 505-277-1914

Course PageWebpage: http://www.cs.unm.edu/~mueen/Teaching/CS_464_564/

Learn: https://learn.unm.edu/

Textbook and ReferencesDatabase Systems, The Complete Book (2nd Edition),by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom.

◦ Copies on reserve at the library (should be 2)◦ Homework assignments◦ Developed at Stanford, other schools◦ Slides by Jeffrey Ullman: http://www-db.stanford.edu/~ullman/dscb/pslides/pslides.html◦ Slides by Hector Garcia-Molina: http://www-db.stanford.edu/~hector/cs245/notes.htm◦ Slides by Arthur Keller: http://www.soe.ucsc.edu/classes/cmps180/Winter02◦ Slides by Rada Chirkova: http://courses.ncsu.edu/csc540/lec/001/

Course Outline Exams 60% : There will be two midterm exams and one final exam, worth 20% each.

Homework 20% : There will be five homework/assignments, typically due on every other Thursdays.

Project 20% : There will be a significant programming project. The project will have several checkpoints. Each group will develop a webpage in the department server. The page will be visible to everyone.

Two person group: Your responsibility. Form group by Jan 30th.

No individual project

Each group will do a unique project in a unique domain

Information Students are responsible for looking up deadlines in

Learn.

Deadlines are strict with zero tolerance.

See me in office hours if You don’t know any of the following languages

C/C++/Java/Python

You have not taken any algorithms class in your life

CS464/564 The two sections will be graded at their own scale.

Homework, project, exams will have different questions for the two sections.

CS Account Name

Email address

Section registered/waitlisted for: 464 or 564?

Do you have a CS account?

If yes, write down the account name

If no, Go to CS Support office (ECE 214) with your Lobocards.

Database Management System (DBMS)Specalized software that manages access to very large amounts of data:◦ Data persists over a long time◦ Efficent access◦ Concurrent access◦ Reliable and predictable access◦ Convenient interface language

Application examples:Airline reservation systems, banking, corporate records, etc.

Outline of the CourseDatabase Design: ◦ Entity-Relationship model: notation to express relationships among the data

(Chs 1, 4)◦ Relational model (Chs 2-3)

Database Programming:◦ Relational algebra: how to operate on relations (Ch 5)◦ Programming with SQL (Chs 6-8, 9)

Database Implementation:◦ storage management (Chs 13-14)◦ query processing (Chs 15-16)◦ transaction processing (Chs 17-19)

Relational Model IntroductionStore information in tables

Each table is a relation

Each column is named with an attribute

Each row is a tuple

Example relation named Accounts

accountNo balance type

12345 1000.0 savings

67890 2846.92 checking

Structured Query Language (SQL) PreviewAccounts

accountNo balance type

12345 1000.0 savings

67890 2846.92 checking

SELECT balanceFROM AccountsWHERE accountNo = 67890;

SELECT accountNoFROM AccountsWHERE type = 'savings'

AND balance < 0;

Setting up a DatabaseDatabase schema specifies what relations are in the database

Schema is altered using DDL (data definition language)

Only database administrator should be able to do this

Schema affects how the data is stored

Querying the DatabaseA query is expressed in a DML (data manipulation language), e.g. SQL

The query compiler translates a query into a query plan (sequence of operations to be performed)

Query compilation includes query parsing, query preprocessing and query optimization.

Execution engine performs the operations, interacting with storage manager, scheduler and logger, etc.

Storage and Buffer ManagementData is usually stored on disk, in units called blocks

Storage manager controls movement of data between disk and main memory

Buffer manager controls partitioning of main memory into block-sized regions

Relies on info about data, schema, statistics, and indexes (special data structures for efficient access)

TransactionsUsually database queries are grouped into transactions

A transaction must satisfy the ACID properties:◦ Atomicity: Either all the changes or none of them are made to the database

◦ Consistency: transaction must preserve consistency constraints of the database

◦ Isolation: the result must be “as if” the transaction ran alone

◦ Durability: once transaction has completed, changes must not be lost

Transaction ProcessingEvery change is logged separately on disk by a log manager

After a system failure, recovery manager uses log on disk to reconstruct a consistent state

Scheduler ensures that concurrently executing transactions do not interfere with each other. In case of Deadlock the scheduler intervenes.