Homework answers / question archive / Assignment 5a Assignment 5a provides experience with the Crow’s Foot notation with an emphasis on precise usage and practice with a drawing tool such as the ER Assistant or Visual Paradigm

Assignment 5a Assignment 5a provides experience with the Crow’s Foot notation with an emphasis on precise usage and practice with a drawing tool such as the ER Assistant or Visual Paradigm

Computer Science

Share With

Assignment 5a Assignment 5a provides experience with the Crow’s Foot notation with an emphasis on precise usage and practice with a drawing tool such as the ER Assistant or Visual Paradigm. I recommend using the ER Assistant for this assignment because some problems involve M-N relationships and other tools do not support M-N relationships. If you use Visual Paradigm, you can use the equivalent representation for a M-N relationship. Assignment 5a uses pass/fail grading. If you complete all problems, you will receive full credit. See the grading rubric after the problem details for grading details. Copy and paste each ERD into a Word document. Label the problems in your Word document so that the grader can easily match your diagrams to the specified problems. 1. For each consistency error in Figure 1, identify the consistency rule violated and suggest possible resolutions of the error. The ERD has generic names to focus on finding diagram errors rather than the meaning of the diagram. Note that you can create the ERD in ER Assistant and use the check diagram feature to determine diagram errors. I also encourage you to determine the diagram errors by yourself before using the check diagram feature. This problem is based on problem 20 in Chapter 5. 9/20/2021 Assignment 5a Page 2 Entity3 Attribute3-1 Attribute3-2 Attribute3-3 Rel1 Entity2 Entity1 Attribute1-1 Attribute1-2 Attribute1-3 Attribute1-4 Rel2 Attribute2-1 Attribute2-1 Attribute2-3 Attribute1-1 Attribute4-1 Rel5 Rel4 Entity6 Rel3 Entity5 Attribute5-1 Attribute5-2 Attribute5-3 Attribute5-4 Attribute6-1 Attribute6-2 Attribute6-3 Attribute6-4 Rel7 Entity4 Attribute4-1 Attribute4-2 Attribute4-3 Attribute4-4 Rel6 Rel6 Entity7 Attribute7-1 Attribute7-2 Figure 1: ERD for Problem 1 2. Draw an ERD containing the Lab, LabVisit, and Patient entity types connected by 1-M relationships from Lab to LabVisit and Patient to LabVisit. Choose appropriate relationship names using your common knowledge of interactions between labs, lab visits, and patients. Define minimum cardinalities so that a lab is required for a lab visit and a patient is required for a lab visit. For the Lab entity type, add attributes LabNo (primary key), LabName, LabStreet, LabCity, LabState, and LabZip. For the Patient entity type, add attributes PatNo (primary key), PatLastName, PatFirstName, PatDOB (date of birth). For the LabVisit entity type, add attributes for the LVNo (primary key), LVDate, LVProvNo, and optional LVOrdNo (for orders from physicians). If you are using a data modeling tool that supports data type specification, choose appropriate data types for the attributes based on your common knowledge. This problem is based on problem 31 in Chapter 5. 9/20/2021 Assignment 5a Page 3 3. Augment your ERD from problem 2 with the Specimen entity type. For each specimen collected, the database should record a unique SpecNo, SpecArea (vaginal, cervical, or endocervical), and SpecCollMethod (thin prep or sure path). A 1-M relationship from LabVisit to Specimen. A lab visit must produce at least one specimen. A specimen is associated with exactly one lab visit. This problem is based on problem 32 in Chapter 5. 4. Augment your ERD from problem 3 with the TestOrder entity type and a relationship between TestOrder and Specimen. Multiple test orders can be created for a specimen, but a specimen does not have a test order until a delay, from hours to days. A test order is created for exactly one specimen. A test order contains a TONo (primary key), TOTestName, TOTestType (HPV, CT/GC, CT, or GC), and TOTestResult (positive, negative, equivocal, or failure). If a test order produces a failure, the specimen is given a new test order and tested again until a non-failure result is obtained. This problem is based on problem 33 in Chapter 5. 5. Augment your ERD from problem with the Supply entity type and a relationship between TestOrder and Supply. A test can use a collection of supplies (0 or more) and a supply can be used on a collection of tests (0 or more). The Supply entity type contains SuppNo (primary key), SuppName, SuppLotNo, and SuppQOH. This problem is based on problem 34 in Chapter 5. Grading Rubric To receive full credit, your solution should contain the following elements. You do not need to provide correct solutions to receive full credit. You only need to demonstrate a complete effort on each problem. For problems 2 to 5, paste an ERD for each problem, not one ERD with the solution for all problems. 9/20/2021 • Assignment 5a Page 4 Problem 1: identification of consistency rule violations and at least one possible resolution for each consistency rule violation. You need to identify at least 2 consistency rule violations for full credit. • Problem 2: ERD with three entity types and two relationships • Problem 3: ERD with an additional entity type and relationship • Problem 4: ERD with an additional entity type and relationship • Problem 5: ERD with an additional entity type and relationship