www.rferro.com
Download
Report
Transcript www.rferro.com
PROGRAMMING LOGIC AND
DESIGN
SIXTH EDITION
Chapter 14
Using Relational Databases
OBJECTIVES
Programming Logic & Design, Sixth Edition
In this chapter, you will learn about:
Relational database fundamentals
Creating databases and table descriptions
Primary keys
Database structure notation
Adding, deleting, updating, and sorting records
within a table
2
OBJECTIVES (CONTINUED)
Creating queries
Relationships between tables
Poor table design
Anomalies, normal forms, and normalization
Database performance and security issues
Programming Logic & Design, Sixth Edition
3
UNDERSTANDING RELATIONAL DATABASE
FUNDAMENTALS
Data hierarchy
Characters
Fields
Records
Files
Programming Logic & Design, Sixth Edition
Database
Holds files organization needs to support operations
Called tables
4
UNDERSTANDING RELATIONAL DATABASE
FUNDAMENTALS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-1 A telephone book table
5
UNDERSTANDING RELATIONAL DATABASE
FUNDAMENTALS (CONTINUED)
Primary key
Uniquely identifies a record
Often defined as a single table column
Can be compound or composite
Programming Logic & Design, Sixth Edition
Database management software functions
Create table descriptions
Identify keys
Add, delete, and update records within a table
6
UNDERSTANDING RELATIONAL DATABASE
FUNDAMENTALS (CONTINUED)
Arrange records within a table so they are sorted by
different fields
Write questions that combine information from
multiple tables
Create reports
Keep data secure
Programming Logic & Design, Sixth Edition
Relational database
A group of database tables from which you can make
these connections
7
CREATING DATABASES
DESCRIPTIONS
AND
TABLE
Planning and analysis
Create the database itself
Name it and indicate the physical location
Save a table
Provide a name that begins with the prefix “tbl”
Programming Logic & Design, Sixth Edition
tblCustomers
Design the table
Decide what columns your table needs and name
them
Provide a data type for each column
8
CREATING DATABASES AND TABLE
DESCRIPTIONS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-2 Customer table description
9
CREATING DATABASES AND TABLE
DESCRIPTIONS (CONTINUED)
Text columns
Numeric columns
Programming Logic & Design, Sixth Edition
Hold any type of characters—letters or digits
Hold numbers only
Other possible column types
Numeric subtypes
Boolean
Currency
Can add descriptions
10
IDENTIFYING PRIMARY KEYS
Primary key
Programming Logic & Design, Sixth Edition
Column that makes each record different from all
others
Examples
customerID
Student ID number
Important for several reasons
Should be immutable
11
IDENTIFYING PRIMARY KEYS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-3 Table containing residence hall student records
12
UNDERSTANDING DATABASE
STRUCTURE NOTATION
Shorthand way to describe a table
Table name followed by parentheses containing all
the field names
Primary key underlined
Programming Logic & Design, Sixth Edition
Example
tblStudents(idNumber, lastName,
firstName, gradePointAverage)
Provides a quick overview of the table’s structure
Does not provide information about data types or
range limits on values
13
ADDING, DELETING, UPDATING, AND
SORTING RECORDS WITHIN TABLES
Entering data
Requires time and accuracy
Method depends on database software
Programming Logic & Design, Sixth Edition
Deleting and modifying data
Keeping records up to date is vital
14
SORTING THE RECORDS IN A TABLE
Sort a table based on any column
Or on multiple columns
Programming Logic & Design, Sixth Edition
Group rows after sorting
Add subtotals
Create displays in the format that suits your
needs
15
CREATING QUERIES
View subsets of data from a table you have
created
Examine only those customers with an address in a
specific state
Limit the columns that you view
Programming Logic & Design, Sixth Edition
School administrator might only be interested in
looking at names and grade point averages
Query
Question using the syntax that the database software
can understand
16
CREATING QUERIES (CONTINUED)
Query by example
Create a query by filling in blanks
Programming Logic & Design, Sixth Edition
Write statements in Structured Query
Language, or SQL
SELECT-FROM-WHERE
Basic form of the SQL statement
Example
SELECT custId, lastName FROM tblCustomer WHERE
state = "WI"
17
CREATING QUERIES (CONTINUED)
Can use comparison operators
Wildcards
Programming Logic & Design, Sixth Edition
Examples
SELECT * from tblCustomer WHERE state = "WI“
SELECT * FROM tblCustomer
18
CREATING QUERIES (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-5 Sample SQL statements and explanations
19
UNDERSTANDING RELATIONSHIPS
BETWEEN TABLES
Most database applications require many related
tables
Relationship
Connecting two tables based on the values in a
common column
Virtual table
Connection between two tables
Join operation
Programming Logic & Design, Sixth Edition
Table that is displayed as the result of the query
Three types of relationships
20
UNDERSTANDING RELATIONSHIPS
BETWEEN TABLES (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-6 Sample customers and orders
21
UNDERSTANDING ONE-TO-MANY
RELATIONSHIPS
One row in a table can be related to many rows in
another table
Most common type of relationship between tables
Example
Programming Logic & Design, Sixth Edition
tblCustomers(customerNumber,
customerName)
tblOrders(orderNumber, customerNumber,
orderQuantity, orderItem, orderDate)
One row in the tblCustomers table can correspond
to, and be related to, many rows in the tblOrders
table
22
UNDERSTANDING ONE-TO-MANY
RELATIONSHIPS (CONTINUED)
Base table: tblCustomers
Related table: tblOrders
customerNumber attribute
Programming Logic & Design, Sixth Edition
Links the two tables together
Nonkey attribute
Foreign key
When a column that is not a key in a table contains
an attribute that is a key in a related table
23
UNDERSTANDING MANY-TO-MANY
RELATIONSHIPS
Another example of a one-to-many relationship
Programming Logic & Design, Sixth Edition
tblItems(itemNumber, itemName,
itemPurchaseDate, itemPurchasePrice,
itemCategoryId)
tblCategories(categoryId, categoryName,
categoryInsuredAmount)
24
UNDERSTANDING MANY-TO-MANY
RELATIONSHIPS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-7 Sample items and categories: a one-to-many relationship
25
UNDERSTANDING MANY-TO-MANY
RELATIONSHIPS (CONTINUED)
Many-to-many relationship
One specific row in the tblItems table can link
to many rows in the tblCategories table
Programming Logic & Design, Sixth Edition
Multiple rows in each table can correspond to
multiple rows in the other
Cannot continue to maintain the foreign key
itemCategoryId in the tblItems table
Simplest way to support a many-to-many
relationship
Remove the itemCategoryId attribute
26
UNDERSTANDING MANY-TO-MANY
RELATIONSHIPS (CONTINUED)
Example
Programming Logic & Design, Sixth Edition
tblItems(itemNumber, itemName,
itemPurchaseDate, itemPurchasePrice)
tblCategories(categoryId, categoryName,
categoryInsuredAmount)
New table
tblItemsCategories(itemNumber,
categoryId)
27
UNDERSTANDING MANY-TO-MANY
RELATIONSHIPS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-8 Sample items, categories, and item categories: a
many-to-many relationship
28
UNDERSTANDING ONE-TO-ONE
RELATIONSHIPS
Row in one table corresponds to exactly one row
in another table
Least frequently encountered
Common reason you create a one-to-one
relationship is security
Programming Logic & Design, Sixth Edition
29
UNDERSTANDING ONE-TO-ONE
RELATIONSHIPS (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-9 Employees and salaries tables: a one-to-one relationship
30
RECOGNIZING POOR TABLE DESIGN
Need to know the following information
Programming Logic & Design, Sixth Edition
Students’ names
Students’ addresses
Students’ cities
Students’ states
Students’ zip codes
ID numbers for classes in which students are
enrolled
Titles for classes in which students are enrolled
Potential problems with simple table design
31
RECOGNIZING POOR TABLE DESIGN
(CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-10 Students table before normalization
32
UNDERSTANDING ANOMALIES, NORMAL
FORMS, AND NORMALIZATION
Normalization
Programming Logic & Design, Sixth Edition
Helps you reduce data redundancies and
anomalies
Types of anomalies
Update
Delete
Insert
33
UNDERSTANDING ANOMALIES, NORMAL
FORMS, AND NORMALIZATION (CONTINUED)
Three normal forms
First normal form 1NF
Second normal form 2NF
Third normal form 3NF
Each normal form is structurally better than the one
preceding
Programming Logic & Design, Sixth Edition
34
FIRST NORMAL FORM
Unnormalized
1NF
Programming Logic & Design, Sixth Edition
Table that contains repeating groups
Contains no repeating groups of data
Sample table
class and classTitle attributes repeat multiple
times for some of the students
Repeat the rows for each repeating group of data
Create combined key of studentId and class
35
FIRST NORMAL FORM (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-11 Students table in 1NF
36
FIRST NORMAL FORM (CONTINUED)
Atomic attributes
Programming Logic & Design, Sixth Edition
Small as possible, containing an undividable piece of
data
37
SECOND NORMAL FORM
Eliminate all partial key dependencies
No column should depend on only part of the key
Must be in 1NF
All nonkey attributes must be dependent on the
entire primary key
Create multiple tables
Programming Logic & Design, Sixth Edition
Each nonkey attribute of each table is dependent on
the entire primary key for the specific table within
which the attribute occurs
38
SECOND NORMAL FORM (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-12 Students table in 2NF
39
SECOND NORMAL FORM (CONTINUED)
When breaking up a table into multiple tables
Consider the type of relationship among the resulting
tables
Determine what type of relationship exists between
the two tables
Programming Logic & Design, Sixth Edition
40
THIRD NORMAL FORM
Table must be in 2NF, and it has no transitive
dependencies
Transitive dependency
Programming Logic & Design, Sixth Edition
Value of a nonkey attribute determines, or predicts,
the value of another nonkey attribute
Example: zip code determines city and state
Remove the attributes that are determined by, or
are functionally dependent on, the zip attribute
41
THIRD NORMAL FORM (CONTINUED)
Programming Logic & Design, Sixth Edition
Figure 14-13 The complete Students database
42
DATABASE PERFORMANCE
AND SECURITY ISSUES
Major issues
Programming Logic & Design, Sixth Edition
Providing data integrity
Recovering lost data
Avoiding concurrent update problems
Providing authentication and permissions
Providing encryption
43
PROVIDING DATA INTEGRITY
Data integrity
Programming Logic & Design, Sixth Edition
Set of rules that makes the data accurate and
consistent
Can enforce integrity between tables
44
RECOVERING LOST DATA
Organization’s data can be destroyed in many
ways
Recovery
Programming Logic & Design, Sixth Edition
Process of returning the database to a correct form
that existed before an error occurred
Periodically make a backup copy of a database
and keep a record of every transaction
45
AVOIDING CONCURRENT UPDATE
PROBLEMS
Concurrent update
Lock
Programming Logic & Design, Sixth Edition
Problem occurs when two database users need to
modify the same record at the same time
Mechanism that prevents changes to a database for a
period of time
Do not allow users to update the original
database at all
Store transactions and then later apply to the
database all at once, or in a batch
46
PROVIDING AUTHENTICATION AND
PERMISSIONS
Authentication techniques include:
Storing and verifying passwords
Using physical characteristics
Programming Logic & Design, Sixth Edition
Permissions assigned
Indicate which parts of the database the user can
view, modify, or delete
47
PROVIDING ENCRYPTION
Encryption
Programming Logic & Design, Sixth Edition
Process of coding data into a format that human
beings cannot read
Only authorized users see the data in a readable
format
48
SUMMARY
Database holds a group of files that an
organization needs to support its applications
Create tables
Database operations
Identify primary key
Sort, add, edit, delete, query
Table relationships
One-to-many, many-to-many, one-to-one
Normalization
Database issues
Programming Logic & Design, Sixth Edition
49