I'm having a bit of a difficult time choosing composite keys on databases.I know that to choose primary keys I have to follow some rules like: it cannot contain null values,avoiding fields that can be changed over time(ex: phone number)., but how do I know which attributes go best for a composite key?For example:

An agency has a number of account(sequential) for each account created,and those account numbers are unique.But in other agencies different accounts can have the same number of account.Each client can have many accounts in a same bank agency and an account can be shared by more than one client.
There would be a roll of atributes like: number of account,code of agency,type of account,name,address,money on account,phone number,birth date.

I would say that the primary key would be composed of number of account,agency code and name of the person, but I'm not sure.Is there any specific rule to follow in order to determine the composite primary key of a database?
Thanks in advance for the reply!

if your PK is (acctno, agencycd, clientname), then you are saying that the same account number can be used by the same client at a different agency

while that would be unusual, it is actually okay

it is also possible that the same account number can be used by different clients at the same agency

which you say is allowed, and of course it does make sense

it is also possible that the same account number can be used by different clients at a different agency

that sounds scary but it's actually normal

my rule of thumb: try saying it, and ask yourself if it makes sense

in your case, it does

So,in my example the primary key composed of that attributes is ok?

looks okay to me

let me know what mark you get on the assignment

Hehe, it's not an assignment...I'm having an exam Monday and I'm studying for that exam, solving exercises ,and I have a yet more difficult exercise to solve which is about the administration of medication in a clinic:

"When a patient (npatient;name_patient;date_birth;address;contact_ patient;numberid;health_system) is interned,there's a set of medication (code_med;name_medication;active-chemical;cod_lab;name_lab) that is prescribed daily by his doctor(ndoctor;name_doctor;contact_doctor) depending on the diseases that are diagnosed (cod_disease;name_disease) and we want to register.A doctor has a special skill(code_skill;name_skill). It's essencial to store the diary dosage of each medicine that each pacient ingests.The diary prescribed medicines for the diseases that were detected by the doctor are administrated by the nurses(nurse_code;name_nurse;contact_nurse).Daily, the administration of the medicines can be done by more than one nurse.Finaly we want to register the state of evolution of the disease of each patient and for the doctos and nurses to consult the description of the effects and main counter-indications of the medicines for each disease."

The primary key I have for this exercise consists on the following attributes:
npatient;code_medicine;code_lab;code_disease;code_ skill;code_nurse;code_doctor.
I'm not sure if those are correct(I think they are but I might be missing one or two attributes for the primary key) so any help is deeply appreciated!
Thanks in advance and thanks to r937 for the reply!

Now that I look at it,in the text it says that a doctor has only one main special skill,so i think it's not necessary to use that attribute as part of the primary key,I guess...

A primary composite key (or "natural key") should contain the minimum columns necessary to uniquely identify a record.

That's all, folks.