Introduction to Relational Databases and SQL Programming

by Allen, Christopher; Creary, Catherine; Chatwin, Simon

• ISBN13: 9780072229240
• ISBN10: 0072229241
• Edition: 1st
• Format: Paperback
• Copyright: 2003-12-15
• Publisher: McGraw-Hill Osborne Media

Summary

This text provides hands-on instruction to relational database design and management using Oracle examples. The proven format features step-by-step tutorials, end-of-chapter reviews, practice questions, key term lists, and lab projects.

Author Biography

Christopher Allen (Culver City, CA) is a database professional with 20 years of experience. He is an OCP DBA and an OCP Application Developer and has worked on numerous Oracle Financials implementations. In addition, he has taught several hundred computer classes to adults, resulting in extensive experience communicating technical information in ways that are easy to absorb and easy to remember. His published works include the Oracle Certified Professional Application Developer Exam Guide (co-author, Oracle Press), Hard Disk Smarts (John Wiley Publishing), and four books on Lotus (Que).

Table of Contents

Introduction	xvii
Chapter 1 Introducing Relational Databases	1	(15)
What Exactly Is a Database?	1	(2)
Tables	1	(1)
Rows/Records	2	(1)
Columns/Fields	2	(1)
How Is a Database Different from a Spreadsheet?	3	(5)
Many Rows	3	(1)
Many Users Simultaneously	4	(1)
Security	4	(1)
Relational Abilities	5	(1)
Constraints to Ensure Data Quality	6	(1)
Case Study-Clapham Specialty Store	6	(1)
Designing Your First Database	7	(1)
How Will Knowing This Help You?	8	(1)
When Developing Software	8	(1)
When Doing Database Administration	8	(1)
When Doing Business Analysis	8	(1)
If You Just Want to Know How to Use Databases Better	9	(1)
History of SQL	9	(2)
Chapter 1 Review	11	(5)
Chapter 2 Storing and Retrieving Data: The Basics	16	(36)
Prepare to Work with a Database	17	(3)
Creating a Table	17	(1)
Inserting Records	18	(1)
Selecting Records	18	(1)
Dropping a Table	18	(2)
Creating Tables	20	(12)
Guidelines for Naming Tables and Columns	21	(3)
Creating a More Involved Table	24	(6)
Determining a Table's Structure	30	(1)
NULL and NOT NULL Columns	31	(1)
Inserting Data-Additional Techniques	32	(5)
How to Insert Records Containing NULL Values	32	(3)
Creating and Populating a Table	35	(2)
How to Insert Data That Contains Apostrophes	37	(1)
Viewing Data from a Table-Additional Techniques	37	(8)
Selecting Specific Columns	38	(1)
Changing Column Order	38	(1)
Performing Math Using Data in a Table	39	(2)
Connecting Two or More Pieces of Text Together	41	(1)
Assigning Aliases to Columns	42	(1)
Changing the Data Values You View	43	(2)
Chapter 2 Review	45	(7)
Chapter 3 Performing Advanced Data Manipulation	52	(28)
SQL Command Categories	53	(1)
Data Definition	53	(1)
Data Manipulation	53	(1)
Data Control	53	(1)
Data Retrieval	54	(1)
Transaction Control	54	(1)
Limiting Which Records You Select	54	(8)
Filtering Records Based on Numbers	55	(4)
Filtering Records Based on Text	59	(1)
Filtering Records Based on Dates	60	(2)
Viewing Records in a Different Order	62	(2)
Sorting on Individual Columns	63	(1)
Sorting on Multiple Columns	63	(1)
Showing Only Unique Values	64	(2)
Selecting from the DUAL Table	66	(1)
Modifying Data in a Table	67	(1)
Removing Records from a Table	68	(1)
Deleting Rows Matching Specific Criteria	68	(1)
Deleting All Rows	68	(1)
Transaction Control	69	(6)
Undoing DML Transactions	69	(3)
Making Data Available to Others	72	(1)
Implicit and Explicit COMMITS	73	(1)
Performing Advanced Data Manipulation	74	(1)
Chapter 3 Review	75	(5)
Chapter 4 Controlling SQL*Plus	80	(24)
Editing Prior Commands	81	(4)
Using a Text Editor	81	(1)
Using the EDIT Command	82	(1)
Line-Level Editing	83	(2)
Copying and Pasting	85	(2)
Using Your Mouse to Edit Text	86	(1)
Clearing the SQL*Plus Screen	87	(1)
Customizing the SQL*Plus Environment	87	(2)
Customizing Using the SQL*Plus Menu	87	(2)
Customizing Using Commands	89	(1)
Saving Environment Customizations	89	(1)
Producing More Readable Output	90	(4)
Formatting Numbers in SQL*Plus	90	(1)
Formatting Text in SQL*Plus	91	(1)
Formatting Column Headings in SQL*Plus	92	(2)
Spooling Output to Disk	94	(1)
SQL Script Files	95	(4)
Creating a Script File	95	(1)
Running a Script File	96	(1)
Using Variables In Script Files	96	(3)
Chapter 4 Review	99	(5)
Chapter 5 Oracle SQL Built-in Functions	104	(48)
Implementing Commonly Used Single-Row Functions	105	(34)
System Variables	106	(2)
Number Functions	108	(2)
Text Functions	110	(10)
Using Single-Row Functions	120	(1)
Date	121	(5)
Data Conversion	126	(6)
Other Functions	132	(7)
Implementing Commonly Used Group Functions	139	(8)
Grouping Data via the GROUP BY Clause	142	(1)
Including and Excluding Grouped Rows via the HAVING Clause	143	(2)
Using Group Functions	145	(2)
Chapter 5 Review	147	(5)
Chapter 6 Indexes, Joins, and Subqueries	152	(36)
Creating the Test Tables	153	(2)
Indexes	155	(2)
Indexes in Databases	155	(1)
How to Create Indexes	156	(1)
Types of Indexes	157	(4)
B Tree Indexes	157	(2)
Bitmap Indexes	159	(1)
Bitmap Versus B-Tree Indexes	160	(1)
Function-Based Indexes	160	(1)
When to Use Indexes	161	(1)
Relationships Between Tables	162	(15)
Creating an Index	163	(1)
Writing SELECT Statements to Display Data from More Than One Table	164	(3)
Types of Joins	167	(6)
Set Operators	173	(4)
Writing Subqueries	177	(6)
What Is a Subquery?	177	(1)
Types of Problems Subqueries Can Solve	177	(1)
Single-Row Subqueries	177	(2)
Multirow Subqueries	179	(1)
Multicolumn 5ubqueries	180	(1)
Correlated Subqueries	181	(2)
Chapter 6 Review	183	(5)
Chapter 7 Creating a Program with PL/SQL	188	(42)
What Is PL/SQL7	189	(7)
Describing PL/SQL	192	(1)
Who's Who in SQL, PL/SQL, and SQL*Plus	193	(1)
Stored Procedures, Functions, and Triggers	194	(2)
Stored Procedures and SQL Scripts	196	(1)
Structure of a PL/SQL Block	196	(3)
Header Section	197	(1)
Declaration Section	197	(1)
Execution Section	198	(1)
Exception Section	198	(1)
Creating a Simple PL/SQL Procedure	199	(2)
Calling Procedures and Functions	200	(1)
PL/SQL Variables and Constants	201	(4)
Declaring PL/SQL Variables	202	(1)
Declaring PL/SQL Constants	202	(1)
Assigning Values to Variables	203	(1)
Using Variables	204	(1)
Control Structures in PL/SQL	205	(12)
IF Statement	206	(2)
Loops	208	(2)
Cursors	210	(5)
Nested Loops and Cursor Example	215	(2)
Error Handling	217	(6)
Exceptions	217	(1)
System-Defined Exceptions	218	(2)
Programmer-Defined Exceptions	220	(1)
Creating a Programmer-Defined Exception	221	(2)
Chapter 7 Review	223	(7)
Chapter 8 Reading a Data Model	230	(16)
Overview of Data Model Design	231	(2)
Purpose and Benefits of Models	231	(1)
Relational Integrity: Quality Data	232	(1)
Types of Data Models	233	(3)
Conceptual Model	233	(1)
Logical Data Model	234	(1)
Physical Data Model	234	(2)
Reading an Entity Relationship Diagram	236	(6)
Entities	236	(1)
Attributes	237	(1)
Relationships	238	(2)
Cardinality and Optionality Notations	240	(1)
Reading an Entity Relationship Diagram	241	(1)
Chapter 8 Review	242	(4)
Chapter 9 Basics of Designing a Database's Structure	246	(26)
The Business Specification: Let the Data Tell You Where It Goes	247	(1)
Selecting the Database's Grain	247	(1)
Entities and Attributes	248	(3)
Identifying Records Reliably: Primary Keys	251	(4)
Why Do You Need a Primary Key?	251	(1)
Composite Primary Keys	251	(1)
Natural Primary Keys vs. Surrogate Primary Keys	252	(2)
Relationships: Referring to Data in Other Tables	254	(1)
Common Data Model Standards	255	(3)
Crow's Foot (IE)	256	(1)
IDEF1X	256	(2)
Relationships: Cardinality and Optionality	258	(6)
One-to-Many	258	(1)
Many-to-Many	259	(1)
One-to-One	260	(1)
Optionality	261	(2)
Dependency: Identifying Relationships	263	(1)
Recursive vs. Binary	264	(1)
Modeling Multiple Categories: Supertype and Subtypes	264	(4)
Creating Basic Data Models	265	(1)
Categories, Supertypes, and Subtypes	265	(2)
Implementing Super/Subtypes in a Physical Model	267	(1)
Chapter 9 Review	268	(4)
Chapter 10 Normalization	272	(22)
The Process of Normalization	273	(1)
Dependency	273	(2)
Dependents and Determinants	273	(2)
The First Three Normal Forms	275	(4)
First Normal Form: Eliminate Repeating Groups	275	(2)
Second Normal Form: Eliminate Redundant Data	277	(1)
Third Normal Form: Eliminate Attributes Not Dependent on the Primary Key	278	(1)
Apply the Normal Forms to a Database Model	279	(1)
The Fourth and Fifth Normal Forms	279	(4)
Fourth Normal Form: Isolate Independent Multiple Relationships	280	(2)
Fifth Normal Form: Isolate Semantically Related Multiple Relationships	282	(1)
The Rules You Really Need	283	(1)
Anomalies in the Data	283	(3)
Normalizing the Data	284	(1)
Insert	285	(1)
Delete	285	(1)
Update	285	(1)
A Tax on Being Law-Abiding	286	(1)
Moving from Logical to Physical Models	286	(3)
Choosing Your Engine	286	(1)
Changing Terminology	287	(1)
Translating Super- and Subtypes	287	(2)
Chapter 10 Review	289	(5)
Chapter 11 Analyzing Data Quality Issues	294	(44)
Datatypes and Missing Data: Quality Basics	295	(6)
Handling Missing Values	295	(1)
Apples and Oranges: Defining Datatypes	296	(2)
Choosing a Datatype	298	(1)
Creating a Table and Inserting Data	299	(2)
Converting Datatypes: Weak and Strong Typing	301	(1)
Data Domains: Sanity Checks	301	(1)
Domains as Sets of Values	301	(1)
Column and Table Constraints	302	(5)
Column Constraints	303	(2)
Table Constraints	305	(2)
Primary Key Constraints and Indexes	307	(5)
Uniqueness and How to Enforce It	307	(3)
Alternate Keys	310	(1)
Other Indexes	311	(1)
Foreign Key Constraints: Values from Other Tables	312	(14)
Adding the Constraint	313	(1)
Implementing Cardinality and Optionality	314	(2)
Cascading Effects	316	(3)
Cascade Delete on a Recursive Relationship	319	(2)
The Cascades That Don't	321	(1)
Creating the Movie Database	322	(4)
Declarative Relational Integrity: Pros and Cons	326	(3)
Declaring Foreign Key Constraints	327	(1)
Triggers and Procedural Code	327	(2)
Naming Constraints: Make It Easy for the Programmers	329	(3)
Naming Tables and Columns	330	(1)
Naming Check Constraints	330	(1)
Naming Foreign Key Constraints	330	(1)
Naming Indexes	331	(1)
Chapter 11 Review	332	(6)
Chapter 12 Other Useful Oracle Techniques	338	(32)
Transferring Data Between Tables	339	(5)
Transferring Data Using INSERT	342	(1)
Creating a New Table Based on an Existing One	343	(1)
Renaming Tables	344	(1)
Altering a Table's Structure	345	(3)
Adding Columns	345	(1)
Changing Column Datatypes	345	(1)
Changing NULL Options	346	(2)
Views	348	(10)
Creating a View	349	(1)
Updateable Views	350	(1)
Dropping Views	351	(1)
Top N Analysis	352	(1)
Creating a View on a Table	353	(5)
Other Database Objects	358	(7)
Sequences	358	(4)
Synonyms	362	(3)
Chapter 12 Review	365	(5)
Appendix ON the CD-ROMs	370	(3)
About Oracle 9i Standard Edition for Windows	370	(3)
System Requirements	370	(1)
Registering with the Oracle Technology Network	371	(1)
Installing Oracle 9i Standard Edition	372	(1)
Glossary	373	(12)
Index	385