RelationalDBDesign

This website discusses Normalization, Relational Database Design, SQL and Oracle Technology.

Oracle SQL Tuning

Advanced Oracle Tuning - Glossary
High Water Mark	PCTFree Optimal Performance	Monitoring Tuning
Object Oriented Database	Abstract Data Type
Concatenated Indexes	STAR Transformation Queries	B-Tree Bitmapped Indexes	Function Based Indexed

Advanced SQL

Characteristics of SQL	PUBS Database explained	Group By Clause	Subquery Equals Clause
GETDATE, SYSDATE, TODAY

Backup Recovery Methods

Backup Recovery Methods - Glossary
Physical Archiving	Physical vs Logical	OS Backup Options	Closed DB Backup

Backup Recovery Concepts

Backup Recovery Considerations	Business Considerations	Operational Considerations	Technical Considerations
Oracle Memory Processes	Instance Structure	Instance Memory Structure	DBWR Arch Process
What Causes Checkpoint

Basic SQL

SQL Approaches - Quiz	SQL-92	Relational DB Analogy	Three Normalization Levels
Customer Table Example	Flatfile Database	Relational vs Flatfile	Normalization Example Table

Database Admin

Database Admin-Glossary
DBA	Tablespace Resource MGT	Large DB Features	Security Features

DBA Admin -Tasks

DBA Admin Tasks-Glossary
DB Monitoring	Alert Log Info	Alert Log Contents	License Compliance

Database Architecture

Oracle Describe Command	Oracle Spool Command	desc Oracle command	Archive Log List Command
Checkpoint process (CKPT)	(DBW0) Database Writer	Instance vs. Database	Oracle Large Pool

Extended DB Features

Database Features - Glossary
Partitioned Tables	Partitioning Tables	Partitioning Advantages	Table Range Partitioning

Managing Objects

Managing Objects - Glossary
Coalesce Tablespace	Operating System Blocks	Describing Columns View	Oracle Extents
Oracle free Space	Oracle Segment Role	Oracle Storage Concepts	Fragmented TableSpace
Alter extent size Table	Oracle Dictionary Extents	Describing Table Columns	Data Dictionary Constraints

Managing Users

Managing Users - Glossary
Configuring Oracle	SID List Listener	Configuring listener.ora	Stop Restart Listener
Admin Privileges	Oracle Connect Database	Remote Login Password	Oracle DBA Roles

Config Management

Internet Directory	Directory Naming	Oracle Names	Local Filesystem

Oracle Backup Recovery

Oracle Backup Recovery - Glossary
Database Backup	Recovery Manager Features	Backup Strategies	Enterprise Manager

Database Backup

Database Backup Recovery - Glossary
Import Export Utilities	Export/Import Overview	Running Export	Complete Backup

Network Admin

Network Administration - Glossary
Network Services	Scalability Features	Partitioned Tables	Security Features

Network Topology

Network Topology - Glossary
Transparent Network Substrate	Database Local Autonomy	Location Independence	Distributed Fragmentation
Oracle Networked Computers		Evolution Network Protocols	sqlnet.ora Location

SQL Extensions

Oracle SQL - Glossary
Union, Intersect, Minus	Hierarchical Retrieval via Connect	Advanced Group Operators	Oracle Pseudo Columns
Decode NVL Functions	Concat substr, length	Character, String Functions	to_char, round, trunc

SQL Tuning

SQL Tuning - Glossary
	Decode ANSI SQL	Heuristics Based Optimizer	SQL Tuning Quiz
SQL Optimizer Modes	Non-correlated Subquery	Analyze Explain Plan	SQL Optimizer Modes

Oracle PL/SQL

Oracle PL/SQL - Glossary
Querying Object Tables	Oracle Objects	Two Types Object Tables	Collections | varrays

Performance Tuning

Performance Tuning - Glossary
Optimizer/Query Improvements	Optimizer Functions	Stored Outlines Behavior	dbms_stats package

Tuning Basics

OCP Exam Preparation
Oracle Performance Tuning

Stored Objects

PL/SQL Objects - Glossary
User Defined Objects	Function Syntax	Function Return Values	Function Boolean Value

PL/SQL Programming

Programming PL/SQL - Glossary
Petstore DB Schema	Creating Naming Conventions	non-PL/SQL VAR bind host	DBMS_OUTPUT.PUT_LINE
PL/SQL Block Conclusion	Adding PL/SQL Comments	Explicit/Implicit Data Conversions	Variables Nested Blocks

Database Analysis

Database Analysis - Glossary
DB Tables Characteristics	Concatenated Keys	Define Primary Key	Defining Foreign Keys
Referential Integrity	Relational Theory Notation	Database Null Values	Relational Notation
Normalization Defined	Normalization Objective	Identify Functional Dependencies	Normalization Process - Quiz
Achieving 2nd Normal Form	Functional Dependencies - Exercise	Normalization Database - Quiz	Reduce Data Redundancy
Third Normal Form	1st, 2nd, 3rd Normal Form	Second NF Limitations	Transitive Dependencies
Table Joins User Views	Permissions Roles DB	Permissions Requirements	SQL Inner Join
Downside Horizontal Partitioning	Downside Vertical Partitioning	Horizontal Partitioning	Database Partitioning

Database Design

Database Design - Glossary
Linking Tables	Relational Database Model	Relationally Complete	Relational Database Quiz
RDBMS Functions	Database LifeCycle	Design Stages in DBLC	Three Schema Architecture
Application Approach Design	Post Database Design Stages	Top Down Design	Database Design Strategy
Business Rules Importance	Purpose Requirements Analysis	Define Business Objects	Business Object Characteristics
Entities Attributes Conceptual Model	Multi-valued Attribute Resolve	Entity Identifier Attribute	Attribute Domain Types
Three Relationship Types	Many to Many to One-to-Many	Optional Participation Relationship	One-to-one Relationship

DB2 Interview Questions

Outer Joins, Subselect Questions
DB2 Lock Contention Questions

Tuning Oracle Instance

Tuning Instance - Glossary
Instance Tuning	SGA Components	Shared Pool	Library Cache

Tuning External Environment

DB Monitoring	Tune OS Components	Architecture Related Performance	Disk I/O DB Performance

Building Access Database

Building Access Database - Glossary
MS Access Window	Access Help Systems
How Data Is Stored	Database Concepts - Quiz	Design Tables Conclusion	Create Access Database

Access Data Manipulation

Access Data Manipulation- Glossary
Filter Records Display Subset	Filter-records using Complex Criteria	Sorting Access-Records	Finding Data Table
Multiple Queries in Access	Access Queries-Conclusion	Refine Query Specifying Sort Order	Using Query Design View
Access Form Controls	Toolbox Buttons AddControls Form	Form Datasheet-View	Creating-editing Forms

Tables Queries

Input Mask Mechanics

Specifying Input Mask	Input Masks - Exercise
Multiple Access Queries	Different Types Access Queries	Query Field Properties	Filtering Multiple Criteria - Quiz
Working with Option Groups	Create Multipage Forms	Subform Control	Calendar Activex Control

Access Features

Cascade Update Delete	Different Table Relationship Types	Many-to-many Relationship	Database Relationships - Quiz
Access Web
Access Macros	Create Macros	Macro Builder	Running Macro Queries

Database Web Apps

Database Web - Glossary
Database Web Applications
Database Concepts Conclusion	Table-Structures Databases	ODBC Middleware Solutions	Database Concepts - Quiz

Enterprise Business Rules

Enterprise Business Rules - Glossary
Transaction Isolation - Quiz	Transaction Locks-Quiz	Error Handling-testing	Deadlock-Transaction Blocking
Declare, assign, use variables
Testing SQL Server Triggers	Creating Triggers-Exercise	Sql Triggers - Quiz	Enforcing-Business Rules
Stored Procedure - Quiz	Stored Procedure - Exercise	Modifying Stored Procedure	Error Handling

Queries Cursors Views

Queries Cursors Views - Glossary
SQL Basic Conclusion	Sysprocesses Table	Executing SQL Server-Queries
Using Subqueries - Exercise	Selecting Data Conclusion
Using SubQuery Statements	SQL Statement-Punctuation

SQL Monitoring

SQL Monitoring - Glossary
Trace Flags	DTS Limitations	DTS Package Designer

SQL-Database Creation

SQL Server Database Creation - Glossary
What is Relational Database	SQL-Client/Server-Architecture	SQL Server Services	Administer SQL Servers
Database DeNormalization	Data Normalization - Exercise

Oracle SQL Tuning

High Water Mark	PCTFree Optimal Performance
Monitoring Tuning
Object Oriented Database	Abstract Data Type
Concatenated Indexes	STAR Transformation Queries
B-Tree Bitmapped Indexes	Function Based Indexed

Advanced SQL

Characteristics of SQL	PUBS Database explained
Group By Clause	Subquery Equals Clause
GETDATE, SYSDATE, TODAY

Basic SQL

SQL Approaches - Quiz	SQL-92
Relational DB Analogy	Three Normalization Levels
Customer Table Example	Flatfile Database
Relational vs Flatfile	Normalization Example Table

Database Architecture

Oracle Describe Command	Oracle Spool Command
desc Oracle command	Archive Log List Command
Checkpoint process (CKPT)	(DBW0) Database Writer
Instance vs. Database	Oracle Large Pool

Managing Objects

Coalesce Tablespace	Operating System Blocks
Describing Columns View	Oracle Extents
Oracle free Space	Oracle Segment Role
Oracle Storage Concepts	Fragmented TableSpace
Alter extent size Table	Oracle Dictionary Extents
Describing Table Columns	Data Dictionary Constraints

Network Topology

Transparent Network Substrate	Database Local Autonomy
Location Independence	Distributed Fragmentation
Oracle Networked Computers
Evolution Network Protocols	sqlnet.ora Location

SQL Extensions

Union, Intersect, Minus	Hierarchical Retrieval via Connect
Advanced Group Operators	Oracle Pseudo Columns
Decode NVL Functions	Concat substr, length
Character, String Functions	to_char, round, trunc

SQL Tuning

	Decode ANSI SQL
Heuristics Based Optimizer	SQL Tuning Quiz
SQL Optimizer Modes	Non-correlated Subquery
Analyze Explain Plan	SQL Optimizer Modes

PL/SQL Programming

Petstore DB Schema	Creating Naming Conventions
non-PL/SQL VAR bind host	DBMS_OUTPUT.PUT_LINE
PL/SQL Block Conclusion	Adding PL/SQL Comments
Explicit/Implicit Data Conversions	Variables Nested Blocks

Database Design

Linking Tables	Relational Database Model
Relationally Complete	Relational Database Quiz
RDBMS Functions	Database LifeCycle
Design Stages in DBLC	Three Schema Architecture
Application Approach Design	Post Database Design Stages
Top Down Design	Database Design Strategy
Business Rules Importance	Purpose Requirements Analysis
Define Business Objects	Business Object Characteristics
Entities Attributes Conceptual Model	Multi-valued Attribute Resolve
Entity Identifier Attribute	Attribute Domain Types
Three Relationship Types	Many to Many to One-to-Many
Optional Participation Relationship	One-to-one Relationship

Database Analysis

DB Tables Characteristics	Concatenated Keys
Define Primary Key	Defining Foreign Keys
Referential Integrity	Relational Theory Notation
Database Null Values	Relational Notation
Normalization Defined	Normalization Objective
Identify Functional Dependencies	Normalization Process - Quiz
Achieving 2nd Normal Form	Functional Dependencies - Exercise
Normalization Database - Quiz	Reduce Data Redundancy
Third Normal Form	1st, 2nd, 3rd Normal Form
Second NF Limitations	Transitive Dependencies
Table Joins User Views	Permissions Roles DB
Permissions Requirements	SQL Inner Join
Downside Horizontal Partitioning	Downside Vertical Partitioning
Horizontal Partitioning	Database Partitioning