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!
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:
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!