\nI Best practices for the relational model

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

",{"_324":325,"_43":44,"_47":16,"_45":46,"_64":65,"_326":327,"_395":322},"previewContent","

\n \n
- \n Foreword\n
- \n Preface\n
  - \n Why this book and why now?\n
  - \n This book is a work-in-progress\n
  - \n Who is this book for?\n
  - \n How to read this book?\n
  - \n Please help improve this book!\n
  - \n About the author\n
  \n
\n
\n I Best practices for the relational model\n
- \n 1 Introduction\n
  - \n 1.1 Why a new book about the relational database?\n
  - \n 1.2 Is the RDBMS becoming less relevant?\n
  - \n 1.3 The foundation of the RDBMS revisited\n
  \n
- \n 2 A common question of life: What makes me unique?\n
  \n
- \n 3 A foreigner is not a stranger\n
  \n
\n
\n II Implementing Data Integrity\n
- \n 4 A brief history of business rule implementation\n
  - \n 4.1 Java rules the world!\n
  - \n 4.2 It’s the process, stupid!\n
  - \n 4.3 Keep it simple: everything in the database!\n
  - \n 4.4 The proper way: Use all three methods\n
  \n
- \n 5 Implementing Data Integrity Rules\n
  - \n 5.1 What is a data integrity rule?\n
  - \n 5.2 Why data integrity rules should always be implemented in the database\n
  - \n 5.3 Specifying business rules\n
  - \n 5.4 Categories of business rules\n
  - \n 5.5 Implicit Data Integrity Rules of table definitions\n
  - \n 5.6 Implementing Data Integrity using constraints\n
  - \n 5.7 Implementing Data Integrity Rules using database triggers\n
  - \n 5.8 Implementing business rules and functionality using APIs\n
  \n
\n
\n III The relational database in an Object Oriented world\n
- \n 6 Which language do we speak? Communicating with the database\n
  \n
- \n 7 PL/SQL APIs for data manipulation and retrieval\n
  - \n 7.1 Using PL/SQL functions with object type parameters\n
  - \n 7.2 Using PL/SQL functions with JSON parameters\n
  - \n 7.3 Using PL/SQL functions with XML parameters\n
  \n
- \n 8 Database APIs for data retrieval\n
  - \n 8.1 Using database views as APIs\n
  - \n 8.2 Using Pipelined PL/SQL functions for data retrieval\n
  - \n 8.3 Using PL/SQL functions that return a ref cursor\n
  \n
- \n 9 Using Oracle REST Data Services (ORDS) for database APIs\n
- \n 10 Using JSON Duality Views as database APIs\n
  - \n 10.1 Declarative ORM Mapping using JSON Duality Views\n
  - \n 10.2 Using JSON Duality Views for ACID compliant transactions\n
  \n
- \n 11 Summary of best practices for Object-Relational mapping\n
- \n Afterword\n
\n

\nForeword

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\nPreface

\n\n

\nWhy this book and why now?

\n\n

Having worked with Oracle technology since 1992 and having been involved in the design and development of very disparate systems and applications, I realised some time ago that people are still thinking very differently on how to apply the relational database technology in general and the Oracle database in particular. I have worked with people having different backgrounds, from hardcore SQL and PL/SQL developers to Java EE developers, web (Javascript) developers, integration engineers and data scientists. They all have a different view on the role of the database, and due to this some are looking for other solutions for the persistence of data, e.g. NoSQL databases or in-memory solutions. So I think it is about time to go back to basics, and explain again why and how a relational database should be used. And this is also a good opportunity to explain how the Oracle database (and other relational databases) have evolved into multi-purpose solutions.

\n\n

Another thing that I wanted to write about is the way database code is being developed and deployed. Although many tools are available to support both development and deployment, it is amazing how often database objects are built using badly managed scripts, that are run manually and are not optimal for repeated deployments. It often leads to differences between instances in different environments. So it is time to write something about DevOps for database developers. Some time ago I read an article on LinkedIn, written by a former colleague, Gert-Jan Paulissen, who I had not spoken for some time, and who mentioned exactly that problem. He proposed writing a book about the subject together. So we started brainstorming about content, and after some time we decided it would be better to make it a series of books about best practices in Oracle database development. Gert-Jan would work out the DevOps book, while I would focus on the database architecture and new features.

\n\n

This book should enable you to design and build Oracle database architectures for any purpose, using best practices and the newest features. After design you can go hands-on using Gert-Jan’s book for a guide to use the right tooling and methodology to develop the assets described here, and to develop GUI applications.

\n\n

\nThis book is a work-in-progress

The nicest feature of on-line publishing is that the buyer of a book does not get content that might be out-of-date after a few years. Instead, the buyer is entitled to get new editions when these are published. This enables the writer to publish a book that is in fact work-in-progress, and that might be enhanced later with new chapters or updated information when a new version of the discussed software is released.

\n\n

At the time of writing of the first edition of this book Oracle brought out the first beta-version of the Oracle 23c database. I was lucky to be in the beta-testing program of this release. This new release contains a number of new features that may deserve revised and additional chapters. The second edition appeared after the 23c developer release was launched, and aspects of some new features, such as Boolean columns and JSON Duality views, have been added.

\n\n

Moreover, I plan to add some more chapters in the next 1 or 2 years. Anyone who bought this book will be able to download the updated versions for free. Each version will get a ‘What’s new’ section, so the reader will be able to find added and updated information quickly. A few subjects that are in planning are:

\n\n

- Best practices using JSON (and XML) for storing and processing data
- Best practices for using pipelined functions
- Best practices for writing SQL, including new 23c features
- Using in-memory column store for fast analytics
- Setting-up a secure database with Real Application Security

\n\n

\nWho is this book for?

\n\n

This book is meant for architects and database developers alike who’d like to design and build better Oracle database applications and integrations. They will learn to get the best out of the Oracle database and to optimally integrate the Oracle database in any technology.

\n\n

\nHow to read this book?

\n\n

Sometimes, asides are added to highlight further information about a specific topic:

\n\n\n\n\n\n\n\n

\nPlease help improve this book!

\n\n

New revisions of this book are published on a regular basis. If you think a particular section needs improvement, please post an issue in the Leanpub forum, or contact me via Twitter [@pdevaal]:(https://twitter.com/pdevaal).

\n\n

\nAbout the author

\n\n

Peter de Vaal has been working as an Oracle professional since 1992, after having worked in science (1989 PhD physical organic chemistry at Leiden University, the Netherlands, and chimie quantique at the university of Strasbourg, France). It brought him to places such as Mumbai (India), Minneapolis (MI) and Greenville (SC) in the USA and many European countries. And the industries where he could apply his skills ranged from airport baggage handling to chemistry, food industry, hospitals, banking and insurance, governmental institutions etc. Outside his working hours Peter has two other passions: riding bicycles (MTB and race) and playing the card game bridge.

\n\n\n\n

\nI Best practices for the relational model

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n1 Introduction

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n1.1 Why a new book about the relational database?

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n1.2 Is the RDBMS becoming less relevant?

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n1.3 The foundation of the RDBMS revisited

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2 A common question of life: What makes me unique?

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.1 The database recognises all its rows: Internal unique identifiers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.2 The unchallenged unique identifier: Primary key constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.3 Being more unique than just a number: Unique Key constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.4 Uniqueness as inheritance: Unique indexes

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.5 Some examples of unique identifiers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.6 Best practices when defining unique identifiers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n2.7 Best practices for sequences and identity columns

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3 A foreigner is not a stranger

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.1 Happy together: The pure Master-detail relationships

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.2 When relationships break up

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.3 Join the club: Many-to-many relationships

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.4 Click for more information : A one-to-many relationship for look-up data

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.5 Limiting the choice: Restricted foreign keys

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.6 The family tree: Self-references, tree-structures and networks

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.7 To whom do I belong? The exclusive-arc relation

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n3.8 Without relations data will not be coherent

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\nII Implementing Data Integrity

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n4 A brief history of business rule implementation

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n4.1 Java rules the world!

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n4.2 It’s the process, stupid!

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n4.3 Keep it simple: everything in the database!

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n4.4 The proper way: Use all three methods

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5 Implementing Data Integrity Rules

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.1 What is a data integrity rule?

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.2 Why data integrity rules should always be implemented in the database

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.3 Specifying business rules

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.4 Categories of business rules

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5 Implicit Data Integrity Rules of table definitions

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1 Data type, length, precision, scale and semantics

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.1 NUMBER (and its subtypes)

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.2 DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.3 VARCHAR2, CHAR, NVARCHAR2, NCHAR

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.4 Spatial types

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.5 RAW and LONG RAW

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.6 CLOB, BLOB and BFILE

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.7 XMLTYPE and JSONTYPE

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.8 BOOLEAN

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.1.9 User-defined data types

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.2 Collation

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.3 Optionality

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.4 Auto-increment property

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.5.5 Default Value

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6 Implementing Data Integrity using constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.1 VALIDATE/NOVALIDATE

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.2 DEFERRABLE constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.3 Collation

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.4 The purpose of check constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.5 Advantages of check constraints over other implementations of attribute and tuple rules

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.6 Disadvantages of check constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7 Examples of check constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.1 Constraint example 1 Testing dates

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.2 Constraint example 2 The exclusive arc relation

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.3 Constraint example 3 Testing on limit values

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.4 Constraint example 4 Validating JSON

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.5 Constraint example 5 Checking domain values

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.7.6 Constraint example 6 Conditional optionality

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.6.8 Summary of using database constraints

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7 Implementing Data Integrity Rules using database triggers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1 Examples of implementing data integrity rules in database triggers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1.1 Trigger example 1 Conditional optionality

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1.2 Trigger example 2 Restricted foreign keys

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1.3 Trigger example 3 Conditional updatability

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1.4 Trigger example 4 Domain checks against a table

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.1.5 Trigger example 5 Preventing overlapping value ranges and gaps between ranges

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.7.2 Summary using database triggers

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.8 Implementing business rules and functionality using APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n5.8.1 An API for creating an Order

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\nIII The relational database in an Object Oriented world

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6 Which language do we speak? Communicating with the database

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6.1 Application development frameworks using Object Relational Mapping

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6.2 Why objects are not tables and vice versa

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6.3 Integration, what integration?

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6.4 Choosing between object type, XML or JSON for data manipulation APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n6.5 Choosing between object type, XML, JSON or SQL methods for data retrieval APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n7 PL/SQL APIs for data manipulation and retrieval

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n7.1 Using PL/SQL functions with object type parameters

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n7.2 Using PL/SQL functions with JSON parameters

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n7.3 Using PL/SQL functions with XML parameters

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n8 Database APIs for data retrieval

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n8.1 Using database views as APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n8.2 Using Pipelined PL/SQL functions for data retrieval

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n8.3 Using PL/SQL functions that return a ref cursor

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n9 Using Oracle REST Data Services (ORDS) for database APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10 Using JSON Duality Views as database APIs

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10.1 Declarative ORM Mapping using JSON Duality Views

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10.1.1 Using the duality view for querying data

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10.1.2 Using duality views for updates

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10.1.3 Using duality views for inserts

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n10.2 Using JSON Duality Views for ACID compliant transactions

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\n11 Summary of best practices for Object-Relational mapping

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n

\nAfterword

\n\n\n

This content is not available in the sample book. The book can be purchased on Leanpub at http://leanpub.com/the-oracle-database-in-modern-it-architectures.

\n\n\n\n

\n\n","tocStructure",[328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394],{"_78":72,"_43":69,"_70":-5,"_73":74,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":69,"_70":-5,"_73":74,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":84,"_70":-5,"_73":85,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":89,"_70":-5,"_73":90,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":94,"_70":-5,"_73":95,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":97,"_70":-5,"_73":98,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":100,"_70":-5,"_73":101,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":103,"_70":-5,"_73":104,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":106,"_70":-5,"_73":107,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":72,"_43":109,"_70":-5,"_73":110,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":113,"_70":-5,"_73":114,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":117,"_70":-5,"_73":118,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":120,"_70":-5,"_73":121,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":123,"_70":-5,"_73":124,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":126,"_70":-5,"_73":127,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":130,"_70":-5,"_73":131,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":133,"_70":-5,"_73":134,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":136,"_70":-5,"_73":137,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":139,"_70":-5,"_73":140,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":142,"_70":-5,"_73":143,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":145,"_70":-5,"_73":146,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":148,"_70":-5,"_73":149,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":151,"_70":-5,"_73":152,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":155,"_70":-5,"_73":156,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":158,"_70":-5,"_73":159,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":161,"_70":-5,"_73":162,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":164,"_70":-5,"_73":165,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":167,"_70":-5,"_73":168,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":170,"_70":-5,"_73":171,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":173,"_70":-5,"_73":174,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":176,"_70":-5,"_73":177,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":72,"_43":179,"_70":-5,"_73":180,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":183,"_70":-5,"_73":184,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":187,"_70":-5,"_73":188,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":190,"_70":-5,"_73":191,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":193,"_70":-5,"_73":194,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":196,"_70":-5,"_73":197,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":199,"_70":-5,"_73":200,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":203,"_70":-5,"_73":204,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":206,"_70":-5,"_73":207,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":209,"_70":-5,"_73":210,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":212,"_70":-5,"_73":213,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":215,"_70":-5,"_73":216,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":218,"_70":-5,"_73":219,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":221,"_70":-5,"_73":222,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":224,"_70":-5,"_73":225,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":72,"_43":227,"_70":-5,"_73":228,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":231,"_70":-5,"_73":232,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":235,"_70":-5,"_73":236,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":238,"_70":-5,"_73":239,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":241,"_70":-5,"_73":242,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":244,"_70":-5,"_73":245,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":247,"_70":-5,"_73":248,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":250,"_70":-5,"_73":251,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":254,"_70":-5,"_73":255,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":257,"_70":-5,"_73":258,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":260,"_70":-5,"_73":261,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":263,"_70":-5,"_73":264,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":267,"_70":-5,"_73":268,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":270,"_70":-5,"_73":271,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":273,"_70":-5,"_73":274,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":276,"_70":-5,"_73":277,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":280,"_70":-5,"_73":281,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":284,"_70":-5,"_73":285,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":88,"_43":287,"_70":-5,"_73":288,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":290,"_70":-5,"_73":291,"_71":72,"_91":-5,"_92":-5,"_82":-5},{"_78":79,"_43":294,"_70":-5,"_73":295,"_71":72,"_91":-5,"_92":-5,"_82":-5},"publicChapterContent","nextChapter","sections",[338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358],"routes/_store+/_layout","routes/_store+/_readers+/_layout","actionData","errors"]

\nI Best practices for the relational model

Table of Contents

\nForeword

\nPreface

\nWhy this book and why now?

\nThis book is a work-in-progress

\nWho is this book for?

\nHow to read this book?

\nPlease help improve this book!

\nAbout the author

\nI Best practices for the relational model

\n1 Introduction

\n1.1 Why a new book about the relational database?

\n1.2 Is the RDBMS becoming less relevant?

\n1.3 The foundation of the RDBMS revisited

\n2 A common question of life: What makes me unique?

\n2.1 The database recognises all its rows: Internal unique identifiers

\n2.2 The unchallenged unique identifier: Primary key constraints

\n2.3 Being more unique than just a number: Unique Key constraints

\n2.4 Uniqueness as inheritance: Unique indexes

\n2.5 Some examples of unique identifiers

\n2.6 Best practices when defining unique identifiers

\n2.7 Best practices for sequences and identity columns

\n3 A foreigner is not a stranger

\n3.1 Happy together: The pure Master-detail relationships

\n3.2 When relationships break up

\n3.3 Join the club: Many-to-many relationships

\n3.4 Click for more information : A one-to-many relationship for look-up data

\n3.5 Limiting the choice: Restricted foreign keys

\n3.6 The family tree: Self-references, tree-structures and networks

\n3.7 To whom do I belong? The exclusive-arc relation

\n3.8 Without relations data will not be coherent

\nII Implementing Data Integrity

\n4 A brief history of business rule implementation

\n4.1 Java rules the world!

\n4.2 It’s the process, stupid!

\n4.3 Keep it simple: everything in the database!

\n4.4 The proper way: Use all three methods

\n5 Implementing Data Integrity Rules

\n5.1 What is a data integrity rule?

\n5.2 Why data integrity rules should always be implemented in the database

\n5.3 Specifying business rules

\n5.4 Categories of business rules

\n5.5 Implicit Data Integrity Rules of table definitions

\n5.5.1 Data type, length, precision, scale and semantics

\n5.5.1.1 NUMBER (and its subtypes)

\n5.5.1.2 DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE

\n5.5.1.3 VARCHAR2, CHAR, NVARCHAR2, NCHAR

\n5.5.1.4 Spatial types

\n5.5.1.5 RAW and LONG RAW

\n5.5.1.6 CLOB, BLOB and BFILE

\n5.5.1.7 XMLTYPE and JSONTYPE

\n5.5.1.8 BOOLEAN

\n5.5.1.9 User-defined data types

\n5.5.2 Collation

\n5.5.3 Optionality

\n5.5.4 Auto-increment property

\n5.5.5 Default Value

\n5.6 Implementing Data Integrity using constraints

\n5.6.1 VALIDATE/NOVALIDATE

\n5.6.2 DEFERRABLE constraints

\n5.6.3 Collation

\n5.6.4 The purpose of check constraints

\n5.6.5 Advantages of check constraints over other implementations of attribute and tuple rules

\n5.6.6 Disadvantages of check constraints

\n5.6.7 Examples of check constraints

\n5.6.7.1 Constraint example 1 Testing dates

\n5.6.7.2 Constraint example 2 The exclusive arc relation

\n5.6.7.3 Constraint example 3 Testing on limit values

\n5.6.7.4 Constraint example 4 Validating JSON

\n5.6.7.5 Constraint example 5 Checking domain values

\n5.6.7.6 Constraint example 6 Conditional optionality

\n5.6.8 Summary of using database constraints

\n5.7 Implementing Data Integrity Rules using database triggers

\n5.7.1 Examples of implementing data integrity rules in database triggers

\n5.7.1.1 Trigger example 1 Conditional optionality

\n5.7.1.2 Trigger example 2 Restricted foreign keys

\n5.7.1.3 Trigger example 3 Conditional updatability

\n5.7.1.4 Trigger example 4 Domain checks against a table

\n5.7.1.5 Trigger example 5 Preventing overlapping value ranges and gaps between ranges