Databases – Relational Databases


title: Relational Databases

As a database is a way to store data, relational-databases are a model for how the data is being stored. The data is organized into tables, also known as relations. The tables contain a record for each instance of the data, known as records or tuples. Unique identifiers identify each record to describe it across the database.

Tables

Like the a sheet in excel, tables are made up of columns and rows. Each row is an instance of data with attributes in the column of the table know as fields. The rows are called as records or tuples while columns are called as fields or attributes. There can be several tables for each category for entities. An example could be a table of users. Each row would be a user and each field would be details on the user like email, password, and contact details for that specific user. In Figure 1 you can see diagram of the example.

           user      email    Telephone                           
row 1      Jerry    [email protected]      771447444121          
row 2Sally[email protected]771447444121
row 3Alex[email protected]771447444121
row 4Doug[email protected]745151515152

Figure 1 – Example of user table.

Records

A record is a single entity of data. It is a single row of a table known as tuple. As in the example above, it could be a user, an account, a device, or anything that data can represent. Records do need a unique identifier, sometimes referred to as a key. This key must be unique as it is used to describe relationships a record has with other records in other tables. In Figure 1, we could add keys to each row that identifies each user with a key and the table would now look like Figure 2.

KEY  user      email          Telephone                         
u1      Jerry    [email protected]      771447444121          
u2Sally[email protected]771447444121
u3Alex[email protected]771447444121
u4Doug[email protected]745151515152

Figure 2 – Example of user database with KEY field.

Fields

Fields describe the record. This could hold any information on the entity known as attributes that the record symbolizes. In Figure 3 you can see a table that shows pets. The columns (fields) describe each pet (record) with p_name, p_age, p_type and p_owner. The p is shorthand for pet and the last column will be explained in the next section on relationships.

KEY      p_name   p_age    p_owner   
p1      Suzy    [email protected]      u1
p2Little Dip[email protected]u1
p3      Amillë    [email protected]  u2
p4Doug[email protected]u3

Figure 3 – Example of Pet table.

Relationships

Relational-databases allow you to describe the relationships entities have with each other. This is sometimes the most difficult topic of relational databases to understand. If we take our example tables we should be able to see the relationship our user table has with the pet table. If you read the p_owner field you can see it could be also be as in Figure 4. This explains the relation each pet has with a user, as each of the pet is owned by someone say user in our example. Relationship could have different types: one-to-one, one-to-many or many-to-many.

KEY      p_name  p_age    p_owner   
p1      Suzy    [email protected]      Jerry
p2Little Dip[email protected]Jerry
p3      Amillë    [email protected]  Sally
p4Doug[email protected]Doug

Figure 4 – Example of Pet table with owner field linked.

A one-to-many relationship is one record linked to many other records, the example being the user Jerry having two pets. It could also be a many-to-many relationship where the tables could be books and authors, as authors could co-write many books. Finally the most common relationship type is one-to-one, a record that can only be linked to one, and only one, other record.

Foreign key

To describe the Relationships above, Foreign keys(FK) should be used to link tables.
Each FK needs 3 parameters:

  1. Referenced table: having candidate keys
  2. Child table: having foreign keys
  3. Constraint: having the columns which both tables have in common.
IdNameGender
1Hung1
2Linh2
3Duong0
4Alice2
5Bob1

Figure 5 – Example of child table containing FK Gender

GenderIdDescription
0Not yet defined
1Male
2Female

Figure 6 – Example of referenced table containing GenderId with candidate keys

ALTER TABLE <child table> ADD CONSTRAINT FK_Child_Gender_Ref_GenderId FOREIGN KEY(Gender) REFERENCES Child(GenderId);

Figure 7 – A constraint of both tables is Gender linking to GenderId

Conclusion

This is just a brief intro into relational-databases. Below links are provided to resources that could help you further study the subject.

More Information:

This article needs improvement. You can help improve this article. You can also write similar articles and help the community.