Toothpaste Company – ER Modeling

by Saurabh Gupta

As part of project for course in Advanced Database Modeling in Masters in Science (IT) from Lindner College of Business, University of Cincinnati under Prof. Umanath

Revisiting

Course required us to develop an industry driven case on database modeling. The idea was to design and implement business model of any fictitious organization. The project offered first-hand experience developing concepts like ER modeling, physical database design, representation of business using conceptual model etc.

Story

We developed a fictitious story. The story involved gathering requirements and then developing a database model for a FMCG company (fast moving consumer goods). In a gist, the company planned to integrate its sales, marketing, research and manufacturing departments under one single system. Building a single database was one of the challenges towards this goal.

The proposed database should perform/handle following tasks clearly –

  1. Allow for sales team to see the inventory numbers for various products in warehouses. Also, allow them to place orders directly. Both these processes will be constrained by specific business rules
  2. Every department has access to and ability to propose changes to information about a product. For example, a Research department can create a new product, manufacturing department manufactures a product, and sales department can order particular product from appropriate manufacturing unit
  3. Ability to clearly distinguish between departments based on their product category and geographical reach. Thus avoiding any confusion when business unit wishes to place an order with manufacturing units
  4. Handle product branding process as defined below –
    1. R&D of a particular ‘product category’ center comes up with new product
    2. Assign the brand to which this product will belong
    3. Management decides on the plant the product would be manufactured
  5. Business units (as per demand calculation or anticipation) place regular orders with different production units for their product category. Orders contain information like product category, number of units, expected order completion date, actual order completion date (optional) etc.

Rest of the discussion on business rules can be found here (https://saurabhguptawrites.files.wordpress.com/2012/07/story-toothpaste-company.docx)

Design Challenges

High level challenges –

  1. Reduce redundant data in existing system by defining functional dependencies within business attributes
  2. Build an efficient transactional system. Faster database updates, deletes and inserts
  3. On the other hand, the system should be efficient towards retrieval and instantaneous reporting purposes
  4. Ensure all existing business rules are implemented either by design or semantic integrity constraint.
  5. Ensure that we do not lose any business rule in steps towards transitioning from conceptual model to actual physical model

Low level

  1. Ensure integrity of Product relation which is accessed and maintained by all departments
  2. Ensure extreme level of transactional ability in Product_Orders relation
  3. Ensure that a plant can be ordered to manufacture a product only if it handles its product category
  4. Ensure that the product order can only be placed in a plant which manufactures that product

Design Approach

Perform complete analysis of existing systems, business rules and requirements.

  1. Develop conceptual model representing existing entity relationships (for e.g. how each department is related to single legal entity? How manufacturing plants are managed by management units?)
  2. Identify entity level and attribute level business rules
  3. Identify and list business rules related to entity deletion (for e.g. what happens when a production unit is shut off or a product is discontinued)
  4. Identify semantic integrity constraint for all entities and their attributes in database

Develop a coarse granular model and information preserving schema –

  1. Incorporate as much information identified above into a more detailed ER model, called “coarse granular model”
    1. Move all business rules related to entity deletion on to the model using notations like N – update all foreign key column to null when parent entity is deleted, C – delete all rows of entities whose parent is deleted, or R – to not allow parent to be deleted if there exist a child entity
    2. Identify composite relations and decompose it into simple entities and relationships (for e.g. relationship between R&D center and Product Category was broken down to two 1:n relationships and one bridge/intersect entity called “R&D_Category” entity)
    3. Carry over list of business rules (entity and attribute level) which couldn’t be represented in the model still (for e.g. following business rule “consolidated group id should be assigned to all the Business units. This identifier is used to group all BU as per their tier level and province” couldn’t be represented in model yet)
  2. Information preserving schema helps identify many semantic integrity constraints. For example – following schema represents entity called Plant which is assigned to a management unit with identifier MU_Name and MU_Number. It also represents that each management unit should have at least 3 plants and no more than 10 plants within its control –
  3. Ensure that model has no normal form violations. At high level, this was identified by ensuring all functional dependencies are effectively mapped into the model and no functional dependency overlap within same entity. If it did, the functional dependency violation was resolved by decomposing the entity further.
  4. Business unit was identified by business unit number and city it belonged to. Also, each business unit is assigned a consolidation group ID which identifies a business unit by city and tier-level. Therefore, in following relation part of primary key (city) was identified by a non-primary key (consolidated group id)

The violation was resolved by following kind of decomposition

Implementation

Develop final information preserving schema, design specify layer and remaining semantic integrity constraints. This along with Backmann’s diagram were used to generate database scripts in using DDL – (CREATE, ALTER and DROP)

Models Designed

Presentation layer –

Presentation Layer Toothpaste Company

Design Specific Layer –

Design Specific ER Model

Backmann’s Diagram

Project Document(s)

Advertisements