Relational Database Design with ER diagram - An Example

Relational Database Design with ER diagram - An Example

ABC General Store:

ABC General Store is a medium sized store selling grocery and other general items everyday through only one shop. Now, they need to establish computer based billing system to maintain the flow of goods in their store. Their basic requirement is to store information about the products to maintain an inventory, to store information about their workers, information about their wholesale vendors, etc.

Requirement analysis:

The requirement states that the customer need to do the following;

• To perform inventory management on items they sell
• To maintain the details regarding the vendors (wholesale dealers, agents, or direct selling vendors, etc)
• To handle the workers’ details.

Based on the above minimum requirements, the following Entity Sets are identified. They are defined as follows;

1. Items

Item No. – Unique identification for every item sold.

Item Name – Name of the items.

Item Price – Price of the item per piece. Based on the product, the meaning may vary. For example, tooth brush price is for one piece, rice price is for one kilogram, and so on.

Quantity – Available quantity of any item. Quantity need to be updated whenever an item is sold out or purchased for stock.

Threshold – The reorder level for any item. This is used for deciding when to put a purchase order for particular product. For example, if the threshold value for ‘Tooth Paste’ is 50, it means when our stock level (quantity) reaches 50, we have to immediately place a purchase order to the appropriate vendors.

Remarks – Any specific description about any item.

Expiry Date – Expiry date of any product (if applicable)

2. Vendor

Vendor Id – Unique Identification Number to represent any vendor.

Vendor Name – Name of the agent, or dealers, or direct seller name

Vendor Address – this detail the customer likes to store, door number, street name, city name along with pin code for future usage like placing orders, and customer specified complaints.

Phone – Phone number of the vendors. Here, we can collect any number of phone numbers of the vendor.

Remarks – To save information about specific products we could purchase from one vendor and any other information.

3. Employee

 Employee ID – Identification number of any employee

Name – Name of the employee

Join Date – joining date of the employee

DOB – Date of Birth of the employee.

WAddress – This stores the address of the employee. This address can be stored in a single column. It need not be separated as street name, city name etc.

Section – Particular section (like Grocery, Toys, and Cosmetics etc.) in the shop in which the employee got his job assigned.

Salary – salary of the worker.

Designation – Various designations of the employees. In our case they are Sales Man, Floor Manager, Store Manager, and Purchase Officer.

4. Section

Section Name – It is used to identify any sections uniquely. Various sections of the store are Grocery, Cosmetics, Toys, Sports, Gifts, Foot wears, Home appliances, and Vegetables. The proposed system must be able to include more such sections in the future.

Section Location – There are five floors in the shop. Each floor is divided into four sections like, Floor I Billing Section, Floor I Toys Section I, Floor I Toys Section II, and so on.

Description – To store information regarding what shall be sold in any particular section, and any other specific details regarding the section.

Cardinality Ratios(Type of Relationship):

1. The relationship between Items and Vendor is Many-to-Many. Because, one vendor may supply many products, and more than one vendor may supply one product.
2. The relationship between Items and Section is Many-to-One from Items to Section. Because, every section have many employees assigned, and any employee is assigned to exactly one section.
3. The relationship between Section and Employee is One-To-Many from Section to Employee. In our store, an employee is allotted to exactly one section.

Entity Relationship Diagram

Using the details collected above, we draw an ER diagram which could depict the design in a more clear form as follows;

 Figure 1 - ABC General Store ER diagram

Figure 1 shows the ER diagram for ABC General Store. Here, I have given a simple design, purely for the purpose of explaining the process of ER diagram based design to the new learners.

There are some rules defined, which would help us to convert the ER diagram into set of schemas (structure of tables in the basic level) very easily.

1. Strong Entity sets

We know that, a Strong Entity set is one which has a primary key. In the ER diagram, primary keys are represented with a solid underlined attribute names. In our design, all the entity sets are having primary keys. Hence all are strong entity sets.

Rule says “We can convert strong entity sets into relational design as it is”. That is, all the attributes listed under an entity set can be used as attributes in the target table. As a result, we will get the following relations (tables).

Items (Item_No, Item_Name, Price, Qty, Threshold, Remarks)

Vendors (VID, VName, Address, Phone, Remarks)

Section (SectionName, Section_Loc, Description)

Employee (EID, EName, Desig, JDate, DOB, WAddress, Work_Section, Salary)

At the end of converting the Strong entity sets, we have the above 4 tables as result.

2. Attribute type identification

There are some conversions required based on the type of attributes used in any entity sets. The attributes type based conversion help us to eliminate some amount of duplication (redundancy) at the basic level of design.

In our design, we have following type of attributes;

Simple attributes – all the entity sets have simple attributes.

Composite attributes – entity set Vendors has one composite attribute ‘Address’ (represented as set of attributes linked with one attribute).

Multi-valued attributes- entity set Vendors has one multi-values attribute ‘Phone’ (represented using double ellipse)

While converting ER diagram into tables, we need special attention on attribute types other than simple attributes. For composite attributes, we need to replace the main attribute name with its sub-attributes. In our case, in entity set Vendors, ‘Address’ need to be replaced with door no, street, city and pincode. For multi-valued attributes, the attribute with multiple values might cause complexity in accessing data, or it may end up in redundancy of other related data. Hence, it is suggested to have different table designation for multi-valued attributes. As a result, we have the following relations (tables).

Items (Item_No, Item_Name, Price, Qty, Threshold, Remarks)

Vendors (VID, VName, Door_No, Street, City, Pincode, Remarks)

Vendor_Phone (VID, Phone) – (Note - Phone alone cannot go into a separate table. It must be identified as whose phone. So, the primary key of the base table is included to represent whose phone)

Section (SectionName, Section_Loc, Description)

Employee (EID, EName, Desig, JDate, DOB, WAddress, Work_Section, Salary)

3. Type of relationship sets.

In our design, we have three relationship sets namely, Item_Vendor, It_Sec, and Sec_Wor of type Many-To-Many, Many-To-One, and One-To-Many respectively.

Rule says, “Include the one side entity set’s primary key as many side entity set’s foreign key” to handle One-To-Many or Many-To-One relationships.

Rule says, “Create separate table for Many-To-Many relationship”. Because, handling this type of relationships like One-To-Many or Many-To-One will cause complete redundancy in one of the tables. (This can be understood if you test with Items and Vendors with some sample data). The target table will contain the primary keys of participating entity sets. In our example, the participating entity sets are Items and Vendors on Many-To-Many relationship set.

As a result, we have the following table included in the database as new table.

Item_Vendor (Item_no, VID)

At the end, we have the following tables;

Items (Item_No, Item_Name, Price, Qty, Threshold, Remarks, SectionName) – [due to the One-To-Many relationship from Section to Item, the attribute ‘SectiosName’ is included as foreign key in Items table]

Vendors (VID, VName, Door_No, Street, City, Pincode, Remarks)

Vendor_Phone (VID, Phone)

Item_Vendor (Item_no, VID)

Section (SectionName, Section_Loc, Description)

Employee (EID, EName, Desig, JDate, DOB, WAddress, Work_Section, Salary, SectionName) – [due to the Many-To-One relationship from Employee to Section, the attribute ‘SectionName’ is included as foreign key in Employee table].

Finally, our database contains 6 tables namely, Items, Vendors, Vendor_Phone, Item_Vendor, Section, and Employee.

You may visit ER Diagram Tutorials to know about various components.