The GCS database must support all of GCSs 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), Midwest 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++ I, C++ II, Python I, Python 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 P5.11a shows an example of the Skills Inventory.
• GCS has many customers. Each customer has a customer ID, name, phone number, and region.
• 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 (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.
• The actual cost of the project is updated each Friday by adding that weeks cost to the actual cost. The weeks cost is computed by multiplying the hours each employee worked by the rate of pay for that skill.
• 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 manager 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, implementation, coding, testing, and final evaluation and sign-off. For example, GCS might have the project schedule shown in Table P5.11b.
• 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 projects 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 created 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 required, 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 assignment 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 employee with a project task, using the project schedule. Therefore, to keep track of the assignment, 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 assignment form.

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 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.
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 update 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.

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.

After creating the tables, use SELECT queries to display all of them.

NOTE: The first employee names must be Charlene

empID	empLastName	empFirstName	empHireDate	regionID
E1	White	Charlene	2019-2-7	1004
E2	Craig	Brett	2019-3-30	1004
E3	Williams	Josh	1999-3-17	1005
E4	Cope	Leslie	2017-4-21	1002
E5	Mudd	Roger	2007-10-18	1001

Write a query to display average, maximum and minimum skill rate.

1. 2. Write a query to display the names of all customers in the region named Northeast. You must use a JOIN.
2. 3. The result of the SELECT statement should be:Write a query to display employee ID of employees who have skills with that pay more than $15 per hour. You must use a subquery.
3. 4. Write a query to create view that contains employee id, employee last name, employee first name and skill ID for each employee. After the view is create Use a SELECT command to display the view.

empID	empLastName	empFirstName	SkillID
E1	White	Charlene	S1
E2	Craig	Brett	S1
E3	Williams	Josh	S2
E3	Williams	Josh	S4
E4	Cope	Leslie	S3

Final Submission

1. A word document that contains:
   1. The script of used for creating and populating the database with data. Also include a screen captures SELECT queries that display all data in each field.
   2. Coding for the four SQL queries above. For each query, please also submit screen prints of the results showing that each query worked.
2. A Visio file that has your ERD