A number of engineers employed by a builder company work in several construction projects. We store the project name, location, estimated duration and project manager name for a construction project. Each project has a number of engineers working on the project. For an engineer, we store name, address, engineering membership number and area of expertise. We also need to store the number of days each engineer spends on each project.
A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question.
A property rental manager needs to store details about the tenant and the owner of a property. Tenant and Owner details include an identification number, name and contact number. For tenant, we also need to store name and contact number of next of kin. Property is directly associated with the owner. One owner may own many properties but a property must belong to one owner. For a property, we store its address, property type and current rental rate.
A truck driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to four. For driver, we store the name, license number and birth date. For team, we store the team identifier and base location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the total kilometres the driver has driven within the team.
Prepare the following for all four questions:
- An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials.
- A list of relations (entity list): Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned but do not create any other additional attributes.