Architecture of an RDB

Databases can be understood at three conceptual levels:

External level
Conceptual level
Physical or internal level

The external level represents the application view of the data, including application-specific semantics and constraints. For the VideoMagix application, for example, you might decide that the customer can only belong to a single club, or that a video tape can only be borrowed by one customer at a time.

The next level is the conceptual level. This is the level of entity-relationship modeling. From this perspective, we consider tables, rows, columns and indexes. A table is a structure which looks like this:

Tape ID	Title Name	Release Date	Cost	Period	Available?
001	Gone with the Wind	1939	$4.00	2 day	Y
002	Ghandi	1982	$3.00	2 day	N
003	2001	1968	$4.50	1 day	Y

Each row represents a single record. Each column represents a field in the record. At this level, we are concerned with understanding the relationships among the various objects in the domain and how these translate to tables and their interrelationships.

The most basic level is the physical level, which corresponds directly to the contents of the disk files used to capture these data and indexes. Here, you consider questions about the size of the records in the file, data access blocks, disk access methods and so forth.

The architecture of relational databases assumes three types of users:

The end user, who is not aware of the internal database structures at the physical or logical level. End users are not responsible for programming, but rather they use the applications prepared by the programmer and the query tools provided by the database product. They operate at the conceptual and external level.
The programmer, who is responsible for developing the applications used by the end users. He is responsible for the creating the external level of the architecture.
The database administrator (DBA), who is responsible for defining the physical and conceptual levels of the database. The DBA is typically an expert in a particular vendor's database software, and knows how to optimize the tables for maximum performance.

Each row in a table has a unique identifier. This identifier consists of one or more fields in the row. The same fields are used as the identifier for all rows in a table. This is called the primary key for the table.

Relational databases are all about relations among tables. One way for two tables to be related to one another is for Table A to contain the primary key of Table B. This is called a foreign key in Table A, and it serves as a reference to a row in Table B.

Tape Name (primary key)	Release Date	Cost	Days	Who rented (foreign key)
Gone With The Wind	1939	$3.00	2	10010
Ghandi	1982	$4	2	10021

Table A

ID (primary key)	Name	Address	City/ State	Phone	Club?
10021	John Doe	1 Main St.	Anytown	999-123-4567	N
10010	Jesse Liberty	1 Till Dr.	Acton, MA	617-747-7301	Y

Table B

In this example the

ID

in Table B is the key on which Table B will be indexed. It is also a foreign key into Table A. If you want to find out who rented a tape listed in Table A, you look up the ID
in Table B and get the customer's record.

A table is always indexed on its primary key. It is also possible to index on any other field to speed up searching for a record on that field. For example, you might index

Name

in Table B. Or, for that matter, you might index the combination of Address
and City/State
as a single key. These additional indexed fields are called secondary keys.

There are restrictions on the contents of the rows. For example, duplicate rows are not allowed and all primary keys must be unique within the table. The order of the rows is not important to the conceptual model, although it can be important to the physical model. The value of each field is atomic — each field represents a single, indivisible bit of information, thus, lists are not allowed within a single field.

There are three types of table:

Entity table — contains user-meaningful data
Relationship table — represents relationships between tables, using foreign keys to reference the underlying rows in the entity tables
Virtual table — used to present a "view" of the database to an application; it is usually not possible to update a database through a view