Transcript Document
Database
Design Methodology
Lesson 1:
Introduction to Databases
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
What Is
a Database?
• File-based databases
– Flat-file databases
• The evolution of databases
Relational Databases
and DBMSs
•
•
•
•
•
Relational databases
Structured Query Language
Database administrator
Advantages and disadvantages of DBMSs
Choosing the correct database type
The Origins of
Relational Databases
• Codd first proposed the relational data
model in 1970
• System R
• Development of SQL
• INGRES
• IBM UK Scientific Center
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
Lesson 2:
Relational
Database Fundamentals
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)
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
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
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
Multitier
Database Architecture
• Two-tier client-server architecture
– Fat client
• Three-tier client-server architecture
– Thin client
• n-tier architecture
Relational
Model Terminology
• Relational data
structure
• Rows (tuples)
• Columns
(attributes)
•
•
•
•
Domains
Degrees
Cardinality
Normalization
Using Tables
to Represent Data
•
•
•
•
•
Entities
Characteristics of relations
Types of tables
Primary keys
Foreign keys
Data
Models
• Components
– Structural information
– Manipulative information
– Integrity information
Entities and
Data Relationships
• Entity-relationship (ER) modeling
• Strong entities
• Weak entities
Relational Integrity
•
•
•
•
Domain constraints
Entity integrity
Referential integrity
Views
Database Languages
•
•
•
•
Data Definition Language
Data Manipulation Language
Data Control Language
Data dictionaries
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)
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
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
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
Lesson 3:
Database Planning
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
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
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 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
Requirements Document
• Conducting interviews
• Requirements document information
Selecting a DBMS
• Selection criteria
–
–
–
–
–
–
–
Data definition functionality
Physical criteria
Access criteria
Transactions
Utilities
Development tools
Miscellaneous features
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
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
Summary (cont’d)
Explain the importance of data modeling
and entity relationship diagrams during the
planning stage (e.g., define the concept of
an entity-relationship [ER] diagram)
Create a database prototype
Describe the purpose of database
application testing, including white-box
testing, black-box testing
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)
Lesson 4:
Overview of Database
Design Methodology
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
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
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)
Effects of
Poor Database Design
• Insertion anomalies
• Deletion anomalies
• Update anomalies
Database
Design Phases
• Conceptual phase
• Logical phase
• Physical phase
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
Entity-Relationship Models
•
•
•
•
Creating ER models
Defining domains
Common SQL data types
Determining data relationships
– Recursive relationships
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
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
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)
Lesson 5:
Normalization
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
Objectives (cont’d)
• Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
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
Related Concepts
•
•
•
•
•
Decomposition
Atomic value
Partial functional dependency
Transitive dependency
Denormalization
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
Summary (cont’d)
Describe database design steps, including
determining the proper steps in creating
entities, applying normalization, selecting a
database management system (DBMS)
Lesson 6:
Logical Database Design
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)
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)
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
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)
Logical
Database Design
• Logical data models
– One-to-one
– One-to-many
– Many-to-many
Creating a
Logical Data Model
Logical data model creation
Logical data model creation
Normalization
Normalization
Data model refinement
Data model refinement
Using a Database
Definition Language
• Data dictionaries
• Validating the logical data model
Defining
Integrity Constraints
•
•
•
•
•
Necessary data
Domain constraints
Entity integrity
Referential integrity
Enterprise constraints
Creating an
Enterprise Data Model
• User views
• Normalization
• Validating data operations
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)
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)
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
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)
Lesson 7:
Physical Database Design
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)
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
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
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
Physical
Database Design
• Creating base relations for a target DBMS
• Data Definition Language
Creating
Enterprise Constraints
• Determining referential constraints
Using
Secondary Indexes
• A secondary index is a mechanism that
creates an additional key for a relation
Denormalization
• The process of reuniting relations that were
split during the normalization process to
improve performance
Creating
User Views
• Determine the appropriate data to be
included with each particular view
Designing
Database Access Rules
• Identifiers
• Privileges
– SELECT
– UPDATE
– DELETE
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)
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
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
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
Lesson 8:
Structured Query Language
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
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
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
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
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
SQL Basics
• Subsets of SQL
• SQL features
• Data types
Data
Definition Language
• Creating a schema
• Altering and dropping relations and views
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
Data
Control Language
• Granting privileges
• Revoking privileges
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
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
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
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
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
Lesson 9:
Relational Algebra
Objectives
• Describe a selection operation
• Create a projection operation
• Explain and use a Cartesian product
operation
• Create unions
• Define a set difference operation
Objectives (cont’d)
•
•
•
•
•
Describe an intersection operation
Create a theta-join
Use a natural join
Create an equi-join
Create an outer join
Defining
Relational Algebra
• Selection
• Projection
• Cartesian
product
•
•
•
•
Union
Difference
Intersection
Joins
Summary
Describe a selection operation
Create a projection operation
Explain and use a Cartesian product
operation
Create unions
Define a set difference operation
Summary (cont’d)
Describe an intersection operation
Create a theta-join
Use a natural join
Create an equi-join
Create an outer join
Lesson 10:
Transactions and
Database Security
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)
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
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)
Objectives (cont’d)
• List typical security threats to databases
• Describe database countermeasure and
protection techniques (e.g., encryption,
ways to create views)
Transactions
• Commit
• Rollback
• ACID properties
–
–
–
–
Atomicity
Consistency
Isolation
Durability
Concurrency Control
•
•
•
•
•
Lost updates
Uncommitted updates
Unrepeatable query results
Serializability
Concurrency control methods
– Locking
– Timestamps
• Optimistic concurrency control
Database
Security
• The scope of database security
• Types of security threats
• Types of protection techniques
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)
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
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)
Summary (cont’d)
List typical security threats to databases
Describe database countermeasure and
protection techniques (e.g., encryption,
ways to create views)
Database
Design Methodology
Introduction to Databases
Relational Database Fundamentals
Database Planning
Overview of Database Design Methodology
Normalization
Database
Design Methodology
Logical Database Design
Physical Database Design
Structured Query Language
Relational Algebra
Transactions and Database Security
Database Design Methodology
• For information about the CIW Database
Specialist exam and certification, visit
www.CIW-certified.com