Official Course Description: MCCCD Approval: 07/22/08
CIS164AH 20034-20086	L+L	3 Credit(s)	4 Period(s)
Database Design and Generation with Oracle
Top-down, systematic design and generation of relational databases using a Computer Aided Software Engineeering (CASE) tool. Inputting the ER (Entity Relationship) diagram, adjusting the base model for physical considerations in forward engineering the model into relational database tables, reverse engineering the ER diagram and design from relational database tables. Prerequisites: CIS164AG, CIS119DO or permission of Instructor.

Go to Competencies    Go to Outline
 

MCCCD Official Course Competencies:
CIS164AH   20034-20086	Database Design and Generation with Oracle

1.	Describe development lifecycles, roles and change management issues that affect database design. (I)
2.	Analyze and define database requirements. (II, III)
3.	Input business system requirements into Oracle designer and or other CASE tools. (III)
4.	Identify and perform the primary tasks to implement the physical Model. (IV)
5.	Transform the analysis model into design definitions. (V, VI)
6.	Refine, generate, and build the database design. (VI)
7.	Design and generate application database tables. (VII, VIII, IX)
8.	Modify and redesign non-relational and legacy relational data models using re-engineering methodology. (X, XI)

Go to Description    Go to top of Competencies
 

MCCCD Official Course Outline:
CIS164AH   20034-20086	Database Design and Generation with Oracle

I. Development Life Cycle, Methods A. Defining the stages of development B. Examining the development roles C. Evaluating the cost of change II. The Context Diagram A. Describing CASE tool B. Identifying the major components of CASE tool C. Explaining data and process modeling D. Explaining the Purpose of an Entity Relationship Diagram (ERD) E. Identifying the components of an ERD F. Creating a simple data model G. Assigning a unique identifier to an entity H. Defining a domain III. Cross-Checking Business Functions and Data A. Explaining the Purpose of a CRUD (Create, Retrieve, Update, Delete) Matrix B. Creating a matrix diagram C. Verifying business function to entity usage D. Adding data usages IV. Reviewing Analysis Tasks A. Articulating the primary analysis tasks B. Identifying analysis quality checking tasks C. Quality checking the analysis using repository reports V. Transforming the Data Model into a Table Design A. Performing one-to-one mapping of entities to tables B. Explaining how relationships are implemented C. Creating a first-cut data design from the information model using the database D. Identifying and using the main components of the CASE tool E. Reverse engineering with CASE tools VI. Refining the Database Design A. Defining the components of the server model diagram B. Creating a data schema diagram C. Examining column properties D. Adding a column to a table definition E. Automating the generation of a primary key column F. Providing default value for a column G. Associating a sequence definition with a column that represents a surrogate key VII. Building the Database A. Defining a database B. Implementing the data design using the generator C. Viewing the online database VIII. Loading the Tables A. Creating and loading test data to the tables B. Explanations of different load methods IX. Tuning Techniques A. When to add an index B. When to use partition table spaces C. How to identify problems with the tables X. Querying and Extracting Data A. Tools to query and extract data from the tables B. Ad hoc queries and canned processes XI. Operational Tables and Data Warehouse Tables A. Definitions and distinctions B. Normalization differences C. Loading Data Warehouse tables D. Basic rules for Data Warehouse processes

Go to Description    Go to top of Competencies    Go to top of Outline