Programming Logic and Design Fifth Edition, Comprehensive

1
Programming Logic and Design Fifth Edition,
Comprehensive

• Chapter 14
• Using Relational Databases

2
Objectives

• Understand relational database fundamentals
• Create databases and table descriptions
• Be able to identify primary keys
• Understand database structure notation
• Understand the principles of adding, deleting,
  updating, and sorting records within a table

3
Objectives (continued)

• Create queries
• Understand relationships between tables
• Recognize poor table design
• Understand anomalies, normal forms, and the
  normalization process
• Understand database performance and security
  issues

4
Understanding Relational Database Fundamentals

• Data hierarchy
• Smallest unit of data is the character
• Characters form fields
• Fields form records
• Records form files
• Database
• Group of files needed to support an organization
• Files in a database are called tables

5
Understanding Relational Database Fundamentals
(continued)

• Data in tables can be arranged in rows and
  columns
• Each column represents a field
• Each row represents a record

Figure 14-1 A telephone book table
6
Understanding Relational Database Fundamentals
(continued)

• Primary key (or key)
• Uniquely identifies a record
• May be composed of one or multiple columns
• Compound key constructed from multiple columns
• Also known as composite key

7
Understanding Relational Database Fundamentals
(continued)

• Database management software allows you to
• Create table descriptions
• Identify keys
• Add, delete, and update records within a table
• Sort records by different fields
• Write questions to select specific records for
  viewing
• Write questions to combine information from
  multiple tables
• Relational database tables with related columns
• Create reports
• Secure the data

8
Creating Databases and Table Descriptions

• Creating a database requires planning and
  analysis
• What data to store
• How to divide the data between tables
• How the tables will interrelate
• Designing a database table
• Required columns
• And how they are named
• Type of data in each column

9
Creating Databases and Table Descriptions
(continued)
Figure 14-2 Customer table description
10
Identifying Primary Keys

• Identify a column or combination of columns to be
  the primary key
• Values of primary keys must be unique, such as
• Student ID number
• Inventory part number
• Social Security number

11
Identifying Primary Keys (continued)

• Primary key is used for
• Ensuring multiple records with the same values
  cannot be added
• Sorting the records in primary key order
• Creating relationships between tables
• Normalizing a database
• Multicolumn keys ensure unique values

12
Identifying Primary Keys (continued)
Figure 14-3 Table containing residence hall
student records
13
Understanding Database Structure Notation

• Table description
• Table name with column names in parentheses
• Underline the primary key field(s)
• tblStudents (idNumber, lastName,
  firstName, gradePointAverage)
• Does not provide information about data types or
  range limits
• Does provide overview of table structure

14
Adding, Deleting, Updating, and Sorting Records
Within Tables

• May have to set up table to prevent duplicate
  data in specific fields
• Prevent data entry outside of specified bounds in
  other fields
• Depending on database software
• Data typed into rows representing each record
• Columns represent each field
• Create onscreen forms to make data entry more
  user-friendly
• Some software does not allow any blank fields
• Deleting and modifying records is relatively easy

15
Sorting the Records in a Table

• Can sort a table based on any column
• After sorting
• Records can be grouped by specific values or
  ranges
• Aggregate values can be calculated (counts, sums,
  averages, etc.)
• Database software provides the means to create
  displays in various formats

16
Creating Queries

• Query question that causes database software to
  extract appropriate records from the table
• Query by example fill in blanks to create
  queries
• Structured Query Language (SQL) most common
  language used to query a database

17
Creating Queries (continued)

• SELECT-FROM-WHERE is the basic form of a query
• Selects columns to view
• From a specific table
• Where one or more conditions are met
• Wildcard symbol specifies any or all
• Compound conditions using AND, OR, or NOT

18
Creating Queries (continued)
Figure 14-4 The tblInventory table
19
Creating Queries (continued)
Figure 14-5 Sample SQL statements and
explanations
20
Understanding Relationships Between Tables

• Relationship connection between two tables
• Relational database database containing
  relationships
• Join operation (or join) connecting two tables
  based on values in a common column
• Query returns data taken from each joined table
• Three types of relationships
• One-to-many
• Many-to-many
• One-to-one

21
Understanding Relationships Between Tables
(continued)
Figure 14-6 Sample customers and orders
22
Understanding One-to-Many Relationships

• One-to-many relationship
• Row in one table related to one or more rows in
  another table
• Most common type of table relationship
• Relationship based on one or more columns
• One side is the base table
• Primary key is used for the join
• Many side is the related table
• May be a non-key attribute
• Foreign key field in one table which is primary
  key in another table

23
Understanding One-to-Many Relationships
(continued)
Figure 14-7 Sample items and categories a
one-to-many relationship
24
Understanding Many-to-Many Relationships

• Many-to-many relationship
• Multiple rows in each table can correspond to
  multiple rows in the other table
• Additional table contains pairs of primary keys
  from each table
• New table contains compound primary key
• These pairs form unique keys in the new table

25
Understanding Many-to-Many Relationships
(continued)
Figure 14-8 Sample items, categories, and item
categories a many-to-many relationship
26
Understanding One-to-One Relationships

• One-to-one relationship
• Row in one table corresponds to exactly one row
  in another table
• Least frequently encountered relationship
• Tables could be combined into a single table
• Often keep the tables separate for security
  purposes

27
Understanding One-to-One Relationships (continued)
Figure 14-9 Employees and salaries tables a
one-to-one relationship
28
Recognizing Poor Table Design

• Often table design is inadequate to support needs
  of the application
• Structure may be cumbersome
• Prone to data errors
• Take time to ensure well-designed table
  structures initially

29
Recognizing Poor Table Design (continued)
Figure 14-10 Students table before normalization
process
30
Understanding Anomalies, Normal Forms, and the
Normalization Process

• Normalization
• Designing a database structure to satisfy user
  needs
• Reduce duplication of data
• Data redundancy unnecessary repetition of data
• Anomaly irregularity in database design that
  causes problems
• Update anomalies
• Delete anomalies
• Insert anomalies

31
Understanding Anomalies, Normal Forms, and the
Normalization Process (continued)

• Update anomaly when updating data in one table,
  you must update the same data in another table
• Delete anomaly deleting a record causes other
  problems, such as loss of unrelated information
• Insert anomaly inability to add a new record due
  to lack of related data

32
Understanding Anomalies, Normal Forms, and the
Normalization Process (continued)

• Normalization removes redundancies and anomalies
• Three normal forms
• First normal form (or 1NF) eliminate repeating
  groups
• Second normal form (or 2NF) eliminate partial
  key dependencies
• Third normal form (3NF) eliminate transitive
  dependencies

33
First Normal Form

• Unnormalized table contains repeating groups
• Repeating group subset of rows in a table all
  depend on the same key
• Table in 1NF contains no repeating groups of data
• Primary key attributes are defined
• Atomic attributes columns as small as possible
• Containing undividable pieces of data
• In 1NF, all values for intersecting row and
  column must be atomic

34
First Normal Form (continued)
Figure 14-11 Students table in 1NF
35
Second Normal Form

• Partial key dependencies column depends on only
  part of the key
• For 2NF
• Database must already be in 1NF
• All non-key fields must be dependent on the
  entire primary key
• Eliminate partial key dependencies by creating
  multiple tables

36
Second Normal Form (continued)
Figure 14-12 Students table in 2NF
37
Second Normal Form (continued)

• Improvements over 1NF
• Eliminate update anomalies
• Eliminating redundancies reduces anomalies
• Eliminate insert anomalies
• Eliminate delete anomalies
• When breaking a table into multiple tables,
  consider relationships among tables

38
Third Normal Form

• Transitive dependency value of a non-key
  attribute determines value of another non-key
  attribute
• For 3NF
• Database must already be in 2NF
• No transitive dependencies
• Remove attributes that are functionally dependent
  on the attribute that causes the transitive
  dependency

39
Third Normal Form (continued)
Figure 14-13 The complete Students database
40
Third Normal Form (continued)

• All redundancies and anomalies are removed
• Determinant is allowed in 3NF if it is a
  candidate key
• Normalization summary
• 1NF no repeating groups
• 2NF 1NF plus no partial key dependencies
• 3NF 2NF plus no transitive dependencies

41
Database Performance and Security Issues

• A companys data must be protected
• Data security includes
• Providing data integrity
• Recovering lost data
• Avoiding concurrent update problems
• Providing authentication and permissions
• Providing encryption

42
Providing Data Integrity

• Data integrity
• Data is accurate and consistent
• Database software must enforce data integrity

43
Recovering Lost Data

• Data loss caused by
• User mistakes
• Hackers or other malicious users
• Hardware problems
• Fire, flood, or other natural disasters
• Recovery return the database to a correct form
  that existed before the problem occurred
• Make a backup copy of the database
• Record of all transactions to recover a database

44
Avoiding Concurrent Update Problems

• Concurrent update problem
• Two users make changes to the same record
• One update will be lost
• Lock mechanism to prevent changes to a record
  for some period of time
• Solving concurrent update problem
• Use record-level locking
• Make transactions offline, and process as a batch

45
Providing Authentication and Permissions

• Database software must determine that a user is
  legitimate and is authorized to use the database
• Authentication techniques include
• Storing and verifying passwords
• Using biometric data to identify users
• Permissions settings that determine what actions
  a user is allowed to perform
• Authentication determines what permissions a user
  has

46
Providing Encryption

• Encryption coding data into a format that humans
  cannot read
• Prevents use of the data by unauthorized users

47
Summary

• Database collection of tables containing an
  organization's data
• Primary key value that uniquely identifies a
  record
• Database management software allows you to add,
  delete, and update records in the database

48
Summary (continued)

• Query question that selects data from database
• Database creation requires planning and analysis
• Primary key can consist of one or multiple
  columns
• Most data is in a constant state of change

49
Summary (continued)

• Functions
• Can sort a table based on any column
• Can do aggregate calculations on data
• Normalization designing a database to meet the
  needs, yet avoiding redundancies and anomalies
• Three forms of normalization are commonly used

50
Summary (continued)

• Database may be one of a companys most important
  assets, so it must be secured
• Security issues
• Data integrity
• Recovery
• Avoiding concurrent update problems
• Authentication and permissions
• Providing encryption