When William the Conqueror invaded England in 1066 and defeated Harold’s army near Hastings, his immediate task was to pacify the country. After a phase of castle building, land redistribution and military suppression of the Anglo Saxons, William ruled in relative peace. He knew that in order to rule a country he needed a stable government based on a sound income. He needed to raise taxes. In order to do this he needed data about the land holdings, the agricultural output and the military capacity of the population.
In Utopia, the Utopian Football Association, UFA, regulates football. This body is responsible for interpreting the rules of football; for the disciplinary hearings of cases reported to it; for developing coaching programmes in the amateur game; for organizing the national team; and for keeping a register of players and coaching staff for all the leagues of professional and amateur teams in Utopia. In addition, UFA organizes a number of competitions between the clubs, notably the UFA Cup for professional sides and the UFA Urn for amateur clubs.
The Object Diagram derived in Section 3.4 will form the basis of the analysis for the creation of a database schema for the UFL application. Because it follows object-oriented principles, it also contains a great deal of the specifications for the processing logic of the proposed system. Each of the object definitions shows the object’s data elements as well as the methods it contains.
E. F. Codd’s first article on the application of set theory to stored data led to the development of the relational data model. Part of this application of set theory gives the definitions of entity sets participating in relationships. By using just these definitions, the entity-relationship diagram can illustrate the entity types in a database schema and the relationships that exist between these entity types.
The relational model depends on a well-established branch of mathematics called set theory. This theory has led to the process of normalization to ensure that the entities described in the UFL design are well formed and that the relationships that exist between entities are correctly specified.
A method has been established whereby information about our scenario may be captured in a formal and methodical way. The object-oriented analysis methodology leads into using the Universal Modeling Language (UML), as a diagramming standard.
A join takes place when more than one table is cited in the from clause of a select statement or in a sub-query associated with an insert, update or delete command.
Up until now, retrievals have been concerned with information exactly as it was entered into the database. Often, elements of this data have been chosen for the selection set through restrictions and projections (via SQL select statements). However, the selection set has always been a subset of the tuples held in the relation.
SQL is a declarative language, not procedural or imperative. The art of writing SQL instructions is based on the ability to define clearly what is required, rather than programming a set of instructions to meet such requirements.
An Oracle database is a logical storage unit. It has a name and its data is stored in several physical files, spread across the host machine’s disks. The files are used for user data, for data recovery and to store control information. The files have a different internal format according to their purpose and they belong exclusively to the named database.
The first section of this book introduced the idea of separating the common functions for data storage and retrieval from the interface and business logic functions. This results in smaller application programs communicating with a database manager and requesting data access services.
The discussion on PL/SQL was left at the end of Chapter 10, having introduced the block structure of the language and the various forms this structure may take. The simplest form is the anonymous block that has no name and is not stored on the server, although, for convenience, the client may store it in an external file.
The initial requirements analysis for the Liga Utopia, using UseCase diagrams, was introduced in Chapter 2. As well as the UseCases, a class diagram was developed that was converted to an entity-relationship diagram (ERD). In subsequent chapters, the UFL case study has been used to develop a normalized database implementation and to illustrate the SQL language, including PL/SQL.
Indexes are used to provide fast access paths during the execution of queries. The optimizer will generally choose to use an index in place of a full table scan if the index key is an attribute within the selection restriction or if it is a join attribute. When a primary key constraint is specified on a table, an index is automatically created. Indexes on attributes other than the primary key can be created once the table is populated with data. Each index entry contains the index key value together with a ROWID that points directly to the position of the row in the base table that contains the key value.
The majority of database applications process many small concurrent update transactions. A bank’s automated teller machine (ATM) system, which must allow for thousands of withdrawal, deposit and transfer transactions to happen concurrently, is a typical example of such an application. This type of processing is known as online transaction processing (OLTP), and databases used to support such processing need to meet the strict ACID properties of transaction processing (see Chapter 8). OLTP is often measured in terms of transaction throughput: the number of committed transactions per second.
Online transaction processing (OLTP) applications define performance in terms of throughput. These applications must sometimes process millions of very small transactions per day. Decision support systems define performance in terms of response time. Demands on the database that are made by DSS applications vary between a query that fetches only a few records, or a massive query that fetches and sorts hundreds of thousands of records from different tables.
The application example in Chapter 14 is designed to reside on a client machine and connect independently to the database server. The application illustrates the use of JBuilder to provide a visual design environment that can be used to generate Java code and compile, test and deploy it.
This chapter outlines some performance and reliability issues associated with traditional, single processor, centralized database systems, and describes a number of architectural alternatives devised to alleviate such problems. Specific focus is given to parallel database architectures that enable a single database to distribute its associated query processing across multiple processors and disks.
A distributed database is a collection of independent database installations, situated on different nodes of a local or wide area network. Each of the individual participating databases offers a part of the overall dataset of the distributed database. Any type of database can participate. They could be relational, object or Codasyl, or adhere to any other data model.
Documentation for businesses, governments, news media, universities, libraries and practically every organization plays an increasingly critical role. It is fundamental to their ability to provide public and private services based on knowledge assets and even to sustain their existence.
