5.5 Components of Relational Databases and Technical Terms


A relational database consists of various components. In this section you will be guided through the most important terms that are often used when working with relational databases. Knowing the meaning of the most important words used in the database arena is important in order to understand the next few chapters of this book.

  • Tables and relations Every database consists of fundamental components called relations. Relations are tables consisting of various lines and a fixed number of columns.

  • Primary keys In order to identify a row of data, every table should have a column containing unique values. This column is called the primary key of a table. A column that is the primary key must not contain duplicated entries. Every sophisticated database will take care of that in order to satisfy the demands of the relational database concept. In the case of PostgreSQL, a table need not have a primary key primary keys are optional.

  • Foreign keys Foreign keys are columns that are related to a certain column in a second table. The values in a foreign key column are treated according to the constraints defined for the column.

  • Constraints Constraints are attributes defined for a certain object. Constraints can either be restrictions or rules on how to perform certain operations.

  • Data types Every column in a table must have a predefined data type. In contrast to some programming languages, PostgreSQL, like most other databases, has strict rules concerning data types. For instance, a column defined as date must not contain values other than dates.

  • Indexes To speed up queries, indexes can be used. Internally indexes are a complex tree structure that allows fast access to individual records.

  • Sequences Sequences can be used to generate consecutively listed numbers that are unique. Even in case of concurrent accesses to the sequence, PostgreSQL will make sure that every number in the list can only be used once. To guarantee that the value returned by the sequence is unique, accessing the current value of the sequence will automatically increment the value of the sequence.

  • Joins In the relational database model, data is stored in tables. If information from more than just one table has to be retrieved from the database, so-called joins have to be performed. Joining means that tables are connected with each other by using a common column.

  • Triggers All sophisticated databases support triggers. Triggers are a feature that can be used to start certain functions whenever a predefined event occurs. Triggers are defined on tables and can fire when INSERT, UPDATE, or DELETE operations occur.

  • Views Views are virtual tables. This means that users can select data from a view just as they can from a table. However, in reality, a view is not a real table but the result of executing a SQL statement. Performing INSERT and UPDATE operations can only be done by defining rules that tell PostgreSQL what to do in case of INSERT or UPDATE.

  • Aggregate expressions Aggregate expressions are used to perform operations with multiple lines returned by a query.

  • Aggregate functions Aggregate functions are used to perform data calculations, like maximum, minimum, or average.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net