Reference no: EM132629625

A. Produce an ERD that portrays the relationships listed below.
• An ASOOCIATE(AssocID,Name,Status)
• Works in exactly 1 department
• Is a member of at least one team, but may be on many
• Is either an employee or a contractor
• A DEPARTMENT(DeptID,DeptName)
• May have many associates
• You can have a department without any associates
• A TEAM(Name,Description)
• Is in zero or more office locations
• You can't have a team without multiple associates
• An OFFICE(Name,Description)
• Can have zero or more teams
• Is in exactly 1 building but you can't have an office without a building
• A BUILDING(BldgID,Address,Owner)
• Has at least 1 but may have many offices
• Eliminate the N:M relationships in this exercise by using TEAM_ASSIGNMENT and TEAM_LOCATION associative entities

B. GreenOrange (GO) finds temporary job placements for applicants in the accounting, actuarial science, and supply chain fields. GO's manager gives you the following description of their business, with the hope that you will be able to convert their error-prone manual record keeping to a database-driven one:
• GO has a file of applicants who are willing to work.
• If the applicant has worked before, that applicant has a specific job history. (Naturally, no job history exists if the applicant has never worked.) Each time the applicant works, one additional job history record is created. Job history includes work outside of GO.
• Each applicant has earned several qualifications. Each qualification may be earned by more than one applicant. (For example, it is possible for more than one applicant to have earned a BBA degree or a Microsoft Certification. And clearly, an applicant may have earned both a BBA and a Microsoft Certification.). The date the qualification was earned is recorded.
• GO offers courses to help applicants improve their qualifications.
• Every course develops one specific qualification; however, GO does not offer a course for every qualification. Some qualifications have multiple courses that develop that qualification.
• Courses are taught during training sessions. A training session is the presentation of a single course. Over time, GO will offer many training sessions for each course; however, new courses may not have any training sessions scheduled right away.
• Applicants can pay a fee to attend a training session. A training session can accommodate several applicants, although new training sessions will not have any applicants registered at first.
• GO also has a list of companies that request temporaries.
• Each time a company requests a temporary employee, GO makes an entry in the Openings folder. That folder contains an opening number, a company name, one required main qualification (Each opening requires only one specific or main qualification), a starting date, an anticipated ending date, and hourly pay.
• When an applicant matches the qualification, the job is assigned, and an entry is made in the Placement folder. That folder contains an opening number, an applicant number, start date, end date and expected hours. In addition, an entry is made in the job history for the applicant. An applicant can have multiple placements and each placement applies to only one applicant.

GO's management wants to keep track of the following entities (hint: you will find that you also need to add additional association entities - anywhere you initially have N:M relationships between entities):

• COMPANY
• OPENING
• QUALIFICATION
• APPLICANT
• JOB_HISTORY
• PLACEMENT
• COURSE
• TRAINING_SESSION

Do the following:

a. Draw a Crow's Foot ERD, using proper notation, for the above scenario.

1. Identify all possible relationships

2. Identify the connectivity for each relationship. The relationship line should connect PK to FK and show the proper cardinality. The key's attribute name may be named in the problem narrative or you may need to use a surrogate (e.g. ApplicantID)

3. Use theseLucidChart entity shapes:

4. Derive non-key attribute names from the narrative wherever possible (e.g. "StartDate"). Each entity should have a few attributes that would make sense for that particular entity (e.g. COMPANY(...., City, State,...). These attributes would be in addition to the key attributes and are primarily to "fill out" the ERD so the purpose of each entity is a little clearer.

5. Identify and properly label all strong and weak entity relationships. To help simplify, work under the assumption that the only weak entities in this ERD are ID-dependent entities.

Attachment:- database system.rar