CEIS236 Final Course Project
Please read the project scenario and them ca
yout 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
ief 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
ief 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 cu
ent 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 cu
ent 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 cu
ent 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.
· Custome
· 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
XXXXXXXXXX
1003
2
Comcast
XXXXXXXXXX
1003
3
Enron
XXXXXXXXXX
1005
4
Exxon
XXXXXXXXXX
1004
Employee table
empID
empLastName
empFirstName
empHireDate
regionID
E1
(put your last name here)
(put your first name here)
XXXXXXXXXX
1004
E2
Craig
Brett
XXXXXXXXXX
1004
E3
Williams
Josh
XXXXXXXXXX
1005
E4
Cope
Leslie
XXXXXXXXXX
1002
E5
Mudd
Roge
XXXXXXXXXX
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
. 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.
) 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 Ru
ic
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 e
ors.
Logical business rule.
Points
0
35
70
70
Database creation and population
No database was created.
Database and tables are created and populated with e
ors.
Database and tables are created and populated based on the business rule.