CSC100
Databases
Marni Ferner
A Few Well-Known Examples
• Itunes & Ipods
• Amazon.com
• Ebay
• LL Bean
• Wal-Mart
Components of a Database
System
1-3
Components of a Database
System
1-4
Why not just use a spreadsheet?
Problems with Flat Tables:
Redundancy and Multiple Themes
Customer
Ariel Johnson
Robin Green
Charles Jackson
Ariel Johnson
Jeffery Pearson
Miguel Sears
Leah Kyle
Phone
CourseDate AmtPaid
206.555.1234
10/1/2006 $ 250.00
312.555.6689
9/15/2006 $ 350.00
306.555.1488
10/1/2006 $ 500.00
206.555.1234
3/15/2006 $ 350.00
212.555.8878
10/1/2006 $ 500.00
770.555.3289
9/15/2006 $ 350.00
444.555.3833 11/15/2006 $ 250.00
Course
Advanced Pastels
Beginning Oils
Advanced Pastels
Intermediate Pastels
Advanced Pastels
Begining Oils
Advanced Pastels
Fee
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
Modification Issues
• Redundancy and multiple themes create
modification problems
– Deletion problems
– Update problems
– Insertion problems
Potential Problems: Deletion
What if Ariel Johnson is deleted?
Customer
Ariel Johnson
Robin Green
Charles Jackson
Ariel Johnson
Jeffery Pearson
Miguel Sears
Leah Kyle
Phone
CourseDate AmtPaid
206.555.1234
10/1/2006 $ 250.00
312.555.6689
9/15/2006 $ 350.00
306.555.1488
10/1/2006 $ 500.00
206.555.1234
3/15/2006 $ 350.00
212.555.8878
10/1/2006 $ 500.00
770.555.3289
9/15/2006 $ 350.00
444.555.3833
11/15/2006 $ 250.00
Course
Advanced Pastels
Beginning Oils
Advanced Pastels
Intermediate Pastels
Advanced Pastels
Beginning Oils
Advanced Pastels
Fee
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
Potential Problems: Deletion
Intermediate Pastels is gone
Customer
Robin Green
Charles Jackson
Jeffery Pearson
Miguel Sears
Leah Kyle
Linda Myers
Phone
312.555.6689
306.555.1488
212.555.8878
770.555.3289
444.555.3833
509.555.3303
CourseDate
9/15/2006
10/1/2006
10/1/2006
9/15/2006
11/15/2006
10/15/2006
AmtPaid
$ 350.00
$ 500.00
$ 500.00
$ 350.00
$ 250.00
$
-
Course
Beginning Oils
Advanced Pastels
Advanced Pastels
Beginning Oils
Advanced Pastels
Beginning Oils
Fee
$
$
$
$
$
$
350.00
500.00
500.00
350.00
500.00
350.00
Potential Problems: Updates
What if the fee for Advanced Pastels changes?
Customer
Ariel Johnson
Robin Green
Charles Jackson
Ariel Johnson
Jeffery Pearson
Miguel Sears
Leah Kyle
Phone
206.555.1234
312.555.6689
306.555.1488
206.555.1234
212.555.8878
770.555.3289
444.555.3833
CourseDate
10/1/2006
9/15/2006
10/1/2006
3/15/2006
10/1/2006
9/15/2006
11/15/2006
AmtPaid
$ 250.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 250.00
Course
Advanced Pastels
Beginning Oils
Advanced Pastels
Intermediate Pastels
Advanced Pastels
Beginning Oils
Advanced Pastels
Fee
$
$
$
$
$
$
$
500.00
350.00
500.00
350.00
500.00
350.00
500.00
Potential Problems: Updates
Does it change for all offerings?
Is the 11/15/2006 date an error or another offering?
Did we make ALL of the required changes?
Customer
Ariel Johnson
Robin Green
Charles Jackson
Ariel Johnson
Jeffery Pearson
Miguel Sears
Leah Kyle
Phone
206.555.1234
312.555.6689
306.555.1488
206.555.1234
212.555.8878
770.555.3289
444.555.3833
CourseDate
10/1/2006
9/15/2006
10/1/2006
3/15/2006
10/1/2006
9/15/2006
11/15/2006
AmtPaid
$ 250.00
$ 350.00
$ 500.00
$ 350.00
$ 500.00
$ 350.00
$ 250.00
Course
Advanced Pastels
Beginning Oils
Advanced Pastels
Intermediate Pastels
Advanced Pastels
Beginning Oils
Advanced Pastels
Fee
$
$
$
$
$
$
$
400.00
350.00
400.00
350.00
500.00
350.00
400.00
Potential Problems: Insertion
How do we add a new course?
Lots of empty space
Ariel Johnson
Robin Green
Charles Jackson
Ariel Johnson
Jeffery Pearson
Miguel Sears
Leah Kyle
Linda Myers
206.555.1234
312.555.6689
306.555.1488
206.555.1234
212.555.8878
770.555.3289
444.555.3833
509.555.3303
10/1/2006
9/15/2006
10/1/2006
3/15/2006
10/1/2006
9/15/2006
11/15/2006
10/15/2006
$
$
$
$
$
$
$
$
250.00
350.00
500.00
350.00
500.00
350.00
250.00
-
Advanced Pastels
Beginning Oils
Advanced Pastels
Intermediate Pastels
Advanced Pastels
Beginning Oils
Advanced Pastels
Beginning Oils
Beginning Pastels
$
$
$
$
$
$
$
$
$
500.00
350.00
500.00
350.00
500.00
350.00
500.00
350.00
250.00
A Better Solution
Relational Databases
• A relational database stores information in
tables. Each informational topic is stored in its
own table
• In essence, a relational database will break-up
a list into several parts. One part for each
theme in the list
• An Enrollment List would be divided into a
CUSTOMER Table, a CLASSES Table, and an
ENROLLMENT Table
But, how do we re-create the
original data?
Structured Query Language
(SQL)
• Structured Query Language (SQL) is an
international standard for creating,
processing and querying database and
their tables
• Most database applications use SQL to
retrieve, format, report, insert, delete,
and/or modify data for users
SQL Example
SELECT CUSTOMER.CustomerName,
CUSTOMER.Phone,COURSE.CourseDate,
ENROLLMENT.AmountPaid,
COURSE.Course, COURSE.Fee
FROM
CUSTOMER, ENROLLMENT, COURSE
WHERE CUSTOMER.CustomerNumber
= ENROLLMENT.CustomerNumber
AND
COURSE.CourseNumber
= ENROLLMENT.CourseNumber;
A Relational Database Solves the
Problems of Lists and adds
THE POWER OF QUERIES
Database Management
System Software
Query Tools
Database Management
System Software
Query Tools
– Query tools help users narrow down information
needed to be searched.
Database Management
System Software
Query Tools
– Query tools help users narrow down information
needed to be searched.
– A query allows users to ask questions designed to
retrieve information.
Database Management
System Software
Query Tools
– Query tools help users narrow down information
needed to be searched.
– A query allows users to ask questions designed to
retrieve information.
– A select command asks a database to return
records that match specific criteria.
Database Management
System Software
Query Tools
– Query tools help users narrow down information
needed to be searched.
– A query allows users to ask questions designed to
retrieve information.
– A select command asks a database to return
records that match specific criteria.
– Joining matches data from fields in various
tables.
Database Management
System Software
Structured Query Language (SQL)
This SQL statement could search for top students in a college,
with “top” defined as those students with a grade point
average (GPA) above 3.49.
Database Basics
What is a database and how does it organize
information?
Database Basics
What is a database and how does it organize
information?
A database is a computerized system for storing
information in an organized manner so that it can be
searched for and retrieved when needed. Information
is stored in the form of fields, records, and files.
Database Basics
What is a database and how does it organize
information?
A database is a computerized system for storing
information in an organized manner so that it can be
searched for and retrieved when needed. Information
is stored in the form of fields, records, and files.
Database Basics
What is a database and how does it organize
information?
A database is a computerized system for storing
information in an organized manner so that it can be
searched for and retrieved when needed. Information
is stored in the form of fields, records, and files.
Unlike a list or spreadsheet, a database may store
information that is more complicated than a
simple list.
Database Basics
Levels of Data within a Database
Database Basics
Levels of Data within a Database
– Entity – a person, place, thing, or event
Database Basics
Levels of Data within a Database
– Entity – a person, place, thing, or event
– Field – a single value, such as a name, address, or
dollar amount
• data type: usually numeric or text (numbers and text)
• name: assigned by person developing the database
• size: number of characters that can be entered
Database Basics
Levels of Data within a Database
– Entity – a person, place, thing, or event
– Field – a single value, such as a name, address, or
dollar amount
• data type: usually numbers, text, dates, but can also be
other files (BLOBs), URL’s, etc.
• name: assigned by person developing the database
• size: number of characters that can be entered
– Record – related fields describing an event or
situation
Database Basics
Levels of Data within a Database
– Entity – a person, place, thing, or event
– Field – a single value, such as a name, address, or
dollar amount
• data type: usually numeric or text (numbers and text)
• name: assigned by person developing the database
• size: number of characters that can be entered
– Record – related fields describing an event or
situation
– File – collection of records of the same type
Database
Basics
Traditional
database
organizes data in
a hierarchy.
A field is the
smallest element.
Fields are within
a record. Records
are organized in
a data file.
Database is a
collection of
data files.
Sounds like
More Work, Not Less
• A relational database is more complicated than
a list
• However, a relational database minimizes data
redundancy, preserves complex relationships
among topics, and allows for partial data
• Furthermore, a relational database provides a
solid foundation for user forms and reports and
allows the data to be examined in a myriad of
different ways.
Current and Future Applications
THE WEB
Sponges of the Caribbean
Current and Future Applications
Data Warehousing
and Data Mining
Wal-Mart
“When the world's largest retailer struggles with
a database issue, the numbers can be a bit
daunting. It's data warehouse, for example, is
larger than 4 Petabytes. That's more than 4,096
TBytes, give or take a few million bits. The chain
has more than 6,000 stores, with some having
almost a half-million SKUs each. You think your
Excel spreadsheets are bad? Wal-Mart's database
tables have literally 100 billion rows. The
retailer's POS systems have to ring up some 276
million items—a day.”
Source: StorefrontBacktalk.com
Current and Future Applications
Biometrics
Ethics and Privacy Issues
Discussion Topic:
Sharing Criminal Data
and Megan’s Law
California was the first state to employ a controversial database. Based on Megan’s
Law – the statute named for a seven-year-old girl who was violated and killed by a
paroled felon – the database listed the names and addresses of people convicted of
crimes against children. Today, all states employ similar databases and are required
to share the information with a national database. Most states now allow you to use
the databases to find out if these felons live near you. In some communities, when an
offender moves in, the police inform the local school system, which in turn sends
parents a notification that includes a history, address, and picture of the wrongdoer.
Some states share information with each other regarding almost all criminals, and
some allow citizens to search for these offenders by name. Touted as a valuable tool in
crime prevention, some feel that publishing this information makes it impossible for
an offender to lead a normal life and can result in vigilantism – one paroled
lawbreaker’s car was firebombed only days after his name was released. Should a
database of people paroled or released after the commission of sex crimes be made
public? Why or why not? What about those who have committed other types of
crimes? What about those awaiting trial? Who should have access to the database,
and why? Should there be a cost to accessing this type of data?
•
http://sexoffender.ncdoj.gov/search.aspx
•
http://www1.aoc.state.nc.us/www/calendars/DWIQuery.html
The End
Descargar

Computers: Understanding Technology, 3e