In this second week of the tutorial, l’m going to talk about the levels of database development and explain what each of these levels are and how they are important to the database design process.
There are 3 levels of database development. These levels are Conceptual, Logical and Physical.
Conceptual level is the basic and the first approach to knowing the entities to be collected about the system being built. There are many ways to gather data or information about a system. You can do interviews, questionnaires etc to collect information about the system. so the conceptual level comes right after collecting this information and understanding the system at its very high level. We can say, the conceptual level comes after the business analysis. Business analysis simply means studying or collecting data about the processes of the system that needs the database. It results in identifying entities and high level relationships. No standard representation but should be presented detailed for understanding. It’s very good to conceptually give a relationship to help in further breaking down the components of the system into lower level parts. Failure to capture a process at the conceptual level will result in the system missing that component at the end and result in failure. The conceptual level of development can simply be described as getting the objects or components of the system process and understanding their relationship to each other at a high level. You don’t have to understand the details of every single relationship, just understand there exists a relationship.
Three important terms to take away from here are entity and relationship.
An Entity is a real world object that information need to be stored about in the database. An entity can be a bank, person, customer, appartment, etc. It’s very important to identity all the entities of the system at the conceptual level. if you fail to identify an entity, that means it’s not going to exist in the system and that is failure for the system.
Now let’s talk about second important thing to identify at the conceptual level. That is relationship. This is very simple to understand and it means exactly as it sounds. After identifying many entities in the system under consideration, it is very important to identify the relationship that exist between these entities. Remember, relationship that exist among entities, not relationship that exist among attributes. We are not even at attributes yet, attributes come at the logical level of design. You are not supposed to know the detailed relationship at the conceptual level. Just recognise that a relationship exist between one entity and another.
For example, let’s take our banking system again. If l’ve identified customer as an entity and account as another entity, I should be able to recognise that there is a relationship between a customer and an account base on the business requirement. That is, a customer has account or accounts and an account belongs to a customer or customers. This is very important to continue the design process because it will help you to build good Entity Relationship diagrams for the business process. I’ll be talking about Entity Relationship Diagram, Relation Model and keys in week 3 of the tutorial.
When you get your entities and identify their relationships, then this is converted into a graphical diagram called the entity relationship diagram which l’ll be discussing in week 3. For now, let’s go and identify some entities and relationships that exists among them in common systems like the bank, a college institution etc.
The next level of development is the Logical design level. At the logical level, the attributes of all entities are identified. An Attribute is a property of an entity that gives more information about the entity. It is some characteristic of an object that the business wants to keep information about. For exampe, if you identify an entity customer for a banking system. The customer entity will have some attributes or characteristics that the bank wants to keep information about. This can be the customer name, address, phone, account number, account branch, gender, race, date of birth, date account opened etc. These are characteristics the business wants to keep information about. Its not your responsibility to add other attributes you know about that particular entity, for example, knowing that a customer is a human and you know humans can play sports. so you decide to add, sport played to the set of attributes. If the business doesn’t need this attribute for it’s operation, there is no need to keep that attribute. So, it’s the business requirement that helps in identifying the entity and the attributes necessary to keep about those entities in the database.
In short, get the entity for the system and their relationships at conceptual level, then get the attributes of those entities identified base on the business requirements at the logical level. For example, for an apartment business, you might identity apartment as an entity, then you go on and identify the attributes of this apartment entity such as, building number, deposit, monthly payment, length of lease etc.
At the logical level, you identify the primary key of the entities and the foreign keys that exist for related entities. l’ll be talking about keys in week 3 of the tutorial. A primary key is simply an attribute or set of attributes that uniquely identify each instance of an entity.
Stored vs Derived attributes.
Stored attributes: Attributes stored permanently in the database. example Date of birth.
Derived attributes: Attributes based on calculation from other columns or derived based on other attributes, example age.
Entity Relationship Diagrams and Relational schemas can then be drawn for the system at the logical level to graphically represent the system. l’ll be talking separately about ER diagrams, Relational Schema and Keys in week 3, so check it out.
The next level of database design is the physical level. This is where you actually create your database consisting of the tables representing the entities that you want to keep information about. An entity might translate into a table or not. It depends on the entity. Some entities might be broken apart and simply become column fields in another table. Simply, we get the table names to represent entities, name of columns in the tables and the data types of the column fields. All these are physically created in the system depending on the type of database management system in use. we’ll focus our attention on programming in microsoft sql server 2008 R2 using TSQL in the up coming weeks.
Watch out for the up coming tutorial in week 3 on Entity Relationship Diagrams, Relational Schema and Keys, it’s very important.
