Transcript Chapter 15

Chapter 15
A Table
with a View:
Database Queries
The Physical Database
• Redundancy is Bad, Very Very
Very Bad
– Never duplicate information in
database table
• To avoid inconsistency among copies
– We might change the information in one place,
and forget to change it in another
• Inconsistent data is known as garbage
– Worse than having no data at all
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-2
The Physical Database (cont'd)
• Keep Only One Copy of Information
– Avoiding duplication promotes internal
consistency, but does not ensure that the
information is correct
• Database information may be needed in
several places
– Best to keep a master list and allow access
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-3
The Physical Database (cont'd)
• Keep a Separate Table and a Key
– Rather than repeating information, keep
a separate table keyed with a unique
identifier (foreign key)
– When information is needed, it can be
looked up using the foreign key
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-4
The Database Schema
• The metadata of a database's tables is
called its schema or scheme
– Structure and design
• Imagine a college having two tables
defined in its schema, Student and
Home_Base:
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-5
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-6
Connecting Database Tables
by Relationship
• The two tables are separate,
but not independent
• The common Student_ID field connects them
– There is a relationship between the two entities
• Correspondence between rows
• Relationships are part of the metadata
• This is a two-way relationship (we can find the address
for any student, or the student for any address)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-7
Reconstruction Using Join
• The relationship between the two tables
allows us to construct a single table
(Master_List) containing the combined
information from both tables
– Use the natural join operation from
Chapter 14
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-8
Designing a Database Schema
• Suppose the Dean's office and
the sports center also need
address information
– Define tables without using address,
but with Student_ID as primary key
– Each new table has one-to-one
relationship with Home_Base table, and
with Student table (so the Dean or sports
center could look up the students' names)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-9
Physical versus Logical Database
• From our set of four tables, we can
create other tables customized to different
campus units
– Logical database — does not physically exist
• Created fresh every time it is needed, using current
values in the physical database
• Logical databases contain duplicate information, so we
don't want to store them and create redundancy
• Personalized logical databases (database views) allow
every user group to see the database in a different way
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-10
Logical Database: Creating Views
• Views are the logical tables constructed by
database operations from the physical table
• The operations that create views are called
database queries
– Natural join is a query
• Every named table of a database is either a
physical table, stored on the disk, or a logical
table created by a query
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-11
Creating a Dean's View
• Will include selected information from the
physical tables
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-12
Join Three Tables into One
• First step: Note that Dean's View
contains information from three tables
• Join operation associates the
information for each student
• You'd have all the information from all
three tables for each student
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-13
Trim the Table
• Retrieve only the columns the Dean
wants to see
• Join-then-trim strategy is a standard
approach
– A super table is formed by joining several
physical tables
– Then they are trimmed down to keep
only the information that is of interest to
the user
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-14
Creating a Sport's Center View
• Join Sports Center's view with its table and
the administration table
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-15
A Query Language: SQL
• SQL (Structured Query Language):
– Widely used standard language
– Provides specific query structure for techniques
like join-then-trim
– SQL varies (dialects) by vendor, but simple
queries are roughly the same:
• SELECT
• FROM
• WHERE
List of fields
Table(s)
Constraints on the rows
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-16
SQL ON Clause
• Following the SELECT is the list
of fields
• the FROM has to Joins that have been
grouped together (which field they are
joined ON)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-17
SQL WHERE Clause
• WHERE clause would set a condition
(suppose the Dean only wanted to see
students whose GPA is greater
than 3.75)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-18
Entity Relationship Diagrams
• Creating new tables involves relationships
• The point of identifying relationships in a
database schema is to indicate how the
information is connected, and joins make
these connections
• If there are relationships, they are likely to be
applied when building the logical database
• Database administrators diagram the
relationships to make the structure clear
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-19
Entity Relationship Diagrams (cont'd)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-20
Entity Relationship Diagrams (cont'd)
• One-to-One Relationships
– Any row in the first entity is associated with at
most one row in the other entity
• Many-to-One Relationships
– Many of the rows of the first entity can be
associated with a single row in the second entity
• These types of relationships can de shown in
different ways in ER diagrams
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-21
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-22
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-23
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
15-24