Can someone please tell me if I have a transitive dependancy here. I would like to have one table with mfgr, model number and serial number as non-key fields with a surrogate key.

I think that I possibly have a transitive dependancy between mfgr and serial number and most certainly between mfgr and model number but I am not positive.


Thanks.

Frank

pretty hard to tell just from the names, but yeah, i would say you're right

try this: given a specific value of the PK, this determines the values of all non-key attributes, right?

so for a specific value of the PK, if i were to change the value in one of the non-key attributes, would the data still be the same in the other non-key attributes? if i changed the mfgr, would the model number still be valid?

by comparison, consider a table for employees, with empno as the PK, and let's take a specific value of the PK, the one corresponding to you

if i changed the phone number for you, would that require a change to your address? a change to your hatsize? no, because all these non-key attributes are independently dependent on the value of the PK, namely you

Hi Rudy. Thanks for the reply.

The PK will be an auto-incrementing surrogate. So if a value was changed in one of the non-key fields, it would affect the the other fields as well. Unless I am misunderstanding your question. Here would be some sample data.

I have a pic I am going to post. brb.

okay, let's use an example

for PK=1, if i changed the serial number to XP428-834, it is still model=X563?

answer: prolly not

it looks like model is dependent on serial, and mfgr on model

i.e. it's not 3NF

That is what I figured. It is not in 3NF. Ok.. So here is a jpg of the changes I have made so far..

We are talking about the company_Equipment tables. I have taken serial_Number out and I am debating whether to put mfgr in its own table.

so a piece of company equipment cannot be entered into your database unless it is already loaned out to a specific employee? what happens when he returns it? you won't allow company_equipment.employee_number_seq to be null, so what do you put there if the piece of equipment is currently not loaned out?

and what's the purpose of naming your columns like company_equipment_status? what's wrong with just status? you particularly enjoy typing extra needless characters?

and how come a single piece of company equipment can have multiple serial numbers?

That is correct. The only time that a pice of equipment would be entered into the database is if it has been loaned out.

When he returns it, the date is entered into the date_In field. I know that these tables have some issues. I am trying to work them out.. I am all ears if you have a better way to model this.

LOL.. I hate typing! Ok, here is where being a nubie comes into play. I type the prefixes such as customer_Status because when I am joining the tables back together and I am viewing the results, the tables that have the same names such as "status" are null. I am thinking that may be because the names are the same. Can I use an alias to fix that?

No, I don't see that a single piece of equipment can have multiple serial numbers. Actually, your correct.. What do you suggest. I am really out of answers.

so if a piece of equipment has been loaned out, and then comes back in, it's not still loaned out, is it? so what do you do if it has to be loaned out again?

using column aliases in your queries for specific reporting purposes sounds like a much more sensible approach than bastardizing the column names in the table

as for the serial number, i don't know your equipment but i would guess that the serial number is actually a candidate key for the equipment

can two pieces of equipment have the same serial number?

Well, if the piece of equipment comes back, it would be reflected in the date_In field then if it were loaned back out, the equipment would be re-entered into the db under a different person. Is there a better way to do that? Ywo pieces of equipment would not have the same serial number.

Ok.. I hate bastardizing anything.. I have to go back through the database and rename all of those columns.

The serial number is the candidate key but the reason I gave it a surrogate key was because the user may not know what the serial number is. Does that make sense?

EDIT: Nevermind. I understand what you are saying about the serial number.. Ok, I need to dump the surrogate..

I have a question regarding the company_Vehicle tables. The vehicle can be loaned out during a shift and I need to tie that table to the employee. Vehicles are equipment as well so shouldn't that be somehow tied to the equipment table?

What I did to circumvent a single piece of equipment from having a second serial number attached to it was to drop the serial number table and add serial_Number to the equipment table as its PK.

I am seeing that now, with this change, it will be impossible to add a piece of equipment that *does not* have a serial number. What would be the best way around this?

Thanks,

Frank

if you dump the surrogate and make serial number the PK, then you can never have a piece of equipment without a serial number

you could give the serial number a unique constraint

but then you'd only ever be able to loan it out once


ain't data modelling fun?

do you see now why so many programmers just throw their hands up and forget about primary keys and foreign keys?

What on earth leads you to suspect that device serial numbers are unique? IP Addresses are supposed to be unique, and they are unique on any given VLAN, but do you have any concept of how many devices have the address of 192.168.1.100 in the world? VIN numbers are supposed to be unique too, but I happen to own a vehicle that shares its VIN with at least one other vehicle registered in the United States (this is ugly, do NOT try it at home).

The only "unique" that I will ever depend on again is when I can define something to be unique, such as a GUID or (under duress) a sequence number with a unique constraint.

-PatP

who, me??

" ... on earth?" sure, your point may have merit

but actually, within a given app, where we are dealing with pieces of equipment owned by a given company, it's not unreasonable to have unique serial numbers

in fact, i've worked for more than one company where they inventoried every single piece of equipment in the company with "asset tags" which had unique serial numbers (once as part of a Y2K initiative)

In SQL Server the GUID is only guaranteed unique if generated by two machines on the same network. I hope you don't have any rogue servers knocking around on different netwroks on remote sites

I would agree with Rudy that you need to measure this against your domain. I have (in my much more limited experience naturally) experienced far more problems from absent unique constraints than present ones.

pootle flump ur codings are working excelent.

Statistically speaking there are 2 other people who share the same DNA as you!