This project involves creating a fully functioning, fully realized database from scratch. For this project, you will document the full lifecycle of database realization from business situation to requirements to design to implementation.
Business Situation for Project
The business situation for the project is that of Global Computer Solutions (GCS), an information technology consulting company with many of?ces 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, Cobol I, Cobol II, C++ I, C++ II, VB I, VB 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.10a shows an example of the Skills Inventory.
TABLE P5.10a
| SKIIL |
EMPLOYEE |
|
Data Entry I
|
Seaton Amy; Williams Josh; Underwood Trish
|
| Data Entry II |
Williams Josh; Seaton Amy |
|
Systems Analyst I
|
Craig Brett; Sewell Beth; Robbins Erin; Bush Emily; Zebras Steve
|
|
Systems Analyst II
|
Chandler Joseph; Burklow Shane; Robbins Erin
|
|
DB Designer I
|
Yarbrough Peter; Smith Mary
|
|
DB Designer II
|
Yarbrough Peter; Pascoe Jonathan
|
|
Cobol I
|
Kattan Chris; Ephanor Victor; Summers Anna; Ellis Maria
|
|
Cobol II
|
Kattan Chris; Ephanor Victor; Batts Melissa
|
|
C++ I
|
Smith Jose; Rogers Adam; Cope Leslie
|
|
C++ II
|
Rogers Adam; Bible Hanah
|
|
VB I
|
Zebras Steve; Ellis Maria
|
|
VB II
|
Zebras Steve; Newton Christopher
|
|
ColdFusion I
|
Duarte Miriam; Bush Emily
|
|
ColdFusion II
|
Bush Emily; Newton Christopher
|
|
ASP I
|
Duarte Miriam; Bush Emily
|
|
ASP II
|
Duarte Miriam; Newton Christopher
|
|
Oracle DBA
|
Smith Jose- Pascoe Jonathan
|
|
SQL Server DBA
|
Yarbrough Peter; Smith Jose
|
|
Network Engineer I
|
Bush Emily; Smith Mary
|
|
Network Engineer II
|
Bush Emily; Smith Mary
|
|
Web Administrator
|
Bush Emily; Smith Mary; Newton Christopher
|
|
Technical Writer
|
Kilby Surgena; Bender Larry
|
|
Project Manager
|
Paine Brad; Mudd Roger; Kenyon Tiffany; Connor Sean
|
- 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.10b.
TABLE P5.10b
|
PROJECT ID: 1 COMPANY SEE ROCKS START DATE: 3/1/2014 START END DATE
DATE
|
DESCRIPTION: SALES MANAGEMENT SYSTEM
CONTRACT DATE: 2/12/2014 REGION: NW
END DATE: 7/1/2014 BUDGET: $15,500
TASK DESCRIPTION SKILL(S) REQUIRED QUANTITY
REQUIRED
|
|
3/1/14
|
3/6/14
|
Initial interview
|
Project Manager
|
1
|
|
|
|
|
Systems Analyst 11
|
1
|
|
|
|
|
DB Designer!
|
1
|
|
3/11/14
|
3/15/14
|
Database design
|
DB Designer I
|
I
|
|
3/11/14
|
4/12/14
|
System design
|
Systems Analyst II
|
1
|
|
|
|
|
Systems Analyst I
|
2
|
|
3/18/14
|
3/22/14
|
Database implementation
|
Oracle DM
|
I
|
|
325/14
|
5/20/14
|
System coding and testing
|
Cobol I
|
2
|
|
|
|
|
Cobol II
|
1
|
|
|
|
|
Oracle DBA
|
I
|
|
3/25/14
|
617/14
|
System documentation
|
Technical Writer
|
I
|
|
6/10/14
|
6/14/14
|
Final evaluation
|
Project Manager
|
1
|
|
|
|
|
Systems Analyst II
|
1
|
|
|
|
|
DB Designer I
|
1
|
|
|
|
|
Cobol II
|
1
|
|
6/17/14
|
6/21/14
|
On-site system online and data loading
|
Project Manager
|
1
|
|
|
|
|
Systems Analyst II
|
1
|
|
|
|
|
DB Designer I
|
1
|
|
|
|
|
Cobol II
|
1
|
|
7/1/14
|
7/1/14
|
Sign-off
|
Project Manager
|
1
|
- GCS pools all 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/14 to 3/6/14. 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/14 to 3/3/14, 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 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.10c shows a sample assignment form.
TABLE P5.10c
|
PROJECT ID: 1 COMPANY: SEE ROCKS
PROJECT TASK Initial interview
|
SCHEDULED START
DATE
3/1/14
|
DESCRIPTION: CONTRACT
DATE:
END DATE
3/6/14
|
SALES MANAGEMENT
2/12/2014 SKILL
Project Mgr. Sys. Analyst II DB Designer I
|
SYSTEM
ACTUAL
EMPLOYEE
101 -Connor S. 102-Burklow S. 103-Smith M.
|
AS OF: 03/29/14 ASSIGNMENTS START DATE
3/1/14
3/1/14
3/1/14
|
END DATE
|
|
3/6/14
3/6/14
3/6/14
|
|
Database design
|
3/11/14
|
3/15/14
|
DB Designer I
|
104-Smith M.
|
3/11/14
|
3/14/14
|
|
System design
|
3/11/14
|
4/12/14
|
Sys. Analyst II
|
105-Budclow S.
|
3/11/14
|
|
|
|
|
|
Sys. Analyst I
|
106-Bush E.
|
3/11/14
|
|
|
|
|
|
Sys. Analyst I
|
107-Zebras S.
|
3/11/14
|
|
|
Database implementation
|
3/18/14
|
3/22/14
|
Oracle DBA
|
108-Smith J.
|
3/15/14
|
3/19/14
|
|
System coding and
|
3/25/14
|
5/20/14
|
Cobol!
|
109-Summers A.
|
3/21/14
|
|
|
testing
|
|
|
Cobol I
|
110-Ellis M.
|
3/21/14
|
|
|
|
|
|
Cobol II
|
111-Ephanor V.
|
3/21/14
|
|
|
|
|
|
Oracle DBA
|
112-Smith J.
|
3/21/14
|
|
|
System documentation
|
3/25/14
|
6/7/14
|
Tech. Writer
|
113-Kilby S.
|
3/25/14
|
|
|
Final evaluation
|
W10/14
|
6/14/14
|
Project Mgr.
|
|
|
|
|
|
|
|
Sys. Analyst II
|
|
|
|
|
|
|
|
DB Designer I
|
|
|
|
|
|
|
|
Cobol II
|
|
|
|
|
On-site system online and data loading
|
6/17/14
|
6/21/14
|
Project Mgr. Sys. Analyst II DB Designer I
|
|
|
|
|
|
|
|
Cobol II
|
|
|
|
|
Sign-off
|
7/1/14
|
7/1/14
|
Project Mgr.
|
|
|
|
(Note: The assignment number is shown as a pre?x 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 work-log 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.10d.
TABLE P5.10d
|
EMPLOYEE NAME
|
WEEK ENDING
|
ASSIGNMENT NUMBER
|
HOURS WORKED
|
BILL NUMBER
|
|
Burklow S.
|
3/1/14
|
1-102
|
4
|
xxx
|
|
Connor S.
|
3/1/14
|
1-101
|
4
|
xxx
|
|
Smith M.
|
3/1/14
|
1-103
|
4
|
xxx
|
|
Burklow S.
|
3/8/14
|
1-102
|
24
|
xxx
|
|
Connor S.
|
3/8/14
|
1-101
|
24
|
xxx
|
|
Smith M.
|
3/8/14
|
1-103
|
24
|
xxx
|
|
Burklow S.
|
3/15/14
|
1-105
|
40
|
xxx
|
|
Bush E.
|
3/15/14
|
1-106
|
40
|
xxx
|
|
Smith J.
|
3/15/14
|
1-108
|
6
|
xxx
|
|
Smith M.
|
3/15/14
|
1-104
|
32
|
xxx
|
|
Zebras S.
|
3/15/14
|
1-107
|
35
|
xxx
|
|
Burklow S.
|
3/22/14
|
1-105
|
40
|
|
|
Bush E.
|
3/22/14
|
1-106
|
40
|
|
|
Ellis M.
|
3/22/14
|
1-110
|
12
|
|
|
Ephanor V.
|
3/22/14
|
1-111
|
12
|
|
|
Smith J.
|
3/22/14
|
1-108
|
12
|
|
|
Smith J.
|
3/22/14
|
1-112
|
12
|
|
|
Summers A.
|
3/22/14
|
1-109
|
12
|
|
|
Zebras S.
|
3/22/14
|
1-107
|
35
|
|
|
Burklow S.
|
3/29/14
|
1-105
|
40
|
|
|
Bush E.
|
3/29/14
|
1-106
|
40
|
|
|
Ellis M.
|
3/29/14
|
1-110
|
35
|
|
|
Ephanor V.
|
3/29/14
|
1-111
|
35
|
|
|
Kilby S.
|
3/29/14
|
1-113
|
40
|
|
|
Smith J.
|
3/29/14
|
1-112
|
35
|
|
|
Summers A.
|
3/29/14
|
1-109
|
35
|
|
|
Zebras S.
|
3/29/14
|
1-107
|
35
|
|
- 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/14 for the first project (SEE ROCKS), totaling the hours worked between 3/1/14 and 3/15/14. 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.
Project Requirements
Complete the following activities:
- Construct an ER model for this business situation.
- Create a relational model from your ER model. All keys should be clearly identified.
- Demonstrate that your database is in at least 3rd Normal Form.
- Write the DDL to create the tables for your design.
- Implement this model in MS Access.
- Add records to each table sufficient to demonstrate your interfaces and reports.
- Your application should allow the user to generate a report that shows the progress on a project similar to Table P5.10c of the description.
- Your application should allow the user to generate at least 4 other reports needed by GCS management. Each of these reports should use at least two of the tables in your database.
Submission Requirements
There are two submission requirements:
- Project Report -- The project report (in MS Word) must minimally consist of:
1. A 1-page description of the application suitable for users/managers to understand
2. An ER model
3. A Relational model
4. A brief (paragraph) description of the normalization process (i.e. - why is this relational model in the normal form you claim)
5. DDL that implements the design
6. A description of the dataentered into the tables and why it is adequate for demonstrating the sufficiency of the design
Please name this report: "Development Project Report.docx"
- Database - The actual database in MS Access that includes the populated tables and working reports and queries should be submitted, with the file named "GCS Database"