Database Design Tutorial

A Database Design Tutorial:

There are several different kinds of database representations. An Entity Relationship Diagram or a ER model, an Enhanced Entity Relationship Diagram EER model, Object Relational Model etc. ER Model is the most universally followed database convention at least in my experience with software application development. Any enterprise application today would surely have a database connected to it, so I think that understanding database design and to be able to design databases is a “must have” for Software developers personnel. In this article I would walk through some of the essentials to give you the basic idea by giving you an example. Once you know how it’s done in a “Non Textbook” way I guess reading the text book or some advanced tutorial would be very easy and interesting. 

The First Step in ER modeling: 

The first step in designing the database is to know what are the business objects or so called “Entities”. How to determine what the entities are? The textbook says Read the problem statement or the scenario and try to identify nouns. From a collection of nouns it would be much clearer to determine what the entities are in your system. No doubt it’s a text book approach. I have had very few instances where I had a problem statement and I could do a noun analysis for an object oriented or the database design. So try to think in terms of the business and ask yourself why? This is called the “ROOT CAUSE ANALYSIS”. Ask yourself the question Why the customer wants to develop this system, who would be using the system, what features should be provided and most importantly what data needs to be stored?. 

 E.g. If someone wants to develop an online forum and you have to design the database for them, try to think what a forum does and generate a problem statement. The requirement would be vaguely “A discussion forum has many categories. In each category there are many topics and each topic may have many messages by different users. Old messages (let’s say 10 days old) are deleted automatically by the system. User can post new messages and edit their message” what would the database design be?It is evident with the problem statement that you need to keep track of categories, messages (post) and user. An entity in the system might be a strong or a weak entity. A Weak entity is one which cannot exist on its own in a system and requires a relationship with a strong entity (the one with a primary key) to identify its records. Such an entity is described by a double lined rectangle in the model. What attributes to store? What is a primary key? What is a partial Key? What are candidate keys? What is a super key? How to select them: Simply ask yourself what is the information that the system should store for a particular entity, importantly how that information would be identified at a later time? Is there a way, an attribute which uniquely identifies any record/row/ tuple in the table? What are some of the other important attributes the system has to store?
The answer to these questions would be very important to you in terms of database design. Primarily while resolving the ER model you would be converting all your entities to tables so what are the columns of that table. They are the characteristics of the entity.
e.g. In our scenario what are the important attributes of a User (in this example we only require a loginid and a password but in a slightly complex scenario like a university registration system there might be many attributes like address , phone number , date of birth , previous college , social security etc Similarly what is the data about the categories we want to store (I guess the category name , Description , we would like to know the number of messages in the category and the topic with maximum posts and stuff like that but lets not talk about that here)The set of attributes which can together uniquely identify an entity instance constitute the Super key. The minimum set of attributes (subset of super key) which can uniquely identify a record in the database is called the candidate key. There can be more than one candidate key for the entity e.g. A Student number and the Social security number we use only one as the primary key of the table and the candidate key which we use to uniquely identify any record in the system is called the Primary Key ( The programmer assigned candidate key). Note in case of weak entities the identifying attribute is called the partial key instead of the primary key. 

Step 2 : What are relationships and why? What is cardinality and multiplicity?

 Once you have identified the entities, all of them think of the relationships and the cardinalities or multiplicity of the relationship. For the sake of simplicity check each of the entity against all others in order to clarify the design. Start with the User. How is the user related to Categories? A user can create a new category? If yes then we have a relationship. Then there are 2 things that need to be answered.1.       Can a Single User create more than 1 category2.       Can a Single Category be created by more than one user These two questions are the key to determine the cardinalities which would indicate how we resolve the database design in terms of tables.The relationship can have the following cardinalities: 

·          1 to 1·          1 to Many·          Many to 1·          Many to Many 

Similarly then compare if the user and topic has a relationship. That would be pretty similar to the first one. Now also compare the user with all the other entities.Note that relationship may also have attributes like the date/time when the message was posted etc.Relationships are indicated as diamonds in the design. A relationship with one of the participating entities is weak is called a weak relationship and is indicated with a double lined diamond. Resolving Relationship in the form of physical Database design:It’s a Science not an art J So you can generate the database physical design by pushing a button given the logical design , but the logical design is not a science its an art , thankfully otherwise we would be out of business JJ

 For 1 to 1 and many to many relationships create a new table for the relationship. Include the primary key of each participating entry. The union of all the primary keys which are referenced as foreign keys can uniquely identify any record in the table. So if the cardinality of both the participating entries is the same make a separate table. However if it is not take the primary key of the entity where cardinality is 1 and include it in the table where it is many as a foreign key. This is also called an identifying relationship sometimes. I would talk a bit about the enhanced ER in my next article; hope this would help you in learning the basics of database design.Some good database design tools are Dezign for databases by datanamic, Microsoft Visio, ERWin, Rational Rose and perhaps some other free design tool. If you have any queries, suggestions or feedback I would really appreciate it.This is a sample DB Design for a STUDENT GRADING SYSTEM which is a solved exercise from “Fundamentals of Database Systems” from ELmasri/ Navathe Question 16 and Question 18 Sample ER Model

Here is Question 18 where a new structure is to be designed that holds the previous college info , degrees and courses taken at another college:

Sample ER Model

Note: If I missed an important design concept you were looking for or is there anything you might wanna discuss ? Was this helpful to you ? I would appreciate your comments . Thanks

Advertisements

15 Comments

Filed under Software Design

15 responses to “Database Design Tutorial

  1. Asad Bhai = 2 of Rafi Bhai
    Right now, I’m just scroll down and up. But this post seems to be very interested. I will response detail comment.

  2. Hello Mohammed,

    The general thrust of your post is good, however you make reference to concepts and diagrams without adequate definition and examples. I would really like to see you take perhaps question 16 that you solved and write the post step by step explaining how you solved it. Question, Entities, Relationships, Attributes. In this way your concepts and explanations would have a single example as a thread through the entire post.

    May I suggest a refinement of your relationships types.

    Reference here.

    Mohammend, I think you are really bright and can give many people help through this blog. Keep posting.

    Regards.

  3. Ankur

    very useful

    thanks

  4. Well Asad bhai the matter is more informative and illustrative but asad bhai i just wanted to know that the relationship in this above given ERDits in — Dash form what does it means. I hope u ll answer me rapidly.. thanks

  5. HANUMANTHA

    send me ER diagram and DFD for library management system and college database system..

  6. this is useful for my assignment thx buddy 🙂

  7. nice concepts explanations 😀

  8. im very much interested with this topic and i want to seek help…

  9. How will i solve this problem? Define an ERD for the following narrative. The database should track homes and owners. A home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, a number of bathrooms, and square feet. A home is either owner-occupied or rented. An owner has a social security number, a name, an optional spouse name, a profession, and an optional spouse profession. An owner can possess one or more homes. Each home has only one owner.

  10. Problem 1
    Define an ERD for the following narrative. The database should track homes and owners. A home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, a number of bathrooms, and square feet. A home is either owner-occupied or rented. An owner has a social security number, a name, an optional spouse name, a profession, and an optional spouse profession. An owner can possess one or more homes. Each home has only one owner.
    Problem 2
    Refine the ERD from problem 1 by adding an agent entity type. Agents represent owners in the sale of a home. An agent can list many homes but only one agent can list a home. An agent has a unique agent identifier, a name, an office identifier, and a phone number. When an owner agrees to list a home with an agent, a commission and selling price are determined.
    Probem 3
    In the ERD from problem #2, transform the attribute office identifier into an entity type. Data about an office include the phone number, the manager name, and the address.
    Problem 4
    In the ERD from problem #3, add a buyer entity type. A buyer entity type has a social security number, a name, a phone, preferences for the number of bedrooms and bathrooms, and a price range. An agent can work with many buyers, but a buyer works with only one agent.
    Problem 5
    Refine the ERD from problem #4 with a generalization hierarchy to depict similarities between buyers and owners.
    Probem 6
    Revise the ERD from problem#5 by adding an offer entity type. A buyer makes an offer on a home for a specified sales price. The offer starts on the submission date and time and expires on the specified date and time. A unique offer number identifies an offer. A buyer may submit multiple offers for the same home.

  11. I am a 3rd year College Student

    “I have a lot of problem in here about the ERD…about the Grading System..My question is why is so hard to create a system related to this?…More complicated and complex is that so?…
    I think it is simple but actually absolutely harder that my previous system the payroll system…Plz help me to fix my problem i want a very simple system about the grading system for me to pass the exam…tnx…

    • asadsiddiqi

      Hey there,
      It is just meant to explain some concepts. Its not really a real life scenario or a complex problem. Its just an example for you to explain different relationships 🙂

  12. MUGRENE

    I deeply thank you for your help

  13. Good post however I was wanting to know if you could write a litte more on
    this topic? I’d be very grateful if you could elaborate a little bit more.
    Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s