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.
The following reports are required:
Project list
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).
Project Assignments list
This is a printed list, distributed to all employees. It lists the project name, the first and last names of project team members, in alphabetical order by last name. Updates are triggered by changes in project team assignments, as well as name changes and personnel turnover. Currently YK maintains this list in a word processing document. This document does not allow for tracking which programmers worked on
which projects. JC would like to be able to retrieve a history of the programmers who worked on specific projects and to retrieve a history of project assignments for individual programmers.
Office Phone lists
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. The list is currently maintained on an Excel spreadsheet. JC wants the listing by last name, but the employees prefer the listing by first name. When JC and YK use the phone list to monitor telephone usage, they need the listing by phone number. YK must be able to select different sorting options.

Project Members Phone Contact List
Employees may work from home up to 15 hours per week. Each project leader needs a list of current means of contacting each member of the project team: home phone and home FAX number. To contact employees "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. Updates are to be triggered by changes in project assignments or in contact numbers, but such updates are to be distributed only to affected project leaders. 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. Follow the steps outlined in class to provide JC with the database.
Your final submission will include:
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:
? Project list
? Project Assignment list
? Project Assignments for employees who have been assigned to more than one project.
Include first and last names, project name, begin and end dates on project. *Make use of a View for this query.
? Office Phone and Email list (sorted by phone number) for the first 15 in the list.
? Project Members Phone Contact list for Far West project only, current members only.
? 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 Canvas.
Case Study