MS access .

ER diagram.

Question 2.

You were requested to design a database for a small business called Vittorio Verde Landscaping, Inc. (VVLI), which performs a variety of landscaping work for residential customers. Your database has to store the following: For each of its workers, VVLI wants to store a first name, last name, street address, city, state, zip code, and phone number. Each worker is assigned to a single work crew, each of which usually consists of three to nine workers, but there is no upper limit. For each crew, you want to store a crew nickname, the date it was formed, and which workers belong to it. For each customer, the database should store a unique customer number, first name, last name, street address, city, state, zip code, and a single phone number. In VVLIs records, a visit consists of one of the crews visiting one of the customers to perform several works. For each visit, VVLIs database should remember which customer was involved, which work crew made the visit, the date and time the visit began, the number of hours it required, and a description of the works performed in the visit. Assume you do wish to use a zip code table in the database.

a) Design a database to hold all this information. You may create synthetic keys as necessary or convenient. Hand in a database design outline and an ER diagram.

Advice: First, forget the zip code table: create the tables and the relationships assuming you do not have a zip code table. Then, when you think what you have is correct, bring the zip code table in: for every table that needs an address, link it to the zip code table with a relationship. For this question, please submit one Word file or PDF with the answer to part a). You may do the ER diagram by hand, then take a picture of it, and paste it to the Word file. Do not submit image files like JPEG or PNG, because Blackboard cannot preview them. Only Word or PDF are allowed.

b) In Access, create all the necessary tables for this database, and choose reasonable datatypes for each attribute. Create the relationships between the tables and enforce referential integrity. You do not have to enter any data in the tables or create any forms or queries.