Here is my assignment. I have gotten as far as creating a table for the four entities… Interns, Clients, Projects, and Employees. I need to create a relationship between the two things… I can’t figure out how to create a relationship between one group and the relationship should correspond to more than one entity. Could you read the question, I don’t really know how to ask what I need help with… I would be so gratetful!!!!!!!!!
PART I: Creating the Consulting Project Management Database
CASE BACKGROUND
For your summer break you decide to intern at Strategic Business Consulting Group (SBCG). You applied and are offered an intern position. Currently SBCG manages information about the clients, projects and amount spent by employees on various projects as well as other project work related information using Excel spreadsheet (Download “project_management_reports.xlsx” file from WebCT to see the spreadsheet used by the company). Given that the consulting group is rapidly growing, you are asked to design and develop a project management database for the consulting group. This database will help SBCG to conduct various sophisticated queries, and generate useful reports that cannot be done easily with the current system. The database will also help company minimize data inconsistency issues and any data anomalies. The database should be implemented in Microsoft Access 2007 and it would contain data about its current clients, projects, their locations and fees for the projects, employee assignments to various projects, etc.
You recall the concepts that you learned in the Introduction to IS class and feel comfortable that you can fulfill your task. Next, you interview various employees of SBCG to gather the business rules and requirements related information that will be stored in the project management database. The notes that you took are reproduced below:
Business Rules
SBCG works on a wide variety of projects. Though most of the projects have only one sponsoring client company, there are some projects that can have two or more sponsoring clients (for e.g., a project named “examining the impact of electronic cars” had 2 clients — Société de l’assurance automobile du Québec” (SAAQ) and Toyota Canada Inc.) If there is more than one client for a project, then each client specifies the percentage of the total fee that they will pay, otherwise the percentage of the total fee paid by the client is 100% (this attribute is referred as “PercentageOfFee”. Each project has to have at least one sponsoring client associated with it. However, it is possible to have clients in SBCG database that are not yet associated with any project (i.e., there is information about these clients but they have not sponsored/assigned any project to SBCG). A client can be associated with more than one project. At the end of the project each client associated with a project gives a satisfaction score (referred as “SatisfactionScore”). A satisfaction score is a number between (and including) 1 and 7 where 1 stands for very dissatisfied and 7 stands for very satisfied (see Project_client_report worksheet in the Project_management_reports.xlsx for sample data).
Each project can be assigned to one or more employees. A project has to have at least one employee assigned to it. Each employee can work on one or more projects. There may be employees who have not been assigned to any project. An employee also has to record the number of hours spent on a project on all the projects on which he/she is working (see Project_Employee_Report worksheet in the Project_management_reports.xlsx for sample data).
Each employee is assigned interns who assist the employee. An employee can be assigned one or more interns. Each intern has a start date, end date, hourly rate. An intern can work for only one employee and each intern should work with some employee. (See Intern_employee_report worksheet in the Project_management_reports.xlsx for sample data).
Each employee has a unique ID (referred as “EmpID”). For each employee, SBCG records the Last Name, First Name, E-mail Address, Job Title, Business Phone, Mobile Phone, Fax Number, Address, City, State/Province, Zip/Postal Code, Country/Region. Also SBCG, wants to be able to store detailed comments about each employee (assume that field should be referred as “Notes”, which can be more than 500 characters long).
Each client also has a unique ID (referred as “ClientID”), Client Name, City, Country, and the Industry in which the client operates.
Each project has a Project ID (unique ID), Project Name, Category, ProjectsPriority, ProjectsStatus, StartDate, End Date, Budget in Days, BudgetDollar, PercentageComplete
Each Intern has an InternID, Last Name, First Name, E-mail Address, Mobile Phone, Address, City, State/Province, Zip/Postal Code, Country/Region, StartDate, EndDate, and HourlyWage
Requirements
1.Read the business rules given above, carefully.
2.Create an Entity Relationship Diagr
