Description: The goal is develop a database for the Wedgewood Pacific Corporation using MS SQL Server. 1. In MS SQL Server, create a new database called WPC. 2. Create the DEPARTMENT and EMPLOYEE tables shown below along with the PROJECT and ASSIGNMENT tables shown on the next page, and set the relationships between the tables explained below: DEPARTMENT Column Name DepartmentName BudgetCode OfficeNumber Phone EMPLOYEE Column Name EmployeeNumber FirstName LastName Department Assignment #4: SQL Statements Phone Email Type nvarchar(35) nvarchar(30) nvarchar(15) No Key Primary Key Type bigint: Identity nvarchar(12) No No nvarchar(35) Key Primary Key nvarchar(25) No nvarchar(25) No Foreign Key nvarchar(12) nvarchar(100) No No Required Yes Yes Yes Yes Required Yes Yes Yes Yes No Yes Remarks Remarks Surrogate Key A. To set a column to be a Primary Key, right-click on the column name (while in the design mode) and select Set Primary Key. B. To set a column as a Foreign Key, right-click on the column name (while in the design mode) and select Relationship. Click the Add button and then click on "Tables and Columns Specification" and then click on the button next to it (with three dots) and complete the relationship by selecting the Primary Key table and under that pick its column name, and match that with the Foreign Key column name in the current table. C. The Department column in the EMPLOYEE and PROJECT tables are both a Foreign Key of the DepartmentName column from the DEPARTMENT table. ProjectID in the ASSIGNMENT table is a Foreign Key of ProjectID in the PROJECT table, and EmployeeNumber in the ASSIGNMENT table is a Foreign Key of EmployeeNumber in the EMPLOYEE table. Notice that ProjectID and EmployeeNumber are also Primary Keys in the ASSIGNMENT table. D. The EmployeeNumber column in the EMPLOYEE table is an Identity column, which means its value will be automatically assigned (AutoNumber). To set it as an Identity column, click on the column name, then go into the properties window below it and scroll to "Identity Specification" and open that property line and click on “(Is Identity)” and then click on the down arrow on the right side and select "Yes". 3. Now create the PROJECT and ASSIGNMENT tables as shown here. Note the Foreign Key relationships in these tables and set them up as explained above. PROJECT Column Name ProjectID Name Department MaxHours StartDate EndDate ASSIGNMENT Column Name ProjectID EmployeeNumber HoursWorked Administration Legal Accounting Finance Production Marketing InfoSystems [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] [AutoNumber] Type DEPARTMENT Data DepartmentName BudgetCode bigint nvarchar(50) nvarchar(35) float datetime datetime Type bigint bigint BC-100-10 BC-200-10 Human Resources BC-500-10 float BC-300-10 Tom BC-400-10 Mary BC-600-10 Tom BC-700-10 BC-800-10 Ken Rick Rosalie Richard Heather Mary George George 4. Populate the DEPARTMENT and EMPLOYEE tables with the same exact data provided here: James Jacobs Jones Smith Key Primary Key Jones No Foreign Key Nestor No Jackson Brown No No Key Primary Key, Foreign Key Primary Key, Foreign Key OfficeNumber No BLDG01-300 Jackson BLDG01-200 EMPLOYEE Data EmployeeNumber FirstName LastName Department Administration BLDG01-100 Numoto BLDG01-140 BLDG01-180 BLDG02-100 BLDG02-200 BLDG02-270 Abernathy Bandalone Legal Caruthers Accounting Accounting Required Yes Finance Yes Yes Production Yes Production No No Required Yes Yes Administration No Phone Marketing InfoSystems InfoSystems Remarks Long Integer Double Remarks Long Integer Long Integer Do 360-285-8100 360-285-8200 Phone 360-285-8300 360-285-8400 360-285-8500 360-287-8600 360-287-8700 360-287-8800 360-285-8110 MJacobs@WPC.com 360-285-8120 RJackson@WPC.com 360-285-8210 360-285-8310 Email Human Resources 360-285-8510 GSmith@WPC.com RBandalone@WPC.com 360-285-8320 HJones@WPC.com TCaruthers@WPC.com 360-285-8410 MAbernathy@WPC.com 360-287-8620 360-287-8710 360-287-8610 TJackson@WPC.com GJones@WPC.com KNumoto@WPC.com JNestor@WPC.com 360-287-8820 RBrown@WPC.com 5. Populate the PROJECT and ASSIGNMENT tables with the following data: PROJECT Data ProjectID Name 1000 1100 1200 1300 1400 1000 1000 1000 ASSIGNMENT Data ProjectID 1100 1100 1100 1200 1200 1200 1300 1300 1300 1400 1400 2008 Q3 Product Plan 1400 2008 Q3 Portfolio Analysis 2008 Q3 Tax Preparation 2008 Q4 Product Plan 2008 Q4 Portfolio Analysis EmployeeNumber 1 8 10 4 6 1 4 5 1 8 10 4 5 6 Department MaxHours Marketing Finance Accounting Marketing Finance HoursWorked 30.0 75.0 55.0 40.0 45.0 25.0 20.0 45.0 40.0 35.0 80.0 50.0 15.0 10.0 27.5 135 120 145 150 140 StartDate 05/10/08 07/05/08 08/10/08 08/10/08 10/05/08 EndDate 06/15/08 07/25/08 10/25/08 09/15/08 6. Develop SQL queries (in one script) to answer the following questions A through L: A. What projects are in the PROJECT table? Show all information for each project. B. What are the ProjectID, Name, StartDate, and EndDate values of projects in the PROJECT table? C. What projects in the PROJECT table started before August 1, 2008? Show all the information for each project. D. What projects in the PROJECT table have not been completed? Show all the information for each project. E. Who are the employees assigned to each project? Show ProjectID, Employee-Number, LastName, FirstName, and Phone. F. Who are the employees assigned to each project? Show the ProjectID, Name, and Department. Show EmployeeNumber, LastName, FirstName, and Phone. G. Who are the employees assigned to each project? Show ProjectID, Name, Department, and Department Phone. Show Employee Number, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order. H. Who are the employees assigned to projects run by the marketing department? Show ProjectID, Name, Department, and Department Phone. Show Employee Number, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order. I. How many projects are being run by the marketing department? Be sure to assign an appropriate column name to the computed results. J. What is the total MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results. K. What is the average MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results. L. How many projects are being run by each department? Be sure to display each DepartmentName and to assign an appropriate column name to the computed results. Note: Place a comment block at the top of your script file: /* /* Name: /* Class: /* Term: /* Assgn #: /*****. CS3410 Spring 2021 4 *** / * 1 ·1 * .sql". For example, John Smith's script file would be named “A4-Smith.sql”. Submit .sql file in dropbox area of D2L labled Assignment 4 Submission.

Solved: Description: The goal is develop a database for the Wedgewood Pacific

Home
questions and answers
description the goal is develop a database for the wedgewood pacific c

Question

Description: The goal is develop a database for the Wedgewood Pacific Corporation using MS SQL Server. 1. In MS SQL Server, create a new database called WPC. 2. Create the DEPARTMENT and EMPLOYEE tables shown below along with the PROJECT and ASSIGNMENT tables shown on the next page, and set the relationships between t