NEED HELP WITH PROJECT
CEIS236 Final Course Project Please read the project scenario and them carryout the instructions for students presented in the following pages. 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 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 GCS managers can keep track of their customers, employees, projects, project schedules, assignments, and invoices. 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), 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 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. · 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 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 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. (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 design. 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 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 Crow’s 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 YOUR NAME. Region table regionID RegionName 1001 Northwest 1002 Southwest 1003 Northeast 1004 Southeast 1005 Central Customer table cusID cusName cusPhone regionID 1 Bellsouth 222-333-4571 1003 2 Comcast 253-444-5555 1003 3 Enron 367-555-6666 1005 4 Exxon 444-777-7777 1004 Employee table empID empLastName empFirstName empHireDate regionID E1 (put your last name here) (put your first name here) 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 Skill table skillID skillDescription skillRate S1 Data Entry I 12 S2 Java I 25 S3 Python I 25 S4 Python II 35 EmpSkill table empID skillID E1 S1 E2 S1 E3 S2 E3 S4 E4 S3 a. Write a query to display average, maximum and minimum skill rate. The result of the query should be: Average Maximum Minimum 24.25 35 12 b. Write a query to display the names of all customers in the region named Northeast. You must use a JOIN. The result of the query should be: cusName BellSouth ComCast c. 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. The result of the query should be: empID E3 E4 HINT: Use the subquery to get a list of skills that pay $25/hour (i.e. WHERE skillRate = 15). Then use main query to find employees whose skill matches one of those in the list. d. 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 created. Use a SELECT command to display the view. The result of the SELECT statement should be: empID empLastName empFirstName SkillID E1 (your last name) (your first name ) S1 E2 Craig Brett S1 E3 Williams Josh S2 E3 Williams Josh S4 E4 Cope Leslie S3 Final Submission You must submit two files: 1. A word document that contains: a) 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. b) 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. Note: Please submit the above two items separately into the dropbox. Final Project Rubric Performance Fail Good Excellent Points Awarded Total Possible Points Points 0 5 10 10 ERD diagram No rule was identified. Rule was developed but contained errors. Logical business rule. Points 0 35 70 70 Database creation and population No database was created. Database and tables are created and populated with errors. Database and tables are created and populated based on the business rule.