Databases have become more and more indispensable in our daily life. We deal with data everyday and everywhere - cellular phone contacts, medical records, logistical data and transaction records, etc. They are all stored in databases. It is hard to imagine what the world would be like without databases. Perhaps there would be no ATM, no credit card, no GIS and no airline reservation...
The history of database can be traced back to the earliest days of electronic computing. Over the years, there has been a number of database types emerged, such as hierarchical database, relational database, object database, XML database etc. They differ in hardware requirements, efficiency, and how data is stored, organized and accessed.
Relational databases store data in collections of tables. Relations are defined between tables for cross referencing. The way it stores data makes users easy to understand the structure and content of the data. Developers may use Structured Query Language (SQL) to query data, and add indexes to database for faster querying, making relational database performs well even when the amount of data increases over time. Therefore, despite being challenged by object database for years, relational database still remains to be the most prevalent way of storing enterprise data to this date. Oracle, Microsoft SQL Server, MySQL and PostgreSQL are some of the popular relational database management systems.
Relational database stores data as collections of tables. Each table contributes a set of columns, which are the properties of the table that are worthwhile and need to make persist. Relationships, critical elements in relational database can be added between tables to indicate that two sets of data are inter-related.
A relational database consists of a collection of tables (i.e. entities), from which we want to seek information. A table consists of columns, which are the properties of the table, and rows which are the records to store and retrieve.
Columns refer to a set of fields in tables. A column describes a property we are interested in storing for the table it belongs to.
A relationship is a connection between two entities. It connects data (in tables) together in meaningful ways. For instance, knowing the information of a transaction is meaningless without knowing the customer who performed the transaction. Hence, we would relate the customer and transaction tables to obtain complete information about a transaction.
An entity relationship diagram (ERD) is a visual form of relational databases. People use ERDs to model and design relational databases. The following is an ERD that depicts the tables for a simple school system.
School and Student are entities (note: In ERD, the term "entity" is often used instead of "table". They are actually the same). In the School table, there are two columns - id and name. id is the primary key (PK) column, which is in bold and has a key symbol next to it. A primary key is capable in uniquely defining records in a table. In other words, there must not be two (or more) school records that share the same id. Student, another table, has a foreign key column, namely SchoolId. It is a reference to the primary key Id in the School table. Note that foreign keys need not be unique. Multiple student records can share the same School ID. In a real world scenario, there can be multiple students studying at the same school and therefore have the same school id.
Between the School and Student entities, there is a connector. We call it a relationship. In this case, it is a one-to-many relationship. It means that the entity with the primary key (i.e. School) contains only one record that associates with zero, one or many records in the referenced entity (i.e. Student). We can describe the relationship in everyday language by saying: A school intakes many students.
If the database you are going to build consists of a few tables only, you do not need to design it at all. You can simply use the management tools provided by the database management system, or run a few SQL statements to get the database built. However, this would hardly be the case in real world.
If you are planning a database with more than 10 tables, or even up to hundreds of tables, it would be better to consider designing the database prior to building it. Here are some of the benefits of database design:
A good database design does take time and effort to develop and conceive. A helpful database design software can help you to reduce time and effort spent. Visual Paradigm provides you not only with an ERD tool but also a set of visual modeling features that helps you to express your design ideas more easily and quickly. It supports most of the popular relational database management systems in the market today. Here is a list of supported databases:
In this section, we are going to design a relational database for a bus route management system, using ERD, in Visual Paradigm.
To begin, we need to make decisions regarding what entities to be created. During the process, you may find many candidates. Here are some of the guidelines to help you to identify entities:
Finally, the following entities are obtained:
Now, let's start the design process.
Now, we can specify the columns for the entities. Similar to identifying entity, you need to think carefully what columns you need to store in each entity. Do not add columns for data that have no value to the system.
+ schedule_id : integer (10)
departure : date
arrival : date
+ route_id : integer (10)
fare : float (10)
+ stop_id : integer (10)
name : varchar(255)
terminus : blob
+ driver_id : integer (10)
name : varchar(255)
employ_date : date