Introduction to Databases-Databases-Lecture 01 Slides-Computer Science, Slides of Database Management Systems (DBMS)

Download Introduction to Databases-Databases-Lecture 01 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Lecture 1:

Introduction to databases

Timothy G. Griffin

Easter Term 2008 – IB/Dip/IIG

www.cl.cam.ac.uk/Teaching/current/Databases/

2

Database Prehistory

Data entry Storage and retrieval

Query processing Sorting

Our Hero --- E. F. Codd

Edgar F. "Ted" Codd ( August 23, 1923 - April 18, 2003 ) was a British computer scientist who invented relational databases while working for IBM.

He was born in Portland, Dorset, studied maths and chemistry at Oxford. He was a pilot in the Royal Air Force during WWII. In 1948 he joined IBM in New York as a mathematical programmer. He fled the USA to Canada during the McCarthy period. Later, he returned to the USA to earn a doctorate in CS from the University of Michigan in Ann Arbor. He then joined IBM research in San Jose.

His 1970 paper “A Relational Model of Data for Large Shared Data Banks” changed everything.

In the mid 1990’s he coined the term OLAP.

5

Database Management Systems (DBMSs)

Raw Resources (bare metal)

DBMS

Your Applications

Go Here Database abstractions allow this interface to

be cleanly defined and this allows applications and data management systems to be implemented separately.

What is a database system?

• A database is a large, integrated

collection of data

• A database contains a model of

something!

• A database management system

(DBMS) is a software system designed to

store, manage and facilitate access to the

database

What does a database system

do?

• Manages Very Large Amounts of Data

• Supports efficient access to Very Large

Amounts of Data

• Supports concurrent access to Very

Large Amounts of Data

• Supports secure , atomic access to Very

Large Amounts of Data

What this course is about

• According to Ullman, there are three

aspects to studying databases:

1. Modelling and design of databases

2. Programming

3. DBMS implementation

• This course addresses 1 and 2

Course Outline

1. Introduction
2. Entity-Relationship Model
3. The Relational Model
4. The Relational Algebra
5. The Relational Calculus
6. Schema refinement: Functional dependencies
7. Schema refinement: Normalisation
8. Transactions
9. Online Analytical Processing (OLAP)
10. More OLAP
11. Basic SQL and Integrity Constraints
12. Further relational algebra, further SQL

Some systems to play with

1. mysql:

• www.mysql.org
• Open source, quite powerful

2. PostgreSQL:

• www.postgresql.org
• Open source, powerful

3. Microsoft Access:

• Simple system, lots of nice GUI wrappers

4. Commercial systems:

• Oracle 10g (www.oracle.com)
• SQL Server 2000 (www.microsoft.com/sql)
• DB2 (www.ibm.com/db2)

Database system architecture

• It is common to describe databases in two ways

• The logical level :
  • What users see, the program or query language interface, …
• The physical level :
  • How files are organised, what indexing mechanisms are used, …

• It is traditional to split the logical level into two:

overall database design ( conceptual ) and the

views that various users get to see

• A schema is a description of a database

Logical and physical data

independence

• Data independence is the ability to change the

schema at one level of the database system

without changing the schema at the next higher

level

• Logical data independence is the capacity to

change the conceptual schema without

changing the user views

• Physical data independence is the capacity to

change the internal schema without having to

change the conceptual schema or user views

Database design process

• Requirements analysis

• User needs; what must database do?

• Conceptual design

• High-level description; often using E/R model

• Logical design

• Translate E/R model into (typically) relational schema

• Schema refinement

• Check schema for redundancies and anomalies

• Physical design/tuning

• Consider typical workloads, and further optimise

Next Lecture

A Theme of this Course: OLTP vs. OLAP

• OLTP = Online Transaction Processing
  • Need to support many concurrent transactions (updates and queries)
  • Normally associated with the “operational database” that supports day-to-day activities of an organization.
• OLAP = Online Analytic Processing
  • Often based on data extracted from operational database, as well as other sources
  • Used in long-term analysis, business trends.

20

Service Tools

End Users

Production DB^ Service DB

Development DB

Submitters Submission tools

Add value (computation) Add value (review etc.)

Other archives

Q/C etc

Database design

Releases & Updates

Releases & Updates

Design Heterogeneity

Database system design from the European Bioinformatics Institute (Hinxton UK)

De-normalized Derived Tables --- for fast access

Normalized Tables