Review Exam Topics

What is a Database?

Database Concepts

• Describe the components of a database system
• Explain the purpose of a database

Types of  Databases Models

• Describe types of database models (relational, object oriented, flat, network…)
• Compare the differences between the different types of databases

Relational Database Concepts

• Describe the characteristics of a relational database
• Explain the importance of relational databases in business
• List the major transformations in database technology

Defining Levels of Data Abstraction

• Define the terminology used for database storage
• Describe levels of data abstraction used in relational databases

Gathering Requirements for Database Design

• Gather requirements to implement a database solution
• Explain business rules

The Language of Database and Data Modeling

Defining a Table in a Database

• Describe the structure of a single table

Using Conceptual Data Modeling

• Describe a conceptual data model
• Explain the components of a conceptual/logical model

Defining Instance and Schema in Relational Databases

• Examine examples of an entity and a corresponding table
• Examine examples of an attribute and a corresponding column
• Explain instances and schemas in a relational database

Using Unique Identifiers, Primary and Foreign Keys

• Identify unique identifiers and a corresponding primary key
• Define composite and compound primary keys
• Define relationships and corresponding foreign keys
• Define barred relationships and the corresponding primary keys

Data Modeling – Creating the Physical Model

Creating Physical Data Models

• Create a physical data model
• Compare conceptual and physical data models

Documenting Business Requirements and Rules

• Explain the importance of clearly communicating and accurately capturing database information requirements
• Identify structural business rules
• Identify procedural business rules
• Identify business rules that must be enforced by additional programming (eg SQL)

Defining Supertype and Subtype Entity Relationships

• Describe an example of an entity
• Define supertype and subtype entities
• Implement rules for supertype and subtype entities

Using Attributes

• Describe attributes for a given entity
• Identify and provide examples of instances
• Distinguish between mandatory and optional attributes
• Distinguish between volatile and nonvolatile attributes

Using Unique Identifiers (UIDs)

• Define the types of unique identifiers
• Select a unique identifier using business rules
• Define a candidate unique identifier
• Define an artificial unique identifier

Identifying  Relationships

• Explain one-to-one, one-to-many, and many-to-many relationships
• Identify the optionality necessary for a relationship
• Identify the cardinality necessary for a relationship
• Identify nontransferable relationships
• Name a relationship
• Create ERDish sentences to represent ERDs
• Create ERDs to represent ERDish sentences

Resolving Many to Many Relationships and Composite Unique Identifiers

• Resolve a many-to-many relationship using an intersection entity
• Identify the variations of unique identifiers after creation of an intersection entity
• Define a barred relationship
• Identify composite unique identifiers

Identifying Hierarchical, Recursive, and Arc Relationships

• Define a hierarchical relationship
• Define a recursive relationship
• Define an arc relationship
• Identify UIDs in a hierarchical, recursive and arc relationship model
• Construct a model using recursion and hierarchies
• Identify similarities and differences in an arc relationship and a supertype/subtype entity

Tracking Data Changes Over Time

• Explain necessity of tracking data changes over time
• Identify data that changes over time
• Identify the changes in unique identifiers after adding the element of time to an ERD

Validating Data Using Normalization

• Define the purpose of normalization
• Define the rules of First, Second, and Third Normal Forms
• Apply the rules of First, Second, and Third Normal Form

Mapping the Physical Model

Mapping Entities,Columns and Data Types****

• Map entities to identify database tables to be created from an ERD
• Identify column data types from an ERD
• Identify common data types used to store values in a relational database

Mapping Primary, Composite Primary and Foreign Keys

• Identify primary keys from an ERD
• Identify which ERD attributes would make candidate primary keys
• Describe the purpose of a foreign key in an Oracle Database
• Identify foreign keys from an ERD
• Describe the relationship between primary keys, composite primary keys, and foreign keys in an Oracle Database

Introduction to SQL

Using Structured Query Language (SQL)

• Explain the relationship between a database and SQL

Using Data Definition Language (DDL)

• Describe the purpose of DDL
• Use DDL to manage tables and their relationships

Using Data Manipulation Language (DML) and Transaction Control Language (TCL)

• Describe the purpose of DML
• Use DML to manage data in tables
• Use TCL to manage transactions

Defining and using Basic Select statements

• Identify the connection between an ERD and a Relational Database using SQL SELECT statements
• Build a SELECT statement to retrieve data from an Oracle Database table
• Use the WHERE clause to the SELECT statement to filter query results

Displaying Sorted Data

• Use the ORDER BY clause to sort SQL query results

Defining Table Joins

• Describe the different types of joins and their features
• Use joins to retrieve data from multiple tables