Database Design Guide

Database Design Guide Copyright 1998 Pervasive Software Inc. All rights reserved worldwide. Reproduction, photocopying, or transmittal of this publication, or portions of this publication, is prohibited without the express prior written consent of the publisher, unless such reproduction, photocopying, or transmittal is part of a Derivative Software Product as defined in the licenses granted in conjunction with the purchase of this publication and associated software. This product includes software developed by Powerdog Industries.  1994 Powerdog Industries. All rights reserved. Pervasive Software Inc. 8834 Capital of Texas Highway Austin, Texas 78759 USA

disclaimer

PERVASIVE SOFTWARE INC. LICENSES THE SOFTWARE AND DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY ON AN "AS IS" BASIS AND SOLELY IN ACCORDANCE WITH THE TERMS AND CONDITIONS OF THE ACCOMPANYING LICENSE AGREEMENT. PERVASIVE SOFTWARE INC. MAKES NO OTHER WARRANTIES WHATSOEVER, EITHER EXPRESS OR IMPLIED, REGARDING THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION; PERVASIVE SOFTWARE INC. HEREBY EXPRESSLY STATES AND YOU OR YOUR COMPANY ACKNOWLEDGES THAT PERVASIVE SOFTWARE INC. DOES NOT MAKE ANY WARRANTIES, INCLUDING, FOR EXAMPLE, WITH RESPECT TO MERCHANTABILITY, TITLE, OR FITNESS FOR ANY PARTICULAR PURPOSE OR ARISING FROM COURSE OF DEALING OR USAGE OF TRADE, AMONG OTHERS.

trademarks

Btrieve and XQL are registered trademarks of Pervasive Software Inc. Built on Btrieve, Built on Scalable SQL, Client/Server in a Box, DDF Ease InstallScout, MicroKernel Database Engine, MicroKernel Database Architecture, Navigational Client/Server, Pervasive.SQL, Scalable SQL, Smart Components, Smart Component Management, Smart Naming, SmartScout, and Xtrieve PLUS are trademarks of Pervasive Software Inc. Microsoft, MS-DOS, Windows, Windows NT, Win32, Win32s, and Visual Basic are registered trademarks of Microsoft Corporation. Windows 95 is a trademark of Microsoft Corporation. NetWare and Novell are registered trademarks of Novell, Inc. NetWare Loadable Module, NLM, Novell DOS, Transaction Tracking System, and TTS are trademarks of Novell, Inc. All company and product names are the trademarks or registered trademarks of their respective companies.

Database Design Guide

100-003195-004

February 1998

Contents About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Who Should Read This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Organization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

1

Relational Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Stages of Design . . . . Conceptual Design Logical Design. . . Physical Design . .

2

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

16 16 17 21

Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Named Databases . . . . . . . Bound Databases . . . . . . . . Creating Database Components Naming Conventions . . . . . . Unique Names . . . . . . Valid Characters. . . . . . Maximum Name Lengths . Case-Sensitivity . . . . . . Creating a Data Dictionary . . . Creating Tables . . . . . . . . . Aliases. . . . . . . . . . . Creating Columns . . . . . . . . Creating Indexes . . . . . . . . Index Segments . . . . . . Index Attributes . . . . . .

Database Design Guide

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

3

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

23 24 25 26 26 27 28 29 30 33 34 36 37 39 41

Contents

3

Inserting and Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Inserting Values . . . . . . . . . . . . . . Inserting Values from Other Tables . Transaction Processing . . . . . . . . . . Deleting Data . . . . . . . . . . . . . . . Dropping Indexes . . . . . . . . . . . . . Dropping Columns . . . . . . . . . . . . Dropping Tables. . . . . . . . . . . . . . Dropping an Entire Database . . . . . . .

4

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

. . . . . . . .

44 45 46 47 48 48 49 50

Modifying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Modifying Tables . . . . . . . . . . . . Setting Column Attributes . . . . . . . . Setting Default Values. . . . . . . Setting Valid Values . . . . . . . . Setting Null Values . . . . . . . . Specifying Edit Masks . . . . . . . Permanent and Temporary Masks String Masks . . . . . . . . . . . Number Masks . . . . . . . . . . Hexadecimal Masks . . . . . . . . Date Masks . . . . . . . . . . . . Time Masks . . . . . . . . . . . . Timestamp Masks . . . . . . . . . Boolean Masks . . . . . . . . . . Using UPDATE . . . . . . . . . . . . .

5

. . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

52 53 53 54 55 57 63 63 64 68 69 74 76 77 81

Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Views . . . . . . . . . . . . . . . Features of Views . . . . . . Temporary and Stored Views Read-Only Tables in Views .

Database Design Guide

. . . .

. . . .

. . . .

. . . . 4

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

83 83 84 86 Contents

Mergeable Views . . . . . . . . . Selection Lists. . . . . . . . . . . . . . Sorted and Grouped Rows . . . . . . . Joins. . . . . . . . . . . . . . . . . . . Joining Tables with Other Tables . Joining Views with Tables . . . . . Types of Joins . . . . . . . . . . . Subqueries . . . . . . . . . . . . . . . Subquery Limitations . . . . . . . Correlated Subqueries . . . . . . Restriction Clauses . . . . . . . . . . . Restriction Clause Operators . . . Restriction Clause Examples . . . Expressions . . . . . . . . . . . . Functions . . . . . . . . . . . . . . . . Aggregate Functions . . . . . . . Arguments to Aggregate Functions Aggregate Function Rules. . . . . Scalar Functions . . . . . . . . .

6

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. 88 . 90 . 92 . 93 . 95 . 96 . 96 . 100 . 101 . 101 . 103 . 104 . 107 . 111 . 126 . 126 . 127 . 128 . 129

Storing Logic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Stored Procedures . . . . . . . . . Declaring Stored Procedures . Invoking Stored Procedures . . Deleting Stored Procedures . . SQL Variable Statements . . . . . . Declaring SQL Variables . . . Substitution Variables . . . . . Assignment Statement . . . . SQL Cursor-Based Statements . . . SQL Cursor Declaration . . . . Open Cursor Statement . . . . FETCH Statement . . . . . . . Positioned DELETE Statement

Database Design Guide

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . . 5

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. 159 . 160 . 160 . 161 . 162 . 162 . 163 . 165 . 166 . 167 . 168 . 169 . 169 Contents

Positioned UPDATE Statement . . . . SQL Control Statements . . . . . . . . . . Compound Statement . . . . . . . . . IF Statement. . . . . . . . . . . . . . LEAVE Statement . . . . . . . . . . . LOOP Statement . . . . . . . . . . . WHILE Statement . . . . . . . . . . . Retrieving Status Information . . . . . . . . SQLSTATE System Variable . . . . . Exception and Completion Conditions SQL Triggers . . . . . . . . . . . . . . . . Timing and Ordering of Triggers . . . Defining the Trigger Action . . . . . .

7

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. 170 . 171 . 171 . 172 . 172 . 173 . 174 . 175 . 175 . 176 . 179 . 180 . 182

Managing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Defining Relationships Among Tables . . . . . . . . . . . . Referential Integrity Definitions . . . . . . . . . . . . . Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Primary Keys . . . . . . . . . . . . . . . . . . . . . . Foreign Keys . . . . . . . . . . . . . . . . . . . . . . Referential Constraints . . . . . . . . . . . . . . . . . . . . Referential Integrity Rules . . . . . . . . . . . . . . . . RI in the University Database . . . . . . . . . . . . . . . . . Creating the Course Table . . . . . . . . . . . . . . . Adding a Primary Key to Course . . . . . . . . . . . . Creating the Student Table with Referential Constraints Administering Database Security . . . . . . . . . . . . . . . Understanding Database Rights . . . . . . . . . . . . Establishing Database Security . . . . . . . . . . . . . Enabling Security . . . . . . . . . . . . . . . . . . . . Creating User Groups and Users . . . . . . . . . . . . Granting Rights . . . . . . . . . . . . . . . . . . . . . Dropping Users and User Groups . . . . . . . . . . . . Revoking Rights . . . . . . . . . . . . . . . . . . . . .

Database Design Guide

6

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . .

. 185 . 186 . 188 . 188 . 190 . 193 . 194 . 204 . 204 . 204 . 205 . 207 . 208 . 211 . 213 . 213 . 215 . 217 . 218 Contents

Disabling Security . . . . . . . . . . . . . . . . Specifying Data File Owner Names . . . . . . . Retrieving Information about Database Security Concurrency Controls . . . . . . . . . . . . . . . . . Transaction Processing . . . . . . . . . . . . . Starting and Ending Transactions . . . . . . . . Using Savepoints to Nest Transactions . . . . . Special Considerations . . . . . . . . . . . . . Isolation Levels . . . . . . . . . . . . . . . . . Explicit Locks . . . . . . . . . . . . . . . . . . Passive Control . . . . . . . . . . . . . . . . . Atomicity in Scalable SQL Databases . . . . . . . . Transaction Control in Procedures . . . . . . .

A

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . .

. 218 . 219 . 220 . 221 . 221 . 223 . 223 . 227 . 228 . 234 . 239 . 240 . 241

University Database Tables and Referential Integrity. . . . . . . . . . . . 242 Structure of the University Database . . . . . . Assumptions. . . . . . . . . . . . . . . . Entity Relationships . . . . . . . . . . . . Referential Integrity in the University Database. Table Design . . . . . . . . . . . . . . . . . . BILLING Table . . . . . . . . . . . . . . CLASS Table . . . . . . . . . . . . . . . COURSE Table . . . . . . . . . . . . . . DEPARTMENT Table . . . . . . . . . . . ENROLLS Table . . . . . . . . . . . . . FACULTY Table . . . . . . . . . . . . . . PERSON Table . . . . . . . . . . . . . . ROOM Table . . . . . . . . . . . . . . . STUDENT Table . . . . . . . . . . . . . TUITION Table . . . . . . . . . . . . . .

Database Design Guide

7

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. 243 . 243 . 244 . 246 . 248 . 248 . 249 . 249 . 250 . 250 . 251 . 251 . 253 . 253 . 254

Contents

Figures 5-1

Example Restriction Clause .................................................................................... 104

7-1

Unsuccessful Insert Operation ................................................................................ 196

7-2

Unsuccessful Delete Operation............................................................................... 199

7-3

Anomaly on Self-Referencing Tables...................................................................... 200

7-4

Anomaly on Delete-Connected Cycle ..................................................................... 201

7-5

Prevention of a Delete-Connected Cycle ................................................................ 202

7-6

Multiple Paths Anomaly........................................................................................... 203

7-7

Foreign Key References ......................................................................................... 206

7-8

Relationships Among Rights ................................................................................... 209

A-1

Entity Relationships................................................................................................. 245

A-2

RI Structure in the University Database .................................................................. 246

Database Design Guide

8

Figures

Tables 2-1

Database Component Name Lengths ........................................................................28

2-2

Scalable SQL System Tables ....................................................................................30

2-3

Maximum Number of Index Segments per Data File .................................................40

4-1

Mask Characters and Special Literals for String Data Types.....................................58

4-2

Mask Characters and Special Literals for NUMERIC Data Types .............................59

4-3

Mask Characters and Special Literals for TIME and TIMESTAMP Data Types.........60

4-4

Mask Characters and Special Literals for DATE Data Type ......................................61

4-5

Mask Characters and Special Literals for Boolean Data Types .................................62

4-6

Examples of Special Literals in Number Masks .........................................................67

5-1

Boolean Operators ...................................................................................................104

5-2

Relational Condition Operators ................................................................................105

5-3

Range Condition Operators .....................................................................................105

5-4

Numeric Computations.............................................................................................113

5-5

Data Type Categories ..............................................................................................114

5-6

Integral Promotions Between Integral Operands .....................................................116

5-7

Arithmetic Operators ................................................................................................118

5-8

String Operators .......................................................................................................119

5-9

Conditional Computed Column Operators ...............................................................120

5-10

Date Computations ..................................................................................................121

5-11

Time Computations ..................................................................................................123

5-12

Aggregate Functions ................................................................................................126

Database Design Guide

9

Tables

5-13

String Scalar Functions ............................................................................................130

5-14

Numeric Scalar Functions ........................................................................................131

5-15

Date Scalar Functions ..............................................................................................143

5-16

Time Scalar Functions .............................................................................................145

5-17

CAST Function Data Type Conversions ..................................................................149

5-18

Masks and the CAST Function ................................................................................150

7-1

Database Rights.......................................................................................................208

7-2

Granting Rights ........................................................................................................209

7-3

Revoking Rights .......................................................................................................210

A-1

Tables and Columns Involved with RI ......................................................................247

Database Design Guide

10

Tables

About This Manual This manual contains information about using the Scalable SQL relational data access system to design, create, and use databases. If you are new to our products or to databases in general, read the Pervasive Products and Services manual first. Pervasive Software would appreciate your comments and suggestions about this manual. As a user of our documentation, you are in a unique position to provide ideas that can have a direct impact on future releases of this and other manuals. Please complete the User Comments form that appears at the end of this manual, and fax or mail it to Pervasive Software, or send e-mail to [email protected].

Who Should Read This Manual This manual is targeted to developers who are designing a SQL database or planning to execute SQL statements.

Database Design Guide

11

About This Manual

Organization The following list briefly describes each chapter in the manual:

‹

Chapter 1—“Relational Database Design” This chapter introduces the fundamental principles of relational database design. A thorough database design throughout the development process is critical to successful database functionality and performance.

‹

Chapter 2—“Creating a Database” This chapter explains how to create a database by creating the data dictionary and creating the database’s tables, columns, and indexes.

‹

Chapter 3—“Inserting and Deleting Data” This chapter explains how to add data to a database using either Scalable SQL applications or SQL statements. It also explains how to drop (delete) rows, indexes, columns, or tables from your database or drop an entire database when you no longer need it.

‹

Chapter 4—“Modifying Data” This chapter explains how to modify table definitions, column attributes, and data. You can perform these tasks by entering SQL statements using an interactive application.

‹

Chapter 5—“Retrieving Data” This chapter discusses how you can use SELECT statements to retrieve data.

Database Design Guide

12

About This Manual

‹

Chapter 6—“Storing Logic” This chapter explains how to store SQL procedures for future use and how to create SQL triggers.

‹

Chapter 7—“Managing Data” This chapter discusses defining relationships among tables, administering database security, and controlling concurrency with transactions. This chapter also discusses atomicity in Scalable SQL databases.

‹

Appendix A—“University Database Tables and Referential Integrity” This appendix describes the design of the tables in the university sample database.

This manual also includes an Index.

Database Design Guide

13

About This Manual

Conventions Unless otherwise noted, command syntax, code, and code examples use the following conventions: Case

Commands and reserved words typically appear in uppercase letters. Unless the manual states otherwise, you can enter these items using uppercase, lowercase, or both. For example, you can type MYPROG, myprog, or MYprog.

[ ]

Square brackets enclose optional information, as in [log_name]. If information is not enclosed in square brackets, it is required.

|

A vertical bar indicates a choice of information to enter, as in [file name | @file name].

< >

Angle brackets enclose multiple choices for a required item, as in /D = <5|6|7>.

variable

Words appearing in italics are variables that you must replace with appropriate values, as in file name.

...

An ellipsis following information indicates you can repeat the information more than one time, as in [parameter ...].

::=

The symbol ::= means one item is defined in terms of another. For example, a::=b means the item a is defined in terms of b.

Database Design Guide

14

About This Manual

chapter

1

Relational Database Design

This chapter introduces the fundamental principles of relational database design. A thorough database design throughout the development process is critical to successful database functionality and performance. The Pervasive Software University sample database is provided as part of Pervasive.SQL 7.0 and is frequently used in the documentation to illustrate database concepts and techniques. For an overview of the structure and components of a relational database, refer to the Pervasive Products and Services manual. For definitions of basic relational database concepts, such as table, row, and column, refer to the glossary provided in the Pervasive.SQL User’s Guide.

Database Design Guide

15

Relational Database Design

Stages of Design Once you understand the basic structure of a relational database, you can begin the database design process. Designing a database is a process that involves developing and refining a database structure based on the requirements of your business. Database design includes the following three stages: 1. Conceptual Database Design 2. Logical Database Design 3. Physical Database Design

Conceptual Design The first step in the database design cycle is to define the data requirements for your business. Answering these types of questions helps you define the conceptual design:

‹ ‹ ‹ ‹ ‹ ‹ ‹

What types of information does my business currently use? What types of information does my business need? What kind of information do I want from this system? What are the assumptions on which my business runs? What are the restrictions of my business? What kind of reports do I need to generate? What will I do with this information?

Database Design Guide

16

Relational Database Design

‹ ‹

What kind of security does this system require? What kinds of information are likely to expand?

Identifying the goals of your business and gathering information from the different sources who will use the database is an essential process. With this information you can effectively define your tables and columns.

Logical Design Logical database design helps you further define and assess your business’s information requirements. Logical database design involves describing each piece of information you need to track and the relationships among those pieces of information. Once you create a logical design, you can verify with the users of the database that the design is complete and accurate. They can determine if the design contains all of the information that must be tracked and that it reflects the relationships necessary to comply with the rules of your business. Creating a logical database design includes the following steps: 1. Define the tables you need based on the information your business requires (as determined in the conceptual design). 2. Determine the relationships between the tables. (See the section “Table Relationships” for more information.) 3. Determine the contents (columns) of each table. 4. Normalize the tables to at least the third normal form. (See the section “Normalization” for more information.) 5. Determine the primary keys. (See the “Keys” section for more information.) 6. Determine the values for each column. Database Design Guide

17

Relational Database Design

Table Relationships In a relational database, tables relate to one another by sharing a common column. This column, existing in two or more tables, allows you to join the tables. There are three types of table relationships: one-to-one, one-to-many, and many-to-many. A one-to-one relationship exists when each row in one table has only one related row in a second table. For example, the Pervasive Software University may decide to assign one faculty member to one room. Thus, one room can only have one instructor assigned to it at a given time. The university may also decide that a department can only have one Dean. Thus, only one individual can be the head of a department. A one-to-many relationship exists when each row in one table has many related rows in another table. For example, one instructor can teach in many classes. A many-to-many relationship exists when a row in one table has many related rows in a second table. Likewise, those related rows have many rows in the first table. A student can enroll in many courses, and courses can contain many students.

Normalization Normalization is a process that reduces redundancy and increases stability in your database. Normalization involves determining in which table a particular piece of data belongs and its relationship to other data. Your database design results in a data-driven, rather than process or application-driven, design which provides a more stable database implementation. When you normalize your database, you eliminate the following columns:

‹ ‹

Columns that contain more than one non-atomic value. Columns that duplicate or repeat.

Database Design Guide

18

Relational Database Design

‹ ‹ ‹

Columns that do not describe the table. Columns that contain redundant data. Columns that can be derived from other columns.

First Normal Form Columns in the first normal form have the following characteristics:

‹ ‹

They contain only one atomic value. They do not repeat.

The first rule of normalization is that you must remove duplicate columns or columns that contain more than one value to a new table. Tables normalized to the first normal form have several advantages. For example, in the Billing table of the sample database, first normal form does the following:

‹ ‹ ‹

Allows you to create any number of transactions for each student without having to add new columns. Allows you to query and sort data for transactions quickly because you search only one column (transaction number). Uses disk space more efficiently because no empty columns are stored.

Second Normal Form A table is in the second normal form when it is in the first normal form and only contains columns that provide information about the key of the table.

Database Design Guide

19

Relational Database Design

In order to enforce the second rule of normalization, you must move those columns that do not depend on the primary key of the current table to a new table. A table violates second normal form if it contains redundant data. This may result in inconsistent data which causes your database to lack integrity. For example, if a student changes her address, you must then update all existing rows to reflect the new address. Any rows with the old address result in inconsistent data. To resolve these differences, identify data that remains the same when you add a transaction. Columns like Student Name or Street do not pertain to the transaction and do not depend on the primary key, Student ID. Therefore, store this information in the Student table, not in the transaction table. Tables normalized to the second normal form also have several advantages. For example, in the Billing table of the sample database, second normal form allows you to do the following:

‹ ‹ ‹

Update student information in just one row. Delete student transactions without eliminating necessary student information. Use disk space more efficiently since no repeating or redundant data is stored.

Third Normal Form A table is in the third normal form when it contains only independent columns. The third rule of normalization is that you must remove columns that can be derived from existing columns. For example, for a student, you do not have to include an Age column if you already have a Date of Birth column, because you can calculate age from a date of birth.

Database Design Guide

20

Relational Database Design

A table that is in third normal form contains only the necessary columns, so it uses disk space more efficiently since no unnecessary data is stored. In summary, the rules for the first, second, and third normal forms state that each column value must be a fact about the primary key in its entirety, and nothing else.

Keys A Scalable SQL key is a column or group of columns on which a table’s referential integrity (RI) constraints are defined. In other words, a key or combination of keys acts as an identifier for the data in a row. For more information about referential integrity and keys, refer to Chapter 7, “Managing Data.”

Physical Design The physical database design is a refinement of the logical design; it maps the logical design to a relational database management system. In this phase, you examine how the user accesses the database. This step of the database design cycle involves determining the following types of information:

‹ ‹ ‹ ‹

Data you will commonly use. Columns requiring indexes for data access. Areas needing flexibility or room for growth. Whether denormalizing the database will improve performance. (To denormalize your database, you reintroduce redundancy to meet performance.) For more information about normalization, refer to the section “Normalization” earlier in this chapter.

Database Design Guide

21

Relational Database Design

chapter

2

Creating a Database

A Scalable SQL database consists of two basic parts:

‹ ‹

A data dictionary that describes the data. Data files that physically contain your data.

This chapter explains named databases, bound databases, and how to create a database by creating the data dictionary and creating the database’s tables, columns, and indexes in the following sections:

‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹

“Named Databases” “Bound Databases” “Creating Database Components” “Naming Conventions” “Creating a Data Dictionary” “Creating Tables” “Creating Columns” “Creating Indexes”

Database Design Guide

22

Creating a Database

Named Databases A named database has a logical name that allows users to identify it without knowing its actual location. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths. When you log in to Scalable SQL using a database name, Scalable SQL uses the name to find the database’s dictionary and data files. Unless your database is named, you cannot do the following:

‹ ‹ ‹ ‹

Define triggers Define primary and foreign keys Bind a database Suspend a database’s integrity constraints

You use the Setup utility to name existing, unbound databases and to create new, bound databases. Refer to the Pervasive.SQL User’s Guide for more information.

Database Design Guide

23

Creating a Database

Bound Databases Binding a database ensures that the MicroKernel enforces the database’s defined security, referential integrity (RI), and triggers, regardless of the method you use to access the data. The MicroKernel enforces these integrity controls as follows:

‹ ‹ ‹

When you define security on a bound database, Btrieve users cannot access it. When you define security on an unbound database, Btrieve users can access it. When no security is defined on a bound database, Btrieve users can access the data files as follows: Bound File’s Constraint

Level of Access Using Btrieve

RI constraints defined

User can access and do anything within RI constraints

INSERT triggers defined

Read-only, update, and delete access

UPDATE triggers defined

Read-only, insert, and delete access

DELETE triggers defined

Read-only, insert, and update access

If more than one constraint exists on the bound file, the access level follows the most restrictive constraint. For example, if a file has both INSERT and UPDATE triggers defined, then you have read-only and delete access. Note

Even if you do not bind your database, Scalable SQL automatically stamps a data file as bound if it has a trigger, has a foreign key, or has a primary key that is referenced by a foreign key. Thus, a data file may be part of an unbound database, but be bound. In such cases, the MicroKernel enforces integrity constraints on the file as if it were part of a bound database.

Database Design Guide

24

Creating a Database

The dictionary and data files in a bound database cannot be referenced by other named databases. Also, bound data files cannot be referenced by other tables in the database. When you create a bound database or bind an existing database, Scalable SQL stamps every dictionary and data file with the name of the bound database. Also, Scalable SQL stamps every data file with the name of the table associated with that data file. In addition, when you add new tables or dictionary files to the database, Scalable SQL automatically binds them.

Creating Database Components To create a new database with Scalable SQL, you first create the dictionary (using the CREATE DICTIONARY statement), and then issue CREATE TABLE statements for each table you want to create. When you issue a CREATE TABLE statement, you must include commands that define columns. In addition, you can include commands that define indexes or referential integrity (RI) constraints. You can also use DDF Ease to create dictionaries and database. Refer to the DDF Ease on-line help for complete instructions on using this utility or refer to the Pervasive.SQL User’s Guide.

Database Design Guide

25

Creating a Database

Naming Conventions When you create a database, Scalable SQL allows you to assign a descriptive name to each database component. Users and applications refer to the components of the database using these names. This section outlines the conventions to which you must adhere when naming database components.

Unique Names The following database components must have unique names within a dictionary:

‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹

Tables Views Indexes Keys User names Group names Stored procedures Triggers Column names within a single table

Database Design Guide

26

Creating a Database

Names for parameters and substitution variables must be unique within a SQL statement. Because Scalable SQL keywords are reserved words, you cannot use them for naming database components or in parameter names and substitution variables. See the SQL Language Reference for a list of reserved keywords. When a column name is repeated in different tables, you can qualify it in each table by preceding it with the relevant table name or alias name. For example, you can refer to the ID column in the Student table as Student.ID. This is a fully qualified column name, and the table name (Student) is the column qualifier.

Valid Characters Following are the valid characters for the names of database components at the SQL level, and for substitution variables and parameter names:

‹ ‹ ‹ ‹ ‹ ‹ ‹

a through z A through Z 0 through 9 _ (underscore) ^ (caret) ~ (tilde) $ (dollar sign)

Database Design Guide

27

Creating a Database

Note

The name of a database component must begin with a letter. If you specify the name of a database component or a parameter name that does not follow these conventions, specify the name in double quotes (such as “name”).

Maximum Name Lengths Scalable SQL restricts the maximum length of database component names in a dictionary. Table 2-1 lists the maximum name lengths for each type of database component.

Table 2-1

Database Component Name Lengths

Type of Name

Maximum Length

Type of Name

Maximum Length

Table

20

Password

8

View

20

Database

20

Column

20

Stored Procedure

30

Index

20

Trigger

30

Foreign key

20

Substitution variable

None

User or group

30

Data file pathname

641

1 The maximum length of the data file pathname is a combination of the Xf$Loc path and the data file path.

Database Design Guide

28

Creating a Database

Case-Sensitivity Scalable SQL is case-sensitive when you are defining database component names. If you create a table named TaBLe1, Scalable SQL stores the table name in the dictionary as TaBLe1. With the exception of user names, user group names, and passwords, Scalable SQL is case-insensitive after you define the component name. After defining the table TaBLe1, you can refer to it as table1. User names, user group names, and passwords are case-sensitive in Scalable SQL. For example, when you log in as the master user, you must specify the user name as Master. When retrieving data, Scalable SQL displays names for tables, views, aliases, and columns based on the way you specify the name in the query. If you use * to specify column names, Scalable SQL returns the names in all uppercase: SELECT * FROM Course; Scalable SQL returns the column names as follows: NAME, DESCRIPTION, CREDIT_HOURS, DEPT_NAME

Database Design Guide

29

Creating a Database

Creating a Data Dictionary Scalable SQL uses the dictionary to store information about the database. The dictionary consists of several system tables that describe the tables and views of your database. The system tables contain several types of database information, including table and index definitions, column characteristics, and integrity and security information. Table 2-2 describes the system tables Scalable SQL creates.

Table 2-2

Scalable SQL System Tables

Operation

Resulting Table

Create a data dictionary

X$File, X$Field, X$Index

Specify column attributes

X$Attrib

Create stored SQL procedures

X$Proc

Define database security

X$User, X$Rights

Define referential constraints

X$Relate

Define views

X$View

Define triggers

X$Trigger, X$Depend

Because the system tables are part of the database, you can query them to determine their contents. If you have the appropriate rights, you can also create system tables or change their contents.

Database Design Guide

30

Creating a Database

Note

Scalable SQL does not display some data in the system tables. For example, information about stored views and procedures, other than their names, is available only to Scalable SQL. In addition, some data (such as user passwords) displays in encrypted form.

For a complete reference to the contents of each system table, refer to the SQL Language Reference. Once you have created a dictionary, you can add tables, columns, and indexes to your database.

➤

To create a named database, perform the following steps. Note

You must have named databases in order to use some features, such as referential integrity and triggers. 1. Create a directory in which to store the new dictionary tables. 2. Use the Setup utility to add a Named Database. Refer to the Pervasive.SQL User’s Guide for more information.

➤

To create a dictionary for an unnamed database, perform the following steps: 1. Create a directory in which to store the new dictionary tables. 2. Log in to an existing database (for example, the sample university database included with Scalable SQL) using an application that supports Scalable SQL, such as SQLScope.

Database Design Guide

31

Creating a Database

3. Issue a CREATE DICTIONARY statement, specifying the directory you created in Step 1 (such as F:\DemoDict). CREATE DICTIONARY USING ‘f:\DemoDict\; or CREATE DICTIONARY USING ‘\\MyServer\Sys:DemoDict’; or CREATE DICTIONARY USING ‘\\MyServer\Vol1\DemoDict’; Scalable SQL creates dictionary tables to store the X$File, X$Field, and X$Index system tables in the directory you specify. 4. Log out of the database.

Database Design Guide

32

Creating a Database

Creating Tables When you create a table, you must name it. Each table name must be unique within a database and cannot exceed 20 characters. For more information about rules for naming tables, refer to the section "Naming Conventions." When you are deciding which tables to create in your database, consider that different users can look at data in different combinations using views. A view looks like a table and can be treated as a table for most purposes (such as retrieving, updating, and deleting data). However, a view is not necessarily associated with a single table; it can combine information from multiple tables. For more information, refer to "Views." To create a table, issue a CREATE TABLE statement, as in the following example: CREATE TABLE Department USING 'dept.mkd' (Name CHAR(10), Phone_Number NUMERIC(10), Building_Name CHAR(8), Room_Number FLOAT(4), Head_of_Department NUMERIC(9)); In this example, Department is the table name, and DEPT.MKD is the data file name. This example does not create indexes or define referential constraints. You can add additional columns, indexes, and RI constraints to the table later. The next section, “Creating Columns” provides more information about creating columns. In addition, this example explicitly specifies the name of the data file to create. The data file name stored in the dictionary cannot exceed 64 characters. Also, the combination of the data file name and the directory path for the data file cannot exceed 80 characters. You specify the directory path when logging in with pathnames or when setting up a

Database Design Guide

33

Creating a Database

database name with the Setup utility. For more information about data file pathnames, refer to the SQL Language Reference. You can omit the filename if you want Scalable SQL to create it for you. If Scalable SQL creates the data file name, it generates a unique filename with a .MKD extension.

Aliases You can assign aliases (also called alias names) to table names in the following elements of statements:

‹ ‹ ‹

FROM clause of a SELECT or DELETE statement. INTO clause of an INSERT statement. List of tables in an UPDATE statement. Note

Aliases apply only to the statement in which you use them. Scalable SQL does not store them in the data dictionary.

An alias can be any combination of up to 20 characters. Always separate the table name from the alias with a blank. Separate the alias and the column name with a period (.). Once you specify an alias for a particular table, you can use it elsewhere in the statement to qualify column names from that table. The following example specifies the alias name s for the table Student and e for the table Enrolls. SELECT s.ID, e.Grade FROM Student s, Enrolls e WHERE s.ID = e.Student_ID;

Database Design Guide

34

Creating a Database

You can use an alias to do the following:

‹

Replace long table names. When you are working interactively, using aliases can save typing time, especially when you need to qualify column names. For example, the following statement assigns s as the alias for the Student table, e for the Enrolls table, and c1 for the Class table. This example uses aliases to distinguish the source of each column in the selection list and in the WHERE conditions. SELECT s.ID, e.Grade, c1.ID FROM Student s, Enrolls e, Class c1 WHERE (s.ID = e.Student_ID) AND (e.Class_ID = c1.ID);

‹ ‹

Make a statement more readable. Even in statements with only a single table name, an alias can make the statement easier to read. Use the table in the outer query in a correlated subquery. SELECT s.ID, e.Grade, c1.ID FROM Student s, Enrolls e, Class c1 WHERE (s.ID = e.Student_ID) AND (e.Class_ID = c1.ID) AND e.Grade >= (SELECT MAX (e2.Grade) FROM Enrolls e2 WHERE e2.Class_ID = e.Class_ID);

Database Design Guide

35

Creating a Database

Creating Columns You create columns when you create a table using a CREATE TABLE statement, or you can add columns to an existing table using an ALTER TABLE statement. In either case, you must specify the following characteristics:

‹

‹ ‹

Column name—identifies the column. Each column name must be unique within a table and the column name cannot exceed 20 characters. Scalable SQL is case-sensitive when defining database column names, but case-insensitive after you define the column name. For example, if you create a column named ColuMN1, the name is stored in the dictionary as ColuMN1; subsequently, you can refer to it as column1. For more information about rules for naming columns, refer to the section "Naming Conventions." Data type—identifies the kind of data to expect, such as a string of characters or a number, and how much disk storage space to allocate. Length—indicates how much disk storage space to allocate. For some numeric data types, you must also specify the number of decimal places. All the Scalable SQL data types are fixed-length except NOTE and LVAR, which are variable length. For instance, the maximum column length for LVAR is 32,761 bytes.

For more information about data types, see the SQL Language Reference.

Database Design Guide

36

Creating a Database

Creating Indexes Indexes optimize operations that either search for or order by specific values. Define indexes for any columns on which you frequently perform either of these operations. Indexes provide a fast retrieval method for a specific row or group of rows in query optimization. Scalable SQL also uses indexes with referential integrity (RI). Indexes improve performance on joins and help to optimize queries. For more information about RI, see Chapter 7, “Managing Data.” In Scalable SQL databases, the MicroKernel creates and maintains indexes as part of the physical file for which they are defined. The MicroKernel performs all maintenance for Insert, Update, or Delete operations. These activities are transparent to any Scalable SQL or Btrieve application. To create an index when you create a table, use the WITH INDEX clause of the CREATE TABLE statement. This method creates an unnamed index. You cannot delete unnamed indexes after you create them. To create an index after you create the table, use a CREATE INDEX statement. This method creates a named index. You can delete named indexes after you create them. For more information about dropping indexes, refer to Chapter 3, “Inserting and Deleting Data.” While indexes allow you to sort rows and retrieve individual rows quickly, they increase the disk storage requirements for a database and decrease performance somewhat on Insert, Update, and Delete operations. You should consider these trade-offs when defining indexes.

Database Design Guide

37

Creating a Database

The following example instructs Scalable SQL to create an index when creating the Course table: CREATE TABLE Course USING 'course.mkd' (Name CHAR(7) CASE, Description CHAR(50) CASE, Credit_Hours UNSIGNED(2), Dept_Name CHAR(20)) WITH INDEX (Name UNIQUE CASE, Dept_Name CASE MOD); The next example uses a CREATE INDEX statement to add an index to a table that already exists: CREATE INDEX DeptHours ON Course (Dept_Name CASE MOD SEG, Credit_Hours MOD); Note

Be aware that if you use the CREATE INDEX statement on files that contain a lot of data, execution could take some time to complete, and other users may not be able to access data in that file in the meantime.

For detailed information about the CREATE TABLE and CREATE INDEX statements, refer to the SQL Language Reference.

Database Design Guide

38

Creating a Database

Index Segments You can create an index on any single column or group of columns in the same table. An index that includes more than one column is called a segmented index, in which each column is called an index segment. Note

You cannot define an index on a column of type BIT, LVAR, or NOTE. For more information about segmented indexes and AUTOINC, refer to the SQL Language Reference.

For example, the Person table in the sample database has the following three indexes:

‹ ‹ ‹

A segmented index consisting of the Last Name column and the First Name column. The Perm_State+Perm_City column. The ID column.

By default, Scalable SQL can create 119 key segments. The number is affected by the MicroKernel Database Engine page size of the data file. See the SQL Language Reference for more information on how to use the PAGESIZE keyword. The maximum number of indexes you can create for a table depends on the page size of its data file and the number of segments in each index. As Table 2-3 shows, data files with page sizes smaller than 4096 bytes cannot contain as many index segments as a data file with a page size of 4096.

Database Design Guide

39

Creating a Database

Table 2-3

Maximum Number of Index Segments per Data File

Page Size

Maximum Number of Index Segments

512

8

1024

23

1536

24

2048

54

2560

54

3072

54

3584

54

4096

119

Using the page size and fixed record length, you can calculate the efficiency with which data is being stored (such as the number of wasted bytes per page). By having fewer records per page, you can improve concurrency where page-level locking is concerned. By default, Scalable SQL creates all tables with a page size of 4096 bytes. However, you can specify a smaller page size using the PAGESIZE keyword in a CREATE TABLE statement, or you can create a table using the MicroKernel Database Engine and specify a smaller page size for that table. When calculating the total number of index segments defined for a table, a nonsegmented index counts as one index segment. For example, if your table has three indexes defined, one of which has two segments, the total number of index segments is four. Database Design Guide

40

Creating a Database

You can use the SQLScope utility to display the number of defined index segments and the page size of a data file. For information about this utility, see the Pervasive.SQL User’s Guide.

Index Attributes When you create an index, you can assign to it a set of qualities, or attributes. Index attributes determine the modifiability of the index and how Scalable SQL sorts the indexes you define for a table. You can include parameters specifying index attributes anytime you create or alter an index definition. Indexes can have the following attributes: Case-sensitivity

Determines how Scalable SQL evaluates uppercase and lowercase letters during sorting. By default, Scalable SQL creates casesensitive indexes. To create a case-insensitive index, specify the CASE keyword when you create the index.

Sort order

Determines how Scalable SQL sorts index column values. By default, Scalable SQL sorts index column values in ascending order (from smallest to largest). To create an index that sorts in descending order, specify the DESC keyword when you create the index.

Uniqueness

Determines whether Scalable SQL allows multiple rows to have the same index column value. By default, Scalable SQL creates nonunique indexes. To create an index that requires unique values, specify the UNIQUE keyword when you create the index.

Modifiability

Determines whether you can modify index column values after Scalable SQL stores the corresponding row. By default, Scalable SQL does not allow changes to index column values once Scalable SQL stores the row. To create a modifiable index, specify the MOD keyword when you create the index.

Database Design Guide

41

Creating a Database

Null value indexing Determines whether Scalable SQL includes columns that contain the predefined null value in the index structure. By default, Scalable SQL includes null values in the index structure. To create an index so that Scalable SQL does not include null values, specify the NULL keyword when you create the index. Segmentation

Indicates whether the index is segmented (whether it consists of a group of columns combined into a single index). By default, Scalable SQL creates indexes that are not segmented. To create a segmented index using the CREATE TABLE statement, specify the SEG keyword for each index segment you create, except the last segment in the index. (The SEG keyword indicates that the next column specified is a segment of the index you are creating.) Because you can create only one index at a time with the CREATE INDEX command, you do not need to use the SEG keyword to specify a segmented index. If you specify more than one column, Scalable SQL creates a segmented index using the columns in the order in which you specify them.

Uniqueness and modifiability apply only to entire indexes. You cannot apply uniqueness or modifiability to a single index segment without applying it to the entire index. For example, if you create a segmented index and specify the MOD keyword for one of the index segments, you must specify the MOD keyword for every segment. In contrast, you can apply case-sensitivity, sort order, null value indexing, and segmentation to individual index segments without affecting the entire index. For example, you can create a case-insensitive index segment in an otherwise case-sensitive index.

Database Design Guide

42

Creating a Database

chapter

3

Inserting and Deleting Data

After creating a data dictionary, tables, and columns, you can add data to the database using either Scalable SQL applications or SQL statements. SQL statements allow you to do the following:

‹ ‹

Specify literal values to insert. Select data from other tables and insert the resulting values into entire rows or specified columns.

When you insert a literal value, it must conform to the specified column’s data type, length, and edit mask. You can drop (delete) rows, indexes, columns, or tables from your database. In addition, you can drop an entire database when you no longer need it. This chapter includes the following sections:

‹ ‹ ‹ ‹ ‹ ‹ ‹

“Inserting Values” “Transaction Processing” “Deleting Data” “Dropping Indexes” “Dropping Columns” “Dropping Tables” “Dropping an Entire Database”

Database Design Guide

43

Inserting and Deleting Data

Inserting Values You can use a VALUES clause in an INSERT statement to specify literal values to insert into a table. The following example inserts a new row into the Course table of the sample database: INSERT INTO Course VALUES ('ART 103', 'Principles of Color', 3, 'Art'); In this example, listing the columns Name, Description, Credit_Hours, and Dept_Name is optional because the statement inserts a value for each column in the table, in order. However, a column list is required if the statement inserted data only into selected columns instead of the entire row, or if the statement inserted data into the columns in a different order than is defined in the table. Note

If you do not specify a value for a column of DATE, TIME, or TIMESTAMP, Scalable SQL does not insert the current date or time by default. To insert the current date or time, use the CURDATE and CURTIME variables as values.

You can insert a constant into a column by placing it in the VALUES clause of an INSERT statement. If the data type of the column into which you are inserting is DATE, TIME, or TIMESTAMP, then this constant must conform to the mask defined on the column into which you are inserting the data. For all other data types, the constant must conform either to the defined mask on the column or the default mask for the data type of the column. For more information about the role of data types and defined or default masks, or about using the INSERT statement, refer to the SQL Language Reference.

Database Design Guide

44

Inserting and Deleting Data

Inserting Values from Other Tables You can use a SELECT clause in an INSERT statement to select values from one table and insert them into another table. The following example populates a new table, InactiveStudents, with data selected from the sample database tables Student and Tuition. CREATE TABLE InactiveStudents USING 'Inact.mkd' (Student_ID UNSIGNED(8), Major CHAR(20) CASE, Degree LSTRING(5)); INSERT INTO InactiveStudents (Student_ID, Major, Degree) SELECT ID, Major, Degree FROM Student s, Tuition t WHERE s.tuition_id = t.id AND s.id NOT IN (SELECT Student_ID FROM Enrolls); In this example, Scalable SQL selects the values for the Student_ID, Major, and Degree columns based on restriction criteria that utilizes the Enrolls table. Scalable SQL then inserts these values into the corresponding fields of the new table, InactiveStudents. When inserting rows that are the result of a SELECT statement, the data types of the columns in the view defined by the SELECT statement must be compatible with the data types of the columns into which you are inserting the data. For more information, refer to the SQL Language Reference.

Database Design Guide

45

Inserting and Deleting Data

Transaction Processing When you attempt to insert data into a table, Scalable SQL returns an error if the data is invalid. Any data inserted before the error occurred is rolled back. This enables your database to remain in a consistent state. You can use transaction processing in a Scalable SQL database to group a set of logically related statements together. Within a transaction, you can use savepoints to effectively nest transactions; if a statement in a nesting level fails, then the set of statements in that nesting level is rolled back to the savepoint. Refer to Chapter 7, “Managing Data,” for information about transaction processing and savepoints.

Database Design Guide

46

Inserting and Deleting Data

Deleting Data There are two types of DELETE statements: positioned and searched. You can use a DELETE statement to delete one or more rows from a table or an updatable view. To specify specific rows for Scalable SQL to delete, use a WHERE clause in a DELETE statement. DELETE FROM Class WHERE ID = '005'; The Positioned DELETE statement deletes the current row of a view associated with an open SQL cursor. DELETE WHERE CURRENT OF mycursor; For more information about SQL cursors, refer to "SQL Cursor-Based Statements." For more information about the syntax of these statements, refer to the SQL Language Reference.

Database Design Guide

47

Inserting and Deleting Data

Dropping Indexes If you find that you no longer need a named index, use a DROP INDEX statement to drop it. DROP INDEX DeptHours; You cannot drop unnamed indexes (those you created using a WITH INDEX clause in a CREATE TABLE statement).

Dropping Columns To drop a column from a table, use an ALTER TABLE statement. ALTER TABLE Faculty DROP Rsch_Grant_Amount; This example drops the Rsch_Grant_Amount column from the Faculty table and deletes the column definition from the data dictionary. Note

Be aware that if you use the ALTER TABLE statement on files that contain a lot of data, execution could take some time to complete, and other users may not be able to access data in that file in the meantime.

Database Design Guide

48

Inserting and Deleting Data

Dropping Tables To drop a table from the database, use a DROP TABLE statement. DROP TABLE InactiveStudents; This example drops the InactiveStudents table definition from the data dictionary and deletes its corresponding data file (INACT.MKD). Note

You cannot drop any system tables. Refer to the SQL Language Reference for a complete listing of system tables.

Database Design Guide

49

Inserting and Deleting Data

Dropping an Entire Database When you no longer need a particular database, you can drop it using a DROP DICTIONARY statement, which deletes all the data dictionary and data files for the database. You cannot be logged in to the database you are attempting to drop, but you must be logged in to another database. The following example deletes the sample database: DROP DICTIONARY USING 'f:\demodict'; Note

You cannot drop bound databases.

If the database you drop is unbound but named, delete the database name; otherwise, users may try to log in to a database that no longer exists. You can use the Setup utility to delete a database name. For more information, refer to the Pervasive.SQL User’s Guide.

Database Design Guide

50

Inserting and Deleting Data

chapter

4

Modifying Data

After creating a database, you can modify it as follows:

‹ ‹ ‹

After creating tables, you can modify the table definitions. After creating columns, you can set optional column attributes. After adding data to the database, you can modify the data.

You can perform these tasks using either SQL statements or a Scalable SQL application. For information about interactive applications, refer to the Pervasive.SQL User’s Guide. For more information about SQL statements, refer to the SQL Language Reference. This chapter contains the following sections:

‹ ‹ ‹

Modifying Tables Setting Column Attributes Using UPDATE

Database Design Guide

51

Modifying Data

Modifying Tables You can use an ALTER TABLE statement to modify a table definition after creating the table. ALTER TABLE statements allow you to add, modify, or drop columns; add or drop primary and foreign keys; and change the pathname of a table’s data file. The following example adds a 1-byte logical column called Residency to the Tuition table in the sample database. ALTER TABLE Tuition ADD Residency LOGICAL(1); The following example changes the data file name of the Person table in the sample database. ALTER TABLE Person IN DICTIONARY USING 'person.mkd'; Note

This example changes only the data dictionary. It does not rename the data file. To rename the data file, use operating system commands. For more information about columns, refer to Chapter 2, “Creating a Database.” For more information about primary and foreign keys, refer to Chapter 7, “Managing Data.”

Database Design Guide

52

Modifying Data

Setting Column Attributes You can set the following optional column attributes for a column using a SET DEFAULT, SET VALUES, SET RANGE, SET CHAR, or SET MASK statement.

‹ ‹ ‹ ‹

Default value—A value that Scalable SQL stores in the column if you do not specify a value. Valid values—Specifies the values Scalable SQL allows you to store in the column. Null value—Specifies the value you can use to indicate that the information that is normally stored in the column is unavailable or irrelevant. Edit mask —Indicates how Scalable SQL displays the column. Some edit masks actually determine the value you specified; others just describe the format.

If you specify attributes on existing data, Scalable SQL does not detect values that are already outside these ranges. The values in SET DEFAULT, SET VALUES, and SET RANGE statements must match either the column’s user-defined edit mask or the column’s data type default mask, except in the case of types DATE, TIME, and TIMESTAMP. The values must match the column’s user-defined edit mask for columns of type DATE, TIME, and TIMESTAMP; if the column has no user-defined edit mask, then the values must match the column’s data type default mask.

Setting Default Values Scalable SQL inserts a default value if you insert a row but do not provide a value for that column. Default values ensure that each row contains a valid value for the column.

Database Design Guide

53

Modifying Data

In the Person table of the sample database, all students live in a state. Setting a default value such as TX for the State column ensures that the most probable value is always entered for that column. To set a default value for a column, use a SET DEFAULT statement: SET DEFAULT State = 'TX';

Setting Valid Values Scalable SQL lets you limit the type of information you can insert into a column. Once you set these limits, Scalable SQL validates the data when you attempt to insert or update a row and returns an error if the data conflicts with these limits. Scalable SQL allows you to set the following types of limits:

‹ ‹ ‹

Ranges Character lists Value lists

Ranges You can specify the minimum and maximum values that a column can accept. You can also set multiple ranges of values for a column. For example, you can specify that a column named Grade accepts values only from 0.0 through 4.0. When you insert a new row or update an existing row, Scalable SQL returns an error if the new column value is not within the specified range. To set a data range for a column, use a SET RANGE statement, as in the following example: SET RANGE Grade = 0.0 - 4.0;

Database Design Guide

54

Modifying Data

Character Lists A character list allows you to specify individual characters and ranges of characters as valid input for a string column. If you set a character list, Scalable SQL allows you to insert or update data only if each character in the column value is included in the character list. To set a character list for a column, use a SET CHAR statement. SET CHAR Person.Zip = '0'-'9','-';

Value Lists A value list allows you to specify all acceptable values for a column. For example, a value list for the column recording faculty designations includes PROF, ASST PROF, and ASSOC PROF. To set a value list for a column, use a SET VALUES statement. SET VALUES Designation = 'PROF','ASST PROF','ASSOC PROF';

Setting Null Values A null value in a column indicates that the corresponding data is unavailable or does not exist. For example, assume the Minor column of a Student row contains a null value. Most likely, either the student’s minor was inadvertently omitted from the database, or the student has not declared a minor. Scalable SQL allows you to set a null value for a particular group of data types. You cannot set a null value for a specific column, only for all columns of a certain group of data types. Also, Scalable SQL does not store the setting in the dictionary; the null value you specify is in effect only for the current login session. When you set a null value for a data type, Scalable SQL inserts that null value into any column that has neither a default value nor a value that the application assigned. Database Design Guide

55

Modifying Data

Scalable SQL provides predefined null values for each data type. This null value is used as a fill byte for any value in its category. However, it is usually preferable to set a default value for a data type rather than use a null value, because a default value is meaningful and maintains the logical integrity of your database. Depending on the keyword you use with the SET statement, Scalable SQL sets the null value as follows:

‹ ‹ ‹

SET STRINGNULL sets a null value for all columns of data type CHARACTER. SET DECIMALNULL sets a null value for all columns of data type CURRENCY, DECIMAL, MONEY, NUMERIC, NUMERICSA, and NUMERICSTS. SET BINARYNULL sets a null value for all columns of type AUTOINC, BFLOAT, DATE, INTEGER, FLOAT, LOGICAL, LSTRING, TIME, TIMESTAMP, UNSIGNED, and ZSTRING.

You cannot set a null value for LVAR or NOTE columns. Note

Each session must specify these null values to indicate how Scalable SQL should interpret data values. Otherwise, the session may misinterpret the null value of the data in the database.

The following example instructs Scalable SQL to insert an asterisk into all bytes of a character column if the column has no default value and you do not specify a value. SET STRINGNULL = '*';

Database Design Guide

56

Modifying Data

Specifying Edit Masks An edit mask specifies the way Scalable SQL displays specific columns when you retrieve them (in some cases, edit masks determine how you insert values into columns). For example, you can choose to separate groups of three digits with a comma or display negative numbers inside parentheses. You can also use edit masks to specify the display length for CHARACTER, LSTRING, and ZSTRING columns. In addition to using edit masks to format displays, you can use masks to format input values when inserting data for the AUTOINC, BFLOAT, BIT, CURRENCY, DATE, DECIMAL, FLOAT, INTEGER, LOGICAL, MONEY, NUMERIC, NUMERICSA, NUMERICSTS, TIME, TIMESTAMP, and UNSIGNED data types. You specify an edit mask using the SET MASK or SELECT statements. Masks do not describe the internal format for data types. Scalable SQL sets the internal format, as explained in the SQL Language Reference. You can specify an edit mask for a column of any data type except LVAR or NOTE. The rules for edit masks vary depending on a column’s data type. Each type uses reserved characters that affect a column’s appearance. Masks cannot exceed 30 characters. Scalable SQL determines the displayable format for a column in one of the following ways:

‹ ‹ ‹

If you defined a temporary edit mask in a SELECT statement, Scalable SQL uses that mask. To define a temporary mask for any column (including a computed column) in a SELECT statement, place the mask in square brackets directly following the column. If you have not specified an edit mask in the SELECT statement, Scalable SQL uses the format specified in the dictionary. You can specify a mask for a column in the dictionary using a SET MASK statement. If you did not specify an edit mask with one of the previous methods, Scalable SQL uses a default mask, which depends on the data type of the column. The SQL Language Reference describes the default mask for each data type.

Database Design Guide

57

Modifying Data

Edit Mask Literals You can also embed edit mask literals before, after, or within the data positions. An edit mask literal is any character in the mask that does not represent data and is not a special literal. It appears in your formatted data exactly as it appears in the mask. For example, the following mask uses backward slashes (/) to separate day, month, and year values in a DATE column. mm/dd/yy Each mask character you specify correlates to a single character or digit in a column value. Each special literal affects the display of a column value or portion of a column value. The following tables list the edit mask characters and special literals you can use with each Scalable SQL data type and summarizes their effect on displayed data. Table 4-1 lists the mask characters, special literals, and usage rules for string data types. These types include CHARACTER, LSTRING, and ZSTRING data types. (You cannot specify masks for LVAR and NOTE columns.) For more information about these masks, refer to the section "String Masks."

Table 4-1

Mask Characters and Special Literals for String Data Types

Mask Characters

Special Literals

Char Effect on Data

Char

Effect on Data

Rules

x

nnn

Numerals indicate number of characters displayed.

Mask must be xnnn. You must precede length numerals (nnn) with a lowercase x. Maximum length (nnn) is 255 characters.

Characters

Database Design Guide

58

Modifying Data

Table 4-2 lists the mask characters, special literals, and usage rules for numeric data types. These types include the following: AUTOINC

BFLOAT

CURRENCY

DECIMAL

FLOAT

INTEGER

MONEY

NUMERIC

NUMERICSA

NUMERICSTS

UNSIGNED

For more information about these masks, refer to the section "Number Masks."

Table 4-2

Mask Characters and Special Literals for NUMERIC Data Types

Mask Characters

Special Literals

Char

Effect on Data

Char

Effect on Data

Rules

Z

Deletes blank characters or displays zeros, depending on context.

+

Displays leading or trailing +/–.

–

Displays leading or trailing +/–.

See the section “Special Literals in Number Masks” for the rules for using these literals.

Displays 0 for each zero value.

()

Displays negative inside parentheses.

.

Scales integers; displays in actual position for others.

{}

Suppresses display of negative values.

9

Database Design Guide

59

Modifying Data

Table 4-2

Mask Characters and Special Literals for NUMERIC Data Types continued

Mask Characters

Special Literals

Char

Char

Effect on Data

Rules

H

Displays value in hexadecimal format (INTEGER, AUTOINC, and UNSIGNED data types only).

Hexadecimal masks must end with H.

E

Displays values in scientific notation. Positive or negative sign may appear before the exponent.

Effect on Data

Table 4-3 lists the mask characters, special literals, and usage rules for TIME and TIMESTAMP data types. For more information about these masks, refer to the section "Time Masks."

Table 4-3

Mask Characters and Special Literals for TIME and TIMESTAMP Data Types

Mask Characters

Special Literals

Char

Effect on Data

Char1 Effect on Data

hh

Hour digits

B

Forces or deletes a blank space.

mm

Minute digits (in TIME)

b:

Displays all blanks if column value is 0.

tt

Minute digits (in TIMESTAMP)

d:

Displays current time if column value is 0.

f

Fraction of seconds (in TIMESTAMP) z:

Database Design Guide

60

Displays all zeros if column value is 0.

Modifying Data

Table 4-3

Mask Characters and Special Literals for TIME and TIMESTAMP Data Types

Mask Characters

Special Literals

Char

Effect on Data

Char1 Effect on Data

ss

Second digits

ap:

uu

Hundredth of a second digits

B

Blank

Indicates a.m./p.m.

1 Rules: If you do not specify b: or d:, Scalable SQL uses z:. b:, d:, or z: literals must be the first characters of the edit mask. The ap: can appear at the beginning or end of the mask.

Table 4-4 lists the mask characters, special literals, and usage rules for the DATE data type. For more information about these masks, refer to the section "Date Masks."

Table 4-4

Mask Characters and Special Literals for DATE Data Type

Mask Characters

Special Literals

Char

Effect on Data

Char1 Effect on Data

mm

Month digits

B

Forces a blank space.

mmm...

Month characters, minimum of 3 (trailing blanks)

b:

Displays all blanks if column value is 0.

MMM...

Month characters, minimum of 3 (no trailing blanks)

d:

Displays current date if column value is 0.

Database Design Guide

61

Modifying Data

Table 4-4

Mask Characters and Special Literals for DATE Data Type continued

Mask Characters

Special Literals

Char

Effect on Data

Char1 Effect on Data

dd

Day digits

z:

www...

Day characters (trailing blanks)

WWW...

Day characters (no trailing blanks)

yy

Last two digits of year

yyyy

Entire year

Displays all zeros if column value is 0.

1 Rules: If you do not specify b: or d:, Scalable SQL uses z:. b:, d:, or z: literals must be the first characters of the edit mask.

Table 4-5 lists the mask characters, special literals, and usage rules for Boolean data types. These types include LOGICAL and BIT. For more information about these masks, refer to the section "Boolean Masks."

Table 4-5

Mask Characters and Special Literals for Boolean Data Types

Mask Characters Char

Effect on Data

<true>

Database Design Guide

Special Literals Char

Effect on Data

–

Separates true For details about specifying Boolean and false values. masks, see the section “Boolean Masks”

62

Rules

Modifying Data

Permanent and Temporary Masks Permanent masks are stored in the data dictionary and become part of the column’s data definition. You can use a SET MASK statement to set a permanent mask. Temporary masks are used in a single data retrieval operation. You can use a temporary mask either to set the display format for a column that does not have a mask or to override a permanent mask specified for a column. To set a temporary mask, specify the mask after the column name in a SELECT statement, and enclose the mask in square brackets ([ ]).

String Masks String masks specify only the display length for CHARACTER, LSTRING, and ZSTRING columns; you cannot embed characters like you can with number masks. You cannot specify masks for LVAR and NOTE columns. You can specify the display length for CHARACTER, LSTRING, and ZSTRING columns by defining the mask as follows: xnnn In this format, nnn specifies how many characters you want the column values to contain. Always precede the length numerals with a lowercase x. The maximum length you can specify after the x is 255. The following string-length mask specifies a display length of 121 bytes. x121 If the mask is longer than the defined length of the string data, Scalable SQL blank-pads the data up to nnn bytes before it returns the data to the application.

Database Design Guide

63

Modifying Data

If you attempt to insert data into a string column that has a defined mask shorter than the actual column length (using a SQL INSERT statement), Scalable SQL inserts the data according to the mask, truncating your data as necessary. The following example sets a permanent edit mask to limit the display length of the City column to 10 characters in the Person table of the sample database. SET MASK Person.City = 'x10'; If a value in the City column is longer than 10 characters, Scalable SQL truncates the column from the right. For example, the column value Philadelphia is displayed as Philadelph. To set the same mask as a temporary mask, you could issue the following SELECT statement: SELECT City [x10] FROM Person;

Number Masks Number masks specify the display format for AUTOINC, BFLOAT, CURRENCY, DECIMAL, FLOAT, INTEGER, MONEY, NUMERIC, NUMERICSA, NUMERICSTS, and UNSIGNED columns. The following example sets a permanent mask for the Scholarship Amount column (a DECIMAL column) in the Student table of the sample database. SET MASK Student.Scholarship_Amount = '$Z,ZZZ.99'; This mask inserts a dollar sign ($) at the beginning of the column value, inserts a comma between the hundreds and thousands places, and adds a decimal point and zeros. For example, the column value 1095 is displayed as $1,095.00. To set the same mask as a temporary mask, you could issue the following SELECT statement. SELECT Scholarship_Amount [$Z,ZZZ.99] FROM Student; Database Design Guide

64

Modifying Data

Data Digits in Number Masks When you define a mask for any of the numeric data types, you can use either Z or 9 to represent the data digits. Each leading zero digit defined by a 9 in the mask appears in the displayed column as a zero. For example, if the number you enter is 45.50 and you defined the mask as 99999.99, the number appears as 00045.50. Scalable SQL handles the Z (zero-suppression) mask character, depending on the context in which it appears. Scalable SQL deletes any Zs preceding the first significant digit of the value to which the mask applies. Scalable SQL displays any Zs to the right of the last significant digit as zeros. Type

Value

Mask

Representation

INTEGER

4958

ZZZZ.ZZ

49.58

Special Literals in Number Masks You can use the following special literals in number masks: +

If a plus sign (+) appears as the first or last character in a mask, Scalable SQL displays a leading or trailing minus sign for negative values, and a leading or trailing plus sign for positive values. A plus sign anywhere else in the mask always appears at that position in the column, regardless of the value.

–

If a minus sign (–) appears as the first or last character in a mask, either a leading or trailing minus sign appears with negative values. The minus sign does not appear in the column for positive values and for the UNSIGNED data type.

Database Design Guide

65

Modifying Data

()

If the first character in your mask is an open parenthesis [(] and the last character is a close parenthesis [)], Scalable SQL encloses negative column values in parentheses. The parentheses do not appear for positive values. Open or close parentheses elsewhere in the mask always appear at that position, regardless of the value.

.

The effect of a decimal point in the mask depends on the specific data type. For all data types except INTEGER, Scalable SQL positions the decimal according to the defined value of the column. For INTEGER columns, a decimal in the mask adjusts the displayed value of the column according to the position of the decimal. In this case, only the displayed value is adjusted; Scalable SQL uses the integer value, not the masked value, in comparisons and computations. If you specify too few digits to the right or left of the decimal in your mask, Scalable SQL displays asterisks (*) instead of the data.

{}

If the first character in your mask is an open brace [{] and the last character is a close brace [ }], Scalable SQL displays only positive values. If the value in the column is negative, Scalable SQL suppresses it.

H

If the last character in your number mask is an H, Scalable SQL displays values in hexadecimal format. (You can use this literal with INTEGER, UNSIGNED, and AUTOINC data types only.)

E

The letter E is a special literal that indicates a number is in scientific notation. A plus or minus sign may appear in front of the exponent to indicate its sign. If the mask has a minus sign (–) in front of the exponent, a minus sign appears for negative exponents, and no sign appears in the column for positive exponents. If the mask has a plus sign (+) in front of the exponent, a minus sign appears for negative exponents and a plus sign appears for positive exponents.

Database Design Guide

66

Modifying Data

Table 4-6 lists examples of the special literals you can use in number masks.

Table 4-6

Examples of Special Literals in Number Masks

Type

Value

Mask

Representation

FLOAT

244.4

–ZZZZZ.99

244.40

FLOAT

–244.4

–ZZZZZ.ZZ

–244.40

FLOAT

–244.4

ZZZZZ.ZZ–

244.40–

MONEY

–6577.89

(ZZ,ZZZ.99)

(6,577.89)

MONEY

577.89

+Z,ZZZ.99

+577.89

MONEY

598.21

($ZZ,ZZZ.99)

$598.21

DECIMAL

56784.50

Z,ZZZ.99

6,784.50

NUMERIC

5125551234

(999)999–9999

(512)555-1234

INTEGER

63

{ZZZ}

63

INTEGER

– 63

{ZZZ}

INTEGER

578293

ZZZZZZ

578293

INTEGER

578293

ZZZZ.99

5782.93

FLOAT(4)

7.87989

–Z.ZZZZZZE+ZZ

7.879890E+00

FLOAT(4)

7.87989E+2

–Z.ZZZZZZE+ZZ

7.879890E+02

FLOAT(8)

7.87989

–Z.ZZZZZE+ZZZ

7.87989E+000

FLOAT(8)

–7.87989E+111

–Z.ZZZZZE+ZZZ

–7.87989E+111

Database Design Guide

67

Modifying Data

Other Characters in Number Masks A mask character that is neither data nor a special literal appears in the column as follows:

‹ ‹

Only if other data appears to the left of that character. If no data appears in the column and you place the character in the leftmost position in the mask.

For example, you can place commas so that they appear between sets of three digits. Scalable SQL does not include the comma if no digits are to the left of the comma. The following examples illustrate the use of commas and other literals in masks: Type

Value

Mask

Representation

NUMERIC

473553291

999–99–9999

473–55–3291

NUMERIC

678950

ZZZ,ZZZ.ZZ

678,950.00

NUMERIC

784.50

ZZZ,ZZZ.ZZ

784.50

Hexadecimal Masks Hexadecimal masks specify the hexadecimal display format for INTEGER, UNSIGNED, and AUTOINC columns. The following example sets a permanent mask for a 1-byte INTEGER column called ID in the Class table. SET MASK Class.ID = 'ZZZ9H'; All hexadecimal masks must end with H. The example mask includes 9 as the last digit in the mask value; therefore, if the column value is zero, Scalable SQL displays it as 0H. To set the same mask as a temporary mask, you could issue the following SELECT statement: SELECT ID [Z9H] FROM Class; Database Design Guide

68

Modifying Data

When you define a mask for columns of type INTEGER, UNSIGNED, or AUTOINC, you can use the number mask characters Z and 9 followed by an H, to represent a hexadecimal value. If you use 9s in the mask, Scalable SQL displays a zero for each leading zero digit you defined. For example, if the number you enter is 15 and you defined the mask as 999H, Scalable SQL formats the value as 00FH. If you use Zs in the mask, leading zero digits defined in the mask do not appear in the data. For example, if the number you enter is 15 and you defined the mask as ZZZH, the value appears as FH. The following examples illustrate the effects of hexadecimal masks. Type

Value

Mask

Representation

INTEGER

244

ZZZ9H

F4H

INTEGER

63782

ZZZZZZZ9H

F926H

INTEGER

–63782

ZZZZZZZ9H

FFFF06DAH

INTEGER

10

9999H

000AH

Date Masks To permit applications to use a variety of date formats, Scalable SQL uses a date mask to evaluate input and output values. For this reason, ASCII date value formats must conform to the currently defined mask (or to the default mask, if you have not defined another mask). Date masks allow you to set both the input format for column values and the display, or output format for DATE values. By default, Scalable SQL requires that you enter date values using the mask mm/dd/yy. However, you can change the default by setting a different permanent mask.

Database Design Guide

69

Modifying Data

In restriction clauses and computed columns, you must specify date values according to the default mask. For example, even if you set a permanent date mask as mm-dd-yy, you must specify dates as mm/dd/yy in restriction clauses and computed columns. In INSERT statements, you must specify date values according to the permanent mask, or the default mask if you do not have a permanent mask. The following example sets a permanent date mask for the Date of Birth column in the sample database: SET MASK Person.Date_of_Birth = 'mm-dd-yy'; This mask changes both the input and display formats for the Date of Birth column from the default mm/dd/yy to mm-dd-yy. To set the same mask as a temporary mask, you could issue the following SELECT statement: SELECT Date_of_Birth [mm-dd-yy] FROM Person; A column of data type DATE contains three elements: month, day, and year. These elements are represented as follows: Mask Character

Meaning

mm

Month

dd

Day

yy

Last two digits of year length

yyyy

Full year

You can use a mask to specify names for the month and day. Any other characters you include in the mask appear unchanged in the displayed value of the column. You can insert blanks in the column using the B literal. Database Design Guide

70

Modifying Data

You can set the default values for the date mask by placing the following characters at the beginning of the mask. Mask Character Result b:

Displays all blanks when the input value is all zeros (month, day, and year).

d:

Uses the system date when the date is not specified (input value is all blanks); has no effect on output.

z:

Uses all zeros when the date is not specified (input value is all blanks); has no effect on output.

If you do not precede the mask with either b: or d:, Scalable SQL uses z: at the beginning of the mask. The following examples illustrate the effects of using masks. Value

Mask

Representation

05/09/56

mm–dd–yy

05–09–56

05/09/56

mm/dd/yyyy

05/09/1956

04/01/85

mm/yy

04/85

05/28/85

dd.mm.yyyy

28.05.1985

06/30/86

b:mm/dd/yy

06/30/86

03/15/86

z:mm/dd/yy

03/15/86

00/00/00

b:mm/dd/yy

[blank]

00/00/00

z:mm/dd/yy

00/00/00

Database Design Guide

71

Modifying Data

Scalable SQL provides mask characters that allow you to specify the name of the day or the month when the date is displayed. Mask Character Meaning M

Displays the month name, up to the number of M characters you include in the mask, and strips any trailing blanks. The minimum number of M characters you can include is three. If you specify only two characters (MM), Scalable SQL displays the month as an integer.

m

Displays the month name, up to the number of m characters you include in the mask, including trailing blanks. The minimum number of m characters you can include is three. If you specify only two characters (mm), Scalable SQL displays the month as an integer.

W

Displays the name of the day of the week, up to the number of W characters you include in the mask, and strips any trailing blanks.

w

Displays the name of the day of the week, up to the number of w characters you include in the mask, including trailing blanks.

The following examples show different date masks and their results. This example strips any trailing blanks. MMMMMMMMMBdd,Byyyy December 12, 1992 April 06, 1992 September 30, 1992 May 23, 1992

Database Design Guide

72

Modifying Data

This example includes trailing blanks. mmmmmmmmmBdd,Byyyy December 12, 1992 April 06, 1992 September 30, 1992 May 23, 1992 mmmmmMMMMBdd,Byyyy December 12, 1992 April 06, 1992 September 30, 1992 May 23, 1992 The following examples show different day masks and their results. wwwwwwwwwBmmmBdd Tuesday Dec 01 Wednesday May 10 wwwWWWWWWBmmmBdd Tuesday Dec 01 Wednesday May 10 WWWWWWWWW, BMMMMMMMMMBdd Tuesday , December 01 Wednesday, May 10

Database Design Guide

73

Modifying Data

Time Masks To permit users and applications to use a variety of time formats, Scalable SQL uses a time mask to evaluate input and output values. For this reason, ASCII time value formats must conform to the currently defined mask (or to the default mask if you have not defined another mask). Time masks allow you to set both the input format for column values and the display, or output format for TIME values. By default, Scalable SQL requires that you enter time values using the mask hh:mm:ss. However, you can change the default by setting a different permanent mask. In restriction clauses and computed columns, you must specify time values according to the default mask. For example, even if you set a permanent time mask as hh:mm, you must specify times as hh:mm:ss in restriction clauses and computed columns. In INSERT statements, you must specify time values according to the permanent mask, or the default mask if you do not specify another mask. The following example sets a permanent time mask for the Start Time and Finish Time columns for a class in the sample database. SET MASK Class.Start_Time = 'hh:mm'; SET MASK Class.Finish_Time = 'hh:mm'; This mask changes both the input and display formats for the Start Time and Finish Time columns from the default hh:mm:ss to hh:mm. To set the same mask as a temporary mask, you could issue the following SELECT statement: SELECT Start_Time [hh:mm], Finish_Time [hh:mm] FROM Class;

Database Design Guide

74

Modifying Data

Columns of data type TIME contain four elements: hours, minutes, seconds, and hundredths of a second. Each element is represented as follows. Mask Character

Meaning

hh

Hours

mm

Minutes

ss

Seconds

uu

Hundredths of a second

Any other characters you include in the mask appear unchanged in the displayed value of the column. You can insert blanks in the column using the B literal. If you use the B literal, you cannot insert data into the space in which you placed the literal. You can set the default values for the time mask using the following characters. Mask Character Meaning b:

Displays all blanks when the input value is all zeros.

d:

Uses the system time when the time is not specified (input value is all blanks); has no effect on output.

z:

Uses all zeros when the time is not specified (input value is all blanks); has no effect on output.

ap

Displays am or pm after the time. Used to interpret input and output value.

You must place the b:, d:, and z: characters in front of the mask. The ap: characters can appear either in front of the mask or after it.

Database Design Guide

75

Modifying Data

If you do not precede the mask with either b: or d:, Scalable SQL uses z: at the beginning of the mask. The following examples illustrate some time masks. Value

Mask

Representation

06:15:32:99

hh:mm:ss

06:15:32

00:02:10:54

mm:Bss:uu

02: 10:54

08:15:00:00

b:hh:mm:ss

08:15:00

00:00:00:00

b:hh:mm:ss

[blank]

08:15:00:00

z:hh:mm:ss

08:15:00

00:00:00:00

z:hh:mm:ss

00:00:00

08:20:00:00

z:hh:mm:ss ap

08:20:00 am

Timestamp Masks To permit applications to use a variety of date and time formats, Scalable SQL uses a timestamp mask to evaluate input and output values. For this reason, ASCII date and time value formats must conform to the currently defined mask (or to the default mask, if you have not defined another mask). Timestamp masks allow you to set both the input format for column values and the display or output format for TIMESTAMP values. By default, Scalable SQL requires that you enter date values using the mask YYYY-mm-ddBhh:tt:ss.fffffff (where f is equal to the precision, which is optional). You can change the default by setting a different permanent mask. In restriction clauses and computed columns, you must specify TIMESTAMP values according to the default mask. In INSERT statements, you must specify TIMESTAMP values according to the permanent mask, or the default mask if you do not specify another mask.

Database Design Guide

76

Modifying Data

You can also set the following default values: Mask Character Description b

Displays blanks when you do not specify the TIMESTAMP value.

z

Displays zeros when you do not specify the TIMESTAMP value.

You must use at least one separator character (b or any character that is not used as a mask character) between the date and time of the mask.

Boolean Masks Boolean masks set the display format for BIT and LOGICAL columns. By default, Scalable SQL displays true or false in Boolean columns. Although Scalable SQL stores values in a Boolean column as true or false, you can use a Boolean data type for any column that can have only two values. By specifying an edit mask, you can cause Scalable SQL to display the two actual values and accept them as input. In restriction clauses and computed columns, you must specify Boolean values according to the default mask. For example, even if you set a permanent logical mask as yes-no, you must specify logical values as true-false in restriction clauses and computed columns. In INSERT statements, you must specify Boolean values according to the permanent mask, or the default mask if you do not specify another mask. The following example sets a permanent yes-no mask for a BIT column called Married in the Person table. SET MASK Person.Married = 'yes-no';

Database Design Guide

77

Modifying Data

This mask changes the display for the Married column from the default true-false to yesno. For example, the column value True is displayed as yes. To set the same mask as a temporary mask, you could issue the following SELECT statement: SELECT Married [yes-no] FROM Person; You can specify the word that represents the true condition without specifying a word that represents the false condition. If the value is true, Scalable SQL displays your true mask; otherwise, it displays blanks for the false value. You can also specify the word that represents the false condition without specifying a word for the true condition, but in this case, a dash must precede the word. If you set a Boolean column’s mask to yes – no, you must enter yes to input true values and no to input false values. However, if you set the mask to true – , you must still enter the word false when you enter false values, even though the current dictionary mask causes Scalable SQL to display false values as blanks. Scalable SQL also uses Boolean masks to interpret ASCII Boolean input values. For INSERT statements, you must specify Boolean values using either the current dictionary mask or the default mask if you have not defined another mask, or a 0 to represent false and a 1 to represent true. Additionally, in restriction clauses and computed columns, you must either specify Boolean values according to the default mask, or specify a 0 to represent false and a 1 to represent true. Following are some examples of Boolean masks. Internal Value

Mask

Representation

true

‘yes – no’

yes

false

‘yes – no’

no

true

‘ON – OFF’

ON

false

‘T – F’

F

Database Design Guide

78

Modifying Data

Internal Value

Mask

Representation

false

‘T –’

true

‘YES –’

false

‘YES –’

false

‘–NO’

true

‘– NO’

true

‘available –’

false

‘available–’

true

‘2 dr. – 4 dr.’

2 dr.

false

‘2 dr. – 4 dr.’

4 dr.

YES NO available

The following table shows the appropriate values for input when each mask is in effect. Input Values Mask

True

False

‘yes – no’

yes

no

‘ON – OFF’

ON

OFF

‘T – F’

T

F

‘T–’

T

false

‘YES –’

YES

false

‘– NO’

true

NO

‘available –’

available

false

‘2 dr.’–‘4 dr.’

2 dr.

4 dr.

Database Design Guide

79

Modifying Data

Masks Specified on Multiple Columns You can specify temporary masks for multiple columns within the same SELECT statement. Use a comma to separate each column and its mask definition from the next column. SELECT Name, Phone_Number [(999) 999-9999], Room_Number [ZZZ] FROM Department WHERE Name = 'English'; Issuing this SQL statement on the sample database produces the following results: Name

Phone_Number

Room_Number

English

(512) 694-1900

307

Database Design Guide

80

Modifying Data

Using UPDATE You can use an UPDATE statement to change the data in a row that is already in a table. UPDATE statements let you modify specific columns in a row. Also, you can use a WHERE clause in an UPDATE statement to specify which rows for Scalable SQL to change. This is referred to as a searched update. Using SQL declared cursors and the Positioned UPDATE statement, you can update the current row of a declared cursor from which you are fetching data. UPDATE Course SET Credit_Hours = 4 WHERE Course.Name = 'Math'; This example instructs Scalable SQL to find the row that contains the course name Math and change the Credit Hours column value to 4. As shown in the previous example, you can use a constant to update a column by placing it on the right hand side of a SET clause in an UPDATE statement. If the data type of the column you are updating is DATE, TIME, or TIMESTAMP, then this constant must conform to the column’s user-defined edit mask, if present; otherwise, it must match the default mask for the data type. For all other data types, the constant must conform either to the mask defined on the column or the default mask for the data type of the column. For more information about the role of data types and defined or default masks, refer to the SQL Language Reference.

Database Design Guide

81

Modifying Data

chapter

5

Retrieving Data

Once your database contains data, you can retrieve and view that data using a SELECT statement. Scalable SQL returns the data you request in a result table. Using SQL statements, you can do the following:

‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹

Create temporary views or permanent (stored) views. Specify a selection list that lists the columns to retrieve from one or more tables in your database. Specify how to sort the rows. Specify criteria by which to group the rows into subsets. Assign a temporary name (alias) to a table. Retrieve data from one or more tables and present the data in a single result table (a join). Specify a subquery within a SELECT statement. Specify a restriction clause to restrict the rows Scalable SQL selects.

The remainder of this chapter discusses how you can use SELECT statements to accomplish these tasks in the following sections:

‹ ‹ ‹ ‹ ‹ ‹ ‹

Views Selection Lists Sorted and Grouped Rows Joins Subqueries Restriction Clauses Functions

Database Design Guide

82

Retrieving Data

Views A view is the mechanism for examining the data in your database. A view can combine data from multiple tables or can include only certain columns from a single table. Although a view looks like a table, it consists of a selected set of columns or calculations based on those columns from tables in your database. Thus, a view may contain data from columns in more than one table or data that is not actually in any table at all (for example, SELECT COUNT (*) FROM Person).

Features of Views Following are some of the features of views:

‹ ‹ ‹ ‹

You can arrange the columns of a view in any order except that the variablelength column must be last. You can specify only one variable-length column. You can use a restriction clause to specify the set of rows that Scalable SQL returns in a view. The restriction clause lists criteria that the data must satisfy to be included in the view. For more information, see the section "Restriction Clauses." You can specify a sort order for the view. When Scalable SQL returns the data to your application, the rows are returned in the view according to the sort order you designate. If you do not specify a sort order, the order of the returned rows is undefined. You can design and customize views for each user and application that accesses the database. You can store these view definitions within the data dictionary for later recall.

Database Design Guide

83

Retrieving Data

‹ ‹

You can include any number of stored view names in a table list when retrieving, updating, or deleting data unless the view is a read-only view. In a read-only view, you can only retrieve data. In a stored view, you must provide headings for the view’s computed columns and constants and use those names in a list of column names when you retrieve data from the view.

Temporary and Stored Views You can use SELECT statements to create temporary views or stored views. You use a temporary view only once and then release it. Scalable SQL places the definition of a stored view in the data dictionary so you can recall the view later. You use CREATE VIEW statements to create and name stored views. Each view name must be unique within a database and cannot exceed 20 characters. For more information about rules for naming views, refer to Chapter 2, “Creating a Database.” Scalable SQL is case-sensitive when defining database element names. If you create a stored view named PhoNE, Scalable SQL stores the view name in the dictionary as PhoNE. Scalable SQL is case-insensitive after you define the view name. After defining the stored view PhoNE, you can refer to it as phone. Using stored views provides the following features:

‹

You can store frequently executed queries and name them for later use. The following example creates a stored view named Phones based on the Department table. CREATE VIEW Phones (PName, PPhone) AS SELECT Name, Phone_Number FROM Department;

Database Design Guide

84

Retrieving Data

‹

You can specify the name of the stored view in table lists when retrieving, updating, and deleting data. The stored view behaves as though it is a table in the database, but it is actually reconstructed internally by the Scalable SQL engine each time it is used. The following example updates the phone number for the History Department in the Department table by referring to the stored view Phones. UPDATE Phones SET PPhone = '5125552426' WHERE PName = 'History';

‹

You can specify headings. A heading specifies a column name that is different from the name you defined for the column in the dictionary. The following example specifies the headings Department and Telephone for the stored view Phones. CREATE VIEW Dept_Phones (Department, Telephone) AS SELECT Name, Phone_Number FROM Department; You can use the headings in subsequent queries on the view, as in the following example. SELECT Telephone FROM Dept_Phones; If the selection list contains simple column names and you do not provide headings, Scalable SQL uses the column name as the column heading. You must use headings to name constants and computed columns that you include in the view. If you do not provide a heading for a computed column, Scalable SQL returns Status Code 845; you need headings for computed columns in order to reference them explicitly. The following example creates the headings Student and Total. CREATE VIEW Accounts (Student, Total) AS SELECT Student_ID, SUM (Amount_Paid)

Database Design Guide

85

Retrieving Data

FROM Billing GROUP BY Student_ID; You must also use headings if you specify SELECT * from multiple tables that have any duplicate column names. If you do not specify a heading, Scalable SQL returns Status Code 533.

‹

You can create customized views for each user or application that accesses the database. You can store these view definitions within the data dictionary for later recall.

Read-Only Tables in Views You cannot insert, update, or delete rows from views that contain read-only tables. (Here the term update refers to insert, update, and delete; if a table is read-only, you cannot update it.) Some tables are read-only whether or not they are in views that are specified as such; such tables are intrinsically read-only, and you cannot update them. A table is read-only if it meets one of the following criteria:

‹ ‹ ‹

The database has security enabled, and the current user or user group has only SELECT rights defined for the database or the table. The data files have been flagged read-only at the physical file level (for example, using the FLAG command in NetWare or the ATTRIB command in DOS). You execute a SELECT clause that creates a view and contains any of the following items: Š An aggregate function in the selection list. Š A GROUP BY or HAVING clause. Š A UNION. Š The DISTINCT keyword.

Database Design Guide

86

Retrieving Data

‹

You execute a SELECT statement that creates a view, and the table contains any of the following characteristics: Š It appears in a non-mergeable view that is in the SELECT statement’s FROM clause. Š It is a system table. System tables are always opened as read-only in a view, even if this overrides the view’s open mode. Š A column from the table appears in a computed column or a scalar function in the selection list. Š The table appears in the FROM clause of a subquery that is not correlated to the outermost query. A subquery can be directly or indirectly correlated to the outermost query. A subquery is directly correlated with the outermost query if it contains a reference to a column from a table and its specific occurrence in the outermost query’s FROM clause. A subquery is indirectly correlated to the outermost query if it is correlated to a subquery that is in turn directly or indirectly correlated to the outermost query. Š The open mode is read-only. You can set the open mode using the SET OPENMODE statement. Note

You cannot set the open mode on a table by table basis; the SET OPENMODE statement causes all tables to be opened in the indicated mode. Š You execute a Positioned UPDATE statement with any of the following keywords, without specifying FOR UPDATE: ORDER BY SCROLL

Database Design Guide

87

Retrieving Data

Mergeable Views A view is mergeable if you can rewrite the SELECT statement using only base tables and columns. For example, if you want to know how many students are in a class, you can define a view to calculate that. The view NumberPerClass is defined as follows: CREATE VIEW NumberPerClass (Class_Name, Number_of_Students) AS SELECT Name, COUNT(Last_Name) FROM Person, Class, Enrolls WHERE Person.ID = Enrolls.Student_ID AND Class.ID = Enrolls.Class_ID GROUP BY Name; The view NumberPerClass is defined as follows: SELECT * FROM NumberPerClass; The view NumberPerClass is then mergeable because we can rewrite the SELECT statement as follows: SELECT Name, COUNT(Last_Name) FROM Person, Class, Enrolls WHERE Person.ID = Enrolls.Student_ID AND Class.ID = Enrolls.Class_ID GROUP BY Name; The view NumberPerClass is non-mergeable if you want to write a SELECT statement such as the following: SELECT COUNT(Name) FROM NumberPerClass WHERE Number_of_Students > 50; This statement is invalid for the view NumberPerClass. You cannot rewrite it using only base tables and base columns. Database Design Guide

88

Retrieving Data

A view is mergeable if it does not contain any of the following characteristics:

‹ ‹ ‹ ‹

It refers to a non-mergeable view. It has an aggregate function in its selection list or a DISTINCT keyword, and it appears in the FROM clause of a SELECT statement that has an aggregate in its selection list. It has a DISTINCT keyword and appears in the FROM clause of a SELECT statement that has more than one item in its FROM clause, does not have an aggregate in its selection list, and does not have a DISTINCT keyword. It has an aggregate in its selection list and appears in the FROM clause of a SELECT statement that either has more than one item in its FROM clause or a WHERE clause restriction.

Database Design Guide

89

Retrieving Data

Selection Lists When you use a SELECT statement to retrieve data, you specify a list of columns (a selection list) to include in the result table. To retrieve all the columns in a table or tables, you can use an asterisk (*) instead of a list of columns. Note

Avoid using * in place of the list. Using * can expose an application to potential problems if the number of columns or column sizes in a table changes. Also, it typically returns unnecessary data.

The following example selects three columns from the Class table. SELECT Name, Section, Max_Size FROM Class; The following example selects all columns from the Class table. SELECT * FROM Class; When retrieving data, Scalable SQL displays column names based on how you specify the names in the query.

‹

If you explicitly specify a column name, Scalable SQL returns it as you entered it. The following example specifies column names in all lowercase. SELECT name, section, max_size FROM Class; Scalable SQL returns the column names as follows: name, section, max_size These column names are headings for the returned data; they are not data themselves.

Database Design Guide

90

Retrieving Data

The following example defines aliases for the tables Department and Faculty. SELECT d.Name, f.ID FROM Department d, Faculty f; Scalable SQL returns the column names as follows: d.Name, f.ID

‹

If you use * to specify column names, they appear in all uppercase, as in the following example. SELECT * FROM Department; Scalable SQL returns the column names as follows: NAME, PHONE_NUMBER, BUILDING_NAME, ROOM_NUMBER, HEAD_OF_DEPARTMENT The following example defines aliases for the tables Department and Faculty. SELECT * FROM Department d, Faculty f; Scalable SQL returns the column names as follows: D.NAME D.PHONE_NUMBER D.BUILDING_NAME D.ROOM_NUMBER D.HEAD_OF_DEPT F.ID F.DEPT_NAME F.DESIGNATION F.SALARY F.BUILDING_NAME F.ROOM_NUMBER F.RSCH_GRANT_AMOUNT

Database Design Guide

91

Retrieving Data

Sorted and Grouped Rows Once you have decided what data to include in your result table, you can specify how to order the data. You can use the ORDER BY clause to sort the data, or you can use a GROUP BY clause to group rows by a certain column. When you group the data, you can also use aggregate functions to summarize data by group. For more information about aggregate functions, refer to the section "Aggregate Functions." The following example orders all rows by last name in the Person table of the sample database. SELECT * FROM Person ORDER BY Last_Name; The following example groups the results by the Building Name column in the Room table. This example also uses two aggregate functions, COUNT and SUM. SELECT Building_Name, COUNT(Number), SUM(Capacity) FROM Room GROUP BY Building_Name;

Database Design Guide

92

Retrieving Data

Joins A join results from a statement that combines columns from two or more tables into a single view. From this view, you can retrieve, insert, update, or delete data, provided it is not read-only. Note

This section primarily discusses joining tables using SELECT statements. However, you can also create joins with INSERT, UPDATE, and DELETE statements by applying a single statement to more than one table. The SQL Language Reference describes these SQL statements and how to optimize joined views.

You can retrieve data from tables by listing each table or view name in a FROM clause. Use a WHERE clause to specify one or more join conditions. A join condition compares an expression that references a column value from one table to an expression that references a column value from another table. When data is properly normalized, most joins associate values based on some specified key value. This allows you to extract data in terms of referential integrity relationships. For example, if you want to know which professor teaches each class, you can create a join based on the Faculty ID, which is a foreign key in the Class table and a primary key in the Person table: SELECT DISTINCT Class.Name, Person.Last_Name FROM Class, Person, Faculty WHERE Class.Faculty_ID = Person.ID AND Class.Faculty_ID = Faculty.ID; This example joins two tables on the basis of common values in a common column: Faculty ID.

Database Design Guide

93

Retrieving Data

You can also join tables by making numeric comparisons between columns of like data types. For example, you can compare columns using <, >, or =. The following self-join on the Faculty table identifies all faculty members whose salary was higher than each faculty member (this would produce considerably more records than the faculty table contains): SELECT A.ID, A.Salary, B.ID, B.Salary FROM Faculty A, Faculty B WHERE B.Salary > A.Salary; Similar comparisons of dates, times, etc. can produce many useful and meaningful results. When joining columns, choose columns that are of the same data type when possible. For example, comparing two NUMERICSTS columns is more efficient than comparing a NUMERICSTS column with an INTEGER column. If the columns are not of the same data type but are both numeric or strings, Scalable SQL scans both the tables and applies the join condition as a restriction to the results. However, if you attempt to compare a string type column (such as a ZSTRING column) to a numeric type column (such as a DECIMAL column), Scalable SQL returns Status Code 223, and does not complete the query. When you use string type columns in a WHERE clause, one column in the join condition can be a computed string column. This allows you to concatenate two or more strings and use a join condition to compare them to a single string from another table. The way in which Scalable SQL handles a join depends on whether the join condition contains an index column.

‹ ‹

If the join condition contains a column that is defined as an index, performance improves. Using the index to sort rows in the corresponding table, Scalable SQL selects only rows that meet the restriction clause condition. If the join condition does not contain a column that is defined as an index, performance is less efficient. Scalable SQL reads each row in each table to

Database Design Guide

94

Retrieving Data

select rows that meet the restriction clause condition. To enhance performance, you can create an index in one of the tables before executing the join. This is especially helpful if the query is one that you perform often.

Joining Tables with Other Tables To specify a join using a SELECT statement, use a FROM clause to list the relevant tables and a WHERE clause to specify the join condition and the restriction. The following example also uses aliases to simplify the statement. SELECT Student_ID, Class_ID, Name FROM Enrolls e, Class cl WHERE e.Class_ID = cl.ID; The next example joins three tables: SELECT p.ID, Last_Name, Name FROM Person p, Enrolls e, Class cl WHERE p.ID = e.Student_ID AND e.Class_ID = cl.ID; The next example retrieves a list of students who received a grade lower than a 3.0 in English. SELECT First_Name, p.Last_Name FROM Person p, Student s, Enrolls e, Class cl WHERE s.ID = e.Student_ID AND e.Class_ID = cl.ID AND s.ID = p.ID AND cl.Name = 'ACC 101' AND e.Grade < 3.0; In this example, the first three conditions in the WHERE clause specify the join between the four tables. The next two conditions are restriction clauses connected by the Boolean operator AND.

Database Design Guide

95

Retrieving Data

Joining Views with Tables To join a view with one or more tables, include a view name in the FROM clause. The view you specify can include columns from a single table or from several joined tables.

Types of Joins Scalable SQL supports equal joins, nonequal joins, null joins, Cartesian product joins, and self joins.

Equal Joins An equal join occurs when you define the two join columns as equal. The following statement defines an equal join. SELECT First_Name * Last_Name, Degree, Residency FROM Person p, Student s, Tuition t WHERE p.ID = s.ID AND s.Tuition_ID = t.ID;

Nonequal Joins You can join tables based on a comparison operation. You can use the following operators in nonequal joins: <

Less than

>

Greater than

<=

Less than or equal

>=

Greater than or equal

Database Design Guide

96

Retrieving Data

The following WHERE clause illustrates a join that uses a greater than or equal operator. SELECT Name, Section, Max_Size, Capacity, r.Building_Name, Number, FROM Class cl, Room r WHERE Capacity >= Max_Size;

Null Joins A null join allows you to retrieve each row from one table, regardless of whether there is a corresponding row in the table to which you are joining. The join returns rows from the leftmost table in the join condition, even if the rightmost table does not contain data that satisfies the restriction. For these rows, Scalable SQL returns null values for all the columns in the rightmost table. To specify a null join, insert a plus sign in parentheses after the rightmost column in a WHERE clause. The following example performs a null join on the Person and Class tables from the sample database and returns a row for each student enrolled in a class. If a student is enrolled, the corresponding course names return; otherwise, a null returns if the student is not enrolled. SELECT First_Name * Last_Name, cl.Name FROM Person, Enrolls, Class cl WHERE ID = Student_ID (+) AND Class_ID = cl.ID (+); Null joins often behave similarly to left outer joins, unless you specify additional conditions or restrictions. You can only specify a null join condition in a SELECT statement; do not specify a null join in an UPDATE or DELETE statement.

Database Design Guide

97

Retrieving Data

Cartesian Product Joins A Cartesian product join associates each row in one table with each row in another table. Scalable SQL reads every row in one table once for each row in the other table. On large tables, a Cartesian product join can take a significant amount of time to complete since Scalable SQL must read the following number of rows to complete this type of join: (# of rows in one table) * (# of rows in another table) For example, if one table contains 600 rows and the other contains 30, Scalable SQL reads 18,000 rows to create the Cartesian product join of the tables. The following statement produces a Cartesian product join on the Person and Course tables in the sample database: SELECT s.ID, Major, t.ID, Degree, Residency, Cost_Per_Credit FROM Student s, Tuition t;

Database Design Guide

98

Retrieving Data

Self Joins In a self join, you can specify a table name in the FROM clause more than once. When you specify a self join, you must assign aliases to each instance of the table name so that Scalable SQL can distinguish between each occurrence of the table in the join. The following example lists all the people who have a permanent address in the same state as the person named Jason Knibb. The query returns the ID, first name, last name, current phone number, and e-mail address. SELECT p2.ID, p2.First_Name, p2.Last_Name, p2.Phone, p2.EMail_Address FROM Person p1, Person p2 WHERE p1.First_Name = 'Jason' AND p1.Last_Name = 'Knibb' and p1.Perm_State = p2.Perm_State

Database Design Guide

99

Retrieving Data

Subqueries A subquery (also known as a nested query) is a SELECT statement contained within one of the following:

‹ ‹

The WHERE clause or HAVING clause of another SELECT statement. The WHERE clause of an UPDATE or DELETE statement.

A subquery allows you to base the result of a SELECT, UPDATE, or DELETE statement on the output of the nested SELECT statement. Except in correlated subqueries, when you issue a subquery Scalable SQL parses the entire statement and executes the innermost subquery first. It uses the result of the innermost subquery as input for the next level subquery, and so forth. The following statement returns the names of students who are enrolled in Math: SELECT First_Name * Last_Name FROM Person p, Enrolls e WHERE p.ID = e.Student_ID AND e.Class_ID IN (SELECT ID FROM Class WHERE Name BEGINS WITH 'MAT'); Scalable SQL first evaluates the subquery to retrieve the class ID from the Class table. It then performs the outer query, restricting the results to only those students who have enrolled in the class. For more information about expressions you can use with subqueries, refer to "Expression Rules."

Database Design Guide

100

Retrieving Data

Subquery Limitations A subquery in a WHERE clause becomes part of the search criteria. The following limits apply to using subqueries in SELECT, UPDATE, and DELETE statements:

‹ ‹ ‹

You must enclose the subquery in parentheses. The subquery cannot contain a UNION clause. Unless you use an ANY, ALL, EXISTS, or NOT EXISTS keyword in the WHERE clause of the outer query, the selection list of the subquery can contain only one column name expression.

You can nest several levels of subqueries in a statement. The number of subqueries you can nest is determined by the amount of memory available to Scalable SQL.

Correlated Subqueries A correlated subquery contains a WHERE or HAVING clause that references a column from a table in the outer query’s FROM clause; this column is called a correlated column. To test the results from a subquery against the results from the outer query, or to test for a particular value in a query, you must use a correlated subquery. Since the correlated column comes from the outer query, its value changes each time a row in the outer query is fetched. Scalable SQL then evaluates the expressions in the inner query based on this changing value. The following example shows the names of courses that provide more credit hours than time actually spent in the class room. SELECT Name, Credit_Hours FROM Course c WHERE Name IN Database Design Guide

101

Retrieving Data

(SELECT Name FROM Class cl WHERE c.Name = cl.Name AND c.Credit_Hours > (HOUR (Finish_Time - Start_Time) + 1)); To improve performance, you could rephrase the previous statement as a simple query. SELECT Name, Credit_Hours FROM Class, Course WHERE Course.Name = Class.Name AND Credit_Hours > (HOUR (Finish_Time - Start_Time) + 1);

Database Design Guide

102

Retrieving Data

Restriction Clauses A restriction clause is an ASCII text string of operators and expressions. A restriction clause specifies selection criteria for the values in the columns of a view, limiting the number of rows the view contains. The syntax of certain clauses (such as WHERE or HAVING) requires using a restriction clause. A restriction clause can specify these conditions:

‹ ‹

Restriction condition—Compares an expression that references a column value to either a constant or another expression that references a column value in the same table. Join condition—Compares an expression that references a column value from one table to an expression that references a column value from another table.

A restriction clause can contain multiple conditions. It can also contain a SELECT subquery that bases search criteria on the contents of other tables in the database. The condition containing the subquery can contain the EXISTS, NOT EXISTS, ALL, ANY, and SOME keywords, or the IN range operator. You can specify a restriction clause using a WHERE or HAVING clause in a SELECT, UPDATE, or DELETE statement. Figure 5-1 provides an example restriction clause and illustrates restriction clause elements.

Database Design Guide

103

Retrieving Data

Figure 5-1

Example Restriction Clause where table1.field1 > 5 and table1.field2 = table1.field1 * 10 and table1.field1 = table2.field1 Boolean Operators

Restriction Clause Expression Operator

Condition Operators

Restriction Clause Operators Restriction clauses can use three types of operators:

‹ ‹ ‹

Boolean operators — Connect conditions in a restriction clause. Condition operators — Connect expressions to form a condition. A condition operator can be a relational or a range operator. Expression operators — Connect two expressions to form another expression. An expression operator can be an arithmetic or string operator.

Boolean Operators Boolean operators specify logical conditions.

Table 5-1

Boolean Operators

Operator

Description

AND

If all search conditions connected with AND are true, the restriction passes.

OR

If at least one of the conditions connected by OR is true, the restriction passes.

NOT

If the condition is true, the restriction passes.

Database Design Guide

104

Retrieving Data

Condition Operators A condition operator can be a relational or a range operator.

‹ ‹

Relational operator—Compares a column value with either another column value or a constant. If the value of the column is true, Scalable SQL selects the row. Range operator—Compares a column value with a specified range of values for the column. If the value of the column is true, the restriction passes, and Scalable SQL selects the row.

Table 5-2 lists the relational operators.

Table 5-2

Relational Condition Operators

Operator

Description

Operator

Description

<

Less than

>=

Greater than or equal to

>

Greater than

!=

Not equal to

=

Equal to

<>

Not equal to

<=

Less than or equal to

Table 5-3 lists the Condition operators.

Table 5-3

Range Condition Operators

Operator

Description

IN

Value exists in specified list.

NOT IN

Value does not exist in specified list.

BETWEEN

Value exists within specified range.

Database Design Guide

105

Retrieving Data

Table 5-3

Range Condition Operators continued

NOT BETWEEN Value does not exist within specified range. BEGINS WITH

Value begins with specified string.

CONTAINS

Value contains specified string.

NOT CONTAINS

Value does not contain specified string.

IS NULL

Value is the defined NULL value for the column.

IS NOT NULL

Value is not the defined NULL value for the column.

LIKE

Value matches specified string. You can substitute two wildcard characters for actual characters. The percent sign (%) represents any sequence of n characters (where n can be zero). The underscore ( _ ) represents a single character.

NOT LIKE

Value does not match specified string.

With the IN and NOT IN operators, the second expression can be a subquery instead of a column name or constant. Note

Do not confuse using range operators with using the RANGE keyword in SET statements. For more information about the RANGE keyword, refer to the discussion of the SET statement in the SQL Language Reference.

Expression Operators Expression operators allow you to create expressions for computed columns using arithmetic or string operators. For more information, refer to "Expressions."

Database Design Guide

106

Retrieving Data

Restriction Clause Examples The following examples demonstrate some of the restriction clause operators.

OR and Equal To (=) The following example uses the relational EQUAL TO and boolean OR operators. It selects all rows in which the value of the State column is Texas or New Mexico. SELECT Last_Name, First_Name, State FROM Person WHERE State = 'TX' OR State = 'NM';

IN The following example uses the IN operator. It selects all rows in which the value of the Date of Birth column contains the date 10/01/65, 12/06/67, 01/19/70, 11/13/77, or 08/02/79. SELECT ID, Last_Name, First_Name, Date_Of_Birth FROM Person WHERE Date_Of_Birth IN (10/01/65, 12/06/67, 01/19/70, 11/13/77, 08/02/79); Following is the result table: ID (9 bytes)

Last Name (26 bytes)

First Name (16 bytes)

Date of Birth (4 bytes)

101-13-5758

Badad

Ismail

08/02/79

103-65-7107

Bald

Elaine

11/13/77

267-11-2927

Gabbard

Arnold

01/19/70

267-33-3252

Now

Brad

11/13/77

314-57-7429

Iofin

Lev

01/19/70

Database Design Guide

107

Retrieving Data

BEGINS WITH The following example uses the BEGINS WITH operator: SELECT ID, Last_Name, City, State, Zip FROM Person WHERE Last_Name BEGINS WITH 'Ab'; Following is the result table: ID (9 bytes)

Last_Name (26 bytes)

City (31 bytes)

State Zip (3 bytes) (11 bytes)

175-05-3812

Abad

Austin

TX

78722-1307

177-56-5161

Abaecherli

Austin

TX

78749-1225

717-61-2272

Abebe

Austin

TX

78701

241-11-1587

Abel

Austin

TX

78705

605-82-2311

Abgoon

Austin

TX

78704-2353

631-03-5801

Abken

Austin

TX

78704-2834

243-62-1722

Abu

Austin

TX

78701

246-14-1857

Abuali

Austin

TX

78757-2021

CONTAINS The following example uses the CONTAINS operator: SELECT First_Name FROM Person WHERE First_Name CONTAINS 'nd';

Database Design Guide

108

Retrieving Data

Following is a portion of the result table: First Name (16 bytes) Randy Cindy Linda Randy Andrew

LIKE The following example uses the LIKE operator: SELECT ID, First_Name, Last_Name FROM Person WHERE Zip LIKE '787__-____'; Following is a portion of the result table: ID (9 bytes)

First_Name (16 bytes)

Last_Name (26 bytes)

101-13-5758

Ismail

Badad

103-65-7107

Elaine

Bald

106-16-8457

Richard

Baldwin

108-68-9806

Randy

Beavis

111-19-1115

Anthony

Beer

Database Design Guide

109

Retrieving Data

The next example retrieves all person rows in which the character string ll is part of the person’s last name: SELECT ID, First_Name, Last_Name FROM Person WHERE Last_Name LIKE '%ll%'; In the preceding example, the percent signs are wildcards. If a percent sign or underscore is part of the string for which you are searching with a LIKE condition, precede the character with a backslash (\). The backslash indicates that Scalable SQL should treat the character as a regular character, not a wildcard. For example, to determine if any course names include an underscore character, enter the following statement: SELECT Name, Dept_Name, Description FROM Course WHERE Name LIKE '%\_%'; To include a backslash in the search string, precede it with another backslash (\\).

NOT LIKE The following example uses the NOT LIKE operator: SELECT Zip, Last_Name FROM Person WHERE Zip NOT LIKE '78759%';

Database Design Guide

110

Retrieving Data

Following is a portion of the result table: Zip

Last Name

78758-6394

Badad

78722-2012

Bald

78703-5504

Baldwin

78731-3202

Beavis

78748-1846

Beer

Expressions An expression is an element of a condition in a restriction clause, an element in a selection list, or an element on the right hand side of a SET statement. Expressions consist of operands and can also include an expression operator. See the section “Expression Operators” for more information. When you call any string scalar function (except for LENGTH) with a fixed-length string data type, the result is the same data type as the argument. Any string function (except for LENGTH) called with a variable-length string data type returns the result as a ZSTRING. The following syntax describes the parts of an expression: expression ::= < constant | column_ref | scalar_function > [expression_operator < constant | column_ref | scalar_function >...] column_ref ::= [ column_qualifier. ] < column_name > column_qualifier ::= < alias | table_name | view_name > Database Design Guide

111

Retrieving Data

Expression Rules The following rules apply to Scalable SQL expressions:

‹

You must enclose character string constants in single quotation marks. Using the Course table as an example, Name is a column reference, ART101 is a constant expression, and = is an expression operator. These can be used to form the following expression: Name = 'ART101'

‹ ‹ ‹ ‹

String constants cannot exceed 255 bytes in length. You must enter all values as character values. The maximum length of a Scalable SQL expression is limited only by available memory. The rightmost expression of the following expression operators must be a subquery, list of columns, or a constant value: < > = <= >= <> !=

The right side can be an expression or a subquery.

IN NOT IN NOT =ANY !=ANY =ALL !=ALL

The right side must be a subquery.

Database Design Guide

112

Retrieving Data

‹

EXISTS NOT EXISTS

There is no left side; the right side is a subquery.

CONTAINS NOT CONTAINS LIKE NOT LIKE BETWEEN NOT BETWEEN BEGINS WITH

The right side may not be a subquery.

You must specify constant values using the data type’s default mask when comparing the constant to a column value, or when the constant is used as a column’s data value.

Expression Operators An expression operator is an element of a condition that connects two expressions to form another expression. An expression operator can be an arithmetic operator or a string operator. Scalable SQL allows you to use arithmetic and string operators to create computed columns.

Numeric Computations The following table lists the data types of the value Scalable SQL generates based on the data types of the columns (as operands) in the computation. The terms Decimal, Float, and Integer refer to the categories of data types listed in the following table.

Table 5-4

Numeric Computations

Computation

Resulting Data Type

float operator [decimal or integer]

Same as data type and size used in FLOAT.

Database Design Guide

113

Retrieving Data

Table 5-4

Numeric Computations continued

Computation

Resulting Data Type

[decimal or integer] operator float

Same as data type and size used in FLOAT.

float operator float

If both types are BFLOAT or one is BFLOAT size 8 and the other is FLOAT size 4, the result is BFLOAT. In all other cases, the result is FLOAT. Size is always the largest size.

decimal operator integer

Determined by precision and scale rules.

decimal operator decimal

Decimal category. Data type determined by precision and scale rules.

integer operator decimal

Determined by precision and scale rules.

integer operator integer

Integer category. Determined by integral promotion. Refer to the “Integral Promotion” section for more information.

When you specify an expression based on numeric data types, the data type of the result depends on the data types of the operands in the expression. The following table lists the categories of data types that are used in the computations in Table 5-4.

Table 5-5

Data Type Categories

Category

Data Types

Decimal

CURRENCY, DECIMAL, MONEY, NUMERIC, NUMERICSA, NUMERICSTS

Float

BFLOAT, FLOAT

Integer

AUTOINC, INTEGER, UNSIGNED

Database Design Guide

114

Retrieving Data

Precision and Scale Rules The precision of the result of an expression is the total number of digits. The scale of the result of an expression is the number of digits to the right of the decimal point. Both are determined according to the following equation. Result Scale = max (S1, S2) Result Precision = max [(P1 — S1), (P2 — S2)] + Result Scale where P1 P2 S1 S2

= = = =

Precision of operand 1 Precision of operand 2 Scale of operand 1 Scale of operand 2

As you can see from these rules, the data type of the expression’s result is one that can hold the largest value, with no loss of precision or scale from either operand. If no data type is large enough to prevent loss of precision or scale, the data type that loses the least amount of precision and scale is the resulting data type. The data type must be from either the category of the left or right operand. If the result precision is greater than the maximum precision for the result data type, you can determine the result by reducing the result precision and result scale equally until the result precision is less than or equal to the maximum precision for the result data type.

Integral Promotion Integral promotion is the process of promoting a value from one integral data type into a value in another integral data type of an equal or greater capacity.

Database Design Guide

115

Retrieving Data

Integral promotion occurs when the two operands are of different types, different sizes, or when promotion could prevent data loss. The following table shows the integral promotions that occur between two integral operands. In all cases, the promotions never promote to 8 bytes unless at least one of the operands has a size of 8 bytes.

Table 5-6

Integral Promotions Between Integral Operands

Operand 1

Operand 2

Result

Type

Size

Type

Size

Type

Size

UNSIGNED INTEGER

1

UNSIGNED INTEGER

1

INTEGER

2

UNSIGNED INTEGER

1

UNSIGNED

2

INTEGER

4

UNSIGNED INTEGER

1

UNSIGNED

4, 8

UNSIGNED

Size of operand 2

UNSIGNED INTEGER

1

AUTOINC INTEGER

2

INTEGER

4

UNSIGNED INTEGER

1

AUTOINC INTEGER

4, 8

INTEGER

Size of operand 2

AUTOINC INTEGER

2, 4, 8

UNSIGNED INTEGER

1

INTEGER

Size of operand 1

AUTOINC INTEGER

2, 4

UNSIGNED

2, 4

INTEGER

4

AUTOINC INTEGER

2, 4, 8

UNSIGNED

8

INTEGER

8

AUTOINC INTEGER

8

UNSIGNED

2, 4

INTEGER

8

UNSIGNED

2, 4

AUTOINC INTEGER

2, 4

INTEGER

4

Database Design Guide

116

Retrieving Data

Table 5-6

Integral Promotions Between Integral Operands continued

Operand 1

Operand 2

Result

Type

Size

Type

Size

Type

Size

UNSIGNED

2, 4

AUTOINC INTEGER

8

INTEGER

8

UNSIGNED

8

AUTOINC INTEGER

2, 4, 8

INTEGER

8

UNSIGNED

2

UNSIGNED

2

INTEGER

4

UNSIGNED

2, 4

UNSIGNED

4

UNSIGNED

4

UNSIGNED

4

UNSIGNED

2, 4

UNSIGNED

4

UNSIGNED

2, 4, 8

UNSIGNED

8

UNSIGNED

8

UNSIGNED

8

UNSIGNED

2, 4

UNSIGNED

8

AUTOINC INTEGER

2, 4

AUTOINC INTEGER

2, 4

INTEGER

4

AUTOINC INTEGER

2, 4, 8

AUTOINC INTEGER

8

INTEGER

8

AUTOINC INTEGER

8

AUTOINC INTEGER

2, 4

INTEGER

8

Arithmetic Operators You can use arithmetic operators in computed column expressions that contain columns of data types INTEGER, UNSIGNED, CURRENCY, FLOAT, BFLOAT, NUMERIC, NUMERICSA, NUMERICSTS, AUTOINC, DECIMAL, or MONEY. Table 5-7 lists the arithmetic operators.

Database Design Guide

117

Retrieving Data

Table 5-7

Arithmetic Operators

Operator Description +

Adds two expressions.

–

Subtracts the first expression from the second.

*

Multiplies two expressions.

/

Divides the first expression by the second, returning the quotient and remainder.

//

Divides the first expression by the second, returning only the integer quotient.

%

Calculates the modulus or MOD, returning only the remainder.

For example, a table might include one column that contains a student’s credit hours and another that contains the cost per credit. For Scalable SQL to calculate a computed column, such as tuition, specify an expression to multiply the two column values. CREATE VIEW Credit (CurStudent, Credits) AS SELECT Student_ID, SUM(Credit_Hours) FROM Enrolls e, Class cl, Course c WHERE e.Class_ID = cl.ID AND cl.Name = c.Name GROUP BY Student_ID; SELECT CurStudent, Credits, (Credits * Cost_Per_Credit) [$ZZ,ZZZ.99] FROM Credit, Student s, Tuition t WHERE CurStudent = s.ID AND s.Tuition_ID = t.ID; DROP VIEW Credit; Note

If you use the / or // operators to divide by 0, you receive Status Code 346.

Database Design Guide

118

Retrieving Data

String Operators Scalable SQL provides three operators that allow you to specify expressions for fixedlength string operands (columns).

Table 5-8

String Operators

Operator Description *

Concatenates columns while separating them with a blank.

+

Concatenates string columns and strips all trailing blanks from the first column in the expression.

++

Concatenates string columns but leaves trailing blanks in the first column of the expression intact.

When you specify an expression based on different string data types (such as LSTRING and CHARACTER), the resulting data type is ZSTRING. You can use LVAR or NOTE as one of the different types, but the size of the resulting ZSTRING is limited to the maximum for ZSTRING (255). For LSTRING columns, the length byte determines the length of the value. For ZSTRING columns, the null terminator determines the length of the value. For both of these data types, Scalable SQL considers any blanks at the end of the data to be significant and does not strip them for the * and + operators. In effect, the ++ operator is significant only for CHARACTER columns, since the result when using the + and ++ operators is the same for LSTRING and ZSTRING columns. The following example uses the + and * operators: SELECT Last_Name + ',' * First_Name FROM Person;

Database Design Guide

119

Retrieving Data

Scalable SQL concatenates the string columns while deleting blanks between the columns, producing results such as the following: Baldwin, Richard

Conditional Expression Operators Scalable SQL provides two operators that allow you to specify conditions with expression operations.

Table 5-9

Conditional Computed Column Operators

Operator

Description

??

This operator means Then.

::

This operator means Else.

You can use these operators in SELECT statements to manipulate values based on criteria. The following example selects the class name, room number, room capacity, and maximum class size from the sample database. If the capacity of a room is greater than the maximum class size, then the ClassCanExpand field is TRUE; otherwise, the field is FALSE. select class.name, room.number, room.capacity, class.max_size, class.max_size < room.capacity ?? TRUE :: FALSE AS ClassCanExpand from class, room where class.room_number = room.number Note

The ?? and :: operators are not supported in the Delphi development environment.

Database Design Guide

120

Retrieving Data

Date and Time Arithmetic Scalable SQL supports date and time arithmetic in expressions. The date or time you specify can be either a constant or a column value. You can also use the SQL functions CURDATE, CURTIME, and CURRENT_TIMESTAMP to specify the current system date, current system time, and current system timestamp. When you use a statement for date or time arithmetic, the data type of the result is fixed. For example, a date subtracted from another date always results in an integer value representing the number of days.

Date Arithmetic Scalable SQL uses days as the basis for date arithmetic. All date computations are based on the difference in days between two dates. Valid expressions for date arithmetic include the following:

‹ ‹ ‹ ‹

A constant that corresponds to the default date mask. A date value from a column. The CURDATE function (the current system date). An integer value that represents a number of days.

Table 5-10 lists some valid date computations.

Table 5-10

Date Computations

Computations

Result

date – date

number of days

date + number of days

date

date – number of days

date

Database Design Guide

121

Retrieving Data

Table 5-10

Date Computations continued

Computations

Result

CURDATE – date

number of days

CURDATE + number of days

date

CURDATE – number of days

date

The following example retrieves all classes scheduled to begin in the next two days: SELECT ID, Name, Section FROM Class WHERE (Start_Date - CURDATE) <= 2 AND (Start_Date - CURDATE) >= 0;

Time Arithmetic Scalable SQL uses hundredths of a second as the basis for time computations. All times are based on a 24-hour clock. Valid expressions for time arithmetic include the following:

‹ ‹ ‹ ‹

A constant that corresponds to the default time mask. A time value from a column. The CURTIME function (the current system time). An integer value that represents a time of day or elapsed time expressed in hundredths of a second.

Database Design Guide

122

Retrieving Data

Table 5-11 lists some valid time computations.

Table 5-11

Time Computations

Computations

Result

time – time

time

time + time

time

time + hundredths of a second

time

time – hundredths of a second

time

CURTIME – time

time

CURTIME + time

time

CURTIME + hundredths of a second

time

CURTIME – hundredths of a second

time

The following example lists all classes that begin today before noon. SELECT ID, Name, Section FROM Class WHERE Start_Time <= 12:00:00;

Expression Examples This section provides examples of Scalable SQL expressions.

Database Design Guide

123

Retrieving Data

Append (*) In this example, the * operator appends three string columns (from the Course table) and inserts blanks between the columns: SELECT Dept_Name * Name * Description FROM Course; The result of this operation on a particular row might be the following: Accounting ACC 203 Intermediate Accounting I

Concatenate with Blanks (++) In the following example, the ++ operator concatenates two string columns without deleting blanks between them. SELECT Dept_Name ++ Name FROM Course; The result of this operation on some particular rows might be the following: Accounting ACC 101 Anthropology ANT 405

Concatenate without Blanks (+), LENGTH, and RTRIM The following example removes the trailing blanks from the string in the Dept_Name column, then concatenates the Name column without leaving any blanks between them and determines the length of the new string. SELECT Dept_Name, Name, LENGTH(RTRIM(Dept_Name) + Name) FROM Course;

Database Design Guide

124

Retrieving Data

This operation yields results similar to the following: Anthropology ANT 405 19 Art ART 101 10 Modifying the previous example shows how you can apply arithmetic operators to the results of scalar functions. Suppose you need to calculate the number of characters for signs for each building. The signs will say “Pervasive University [Building Name],” and you have to allow for twice the number of characters needed. (There are 20 characters in “Pervasive University.”) SELECT (20 + LENGTH(RTRIM(Building_Name))) * 2 FROM Room; Once Scalable SQL determines the number of letters in a building name, it adds 18 to this number and then multiplies the result by 2.

Database Design Guide

125

Retrieving Data

Functions Once your database contains data, you can use functions on the data to return a result for a set of column values (using aggregate functions) or accept one or more parameters as input and return a single value (using scalar functions).

Aggregate Functions An aggregate function is a function that returns a single result for a given set of column values. Scalable SQL supports the aggregate functions shown in Table 5-12.

Table 5-12

Aggregate Functions

Function

Description

AVG

Determines the average of a group of values. If the operand is not a DECIMAL, then AVG returns an 8-byte FLOAT. If the operand is a DECIMAL, AVG returns a 10-byte DECIMAL.

COUNT

Counts the number of rows in a specified group. COUNT always returns a 4-byte INTEGER.

MAX

Returns the maximum value of a group of values. MAX returns the same data type and size as the operand.

MIN

Returns the minimum value of a group of values. MIN returns the same data type and size as the operand.

SUM

Determines the sum of a group of values. If the operand is not a DECIMAL, then SUM returns an 8-byte FLOAT. If the operand is a DECIMAL, SUM returns a 10-byte DECIMAL.

For more information about each of these functions, refer to the SQL Language Reference. Database Design Guide

126

Retrieving Data

Arguments to Aggregate Functions For AVG and SUM functions, the argument to the function must be the name of a numeric column. The COUNT, MIN, and MAX functions can provide results on numeric or nonnumeric columns. You cannot nest aggregate function references. For example, the following reference is not valid: SUM(AVG(Cost_Per_Credit)) You can use aggregate functions in an expression, as in the following example: AVG(Cost_Per_Credit) + 20 You can also use an expression as an argument to a group aggregate function. For example, the following expression is valid: AVG(Cost_Per_Credit + 20) The aggregate functions treat null column values as significant. For example, on a table that contains 40 rows of data and 5 rows of null values, the COUNT function returns 45. You can use the DISTINCT keyword to force Scalable SQL to treat all null column values as a single value. The following example calculates the average column value in the Grade column: AVG(DISTINCT Grade) The DISTINCT keyword affects the AVG, COUNT, and SUM functions. It has no effect on the MIN and MAX functions.

Database Design Guide

127

Retrieving Data

Aggregate Function Rules You can use aggregate functions in a SELECT statement as follows:

‹ ‹

As items in the selection list. In a HAVING clause.

Generally, you use aggregate functions in a SELECT statement that contains a GROUP BY clause to determine aggregate values for certain groups of rows. However, if the SELECT statement does not contain a GROUP BY clause and you want to use aggregate functions in it, all the items in the selection list must be aggregate functions. If the SELECT statement does contain a GROUP BY clause, the column or columns specified in the GROUP BY clause must be select terms that are single columns, not aggregate functions. All the select terms that are not also listed in the GROUP BY clause, however, must be aggregate functions. The following example returns a result table that allows you to determine the amount each student has paid. SELECT Student_ID, SUM(Amount_Paid) FROM Billing GROUP BY Student_ID; You can also include aggregate functions in HAVING clauses used with a GROUP BY clause. Using the HAVING clause with a GROUP BY clause restricts the groups of rows Scalable SQL returns. Scalable SQL performs the aggregate function on the column of each group of rows specified in the GROUP BY clause, and returns a single result for each set of rows that has the same value for the grouping column.

Database Design Guide

128

Retrieving Data

In the following example, Scalable SQL returns row groups only for students currently enrolled with more than 15 credit hours: SELECT Student_ID, SUM(Credit_Hours) FROM Enrolls e, Class cl, Course c WHERE e.Class_ID = cl.ID AND cl.Name = c.Name GROUP BY Student_ID HAVING SUM(Credit_Hours) > 15; When you use a HAVING clause without a GROUP BY clause, Scalable SQL does not divide the rows of the table into multiple groups. Instead, Scalable SQL treats the rows of the entire table as one large group when it applies the aggregate function, as in the following example: SELECT SUM(Amount_Owed) FROM Billing HAVING COUNT(*) > 200; Using the Billing table as an example, this statement returns the sum of the amount owed only if there are more than 200 rows in the table.

Scalar Functions Scalar functions—STRING, NUMERIC, DATE, TIME, and CAST—accept one or more parameters as input and return a single value. For example, the LENGTH function returns the length of a string column value. You can use scalar functions in Scalable SQL statements that allow computed columns in expressions. The type of expression operator you can use depends on the type of result the function returns. For example, if the function returns a numeric value, you can use arithmetic operators. If the function returns a string value, you can use string operators.

Database Design Guide

129

Retrieving Data

You can nest scalar functions, but each nested function must return a result that is an appropriate parameter to the next level scalar function, as in the following example: SELECT RIGHT (LEFT (Last_Name, 3), 1) FROM Person; Scalable SQL executes the LEFT function first. If the value in the Last Name column is Baldwin, the string resulting from the LEFT function is Bal. This string is the parameter of the RIGHT function, which returns ‘l’ as the rightmost character of the string. You can use scalar functions that return a numeric result within a computed column that calculates a numeric value. You can also use scalar functions that return a string value as an expression to another string function, but the total length of the string result cannot exceed 255 bytes.

String and Numeric Functions For each of the string scalar functions, the function’s parameter may be a character string, the name of a column that is specified as a fixed-length or variable-length string type, or an expression that evaluates to a string. Table 5-13 lists the string scalar functions.

Table 5-13

String Scalar Functions

Function

Description

LEFT

Returns a specified number of characters from a string, beginning with the leftmost character.

LENGTH

Returns the length of a string in bytes.

LOWER

Converts all characters to lowercase.

Database Design Guide

130

Retrieving Data

Table 5-13

String Scalar Functions continued

Function

Description

LTRIM

Removes leading spaces.

RIGHT

Returns a specified number of characters from a string, beginning with the rightmost character.

RTRIM

Removes trailing spaces.

SUBSTRIN G

Returns a portion of a string specified by a start position and a length parameter.

UPPER

Converts all characters to uppercase.

The LENGTH and SUBSTRING functions can also accept numeric data types as parameters. Table 5-14 shows the numeric scalar functions.

Table 5-14

Numeric Scalar Functions

Function

Description

LENGTH

When used with a column value or a constant that is not a string, returns the internal storage length of the column value or constant.

ROUND

Performs arithmetic rounding to the specified number of places.

SUBSTRIN G

When used with a BIT, INTEGER, or AUTOINC column, returns a packed set of bit columns (4-byte INTEGER value) based on the parameters you specify.

TRUNC

Truncates the numeric value to the specified number of places.

Database Design Guide

131

Retrieving Data

String Functions For most of the string functions, the function’s first parameter may be a string or any expression that evaluates to a string (including the name of a column defined as a fixedlength or variable-length string type). The exceptions to this rule are the LENGTH and SUBSTRING functions; these functions can accept parameters that reference non-string data types. Refer to “LENGTH” and “SUBSTRING” for more information. Note

String functions called with a fixed-length string data type return the result as the same data type. String functions called with a variable-length data type return the result as a character. The only exception to this rule is the LENGTH function, which returns the INTEGER data type.

You can use string functions that return a numeric result (as numeric operands within expressions). You can use string functions that return a character value as operands to other string functions as long as the total length of the result does not exceed 255 bytes. Scalable SQL considers trailing blanks in a character value to be significant for comparisons in restriction clauses and when you use the LENGTH function to return the length of a string column. (A character value is any expression that includes only character column values or constant values.) Use the LTRIM, RTRIM, or SUBSTRING functions described in this section to ensure that the lengths of string columns you compare are the same.

LEFT The scalar function LEFT returns a specified number of characters from the string parameter, starting with the first character on the left. If the length you specify is longer

Database Design Guide

132

Retrieving Data

than the string from which you are extracting the returned string, Scalable SQL pads the returned string with spaces on the right to achieve the designated length. Example

Result

left(Last_Name, 3)

Bal

This example shows how the LEFT function works on the Person table, using the Last Name column value Baldwin.

LENGTH Unlike most other string functions, the LENGTH function can accept parameters that reference non-string data types:

‹ ‹ ‹

If the parameter is a column name, the column does not have to be a string data type. If the parameter is an expression, the expression does not have to evaluate to a string data type. If the parameter is a constant, the constant does not have to be a string.

In these cases, the LENGTH function returns the internal storage length of the column, expression result, or constant, respectively. The value the LENGTH function returns is a 4-byte integer.

Database Design Guide

133

Retrieving Data

With a parameter that does reference a string data type, the function returns the length of the string or column in bytes. Scalable SQL determines the length for string types as follows: Data Type

Length Returned

CHARACTE R

Length of the character string, including trailing spaces.

LSTRING

Value of the length byte at the beginning of the data.

LVAR

Length of the data, including length words.

NOTE

Length of the data, including the delimiter.

ZSTRING

Length of the data, up to binary 0 (not including the binary 0 terminator).

The following examples show how the LENGTH function works. In the following example, the parameter is a string. Example 1

Result

LENGTH(’abcdef’)

6

In Example 2, the parameter is a column of data type DATE. Example 2

Result

LENGTH(Person.Date_of_Birth)

4

Database Design Guide

134

Retrieving Data

In Example 3, the parameter is an integer constant, and Scalable SQL returns the internal size of a 4-byte integer. Example 3

Result

LENGTH(10)

4

LOWER The LOWER function converts all characters in the specified string to lowercase. This example shows how the LOWER function works on the Person table, using the Last Name column value Baldwin. Example

Result

LOWER(Last_Name)

baldwin

LTRIM The LTRIM function removes leading spaces from the specified string parameter. This example shows how the LTRIM function works on the column value “ abc” in a column named String. Example

Result

LTRIM(string)

abc

Database Design Guide

135

Retrieving Data

RIGHT The RIGHT function extracts the last n characters from any expression that evaluates to a string, where n is the number of characters to extract. Example 1 shows how the RIGHT function works on the Person table, using the Last Name column value Baldwin. Example 1

Result

RIGHT(Last_Name, 2)

in

Example 2 shows how the RIGHT function works on the Person table, using the Last Name column values Turnipseed, Tuten, Tuyahov, Tuyes, Twelvetrees, and Twentyman. In this example, n is longer than the sizes of the strings extracted. Consequently, Scalable SQL pads each string with spaces on the left until the string is n characters long. You can use this feature to right justify character strings. Example 2 SELECT RIGHT (RTRIM (Last_Name), 15) FROM Person WHERE Last_Name > 'Turn' AND Last_Name < 'Twitch' ORDER BY Last_Name;

Result Turnipseed Tuten Tuyahov Tuyes Twelvetrees Twentyman

Note

Since the RIGHT function retains trailing blanks in CHARACTER, ZSTRING, LSTRING, NOTE, and LVAR data, the resulting data may not appear right justified. In this case, you must use the RTRIM function to remove trailing blanks.

Database Design Guide

136

Retrieving Data

RTRIM The RTRIM function removes trailing blanks from the specified string. This example shows how the LTRIM function works on the column value “abc ” in a column named String. Example

Result

rtrim(string)

abc

SUBSTRING You can specify SUBSTRING using its shortened form, SUBSTR. The parameters for the SUBSTRING function are as follows (in order):

‹ ‹ ‹

An expression. The start position (relative to 1). The length of the portion of the data value to be returned.

The length parameter is optional; if you omit it, Scalable SQL calculates the length of the substring as: LENGTH(data) – start position + 1 The smallest length SUBSTRING can return is one byte. SUBSTRING can return two different types of information, depending on the value you specify in the expression parameter:

‹

If the expression parameter is a string value or evaluates to a string, the function returns the portion of the string that the start position and the length parameter

Database Design Guide

137

Retrieving Data

specify. Examples 1, 2, and 3 show how the SUBSTRING function works on the sample database table Room, using the Building Name column. Example 1

Result

SELECT DISTINCT Building_Name, SUBSTRING(Building_Name, 1, 5) FROM Room WHERE Building_Name BEGINS WITH 'K';

Kimball Building Kimba Klinetob Building Kline

Example 2

Result

SELECT DISTINCT Building_Name, SUBSTRING(Building_Name, 6) FROM Room WHERE Building_Name BEGINS WITH 'K';

Kimball Building ll Building Klinetob Building tob Building

Example 3

Result

SELECT DISTINCT Building_Name, SUBSTRING(Building_Name, 1, 5) + SUBSTRING(Building_Name, 6) FROM Room WHERE Building_Name BEGINS WITH 'K';

Kimball Building Kimball Building Klinetob Building Klinetob Building

Database Design Guide

138

Retrieving Data

‹

If the expression you specify evaluates to a BIT, INTEGER, or AUTOINC value, the SUBSTRING function returns a 4-byte integer value. In this case, SUBSTRING functions according to the following rules: Š SUBSTRING ignores the specified start position. (You must still set this parameter to 1, since SUBSTRING evaluates it as a value of 1.) Š The length specifies the number of contiguous bits to return, beginning with the loworder bit of the specified column. If the number of bit positions specified is less than 32 (4 bytes), then the extra bit positions are zero-filled in the returned data.

Example 4

Result

SELECT Capacity, SUBSTRING(Capacity, 1, 4) FROM Room WHERE Building_Name BEGINS WITH 'Faske';

2 2 40 60 50 1

2 2 8 12 2 1

The following chart shows the binary values for the Capacity column and the rightmost 4 bits, as well as the decimal value of the rightmost 4 bits: Capacity

Binary

Rightmost Four

Decimal Value

2

10

0010

2

40

101000

1000

8

60

111100

1100

12

50

110010

0010

2

1

1

0001

1

Database Design Guide

139

Retrieving Data

Example 4 shows the SUBSTRING function returning a 4-byte integer value. The expression is the name of a 2-byte INTEGER column (Intcolumn). If the value in Intcolumn is decimal 35, SUBSTRING returns the integer value for the rightmost 4 bits of the binary value of 35, which is 00100011. The rightmost 4 bits are 0011.

UPPER The UPPER function converts characters from lowercase to uppercase. You may specify this parameter as either a string value or a column name. This example shows how the UPPER function works on the sample database table Person, using the Building Name column value Young Building. Example

Result

SELECT UPPER(Building_Name) YOUNG BUILDING FROM Room WHERE Building_Name BEGINS WITH 'Young';

Numeric Functions You can use the numeric scalar functions to correct rounding errors that may occur as a result of floating-point math operations. Scalable SQL supports the ROUND and TRUNC functions for all numeric data types. Note

ASCII decimal values stored as FLOAT or BFLOAT values are subject to the normal approximation that floating point normalization requires. The ROUND and TRUNC functions do not correct inaccuracies that result from this approximation.

Database Design Guide

140

Retrieving Data

ROUND Following the evaluation of an expression, the ROUND function performs arithmetic rounding to n spaces, where n represents the number of places to round. The syntax of the function is as follows: ROUND (expression, n) You can apply the ROUND function to either a numeric constant or an arithmetic expression. With an arithmetic expression, Scalable SQL applies ROUND to the resulting value. The result of the ROUND function is a value with the same data type and length as the expression. If the specified number of places to round is positive, Scalable SQL rounds the designated number of places to the right of the decimal point. If the specified number is negative, Scalable SQL rounds the designated number of places to the left of the decimal point. Example 1 specifies rounding the expression to the decimal point; both 0 and -0 round to the decimal point. Example 1

Result

SELECT Cumulative_GPA, ROUND(Cumulative_GPA, -0) FROM Student;

4.000 1.231 2.636 2.239

4.000 1.000 3.000 2.000

Example 2 specifies rounding the expression two places to the right of the first positive digit. Example 2

Result

SELECT Cumulative_GPA, ROUND(Cumulative_GPA, 2) FROM Student;

4.000 1.231 2.636 2.239

Database Design Guide

4.000 1.230 2.640 2.240 141

Retrieving Data

TRUNC Following the evaluation of an expression, the TRUNC function truncates a numeric value to n spaces, where n represents the number of places to truncate. The syntax of the function is as follows: TRUNC (expression, n) You can apply the TRUNC function to either a numeric constant or an arithmetic expression. With an arithmetic expression, Scalable SQL applies TRUNC to the resulting value. The result of the TRUNC function is a value with the same data type and length as the expression. If the specified number of places to truncate is positive, Scalable SQL truncates the designated number of places to the right of the decimal point. If the specified number is negative, Scalable SQL truncates the designated number of places to the left of the decimal point. The following example specifies truncating the expression to the decimal point; both 0 and -0 round to the decimal point. Example 1

Result

SELECT Cumulative_GPA, TRUNC(Cumulative_GPA, 0) FROM Student;

4.000 1.231 2.636 2.239

4.000 1.000 2.000 2.000

The following example specifies truncating the expression two places to the left of the decimal point. Example 2

Result

SELECT Cumulative_GPA, TRUNC(Cumulative_GPA, 1) FROM Student;

4.000 1.231 2.636 2.239

Database Design Guide

4.000 1.200 2.600 2.200 142

Retrieving Data

Date Functions You can use the date scalar functions to manipulate DATE column values. The result is always a 4-byte INTEGER value. Only DATE columns are valid for the date functions.

Table 5-15

Date Scalar Functions

Function

Description

DAY

Returns the day value.

MONTH

Returns the month value.

WEEKDAY Returns the day of the week value, where Sunday is 0 and Saturday is 6. For example, if the day is Thursday, the WEEKDAY function returns 4. YEAR

Returns the year value. Note

The examples in this section assume that the value for the start date column is 01/04/96.

DAY This function returns the day value for a DATE column. Example

Result

DAY(Start_Date)

4

Database Design Guide

143

Retrieving Data

MONTH This function returns the month value for a DATE column. Example

Result

MONTH(Start_Date)

1

WEEKDAY This function returns the day of the week value for a DATE column. The returned values correspond to the days of the week, as follows: Sunday

0

Monday

1

Tuesday

2

Wednesday

3

Thursday

4

Friday

5

Saturday

6

In the following example, the weekday that corresponds to the start date of a class is Wednesday. Example

Result

WEEKDAY(Start_Date)

3

Database Design Guide

144

Retrieving Data

YEAR This function returns the year value for a DATE column. Example

Result

YEAR(Start_Date)

1996

Time Functions You can use the time scalar functions to manipulate TIME column values. The result is always a 4-byte INTEGER value. Only TIME columns are valid for the time functions.

Table 5-16

Time Scalar Functions

Function

Description

HOUR

Returns the hour value.

MILLISECOND

Returns the millisecond value.

MINUTE

Returns the minute value.

SECOND

Returns the second value.

Note

The examples in this section assume that the value for the start date column is 01/04/96 and the start time is 08:00:37:26.

Database Design Guide

145

Retrieving Data

HOUR This function returns the hour value for a TIME column. Example

Result

HOUR(Start_Time)

8

MILLISECOND This function returns the millisecond value for a TIME column. Example

Result

MILLISECOND(Start_Time)

260

MINUTE This function returns the minute value for a TIME column. Example

Result

MINUTE(Start_Time)

0

Database Design Guide

146

Retrieving Data

SECOND This function returns the second value for a TIME column. Example

Result

SECOND(Start_Time)

37

Date and Time Function Example This example illustrates the date and time functions. It returns all Accounting classes that start on Monday morning. SELECT Name, Section, MONTH(Start_Date), DAY(Start_Date), YEAR(Start_Date), HOUR(Start_Time), MINUTE(Start_Time) FROM Class WHERE Name BEGINS WITH 'ACC' AND WEEKDAY(Start_Date) = 1 AND Start_Time < 12:00:00; This example might yield the following results: Name

Section

Month

Day

Year

Hour

Minute

ACC 101

001

6

5

1995

8

0

ACC 102

003

6

5

1995

8

0

ACC 203

001

6

5

1995

11

0

ACC 204

001

6

5

1995

11

0

Database Design Guide

147

Retrieving Data

CAST Function The CAST function converts an expression to a specific data type. The CAST function can be used in any expression to connect a constant value to a different data type or different mask format. You can specify a mask to apply to the expression when converting to the specified data type. The syntax of the function is as follows: CAST (expression AS < data_type | [ TYPE [ OF ] ] column_reference > [ < [ MASK ] edit_mask | [ MASK [ OF ] ] column_reference | [ MASK ] DEFAULT > ] ) ) data_type ::= < AUTOINC | BFLOAT | BIT | CHAR | CHARACTER | CURRENCY | DATE | DEC | DECIMAL | FLOAT | INT | INTEGER | LOGICAL | LSTRING | LVAR | MONEY | NOTE | NUMERIC | NUMERICSA | NUMERICSTS | TIME | TIMESTAMP | UNSIGNED |

ZSTRING > column_reference ::= [ column_qualifier. ] column_name

Database Design Guide

148

Retrieving Data

Table 5-17 shows the valid conversions.

Table 5-17

CAST Function Data Type Conversions Target Data Type Numeric

String

Boolean

Date

Time

Timestamp

Valid

Valid

Not Valid

Valid

Valid

Valid

Original Data Type

Numeric

except INTEGER

String

Valid

Valid

Valid

Valid

Valid

Valid

Boolean

Not Valid

Valid

Valid

Not valid

Not valid

Not valid

Date

Valid

Valid

Not valid

Valid

Not valid

Valid

Time

Valid

Valid

Not valid

Not valid

Valid

Valid

Timestamp Valid

Valid

Not valid

Valid

Valid

Valid

Note

When using the CAST function, Scalable SQL assumes the default data type unless you specify the size of the CAST data type equal to the data type on which the CAST is being performed. If you do not specify the size, you may receive a Status Code 349, “The length exceeds the column length.” The first example returns Status Code 349. The second example specifies the data type size. Incorrect Example: SELECT CAST(SUBSTR(TEST, 1, 255) AS CHAR) Correct Example: SELECT CAST(SUBSTR(TEST, 1, 255) AS CHAR(255))

Database Design Guide

149

Retrieving Data

If you do not specify a mask, Scalable SQL uses the default input format or default displayable mask for the data type size and scale specified. Table 5-18 describes how Scalable SQL uses masks in various data type combinations.

Table 5-18

Masks and the CAST Function

Original Data Type Target Data Type Mask Used

Comments

CHARACTER LSTRING ZSTRING NOTE LVAR

CHARACTER LSTRING ZSTRING NOTE LVAR

String

Scalable SQL applies the mask to the expression before determining the result. Because string masks specify the number of characters in the result data value, some data may be truncated during the cast.

CHARACTER LSTRING ZSTRING NOTE LVAR

INTEGER UNSIGNED AUTOINC CURRENCY MONEY DECIMAL NUMERIC NUMERICSTS NUMERICSA FLOAT BFLOAT

Number

Scalable SQL uses the mask to assist in correctly interpreting the expression. For example, if the expression is ‘12345-6789’ and the mask is ‘999-99-9999’ and the target data type is INTEGER(4), then the result value is 123456789.

CHARACTER LSTRING ZSTRING NOTE LVAR

LOGICAL BIT

Logical

Scalable SQL uses the mask to assist in correctly interpreting the expression. For example, if the expression is ‘YES’ and the mask is ‘YES-NO’, then the result value is TRUE (stored as a nonzero value).

INTEGER

LOGICAL BIT

Database Design Guide

If the value of the expression is not zero, the result value is TRUE. Otherwise, it is FALSE.

150

Retrieving Data

Table 5-18

Masks and the CAST Function continued

Original Data Type Target Data Type Mask Used

Comments

CHARACTER LSTRING ZSTRING NOTE LVAR

DATE

Date

Scalable SQL uses the mask to assist in correctly interpreting the expression. For example, if the expression is ‘12/11/ 1996’ and the mask is ‘dd/mm/yy’, then the result value is November 12, 1996.

CHARACTER LSTRING ZSTRING NOTE LVAR

TIME

Time

Scalable SQL uses the mask to assist in correctly interpreting the expression. For example, if the expression is ‘12:10’ and the mask is ‘hh:mm’, then the result value is 12:10:00:00 (that is, 12:10 in the afternoon, 0 seconds, 0 hundredths of seconds).

CHARACTER LSTRING ZSTRING NOTE LVAR

TIMESTAMP

Timestamp

Scalable SQL uses the mask to assist in correctly interpreting the expression. For example, if the expression is ‘12/11/ 1996 12:10’ and the mask is ‘dd/mm/ yyyy hh:tt’, then the result value is November 12, 1996, at 12:10:00:00 in the afternoon (local time zone).

INTEGER UNSIGNED AUTOINC CURRENCY MONEY DECIMAL NUMERIC NUMERICSTS NUMERICSA FLOAT BFLOAT

CHARACTER LSTRING ZSTRING NOTE LVAR

Number

Scalable SQL uses the mask to assist in formatting the expression in the target data type. For example, if the expression is an INTEGER(4) with a value of 1234567 and the mask is ‘ZZZ,ZZZ,ZZZ’ and the target data type is CHARACTER(11), then the result is ‘ 1,234,567’ (two leading blanks included).

Database Design Guide

151

Retrieving Data

Table 5-18

Masks and the CAST Function continued

Original Data Type Target Data Type Mask Used

Comments

LOGICAL BIT

CHARACTER LSTRING ZSTRING NOTE LVAR

Logical

Scalable SQL uses the mask to assist in formatting the expression in the target data type. For example, if the expression is a LOGICAL with a value of True and the mask is ‘UP-DOWN’ and the target data type is CHARACTER(4), then the result is ‘UP ’ (two trailing blanks included).

DATE

CHARACTER LSTRING ZSTRING NOTE LVAR

Date

Scalable SQL uses the mask to assist in formatting the expression in the target data type. For example, if the expression has a value of November 12, 1996 and the mask is ‘MMM dd, yyy’ and the target data type is CHARACTER(12), then the result is ‘Nov 12, 1996’.

TIME

CHARACTER LSTRING ZSTRING NOTE LVAR

Time

Scalable SQL uses the mask to assist in formatting the expression in the target data type. For example, if the expression has a value of 12:10 in the afternoon and the mask is ‘hh:mm:ss’ and the target data type is CHARACTER(8), then the result is ‘12:10:00’.

Database Design Guide

152

Retrieving Data

Table 5-18

Masks and the CAST Function continued

Original Data Type Target Data Type Mask Used

Comments

TIMESTAMP

CHARACTER LSTRING ZSTRING NOTE LVAR

Timestamp

Scalable SQL uses the mask to assist in formatting the expression in the target data type. For example, if the expression has a value of November 12, 1996, at 12:10 in the afternoon (local time) and the mask is ‘yyyy-mmdd hh:tt:ss.fff’ and the target data type is CHARACTER(23), then the result is ‘1996-11-12 12:10:00.000’.

INTEGER UNSIGNED AUTOINC CURRENCY MONEY DECIMAL NUMERIC NUMERICSTS NUMERICSA FLOAT BFLOAT

INTEGER UNSIGNED AUTOINC CURRENCY MONEY DECIMAL NUMERIC NUMERICSTS NUMERICSA FLOAT BFLOAT

Number

Scalable SQL uses the mask to round the expression before converting it to the target data type. If the expression is an INTEGER, the mask scales the value rather than rounding the value.

LOGICAL BIT

LOGICAL BIT

Logical

The mask is not useful to Scalable SQL in this situation.

DATE TIMESTAMP

DATE

Date

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression is a DATE with a value of November 12, 1920 and the mask is ‘mm/dd’ and the current year is 1996, then the result is November 12, 1996.

Database Design Guide

153

Retrieving Data

Table 5-18

Masks and the CAST Function continued

Original Data Type Target Data Type Mask Used

Comments

TIME

TIME

Time

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression has a value of 12:10:06:03 in the afternoon and the mask is ‘hh:mm’, then the result is 12:10:00:00 in the afternoon.

TIMESTAMP

TIME

Timestamp without a date component

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression has a value of November 12, 1996 at 12:10:06:03.3456 in the afternoon and the mask is ‘hh:mm:ss’, then the result value is 12:10:06 in the afternoon.

DATE

TIMESTAMP

Date

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression has a value of November 12, 1920 and the mask is ‘mm/dd’ and the current year is 1996, then the result value is November 12, 1996 at the current local time.

Database Design Guide

154

Retrieving Data

Table 5-18

Masks and the CAST Function continued

Original Data Type Target Data Type Mask Used

Comments

TIME

TIMESTAMP

Time

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression has a value of 12:10:06:11 in the afternoon and the mask is ‘hh:mm:ss’ and the current day is November 12, 1996, then the result value is November 12, 1996 at 12:10:06 in the afternoon.

TIMESTAMP

TIMESTAMP

Timestamp

Scalable SQL uses the mask to set certain components of the expression to the default value in the result value. For example, if the expression has a value of November 12, 1920 at 12:10:06:3456 in the afternoon and the mask is ‘mm/dd hh:tt:ss.fffffff’ and the current year is 1996, then the result value is November 12, 1996 at 12:10:06:3456 in the afternoon.

Database Design Guide

155

Retrieving Data

When you convert expressions to string data types, the size of the mask determines the number of characters in the result data. Consider the following examples, in which B is a blank: Value Type

Value

Mask

Result Type

Result Value

INTEGER

1

ZZZ

CHARACTER(7)

‘BB1BBBB’

INTEGER

1

ZZZ

ZSTRING(7)

‘BB1’

DATE

May 25, 1992

mm/dd/yy

CHARACTER(12)

‘05/25/92BBBB’

To remove leading blanks in the result value, you can use the LTRIM function. Using the previous examples, the result values would be ‘1BBBB’, ‘1’, and ‘05/25/92BBBB’, respectively. You may want to compare a string value with trailing blanks (such as ‘1BBBB’) to a constant value (such as ‘1’); however, the two values do not compare as equal, because the constant value lacks trailing blanks. To avoid this situation, you can do any one of the following:

‹ ‹ ‹

Specify the size of the CHARACTER result value to be the same as the size of the mask. For example, if the mask is ZZZ, then the result would be CHARACTER(3). Use a result type of ZSTRING or LSTRING and specify the size of the result to be at least 1 more than the size of the mask. For example, if the mask is ZZZ, then the result would be ZSTRING(4). Combine RTRIM functions on the result of the CAST function to remove trailing blanks.

Database Design Guide

156

Retrieving Data

Examples Use the TYPE OF clause to convert an expression to a specific column’s data type. The referenced column must be from a table referenced in the statement. The following example converts a string value to a FLOAT value: CAST ( ‘3.2’ AS TYPE OF Enrolls.Grade ) Use the MASK clause to convert an expression using a mask. The following example converts a string to a DATE value. The mask ensures that the value is interpreted as November 10, 1992: CAST ( ‘10/11/92’ AS DATE MASK [dd/mm/yy] ) The following example presents the expression as a non-string constant. Note that even though the statement includes a mask, Scalable SQL interprets the value as October 11, 1992, because Scalable SQL applies the mask only after first interpreting the constant value, for which mm/dd/yy is the default mask. CAST ( 10/11/92 AS DATE MASK [dd/mm/yy] )

Database Design Guide

157

Retrieving Data

chapter

6

Storing Logic

This chapter explains how to store SQL procedures for future use and how to create triggers. For information about stored views, see Chapter 5, “Retrieving Data.” This chapter includes the following sections:

‹ ‹ ‹ ‹ ‹ ‹

“Stored Procedures” “SQL Variable Statements” “SQL Cursor-Based Statements” “SQL Control Statements” “Retrieving Status Information” “SQL Triggers”

Database Design Guide

158

Storing Logic

Stored Procedures Using stored procedures, you can group logically associated programming steps into a general process and then invoke that process with one statement. You can also execute this process using different values by passing parameters. Once invoked, SQL stored procedures are executed in their entirety without internal communication between a host language program and the SQL engine. You can invoke them independently, and they can be invoked as part of the body of other procedures or triggers. For more information about triggers, refer to the section "SQL Triggers." You can use SQL variable statements within stored procedures to store values internally from statement to statement. See the section “SQL Variable Statements” for more information about these statements. You can use SQL cursor-based statements in row-based data manipulation statements within stored procedures. For details about these statements, see the section "SQL Cursor-Based Statements." You can use SQL control statements in stored procedures to control the execution flow of the procedure. For more information about these statements, refer to the section “SQL Control Statements” later in this chapter.

Database Design Guide

159

Storing Logic

Declaring Stored Procedures To define a stored procedure, use the CREATE PROCEDURE statement. CREATE PROCEDURE EnrollStudent (Stud_id INT (4), Class_Id INT (4)); BEGIN INSERT INTO Enrolls VALUES (Stud_id, Class_Id, 0.0); END The maximum size for a stored procedure name is 30 characters. Parentheses are required around the parameter list, and the parameter name may be any valid SQL identifier. Stored procedures must have unique names in the dictionary. When a declaration of a stored procedure is passed to the Scalable SQL engine and a previous stored procedure declaration with the same name exists, Scalable SQL returns Status Code 366. For information about the syntax of the CREATE PROCEDURE statement, refer to the SQL Language Reference.

Invoking Stored Procedures To invoke a stored procedure, use the CALL statement. CALL EnrollStudent (274410958, 50); You must define a value for every parameter. You can assign a value to a parameter using the associated argument in the CALL statement or with the associated default clause in the CREATE PROCEDURE statement. An argument value for a parameter in a CALL statement overrides any associated default value.

Database Design Guide

160

Storing Logic

You can specify calling values in a CALL statement using either of the following two ways:

‹ ‹

Positional arguments—Allow you to specify parameter values implicitly based on the ordinal position of the parameters in the list when the procedure was created. Keyword arguments—Allow you to specify parameter values explicitly by using the name of the parameter whose value is being assigned.

You cannot assign a parameter value twice in the argument list (either positional or keyword). If you use both positional arguments and keyword arguments in the same call, the keyword arguments must not refer to a parameter that receives its value through the positional arguments; if they do, Scalable SQL returns Status Code 864. When using keyword arguments, the same parameter name must not occur twice; if it does, Scalable SQL returns Status Code 897. For more information about the syntax of the CALL statement, refer to the SQL Language Reference.

Deleting Stored Procedures To delete a stored procedure, use the DROP PROCEDURE statement. DROP PROCEDURE EnrollStudent; For more information about the syntax of this statement, refer to the SQL Language Reference.

Database Design Guide

161

Storing Logic

SQL Variable Statements SQL variable statements provide a means to store values internally from statement to statement. SQL variable statements include the following two statements:

‹ ‹

SQL variable declaration Assignment statement

You can use these statements either inside stored procedures or outside stored procedures as independent, single SQL statements.

Declaring SQL Variables A SQL variable declaration defines a SQL variable. DECLARE CourseName CHAR(7); You can use SQL variables in the following ways:

‹ ‹ ‹ ‹ ‹ ‹

As FETCH statement target values. In SELECT list expressions. In WHERE clauses. In UPDATE expressions. As INSERT values. In an expression used to assign values to the same or other variables.

For more information about the syntax of this statement, refer to the SQL Language Reference. Database Design Guide

162

Storing Logic

Substitution Variables Substitution variables allow you to create a Scalable SQL statement without supplying specific values for all the options; you specify the values when you execute the statement. Because standard SQL syntax does not provide a way to replace substitution variables with values, you can use substitution variables only with applications that allow you to define values interactively or in batch processing. The following statement allows you to insert data into the Course table by providing the course name, description, and credit hours.

INSERT INTO Course (name, description, credit_hours) VALUES (@vName, @vDescription, @vCreditHours); The identifiers @vName, @vDescription, and @vCreditHours are substitution variables. Before you can execute the statement, you must define values for these variables to Scalable SQL. You can use substitution variables in place of character strings or numeric constants in the following types of clauses:

‹ ‹ ‹ ‹

WHERE HAVING VALUES (in an INSERT statement) SET (in an UPDATE statement) Note

You cannot use substitution variables in place of column names or table names.

Database Design Guide

163

Storing Logic

The following rules apply to the names of substitution variables:

‹ ‹ ‹ ‹ ‹ ‹ ‹

a through z A through Z 0 through 9 _ (underscore) ^ (caret) ~ (tilde) $ (dollar sign)

The syntax description for each type of statement that allows substitution variables includes details about how to specify the variables. Refer to the SQL Language Reference for the syntax description of each SQL statement.

Procedure-Owned Variables A SQL variable you define inside a stored procedure is a procedure-owned variable. Its scope is that procedure in which it is declared; you can only refer to it within that procedure. If a procedure calls another procedure, the procedure-owned variable of the calling procedure cannot be directly used in the called procedure; instead, it must be passed in a parameter. You cannot declare a procedure-owned variable more than once in the same stored procedure; if you do, Scalable SQL returns Status Code 807. If a compound statement is the body of a stored procedure, then no SQL variable name declared in that procedure can be identical to a parameter name in the parameter list of that procedure. For more information about compound statements, refer to "Compound Statement."

Database Design Guide

164

Storing Logic

Session Variables A SQL variable you define outside of any stored procedure is a session variable. Its scope is the user’s login session, and you can refer to it anywhere inside or outside of procedures. Once you have declared a session variable, it remains for the duration of that session. You cannot declare a session variable more than once during the same user’s login session; if you do, Scalable SQL returns Status Code 807. You must declare the SQL variable before any reference to the SQL variable name. However, you can declare the same SQL variable name in different procedures as well as declaring that name as a session variable. If the same SQL variable name appears in a procedure and as a session variable, then a reference to the variable name within that procedure references the procedure-owned variable.

Assignment Statement The assignment statement initializes or changes the values of SQL variables. The value expression may be a computed expression involving constants, operators, and this or other SQL variables. SET CourseName = 'HIS305'; The value expression may also be a SELECT statement. SET MaxEnrollment = (SELECT Max_Size FROM Class WHERE ID = classId); For more information about the syntax of this statement, refer to the SQL Language Reference.

Database Design Guide

165

Storing Logic

SQL Cursor-Based Statements You use SQL cursor-based statements in row-based data manipulation statements. You can use these statements either inside or outside stored procedures as independent, single SQL statements. The cursor-based statements consist of the following:

‹ ‹ ‹ ‹ ‹ ‹

SQL Cursor Declaration Open Cursor Fetch Positioned Delete Positioned Update Close Cursor Note

Developers: Cursors declared with a SQL cursor declaration statement are not the same as the cursor ID allocated as a result of the SQL-level function call, XQLCursor. For more information about these cursors, see the Scalable SQL Programmer’s Guide.

A cursor is in a closed state at the point you declare it, or following a CLOSE statement. You must first open a closed cursor using the OPEN statement before you can use it in any row-based data manipulation statement. Data manipulation statements are based on the current position of the cursor. The current position during the open state of the cursor is either before the first row, on a row, or after the last row. When you initially open a cursor, the position of the cursor is before the first row. If a cursor is on a row, that row is the current row of the cursor. A cursor may be before the first row of a table or after the last row of a table even though the table is empty.

Database Design Guide

166

Storing Logic

A cursor can only move forward through the query results unless you specify the SCROLL option when declaring the cursor. A scroll cursor allows you to move backward as well as forward, or to the first row, the last row, or to a relative row. When declaring a cursor, you can specify whether or not that cursor is read-only or updatable. If you specify FOR UPDATE for the cursor, then the view defined by the query expression must be updatable. In a scroll cursor, the updatability clause is READ ONLY by default. You use the FETCH statement to position an open cursor on a specified row and to retrieve the values of the columns of that row. If you specify any positioning option other than NEXT, then the cursor must be a scroll cursor. To delete the current row of the cursor, use a Positioned DELETE statement. To update the current row of the cursor, use a Positioned UPDATE statement. You must execute a FETCH statement on the cursor before you can issue a Positioned UPDATE or Positioned DELETE statement. To close a cursor, issue a CLOSE statement. Once you close a cursor, you can reopen it with an OPEN statement, which re-establishes the cursor before the first row. For more information about these statements and their syntax, refer to the SQL Language Reference.

SQL Cursor Declaration A DECLARE CURSOR statement defines a SQL cursor. A declared cursor logically ties a SELECT statement to a cursor name. DECLARE cursor1 CURSOR FOR SELECT Name FROM Course WHERE Name = CourseName; The maximum length for a cursor name is 255 characters. Database Design Guide

167

Storing Logic

For more information about the syntax of the DECLARE CURSOR statement, refer to the SQL Language Reference.

Procedure-Owned Cursors A SQL cursor defined inside a stored procedure is a procedure-owned cursor. Its scope is the procedure itself; therefore, you can refer to it only within the procedure.

Session Cursors A SQL cursor you define outside of a procedure is a session cursor. You can reference a session cursor inside a stored procedure. Its scope is the user’s login session. You must declare a SQL cursor before any reference to the cursor name. However, you can declare the same SQL cursor name in one or more procedures as well as declaring that name as a session cursor. If the same SQL cursor name appears in a procedure and as a session cursor, then a reference to the cursor name within that procedure references the procedure-owned cursor.

Open Cursor Statement An OPEN CURSOR statement opens a cursor. OPEN cursor1; The cursor specified by cursor name must not be open when you issue the OPEN statement. The current position of the cursor is before the first row of the table. For more information about the syntax of the OPEN cursor statement, refer to the SQL Language Reference. Database Design Guide

168

Storing Logic

FETCH Statement A FETCH statement positions a SQL cursor on a specified row of a table and retrieves values from that row. The specified cursor name must be open. FETCH NEXT FROM cursor1 INTO CourseName; If you do not specify the fetch orientation, NEXT is the default. If the cursor is not a scroll cursor, then the fetch orientation must be NEXT. For more information about the syntax of the FETCH statement, refer to the SQL Language Reference.

Positioned DELETE Statement A Positioned DELETE statement deletes the current row of a table associated with a SQL cursor. DELETE WHERE CURRENT OF cursor1; The referenced cursor name must be open, and it must be positioned on a row, not before the first row or after the last row. For more information about the syntax of the Positioned DELETE statement, refer to the SQL Language Reference.

Database Design Guide

169

Storing Logic

Positioned UPDATE Statement A Positioned UPDATE statement updates the current row of a table associated with a SQL cursor. The cursor must be open and updatable, and it must be positioned on a row, not before the first row or after the last row. The Positioned UPDATE statement in the following example updates the course description “Modern European History” to “Ancient European History.” DECLARE CourseDescription CHAR(50) = 'Modern European History'; DECLARE OldDescription CHAR(50); DECLARE cursor1 CURSOR FOR SELECT Description FROM Course WHERE Description = CourseDescription; OPEN cursor1; FETCH NEXT FROM cursor1 INTO OldDescription; UPDATE SET Description = 'Ancient European History' WHERE CURRENT OF cursor1; In order to use the Positioned UPDATE statement, you must also use DECLARE CURSOR, OPEN CURSOR, and FETCH FROM cursorname statements. For more information about the syntax of the Positioned UPDATE statement, refer to the SQL Language Reference.

Database Design Guide

170

Storing Logic

SQL Control Statements You can only use control statements in the body of a stored procedure. These statements control the execution of the procedure. The control statements include the following:

‹ ‹ ‹ ‹

Compound statement (BEGIN...END) IF statement (IF...THEN...ELSE) LEAVE statement Loop statements (LOOP and WHILE)

Compound Statement A compound statement groups other statements together. BEGIN DECLARE NumEnrolled INT(4); DECLARE MaxEnrollment INT(4); DECLARE failEnrollment CONDITION FOR SQLSTATE '09000'; SET NumEnrolled = (SELECT COUNT (*) FROM Enrolls WHERE Class_ID = classId); SET MaxEnrollment = (SELECT Max_Size FROM Class WHERE ID = classId); IF (NumEnrolled >= MaxEnrollment) THEN SIGNAL failEnrollment ELSE SET NumEnrolled = NumEnrolled + 1; END IF; END Database Design Guide

171

Storing Logic

You can use a compound statement in the body of a stored procedure or a trigger. For more information about triggers, see the section "SQL Triggers." Although you can nest compound statements within other compound statements, only the outermost compound statement can contain DECLARE statements. For more information about the syntax of compound statements, refer to the SQL Language Reference.

IF Statement An IF statement provides conditional execution based on the truth value of a condition. IF (SQLSTATE = '02000' OR counter = NumRooms) THEN LEAVE Fetch_Loop; END IF; For more information about the syntax of the IF statement, refer to the SQL Language Reference.

LEAVE Statement A LEAVE statement continues execution by leaving a compound statement or loop statement. LEAVE Fetch_Loop A LEAVE statement must appear inside a labeled compound statement or a labeled loop statement. The statement label from the LEAVE statement must be identical to the label of a labeled statement containing LEAVE. This label is called the corresponding label.

Database Design Guide

172

Storing Logic

Note

A compound statement can contain a loop statement; therefore, since you can embed loop statements, the statement label in a LEAVE statement can match the label of any of the embedded loops or the label of the body of the stored procedure.

For more information about the syntax of the LEAVE statement, refer to the SQL Language Reference.

LOOP Statement A LOOP statement repeats the execution of a block of statements. FETCH_LOOP: LOOP FETCH NEXT cRooms INTO CurrentCapacity; IF (SQLSTATE = '02000' OR counter = NumRooms) THEN LEAVE FETCH_LOOP; END IF; SET counter = counter + 1; SET TotalCapacity = TotalCapacity + CurrentCapacity; END LOOP; If each statement in the SQL statement list executes without error and Scalable SQL does not encounter a LEAVE statement or invoke a handler, then execution of the LOOP statement repeats. A LOOP statement is similar to a WHILE statement in that execution continues while a given condition is true. If a LOOP statement has a beginning label, it is called a labeled LOOP statement. If you specify the ending label, then it must be identical to the beginning label. Database Design Guide

173

Storing Logic

For more information about the syntax of the LOOP statement, refer to the SQL Language Reference.

WHILE Statement A WHILE statement repeats the execution of a block of statements while a specified condition is true. FETCH_LOOP: WHILE (counter < NumRooms) DO FETCH NEXT cRooms INTO CurrentCapacity; IF (SQLSTATE = '02000') THEN LEAVE FETCH_LOOP; END IF; SET counter = counter + 1; SET TotalCapacity = TotalCapacity + CurrentCapacity; END WHILE; Scalable SQL evaluates the Boolean value expression. If it is true, then Scalable SQL executes the SQL statement list. If each statement in the SQL statement list executes without error and no LEAVE statement is encountered, then execution of the loop statement repeats. If the Boolean value expression is false or unknown, Scalable SQL terminates execution of the loop statement. If a WHILE statement has a beginning label, it is called a labeled WHILE statement. If you specify an ending label, it must be identical to the beginning label. For more information about the syntax of the WHILE statement, refer to the SQL Language Reference.

Database Design Guide

174

Storing Logic

Retrieving Status Information During execution of a stored procedure, you may need information about the last completed statement. The control flow of your procedure may depend on the completion status of the previous operation. For example, you may retrieve data row by row with a FETCH statement inside a loop in a stored procedure, but you need to know whether or not the FETCH statement returned any data. If it has, the loop can continue; if it has not, the loop should terminate. If the status information shows that an error occurred during the execution of a stored procedure, you may need to take some special steps to resolve the error. You do this through condition handling. The SQLSTATE system variable provides the means for you to check the status of an operation.

SQLSTATE System Variable Whenever Scalable SQL executes a SQL statement (including the call of a stored procedure), a status value is placed in the SQLSTATE system variable. This value indicates either that the statement completed or that an exception was raised during the execution of the statement. SQLSTATE is defined as a string of 5 characters, where the first 2 characters form a class value and the last 3 characters form a subclass value.

Database Design Guide

175

Storing Logic

There are two basic categories of conditions: completion conditions and exception conditions. The following three classes indicate completion conditions:

‹ ‹ ‹

Successful completion: class ‘00’, subclass ‘000’ Completion with warning: class ‘01’, subclass ‘000’ Completion with no data: class ‘02’, subclass ‘000’

All other classes indicate exception conditions. By default, the system continues execution beyond completion conditions, but halts execution when it encounters an exception condition. You can only change this default behavior using condition handlers. For more information about the SQLSTATE values and their meanings, refer to SQL Language Reference.

Exception and Completion Conditions When a statement in a stored procedure returns an exception condition or a completion condition other than a successful completion, Scalable SQL checks to see if the procedure has a declared HANDLER associated with that condition. If it does, then the corresponding handler is invoked. Statements that you can use to handle exception and completion conditions include the following:

‹ ‹ ‹ ‹

DECLARE CONDITION statement DECLARE HANDLER statement SIGNAL statement RESIGNAL statement

Database Design Guide

176

Storing Logic

DECLARE CONDITION Statement A DECLARE CONDITION statement declares a condition and an associated SQLSTATE value. DECLARE endData CONDITION FOR SQLSTATE '02000'; For more information about the syntax of the DECLARE CONDITION statement, refer to the SQL Language Reference.

DECLARE HANDLER Statement A DECLARE HANDLER statement provides handlers for exception or completion conditions in a compound statement. DECLARE EXIT HANDLER FOR endData BEGIN SET TotalCapacity = tempCapacity; END; For more information about the syntax of the DECLARE HANDLER statement, refer to the SQL Language Reference.

Database Design Guide

177

Storing Logic

SIGNAL Statement A SIGNAL statement explicitly sets the SQLSTATE system variable to a specified value. The following example signals the value of the SQLSTATE variable associated with the endData declared condition. IF (counter = NumRooms) THEN SIGNAL endData; END IF; For more information about the syntax of the SIGNAL statement, refer to the SQL Language Reference.

RESIGNAL Statement You can use the RESIGNAL statement in a handler if you want to leave the SQLSTATE variable set to the value it had when the handler was invoked, and you do not know what that value was. Any successful statement sets SQLSTATE back to the success value. The purpose of the RESIGNAL statement is to preserve an original SQLSTATE value after an attempt to handle the condition has changed the SQLSTATE value, either by failing or succeeding. DECLARE EXIT HANDLER FOR endData BEGIN SET TotalCapacity = tempCapacity; RESIGNAL; END; A RESIGNAL statement can occur only in a handler action or in a procedure invoked by a handler. For more information about the syntax of the RESIGNAL statement, refer to the SQL Language Reference. Database Design Guide

178

Storing Logic

SQL Triggers Triggers are actions defined on a table that you can use to enforce consistency rules for a database. They are dictionary objects that identify the appropriate action for the DBMS to perform when a user executes a SQL data modification statement on that table. To declare a trigger, use the CREATE TRIGGER statement. CREATE TRIGGER CheckCourseLimit; The maximum size for a trigger name is 30 characters. To delete a trigger, use the DROP TRIGGER statement. DROP TRIGGER CheckCourseLimit; You cannot invoke a trigger directly; they are invoked as a consequence of an INSERT, UPDATE, or DELETE action on a table with an associated trigger. For more information about the syntax of these statements, refer to the SQL Language Reference. Note

In order to prevent circumvention of triggers, Scalable SQL stamps the data file containing a trigger as a bound data file; this restricts access to Btrieve users and prevents the Btrieve user from performing an action that would fire the trigger in a Scalable SQL database. For more information, refer to "Understanding Database Rights."

Database Design Guide

179

Storing Logic

Timing and Ordering of Triggers Since triggers execute automatically for a given event, it is important to be able to specify when and in what order the trigger or triggers should execute. You specify time and order when you create the trigger.

Specifying the Triggered Action Time When an event that is associated with a trigger occurs, the trigger must execute either before the event or after the event. For example, if an INSERT statement invokes a trigger, the trigger must execute either before the INSERT statement executes or after the INSERT statement executes. CREATE TRIGGER CheckCourseLimit BEFORE INSERT ON Enrolls You must specify either BEFORE or AFTER as the triggered action time. The triggered action executes once for each row. If you specify BEFORE, the trigger executes before the row operation; if you specify AFTER, the trigger executes after the row operation. Note

Scalable SQL does not invoke a trigger by enforcing an RI constraint. Also, a table may not have a DELETE trigger defined if an RI constraint may also cause the system to perform cascaded deletes on that table.

Database Design Guide

180

Storing Logic

Specifying Trigger Order You may have situations in which an event invokes more than one trigger for the same specified time. For example, an INSERT statement may invoke two or more triggers that are defined to execute after the INSERT statement executes. Since these triggers cannot execute simultaneously, you must specify an order of execution for them. Since the following CREATE TRIGGER statement specifies an order of 1, any subsequent BEFORE INSERT triggers that you define for the table must have a unique order number greater than 1. CREATE TRIGGER CheckCourseLimit BEFORE INSERT ON Enrolls ORDER 1 You designate the order value with an unsigned integer, which must be unique for that table, time, and event. If you specify a duplicate order for the same time and event as other triggers defined for that table, Scalable SQL returns Status Code 365. If you anticipate inserting new triggers within the current order, leave gaps in the numbering to accommodate this. If you do not designate an order for a trigger, then the trigger is created with a unique order value that is higher than that of any trigger currently defined for that table, time, and event.

Database Design Guide

181

Storing Logic

Defining the Trigger Action The trigger action executes once for each row. The syntax for the trigger action is as follows: CREATE TRIGGER CheckCourseLimit BEFORE INSERT ON Enrolls ORDER 1 REFERENCING NEW AS N FOR EACH ROW BEGIN CALL CheckMax (N.Class_ID); END; If the triggered action contains a WHEN clause, then the triggered SQL statement executes if the Boolean expression is true. If the expression is not true, then the triggered SQL statement does not execute. If no WHEN clause is present, then the triggered SQL statement executes unconditionally. The triggered SQL statement can be either a single SQL statement, including a stored procedure call (CALL procedure_name), or a compound statement (BEGIN...END). Note

The triggered action must not change the subject table of the trigger; if you attempt to declare a trigger that contains an action on the subject table, Scalable SQL returns Status Code 903.

When you need to reference a column of the old row image (in the case of DELETE or UPDATE) or a column of the new row image (in the case of INSERT or UPDATE) in the

Database Design Guide

182

Storing Logic

triggered action, you must add a REFERENCING clause to the trigger declaration, as follows: REFERENCING NEW AS N The REFERENCING clause allows you to maintain information about the data that the trigger modifies.

Database Design Guide

183

Storing Logic

chapter

7

Managing Data

This chapter discusses the following topics:

‹ ‹ ‹ ‹

Defining relationships among tables Administering database security Controlling concurrency Atomicity in Scalable SQL databases

In most cases, you can use SQL statements to perform these database management tasks. You can also enter the SQL statements using an interactive application. For more information about using interactive applications, refer to the Pervasive.SQL User’s Guide. This chapter includes the following sections:

‹ ‹ ‹ ‹ ‹ ‹ ‹

Defining Relationships Among Tables Keys Referential Constraints RI in the University Database Administering Database Security Concurrency Controls Atomicity in Scalable SQL Databases

Database Design Guide

184

Managing Data

Defining Relationships Among Tables You can use referential integrity (RI) with Scalable SQL to define how each table is related to other tables in the database. RI assures that when a column (or group of columns) in one table refers to a column (or group of columns) in another table, changes to those columns are synchronized. RI provides a set of rules that define the relationships between tables. These rules are known as referential constraints. (Referential constraints are also informally referred to as relationships.) When you define referential constraints for tables in a database, the MicroKernel Database Engine enforces the constraints across all applications that access those tables. This frees the applications from checking table references independently each time an application changes a table. You must name your database in order to use RI. Once you have defined referential constraints, each affected data file contains the database name. When someone attempts to update a file, the MicroKernel uses the database name to locate the data dictionary containing the applicable RI definitions and checks the update against those RI constraints. This prevents both Scalable SQL and Btrieve applications from compromising RI, since the MicroKernel blocks updates that do not meet referential constraints. To define referential constraints on the tables in a database, use CREATE TABLE and ALTER TABLE statements. Refer to the SQL Language Reference for the syntax of these statements.

Database Design Guide

185

Managing Data

Referential Integrity Definitions The following definitions are useful in understanding referential integrity.

‹ ‹ ‹

A parent table is a table that contains a primary key referenced by a foreign key. A parent row is a row in a parent table whose primary key value matches a foreign key value. A delete-connected table occurs if your deletion of rows in one table causes the deletion of rows in a second table. The following conditions determine whether tables are delete-connected: Š A self-referencing table is delete-connected to itself. Š Dependent tables are always delete-connected to their parents, regardless of the delete rule. Š A table is delete-connected to its grandparents when the delete rules between the parent and grandparents is CASCADE.

‹ A dependent table is a table that contains one or more foreign keys. Each of

these foreign keys can reference a primary key in either the same or a different table. A dependent table can contain multiple foreign keys. Every foreign key value in a dependent table must have a matching primary key value in the associated parent table. In other words, if a foreign key contains a particular value, the primary key of one of the rows in the foreign key’s parent table must also contain that value. Attempting to insert a row into a dependent table fails if the parent table for each referential constraint does not have a matching primary key value for the foreign key value in the dependent table row being inserted. Attempting to delete a row in a parent table to which foreign keys currently refer either fails or causes the dependent rows to be deleted as well, depending on how you have defined the referential constraints. Database Design Guide

186

Managing Data

‹ ‹ ‹ ‹ ‹ ‹ ‹

A dependent row is a row in a dependent table; its foreign key value depends on a matching primary key value in the associated parent row. An orphan row is a row in a dependent table that has a foreign key value that does not exist in the index corresponding to the parent table’s primary key. The dependent key value does not have a corresponding parent key value. A reference is a foreign key that refers to a primary key. A reference path is a particular set of references between dependent and parent tables. A descendant is a dependent table on a reference path. It may be one or more references removed from the path’s original parent table. A self-referencing table is a table that is its own parent table; the table contains a foreign key that references its primary key. A cycle is a reference path in which the parent table is its own descendant.

Database Design Guide

187

Managing Data

Keys To use RI, you must define keys. There are two types of keys: primary and foreign. A primary key is a column or group of columns whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows. A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. Foreign keys reference primary keys in a parent table. It is this relationship of a column in one table to a column in another table that provides the MicroKernel with its ability to enforce referential constraints.

Primary Keys A good primary key has these characteristics:

‹ ‹

‹ ‹

It is mandatory; it must store non-null values. It is unique. For example, the ID column in a Student or Faculty table is a good key because it uniquely identifies each individual. It is less practical to use a person’s name because more than one person might have the same name. Also, databases do not detect variations in names as duplicates (for example, Andy for Andrew or Jen for Jennifer). It is stable. The ID of a student is a good key not only because it uniquely identifies each individual, but it is also unlikely to change, while a person’s name might change. It is short; it has few characters. Smaller columns occupy less storage space, database searches are faster, and entries are less prone to mistakes. For example, an ID column of 9 digits is easier to access than a name column of 30 characters.

Database Design Guide

188

Managing Data

Creating Primary Keys You create a referential constraint by creating a foreign key on a table. However, before creating the foreign key, you must create a primary key on the parent table to which the foreign key refers. A table can have only one primary key. You can create a primary key using either of the following:

‹ ‹

A PRIMARY KEY clause in a CREATE TABLE statement. An ADD PRIMARY KEY clause in an ALTER TABLE statement.

The following example creates the primary key ID on the Person table in the sample database: ALTER TABLE Person ADD PRIMARY KEY (ID); When creating a primary key, remember that Scalable SQL implements the primary key on the table using a unique, non-null, non-modifiable index. If one does not exist for the specified columns, then Scalable SQL adds a non-named index with these attributes containing the columns specified in the primary key definition.

Dropping Primary Keys You can delete a primary key only after you have dropped all foreign keys that depend on it. To drop a primary key from a table, use a DROP PRIMARY KEY clause in an ALTER TABLE statement. Since a table can have only one primary key, you do not have to specify the column name when you drop the primary key, as the following example illustrates: ALTER TABLE Person DROP PRIMARY KEY; Database Design Guide

189

Managing Data

Changing Primary Keys To change a table’s primary key, follow these steps: 1. Drop the existing primary key using a DROP PRIMARY KEY clause in an ALTER TABLE statement. Note

Doing so does not remove the column, or the index used by the primary key; it only removes the primary key definition. To remove the primary key, there must be no foreign key referencing the primary key. 2. Create a new primary key using an ADD PRIMARY KEY clause in an ALTER TABLE statement.

Foreign Keys A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. When you create a foreign key, you are creating a referential constraint, or a data link, between a dependent table and its parent table. This referential constraint can include rules for deleting or updating dependent rows in the parent table. The foreign key name is optional. If you do not specify a foreign key name, Scalable SQL tries to create a foreign key using the name of the first column in the foreign key definition. For more information about naming conventions for foreign keys and other database elements, refer to "Naming Conventions." Because Scalable SQL keywords are reserved words, you cannot use them in naming database elements. For a list of the Scalable SQL keywords, refer to the SQL Language Reference. Database Design Guide

190

Managing Data

Creating Foreign Keys in Existing Tables To create a foreign key in an existing table, follow these steps: 1. Ensure that a primary key exists in the parent table you are referencing. All columns in the primary and foreign key must be of the same data type and length, and the set of columns must be in the same order in both definitions. 2. Scalable SQL creates a non-null index for the column or group of columns specified in the foreign key definition. If the table definition already has such an index, Scalable SQL uses that index; otherwise, Scalable SQL creates a nonnamed index with the non-null, non-unique, and modifiable index attributes. 3. Create the foreign key using an ADD FOREIGN KEY clause in an ALTER TABLE statement. For example, the following statement creates a foreign key called Faculty_Dept on the column Dept_Name in the Faculty table of the sample database. The foreign key references the primary key created in the Department table and specifies the delete restrict rule. ALTER TABLE Faculty ADD FOREIGN KEY Faculty_Dept (Dept_Name) REFERENCES Department ON DELETE RESTRICT;

Creating Foreign Keys When Creating a Table To create a foreign key when creating the table, follow these steps: 1. Ensure that a primary key exists in the parent table you are referencing. All columns in the primary and foreign key must be of the same data type and length, and the set of columns must be in the same order in both definitions.

Database Design Guide

191

Managing Data

2. Scalable SQL creates a non-null index for the column or group of columns specified in the foreign key definition. If the table definition already has such an index, Scalable SQL uses that index; otherwise, Scalable SQL creates a nonnamed index with the non-null, non-unique, and modifiable index attributes. 3. Create the table using a CREATE TABLE statement and include a FOREIGN KEY clause. For example, the following statement creates a foreign key called Course_in_Dept on the column Dept_Name in a table called Course. CREATE TABLE Course USING 'course.mkd' (FOREIGN KEY Course_in_Dept (Dept_Name) REFERENCES Department ON DELETE RESTRICT, Name CHAR(7) CASE, Description CHAR(50) CASE, Credit_Hours UNSIGNED(2), Dept_Name CHAR(20) CASE) WITH INDEX (Name UNIQUE CASE, Dept_Name CASE MOD);

Dropping Foreign Keys To delete a foreign key from a table, use a DROP FOREIGN KEY clause in an ALTER TABLE statement. You must specify the foreign key name since a table can have more than one foreign key. ALTER TABLE Course DROP FOREIGN KEY Course_in_Dept;

Database Design Guide

192

Managing Data

Referential Constraints Databases on which you define referential constraints must meet the following requirements:

‹ ‹ ‹

The database must have a database name. The database must reside on a single workstation drive or a single mapped network drive. The data files must be in 6.x or later MicroKernel format. For information about converting 5.x or later data files to 6.x or 7.x format, refer to the Pervasive.SQL User’s Guide.

In order for a database to support referential integrity it must also support the concept of foreign keys. A foreign key is a column or set of columns in one table (called the dependent table) that is used to reference a primary key in another table (called the parent table). The RI rule requires all foreign keys’ values to reference valid primary key values. For example, a student cannot enroll in a nonexistent course. You can use a CREATE TABLE or ALTER TABLE statement to define keys on a table in a named database. The following sections explain how to create and modify keys. These sections also provide examples of referential constraints. After you define referential constraints on a database, applications that do not perform data updates according to referential rules may fail. For example, if an application tries to insert a row into a dependent table before inserting the corresponding parent row into the parent table, the insertion fails. Refer to the section “Referential Integrity Rules” for more information.

Database Design Guide

193

Managing Data

Note

If a file has referential constraints defined, it is a bound data file. If a user tries to access it with Btrieve, then the Btrieve user can access the file, but can only perform actions within RI constraints. For more information about bound data files, refer to the section "Understanding Database Rights."

Referential Integrity Rules Certain rules apply to inserting and updating rows in dependent tables and updating and deleting rows in parent tables when you define referential constraints on database tables. Scalable SQL supports the restrict and cascade rules as follows:

‹ ‹ ‹ ‹

Insert into dependent table — The parent table for each foreign key definition must have a corresponding primary key value for the foreign key value being inserted. If any parent table does not have a corresponding value, then the Insert operation fails. Update in the dependent table — The parent table for each foreign key definition must have a corresponding primary key value for the foreign key value (the new value for the foreign key). If any parent table does not have a corresponding value, then the Update operation fails. Update in the parent table — This is not allowed. You cannot update primary key values. To perform a similar operation, delete the row you want to update, then insert the same row with the new primary key value. Delete in the parent table — You can specify either the cascade or restrict rule for this operation. Cascade means that if a dependent table contains a foreign key value that matches the primary key value being deleted, then all rows containing that matching value are deleted from the dependent table also. Restrict means that if a dependent table contains a foreign key value that

Database Design Guide

194

Managing Data

matches the primary key value being deleted, then the Delete operation on the parent table fails. The cascade operation is recursive; if the dependent table has a primary key that is the parent table of a cascade foreign key, then the process is repeated for that set of data.

Insert Rule The insert rule is a restrict rule. For each foreign key in the row being inserted, the foreign key value must be equivalent to a primary key value in the parent table. The parent table must contain a parent row for the foreign key in the row you are inserting; otherwise, the insertion fails. Scalable SQL causes the MicroKernel to automatically enforce the insert rule on dependent tables. Figure 7-1 shows an attempted insert into the Course table. Since the parent table, Department, does not contain the Government Department, Scalable SQL does not insert the row in the Course table.

Database Design Guide

195

Managing Data

Figure 7-1

Unsuccessful Insert Operation

Department Table Primary Key: Department.Name Name

Phone_Number Building_Name

French German History

5125558909 5125558569 5125558120

Bartold Building Boerner Building McAnally Building

Room_Number Head_of_Department 307 206 104

123771790 126281925 128802059

Foreign Key Reference Course Table Foreign Key: Course.Dept_Name (references Department.Name) Name

Description

GER204 GER305 GER406 GOV101

German IV Studies in German Literature Advanced German Literature Introduction to Government

Credit Hours

Dept_Name

3 3 3 3

German German German Government

INSERT operation fails

Update Rule The update rule is restrict. A foreign key value must be updated to an equivalent primary key value in the parent table. If the parent table does not contain a parent row for the foreign key value, the update fails. You can explicitly specify the update rule as restrict when you define a foreign key on a table; however, Scalable SQL causes the MicroKernel to enforce the rule by default if you do not specify it.

Database Design Guide

196

Managing Data

Delete Rule You can explicitly specify the delete rule as either restrict or cascade when you define a foreign key. If you do not specify the delete rule explicitly, Scalable SQL assumes a default of restrict for the delete rule.

‹

‹

If you specify restrict as the delete rule, Scalable SQL causes the MicroKernel to check each row you attempt to delete from a parent table to see if that row is a parent row for a foreign key in another table. If it is a parent row, Scalable SQL returns a status code and does not delete the row. You must first delete all corresponding rows in the referenced table or tables before you can delete the parent row. If you specify cascade as the delete rule, Scalable SQL causes the MicroKernel to check each row you attempt to delete from a parent table to see if that row is a parent row for a foreign key in another table. The MicroKernel then checks the delete rule for each descendant of that table. If any descendant has restrict as the delete rule, the attempted deletion fails. If all descendants have cascade as the delete rule, Scalable SQL deletes all dependent rows on the reference path to the original parent table.

The following guidelines govern the delete rule for foreign keys:

‹ ‹ ‹ ‹ ‹

The delete rule for a self-referencing table must not be restrict. A cycle with two or more tables cannot be delete-connected to itself. Consequently, the delete rule for at least two of the dependent tables in the cycle must not be cascade. The last delete rule in all paths from one table to another must be the same. If the delete rule for the foreign key is cascade, then the table containing the foreign key may not have a delete trigger defined on it. If the table containing the foreign key has a delete trigger defined on it, then the delete rule must be restrict.

Database Design Guide

197

Managing Data

Scalable SQL enforces these guidelines on databases that have referential constraints defined. If you attempt to declare delete rules that violate these guidelines, Scalable SQL returns a status code to indicate an error occurred. Following are some common status codes you might receive: Status Code

Description

882

The delete rule for self-referencing tables must not be restrict.

883

The delete rule for at least two of the foreign key connections in the cycle must not be cascade.

884

The delete rules for multiple paths to the same parent table must match.

885

Scalable SQL allows a maximum of 16 adjacent delete cascade rules in a reference path.

Figure 7-2 represents an attempt to delete the row that contains the primary key value Young Building, 200 from the Room table. However, this row is a parent row for one of the rows in the Department table. (One of the rows in Department has a foreign key value of Young Building, 200.) The delete rule defined for the foreign key is restrict; therefore, Scalable SQL does not delete the row.

Database Design Guide

198

Managing Data

Figure 7-2

Unsuccessful Delete Operation

Room Table Primary Key: Room.Building_Name, Room.Room_Number Building_Name

Room_Number

Capacity

Type

Currah Building Faske Building Young Building

318 101 200

1 1 1

Office Office Office

Delete Operation Fails

Foreign Key Reference Department Table Foreign Key: Department.Building_Name, Department.Room_Number Name

Phone_Number Building_Name

5125558345 Communication Computer Science 5125558123 5125558823 Economics

Currah Building Faske Building Young Building

Room_Number Head_of_Department 318 101 200

113711250 116221385 118741520

Scalable SQL enforces the delete rule guidelines to avoid certain anomalies that might otherwise occur when you delete dependent rows from tables. Following are examples of anomalies that might occur without these guidelines.

Anomaly on Self-Referencing Tables The delete rule for a self-referencing table must be cascade. As Figure 7-3 and the accompanying example show, the results of delete operations can be inconsistent without this rule.

Database Design Guide

199

Managing Data

Figure 7-3 shows a subset of a table named Faculty_Head.

Figure 7-3

Anomaly on Self-Referencing Tables Faculty_Head Table Primary Key: Faculty_Head.ID Foreign Key: Faculty_Head.Head_of_Dept ID

Dept_Name

Head_of_Dept

180086510 176188111 125861123

English English Computer Science

180086510 180086510 175053812

Restrict

The following statement deletes all faculty in the English Department. DELETE FROM Faculty_Head WHERE Dept_Name = 'English'; This statement succeeds if Scalable SQL deletes the rows in the following order: 1. The row in which the primary key value equals 176188111. 2. The row in which the primary key value equals 180086510. However, if Scalable SQL attempts to delete the rows in any other order, the delete operation fails. To avoid such inconsistencies, specify the delete rule for Head_of_Dept as cascade. This guideline ensures that all rows that subsequently reference the initially deleted row are also deleted. When deleting rows from a self-referencing table, be sure that your statement does not inadvertently delete all or most of the rows in the table. Database Design Guide

200

Managing Data

Anomaly on Delete-Connected Cycles A cycle with two or more tables cannot be delete-connected to itself. Consequently, the delete rule for at least two of the dependent tables in the cycle must be restrict. Figure 7-4 shows an anomaly that might occur without this guideline.

Figure 7-4

Anomaly on Delete-Connected Cycle Faculty Table ID

Department Table Name

Dept_Name

Head_of_Department

Mathematics 181831941 Sociology 310082269

181831941 Mathematics 179321806 Mathematics 310082269 Sociology

Restrict

Cascade

Assume you want to execute the following statement. DELETE FROM Faculty Because of the relationships between the Faculty and Department tables, deleting a row from Faculty first deletes a row from Faculty, then from Department, where the cascaded delete stops because of the restrict rule on the name of the department. The results could be inconsistent, depending on the order in which Scalable SQL deletes rows from the Faculty table. If it attempts to delete the row in which the ID is 181831941, the delete operation fails. The restrict rule on the Department name prevents Scalable SQL from deleting the first row in the department table in which the primary key value Database Design Guide

201

Managing Data

equals Mathematics, since the second row in Faculty continues to reference this row’s primary key. If instead, Scalable SQL deletes the Faculty rows in which the primary keys equal 179321805 and 310082269 first (in either order), all the rows in Faculty and Department are deleted. Figure 7-5 shows how you can avoid this anomaly by specifying the two delete rules as restrict.

Figure 7-5

Prevention of a Delete-Connected Cycle Faculty Table ID

Department Table Name

Dept_Name

Head_of_Department

Mathematics 181831941 Sociology 310082269

181831941 Mathematics 179321806 Mathematics 310082269 Sociology

Restrict

Restrict

Since the result of the example DELETE statement is consistent, no rows are deleted.

Anomaly on Multiple Paths Delete rules from multiple delete-connected paths must be the same. Figure 7-6 shows an example of one anomaly that might occur without this guideline. In the figure, the arrows point to the dependent tables. Database Design Guide

202

Managing Data

Figure 7-6

Multiple Paths Anomaly Room Restrict

Faculty

Cascade

Cascade

Department

Faculty is delete-connected to Room through multiple delete-connected paths with different delete rules. Assume you want to execute the following statement. DELETE FROM Room WHERE Building_Name = 'Bhargava Building' AND Number = 302; The success of the operation depends on the order in which Scalable SQL accesses Faculty and Department to enforce their delete rules.

‹ ‹

If it accesses Faculty first, the delete operation fails because the delete rule for the relationship between Room and Faculty is restrict. If it accesses Department first, the delete operation succeeds, cascading to both Department and Faculty.

To avoid problems, Scalable SQL insures that the delete rules for both paths that lead to Faculty are the same.

Database Design Guide

203

Managing Data

RI in the University Database This section demonstrates the table and referential constraint definitions on the sample database.

Creating the Course Table The following statement creates the Course table. CREATE TABLE Course USING 'course.mkd' (Name CHAR(7) CASE, Description CHAR(50) CASE, Credit_Hours UNSIGNED(2), Dept_Name CHAR(20)) WITH INDEX (Name UNIQUE CASE, Dept_Name CASE MOD);

Adding a Primary Key to Course The following statement adds a primary key (Name) to the Course table. ALTER TABLE Course ADD PRIMARY KEY (Name);

Database Design Guide

204

Managing Data

Creating the Student Table with Referential Constraints The following statement creates the Student table and defines its referential constraints. CREATE TABLE Student USING 'Student.mkd' (PRIMARY KEY (ID), FOREIGN KEY S_Tuition (Tuition_ID) REFERENCES Tuition, FOREIGN KEY S_Major (Major) REFERENCES Department, FOREIGN KEY S_Minor (Minor) REFERENCES Department, ID UNSIGNED(8), Cumulative_GPA NUMERICSTS(5,3), Tuition_ID INTEGER(4), Transfer_Credits NUMERICSA(4,0), Major CHAR(20) CASE, Minor CHAR(20) CASE, Scholarship_Amount DECIMAL(10,2), Cumulative_Hours INTEGER(2)) WITH INDEX (ID UNIQUE, Tuition_ID); Figure 7-7 shows the foreign key references on the Student, Tuition, and Department tables and includes sample rows from each table.

Database Design Guide

205

Managing Data

Figure 7-7

Foreign Key References

Student Table

ID

Foreign Key: Tuition_ID (references Tuition.ID) Foreign Key: Major (references Department.Name) Foreign Key: Minor (references Department.Name) Cumulative_GPA

4.000 158974022 3.633 219333635 Foreign Key Reference

Tuition_ID

Transfer_Credits Major

Minor

8 8

0 0

Chemistry Theatre

Accounting Accounting

Foreign Key Reference Foreign Key Reference Department Table Name Tuition Table Primary Key: ID

Phone_Number

Accounting 5126941000 Chemistry 5126941500

Primary Key: Name Building_Name

Room_Number

Bhargava Building Currah Building

302 318

ID

Degree

Residency

Cost_Per_Credit

Comments

3 8

BBA NONE

True False

1.250000E+02 2.500000E+02

1 Bachelor of Business... # no degree program ...

Database Design Guide

206

Managing Data

Administering Database Security The Scalable SQL security option allows you to protect your data by limiting operations on some data columns to particular users. These limits may range from allowing a user to see only certain columns in a table, to allowing them to see all the columns in a table, but not update them. Scalable SQL makes no assumptions about database authorization based on the operating system’s file and directory rights. By default, all users accessing a database through Scalable SQL have complete read-write access to the data. You must enable and define database security to limit this access and protect the database from unauthorized update or access through Scalable SQL. Scalable SQL security statements allow you to perform the following actions to limit access to your database:

‹ ‹ ‹ ‹ ‹ ‹ ‹

Enable security for the database. Identify users and groups of users and assign passwords to them. Grant rights to users and user groups. Revoke rights from users and user groups. Disable security for the database. Specify data file owner names. Retrieve information about security defined for a database.

Database Design Guide

207

Managing Data

Understanding Database Rights Table 7-1 shows the rights you can grant to users and user groups.

Table 7-1

Database Rights

Right

Description

Login

Allows a user to log in to a database. You assign this right when you create a user and a password. The Login right does not give users access to data, however. You must assign other rights to users before they can access data. You cannot assign the Login right to a user group.

Create Table Enables a user to create new table definitions. The user automatically has full access rights to the tables he or she creates at the time of creation, but the Master User can later revoke read, write, and alter rights for the table. The Create Table right is also referred to as a global right, because it applies to the entire data dictionary. Select

Allows a user to query tables for information. You can grant the Select right for specific columns or for a whole table.

Update

Gives a user the right to update information in specified columns or tables. You can grant the Update right for specific columns or for a whole table.

Insert

Allows a user to add new rows to tables. You can grant the Insert right only at the table level.

Delete

Allows a user to delete information from tables. You can grant the Delete right only at the table level.

Alter

Allows a user to change the definition of a table. You can grant the Alter right only at the table level.

References

Allows a user to create foreign key references that refer to a table. The References right is necessary for defining referential constraints.

All

Includes Select, Update, Insert, Delete, Alter, and References rights.

Database Design Guide

208

Managing Data

Many rights automatically imply other rights. When you grant or revoke certain rights, you implicitly grant or revoke additional rights. Figure 7-8 shows the relationships among rights.

Figure 7-8

Relationships Among Rights

References

Alter

Update

Insert

Login Delete

Select All

Table 7-2 lists the implied rights granted for each Scalable SQL right you grant.

Table 7-2

Granting Rights

Explicitly Granting This Right

Implicitly Grants These Rights

Select

None

Create Table

None

References

Select, Insert, Update, Delete, and Alter

Login

None

Database Design Guide

209

Managing Data

Table 7-2

Granting Rights continued

Explicitly Granting This Right

Implicitly Grants These Rights

Update (columns)

Select

Update (tables)

Select, Insert, Delete

Insert

Select, Update, Delete

Delete

Select, Update, Insert

Alter

Select, Update, Insert, Delete

All

Select, Update, Insert, Delete, Alter, References

Table 7-3 lists the implied rights revoked for each Scalable SQL right you revoke.

Table 7-3

Revoking Rights

Explicitly Revoking This Right

Implicitly Revokes These Rights

Select

Update, Insert, Delete, Alter, References

Select (columns)

Insert, Delete, Update (columns), Alter

References

None

Login

None

Update (columns)

None

Update (tables)

Insert, Delete, Alter, References

Insert

Update, Delete, Alter, References

Delete

Update, Insert, Alter, References

Alter

References

All

Select, Update, Insert, Delete, Alter, References

Database Design Guide

210

Managing Data

When you grant or revoke one of the following table rights for a user, Scalable SQL also grants and revokes the other two rights at the table level, as well as the user’s Alter rights. This does not apply to column-specific Update rights.

‹ ‹ ‹

Delete Insert Update

You can assign certain types of rights over the whole database or for a particular database element. For example, when you assign the Update right to a user or user group, you can limit it to certain tables or to certain columns in tables. In contrast, when you assign the Create Table right to a user or user group, that user or user group has the Create Table right for the entire database. You cannot apply the Create Table right to a single table or column. While the Create Table and Login rights apply to the entire database, all other rights apply to tables. In addition, you can apply Select and Update rights to individual columns in tables.

Establishing Database Security The following steps describe the general procedure for establishing security for a database. 1. Log in to the database for which you want to establish security. For more information about logging in to a database, refer to the Pervasive.SQL User’s Guide for your platform. 2. Enable security for the database by creating the master user and specifying the master password with the SET SECURITY statement.

Database Design Guide

211

Managing Data

After you have enabled security, the name of the master user is Master (casesensitive), and the password you specified when you enabled security becomes the master password (also case-sensitive). For more information, refer to "Enabling Security." 3. Log out of the database and log back in as the master user. 4. Optional: Define a minimal set of rights for the PUBLIC group. All users automatically belong to the PUBLIC group. For more information, refer to "Granting Rights to the PUBLIC Group." 5. Optional: Create user groups with the CREATE GROUP statement. You can create as many groups as you need for your system. However, a user can belong to only one group other than PUBLIC. For more information, refer to "Creating User Groups." 6. Optional: Grant rights to each user group with the GRANT CREATETAB and GRANT (access rights) statements. For more information, refer to "Granting Rights to User Groups." 7. Grant login privileges to users by specifying the users’ names and passwords with the GRANT LOGIN statement, and if you choose, assign each user to a user group. For more information, refer to "Creating Users." 8. Grant rights to the users you have created who are not members of a user group using the GRANT CREATETAB and GRANT (access rights) statements. For more information, refer to "Granting Rights to Users." 9. Optional: To protect your files from unauthorized Btrieve access, assign file owner names when you create tables using the CREATE TABLE statement.

Database Design Guide

212

Managing Data

If you assign owner names, users may execute the SET OWNER statement to allow access to the data files during a login session. Another way to protect your files from Btrieve access is to make the database a bound database. For more information about bound databases, refer to "Understanding Database Rights."

Enabling Security You can use a SET SECURITY statement to enable security. In response, Scalable SQL creates the master user, who has complete read-write access to the database. The password you specify with a SET SECURITY statement becomes the master password for the database. The following example enables security for a database and specifies the password for the master user as Secure: SET SECURITY = Secure; Passwords are case-sensitive. When you enable security, Scalable SQL creates the system tables X$User and X$Rights. Enabling security excludes all users except the master user from accessing the database until you explicitly create other users and grant them login rights.

Creating User Groups and Users After you enable security, your database has one user (Master) and one user group (PUBLIC). To provide other users access to the database, log in to the database as the master user and create users by name and password. You can also organize the users in user groups. User names are case-sensitive in Scalable SQL. Therefore, when you log in as the master user, you must specify the user name as Master. Database Design Guide

213

Managing Data

Creating User Groups To simplify security administration, you can organize users in user groups. You can create as many user groups as you need for your system. A user, however, can belong to only one group in addition to PUBLIC. Once the user is in an additional group, the user inherits the rights of that group, and you cannot grant individual rights to that user. The rights of a user in a group cannot differ from the rights defined for the entire group. To give a user unique rights, create a special group just for that user. To create a user group, use a CREATE GROUP statement. CREATE GROUP Accounting; You can also create multiple user groups at once. CREATE GROUP Accounting, Registrar, Payroll; User group names are case-sensitive, cannot exceed 30 characters, and must be unique to the database. For more information about rules for naming user groups, refer to the SQL Language Reference.

Creating Users When you create a user for a database, Scalable SQL enters the corresponding user name and password into the database’s security tables. To create a user, use a GRANT LOGIN TO statement. The following example creates the user Cathy and assigns Passwd as her password. GRANT LOGIN TO Cathy : Passwd; Note

Scalable SQL stores passwords in encrypted form. Therefore, you cannot query the X$User table to view user passwords.

Database Design Guide

214

Managing Data

You can also assign a user to a user group when you create the user. For example, to assign the user Cathy to the Accounting group, use the following statement: GRANT LOGIN TO Cathy : Passwd IN GROUP Accounting; User names and passwords are case-sensitive. User names cannot exceed 30 characters and must be unique to the database. Passwords cannot exceed 8 characters, and passwords do not have to be unique to the database. For more information about rules for user names and passwords, refer to the SQL Language Reference.

Granting Rights This section explains how to grant rights to user groups and individual users.

Granting Rights to the PUBLIC Group All users automatically belong to the PUBLIC group, a special user group used to define the minimum set of rights for all users of a particular database. No user can have fewer rights than those assigned to the PUBLIC group. You cannot drop a user from the PUBLIC group, and you cannot revoke rights from a user if those rights are granted to the PUBLIC group. By default, the PUBLIC group has no rights. To change the rights of the PUBLIC group, use a GRANT (access rights) statement. For example, the following statement allows all users of the sample database to query the Department, Course, and Class tables in the database: GRANT SELECT ON Department, Course, Class TO PUBLIC;

Database Design Guide

215

Managing Data

After granting rights to the PUBLIC group, you can create other groups to define higher levels of access. You can also give individual users additional rights that differ from any other user or group, provided the user is not part of a group.

Granting Rights to User Groups You can assign rights to a user group and add user names and passwords to the group. Doing so eliminates assigning each user’s rights individually. Also, security is easier to maintain if you assign security rights to groups, since you can change the rights of many users by granting new rights or revoking existing rights for an entire group at once. To grant rights to a user group, use a GRANT (access rights) statement. For example, the following statement allows all users in the Accounting group to alter the Billing table definition in the sample database. GRANT ALTER ON Billing TO Accounting; Note

Remember that granting the Alter right implicitly grants the rights Select, Update, Insert, and Delete.

Granting Rights to Users After you create a user, that user can log in to the database. However, the user cannot access data until you either place the user in a user group with rights or grant rights to the user. To grant rights to a user, use a GRANT (access rights) statement. The following example allows the user John to insert rows into the billing table in the sample database. GRANT INSERT ON Billing TO John; Database Design Guide

216

Managing Data

Note

Granting the Insert right implicitly grants the rights Select, Update, and Delete.

Dropping Users and User Groups To drop (delete) a user, use a REVOKE LOGIN statement. REVOKE LOGIN FROM Bill; This statement removes the user Bill from the data dictionary. After you drop a user, the user cannot access any tables in the database unless you disable security for the database. You can also drop multiple users at once, as in the following example. REVOKE LOGIN FROM Bill, Cathy, Susan; To drop a user group, follow these steps: 1. Drop all users from the group, as in the following example: REVOKE LOGIN FROM Cathy, John, Susan; 2. Use a DROP GROUP statement to drop the group. The following example drops the Accounting group: DROP GROUP Accounting;

Database Design Guide

217

Managing Data

Revoking Rights Revoking certain rights automatically revokes other rights. For example, if a user has the Insert right to a table and you revoke that right, you automatically revoke the user’s Delete, Update, Alter, and References rights to that table. For more information about revoking rights, refer to Table 7-3. To revoke a user’s rights, use the REVOKE statement. The following example revokes the user Ron’s Select, Update, Insert, Delete, and Alter rights from the Billing table of the sample database. REVOKE SELECT ON Billing FROM Ron;

Disabling Security To disable security for a database, follow these steps: 1. Log in to the database as the master user. 2. Issue a SET SECURITY statement, specifying the NULL keyword, as follows: SET SECURITY = NULL; When you disable security for a database, Scalable SQL removes the X$User and X$Rights system tables from the database and deletes the associated .DDF files. Note

You cannot disable security simply by deleting the USER.DDF and RIGHTS.DDF data dictionary files. If you delete these and try to access the database, Scalable SQL returns an error and denies access to the database.

Database Design Guide

218

Managing Data

Specifying Data File Owner Names One way to protect the data files associated with your tables from unauthorized access by Btrieve applications is to specify data file owner names when you create the tables. (Another way is to use a bound database; for more information about bound databases, refer to "Understanding Database Rights.") A file owner name works like a password: a user must provide the owner name before the MicroKernel allows that user to access the corresponding data file. You can assign a file owner name to a table when you create the table using a CREATE TABLE statement. CREATE TABLE Tuition USING 'Tuition.mkd' OWNER 'Doris' (ID AUTOINC(4), Degree LSTRING(5) CASE, Residency LOGICAL(1), Cost_Per_Credit FLOAT(4), Comments LVAR(200)) WITH INDEX (ID UNIQUE, Degree CASE MOD, Residency MOD); Note

Owner names are case-sensitive. Therefore, when you specify the owner name as Doris and a user sets the owner name as DORIS (using a SET OWNER statement), Scalable SQL denies the user access to the file.

You can also specify owner access restriction levels (0 through 3) on a file that has an owner name. These levels allow read access to users who do not set the owner name, and they allow you to specify data encryption. For more information, refer to the SQL Language Reference.

Database Design Guide

219

Managing Data

Once you specify an owner name for a file, users must use a SET OWNER statement to set the owner name at the beginning of each Scalable SQL session. If a user wants to access multiple data files that have different owner names, the user can set multiple owner names upon starting the Scalable SQL session. For example, the following statement sets three owner names: SET OWNER = Sally, Thomas, Doris;

Retrieving Information about Database Security When you set up database security, Scalable SQL creates the system tables X$User and X$Rights. Because the system tables are part of the database, you can query them if you have the appropriate rights. For a complete reference to the contents of each system table, refer to the SQL Language Reference.

Database Design Guide

220

Managing Data

Concurrency Controls The MicroKernel and its automatic recovery functions handle the physical integrity of your database. Scalable SQL provides logical data integrity using the transaction and recordlocking capabilities of the MicroKernel. Scalable SQL, in conjunction with the MicroKernel, provides the following types of concurrency controls:

‹ ‹ ‹

Isolation levels for transactions Record locks Passive control

Transaction Processing When you attempt to insert data into a table, Scalable SQL returns an error if the data is invalid. However, if you are using the xInsert, xUpdate, or xRemove APIs, any data inserted before the error occurred remains in the database. For example, if you insert three rows into a table and the third row is invalid, the first two rows remain in the table. To avoid this type of inconsistency, you can use transaction processing. Note

This inconsistency does not occur when using INSERT, UPDATE, and DELETE SQL statements.

Transaction processing lets you identify a set of logically related database modifications, either within a single table or across multiple tables, and require them to be completed as a unit. Transaction processing involves two important concepts:

‹

A logical unit of work, or transaction, is a set of discrete operations that must be treated as a single operation to ensure database integrity. If you make a mistake

Database Design Guide

221

Managing Data

or encounter a problem during a transaction, you can issue a ROLLBACK WORK statement to undo the changes you have already made. For example, the Registrar might credit a student account with an amount paid in one operation, then update the amount owed in a second operation. By grouping these operations together you ensure the student’s finances are accurate.

‹

A locking unit is the amount of data from which other tasks are blocked until your transaction is complete. (A task is a Scalable SQL session.) Locking prevents other tasks from changing the data you are trying to change. If other tasks can also change the data, Scalable SQL cannot roll back work to a previously consistent state. Thus, within a transaction, only one task may access a given locking unit at a time. However, multiple cursors that belong to the same task can access the locking unit at the same time.

On the SQL level, the START TRANSACTION statement begins a transaction. When you have issued all the statements you want to complete during the transaction, issue a COMMIT WORK statement to end the transaction. The COMMIT WORK statement saves all your changes, making them permanent. If an error occurs in one of the operations, you can roll back the transaction and then retry it again after correcting the error. For example, if you need to make related updates to several tables, but one of the updates is unsuccessful, you can roll back the updates you have already made so the data is not inconsistent. Scalable SQL automatically performs a rollback operation if a task issues an XQLLogout or XQLStop call at any time before the current transaction completes. Scalable SQL also performs the rollback operation if two tasks are sharing a login session and the task that originated the session logs out before the second task completes its transition.

Database Design Guide

222

Managing Data

Starting and Ending Transactions To begin a transaction, issue a START TRANSACTION statement. After issuing all the statements you want to complete during the transaction, issue a COMMIT WORK statement to save all your changes and end the transaction. START TRANSACTION; UPDATE Billing B SET Amount_Owed = Amount_Owed - Amount_Paid WHERE Student_ID EXISTS (SELECT E.Student_ID FROM Enrolls E WHERE E.Student_ID = B.Student_ID); COMMIT WORK; For more information about the START TRANSACTION statement, refer to the SQL Language Reference.

Using Savepoints to Nest Transactions In a SQL transaction, you can define additional markers called savepoints. Using savepoints, you can undo changes after a savepoint in a transaction and continue with additional changes before requesting the final commit or abort of the entire transaction. To begin a transaction, use the START TRANSACTION statement. The transaction remains active until you issue a ROLLBACK or COMMIT WORK statement. To establish a savepoint, use the SAVEPOINT statement. SAVEPOINT SP1;

Database Design Guide

223

Managing Data

To rollback to a savepoint, use the ROLLBACK TO SAVEPOINT statement. ROLLBACK TO SAVEPOINT SP1; The savepoint name must specify a currently active savepoint in the current SQL transaction. Any changes made after establishing this savepoint are cancelled. To delete a savepoint, use the RELEASE SAVEPOINT statement. RELEASE SAVEPOINT SP1; You can only use this statement if a SQL transaction is active. If you issue a COMMIT WORK statement, all savepoints defined by the current SQL transaction are destroyed, and your transaction is committed. Note

Do not confuse ROLLBACK TO SAVEPOINT with ROLLBACK WORK. The former cancels work only to the indicated savepoint, while the latter cancels the entire outermost transaction and all savepoints established within it.

Savepoints provide a way to nest your transactions, thereby allowing the application to preserve the previous work in the transaction while it waits for a sequence of statements to complete successfully. As an example, you can use a WHILE loop for this purpose. You can set a savepoint before beginning a sequence of statements that may fail on the first attempt. Before your transaction can proceed, this sub-transaction must complete successfully. If it fails, the sub-transaction rolls back to the savepoint, where it can start again. When the sub-transaction succeeds, the rest of the transaction can continue. A SQL transaction must be active when you issue a SAVEPOINT statement.

Database Design Guide

224

Managing Data

Note

The MicroKernel allows each transaction a total of 255 internal nesting levels. However, Scalable SQL uses some of these levels internally to enforce atomicity on INSERT, UPDATE, and DELETE statements. Therefore, a session can effectively define no more than 253 savepoints to be active at one time. This limit may be further reduced by triggers that contain additional INSERT, UPDATE, or DELETE statements. If your operation reaches this limit, you must reduce the number of savepoints or the number of atomic statements contained within it.

Work that is rolled back within a savepoint cannot be committed even if the outer transaction(s) completes successfully. However, work that is completed within a savepoint must be committed by the outermost transaction before it is physically committed to the database. For example, in the university database you might start a transaction to register a student for several classes. You may successfully enroll the student in the first two classes, but this may fail on the third class because it is full or it conflicts with another class for which the student has enrolled. Even though you failed to enroll the student in this class, you don’t want to undo the student’s enrollment for the previous two classes. The following stored procedure enrolls a student into a class by first establishing a savepoint, SP1, then inserting a record into the Enrolls table. It then determines the current enrollment for the class and compares this to the maximum size for the class. If the comparison fails, it rolls back to SP1; if it succeeds, it releases savepoint SP1. CREATE PROCEDURE Enroll_Student (student UNSIGNED(8), classNum INT(4)); BEGIN DECLARE currentEnrollment INT(4); DECLARE maxEnrollment INT(4); SAVEPOINT SP1; Database Design Guide

225

Managing Data

INSERT INTO Enrolls VALUES (student, classNum); SET currentEnrollment = (SELECT COUNT (*) FROM Enrolls WHERE class_id = classNum); SET maxEnrollment = (SELECT Max_Size FROM Class WHERE ID = classNum); IF (currentEnrollment >= maxEnrollment) THEN ROLLBACK TO SAVEPOINT SP1; ELSE RELEASE SAVEPOINT SP1; END IF; END A calling program or procedure must issue a START TRANSACTION before invoking this procedure, because savepoints are only permitted within a transaction. By the same token, the caller may issue a COMMIT WORK statement, regardless of the outcome of this procedure, because no class is allowed to exceed its enrollment limit. For more information about the syntax of any of these statements, refer to the entries for these statements in the SQL Language Reference.

Database Design Guide

226

Managing Data

Special Considerations Transactions do not affect the following operations:

‹ ‹ ‹

Operations that create or change dictionary definitions. Therefore, you cannot roll back the results of the following statements: ALTER TABLE, CREATE DICTIONARY, CREATE GROUP, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, and CREATE VIEW. Operations that remove dictionary definitions. Therefore, you cannot roll back the results of the following statements: DROP DICTIONARY, DROP GROUP, DROP INDEX, DROP PROCEDURE, DROP TABLE, DROP TRIGGER, and DROP VIEW. Operations that grant or revoke security rights. Therefore, you cannot roll back the results of the following statements: CREATE GROUP, DROP GROUP, GRANT (access rights), GRANT CREATETAB, GRANT LOGIN, REVOKE (access rights), REVOKE CREATETAB, and REVOKE LOGIN.

If you attempt any of these operations within a transaction and Scalable SQL completes the statement, then you cannot roll back the results. You cannot alter or drop a table (in other words, change its dictionary definition) during a transaction if you have previously referred to that table during the transaction. For example, if you start a transaction, insert a record into the Student table, and then try to alter the Student table, the ALTER statement fails. You must commit the work from this transaction, and then alter the table.

Database Design Guide

227

Managing Data

Isolation Levels An isolation level determines the scope of a transaction locking unit by allowing you to define the extent to which a transaction is isolated from other users, who may also be in a transaction. When you use isolation levels, Scalable SQL automatically locks pages or tables according to the isolation level you specify. These automatic locks, which Scalable SQL controls internally, are called implicit locks, or transaction locks. Locks that an application specifies explicitly are called explicit locks, formerly record locks. For more information, refer to "Explicit Locks." Scalable SQL offers two isolation levels for your transactions:

‹ ‹

Exclusive (locks the entire data file you are accessing). Cursor stability (locks either the row or page you are accessing).

The system’s default isolation level is set when you load Scalable SQL. You can use the default isolation level or specify an isolation level for the current session using a SET ISOLATION statement. SET ISOLATION = < EX | CS > The value EX specifies the exclusive isolation level; the value CS specifies the cursor stability isolation level. The session’s isolation level determines the isolation level of the next transaction the session starts; if you change the isolation level within a transaction, the change does not take effect until the next transaction begins. For information about specifying a default isolation level when you load Scalable SQL, see the Pervasive.SQL User’s Guide. For information about setting the isolation level after startup, refer to the discussions of the SET ISOLATION statement in the SQL Language Reference.

Database Design Guide

228

Managing Data

Exclusive Isolation Level When you use the exclusive isolation level, the locking unit is an entire data file. Once you access a file or files within an exclusive transaction, those files are locked from any similar access by any other user in a transaction. This type of locking is most effective when few applications attempt to access the same tables at the same time, or when large parts of the file must be locked in the course of a transaction. Scalable SQL releases the lock on the file or files when you end the transaction. When you access a table during an exclusive transaction, the following conditions take effect:

‹ ‹ ‹

Other tasks that are in a transaction cannot read, update, delete, or insert rows in that table until you end the transaction. Other tasks that are not in a transaction can read rows in the table, but they cannot update, delete, or insert rows. Multiple cursors within the same task can read any row in the table. However, when you perform an update, delete, or insert operation with a particular cursor, Scalable SQL locks the entire data file for that cursor.

When you access tables through a joined view using the exclusive isolation level, Scalable SQL locks all the accessed data files in the view.

Cursor Stability Isolation Level The MicroKernel maintains data files as a set of data pages and index pages. When you use the cursor stability isolation level, the locking unit is a data page or index page instead of a data file. When you read records within a cursor stability transaction, Scalable SQL locks the data pages that contain those records for possible update, but allows concurrent access to a table by multiple tasks within transactions. These read locks are

Database Design Guide

229

Managing Data

released only when you read another set of records. Scalable SQL supports set level cursor stability since it allows an application to fetch multiple records at a time. In addition, any data modifications you make to the data or index pages cause those records to remain locked for the duration of the transaction, even if you issue subsequent reads. Other users in a transaction cannot access these locked records until you commit or roll back your work. However, other applications can lock other pages from the same files within their own transactions. When you access a file during a cursor stability transaction, Scalable SQL locks data and index pages as follows:

‹ ‹ ‹

You read a row, but you do not update it or delete it. Scalable SQL locks the data page for that row until your next read operation or until you end the transaction. You update a non-index column in a row, delete a row from a table that does not contain indexes, or insert a new row into a table that does not contain indexes. Scalable SQL locks the data page for that row for the remainder of the transaction, regardless of subsequent read operations. You update an indexed column in a row, delete a row from a table that contains indexes, or insert a new row into a table that contains indexes. Scalable SQL locks the affected index page(s), as well as the data page, for the remainder of the transaction, regardless of subsequent read operations.

Cursor stability ensures that the data you read remains stable, while still allowing other users access to other data pages within the same data files. Within the cursor stability isolation level, you can generally achieve greater concurrency for all tasks by limiting the number of rows you read at one time, thereby locking fewer data pages at a time. This allows other network users access to more pages of the data file, since you do not have them locked.

Database Design Guide

230

Managing Data

However, if your application is scanning or updating large numbers of rows, you increase the possibility of completely locking other users out of the affected tables. Therefore, it is best to use cursor stability for reading, writing, and committing small transactions. Cursor stability does not lock records within a subquery. Cursor stability does not guarantee that the conditions under which a row is returned do not change, only that the actual row returned does not change.

Transactions and Isolation Levels Whenever you access data within a transaction, Scalable SQL locks the accessed pages or files for that application. No other application can write to the locked data pages or files until the locks are released. Using the cursor stability isolation level, when you access tables through a joined view, Scalable SQL locks all the accessed pages for all the tables in the view. Using the exclusive isolation level, when you access tables through a joined view, Scalable SQL locks all the accessed tables in the view. Scalable SQL performs no-wait transactions. If you try to access a record that another task has locked within a transaction, Scalable SQL informs you that the page or table is locked or that a deadlock has been detected. In either case, roll back your transaction and begin again. Scalable SQL allows multiple cursors in the same application to access the same data file.

Database Design Guide

231

Managing Data

The following steps illustrate how two applications interact while accessing the same tables within a transaction. The steps are numbered to indicate the order in which they occur. Task 1

Task 2

1. Activate the view. 2. Activate the view. 3. Begin a transaction. 4. Begin a transaction. 5. Fetch records. 6. Attempt to fetch records from the same data files. 7. Receive Status Code 84 (Record or Page Locked) if both tasks are using cursor stability and Task 2 attempts to fetch the same records that Task 1 has already locked, or receive 85 (File Locked) if one of the tasks is using an exclusive transaction. 8. Retry the fetch if needed. 9. Update the records. 10. End the transaction. 11. The fetch is successful. 12. Update the records. 13. End the transaction.

Since a transaction temporarily locks records, pages, or tables against other applications’ updates, an application should not pause for operator input during a transaction. This is because no other application can update the records, pages, or tables accessed in the transaction until the operator responds and the transaction is terminated.

Database Design Guide

232

Managing Data

Note

Reading records within a cursor stability transaction does not guarantee that a subsequent update succeeds without conflict. This is because another application may have already locked the index page that Scalable SQL needs to complete the update.

Avoiding Deadlock Conditions A deadlock condition occurs when two applications are retrying operations on tables, data pages, index pages, or records that the other one has already locked. To minimize the occurrence of deadlock situations, have your application commit its transactions frequently. Do not attempt to retry the operation from your application; Scalable SQL attempts a reasonable number of retries before returning an error.

Deadlock Conditions under Exclusive Isolation Level When you use the exclusive isolation level, Scalable SQL locks the entire data file against updates by other applications; thus, it is possible for a deadlock to occur if your applications do not access data files in the same order, as shown in the following table. Task 1

Task 2

1. Begin a transaction. 2. Begin a transaction. 3. Fetch from File 1. 4. Fetch from File 2. 5. Fetch from File 2. 6. Receive lock status.

Database Design Guide

233

Managing Data

7. Retry Step 5. 8. Fetch from File 1. 9. Receive lock status. 10. Retry Step 8.

Deadlock Conditions under Cursor Stability Isolation Level When you use the cursor stability isolation level, other applications can read and update records or pages in the file you are accessing (records or pages that your application has not locked).

Explicit Locks Whenever your application fetches data through Scalable SQL using the Scalable SQL APIs, it can specify that it wants to lock the physical records that contain the data using explicit record locks. These locks are called explicit locks because the task is responsible for setting the locks. Explicit locks lock the rows for update and delete operations but do not allow you to roll back the operations. You can only use explicit locks outside of a transaction. After an application locks a record, other applications can read the same records, but they cannot lock the records or update them. Only the application holding the lock can update or remove the record. An application can lock multiple records in a file at the same time. A single application cannot issue both implicit locks (transaction locks) and explicit locks (record locks). However, multiple applications may use different types of locks, even when accessing the same file (provided the entire file is not locked.)

Database Design Guide

234

Managing Data

For example, Application 1, under the cursor stability isolation level, accesses File 1 to read a record. Application 2, without starting a transaction, accesses File 1, requesting an explicit lock on a record. As long as they are not trying to lock the same record, both applications can obtain the data they need. (However, if Application 1 were running under the exclusive isolation level, Application 2 would not be able to access File 1 since Application 1 would already have the entire file locked.) By using explicit locks with the XQLFetch or xFetch APIs, you can lock the physical records that contain the data you are fetching instead of using transactions; however, you do not have the commit or rollback capabilities that a transaction provides if you use this method. Within the same session, multiple cursors can lock the same rows on a read operation. However, only one cursor can update or delete the locked rows. For example, you might use one cursor to read rows and a second cursor to update rows (the second cursor must also read the row before it can perform the update operation.) The first cursor maintains the position within the view because an update operation on a cursor requires the task to re-establish position if the operation is unsuccessful. Scalable SQL provides two types of explicit record locks: wait and no-wait. Regardless of which one you use, Scalable SQL locks all the records it returns on a fetch.You specify the type of lock on the XQLFetch or xFetch call when fetching the data. On read operations, the locks have the following effects on other tasks (or other cursors of the same task) that try to access the same files:

‹ ‹

If the second task is in a transaction, it cannot read the record. By default, Scalable SQL locks a record on a read operation if a task is in a transaction. A second task that is also in a transaction cannot read and lock a record that is already locked by the first task. However, the second task can lock other records on the same page.

Database Design Guide

235

Managing Data

‹ ‹

If the second task is not in a transaction (and is not using XQLFetch or xFetch with explicit locks), it can read the record. In this case, Scalable SQL reads the record without trying to lock it. If a single task uses multiple cursors to access the same files, other cursors can lock the same record that the first cursor locked.

On Update, Delete, and Insert operations, the locks have the following effects on other tasks (or other cursors of the same task) that try to access the same files:

‹ ‹

‹ ‹

If the first task locks a data page, other tasks (or other cursors of the same task) cannot update or delete any of the records on the locked data page. Also, these tasks (or cursors) cannot insert a record on the locked data page. The tasks that are not in transactions (and are not using XQLFetch or xFetch with explicit record locks) can still read records on the locked data page, as can multiple cursors of the same task. Other tasks that are in transactions cannot read the records. However, any task, whether it is in a transaction or not, can lock other data pages in the same files, as can multiple cursors of the same task. If the first task also locks an index page, other tasks (or other cursors of the same task) cannot perform an update, delete, or insert operation on any record on any data page if the operation involves an index and the index field value of that record is listed on the locked index page. When you access files through a joined view using the cursor stability isolation level, Scalable SQL locks the appropriate records, data pages, or index pages for all the files in the view.

Database Design Guide

236

Managing Data

Wait Locks When you fetch records with a wait lock, Scalable SQL does not return control to your application until it has obtained the lock on every record you requested. If another application has locked one of the records you requested, Scalable SQL waits indefinitely until that application releases the record before proceeding to lock the rest of the records you requested. Note

The Scalable SQL for Windows local engine does not support wait locks. The MicroKernel maps wait locks to no-wait locks.

No-Wait Locks When you fetch records with a no-wait lock, Scalable SQL returns control to your application if it cannot lock the record for any reason (for example, because another application has already locked that record). Scalable SQL immediately returns a non-zero status to your application and unlocks any records it has already locked during the fetch request. A read with no-wait locks may return one of the following results:

‹ ‹ ‹

Record in use (Status Code 84) File in use (Status Code 85) Deadlock (Status Code 78)

Outside a transaction, you can retry if you receive Status Code 84 or Status Code 85. If you receive Status Code 78, you must release all locked resources. Inside a transaction, you must rollback when you receive any lock error, in order to reduce the contention for

Database Design Guide

237

Managing Data

resources. Refer to the Status Codes and Messages manual for details about these status conditions.

Releasing Locks You can release record locks in two ways: implicitly and explicitly. If you choose the implicit release, Scalable SQL automatically unlocks all locked records when you perform any of the following operations:

‹ ‹ ‹ ‹

Update or delete the locked records. Fetch another set of records. Release the cursor ID associated with the locked records. Log out.

If you choose the explicit release, Scalable SQL unlocks records only if you perform one of these operations:

‹ ‹ ‹

Issue an explicit unlock operation. Release the cursor ID associated with the locked records. Log out.

Database Design Guide

238

Managing Data

Passive Control If your application performs single record fetch and update sequences that are not logically connected, you can use Scalable SQL’s passive method of concurrency. Using this method, you can fetch and update (or delete) records without performing transactions or record locks. These operations are referred to as optimistic updates and deletes. By default, if your task does not use transactions or explicit record locks to complete update and delete operations, your task cannot overwrite another task’s changes. The feature that ensures this data integrity is passive control, sometimes referred to as optimistic concurrency control. With passive control, your task does not perform any type of locking. If another task modifies a record after you originally fetched it, you must fetch the record again before you can perform an update or delete operation. Under passive control, if another application updates or deletes a record between the time you fetch it and the time you issue an update or remove operation, your application receives a conflict status. This indicates that another application has modified the data since you originally fetched it. When you receive a conflict status, you must fetch the record again before you can perform the update or remove operation. Passive control allows an application that was designed for a single-user system to run on a network without implementing lock calls. However, passive control is effective only when an application is operating in a lightly used network environment or on files in which the data is fairly static. In a heavily used environment or on files that contain volatile data, passive control may be ineffective.

Database Design Guide

239

Managing Data

Atomicity in Scalable SQL Databases The principle of atomicity states that if a given statement does not execute to completion, then it should not leave partial or ambiguous effects in the database. For example, if a statement fails after it has inserted three out of five records but does not undo that insert, then the database is not in a consistent state when you retry the operation. If the statement is atomic and it fails to complete execution, then all changes are rolled back, so that the database is in a consistent state. In this example, if all five records are not successfully inserted, then none of them are inserted. The atomicity rule is especially significant for statements that modify multiple records and/or tables. It also makes retrying failed operations simpler, because any previous attempt is guaranteed not to have left any partial effects. Scalable SQL enforces atomicity in two ways: 1. Any UPDATE, INSERT, or DELETE statement is defined to be atomic. Scalable SQL guarantees that, if a multi-record and/or multi-table modification operation fails, none of the effects of that modification remain in the database. This is true for Update, Insert, or Delete operations whether or not they are performed inside or outside of procedures. 2. You may specify stored procedures as ATOMIC when you create them. Such procedures apply the rule of atomicity to their entire execution. Therefore, not only do UPDATE, INSERT, or DELETE statements within an ATOMIC procedure execute atomically, but if any other statements within that procedure fail, all effects of the procedure’s execution thus far are rolled back.

Database Design Guide

240

Managing Data

Transaction Control in Procedures Because triggers are always initiated by an external data change statement (INSERT, DELETE, or UPDATE), and all data change statements are defined to be atomic, the following statement are not allowed in triggers or in any procedures invoked by triggers:

‹ ‹ ‹

START TRANSACTION COMMIT WORK ROLLBACK WORK (including RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT)

In other words, triggers follow the same rules as ATOMIC compound statements. No user-initiated COMMIT WORK, ROLLBACK WORK, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT statement can cause a system-begun transaction (for purposes of atomicity) to end. If you attempt to use these statements in a trigger, Scalable SQL returns Status Code 501.

Database Design Guide

241

Managing Data

appendix

A

University Database Tables and Referential Integrity

The Pervasive University sample database is provided as part of the Scalable SQL product and is frequently used in the documentation to illustrate database concepts and techniques. Even if you are already familiar with Pervasive Software’s products, you may want to review the information in this appendix in order to become acquainted with the new sample database. Even though you may not be working in an academic environment, you can use the sample database examples both as a template and a reference to help you design and develop your own customized information systems. You can use the sample queries and other aspects included in our example, since it reflects a real-life scenario. This appendix includes the following sections:

‹ ‹ ‹

Structure of the University Database Referential Integrity in the University Database Table Design

Database Design Guide

242

University Database Tables and Referential Integrity

Structure of the University Database The physical structure of the database consists of the elements of a relational database: tables, columns, rows, keys, and indexes. The database contains 10 tables with various relationships between them. It contains data on students, faculty, classes, registration, etc.

Assumptions Following are some assumptions around which the database was built:

‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹

The scope of the database is one semester. A student cannot take the same course more than once. For example, a student cannot enroll in Algebra I, Sections 1 and 2. A faculty member can be a student, but a faculty member cannot teach and enroll in the same class. Any course is offered by only one department. In order for a student to receive a grade, they must be enrolled in a class, and a faculty member must be assigned to teach the class. Faculty members belong to a single department, but they can teach for many departments. All students have a Student ID that is based on the US standard of a social security number. All faculty members have a Faculty ID that is based on the US standard of a social security number.

Database Design Guide

243

University Database Tables and Referential Integrity

‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹ ‹

All other persons have a Person ID that is based on the US standard of a social security number. Rooms are unique within the same building. Two classes cannot be taught in the same room at the same time. A faculty member can only be teaching one class at a given time. Prerequisites are not required for enrollment in a class. Departments imply majors. A course can only be taught by one faculty member throughout the semester. A telephone number or zip code does not correlate to a state. A registrar cannot be a faculty member or student. When a person is entered into the database, they can complete a survey of which they must answer all the questions or none of the questions. Credit hours for a course are not necessarily equal to the number of hours that a class convenes. An e-mail address does not have to be unique.

Entity Relationships Entities are objects that describe primary components in the database. When designing a database, it is important to define the entities and their relationships to one another before proceeding further. In the university database, CLASSES, STUDENTS, FACULTY, GRADES, etc., are entities. The entities and their relationships to one another are outlined in Figure A-1.

Database Design Guide

244

University Database Tables and Referential Integrity

Figure A-1 Entity Relationships is head of is a

PERSON

FACULTY belongs to

has many registrars from

GRADE

is a

has an office

teaches

enrolls for

CLASS

is taught in

ROOM

STUDENT

has sections for

COURSE

offers

pays fees to

BILLING

has an office

DEPARTMENT

majors in

TUITION

minors in

has a

LEGEND many to many ENTITY

WEAK ENTITY

relationship

one to many one to one

This diagram does not show any attributes.

GRADES is a weak entity. It is dependent upon a student taking a class, so its existence is dependent upon the validity of other entities. The STUDENT and FACULTY tables create common information, since a student could be a faculty member and vice versa. The common information is in the PERSON table.

Database Design Guide

245

University Database Tables and Referential Integrity

Referential Integrity in the University Database This section describes the referential integrity (RI) design in the university database. Figure A-2 depicts the referential constraints that exist among the various tables in the university database. The boxes represent tables. The unidirectional-directional arrow implies a referential constraint from the parent table to the referencing table. For example, in constraint number 16 a foreign key exists in the Class table that references a primary key in its parent table, Course.

Figure A-2 RI Structure in the University Database

BILLING

TUITION

13

1

STUDENT

2

PERSON

10

17

14 11

8 3

ENROLLS

4

DEPARTMENT 9

FACULTY

12

COURSE

6 5 16

15

ROOM 7

CLASS

Note

Figure A-2 also serves as a dependency graph. It tells you what tables must be populated before others when doing the physical design. Database Design Guide

246

University Database Tables and Referential Integrity

The tables, columns, and keys participating in RI are defined as follows:

Table A-1

Tables and Columns Involved with RI

Constraint Referencing Table

Foreign Key

Referenced Table Primary Key

1

BILLING

Registrar_ID

PERSON

ID

2

STUDENT

ID

PERSON

ID

3

FACULTY

ID

PERSON

ID

4

STUDENT

Tuition_ID

TUITION

ID

5

FACULTY

Building_Name, Room_Number

ROOM

Building_Name, Number

6

DEPARTMENT

Building_Name, Room_Number

ROOM

Building_Name, Number

7

CLASS

Building_Name, Room_Number

ROOM

Building_Name, Number

8

FACULTY

Dept_Name

DEPARTMENT

Name

9

DEPARTMENT

Head_Of_Dept

FACULTY

ID

10

STUDENT

Major

DEPARTMENT

Name

11

STUDENT

Minor

DEPARTMENT

Name

12

COURSE

Dept_Name

DEPARTMENT

Name

13

BILLING

Student_ID

STUDENT

ID

14

ENROLLS

Student_ID

STUDENT

ID

15

CLASS

Faculty_ID

FACULTY

ID

16

CLASS

Course_Name

COURSE

Name

17

ENROLLS

Class_ID

CLASS

ID

Database Design Guide

247

University Database Tables and Referential Integrity

Table Design Following is a guide to the tables in the university database. This information is included with each table:

‹ ‹ ‹ ‹ ‹

Columns in the table Data types for each column Size, or length, of the column in bytes Keys (blank if the column is not a key) Indexes (blank if the column does not have an index)

BILLING Table Column

Data Type

Size

Keys

Student_ID

UNSIGNED

8

PRIMARY, FOREIGN

Transaction_Number

UNSIGNED

2

PRIMARY

Log

TIMESTAMP

8

Amount_Owed

MONEY

6

Amount_Paid

MONEY

6

Registrar_ID

NUMERIC

8

Comments

NOTE

200

Database Design Guide

248

FOREIGN

University Database Tables and Referential Integrity

CLASS Table Column

Data Type

Size

Keys

ID

AUTOINC

4

PRIMARY

Name

CHARACTER

7

FOREIGN

Section

CHARACTER

3

Max_Size

INTEGER

2

Start_Date

DATE

4

Start_Time

TIME

4

Finish_Time

TIME

4

Building_Name

CHARACTER

25

FOREIGN

Room_Number

UNSIGNED

4

FOREIGN

Faculty_ID

UNSIGNED

8

FOREIGN

Column

Data Type

Size

Keys

Name

CHARACTER

7

PRIMARY

Description

CHARACTER

50

Credit_Hours

UNSIGNED

2

Dept_Name

CHARACTER

20

COURSE Table

Database Design Guide

249

FOREIGN

University Database Tables and Referential Integrity

DEPARTMENT Table Column

Data Type

Size

Keys

Name

CHARACTER

20

PRIMARY

Phone_Number

NUMERIC

10.0

Building_Name

CHARACTER

25

FOREIGN

Room_Number

UNSIGNED

4

FOREIGN

Head_of_Dept

UNSIGNED

8

FOREIGN

Column

Data Type

Size

Keys

Student_ID

UNSIGNED

8

PRIMARY, FOREIGN

Class_ID

INTEGER

4

PRIMARY, FOREIGN

Grade

FLOAT

4

ENROLLS Table

Database Design Guide

250

University Database Tables and Referential Integrity

FACULTY Table Column

Data Type

Size

Keys

ID

UNSIGNED

8

PRIMARY, FOREIGN

Dept_Name

CHARACTER

20

FOREIGN

Designation

CHARACTER

10

Salary

CURRENCY

8

Building_Name

CHARACTER

25

FOREIGN

Room_Number

UNSIGNED

4

FOREIGN

Rsch_Grant_Money

BFLOAT

8

Column

Data Type

Size

Keys

ID

UNSIGNED

8

PRIMARY

First_Name

ZSTRING

16

Last_Name

ZSTRING

26

Perm_Street

ZSTRING

31

Perm_City

ZSTRING

31

Perm_State

ZSTRING

3

Perm_Zip

ZSTRING

11

Perm_Country

ZSTRING

21

PERSON Table

Database Design Guide

251

University Database Tables and Referential Integrity

Column

Data Type

Size

Street

ZSTRING

31

City

ZSTRING

31

State

ZSTRING

3

Zip

ZSTRING

11

Phone

NUMERIC

10.0

Emergency_Phone

CHARACTER

20

Unlisted

BIT

1

Date_Of_Birth

DATE

4

Email_Address

ZSTRING

31

Sex

LOGICAL

1

Citizenship

ZSTRING

21

Survey

BIT

1

Smoker

BIT

1

Married

BIT

1

Children

BIT

1

Disability

BIT

1

Scholarship

BIT

1

Comments

NOTE

200

Database Design Guide

252

Keys

University Database Tables and Referential Integrity

ROOM Table Column

Data Type

Size

Keys

Building_Name

CHARACTER

25

PRIMARY

Number

UNSIGNED

4

PRIMARY

Capacity

INTEGER

2

Type

CHARACTER

20

Column

Data Type

Size

Keys

ID

UNSIGNED

8

PRIMARY, FOREIGN

Cumulative_GPA

NUMERICSTS

5.3

Tuition_ID

INTEGER

4

Transfer_Credits

NUMERICSA

4

Major

CHARACTER

20

FOREIGN

Minor

CHARACTER

20

FOREIGN

Scholarship_Money

DECIMAL

10.2

Cumulative_Hours

INTEGER

2

STUDENT Table

Database Design Guide

253

FOREIGN

University Database Tables and Referential Integrity

TUITION Table Column

Data Type

Size

Keys

ID

AUTOINC

4

PRIMARY

Degree

LSTRING

5

Residency

LOGICAL

1

Cost_Per_Credit

FLOAT

4

Comments

LVAR

200

Database Design Guide

254

University Database Tables and Referential Integrity

Index A

with DROP keyword 48 with DROP PRIMARY KEY clause 190 with USING clause 52 AND boolean operator 104 Anomalies on delete-connected cycles 201 on multiple paths 202 on self-referencing tables 199 Appending string columns, example of 124 Arithmetic operators in expressions 117 Ascending sort order in indexes 41 ATOMIC keyword, with compound statement 171 Atomicity overview 240 transaction processing and 46 Attributes, index 41 AVG function 126

Access rights. See Security; Rights ADD FOREIGN KEY clause 191 ADD keyword, with ALTER TABLE statement 52 ADD PRIMARY KEY clause 189 Administering database security Aggregate functions. See Group aggregate functions Alias names for tables 34 All right about 208 granting 210 revoking 210 Allocating disk storage space 36 Alter right description 208 granting 210 revoking 210 ALTER TABLE statement defining referential constraints 185 dropping columns with 48 with ADD FOREIGN KEY clause 191 with ADD keyword 52 with ADD PRIMARY KEY clause 189 with DROP FOREIGN KEY clause 192 Database Design Guide

B BEGIN...END statement 171 BEGINS WITH operator 106 BETWEEN operator 105 Boolean expressions in triggers 182 masks 77 255

Index

operators, in restriction clauses 104 Bound databases 24

qualified names of 27 ranges, specifying 54 removing 48 selecting 90 value lists, specifying 55 COMMIT WORK statement 222 Completion conditions 176 Compound statements 171 Computed columns conditional operators for 120 in joins 94 Concatenating strings 119, 124 Concurrency controls 221 data file locks 229 explicit locks 234 passive control 239 transaction processing 221 Condition operators 105 Conditional execution of SQL statements 172 Conditions completion 176 declaring 177 exception 176 handling 177 resignalling 178 signalling 178 CONTAINS operator 106 Correlated subqueries 101 COUNT function 92, 126 CREATE GROUP statement 214 INDEX statement 38 PROCEDURE statement 160

C CALL (procedure) statement 160 Cartesian product joins 98 Cascaded deletes 197 Case-sensitivity column names and 36 index column values and 41 stored view names and 84 table names and 29 CAST function 148 Changing primary keys 190 rows 81 Character lists 55 Clauses, restriction 103 Columns attributes of 53 character lists, specifying 55 computed 94 creating 36 data types for 36 default values, specifying 53 dropping 48 headings in views 85 masks for 57 naming 26 naming, case-sensitivity and 36 null values in 55

Database Design Guide

256

Index

TABLE statement defining referential constraints 185 granting right to use 208 with FOREIGN KEY clause 192 with OWNER clause 219 with PRIMARY KEY clause 189 with USING clause 38 with WITH INDEX clause 37 TRIGGER statement 179 VIEW statement, with SELECT clause 84, 85 Create Table right about 208 granting 209 Creating columns 36 data dictionaries 30 databases 25 foreign keys 190 indexes 37 primary keys 189 stored procedures 160 tables 33 user groups 213 users 214 views 83 CURDATE function 121 Cursor stability isolation level 229 Cursor-based statements, SQL cursor declaration 167 FETCH statement 169 overview 166 Positioned DELETE statement 169 Database Design Guide

Positioned UPDATE statement 170 Cursors cursor-based statements 166 declaring 167 opening 168 procedure-owned 168 scroll cursors 167 session 168 CURTIME function 122 Cycle defined 187 delete-connected 201

D Data dictionaries contents 30 creating 30 deleting 50 path limit 33 querying 30 Data files as locking units 229 index segments, maximum number of 41 owner names, specifying 219 path limit 33 Data security. See Security Data types and joins 94 computations with numeric 113 for columns 36 Database element names

257

Index

about 26 duplicate 26 maximum lengths for 28 unique 26 valid characters for 27 Databases adding rows to 44 bound 24 creating 25 defining referential constraints for 193 deleting 50 deleting rows from 47 design of conceptual 16 logical 17 physical 21 named 23 normalization of 18 retrieving rows from 82, 126 rights 208 security 207 transactions in 221 updating 81 using transactions in 46 Date arithmetic in expressions 121 in scalar functions 143 Date masks 69 DAY function 143 Deadlock condition with cursor stability isolation level 234 with exclusive isolation level 233 Deadlock conditions, avoiding 233 Database Design Guide

DECLARE CONDITION statement 177 cursor statement 167 HANDLER statement 177 Default column values 53 Defining stored procedures 160 Delete right description 208 granting 210 revoking 210 Delete rule about 197 anomalies, for foreign keys 197 DELETE statement deleting rows with 47 invoking triggers with 179 Delete-connected table 186 Deleting columns 48 current row with Positioned DELETE statement 169 databases 50 foreign keys 192 indexes 48 primary keys 189 rows 47 savepoints 224 stored procedures 161 tables 49 triggers 179 users and user groups 217 Dependent rows 187 258

Index

Explicit locks 228, 234 Expressions about 111 arithmetic operators in 117 date arithmetic 121 examples of 123 integral promotion 115 numeric computations in 113 operators in 113 precision rules 115 rules for 112 scalar functions in 129 scale rules 115 syntax in 111 time arithmetic 122

tables 186 Descendants 187 Descending sort order in indexes 41 Dictionaries.See Data dictionaries Disabling security 218 Disk storage space, allocation of 36 Display masks. See Masks DROP DICTIONARY statement 50 FOREIGN KEY clause 192 INDEX statement 48 PRIMARY KEY clause 190 PROCEDURE statement 161 TABLE statement 49 TRIGGER statement 179 Dropping columns 48 indexes 48 tables 49 Duplicatability in indexes 41

F FETCH statement 169 First normal form 19 Foreign keys about 190 creating 191 defined 187, 188 deleting 192 Functions aggregate 126 arguments for 127 AVG 126 COUNT 126 MAX 126 MIN 126

E Edit masks. See Masks Enabling security 213 Ending transactions 223 Entities defined 244 weak entities 245 Equal joins 96 Exception conditions 176 Exclusive isolation level 229

Database Design Guide

259

Index

G

SUM 126 with the DISTINCT keyword 127 CURDATE 121 CURTIME 122 scalar DAY 143 HOUR 145 LEFT 130, 132 LENGTH 124, 130, 131, 133 LOWER 130, 135 LTRIM 131, 135 MILLISECOND 145 MINUTE 145 MONTH 143 overview 129 RIGHT 130, 131, 136 ROUND 131, 141 RTRIM 131, 137 SECOND 145 SUBSTRING 131, 137 TRUNC 131, 142 UPPER 131, 140 WEEKDAY 143 YEAR 143

Database Design Guide

Glossary. See Pervasive.SQL User’s Guide GRANT access rights statement 215 LOGIN statement 214 Granting rights 209 Group aggregate functions about 126 arguments for 127 GROUP BY clause, with SELECT statement 92, 128 Grouping rows 92 Groups. See User groups

H HAVING clause, with SELECT statement 128 Headings 85 Hexadecimal masks 68 HOUR function 145, 146

260

Index

I

values from other tables 45 Integral promotion in expressions 115 Integrity of updates 46, 221 IS NOT NULL operator 106 IS NULL operator 106 Isolation levels cursor stability deadlock condition 234 overview 229 exclusive deadlock condition 233 overview 229 overview 228

IF statement 172 Implicit locks 228 IN operator 105 Indexes attributes 41 creating 37 dropping 48 duplicatability in 41 join conditions and 94 maximum number of 39 modifiability 41 named 26 null values in 42 removing 48 segmented 39, 42 sorting ascending order 41 case-sensitivity and 41 descending order 41 Insert right description 208 granting 210 revoking 210 rule 195 INSERT statement invoking triggers with 179 with VALUES clause 44 Inserting rows 44

Database Design Guide

J Joins about 93 cartesian product 98 computed columns in 94 data types and 94 equal 96 indexes and 94 nonequal 96 null 97 self 99 specifying 95 using views and tables in 96

261

Index

K

granting 209 revoking 210 LOOP statement 173 Loops about 173 and the WHILE statement 174 exiting from 172 LOWER function 130, 135 LTRIM function 131, 135

Keys about 21, 188 foreign defined 188, 190 deleting 192 naming 26 primary changing 190 characteristics of 188 creating 189 defined 188 deleting 189

M Many-to-many relationship 18 Masks boolean 77 date 69 hexadecimal 68 literals 58 number data digits in 65 overview 64 special literals in 65 permanent and temporary 63 specifying 57 string length 63 temporary, for multiple columns 80 time 74 timestamp 76 Master user 213 MAX function 126 MILLISECOND function 145, 146 MIN function 126

L LEAVE statement 172 LEFT function 130, 132 LENGTH function 124, 130, 131, 133 LIKE operator 106 Locking units 222 Locks about 228 data files 229 deadlock condition 233 explicit 234 implicit 228 transaction 228 wait 237 Login right description 208 Database Design Guide

262

Index

MINUTE function 145, 146 Modifiability in indexes 41 Modifying data 51 tables 52 MONTH function 143, 144

NOT IN operator 105 NOT LIKE operator 106, 110 Null joins 97 Null values in indexes 42 setting 55 Number masks 64 Numeric computations 113 scalar functions. See Scalar functions

N Named databases about 23 removing names of 50 Naming conventions columns 26 database elements 26 group names 26 indexes 26 keys 26 stored procedures 26 tables 26 triggers 26 usernames 26 views 26 Nested queries. See Subqueries Nonequal joins 96 Normalization of databases first normal form 19 overview 18 second normal form 19 third normal form 20 NOT BETWEEN operator 106 NOT CONTAINS operator 106

Database Design Guide

O One-to-many relationship 18 One-to-one relationship 18 OPEN (cursor) statement 168 Operators arithmetic 117 BEGINS WITH 106 BETWEEN 105 boolean AND 104 OR 104 condition range 105 relational 105 CONTAINS 106 expression 113 for conditional computed columns 120 IN 105 IS NOT NULL 106 IS NULL 106

263

Index

LIKE 106 NOT BETWEEN 106 NOT CONTAINS 106 NOT IN 105 NOT LIKE 106, 110 string 119 OR boolean operator 104 ORDER BY clause, with SELECT statement 92 Orphan rows 187 Owner names for data files, specifying 219

defined 188 deleting 189 Procedure-owned cursors 168 variables 164 Procedures, stored 159 Processing transactions 46 PUBLIC group 212, 215

Q

P

Qualified column names 27 Querying, data dictionaries 30 Quotes, using with string constants 112

Page size, maximum index segments and 39 Parent rows 186 tables 186 Passive control 239 Passwords about 213 case-sensitivity and 29 storing 214 Paths, limit when stored in dictionary 33 Positioned DELETE statement 169 Positioned UPDATE statement 170 Precision rules in expressions 115 PRIMARY KEY clause 189 Primary keys changing 190 characteristics of 188 creating 189

Database Design Guide

264

R Range operators in restriction clauses 105 Ranges, specifying in columns 54 Read-only tables in views 86 Record locks about 228 wait locks 237 References defined 187 paths cycle 187 defined 187 right 208 granting 209 revoking 210 Referential constraints

Index

Retrieving data 82, 126 REVOKE access rights statement 218 LOGIN statement 217 Revoking rights 210, 217 RI. See Referential integrity; Referential constraints RIGHT function 130, 131, 136 Rights all 208 alter 208 create table 208 delete 208 granting 208, 209 insert 208 login 208 overview 208 references, about 208 revoking 210, 218 select 208 update 208 ROLLBACK with TO SAVEPOINT clause 224 WORK statement 221 Rolling back to a savepoint 224 transactions 221 ROUND function 131, 141 Rows deleting 47 dependent 187 inserting 44 orphan 187

creating foreign keys with 190 creating primary keys with 189 defining 193 deleting foreign keys 192 primary keys 189 examples of 204 overview 185 Referential integrity anomalies about 197 and delete-connected cycles 201 and multiple, delete-connected paths 202 and self-referencing tables 199 and the delete rule for foreign keys 197 definitions 186 delete rule 197 insert rule 195 overview 185 self-referencing tables and 187 update rule 196 Relational operators 105 Relationships many-to-many 18 one-to-many 18 one-to-one 18 RELEASE SAVEPOINT statement 224 RESIGNAL statement 178 Restriction clauses about 103 examples 107 operators 104 Database Design Guide

265

Index

overview 130, 132 RIGHT 136 RTRIM 124, 137 SUBSTRING 137 UPPER 140 time HOUR 146 MILLISECOND 146 MINUTE 146 overview 145 SECOND 147 Scale rules in expressions 115 Scroll cursors 167 SECOND function 145, 147 Second normal form 19 Security creating user groups and 214 creating users and 214 disabling 218 enabling 213 groups, naming 26 master user and 213 overview 207 owner names for data files 219 passwords about 213 case-sensitivity and 29 PUBLIC group and 215 rights about 208 granting 209 revoking 210 setting up 211

parent 186 sorting and grouping 92 updating 81 RTRIM function 124, 131, 137

S Sample database. See University database. SAVEPOINT statement 223 Savepoints creating 223 deleting 224 overview 223 rolling back to 224 Scalar functions CAST 148 date DAY 143 MONTH 144 overview 143 WEEKDAY 144 YEAR 145 numeric overview 140 ROUND 141 TRUNC 142 overview 129 string LEFT 132 LENGTH 133 LOWER 135 LTRIM 135

Database Design Guide

266

Index

system tables 220 users, naming 26 Segmentation in indexes 39, 42 Select right description 208 granting 209 revoking 210 SELECT statement as nested query 100 lists, specifying 90 with FROM clause 64 with GROUP BY clause 92, 128 with HAVING clause 128 with ORDER BY clause 92 with WHERE clause 95 Selecting, columns 90 Selection lists 90 Self joins 99 Self-referencing tables 187 Session cursors 168 Session variables 165 SET BINARYNULL statement 56 CHAR statement 55 DECIMALNULL statement 56 DEFAULT statement 54 ISOLATION statement 228 MASK statement 63 OWNER statement 220 RANGE statement 54 SECURITY statement about 211 with NULL keyword 218 Database Design Guide

SQL variable statement 165 STRINGNULL statement 56 Setting column attributes 53 null values 55 security 211 SIGNAL statement 178 Sorting indexes 41 rows 92 Special literals in masks 57 Specifying character lists 55 column default values 53 column ranges 54 edit masks joins 95 list of columns 90 owner names 219 trigger order 181 triggers 180 value lists 55 SQL control statements compound statements 171 IF statement 172 LEAVE statement 172 LOOP statement 173 overview 171 WHILE statement 174 SQL cursor-based statements cursor declaration 167 FETCH statement 169 overview 166 267

Index

Positioned DELETE statement 169 Positioned UPDATE statement 170 SQL variable statements assigning 165 declaring SQL variables 162 overview 162 procedure-owned variables 164 session variables 165 substitution variables 163 SQLSTATE system variable 175 START TRANSACTION statement 222 Starting transactions 223 Stored procedures as triggers 179 creating 160 defining 160 deleting 161 invoking 160 naming 26 overview 159 Stored statements. See Stored procedures Stored views 84 Storing, passwords 214 String concatenating 119, 124 functions. See Scalar functions length masks 63 operators in expressions 119 Subqueries about 100 correlated 101 limitations 101 Substitution variable statements 163 Database Design Guide

Substitution variables 163 SUBSTRING function 131, 137 SUM function 92, 126 System tables 30

T Tables aliases for, assigning 34 creating 33 defining relationships with referential integrity 185 defining views as an alternative 33 delete-connected 186 deleting 49 dependent defined 186 descendant 187 dropping 49 joining with other tables 95 with views 96 modifying definitions of 52 naming about 26 case-sensitivity and 29 parent 186 primary keys, changing 190 read-only in views 86 relationships many-to-many 18 one-to-many 18

268

Index

U

one-to-one 18 overview 18 self-referencing anomalies 199 defined 187 system 30 Temporary views 84 Third normal form 20 Time arithmetic, in expressions 122 arithmetic, in scalar functions 145 masks 74 Timestamp masks 76 Transaction locks 228 Transactions ending 223 locking units in 222 nesting, with savepoints 223 overview of 46 processing 46, 221 rolling back 221 special considerations for 227 starting 223 Triggers creating 179 defining trigger action 182 deleting 179 invoking 179 naming 26 overview 179 specifying execution time 180 specifying order of execution 181 TRUNC function 131, 142 Database Design Guide

Unique indexes 41 University database and referential integrity 204 entity relationships 244 overview 242 referential integrity 246 structure 243 table design 248 tables Billing 248 Class 249 Course 249 Department 250 Enrolls 250 Faculty 251 Person 251 Room 253 Student 253 Tuition 254 Update anomalies, avoiding 197 right description 208 granting 210 revoking 210 rule 196 UPDATE statement invoking triggers with 179 overview 81 with SET clause 85

269

Index

with WHERE clause 81, 85 Updating rows 81 UPPER function 131, 140 User groups creating 213 deleting 217 granting rights to 216 PUBLIC 215 Users creating 213 deleting 217 granting rights to 216 USING clause with ALTER TABLE statement 52 with CREATE TABLE statement 38

creating 83 features of 83 joining, with tables 96 naming 26 stored 84 temporary 84 with read-only tables 86

W Wait locks 237 Weak entities 245 WEEKDAY function 143, 144 WHEN clause, in triggers 182 WHERE clause, with SELECT statement 95 WHILE statement 174 WITH INDEX clause, with CREATE TABLE statement 37

V Value lists 55 VALUES clause, with INSERT statement 44 Variable statements. See SQL variable statements Variables SQL assigning 165 declaring 162 procedure-owned 164 session 165 substitution 163 system, SQLSTATE 175 Views column headings in 85

Database Design Guide

Y YEAR function 143, 145

270

Index

User Comments Pervasive Software would like to hear your comments and suggestions about our manuals. Please write your comments below and send them to us at:

Pervasive Software Inc. Documentation 8834 Capital of Texas Highway Austin, Texas 78759 USA

Database Design Guide 100-003195-004 February 1998

Telephone: 1-800-287-4383 Fax: 512-794-1778 Email: [email protected] Your name and title: Company: Address:

Phone number:

Fax:

You may reproduce these comment pages as needed so that others can send in comments also.

I use this manual as:

Completeness Readability (style) Organization/Format Accuracy Examples Illustrations Usefulness

‰ an overview

‰ a tutorial

‰ a reference

‰ a guide

Excellent

Good

Fair

Poor

‰ ‰ ‰ ‰ ‰ ‰ ‰

‰ ‰ ‰ ‰ ‰ ‰ ‰

‰ ‰ ‰ ‰ ‰ ‰ ‰

‰ ‰ ‰ ‰ ‰ ‰ ‰

Please explain any of your above ratings:

In what ways can this manual be improved?

You may reproduce these comment pages as needed so that others can send in comments also.