Here are the steps you need to go through to do the project:

1. Given the business rules above, create a logical-level Crows Foot ERD using Visio. Show all original entities and any associative entities (intersection or intermediate tables).

2. Based on the ERD, write and execute a SQL script to create a database and populate it with data based on the tables below. Make sure you clearly identify the primary and foreign keys in your SQL code. Use the attributes shown in the tables below. Data must be entered for all fields except for cusPhone, which can have null values.

CEIS236 Final Course Project Oracle DBA, MS SQL Server DBA, Network Engineer I, Network Engineer II, Web Administra- tor, Technical Writer, and Project Manager. Table P5.11a shows an example of the skills Inventory. TABLE P5.11A Please read the project scenario and them carryout the instructions for students presented in the following pages. SKILL Project Scenario Global Computer Solutions (GCS) is an information technology consulting company with many offices throughout the United States. The company's success is based on its ability to maximize its resourcesthat 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 GCS managers can keep track of their customers, employees, projects, project schedules, assign- ments, and invoices. Data Entry! Data Entry II Systems Analyst Systems Analyst 11 DB Designer DB Designer II Java! Java II C++1 C++ 11 Python Python 11 ColdFusion ColdFusion 11 ASPI ASP II Oracle DBA SQL Server DBA Network Engineer Network Engineer II Web Administrator Technical Writer Project Manager EMPLOYEE Seaton Amy: Williams Josh; Underwood Trish Williams Josh: Seaton Amy Craig Brett; Sewell Beth Robbins Erin; Bush Emily; Zebras Steve Chandler Joseph; Burklow Shane; Robbins Erin Yarbrough Peter; Smith Mary Yarbrough Peter; Pascoe Jonathan Kattan Chris; Ephanor Victor: Summers Anna; Ellis Maria Kattan Chris; Ephanor Victor; Batts Melissa Smith Jose; Rogers Adam: Cope Leslie Rogers Adam Bible Hanah Zebras Steve; Ellis Maria Zebras Steve; Newton Christopher Duarte Miriam; Bush Emily Bush Emily: Newton Christopher Duarte Miriam; Bush Emily Duarte Miriam; Newton Christopher Smith Jose; Pascoe Jonathan Yarbrough Peter; Smith Jose Bush Emily; Smith Mary Bush Emily; Smith Mary Bush Emily; Smith Mary; Newton Christopher Kilby Surgena; Bender Larry Paine Brad; Mudd Roger; Kenyon Tiffany; Connor Sean The GCS database must support all of GCS's operations and information requirements. A basic description of the main entities follows: The employees of GCS must have an employee ID, a last name, a middle initial, a first name, a region, and a date of hire recorded in the system. Valid regions are as follows: Northwest (NW), Southwest (SW), Midwest North (MN), Mid- West South (MS), Northeast (NE), and Southeast (SE). Each employee has many skills, and many employees have the same skill. Each skill has a skill ID, 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, Java I, Java II, C++ 1, C++ II, Python I, Python II, ColdFusion I, ColdFusion II, ASP I, ASP II, GCS has many customers. Each customer has a customer ID, name, phone number, and re- gion. GCS works by projects. A project is based on a contract between the customer and GCS to de- sign, develop, and implement a computerized solution. Each project has specific character- istics such as the project ID, the customer to which the project belongs, a brief description, a project date (the date the contract was signed), an estimated project start date and end date, an estimated project budget, an actual start date, an actual end date, an actual cost, and one employee assigned as the manager of the project. TABLE P5.11B PROJECT ID: 1 DESCRIPTION: SALES MANAGEMENT SYSTEM COMPANY: SEE ROCKS CONTRACT DATE: 2/12/2018 REGION: NW START DATE: 3/1/2018 END DATE: 7/1/2018 BUDGET: $15,500 START DATE END DATE TASK DESCRIPTION SKILL(S) REQUIRED QUANTITY REQUIRED 3/1/18 3/6/18 Initial interview Project Manager 1 Systems Analyst II DB Designer 1 3/11/18 3/15/18 Database design DB Designer 1 4/12/18 System design Systems Analyst 11 1 Systems Analyst 3/18/18 3/22/18 Database implementation Oracle DBA 1 3/25/18 5/20/18 System coding and testing 2 Java II 3/11/18 The actual cost of the project is updated each Friday by adding that week's cost to the actual cost. The week's cost is computed by multiplying the hours each employee worked by the rate of pay for that skill. Java! Oracle DBA 1 3/25/18 6/10/18 6/7/18 6/14/18 1 The employee who is the manager of the project must complete a project schedule, which effectively is a design and development plan. In the project schedule (or plan), the man- ager must determine the tasks that will be performed to take the project from beginning to end. Each task has a task ID, a brief task description, starting and ending dates, the types of skills needed, and the number of employees (with the required skills) needed to complete the task. General tasks are the initial interview, database and system design, implementa- tion, coding, testing, and final evaluation and sign-off. For example, GCS might have the project schedule shown in Table P5.11b. System documentation Technical Writer Final evaluation Project Manager Systems Analyst II DB Designer Java II On-site system online and data loading Project Manager Systems Analyst II DB Designer Java II Sign-off Project Manager 6/17/18 6/21/18 7/1/18 7/1/18 1 GCS pools all of its employees by region; from this pool, employees are assigned to a specific task scheduled by the project manager. For example, in the first project's schedule, you know that a Systems Analyst II, Database Designer I, and Project Manager are needed for the period from 3/1/18 to 3/6/18. The project manager is assigned when the project is cre- ated and remains for the duration of the project. Using that information, GCS searches the employees who are located in the same region as the customer, matches the skills re- quired, and assigns the employees to the project task. Each project schedule task can have many employees assigned to it, and a given employee can work on multiple project tasks. However, an employee can work on only one project task at a time. For example, if an employee is already assigned to work on a project task from 2/20/18 to 3/3/18, the employee cannot work on another task until the current as- signment is closed (ends). The date that an assignment is closed does not necessarily match the ending date of the project schedule task because a task can be completed ahead of or behind schedule. . Given all of the preceding information, you can see that the assignment associates an em- ployee with a project task, using the project schedule. Therefore, to keep track of the as- signment, you require at least the following information: assignment ID, employee, project schedule task, assignment start date, and assignment end date. The end date could be any date, as some projects run ahead of or behind schedule. Table P5.11c shows a sample as- signment form. TABLE P5.11C PROJECT ID: 1 DESCRIPTION: SALES MANAGEMENT SYSTEM COMPANY: SEE ROCKS CONTRACT DATE: 2/12/2018 AS OF: 03/29/18 SCHEDULED ACTUAL ASSIGNMENTS PROJECT TASK START DATE END DATE SKILL EMPLOYEE START DATE END DATE Initial interview 3/1/18 3/6/18 Project Mgr. 101-Connors 3/1/18 3/6/18 Sys. Analyst 11 102-Burklows. 3/1/18 3/6/18 DB Designer 103-Smith M 3/1/18 3/6/18 Database design 3/11/18 3/15/18 DB Designer ! 104-Smith M. 3/11/18 3/14/18 System design 3/11/18 4/12/18 Sys. Analyst 11 105-Burklows. 3/11/18 Sys. Analyst 106-Bush E. 3/11/18 Sys. Analyst 107-Zebras S. 3/11/18 Database 3/18/18 3/22/18 Oracle DBA 108-Smith). 3/15/18 3/19/18 implementation System coding and 3/25/18 5/20/18 Javal 109-Summers A. 3/21/18 testing Java! 110-Ellis M. 3/21/18 Java II 111-Ephanor V. 3/21/18 Oracle DBA 112-Smith). 3/21/18 System 3/25/18 6/7/18 Tech. Writer 113-Kilby s. 3/25/18 documentation Final evaluation 6/10/18 6/14/18 Project Mgr. Sys. Analyst 11 DB Designer Java II On-site system 6/17/18 6/21/18 Project Mgr. online and data Sys. Analyst II loading DB Designer Java II Sign-off 7/1/18 7/1/18 Project Mgr. (Note: The assignment number is shown as a prefix of the employee name-for example, 101 or 102.) Assume that the assignments shown previously are the only ones as of the date of this de- sign. The assignment number can be any number that matches your database design. 1. Employee work hours are kept in a work log, which contains a record of the actual hours worked by employees on a given assignment. The work log is a form that the employee fills BILL NUMBER XOK out at the end of each week (Friday) or at the end of each month. The form contains the date, which is either the current Friday of the month or the last workday of the month if it does not fall on a Friday. The form also contains the assignment ID, the total hours worked either that week or up to the end of the month, and the bill number to which the work-log entry is charged. Obviously, each worklog entry can be related to only one bill. A sample list of the current work-log entries for the first sample project is shown in Table P5.11d. xox xxx XOXOX XOOX XOOX Xxx xox XOOX xox Xox TABLE P5.11D EMPLOYEE NAME WEEK ENDING ASSIGNMENT NUMBER HOURS WORKED Burklow S. 3/1/18 1-102 4 Connor S. 3/1/18 1-101 4 Smith M. 3/1/18 1-103 4 Burklows 3/8/18 1-102 24 Connor S. 3/8/18 1-101 24 Smith M. 3/8/18 1-103 24 Burklows 3/15/18 1-105 40 Bush E 3/15/18 1-106 40 Smith J. 3/15/18 1-108 6 Smith M. 3/15/18 1-104 32 Zebras S. 3/15/18 1-107 35 Burklow S. 3/22/18 1-105 40 Bush E 3/22/18 1-106 40 Ellis M. 3/22/18 1-110 12 Ephanor V. 3/22/18 1-111 12 Smith J. 3/22/18 1-108 12 Smith). 3/22/18 1-112 12 Summers A 3/22/18 1-109 12 Zebras S. 3/22/18 1-107 35 Burklow S. 3/29/18 1-105 40 Bush E. 3/29/18 1-106 40 Ellis M 3/29/18 1-110 35 Ephanor V. 3/29/18 1-111 35 Kilbys 3/29/18 1-113 40 Smith). 3/29/18 1-112 35 Summers A 3/29/18 1-109 35 Zebras S. 3/29/18 1-107 35 Note: xxx represents the bill ID. Use the one that matches the bill number in your database. 2. Finally, every 15 days, a bill is written and sent to the customer for the total hours worked on the project during that period. When GCS generates a bill, it uses the bill number to up- date the work-log entries that are part of the bill. In summary, a bill can refer to many work-log entries, and each work-log entry can be related to only one bill. GCS sent one bill on 3/15/18 for the first project (SEE ROCKS), totaling the hours worked between 3/1/18 and 3/15/18. Therefore, you can safely assume that there is only one bill in this table and that the bill covers the work-log entries shown in the preceding form. Instructions for Students You will create a database that stores information about ONLY the following entities. Customer Region Employee Skill The business rules below give the relationships between these entities. A customer is assigned to one region. One region can have several customers. An employee can have several skills. One skill can be learned by several employees. An employee works for one region. A region can have many employees. CEIS236 Final Course Project Oracle DBA, MS SQL Server DBA, Network Engineer I, Network Engineer II, Web Administra- tor, Technical Writer, and Project Manager. Table P5.11a shows an example of the skills Inventory. TABLE P5.11A Please read the project scenario and them carryout the instructions for students presented in the following pages. SKILL Project Scenario Global Computer Solutions (GCS) is an information technology consulting company with many offices throughout the United States. The company's success is based on its ability to maximize its resourcesthat 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 GCS managers can keep track of their customers, employees, projects, project schedules, assign- ments, and invoices. Data Entry! Data Entry II Systems Analyst Systems Analyst 11 DB Designer DB Designer II Java! Java II C++1 C++ 11 Python Python 11 ColdFusion ColdFusion 11 ASPI ASP II Oracle DBA SQL Server DBA Network Engineer Network Engineer II Web Administrator Technical Writer Project Manager EMPLOYEE Seaton Amy: Williams Josh; Underwood Trish Williams Josh: Seaton Amy Craig Brett; Sewell Beth Robbins Erin; Bush Emily; Zebras Steve Chandler Joseph; Burklow Shane; Robbins Erin Yarbrough Peter; Smith Mary Yarbrough Peter; Pascoe Jonathan Kattan Chris; Ephanor Victor: Summers Anna; Ellis Maria Kattan Chris; Ephanor Victor; Batts Melissa Smith Jose; Rogers Adam: Cope Leslie Rogers Adam Bible Hanah Zebras Steve; Ellis Maria Zebras Steve; Newton Christopher Duarte Miriam; Bush Emily Bush Emily: Newton Christopher Duarte Miriam; Bush Emily Duarte Miriam; Newton Christopher Smith Jose; Pascoe Jonathan Yarbrough Peter; Smith Jose Bush Emily; Smith Mary Bush Emily; Smith Mary Bush Emily; Smith Mary; Newton Christopher Kilby Surgena; Bender Larry Paine Brad; Mudd Roger; Kenyon Tiffany; Connor Sean The GCS database must support all of GCS's operations and information requirements. A basic description of the main entities follows: The employees of GCS must have an employee ID, a last name, a middle initial, a first name, a region, and a date of hire recorded in the system. Valid regions are as follows: Northwest (NW), Southwest (SW), Midwest North (MN), Mid- West South (MS), Northeast (NE), and Southeast (SE). Each employee has many skills, and many employees have the same skill. Each skill has a skill ID, 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, Java I, Java II, C++ 1, C++ II, Python I, Python II, ColdFusion I, ColdFusion II, ASP I, ASP II, GCS has many customers. Each customer has a customer ID, name, phone number, and re- gion. GCS works by projects. A project is based on a contract between the customer and GCS to de- sign, develop, and implement a computerized solution. Each project has specific character- istics such as the project ID, the customer to which the project belongs, a brief description, a project date (the date the contract was signed), an estimated project start date and end date, an estimated project budget, an actual start date, an actual end date, an actual cost, and one employee assigned as the manager of the project. TABLE P5.11B PROJECT ID: 1 DESCRIPTION: SALES MANAGEMENT SYSTEM COMPANY: SEE ROCKS CONTRACT DATE: 2/12/2018 REGION: NW START DATE: 3/1/2018 END DATE: 7/1/2018 BUDGET: $15,500 START DATE END DATE TASK DESCRIPTION SKILL(S) REQUIRED QUANTITY REQUIRED 3/1/18 3/6/18 Initial interview Project Manager 1 Systems Analyst II DB Designer 1 3/11/18 3/15/18 Database design DB Designer 1 4/12/18 System design Systems Analyst 11 1 Systems Analyst 3/18/18 3/22/18 Database implementation Oracle DBA 1 3/25/18 5/20/18 System coding and testing 2 Java II 3/11/18 The actual cost of the project is updated each Friday by adding that week's cost to the actual cost. The week's cost is computed by multiplying the hours each employee worked by the rate of pay for that skill. Java! Oracle DBA 1 3/25/18 6/10/18 6/7/18 6/14/18 1 The employee who is the manager of the project must complete a project schedule, which effectively is a design and development plan. In the project schedule (or plan), the man- ager must determine the tasks that will be performed to take the project from beginning to end. Each task has a task ID, a brief task description, starting and ending dates, the types of skills needed, and the number of employees (with the required skills) needed to complete the task. General tasks are the initial interview, database and system design, implementa- tion, coding, testing, and final evaluation and sign-off. For example, GCS might have the project schedule shown in Table P5.11b. System documentation Technical Writer Final evaluation Project Manager Systems Analyst II DB Designer Java II On-site system online and data loading Project Manager Systems Analyst II DB Designer Java II Sign-off Project Manager 6/17/18 6/21/18 7/1/18 7/1/18 1 GCS pools all of its employees by region; from this pool, employees are assigned to a specific task scheduled by the project manager. For example, in the first project's schedule, you know that a Systems Analyst II, Database Designer I, and Project Manager are needed for the period from 3/1/18 to 3/6/18. The project manager is assigned when the project is cre- ated and remains for the duration of the project. Using that information, GCS searches the employees who are located in the same region as the customer, matches the skills re- quired, and assigns the employees to the project task. Each project schedule task can have many employees assigned to it, and a given employee can work on multiple project tasks. However, an employee can work on only one project task at a time. For example, if an employee is already assigned to work on a project task from 2/20/18 to 3/3/18, the employee cannot work on another task until the current as- signment is closed (ends). The date that an assignment is closed does not necessarily match the ending date of the project schedule task because a task can be completed ahead of or behind schedule. . Given all of the preceding information, you can see that the assignment associates an em- ployee with a project task, using the project schedule. Therefore, to keep track of the as- signment, you require at least the following information: assignment ID, employee, project schedule task, assignment start date, and assignment end date. The end date could be any date, as some projects run ahead of or behind schedule. Table P5.11c shows a sample as- signment form. TABLE P5.11C PROJECT ID: 1 DESCRIPTION: SALES MANAGEMENT SYSTEM COMPANY: SEE ROCKS CONTRACT DATE: 2/12/2018 AS OF: 03/29/18 SCHEDULED ACTUAL ASSIGNMENTS PROJECT TASK START DATE END DATE SKILL EMPLOYEE START DATE END DATE Initial interview 3/1/18 3/6/18 Project Mgr. 101-Connors 3/1/18 3/6/18 Sys. Analyst 11 102-Burklows. 3/1/18 3/6/18 DB Designer 103-Smith M 3/1/18 3/6/18 Database design 3/11/18 3/15/18 DB Designer ! 104-Smith M. 3/11/18 3/14/18 System design 3/11/18 4/12/18 Sys. Analyst 11 105-Burklows. 3/11/18 Sys. Analyst 106-Bush E. 3/11/18 Sys. Analyst 107-Zebras S. 3/11/18 Database 3/18/18 3/22/18 Oracle DBA 108-Smith). 3/15/18 3/19/18 implementation System coding and 3/25/18 5/20/18 Javal 109-Summers A. 3/21/18 testing Java! 110-Ellis M. 3/21/18 Java II 111-Ephanor V. 3/21/18 Oracle DBA 112-Smith). 3/21/18 System 3/25/18 6/7/18 Tech. Writer 113-Kilby s. 3/25/18 documentation Final evaluation 6/10/18 6/14/18 Project Mgr. Sys. Analyst 11 DB Designer Java II On-site system 6/17/18 6/21/18 Project Mgr. online and data Sys. Analyst II loading DB Designer Java II Sign-off 7/1/18 7/1/18 Project Mgr. (Note: The assignment number is shown as a prefix of the employee name-for example, 101 or 102.) Assume that the assignments shown previously are the only ones as of the date of this de- sign. The assignment number can be any number that matches your database design. 1. Employee work hours are kept in a work log, which contains a record of the actual hours worked by employees on a given assignment. The work log is a form that the employee fills BILL NUMBER XOK out at the end of each week (Friday) or at the end of each month. The form contains the date, which is either the current Friday of the month or the last workday of the month if it does not fall on a Friday. The form also contains the assignment ID, the total hours worked either that week or up to the end of the month, and the bill number to which the work-log entry is charged. Obviously, each worklog entry can be related to only one bill. A sample list of the current work-log entries for the first sample project is shown in Table P5.11d. xox xxx XOXOX XOOX XOOX Xxx xox XOOX xox Xox TABLE P5.11D EMPLOYEE NAME WEEK ENDING ASSIGNMENT NUMBER HOURS WORKED Burklow S. 3/1/18 1-102 4 Connor S. 3/1/18 1-101 4 Smith M. 3/1/18 1-103 4 Burklows 3/8/18 1-102 24 Connor S. 3/8/18 1-101 24 Smith M. 3/8/18 1-103 24 Burklows 3/15/18 1-105 40 Bush E 3/15/18 1-106 40 Smith J. 3/15/18 1-108 6 Smith M. 3/15/18 1-104 32 Zebras S. 3/15/18 1-107 35 Burklow S. 3/22/18 1-105 40 Bush E 3/22/18 1-106 40 Ellis M. 3/22/18 1-110 12 Ephanor V. 3/22/18 1-111 12 Smith J. 3/22/18 1-108 12 Smith). 3/22/18 1-112 12 Summers A 3/22/18 1-109 12 Zebras S. 3/22/18 1-107 35 Burklow S. 3/29/18 1-105 40 Bush E. 3/29/18 1-106 40 Ellis M 3/29/18 1-110 35 Ephanor V. 3/29/18 1-111 35 Kilbys 3/29/18 1-113 40 Smith). 3/29/18 1-112 35 Summers A 3/29/18 1-109 35 Zebras S. 3/29/18 1-107 35 Note: xxx represents the bill ID. Use the one that matches the bill number in your database. 2. Finally, every 15 days, a bill is written and sent to the customer for the total hours worked on the project during that period. When GCS generates a bill, it uses the bill number to up- date the work-log entries that are part of the bill. In summary, a bill can refer to many work-log entries, and each work-log entry can be related to only one bill. GCS sent one bill on 3/15/18 for the first project (SEE ROCKS), totaling the hours worked between 3/1/18 and 3/15/18. Therefore, you can safely assume that there is only one bill in this table and that the bill covers the work-log entries shown in the preceding form. Instructions for Students You will create a database that stores information about ONLY the following entities. Customer Region Employee Skill The business rules below give the relationships between these entities. A customer is assigned to one region. One region can have several customers. An employee can have several skills. One skill can be learned by several employees. An employee works for one region. A region can have many employees