HW 7

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

• Observe the data given in the spreadsheet xlsx
• Notice that the spreadsheet contains the data about the following scenario
  • Sun Consulting is an IS consulting firm
  • It operates in the following three regions: East, West, and Mid-Continent
  • 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
  • It employs a number of consultants, each of which is assigned to one region
  • Each consultant is skilled in at least one of the expertise areas, but can be skilled in more than one
  • 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:
    • Step 1: Create an ER diagram (already created – see next page)
    • Step 2: Convert it to a relational schema (already created – see next page)
    • 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 accdb
    • Step 4: Populate the MS Access database with the data given in the spreadsheet
    • Step 5: Create a form that will enable entry into the consultant table and name it ConsultantForm
    • 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.
    • 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.
    • 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.
    • 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.
    • Submit file accdb

ER Diagram

Relational Schema

Sun Consulting spreadsheet