continuing its systems analysis and design work for Mid-Century Modern Décor Unlimited (MCMDU)
Your consulting team is continuing its systems analysis and design work for Mid-Century Modern Décor Unlimited (MCMDU).
Recall the background information provided on MCMDU and your team’s work to date on the hypothetical consulting project work for this company to date. Also keep in mind what your team has accomplished in your system development work to this point. At this point, your team has designed and implemented an information requirements determination process and has completed tasks related to discovering and diagramming system processes through data flow and use case diagramming. Now it is time to complete an essential first step in structuring system data requirements.
Your team has determined that a relational database system will be used to support the company’s inventory management processes. The database design process involves three stages:
- Conceptual data modeling(detailed in textbook chapter 8)
- Logical data modeling (detailed in textbook chapter 9)
- Physical data design (detailed in textbook chapter 9)
Following the third stage, the physical data design will be implemented in a commercial relational database product. The deliverable from the logical data design could be implemented in any relational database product, so you are not concerned with physical implementation issue for a specific product until you reach the physical data design stage.
This task involves the development of components of a conceptual and logical data models, as described in Chapters 8 and 9, for the business. The work for Tasks 1, 2, and 3 is to be completed in a single Word document with the deliverables of each of the three subtasks clearly labeled as Task 1, Task 2, and Task 3. The work for Task 4 is to be completed in a Microsoft Visio file.
- Task 1 Conceptual Design: brainstorm within your team to build from your careful analysis of the business scenario to propose, in a Microsoft Word document, a list of all potential entities with their associated attributes. An “entity” is a person, place thing, or event about which you collect, store, and retrieve data. Use the structure illustrated in the Attributessection on page 265. For example, a customer entity might be illustrated as CUSTOMER: Customer_ID, Customer_First_Name, Customer_Last_Name, Mailing_Address, Email_Address, Phone_Number (this is just an example, your team may have better and/or more complete ideas on defining this entity!). For your Task 1 listing, do not identify the candidate key(s): the deliverable from this subtask is to list the Entities and their respective Attributes.
- Task 2 Conceptual Design: modify the list built in Task 1 above by underlining one or more attributes within each entity to indicate that they are a candidate key (see the Candidate Keys and Identifiers section on pages 266-267). See Figure 8.7 on page 277 of the course textbook for an example of an entity with a candidate key indicted by underlining: the underlined attribute is a candidate key. Note that you may need to identify and add a candidate key for entities from your Task 1 list that do not have an attribute that would uniquely identify each instance (think “record” or “row” within the database table) of the entity.
- Task 3 Conceptual Design: brainstorm within your team to identify all potential relationships (see pages 267-274 for explanations and examples of relationships) among the entities resulting from your work on Task 2. Here are two examples: (1) one CUSTOMER may have many RESERVATIONS, (2) many RESERVATIONS may be made by many CUSTOMERs (use the format of these examples for your work). State each relationship on a separate line in your Word document.
- Task 4 Logical Design: in Microsoft Visio 2013 or 2016, develop a complete ERD (entity relationship diagram) in Third Normal Form (see pages 320-321) on a single page showing all entities, all primary keys, all foreign keys (see page 321), their attributes, and all necessary relationships among the entities. To get started in Visio 2013, choose the Crow’s Foot database Notation Template. In your ERD, you will be creating entities, their attributes, and designating primary and foreign keys (see page 321 for foreign key discussion). Be certain that the correct cardinality (as displayed in Crow’s Foot notation: see the bottom illustration of Figure 8-5 on textbook page 262 for Crow’s Foot notation and pages 270-271 for a description of cardinality. Hint: an associative entity (see pages 272-274) is necessary in order to form many-to-many relationships among entities (see Figure 8-17 (b) on page 274 for a graphic illustration of an associative entity in use to form a many-to-many relationship).