In this chapter the terminology and structural concepts of the relational model are explained. In the picture below there is a Car Rental database. In the database there is information about customers, cars and car rentals. The concepts of the relational database are being drawn in the picture.
Picture. The concepts of the relational database (Original designer of the database is Kai Kivimäki, Haaga University of Applied Sciences)
Table (Relation)
A table consists of rows and columns. Each row in the table represents a collection of related values. Tables are used to hold information about the objects to be represented in the database.
In a specific table, there is data of one kind of objects (entities). In the example Car Rental database there are four tables: Customer, Rent, CarType and Car. In the Customer table there is the data of the customers, in the Rent table there is the data of the car rentals.
A table is a database concept, a relation is a relational model concept.
Row (Tuple, Record)
Each row in the table represents a collection of related values of a one object (entity). There is a data of a one customer in one row in Customer table.
A row is a database concept, a tuple is a relational model concept. A record is a little bit outdated term for a tuple or row.
Column (Attribute)
Each column in a table holds a certain kind of data. A Column has a name that describes the data of the column. In the Customer table there are columns e.g. firstname and surname.
A column is a database concept, an attribute is a relational model concept.
Field
A field stores the actual value of an attribute. There are broken lines to show the values of a certain attribute that are stored in the fields.
Primary key
Primary key is the column (or set of columns) which values uniquely indenfy the row. All primary key fields have a different value in a specific table. A table should have a primary key.
The primary key of the table Car is the register number (Reg_No). Two cars can't have the same register number.
Foreign key
Foreign key is a column whose values refer to the primary key of another table.
For example in the Car table the Model_id values refer to the Model_id of the CarType table. The car whose register number is 'ABC-111' is Ford Focus. The Cust_id in the Rent table refers to the Customer table Cust_id. The customer number 5 who has rented a car having register number CCE-326 is Patricia Smith.
Primary Table and Related Table
Primary and related table definition is always between two tables which have a relationship between them.
For example the tables Car and CarType have a relationship. The foreign key (model_id) of the Car table refers to the CarType table primary key (model_id). The CarType table is the primary table and the Car table is the related table.
There is also a relation between the Car and Rent tables. The Reg_no of the Rent table (foreign key) refers to the Reg_no of the Car table (primary key). In this case the Car table is the primary table and the Rent table is the related table.
Primary table is the table whose primary key is referenced from another table's foreing key. The table having this foreign key is a related table.
The primary table is also called a parent table and the related table is called a child table.