MIS582 Global Computer Solutions Course Project

Course Project

College of Engineering and Information Sciences

Course Number: MIS582

Course Project

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.

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

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

This is a complex database design case that requires the identification of many business rules, the organization of those business rules, and the development of a complete database model. Note that this database design case has three primary objectives:
• Evaluation of primary keys and surrogate keys. (When should each one be used?)
• Evaluation of the use of indexes on candidate keys to avoid duplicate entries when using surrogate keys.
• Evaluation of the use of redundant relationships. In some cases, it is better to have the foreign key attribute added to an entity, instead of using multiple join operations.

Here’s the required submissions of your course project:

o Given the business scenario, create a Crow’s Foot ERD that meet the above requirements. Please use the following information as a guide for your implementation.
o

Table
Name
Primary key Unique, Not Null Index
(on candidate key)
Explanation
Customer cus_id (surrogate) unique(cus_name) The unique index on cus_name is used to ensure no duplicate customers exist.
Region region_id (surrogate) unique(region_name) The unique index on region_name is used to ensure that no duplicate regions are entered.
Employee emp_id (surrogate) unique(emp_lname, emp_fname, emp_mi) The unique index on emp_lname, emp_fname and emp_mi is used to ensure that no duplicate employees are entered.
Skill skill_id (surrogate) unique(skill_description) The unique index on skill_description is used to ensure that no duplicate skills are entered.
EmpSkill emp_id, skill_id
(composite) The composite primary key ensures that no duplicate skills are entered for each employee.
Project prj_id (surrogate) unique(cus_id, prj_description) The unique index on cus_id and prj_description is used to ensure that no duplicate project entries exist for a given customer.
Task (project schedule) task_id (surrogate) unique(prj_id, task_descript) The unique index on prj_id and task_descript is used to ensure that no duplicate task is given for the same project.
TS
(task schedule) ts_id(surrogate) unique(task_id, skill_id) The unique index on task_id and skill_id is to prevent duplicate listings for a single skill within a single task for a single project.
Assign asn_id (surrogate) unique (ps_id, emp_id, ts_id) The unique index on ps_id, emp_id, and ts_id is used to ensure that an employee cannot be assigned twice to perform the same skill on the same task for a given project.
Worklog wl_id (surrogate) unique(asn_id, wl_date)
The unique indexes on asn_id and wl_date are used to ensure that no duplicate work log entries exist (for an employee) on a given date.
Bill bill_id (surrogate)

o Based on the ERD, create a database that fulfills the operations described in this problem. Your assignment is to create a database that will fulfill the operations described in this problem. The minimum required entities are employee, skill, customer, region, project, project schedule, assignment, work log, and bill. (There are additional required entities that are not listed.)

• Create all of the required tables and all of the required relationships.
• Create the required indexes to maintain entity integrity when using surrogate primary keys.
• Populate the tables as needed, as indicated in the sample data and forms.
• Feel free to use the following tips.

o Write a query to practice aggregation functions.
o Write a query to practice joins.
o Write a query to practice subqueries.
o Write a query to practice view creation.

Final Submission:

To submit your project, two entries are required.

• A word document that contains all the table creation scripts and screen print of the ERD diagram. For the four queries, besides scripts, please also submit screen prints of the results.
• A zipped folder that contains ERD diagram and your SQL file.

Note: Please submit the above two items separately into the dropbox.
MIS582 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.
Points 0 2 5 5
Query with aggregation functions No query was submitted. Query was submitted but contain errors. Query was submitted based on the requirement.
Points 0 2 5 5
Query with aggregation functions No query was submitted. Query was submitted but contain errors. Query was submitted based on the requirement.
Points 0 2 5 5
Query with joins No query was submitted. Query was submitted but contain errors. Query was submitted based on the requirement.
Points 0 2 5 5
Query with view No query was submitted. Query was submitted but contain errors. Query was submitted based on the requirement.
Total 100