Transcript Document

Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 1:
Introduction to Databases
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• List database types (e.g., flat file, relational), and
identify their uses and architectures
• Describe the types of database management
system (DBMS), and explain the benefits and
limitations of each DBMS type
• Analyze and select appropriate database
designs, and identify the solution that addresses
the application needs
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
What Is a Database?
• File-based databases
– Flat-file databases
• The evolution of databases
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Relational Databases and DBMSs
•
•
•
•
•
Relational databases
Structured Query Language
Database administrator
Advantages and disadvantages of DBMSs
Choosing the correct database type
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
The Origins of Relational Databases
• Codd first proposed the relational data
model in 1970
• System R
• Development of SQL
• INGRES
• IBM UK Scientific Center
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 List database types (e.g., flat file, relational),
and identify their uses and architectures
 Describe the types of database
management system (DBMS), and explain the
benefits and limitations of each DBMS type
 Analyze and select appropriate database
designs, and identify the solution that
addresses the application needs
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 2:
Relational Database Fundamentals
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Define common database architectures (e.g.,
single, multi-tier)
• Describe the function of Structured Query
Language (SQL), including language subsets
• Describe the function of Data Definition
Language (DDL)
• Describe the function of Data Manipulation
Language (DML)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Describe the function of Data Control Language
(DCL)
• Define essential database concepts and terms,
including relation, relation name, table, row,
column, value, relational algebra, data
modeling, data relationship
• Explain the concepts of attribute, degree, tuple
and cardinality
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Define entities, including strong entities, weak
entities
• List characteristics of relations (e.g., column
characteristics, row characteristics)
• Describe table types (e.g., base tables, virtual
tables)
• Distinguish between primary and foreign keys,
including null value, composite key
• Explain data models in relational databases
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Describe common database relationships (e.g.,
one to one, one to many, many to many) and
identify the notation for such relationships (e.g.,
1:n)
• Define relational integrity concepts, including
domain constraints, entity and referential
integrity views
• Explain the structure and purpose of a data
dictionary
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Multitier Database Architecture
• Two-tier client-server architecture
– Fat client
• Three-tier client-server architecture
– Thin client
• n-tier architecture
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Relational Model Terminology
• Relational data
structure
• Rows (tuples)
• Columns (attributes)
•
•
•
•
Domains
Degrees
Cardinality
Normalization
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Using Tables to Represent Data
•
•
•
•
•
Entities
Characteristics of relations
Types of tables
Primary keys
Foreign keys
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Data Models
• Components
– Structural information
– Manipulative information
– Integrity information
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Entities and Data Relationships
• Entity-relationship (ER) modeling
• Strong entities
• Weak entities
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Relational Integrity
•
•
•
•
Domain constraints
Entity integrity
Referential integrity
Views
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Languages
•
•
•
•
Data Definition Language
Data Manipulation Language
Data Control Language
Data dictionaries
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Define common database architectures (e.g.,
single, multi-tier)
 Describe the function of Structured Query
Language (SQL), including language subsets
 Describe the function of Data Definition
Language (DDL)
 Describe the function of Data Manipulation
Language (DML)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Describe the function of Data Control Language
(DCL)
 Define essential database concepts and terms,
including relation, relation name, table, row,
column, value, relational algebra, data
modeling, data relationship
 Explain the concepts of attribute, degree, tuple
and cardinality
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Define entities, including strong entities, weak
entities
 List characteristics of relations (e.g., column
characteristics, row characteristics)
 Describe table types (e.g., base tables, virtual
tables)
 Distinguish between primary and foreign keys,
including null value, composite key
 Explain data models in relational databases
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Describe common database relationships (e.g.,
one to one, one to many, many to many) and
identify the notation for such relationships (e.g.,
1:n)
 Define relational integrity concepts, including
domain constraints, entity and referential
integrity views
 Explain the structure and purpose of a data
dictionary
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 3:
Database Planning
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Describe the elements of a database planning
strategy (e.g., defining the scope of a database
application)
• List the necessary steps for creating a database
requirements document, including conducting
interviews, noting project constraints, identifying
the precise duties of the database, creating a
database design document
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Explain the importance of data modeling and
entity relationship diagrams during the planning
stage (e.g., define the concept of an entityrelationship [ER] diagram)
• Create a database prototype
• Describe the purpose of database application
testing, including white-box testing, black-box
testing
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• List the criteria for selecting a database
management system and an application
interface
• Describe database design steps, including
determining the proper steps in creating entities,
applying normalization, selecting a database
management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Design Life Cycle
• Create a database strategy
• Define database application
scope
• Create a database
requirements document
• Design the database
• Select a DBMS
• Design the database
application
• Create database
prototypes
• Test the database
application
• Implement the
database application
• Convert legacy data
• Maintain the
database
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Requirements Document
• Conducting interviews
• Requirements document information
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Selecting a DBMS
• Selection criteria
– Data definition functionality
– Physical criteria
– Access criteria
– Transactions
– Utilities
– Development tools
– Miscellaneous features
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Selecting an Application Interface
GUI considerations
– Descriptive page
titles
– Clear instructions
– Consistent grouping
of input fields
– Logically labeled
fields
– Consistent color use
– Properly sized data
entry fields
– Logical cursor
movement
– Error messages
– Clearly indicated
optional fields
– Completion message
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Describe the elements of a database planning
strategy (e.g., defining the scope of a database
application)
 List the necessary steps for creating a database
requirements document, including conducting
interviews, noting project constraints, identifying
the precise duties of the database, creating a
database design document
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Explain the importance of data modeling and
entity relationship diagrams during the planning
stage (e.g., define the concept of an entityrelationship [ER] diagram)
 Create a database prototype
 Describe the purpose of database application
testing, including white-box testing, black-box
testing
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 List the criteria for selecting a database
management system and an application
interface
 Describe database design steps, including
determining the proper steps in creating entities,
applying normalization, selecting a database
management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 4:
Overview of Database
Design Methodology
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• List the steps of the conceptual design phase
(e.g., identifying entities, attribute domains,
relationships)
• Describe how to identify entities
• List ways to identify attributes and attribute
domains for entities
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• List ways to identify candidate and primary
keys for entities
• Identify and determine data relationships
• Create an entity-relationship (ER) diagram
• Analyze an entity-relationship (ER) diagram or
model to determine relation types
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Define and describe domains
• Explain the results of poor database design,
and describe insertion, deletion and update
anomalies in databases
• Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Effects of Poor Database Design
• Insertion anomalies
• Deletion anomalies
• Update anomalies
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Design Phases
• Conceptual phase
• Logical phase
• Physical phase
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Conceptual Database Design
• Identifying entities
• Identifying attributes and attribute domains
for entities
• Identifying relationships
• Identifying candidate and primary keys for
entities
• Creating an entity-relationship (ER) diagram
• Reviewing the ER model by the user and
design team
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Entity-Relationship Models
•
•
•
•
Creating ER models
Defining domains
Common SQL data types
Determining data relationships
– Recursive relationships
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 List the steps of the conceptual design phase
(e.g., identifying entities, attribute domains,
relationships)
 Describe how to identify entities
 List ways to identify attributes and attribute
domains for entities
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 List ways to identify candidate and primary
keys for entities
 Identify and determine data relationships
 Create an entity-relationship (ER) diagram
 Analyze an entity-relationship (ER) diagram or
model to determine relation types
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Define and describe domains
 Explain the results of poor database design,
and describe insertion, deletion and update
anomalies in databases
 Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 5:
Normalization
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Distinguish between specific normal forms,
including first normal form (1NF), second
normal form (2NF), third normal form (3NF),
Boyce-Codd normal form (BCNF)
• Describe the normalization process
• Define and explain determinant,
decomposition and functional dependency
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
What Is Normalization?
• Normal forms
• First normal form
– First normal form anomalies
• Second normal form
– Second normal form anomalies
• Third normal form
• Boyce-Codd normal form
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Related Concepts
•
•
•
•
•
Decomposition
Atomic value
Partial functional dependency
Transitive dependency
Denormalization
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Distinguish between specific normal forms,
including first normal form (1NF), second
normal form (2NF), third normal form (3NF),
Boyce-Codd normal form (BCNF)
 Describe the normalization process
 Define and explain determinant,
decomposition and functional dependency
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 6:
Logical Database Design
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Explain the structure and purpose of a data
dictionary
• Identify logical database design issues
• List the steps of the logical database design
phase (e.g., creating a raw data model;
identifying and documenting entities, attributes,
relationships, domains, and candidate and
primary keys)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Identify the cardinality of data relationships
• Identify and resolve inexpedient data
relationships
• Create an integrated logical data model from a
conceptual data model
• Use Data Definition Language (DDL)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Validate a logical data model against user
operations and system requirements
• Define integrity constraints (e.g., necessary data,
domain constraints, entity integrity, referential
integrity)
• Create corporate and enterprise data models
based on user views of data
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Validate the enterprise data model
• Describe database design steps, including
determining the proper steps in creating entities,
applying normalization, selecting a database
management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Logical Database Design
• Logical data models
– One-to-one
– One-to-many
– Many-to-many
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Creating a Logical Data Model
Logical
Logical
datadata
model
model
creation
creation
Normalization
Normalization
Data model refinement
Data model refinement
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Using a Database Definition Language
• Data dictionaries
• Validating the logical data model
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Defining Integrity Constraints
•
•
•
•
•
Necessary data
Domain constraints
Entity integrity
Referential integrity
Enterprise constraints
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Creating an Enterprise Data Model
• User views
• Normalization
• Validating data operations
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Explain the structure and purpose of a data
dictionary
 Identify logical database design issues
 List the steps of the logical database design
phase (e.g., creating a raw data model;
identifying and documenting entities, attributes,
relationships, domains, and candidate and
primary keys)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Identify the cardinality of data relationships
 Identify and resolve inexpedient data
relationships
 Create an integrated logical data model from a
conceptual data model
 Use Data Definition Language (DDL)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Validate a logical data model against user
operations and system requirements
 Define integrity constraints (e.g., necessary data,
domain constraints, entity integrity, referential
integrity)
 Create corporate and enterprise data models
based on user views of data
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Validate the enterprise data model
 Describe database design steps, including
determining the proper steps in creating entities,
applying normalization, selecting a database
management system (DBMS)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 7:
Physical Database Design
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• List the elements of physical database design
• Determine physical database design issues
• Describe how to create base relations for a
target database management system
(DBMS) using Data Definition Language (DDL)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Identify and create enterprise constraints for
a target DBMS
• Define secondary indexes and determine
when to use them
• Create a base relation
• Identify referential constraints
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Explain denormalization and describe when it
is necessary
• Create user views (e.g., the CREATE VIEW SQL
statement)
• Design database access rules
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Use appropriate commands to create, insert
and retrieve data from a database table,
including the SELECT, CREATE TABLE and
CREATE VIEW statements
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Physical Database Design
• Creating base relations for a target DBMS
• Data Definition Language
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Creating Enterprise Constraints
• Determining referential constraints
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Using Secondary Indexes
• A secondary index is a mechanism that
creates an additional key for a relation
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Denormalization
• The process of reuniting relations that were
split during the normalization process to
improve performance
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Creating User Views
• Determine the appropriate data to be
included with each particular view
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Designing Database Access Rules
• Identifiers
• Privileges
– SELECT
– UPDATE
– DELETE
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 List the elements of physical database design
 Determine physical database design issues
 Describe how to create base relations for a
target database management system
(DBMS) using Data Definition Language (DDL)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Identify and create enterprise constraints for
a target DBMS
 Define secondary indexes and determine
when to use them
 Create a base relation
 Identify referential constraints
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Explain denormalization and describe when it
is necessary
 Create user views (e.g., the CREATE VIEW SQL
statement)
 Design database access rules
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Use appropriate commands to create, insert
and retrieve data from a database table,
including the SELECT, CREATE TABLE and
CREATE VIEW statements
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 8:
Structured Query Language
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Perform data manipulation
• Describe basic SQL syntax
• List data types used in SQL, including SMALLINT,
DECIMAL (p, q), CHAR(n) and TIMESTAMP
• Define the term schema in relation to databases
• Create a schema using the CREATE SCHEMA
statement
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Drop schemas from a database using the DROP
SCHEMA statement
• Use appropriate commands to create, insert and
retrieve data from a database table, including
using the SELECT, CREATE TABLE and CREATE
VIEW statements
• Use the INSERT command appropriately
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Use appropriate UPDATE and DELETE statements
to update or delete information from a
database, including working with tuples
• Use the DISTINCT keyword
• Compose predicates and other statements using
the WHERE clause
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Identify the use and structure of Boolean
operators
• Describe SQL special operators and ORDER BY
clauses (e.g., the IN, BETWEEN and LIKE
operators)
• Deploy the ORDER BY clause to order output by
column or group of columns
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Retrieve data from relations using the SELECT
statement
• Create statements using the GRANT keyword to
control user access to relations
• Use the REVOKE keyword to limit access
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
SQL Basics
• Subsets of SQL
• SQL features
• Data types
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Data Definition Language
• Creating a schema
• Altering and dropping relations and views
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Data Manipulation Language
•
•
•
•
•
•
Inserting values into relations
Deleting records from relations
Updating values in relations
Retrieving data from relations
Aliases
SQL special operators and ORDER BY clauses
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Data Control Language
• Granting privileges
• Revoking privileges
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Perform data manipulation
 Describe basic SQL syntax
 List data types used in SQL, including SMALLINT,
DECIMAL (p, q), CHAR9n) and TIMESTAMP
 Define the term schema in relation to databases
 Create a schema using the CREATE SCHEMA
statement
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Drop schemas from a database using the DROP
SCHEMA statement
 Use appropriate commands to create, insert and
retrieve data from a database table, including
using the SELECT, CREATE TABLE and CREATE
VIEW statements
 Use the INSERT command appropriately
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Use appropriate UPDATE and DELETE statements
to update or delete information from a
database, including working with tuples
 Use the DISTINCT keyword
 Compose predicates and other statements using
the WHERE clause
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Identify the use and structure of Boolean
operators
 Describe SQL special operators and ORDER BY
clauses (e.g., the IN, BETWEEN and LIKE
operators)
 Deploy the ORDER BY clause to order output by
column or group of columns
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Retrieve data from relations using the SELECT
statement
 Create statements using the GRANT keyword to
control user access to relations
 Use the REVOKE keyword to limit access
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 9:
Relational Algebra
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Describe a selection operation
• Create a projection operation
• Explain and use a Cartesian product
operation
• Create unions
• Define a set difference operation
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
•
•
•
•
•
Describe an intersection operation
Create a theta-join
Use a natural join
Create an equi-join
Create an outer join
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Defining Relational Algebra
• Selection
• Projection
• Cartesian
product
•
•
•
•
Union
Difference
Intersection
Joins
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Describe a selection operation
 Create a projection operation
 Explain and use a Cartesian product
operation
 Create unions
 Define a set difference operation
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)





Describe an intersection operation
Create a theta-join
Use a natural join
Create an equi-join
Create an outer join
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Lesson 10:
Transactions and Database Security
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives
• Define the concept of a transaction,
including use of the INSERT, DELETE and
UPDATE operations
• Identify the two results of a transaction
(commit and rollback)
• Describe the four desirable "ACID" properties
(atomicity, consistency, isolation, durability)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Define serializability in relation to a DBMS
• List concurrency control methods, including
locking and timestamps
• Describe the steps involved in optimistic
concurrency control (e.g., reading,
validation, writing), including knowing when
and when not to use optimistic concurrency
control
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• Identify issues that can occur if concurrency
control is not enacted property (e.g., dirty
reads, lost and uncommitted updates,
unrepeatable query results)
• Identify issues and determine the scope of
database security (e.g., theft, privacy,
integrity control, availability)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Objectives (cont’d)
• List typical security threats to databases
• Describe database countermeasure and
protection techniques (e.g., encryption, ways
to create views)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Transactions
• Commit
• Rollback
• ACID properties
– Atomicity
– Consistency
– Isolation
– Durability
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Concurrency Control
•
•
•
•
•
Lost updates
Uncommitted updates
Unrepeatable query results
Serializability
Concurrency control methods
– Locking
– Timestamps
• Optimistic concurrency control
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Security
• The scope of database security
• Types of security threats
• Types of protection techniques
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary
 Define the concept of a transaction,
including use of the INSERT, DELETE and
UPDATE operations
 Identify the two results of a transaction
(commit and rollback)
 Describe the four desirable "ACID" properties
(atomicity, consistency, isolation, durability)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Define serializability in relation to a DBMS
 List concurrency control methods, including
locking and timestamps
 Describe the steps involved in optimistic
concurrency control (e.g., reading,
validation, writing), including knowing when
and when not to use optimistic concurrency
control
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 Identify issues that can occur if concurrency
control is not enacted property (e.g., dirty
reads, lost and uncommitted updates,
unrepeatable query results)
 Identify issues and determine the scope of
database security (e.g., theft, privacy,
integrity control, availability)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Summary (cont’d)
 List typical security threats to databases
 Describe database countermeasure and
protection techniques (e.g., encryption, ways
to create views)
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Design Specialist





Introduction to Databases
Relational Database Fundamentals
Database Planning
Overview of Database Design Methodology
Normalization
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Design Specialist





Logical Database Design
Physical Database Design
Structured Query Language
Relational Algebra
Transactions and Database Security
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved
Database Design Specialist
• For information about the CIW Database
Design Specialist exam and certification, visit
www.CIWcertified.com
Database Design Specialist
Copyright © 2011 Certification Partners, LLC -- All Rights Reserved