Tedext

Sun Consulting company - Assigning consultants to the project

HW 7

Complete the following exercises:
Download the file SunConsultingData.xlsx on your computer

-        Observe the data given in the spreadsheet SunConsultingData.xlsx

-        Notice that the spreadsheet contains the data about the following scenario

o   Sun Consulting is an IS consulting firm

o   It operates in the following three regions: East, West, and Mid-Continent

o   It provides expertise in five areas of IS:

§  DBA - Database Administration 

§  DWH - Data Warehousing           

§  CRM - Customer Relationship Management      

§  SCM - Supply Chain Management          

§  ERP - Enterprise Resource Planning       

o   It employs a number of consultants, each of which is assigned to one region

o   Each consultant is skilled in at least one of the expertise areas, but can be skilled in more than one

o   Sun Consulting has contracts for various clients in various regions.

§  A client can have multiple current contracts with Sun Consulting

§  Each contract between a client and Sun Consulting requires at least one area of expertise (but can require up to three).

§  Sun Consulting assigns at least one consultant to the contract, but can assign up to three.

§  When assigning consultants to the project, the company makes sure those consultants are based in the same region as the client and posses expertise needed for the client’s project.

-        Your task is to convert this inefficient MS Excel spreadsheet into an MS Access application, by following the steps listed:

o   Step 1: Create an ER diagram (already created – see next page)

o   Step 2: Convert it to a relational schema (already created – see next page)

o   Step 3: Implement the relational schema as an empty MS Access database file (i.e. collection of tables and the relationships between them).  Name the MS Access database file SunConsulting.accdb

o   Step 4:  Populate the MS Access database with the data given in the spreadsheet

o   Step 5: Create a form that will enable entry into the consultant table and name it ConsultantForm

o   Step 6:  Using the newly created form, add an additional consultant which is placed in any of the three regions and which has your first and last name. 

o   Step 7:  Make sure that the additional consultant has at least one expertise skills (of your choosing) and is assigned to at least one contract (of your choosing).  You can enter this data directly into the appropriate tables.

o   Step 8: Create a query named ConsultantSkillsQuery which will list for each consultant: first name, last name, and the descriptions of all of his/hers expert skills.

o   Step 9: Create a report named RegionReport which will show current contract for each region.  More precisely, the report will show information about each region (region id and region name) and the information about all the current contracts (contract id, amount, and date) in each particular region.

o   Submit file SunConsulting.accdb

ER Diagram

IsIn
IsIn
IsFor
Has
AssignedTo
Requires
EDescription
Expertise Code
Date
Amount
ContractId
Expertise
Contract
ClLname
ClFname
ClientID
Client
CoLname
CoFname
ConsultantID
RegionName
RegionID
Region
Consultant

Relational Schema

Sun Consulting spreadsheet

Sun Consulting company - Assigning consultants to the project

  • Order

  • Payment

  • Processing

  • Delivery

Validation error occured. Please enter the fields and submit it again.
Thank You ! Your email has been delivered.