Business situation

Your company has a Microsoft Access Contacts database. Your company wants to create an in-house Oracle version of this database. You can get familiar with an Access Contacts database from this video or download a copy by opening Microsoft Access and then search for Contacts.

Review the information that follows to determine the data requirements for the Oracle database. Use your findings to create an ERD, and the relational schema using Oracle Data Modeler.

The business analyst provided following details about company and the new database requirements

The company has more than one department

An employee is assigned to only one department

A department has at least one employee

Warehouse "Order assembler" team members share the same mobile phone

Each employee has a job title

Employees have a sequential employee ID number that isn't shown on the sample output

Each Sales employee is assigned his own office and mobile phone number

The business analyst proposed following sample output from the new Contacts database:

Below is the ER diagram of the company. It consist of two entities Employee, Department and one relationship.

Both the entities have a primary key mentioned as underlined attribute.

Relationship between Employee to department is Many to one.

Relational Schema of the created ERD. Primary key is mentioned as underlined attribute and foreign key as bold attribute.

Employee (EmpID, Name, Title, Email, Office_Phone, Mobile_phone)

Department (DeptID, Name)

Works_on (EmpID, DeptID)

Need help with:

1. Assume that your company plans to expand its operations. There is also a possibility that the company will integrate your contact database with HRs employee database.

a. Suggest some future requirements could influence the preliminary design of your contacts database .( How would the ERD change and what entities, etc would be added?)

Name Department Title John Smith Sales Account representative JSmith@company.com Office Phone Name Department Title Email Office Phone Sally Miles Sales Manager SMiles@company.com 222-223-1111 Joe Harris Warehouse Order assembler JHarris@company.com 111-333-1222 Name Department Email Office Phone Name Department Title Mike Jones Warehouse Order assembler MJones@company.com 111-333-1222 Office Phone Name Department Title Email Office Phone Mobile Phone Marion Davidson Warehouse Manage MDavidson@copany.com 111-333-1222 111-333-1450 Name Department Title John Smith Sales Account representative JSmith@company.com Office Phone Name Department Title Email Office Phone Sally Miles Sales Manager SMiles@company.com 222-223-1111 Joe Harris Warehouse Order assembler JHarris@company.com 111-333-1222 Name Department Email Office Phone Name Department Title Mike Jones Warehouse Order assembler MJones@company.com 111-333-1222 Office Phone Name Department Title Email Office Phone Mobile Phone Marion Davidson Warehouse Manage MDavidson@copany.com 111-333-1222 111-333-1450