In Class Exercise: ER Diagram (Solution – Scenario 1)
Here is the solution to the in-class exercise we did about ER diagrams (this is for Scenario 1: Inventory & Parts).
Notes… when given a business scenario like this, generally you should take the following steps (this is what works for me):
- Read the entire scenario from end to end, before you start drawing anything. Try and understand what the database’s purpose is (e.g. “the company wants to track information about orders” or “the hospital wants to track information about patients and procedures they undergo”).
- Identify the rest of the entities.
- The DB’s ‘purpose’ is usually an immediate hint about some of the entities (in the above two examples, orders, patients and procedures are certainly going to be entities).
- There are usually hints in the text (e.g., “a part is described by a part number, name, etc.” or “the company tracks the following information about orders: order date, contact name, etc.”)! These two statements immediately tell you that part is an entity, and so is order, and each has of them has the indicated attributes.
- Identify the attributes.
- Again, these are explicitly indicated: “… is described by: name, number, etc.” This suggests that name and number are attributes of the entity.
- Make sure every single entity in your diagram has a unique ID (primary key).
- Identify the relationships between entities.
- The text will often hint at these (e.g., “orders have X parts associated with them” or “parts are offered by X suppliers”), though you may have to infer some based on your experience (e.g., orders have parts, parts have suppliers, apartments house residents, cars undergo repairs, etc.).
- Draw the cardinality of those relationships using crows-feet notation.
- Sometimes this will be described in the business scenario (e.g., “a given part can be sold by many suppliers”), other times you will have to make assumptions.
- If the relationship does not seem obvious, write down your assumption for me along with your diagram! If your assumption makes sense and the diagram fits with it, then you will get the marks.
- Deal with any weird attributes that are left over.
- Any attribute that depends on a combination of entities (e.g., “a particular product, supplied by a particular supplier, will have its own supplier product number”), you must tie that attribute to the relationship between them.