EntityAttributes

Entity Attributes

Step 2 in the DesignSteps, we'll write down a table shorthand for the entities on the EntityList here.

In the following list, primary keys are underlined, foreign keys are in italics.

Entities:

• CUSTOMER (customerid, title, firstname , initial, lastname, address, city, state, zip, areacode, phone)
• INVOICE (ordernumber, customerid, date, labourcharges, paymentmethod)
• INVOICE LINES (_ordernumber, partid_, quantity, total)
• PAYMENT METHOD (methodid, name, limit)
• PART (partid, description, price, minimumstock)
• INVENTORY(inventoryid, partid, vendorid, onhand)
• VENDOR (vendorid, name, address, city, state, zip, areacode, phone)
• SUPPLIES (vendorid, partid, cost)
• CAR (carregistration, customerid, lastservice, mileage)

With these attributes we can design our ERDs and define the DataDictionary.

john_knight (Sep 8, 2001 4:02 am; Comment #1)

The naming convention that you have used does not conform to that which we have been encouraged to use. I think we should agree on a naming convention before progressing much further as names will be used in a variety of different tools and will not automatically update across applications. The text book and Sam's notes encourage the use of naming such as CUST_NAME, PART_NO etc. This would also avoid confusion where the same attributes in different tables currently have the same name, for example phone, city etc.

john_knight (Sep 8, 2001 4:06 am; Comment #2)

There needs to be a couple of additional attributes on the CUSTOMER table. Initials and title will help when generating the mailing lists.

john_knight (Sep 8, 2001 4:12 am; Comment #3)

There seems to be a little confusion over the PARTS. There are a set of standard parts such as windscreen wipers, etc. These each have an associated cost but can be supplied by a number of different vendors. As things stand the table would require each part to be linked to a vendor when the other data in the table is generic. If one requires an audit trail of parts then one approach would be to introduce another table bringing together the part serial number with the vendor. In this way one component type can have many items in stock. Each item in stock can only come from one supplier, but many suppliers can provide that component.

mj (Sep 8, 2001 3:55 pm; Comment #4)

The avoiance of the naming convention was deliberate; these psuedo tables. I left the official naming to the DataDictionary stage.

I'll extend the attributes for CUSTOMER to include a first name, a last name and a initial and title.

As for the parts; I see what you mena: PARTS is a table of part types, while there should be a seperate table for INVENTORY. This is an Entity issue, I guess, not so much an attribute issue. I'll update that here and make a remark on the entity page.

stephen_jackson (Sep 9, 2001 4:20 am; Comment #5)

I checked your posting with mine martijn, we very much the same. I had the primary key on car as CAR_REG car registration presumably we are going to make CAR_ID an enterable unique field rather than an auto generated number.

john_knight (Sep 9, 2001 4:36 am; Comment #6)

I will take on board the various changes and implement them in the DD and set up the attribute names as discussed to replace the pseudo names.

john_knight (Sep 9, 2001 12:16 pm; Comment #7)

Having worked on the DD, some more thoughts about Entities. The price would seem to me to sit more comfortably with the other PART attributes, since there will be one price per part type - rather than indivual prices for the same type of component.

Likewise minimum stock would apply to the part type rather than each line item in an inventory.

If parts are not individually recorded by serial number, because individually they are low value, then another approach would be to record cost per item to EverFail and number of items taken on charge. This raises the question of parts to fit a particular car.

So it would seem more sensible to record:

oil filter, type xyz, cost price, vendor, quantity taken on, total stock oil filter, type pqr, cost price, vendor, quantity taken on, total stock etc

this would change PART to PART(partid, type, description, price, minimum stock level)

Any thoughts?

mj (Sep 10, 2001 9:40 pm; Comment #8)

I now see where you are going with the PART/INVENTORY division. My one concern would be that we would now easily loose information about what vendors can supply what parts. I think we would need an addition composit entity SUPPLIES that connects vendors with parts. A vendor can supply multiple types of parts and a part can be supplied by multiple vendors. This is one for the Assumptions page. Who owns Assumptions?