JC Information Consultants: Project Management and Employee Information System

Project Management and Staffing

JC Information Consultants is a software development firm employing about 35 computer professionals. Each programmer is assigned to a programming project, but may be assigned to only one project at a time. Programmers may work on many projects over a period of time. Each project is headed by a project manager. JC Brotherson, the founder and president, oversees all current projects and actively markets the firm’s specialties to local businesses and government entities. There are two technical writers and two software librarians. There is one administrative assistant (YK Proffitt) who handles all
personnel paperwork (like keeping track of who is working on which project at any time) as well as issues which involve the organization of the company’s working space. The information system will assist YK.

This is a printed reference sheet used by JC and YK. It includes the project name, the project’s color (used to flag the project’s paperwork), the beginning date of the project, the projected end of the project, the number of remaining days for the project (relative to the current date).

This is a report to for employees who have been assigned to more than one project. The query lists the project name, the first and last names of project team members, their project begin and end dates as well as their position (role) on the project. ***A view must be used for this query.

This printed list includes last name, first name, and 4?digit phone number for each employee. Updates are triggered by turnover of personnel, name changes, and office relocation (phone numbers are tied to offices). JC would like this list expanded to list each person’s email address.

Employees may work from home up to 15 hours per week. Each project leader needs a list of currentmeans of contacting each member of the project team: home phone and home FAX number. To contactemployees “on the road” the project leaders also need to know cell phone numbers. Currently it is the responsibility of the project leader to obtain this information from the team members and to keep it current. JC wishes the responsibility for this information to be given to YK. A master list of all contact numbers for all employees should be available for reference by JC and YK. Mailing Labels: Occasionally, YK sends correspondence to employees’ homes through the Postal Service. Presently, addresses are maintained in a text file which “feeds” the mail?merge feature of a word processor. YK would like to have mailing labels as part of the new information system. You can make your own assumptions on what fields are required for the employee mailing labels.

1. Your normalized (3rd normal form) database design, illustrated using www.draw.io or MS Visio. This must indicate the primary keys, foreign keys, and indexed columns. Additionally, the diagram must represent the relationship types.

2. The generated SQL for all database objects using the Export Scripts option. See the Generating Export Scripts page within Canvas.

3. Screenshots of the SQL queries and result sets that provide the data for:

1. Project list

2. Project Assignment list

3. Office Phone and Email list (sorted by phone number) for the first 15 in the list.

4. Project Members Phone Contact list for Far West project only.

5. Names and addresses for mailing labels for employees living in North Las Vegas with ‘street’ or road’ in the address. Additionally, include each of the queries as a .sql file as part of your deliverable.

Note: these queries are dependent on the Excel data being successfully imported into the database. See the Importing Data from Excel page within Ca