Transcript Document

Chapter 12.3+
Information Systems
Database Management
Database Management Systems
Database: A structured set of data.
Database Management System: (DBMS) A
combination of software and data, including:



Physical database: a collection of files that
contain the data.
Database engine: software that supports access
to and modification of the database contents.
Database schema: a specification of the logical
structure of the data stored in the database.
2
Database Management Systems
Specialized database languages allow the user
to:



specify the structure of data;
add, modify, and delete data;
query the database to retrieve specific stored
data.
3
Database Management Systems
Figure 12.6 The elements of a database management system
4
Databases
Databases are a recent development in the
management of large amounts of data.
As paper file systems were “computerized”
each application was implemented separately
with its own data set.
These systems were riddled with both corrupt
data and redundant data, none of which
could be shared.
5
Databases
The integration of separate systems into one
database resolved these issues, but
introduced new ones.
With all data shared, control of access to the
data becomes a major concern.
Payroll doesn’t need to see your grades.
6
Database Management Systems
A schema is a description of the entire
database structure used by the database
software to maintain the database.
A subschema is a description of only that part
of the database that is particular to a user’s
needs.
7
Database Management Systems
A layered approach hides the complexities of
database implementation.



User sees data in terms of the application.
The application “sees” data in terms of the
database model.
The DBMS “sees” data as it is organized.
8
Database Management Systems
Advantages of the layered approach include:




Simplification of the design process.
Better control of access.
Data Independence.
Applications can be written in terms of simple,
conceptual views of the data
– the database model.
9
Database Models
A database model is a conceptual view of how
to organize and manipulate data.
The most popular one is the Relational Model.
10
The Relational Model
In a relational DBMS, the data items - and the
relationships among them - are organized into
rectangular tables.
As with spreadsheets, these tables consist of
rows and columns.



Each table is called a relation.
The rows are called tuples.
The columns are called attributes.
11
The Relational Model
Of course, different authors adopt different
terms. There is a commonly used, alternate set
of names:

Relations are also called tables.

A tuple can be referred to as a record,
and in this terminology

a record is a collection of related fields.
12
A Database Table
Figure 12.7 Part of a database table, made up of records and fields
13
A Database Table
We can express the schema for this database
table as follows:
Movie (MovieId:key, Title, Genre, Rating)
14
Another Database Table
A partial CUSTOMER table.
CustomerId Name
Address
CreditCardNumber
101
Dennis Cook
789 Main
993726762357
102
Doug Nickle
456 Second
632783087764
103
Randy Wolf
12 Elm
854072657547
104
Amy Stevens Yellow Brick Road
184585788722
105
Susan Klaton 654 Lois Lane
537212603203
106
Dale Evans
987 Broadway
053118262075
107
Chris Stein
1010 Abbey Road
862175961142
15
Another Database Table
We can express the schema for this table as:
Customer (CustomerId:key, Name, Address,
CreditCardNumber)
16
Relationships
A table can represent a collection of relationships between
objects. The RENTS table relates Customers to the Movies
they’ve rented by their respective Ids.
CustomerId
MovieId
DateRented
DateDue
101
102
08/11/2010
15/11/2010
103
104
07/11/2010
10/11/2010
105
1033
04/11/2010
13/11/2010
102
101
09/11/2010
11/11/2010
101
104
04/11/2010
14/11/2010
104
107
05/11/2010
10/11/2010
103
102
05/11/2010
11/11/2010
107
7442
06/11/2010
13/11/2010
17
Relationships
We can also express the schema for a
relationship:
Rents (CustomerId, MovieId, DateRented,
DateDue)
Note the absence of a key field.
18
Relational Operations
There are 3 fundamental operations that can be
used to manipulate the tables in a database:

SELECT


PROJECT


Extracts rows (tuples) from a table (relation)
Extracts columns (attributes) from a table (relation)
JOIN

Combines 2 tables (relations) into 1
19
Relational Operations
The result of any relational operation is a new
relation. We can express these operations with a
simple syntax.
NEW ← SELECT from MOVIE where RATING = “PG”
This operation creates a new relation (named NEW) by
extracting all rows from the MOVIE table that have a
RATING of PG.
20
SELECT
MovieId
Title
Genre
Rating
102
Back to the Future
comedy adventure
PG
104
Field of Dreams
fantasy drama
PG
The NEW relation.
21
Relational Operations
The same syntax can be used for the other operations.
PGmovies ← PROJECT MovieId, Title from NEW
This operation creates a new relation (named PGmovies)
that extracts 2 attributes from the NEW relation.
22
PROJECT
MovieId
Title
102
Back to the Future
104
Field of Dreams
The PGmovies relation.
23
Relational Operations
A JOIN creates a new relation by combining 2 relations
according to some criterion.
TEMP1 ← JOIN CUSTOMER and RENTS
where CUSTOMER.CustomerId = RENTS.CustomerId
24
JOIN
CustomerId
Name
Address
CreditCardNumber
MovieId
DateRented
DateDue
101
Dennis Cook
789 Main
993726762357
102
08/11/2010
15/11/2010
101
Dennis Cook
789 Main
993726762357
104
04/11/2010
14/11/2010
102
Doug Nickle
456 Second
632783087764
101
09/11/2010
11/11/2010
103
Randy Wolf
12 Elm
854072657547
104
07/11/2010
10/11/2010
103
Randy Wolf
12 Elm
854072657547
102
05/11/2010
11/11/2010
104
Amy Stevens
Yellow Brick Road
184585788722
107
05/11/2010
10/11/2010
105
Susan Klaton
654 Lois Lane
537212603203
1033
04/11/2010
13/11/2010
107
Chris Stein
1010 Abbey Road
862175961142
7442
06/11/2010
13/11/2010
25
Relational Operations
The PROJECT operation can be used to remove the
attributes we don’t want…
RENTALS ← PROJECT Name, Address, MovieId from TEMP1
26
Relational Operations
The RENTALS relation.
Name
Address
MovieId
Dennis Cook
789 Main
102
Dennis Cook
789 Main
104
Doug Nickle
456 Second
101
Randy Wolf
12 Elm
104
Randy Wolf
12 Elm
102
Amy Stevens
Yellow Brick Road
107
Susan Klaton
654 Lois Lane
1033
Chris Stein
1010 Abbey Road
7442
27
Relational Operations
Now, JOINing RENTALS to PGmovies…
PGrenters ← JOIN RENTALS and PGmovies
where RENTALS.MovieId = PGmovies.MovieId
…creates a table of customers who have rented PG movies.
Name
Address
MovieId
Title
Dennis Cook
789 Main
102
Back to the Future
Dennis Cook
789 Main
104
Field of Dreams
Randy Wolf
12 Elm
104
Field of Dreams
Randy Wolf
12 Elm
102
Back to the Future
28
Structured Query Language
Structured Query Language (SQL)
A comprehensive database language for
managing relational databases.
29
Queries in SQL
select attribute-list from table-list where condition
select Title from MOVIE where Rating = 'PG'
select Name, Address from CUSTOMER
select * from MOVIE where Genre like '%action%'
select * from MOVIE where Rating = 'R' order by Title
30
Modifying Database Content
insert into CUSTOMER values (9876, 'John Smith',
'602 Greenbriar Court', '2938 3212 3402 0299')
update MOVIE set Genre = 'thriller drama' where
title = 'Unbreakable‘
delete from MOVIE where Rating = 'R'
31
Database Design
Entity-relationship (ER) modeling

A popular technique for designing relational
databases.
ER Diagram


Chief tool used for ER modeling.
Captures the important record types, attributes,
and relationships in a graphical form.
32
Database Design

These designations show the cardinality
constraint of the relationship
Figure 12.10 An ER diagram for the movie rental database
33