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