Phase 1b This marina has ten employees (2 owners and 8 staff members) who serve

Question

Phase 1b

This marina has ten employees (2 owners and 8 staff members) who serve approximately 100 boats and their owners. As with most marinas, they are equipped to provide a wide variety of services to their customers, from boat launching and haul-out to cleaning and waxing to repair and maintenance. The two owners, Don and Ted, split up the main responsibilities of the marina. Don manages the docks and the fuel dock, and Ted manages the maintenance shop and the crane to launch and haul out boats. The docks have four employees and the maintenance shop has four employees.

For this phase of the project, you must develop an Entity Relationship Diagram (ERD) for the system. Entity types for this system include:

Owners/Employees,

Customers,

Services (i.e. repair, maintenance, fuel),

Supplies used (i.e. fuel, oil, wax, cleaning supplies),

Suppliers (provide supplies),

Sales

Be sure to design in the ability to record the cost to the customer. The purpose of this phase is to communicate with the owners how you envision the relationships in the data generated by the marina. Remember, the owners are not technical experts, so a simple way of communicating the design of the system is valuable.

Phase 2

Once the ERD is complete, convert the diagram into tables with the associated fields. Identify primary keys for each table, data types, etc. Also, determine the relationships between tables and the fields that make up those relationships. The purpose of this phase is to communicate between the system designers and the programmers (you) as to the layout of the system.

Phase 3

Using Microsoft Access, build your database. Be sure all the relationships work and all fields have appropriate data types. Input some sample data and run the following queries:

Generate a list of customers who are scheduled to launch their boats this week (pick any week).

Determine which customers most frequently have maintenance orders on their boats.

Determine which service is most widely used (i.e. bottom painting, waxing).

Which suppliers provide the most commonly used supplies.

Generate a list of customers and their boats by size of boat (in feet).

This is the ERD for the first part. I believe everything is correct I am just having a lot of trouble getting it into access and to work correctly. If it is too difficult to post actual answers any advice would be much appreciated as well. Thank you.

Customer Cust Last Name Cust First Name Cust Phone Cust Boat Fit Suppliers SupplierName Sales SalesID PK Sale Date Employee Name CustID Supplies Supply Name Cost SupplierName PK Services ServiceName Employees Employee Name Items Supply Name ServiceName

Explanation / Answer

The following can improve the ERD:

1. There should be many to one relationship between customer and sales and employees and sales becase for the same sale there may be many customer and for the same sales department there may be many employees working in it.

2. Supplies class can be included inside Supplier.

3. Make Item as base class and inherit attributes accordingly in Services class.

Apart from these three suggestions the whole ERD seems to be good.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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