Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BIS 324 Problem Set 2

Spring 2024

Submission: You must submit an ERD logical model drawn in Data Modeler to CourseSite for this problem set, as well as the relational model generated from this logical model. Hand drawn ERDs are not accepted. If you want to document any assumptions you made for this problem set, you may upload them in a separate Word document, but this is NOT required.

Requirements:

1. Your ERD must support the business requirements as detailed here. The only submissions necessary for this assignment is the completed ERD logical model and the relational model submitted as PDF’s to CourseSite. The word document is OPTIONAL.

2. You must correctly draw all entities and attributes of entities as well as relationships between entities in Data Modeler.

3. All cardinalities and modalities must be represented correctly in the logical model as described in class and using the crow’s feet notation. You must select the Information Engineering Notation for the logical model. Points will be taken off if this is not selected.

4. All attributes must be included for each entity as detailed below. Break up attributes as appropriate, for example an address would break up into street, city, state, and zip.

5. Primary keys and foreign keys must be noted for each entity in the relational model as appropriate.

6. You must name your submission file with your last and first name. For example: JeanNonnemaker_logical.pdf and JeanNonnemaker_relational.pdf

7. If there is an important assumption you made that impacts the ERD, you may optionally submit these assumptions in a Word document also uploaded to CourseSite with your first and last name. For example, JeanNonnemaker.docx.

8. Normal points off for late assignments as noted on the syllabus will apply.

9. You must select the correct data type for all attributes. These must be shown. For all types except DATE you must show the appropriate number of bytes. For NUMBER types you must show precision and scale as discussed in class. Expand the boxes so I can see the data types!

You are drawing an ERD to store information about the operations of a fictional company

· This application will track employees and their jobs, departments, branches, countries, regions and job histories.

· Each employee has a name, email, phone number, address, salary, hire date, and a home department. An employee may have more than one job at a time, for instance they may be a clerk for one department on certain days, and an auditor on other days for a different department. Each job the employee has will have its’ own salary and/or commission.

· All Departments have a manager and are within a single branch of the company.

· Each branch is located in a specific country and has a specific address.

· Each country belongs to an international region, for example, The North American Region.

· Each job has a minimum and maximum salary.

· A job history is kept for each employee including the start and end date for the specific job and department for that employee.

Helpful notes:

1. You should determine and create primary keys other than the fields listed above, as needed (for example, employee id).

2. You should break the information listed above into logically understandable fields, for example, do not store the address in a single field, rather break it up into fields such as zip code, city, etc.

3. You should always include a descriptive field in each entity. It is not enough to provide an attribute for “JOB ID A”, you should also have a field to hold at least a small description or title of what “JOB ID A” is. For example:

Bad:

Good: