CIT-180 Semester Project Before you start the project For this project you will be developing a Microsoft SQL Server database for the following fictitious case study. The case study describes the information needs of an organization, as well as the ways in which the data are currently stored and retrieved. Your database will replace the current system. The JC_Data.xlsx Excel spreadsheet (note there are two tabs) represents the current system being used as well as all the historical data. Deliverable Checklist ? Database Diagram (.png or .pdf) ? Database Create Script (.sql) ? Five screenshots (queries + results) as .png within a Word file. ? Five SQL query scripts (.sql) Project Case Study 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 firms 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 companys 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 projects color (used to flag the projects 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 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. 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 persons email address. 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. 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. Your final submission will include: 1. Your normalized (3 rd 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 Canvas. Case Study COPYRIGHT @2013 Kate Pulling

CIT-180 Semester Project 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. Before you start the project... For this project you will be developing a Microsoft SQL Server database for the following fictitious case study. The case study describes the information needs of an organization, as well as the ways in which the data are currently stored and retrieved. 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. A master list of all contact numbers for all employees should be available for reference by JC and YK Your database wil replace the current system. The JC_Data xlsx Excel spreadsheet (note there are two tabs) represents the current system being used as well as all the historical data. Deliverable Checklist Database Diagram (png or pdf) Database Create Script (.sql) Five screenshots (queries results) as png within a Word file. Five SQL query scripts(sql) ? 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 ag labels. Project Case Study Your final submission will include: Your normalized (3d 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 1. 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 bya 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 2. The generated SL 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. 2. 3. 4. 5. Project list Project Assignment list 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 Names and addresses for mailing labels for emplovees living in North Las Vegas with 'street or 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). 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. Project Assignments List 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. CIT-180 Semester Project 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. Before you start the project... For this project you will be developing a Microsoft SQL Server database for the following fictitious case study. The case study describes the information needs of an organization, as well as the ways in which the data are currently stored and retrieved. 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. A master list of all contact numbers for all employees should be available for reference by JC and YK Your database wil replace the current system. The JC_Data xlsx Excel spreadsheet (note there are two tabs) represents the current system being used as well as all the historical data. Deliverable Checklist Database Diagram (png or pdf) Database Create Script (.sql) Five screenshots (queries results) as png within a Word file. Five SQL query scripts(sql) ? 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 ag labels. Project Case Study Your final submission will include: Your normalized (3d 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 1. 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 bya 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 2. The generated SL 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. 2. 3. 4. 5. Project list Project Assignment list 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 Names and addresses for mailing labels for emplovees living in North Las Vegas with 'street or 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). 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. Project Assignments List 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