Organize a collection of data somehow for later usage and you will get a database. In the last couple of years, with the spread of the Internet, databases have become something you will come across everywhere on the World Wide Web. Most websites have a strong database behind them. With this phase of the web's evolution, if you work in software development, eventually you will need to create and plan a database from scratch. This article is here to teach you and show you what traits a solid plan should have.
As good as the ERM diagram may be at visualizing our plan, for optimization reasons we need to transform it into another model: the relational model. This is the form in which we will find it in the database: the tables.
For this representation there are a couple of rules. Two arbitrary rows cannot be the same. The set of combined attributes that forms the key cannot repeat itself in more than one instance (so the key is indeed unique). The attributes forming the key may be primary or foreign key (identifying a relationship, not the entity itself). A primary key attribute cannot be null or empty.
The order of the instances of the entity (the relation) in the table does not matter. Two attributes within the same entity cannot have the same name. These few constraints will allow us to create a database that we can manage with ease. The collection of relations (tables) will form the relational database.
How do we transform the ERM to the relational model? Here are the steps to follow. First, write down the entities, and list after them their attributes. We underline the keys. Second, write up the relationship names. For those in the parentheses, we will get the attributes that belong to that relationship. Furthermore, both key sets from the connecting entities will get in.
What will be the new primary keys for these relations? It depends on the relationship type. If it is "one on one," either one of the E1 or E2 sets can be the key set for this. If it is "one to many" then the key set at the many side will be the key. And finally, when you have an "many to many" relationship, the new key will be a composed key, and both key sets from E1 and E2 will form the new key set.
In the case of an "is a" connection, the specialization will have the key of the general entity, and will have its attributes as well. The third and final step in the transformation is to determine the foreign keys. If two relationships at this stage have the same key, we unify those two relationships and substitute a single relationship. The relationships that have a composed key (multiple attributes as keys) cannot be unified.
Before we create the database, first we need to further improve it by bringing it to the third normal form. I will leave this to my next article, where, besides the normalization of the databases, I will teach you how to visualize your plan in MySQL Workbench.
Additionally, we will write and generate the database creation code. I will use the MySQL syntax, however the Oracle one is pretty similar. If you understand the concept, with just a few modifications it should work for Oracle too. Therefore, by the end of that article we should have a fully operational and well-planned database.
Thanks for reading my article; I do hope that you learned a lot today. I invite you to share your thoughts about this subject or your comments on the article in the blog following the article. If you have any questions, I will answer them as soon as possible. If you would like to ask a community to help you with your issue you can join the friendly DevHardware forum. Rate my article if you liked it or not and remember to Live With Passion!