SADM 7/ed CTTS CASE STUDY - Milestone 4: Solution Page: 4-1Prepared by Gary B. Randolph forSystems Analysis & Design Methods 7edby J. L. Whitten, L. D. Bentley, & K.C. Dittman Copyright Irwin/McGraw-Hill 2007MILESTONE 4 DATA MODELING Activity 1 Entity/Definition Matrixhe entities in the following matrix were identified by analyzing the forms provided in Milestone 4. This list and the following diagrams are based on the authors assumptions and interpretations. If you, the instructor, choose to do your own solution, it may be different, depending on your interpretation of the forms provided. In most activities explanations for why things were done as they were have been provided to aid in your understanding of the solution.The following tables contain the attributes identified from each form and the interview.PC Configuration Sheet & Interview:Machine NameComponentDate InstalledDate RemovedQuantityComponent TypeSoftware Configuration Sheet & Interview:Client (i.e. Family Vacation Rentals)Machine Name (i.e. SQL Server)Type of Information (i.e. Logon)Multiple Values of Information (i.e. Username: sa / Password: gumbll)TSADM 7/ed CTTS CASE STUDY - Milestone 4: Solution Page: 4-2Prepared by Gary B. Randolph forSystems Analysis & Design Methods 7edby J. L. Whitten, L. D. Bentley, & K.C. Dittman Copyright Irwin/McGraw-Hill 2007Students should be able to identify at least the major entities listed below. Whether or not they identify the optional entities will depend on how they interpret the user requirements and on their experience level with database normalization. The following completed matrix is one possible solution.Entity/Definition MatrixENTITY BUSINESS DEFINITIONMajor EntitiesClient A business entity for whom work is done.Equipment A computer, printer, or other piece of technology equipment that is in use.EquipmentComponent A component (anything from a stick of RAM to an entire turn-key system) that is or has been installed in Equipment.Inventory A component that has been checked into inventoryConfiguration A piece of information concerning software configuration for the Client and possibly for a piece of equipment.SeviceRequest A request for service on Equipment.WorkRecord Work done to resolve a SeviceRequest.Optional EntitiesEquipType A classification of Equipment.ComponentType A classification of EquipmentComponent.SADM 7/ed CTTS CASE STUDY - Milestone 4: Solution Page: 4-3Prepared by Gary B. Randolph forSystems Analysis & Design Methods 7edby J. L. Whitten, L. D. Bentley, & K.C. Dittman Copyright Irwin/McGraw-Hill 2007 Activity 2 Context Data Modelhis model should be constructed based on the entities identified in Activity 1. All of the cardinalities of the major entities can be determined from the interview or the forms. The following diagram is based on the following assumptions and/or business rules: A piece of Equipment is made up of one or more EquipmentComponents that either have in the past or are now installed. Each piece of Equipment is a particular EquipmentType. Each piece of Equipment is owned by a particular Client. Each EquipmentComponent is a particular ComponentType. Each Configuration is associated with a particular Client. Some, but not all, of the configuration records could be associated with a piece of Equipment. Because this does not apply to all records, a formal relationship would not have to be established. A Client submits one or more ServiceRequests, which may related to a piece of Equipment. Each ServiceRequest will be worked on with zero or more WorkRecords (initially zero but eventually at least one). TSADM 7/ed CTTS CASE STUDY - Milestone 4: Solution Page: 4-4Prepared by Gary B. Randolph forSystems Analysis & Design Methods 7edby J. L. Whitten, L. D. Bentley, & K.C. Dittman Copyright Irwin/McGraw-Hill 2007 Activity 3 Key-Based Data Modelhis model is constructed by adding the primary keys to the model in Activity 2. The primary keys are based on how the user uniquely identifies each entity. Make sure students specify primary keys that uniquely identify the entities as well as use proper names for the keys. The following diagram illustrates one possible solution. This diagram was created in Microsoft Visio. Primary keys are shown at the top of the list of attributes and marked with PK. Foreign keys are marked with FK and a number. The Equipment entity has EquipNum as the primary key. In the present system, the computer name acts as a primary identifier. This could be the primary key. However, Jeff Summers states that the computer name is sometimes changed. Primary keys cannot be changed in some systems, and it is never good practice. An EquipNum is a number that could be generated by the system and stays unchanged forever. As a number it would also consume less disk space as a foreign key in related tables and would provide for faster joins. The EquipmentComponent entity has ComponentNum as the primary key. This would also be