ISM 4212 INTRODUCTION TO DATABASE DESIGN AND ADMINISTRATION You will begin creat

Question

ISM 4212 INTRODUCTION TO DATABASE DESIGN AND ADMINISTRATION

You will begin creating a relational database design based on each of the minicase studies provided below. Identify the entities described, and for each entity, identify the attributes included in the description and select an appropriate identifier

Case #1

You are designing a database application for an online grocer. Customers place orders over the Internet, and the orders are delivered to the customers’ homes. Customers must live within 50 miles of a delivery warehouse. The company has 12 delivery warehouses in California.

Each employee has an employee ID. You also need to track the employee name and address, manager, and warehouse location at which the employee works. Each warehouse is identified for tracking purposes by a four-digit code. The application will use a mapping program to automatically qualify customers and assign the nearest warehouse for customer deliveries. A customer is assigned an ID when first registering at the company Web site. The customer must provide a contact name and phone number, address, and billing information.

Restocking orders are placed daily. Each vendor sells a wide range of items, and each inventory item can usually be ordered from at least two different vendors.

1.List the five entities described in the case, including attributes and the best identifier. (2 points for each properly listed entity and its attributes)

Here is the first one you’ll need:

Employee - EmployeeID, name, address, manager, and warehouse location. Use Employee ID as identity.

Now, list the other 4:

Case #2

You are designing a database for a service support company. Employees are tracked by social security number. You must also keep the employee name, address, phone number, department, and manager on file. Because married employees cannot both work in the same department per company policy, you must also track spouses for married employees.

The company provides business services for other companies. Along with a customer ID, you need to track the services provided. Detailed service records are kept that include coded values for the service provided, the rate, and the time to the nearest quarter hour.

Internally, the company tracks who provides what service for which customer, including the date and time the service was provided. This gives the company a reference in case of customer complaints.

Customers have up to 90 days to pay for services rendered. Billing records are tracked as under 30, 31 to 60, 61 to 90, and over 90. Customers with outstanding balances over 90 days old are placed on credit hold and cannot request additional services until payment is made.

1. List the entities described in the case, including attributes and the best identifier.

Explanation / Answer

2)

entities are employees with attributes social security number, employe name, address, phone number, department,manager, married,

another entity is customer with details

customer id, service provided, service detail

Another entity is Billing with bill time, credit status as attributes, custid

Best identifier for employees will be social security number

for customers it will be customer id.

1)

Entity warehouse attributes order placed from , distance, warehouse number

Entity customer attributes customer id , contact name and phone number, address, and billing information

Entity Vendors attributes item sold, number of items

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.