QuestionQuestion

Transcribed TextTranscribed Text

Advanced ER Diagrams Review the scenario and business rules provided and draw a Crow’s foot ERD. Instructions for the ERDs: 1. List the entities AND attributes. 2. Show the relationships, connectivity, AND cardinality. Be sure to label all relationships 3. Convert all M:N relationships using composite entities. 4. Identify the primary key(s) in each entity. (underline with solid line or notate with PK indicator). 5. Place foreign keys as necessary and notate the foreign key (underline with dotted line or notate with FK indicator). 6. Include all other extended ER modeling notations as necessary. 7. All attributes should be single valued attributes. 8. Use proper naming conventions for entities and attributes. 9. Copy and paste your ERD into a Word or PDF document. Save the document and upload using the assignment link provided in eLearning. Global Computer Solutions (GCS) is an information technology consulting company with many offices located throughout the United States. The company’s success is based on its ability to maximize its resources—that is, its ability to match highly skilled employees with projects according to region. To better manage its projects, GCS has contacted you to design a database so that GCS managers can keep track of their customers, employees, projects, and assignments. Create a complete Crow’s Foot ERD for these requirements:  The employees working for GCS have an employee ID, an employee last name, a first name, a region, and a date of hire.  Each employee has one or more skills, and many employees have the same skill. GSC only keeps track of skills that employees have.  Each skill has a description and rate of pay. Valid skills are as follows: data entry I, data entry II, systems analyst I, systems analyst II, database designer I, database designer II, Cobol I, Cobol II, C++ I, C++ II, VB I, VB II, ColdFusion I, ColdFusion II, ASP I, ASP II, Oracle DBA, MS SQL Server DBA, network engineer I, network engineer II, web administrator, technical writer, and project manager. Table 1 shows an example of the Skills Inventory.  GCS has many customers. Each customer has a customer ID, customer name, phone number, and region. Every region has at least one customer.  GCS works by projects. A project is based on a contract between the customer and GCS to design, develop, and implement a computerized solution. Each project has specific characteristics such as the project ID, the customer to which the project belongs, a brief description, a project date (that is, the date on which the project’s contract was signed), a project start date (an estimate), a project end date (also an estimate), a project budget (total estimated cost of project), an actual start date, an actual end date, an actual cost, and one employee assigned as manager of the project. A customer can own many projects, but a project is owned by one customer. All projects must have a single project manager assigned (An employee is assigned to a project).  Different projects have different characteristics. GCS currently works on government projects, healthcare projects, projects for non-profit organizations as well as for profit organizations. Government projects have two unique characteristics – government office and government resource number. Healthcare projects have two unique characteristics – healthcare type and doctor sponsor name. Non-profit projects have a tax-id number. A project will only be one type.  All projects are broken down into many tasks after the project start date. GCS pools all of its employees by region, and from this pool, employees are assigned to one or more tasks. Each task has a description a begin date and an end date. Each task also requires one or more skills. For example, for project ABC for the period 3/1/10 to 3/6/10, a Systems Analyst II, a Database Designer I, and a Project Manager are needed. Using that information, GCS searches the employees who are located in the same region as the customer, matching the skills required and assigning them to the project task.  Each project task can have many employees assigned to it and an employee can work on up to 4 project tasks at a time. Each employee records the duration of time spent on each task. Not every employee is assigned to a task. Your assignment is to create a database that will fulfill the operations described in this problem. The minimum required entities are employee, skill, customer, project, task. (There are additional required entities that are not listed.) Create all of the required tables and all of the required relationships. Table 1. Skills Inventory Skill Employee Data Entry I Seaton Amy; Williams Josh; Underwood Trish Data Entry II Williams Josh; Seaton Amy Systems Analyst I Craig Brett; Sewell Beth; Robbins Erin; Bush Emily; Zebras Steve Systems Analyst II Chandler Joseph; Burklow Shane; Robbins Erin DB Designer I Yarbrough Peter; Smith Mary DB Designer II Yarbrough Peter; Pascoe Jonathan Cobol I Kattan Chris; Epahnor Victor; Summers Anna; Ellis Maria Cobol II Kattan Chris; Epahnor Victor, Batts Melissa C++ I Smith Jose; Rogers Adam; Cope Leslie C++ II Rogers Adam; Bible Hanah VB I Zebras Steve; Ellis Maria VB II Zebras Steve; Newton Christopher ColdFusion I Duarte Miriam; Bush Emily ColdFusion II Bush Emily; Newton Christopher ASP I Duarte Miriam; Bush Emily ASP II Duarte Miriam; Newton Christopher Oracle DBA Smith Jose; Pascoe Jonathan SQL Server DBA Yarbrough Peter; Smith Jose Network Engineer I Bush Emily; Smith Mary Network Engineer II Bush Emily; Smith Mary Web Administrator Bush Emily; Smith Mary; Newton Christopher Technical Writer Kilby Surgena; Bender Larry Project Manager Paine Brad; Mudd Roger; Kenyon Tiffany; Connor Sean Table 2 Project Assignment Form Project ID: 1 Description: Sales Management System Company: See Rocks Contract Date: 2/12/2010 As of: 03/29/10 SCHEDULED ACTUAL ASSIGNMENTS Project Task Start Date End Date Skill Employee Initial Interview 3/1/10 3/6/10 Project Mgr. Sys. Analyst II DB Designer I 101—Connor S. 102—Burklow S. 103—Smith M. Database Design 3/11/10 3/15/10 DB Designer I 104—Smith M. System Design 3/11/10 4/12/10 Sys. Analyst II Sys. Analyst I Sys. Analyst I 105—Burklow S. 106—Bush E. 107—Zebras S. Database Implementation 3/18/10 3/22/10 Oracle DBA 108—Smith J. System Coding & Testing 3/25/10 5/20/10 Cobol I Cobol I Cobol II Oracle DBA 109—Summers A. 110—Ellis M. 111—Ephanor V. 112—Smith J. System Documentation 3/25/10 6/7/10 Tech. Writer 113—Kilby S. Final Evaluation 6/10/10 6/14/10 Project Mgr. Sys. Analyst II DB Designer I Cobol II On-Site System Online and Data Loading 6/17/10 6/21/10 Project Mgr. Sys. Analyst II DB Designer I Cobol II Sign-Off 7/1/10 7/1/10 Project Mgr.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Advanced ER Diagrams
    $60.00 for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Database Development Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats