Download Understanding Relational Databases: Structure, Components, and Importance - Prof. Jimmy Jr and more Study notes School management&administration in PDF only on Docsity!
LBSC 690 Session
Structured Information: Databases
Jimmy Lin
The iSchool
University of Maryland
Wednesday, October 15, 2008
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States See http://creativecommons.org/licenses/by-nc-sa/3.0/us/ for details
Take-Away Messages
| Databases are suitable for storing structured information
| Databases are important tools to organize, manipulate,
and access structured information
| Databases are integral components of modern Web
applications
The iSchool University of Maryland
Databases Yesterday…
Database today?
What’s structured information?
It’s what you put in a database
What’s a database?
It’s what you store structured information in
So what’s a database?
An integrated collection of data organized
according to some model…
So what’s a relational database?
An integrated collection of data organized
according to a relational model
Database Management System (DBMS)
Software system designed to store, manage,
and facilitate access to databases
Databases (try to) model reality…
| Entities: things in the world
z Example: airlines, tickets, passengers
| Relationships: how different things are related
z Example: the tickets each passenger bought
| “Business Logic”: rules about the world
The iSchool University of Maryland
z Example: fare rules
So whatSo what s the Web?’s the Web?
Relational Databases
Source: Microsoft Office Clip Art
Components of a Relational Database
| Field: an “atomic” unit of data
| Record: a collection of related fields
| Table: a collection of related records
z Each record is a row in the table
z Each field is a column in the table
The iSchool University of Maryland
| Database: a collection of tables
A Simple Example
Name DOB SSN
John Doe 04/15/1970 153-78-
J S ith 08/31/1985 768 91 2376
Field Name
Table
The iSchool University of Maryland
Jane Smith 08/31/1985 768-91-
Mary Adams 11/05/1972 891-13-
Field
Record
Primary Key
Approaches to Normalization
| For simple problems (like the homework):
z Start with the entities you’re trying to model
z Group together fields that “belong together”
z Add keys where necessary to connect entities in different tables
| For more complicated problems:
z Entity-relationship modeling (LBSC 670)
The iSchool University of Maryland
z Entity-relationship modeling (LBSC 670)
The Data Model
Department ID Department EE Electrical Engineering
Course ID Course Name
lbsc690 Information Technology
Student ID Last Name First Name Department ID email 1 Arrows John EE jarrows@wam 2 Peters Kathy HIST kpeters2@wam 3 Smith Chris HIST smith2002@glue 4 Smith John CLIS js03@wam
Student Table
Department Table Course Table
The iSchool University of Maryland
g g HIST History CLIS Information Studies
gy
ee750 Communication
hist405 American History
Student ID Course ID Grade
1 lbsc690 90
1 ee750 95
2 lbsc690 95
2 hist405 80
3 hist405 90
4 lbsc690 98
Enrollment Table
Registrar ER Diagram
Enrollment
Student
Course
Grade
Student
Student ID
First name
Last name
Department
E-mail
has
The iSchool University of Maryland
Course
Course ID
Course Name
Department
Department ID
Department Name
has
associated with
Types of Relationships
The iSchool University of Maryland
Many-to-Many One-to-Many One-to-One
Database Integrity
| Registrar database must be internally consistent
z All enrolled students must have an entry in the student table
z All courses must have a name
z …
| What happens:
z When a student withdraws from the university?
The iSchool University of Maryland
z When a student withdraws from the university?
z When a course is taken off the books?
Integrity Constraints
| Conditions that must be true of the database at any time
z Specified when the database is designed
z Checked when the database is modified
| RDBMS ensures that integrity constraints are always kept
z So that database contents remain faithful to the real world
z Helps avoid data entry errors
The iSchool University of Maryland
z Helps avoid data entry errors
| Where do integrity constraints come from?
Relational Algebra
(Don’t Panic!)(Don t Panic!)
Join
Student ID Last Name First Name Department ID email 1 Arrows John EE jarrows@wam 2 Peters Kathy HIST kpeters2@wam 3 Smith Chris HIST smith2002@glue 4 Smith John CLIS js03@wam
Student Table
Department Table
Department ID Department EE El t i l E i i
The iSchool University of Maryland
Student ID Last Name First Name Dept ID Department email
1 Arrows John EE Electrical Engineering jarrows@wam
2 Peters Kathy HIST History kpeters2@wam
3 Smith Chris HIST History smith2002@glue
4 Smith John CLIS Information Stuides js03@wam
“Joined” Table
EE Electrical Engineering HIST History CLIS Information Studies
Project
Student ID Last Name First Name Dept ID Department email
1 Arrows John EE Electrical Engineering jarrows@wam
2 Peters Kathy HIST History kpeters2@wam
3 Smith Chris HIST History smith2002@glue
4 Smith John CLIS Information Stuides js03@wam
The iSchool University of Maryland
SELECT Student ID, Department
Student ID Department
1 Electrical Engineering
2 History
3 History
4 Information Stuides
Restrict
Student ID Last Name First Name Dept ID Department email
1 Arrows John EE Electrical Engineering jarrows@wam
2 Peters Kathy HIST History kpeters2@wam
3 Smith Chris HIST History smith2002@glue
4 Smith John CLIS Information Stuides js03@wam
The iSchool University of Maryland
Student ID Last Name First Name Department ID Department email 2 Peters Kathy HIST History kpeters2@wam 3 Smith Chris HIST History smith2002@glue
WHERE Department ID = “HIST”
Relational Operations
| Joining tables: JOIN
| Choosing columns: SELECT
z Based on their labels (field names)
| Choosing rows: WHERE
z Based on their contents
The iSchool University of Maryland
| These can be specified together
department ID = “HIST”
SELECT Student ID, Dept WHERE Dept = “History”
So how’s a database more than a spreadsheet?So how s a database more than a spreadsheet?
Must support concurrent operations
Solution: this is hard!
(But fortunately doesn’t
matter for many applications)
Database Transactions
| Transaction = sequence of database actions grouped
together
z e.g., transfer $500 from checking to savings
| ACID properties:
z Atomicity: all-or-nothing
z Consistency: each transaction must take the DB between
The iSchool University of Maryland
z Consistency: each transaction must take the DB between
consistent states
z Isolation: concurrent transactions must appear to run in isolation
z Durability: results of transactions must survive even if systems
crash
Making Transactions
| Idea: keep a log (history) of all actions carried out while
executing transactions
z Before a change is made to the database, the corresponding log
entry is forced to a safe location
the log
The iSchool University of Maryland
| Recovering from a crash:
z Effects of partially executed transactions are undone
z Effects of committed transactions are redone
z Trickier than it sounds!
the log
Source: Technology Review (July/August, 2008)
Database layer: 800 eight-core Linux servers running
MySQL (40 TB user data)
Caching servers: 15 million requests per second, 95%
handled by memcache (15 TB of RAM)
RideFinder Exercise
| Design a database to match drivers with passengers (e.g.,
for road trips):
z Drivers post available seats; they want to know about interested
passengers
z Passengers call up looking for rides: they want to know about
available rides (they don’t get to post “rides wanted” ads)
These things happen in no particular order
The iSchool University of Maryland
z These things happen in no particular order
Exercise Goals
| Design the tables you will need
z First decide what information you need to keep track of
z Then design tables to capture this information
| Design queries (using join, project, and restrict)
z What happens when a passenger comes looking for a ride?
z What happens when a driver comes to find out who his
The iSchool University of Maryland
z What happens when a driver comes to find out who his
passengers are?
| Role play!
