As already described in G. Carpenter and G. Gulbenkian’s posts, the definition of a relational database is, “a collection of data items organized as a set of formally described tables from which data can be accessed easily” [1]. There are many important terms associated with relational database design/theory, some of which were already described by G. Carpenter and G. Gulbenkian, such as tuples (rows) and attributes (columns). Another important term is relation (or table) which is defined as, “a set of tuples that have the same attributes” [1]. The relational model requires that each row of a table be unique [2]. They go on to say that this uniqueness can be achieved by “designating a primary key – a column that contains unique values for a table” [2]. The below figure shows that the primary key chosen was Customer ID [2].
Figure 1: Table with the primary key as Customer ID [2]
However, “primary keys are a function of individual tables,” therefore, if you want to join together multiple tables you may need what is called a “foreign key” which is “used to reference a primary key in another table” [2]. The figure below shows how the primary key of Customer ID is then used as a foreign key in a different table.
Figure 2: Table with Customer ID as the foreign key [2]
Utilizing the above, multiple types of relationships can be formed between related tables (i.e. one-to-one, one-to-many, or many-to-many) [2]. From there the tables forming the databases can be further optimized by a process called normalization which G. Carpenter also briefly discussed. The above are the basics for creating an efficient and well designed database.
[1] http://en.wikipedia.org/wiki/Relational_database
[2] http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
No comments:
Post a Comment