Databases
Week 6
LBSC 690
Information Technology
Agenda
•	Questions

•	Relational database design

•	Microsoft Access
Databases
•	Database
–	Collection of data, organized to support access
–	Models some aspects of reality

•	DataBase Management System (DBMS)
–	Software to create and access databases

•	Relational Algebra
–	Special-purpose programming language
Structured Information
•	Field		An “atomic” unit of data
–	number, string, true/false, …
•	Record		A collection of related fields
•	Table 		A collection of related records
–	Each record is one row in the table
–	Each field is one column in the table
•	Primary Key	The field that identifies a record
–	Values of a primary key must be unique
•	Database	A collection of tables
A Simple Example
Registrar Example
•	Which students are in which courses?

•	What do we need to know about the students?
–	first name, last name, email, department

•	What do we need to know about the courses?
–	course ID, description, enrolled students, grades 

A “Flat File” Solution
Goals of “Normalization”
•	Save space
–	Save each fact only once 

•	More rapid updates
–	Every fact only needs to be updated once

•	More rapid search
–	Finding something once is good enough

•	Avoid inconsistency
–	Changing data once changes it everywhere
Relational Algebra
•	Tables represent “relations”
–	Course, course description
–	Name, email address, department

•	Named fields represent “attributes”

•	Each row in the table is called a “tuple”
–	The order of the rows is not important

•	Queries specify desired conditions
–	The DBMS then finds data that satisfies them
A Normalized Relational Database
Approaches to Normalization
•	For simple problems (like the homework)
–	Start with “binary relationships”
•	Pairs of fields that are related
–	Group together wherever possible
–	Add keys where necessary

•	For more complicated problems
–	Entity relationship modeling (LBSC 670)
Example of Join
Problems with Join
•	Data modeling for join is complex
–	Taught in LBSC 670 

•	Join are expensive to compute
–	Both in time and storage space

•	But it is joins that make databases relational
–	Projection and restriction also used in flat files
Some Lingo
•	“Primary Key” uniquely identifies a record
–	e.g. student ID in the student table

•	“Compound” primary key
–	Synthesize a primary key with a combination of fields
–	e.g., Student ID + Course ID in the enrollment table 

•	“Foreign Key” is primary key in the other table
–	Note: it need not be unique in this table
Project
Restrict
Entity-Relationship Diagrams
•	Graphical visualization of the data model

•	Entities are captured in boxes

•	Relationships are captured using arrows
Registrar ER Diagram
Types of Relationships
A More Complex ER Diagram
Databases in the Real World
•	Some typical database applications:
–	Banking (e.g., saving/checking accounts)
–	Trading (e.g., stocks)
–	Airline reservations

•	Characteristics:
–	Lots of data
–	Lots of concurrent access
–	Must have fast access
–	“Mission critical”
Database Integrity
•	Registrar database must be internally consistent
–	Enrolled students must have an entry in student table
–	Courses must have a name

•	What happens:
–	When a student withdraws from the university?
–	When a course is taken off the books?

Integrity Constraints
•	Conditions that must always be true
–	Specified when the database is designed
–	Checked when the database is modified

•	RDBMS ensures integrity constraints are respected
–	So database contents remain faithful to real world
–	Helps avoid data entry errors
Referential Integrity
•	Foreign key values must exist in other table
–	If not, those records cannot be joined

•	Can be enforced when data is added
–	Associate a primary key with each foreign key

•	Helps avoid erroneous data
–	Only need to ensure data quality for primary keys
Concurrency
•	Thought experiment: You and your project partner are editing the same file…
–	Scenario 1: you both save it at the same time
–	Scenario 2: you save first, but before it’s done saving, your partner saves

Concurrency Example
•	Possible actions on a checking account
–	Deposit check (read balance, write new balance)
–	Cash check (read balance, write new balance)
•	Scenario:
–	Current balance: $500
–	You try to deposit a $50 check and someone tries to cash a $100 check at the same time
–	Possible sequences: (what happens in each case?)

Database Transactions
•	Transaction: sequence of grouped database actions
–	e.g., transfer $500 from checking to savings
•	“ACID” properties
–	Atomicity
•	All-or-nothing
–	Consistency
•	Each transaction must take the DB between consistent states.
–	Isolation:
•	Concurrent transactions must appear to run in isolation
–	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
–	Before a change is made to the database, the corresponding log entry is forced to a safe location

•	Recovering from a crash:
–	Effects of partially executed transactions are undone
–	Effects of committed transactions are redone
RideFinder Exercise
•	Design a database to match passengers with  available rides over Spring Break
–	Drivers phone in available seats
•	They want to know about interested passengers
–	Passengers call up looking for rides
•	They want to know about available rides
–	These things happen in no particular order
Exercise Goals
•	Identify the tables you will need
–	First decide what data you will save
•	What questions will be asked?
–	Then decide how to group/split it into tables
•	Start with binary relations if that helps
•	Design the queries
–	Using join, project and restrict
•	Add primary and foreign keys where needed
Exercise Logistics
•	Work in groups of 3 or 4
•	Brainstorm data requirements for 5 minutes
–	Do passengers care about the price?
–	Do drivers care how much luggage there is?
•	Develop tables and queries for 15 minutes
–	Don’t get hung up on one thing too long
•	Compare you answers with another group
–	Should take about 5 minutes
Database “Programming”
•	Natural language
–	Goal is ease of use
•	e.g., Show me the last names of students in CLIS
–	Ambiguity sometimes results in errors

•	Structured Query Language (SQL)
–	Consistent, unambiguous interface to any DBMS
–	Simple command structure:
•	e.g., SELECT Last name FROM Students WHERE Dept=CLIS
–	Useful standard for inter-process communications

•	Visual programming (e.g., Microsoft Access)
–	Unambiguous, and easier to learn than SQL 
The SELECT Command
•	Project chooses columns
–	Based on their label

•	Restrict chooses rows
–	Based on their contents
•	e.g. department ID = “HIST”

•	These can be specified together
–	SELECT Student ID, Dept WHERE Dept = “History”
Restrict Operators
•	Each SELECT contains a single WHERE

•	Numeric comparison 
<, >, =, <>, …
•	e.g., grade<80

•	Boolean operations 
–	e.g., Name = “John” AND Dept <> “HIST”
Using Microsoft Access

•	Create a database called M:\rides.mdb
–	File->New->Blank Database

•	Specify the fields (columns)
–	“Create a Table in Design View”

•	Fill in the records (rows)
–	 Double-click on the icon for the table
Creating Fields
•	Enter field name
–	Must be unique, but only within the same table

•	Select field type from a menu
–	Use date/time for times
–	Use text for phone numbers

•	Designate primary key (right mouse button)

•	Save the table
–	That’s when you get to assign a table name
Entering Data
•	Open the table
–	Double-click on the icon

•	Enter new data in the bottom row
–	A new (blank) bottom row will appear

•	Close the table
–	No need to “save” – data is stored automatically
Building Queries
•	Copy ride.mdb to your M:\ drive

•	“Create Query in Design View”
–	In “Queries”

•	Choose two tables, Flight and Company

•	Pick each field you need using the menus
–	Unclick “show” to not project
–	Enter a criterion to “restrict”

•	Save, exit, and reselect to run the query
Fun Facts about Queries
•	Joins are automatic if field names are same
–	Otherwise, drag a line between the fields

•	Sort order is easy to specify
–	Use the menu

•	Queries form the basis for reports
–	Reports give good control over layout
–	Use the report wizard - the formats are complex
Other Things to Know
•	Forms manage input better than raw tables
–	Invalid data can be identified when input
–	Graphics can be incorporated
Key Ideas
•	Databases are a good choice when you have
–	Lots of data
–	A problem that contains inherent relationships

•	Design before you implement
–	This is just another type of programming
–	The mythical person-month applies!

•	Join is the most important concept
–	Project and restrict just remove undesired stuff
 Discussion Point: 
Mythical Person-Month
•	Why is software development different from manufacturing car?

•	If it would take one person three months, why does it take four people SIX months?
Trading People and Months is Hard
•	Sequential constraints

•	Communication

•	Training
Estimating Completion Time
•	Rules of thumb
–	1/3 specification
–	1/6 coding
–	1/2 test planning, testing, and fixing!

•	Add time for coding to learn as you go, but don’t take time away from the other parts!
–	Reread the section on “gutless estimating” if you are tempted