lab question 1

This assignment is to analyze a given business scenario, create and discuss a solution and then create an Entity Relationship Diagram (ERD). Be sure to read the entire assignment carefully before you begin work on it. Post any questions you have on the business scenario, assignment requirements or deliverables in your Individual Discussion area in a timely manner that will allow the Professor and TA to provide assistance before the due date.

Contents Objectives: 1 Business situation 1 Additional Business Details 3 Deliverables 3 Version History 4

· Use skills you learned and build upon them to gain additional insight and skill into analyzing a business situation for database solution requirements

· Analyze and clearly describe the business situation

· Design and explain a possible solution (database portion only)

· Write your analysis using language appropriate for a reader with basic understanding of database terminology

· Use Oracle Data Modeler to create an ERD and DDL

· Export ODM ERDs that can be imported successfully by someone else

Your company has a proprietary "Company Directory" application that is very old and can no longer be maintained. Your company wants to create an in-house version of this application and have it display the same information as the old system to the user. You are unable to view the application code so you will review the output of the application displayed to the user. From the display, you will determine the data requirements for the solution. From the requirements, you will create an ERD that the company DBA will use to create a new database.

You observed the following information when you generated some sample output from the Company Directory:

· The company has only 2 departments: Sales and Warehouse

· The 6 fields shown on the above are required in the new solution

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

· The job titles shown are the only titles the company has

· Employees have a sequential employee ID number that isn't shown on the display

· Sales employees are assigned unique office and mobile phone numbers

The deliverables for this assignment are:

1. Your analysis of the business situation described in this assignment

You can use this outline or one of your own that provides comparable, and complete, information. You should also modify or add to this outline to provide additional details you feel are necessary to this project and/or to properly document your thoughts and design solution. Document and cite any references you used per APA guidelines for a UMUC research paper.

a. Introduction

b. Description of the Analysis

i. What are your observations and thoughts regarding the business scenario and data views given?

ii. What are the assumptions and/or business rules implied by the given data

c. Preliminary entity, attribute and relationship requirements

i. What entities to you need to fully normalize this database?

ii. What attributes does each entity need?

1. Atomic attributes

2. Multi-valued attributes

3. Data types

4. Candidate keys

iii. What relationships does this database need?

iv. What constraints do you need to enforce - referential integrity and data entry requirements?

d. Benefits and Risks

i. What benefits are there in designing a new application that will store the application data in relational database?

ii. What implementation, usage or maintenance risks are there to consider if this database were to be created and implemented?

iii. What improvements can you suggest (think of address book and employee directory applications you may be familiar with)?

2. ERD using Oracle Data Modeler

a. Create ERD in ODM using tutorial provided in the Virtual Lab area of this course as a guide

b. Export your design using the tutorial provided in the Virtual Lab area of the course content section

c. Generate DDL from your design

3. Explain how your design supports your analysis

4. Files to turn in as a single ZIP, or Windows compressed file:

a. TXT file of DDL generated by ODM

b. ODM export of your ERD (should be two items: DMD file and corresponding folder)

c. Word file of all analysis and explanations