database - How to set up Primary Keys in a Relation? -
i wish know how correctly set primary keys in relation. e.g. have er-diagram contain elements:
- key attributes
- weak key attributes
- identifying relationships
- associative entities
in order translate relational model should tricks. elements above deal primary keys of relations natural keys - can leave them as is or replace surrogate keys.
consider cases.
case 1
key attribute name - must of type char or varchar. names become key attributes.
case 2
two (or more) identifying relationships become composite primary key of relation (which made of foreign keys).
case 3
identifying relationship(s) weak key attribute(s) become composite primary key.
case 4
associative entities have 2 or more identifying relationships junction relations (junction tables).
how set primary keys relations in order handle above cases (perhaps more cases did not mention)?
how avoid using surrogate keys , in cases necessary?
how set datatypes primary keys?
if composite primary key has passed child relation, shall replaced surrogate?
advantages , disadvantages of using surrogate keys in view:
advantages
they're compact (usually of type
int) , replacement composite keysthey're illustrative when they're in foreign keys
they're painlessly indexed
disadvantages
they're numbers , meaningless. e.g. wish fill junction table in interface application - left no other choice relate numbers
they're redundant
they're confusing
as setting datatypes - there must more tricks setting primary keys whole.
update
i should have given example initially, did not. here's example. consider have 2 main entities interact each other (still don't know how illustrate such things diagrams here - i'll show them tables demonstrate international space station crew rotation system):
spaceship
╔════════════════╤════════════════╗ ║ shipname │ shiptype ║ shipname - primary key ╟────────────────┼────────────────╢ shiptype - foreign key (but ║ soyuz tma-14 │ soyuz ║ not being considered here) ║ endeavour │ space shuttle ║ ║ soyuz tma-15m │ soyuz ║ ║ atlantis │ space shuttle ║ ║ soyuz tm-31 │ soyuz ║ ║ ... │ ... ║ ╚════════════════╧════════════════╝ and crew
╔════════╤══════════╗ ║ crewid │ sallsign ║ crewid - primary key (used id 'case crew ╟────────┼──────────╢ shown crew members - has no particular ║ 4243 │ astreus ║ name) ║ 4344 │ altair ║ callsign - attribute (it may not assigned or ║ 4445 │ ... ║ explicitly shown - i.e. can null) ║ ... │ ... ║ ╚════════╧══════════╝ these 2 entities interact via flight. each flight delivers iss 1 crew , returns or same crew. relationship between flight , crew many-to-many , needs junction relation (table). can not relate spaceship , crew because of spaceships - spaceship can reusable (returnable) such space shuttles were.
so flight should like:
╔═══════════════╤════════════╤═══════════════╤═════╗ ║ shipname │ flightname │ shipflightnum │ ... ║ shipname, flightname ╟───────────────┼────────────┼───────────────┼─────╢ composite pk ║ soyuz tm-31 │ null │ 1 │ ... ║ shipflightnum ║ atlantis │ sts-117 │ 28 │ ... ║ depends on whole ║ soyuz tma-14 │ null │ 1 │ ... ║ composite pk ║ endeavour │ sts-126 │ 22 │ ... ║ ... - other ║ soyuz tma-15m │ null │ 1 │ ... ║ attributes ║ endeavour │ sts-111 │ 18 │ ... ║ depend on pk ║ atlantis │ sts-122 │ 29 │ ... ║ ║ ... │ ... │ ... │ ... ║ ╚═══════════════╧════════════╧═══════════════╧═════╝ so flight has composite primary key (flight name soyuz vehicle same spacecraft's name differs reusable spacecrafts such space shuttle) , needs related crew many-to-many. here part of complex question - if composite primary natural key should replaced surrogate one?
, if we're going work natural keys further new junction relation (associative entity) should like:
designation (crew designed flight)
╔═══════════════╤════════════╤════════╤══════════╗ ║ shipname │ flightname │ crewid │ crewtype ║ ╟───────────────┼────────────┼────────┼──────────╢ ║ soyuz tma-15m │ null │ 4243 │ deliver ║ ║ soyuz tma-15m │ null │ 4243 │ return ║ ║ soyuz tma-15m │ null │ 4445 │ backup ║ ║ soyuz tma-16m │ null │ 4344 │ deliver ║ ║ soyuz tma-17m │ null │ 4445 │ deliver ║ ║ soyuz tma-18m │ null │ 4344 │ return ║ ║ endeavour │ sts-111 │ 55 │ deliver ║ ║ endeavour │ sts-111 │ 44 │ return ║ ║ endeavour │ sts-113 │ 55 │ return ║ ║ ... │ ... │ ... │ ... ║ ╚═══════════════╧════════════╧════════╧══════════╝ here have 4x composite primary key made of 4 foreign keys (crewtype have fk constraint). if use surrogates instead of naturals result more compact hard fill (in view).
one more update
another case table (relation) typecrew:
╔══════════╗ ║ crewtype ║ ╟──────────╢ ║ deliver ║ ║ return ║ ║ backup ║ ║ ... ║ ╚══════════╝ everyhing fine if had not use these values in our queries (where crewtype 'backup'). if these values replaced alternative meanings in other languages or symbols e.g. >, < , ^ deliver, return , backup respectively (where crewtype '^'). adding numerical surrogate key not values may mismatch typename (where typeid=2):
╔════════╤══════════╗ ╔════════╤══════════╗ ╔════════╤══════════╗ ║ typeid │ typename ║ ║ typeid │ typename ║ ║ typeid │ typename ║ ╟────────┼──────────╢ ╟────────┼──────────╢ ╟────────┼──────────╢ ║ 0 │ deliver ║ ║ 0 │ backup ║ ║ 0 │ > ║ ║ 1 │ return ║ ║ 1 │ deliver ║ ║ 1 │ < ║ ║ 2 │ backup ║ ║ 2 │ return ║ ║ 2 │ ^ ║ ║ ... │ ... ║ ║ ... │ ... ║ ║ ... │ ... ║ ╚════════╧══════════╝ ╚════════╧══════════╝ ╚════════╧══════════╝ perhaps not question of relational model? perhaps it's bad design? not devise better.
position
any practice not based on solid theory not worthy of consideration. strict relational model practitioner, strong grounding in theory. relational model based on solid theory, , has never been refuted 1. there nothing solid in passes "relational theory", have taken them on, , refuted notions in space. further, relational database design science, not magic, not art 2, therefore can provide evidence of propositions or charges make. answers position.
1. non-science articles, , masses of opinions not understand science, yes, no scientific refutation. pygmies arguing man cannot fly, "true" them, not true mankind, based on complete inability understand principle of flight.
2. there art in presentations of high-end practitioners, yes, not make science art. science, , science, , on , above that, can artfully delivered, in models , databases.
"relational theory"
i wish know how correctly set primary keys in relation. e.g. have er-diagram contain elements:
if erd, wouldn't looking @ "relations", looking @ entities (if diagram early) or tables (if progressed). "relations" wonderful abstraction have nothing implementation. erd or data model means implementation (non-abstract, real) intended, intention physical leaves abstract world of theory behind, , enters physical world, idiotic abstractions destroyed.
further "theoreticians" allege serving database space cannot differentiate between base relations , derived relations: while might acceptable in abstract context, dead wrong in implementation context. eg. base relations tables, , need normalised; derived relations are, well, derived, views, of base relations, definition flattened views (not "denormalised", means different) of base relations. such, need not normalised.
- but "theoreticians" try "normalise" derived relations. , damaged 2 trying have definition of 1nf, have had forty 5 years, fundamental , rock solid, have supported, changed, derived relations, not need "normalisation", can classified "normalised". hilarious if not sad.
one marvellous quality of objective truth, of science, not change. subjective "truth", non-science, changes time. 1 can relied upon, must understood before practice undertaken, other not worth reading about.
isolation
they live in world of own, isolated reality of relational databases, relational model, , industry allege serve. in forty 5 years since rm came out, have done nothing progress rm or relational databases.
mind you, have been progressing sorts of notions, outside relational model.
the progress of rm (completion of neanderthals suggest "incomplete") has happened solely due standardisation (r brown , others working codd, resulting in idef1x standard modelling relational databases), , efforts of high-end sql vendors , customers.
that commercial rdbms vendors, established in 1980's, not non-sql freeware/shareware/vapourware groups of last decade, pass off wares "sql", gets , glued "platform", non-portable.
the worst part is, publish books non-relational concepts, , fraudulently label them "relational". , "professors" blindly "teach" nonsense, parrots, without ever understanding either nonsense, or relational model supposed explore.
if trying find answers "educational" project, sorry, cannot provide that, because "education", can see, totally confused, , has non-relational requirements.
i can however, provide direct answers question, governed science, relational model, laws of physics, etc.
the point take is, while relational theory , practice close after dr e f codd published seminal work, , during time sql platforms developed vendors, in post-codd era, passes "relational theory" divorced original relational theory.
- i can enumerate differences, not here. note if read posts touch on subject, can gather particulars, , enumerate them yourself. or else ask new question.
the question
i wish know how correctly set primary keys in relation. e.g. have er-diagram contain elements:
there no erd examine. ok, in update have example. perfect questions, because set of user views of data, , modelling can begin. note, not erd or model. rely on understanding data; analysing it; classifying it, not on looking @ data values microscope. realise that have been taught do.
in order translate relational model
yes, stated goal. word "translate" incorrect, because rm not merely flat or fixed set of criteria 1 "satisfies" or fits (as known "theoreticians"), provides specific methods , rules. therefore, modelling, according relational model.
we should tricks.
we don't need tricks, use science, , science. "theoreticians" , "professors" follow them, need tricks, , practice non-science. can't in regard. further, tricks use, circumvent , subvert relational model, watch out them.
surrogate
all elements above deal primary keys of relations natural keys - can leave them or replace surrogate keys.
well, there is, "teacher's" first trick exposed.
surrogates physical record (not row) pointers, not logical.
there no such thing "surrogate key", 2 words contradict each other.
a key has specific definition in rm, has made data. surrogate isn't made data, manufactured, meaningless number generated system. therefore not key or "key".
a key in rm has has number of relational qualities, makes keys powerful. since surrogate not key, not have of qualities, has no relational power.
therefore, "surrogate" , key each have specific meanings, , quite fine separate terms, together, self-contradictory, because opposites.
when people use them term "surrogate key", naturally expect some, if not all, qualities of key. not obtain of them. therefore defrauded.
the relational model (the 1 theoreticians know nothing about) has specific access path independence rule. long relational keys used, rule maintained. provides relational integrity 1.
the use of surrogate violates rule. consequence 2 is, relational integrity , relational navigation 3 both lost.
the consequence of is, many more joins required @ same data (not less, lovers of mythology , magic keep parroting).
therefore surrogates not permitted, on another, separate count.
since in modelling stage, either conceptual or logical, , keys logical, , surrogates physical, surrogates should not come picture. (they come picture, if @ all, consideration, when logical model complete, , physical model being considered.) near completion of logical, introduction of surrogate should raise red flag.
the "teacher", , author of "textbook" using, frauds, on 2 separate counts:
they introducing physical field, logical exercise, should not concern physical aspects of database.
but in doing, effect have establish surrogate, physical thing, logical thing. poison mind.
there, straight science, pure logic, uncontaminated insane thinking, , immune frauds. no surrogates @ logical stage.
1. relational integrity (which relational model provides) distinctly different referential integrity (which sql provides, , record filing systems might have). if not understand this, please open new question "what difference ..." , ping me.
2. breaking rule has has undesirable consequences, beyond act itself.
3. if not understand this, please open new question "what relational navigation ..." , ping me.
so final answer question:
all elements above deal primary keys of relations natural keys - can leave them or replace surrogate keys.
in conceptual , logical exercise, deal logical keys only. physical concepts such surrogate illegal. replacement of logical key physical creature, in logical exercise rejected. use keys have, data, , natural.
not "replacement"
there 1 more point. term "replacement" incorrect. surrogate never replacement or substitute natural key.
one of many qualities natural key provides, row uniqueness, , too, demanded in relational model, duplicate rows not permitted.
since surrogate not key row (it physical pointer record), cannot provide required row uniqueness. if not understand saying, please read this answer, top false teachers. test given code exercises.
therefore, surrogate, if considered, @ physical modelling stage, additional column , index. not replacement natural relational key.
and conversely, if surrogate is implemented replacement, consequence duplicate rows, non-relational file, not relational table.
case 1
key attribute name - must of type char or varchar. names become key attributes.
yes.
often codes (users use codes). codes jump out @ (you have example in one more update). { d | r | b } { < | ^ | > }. of course towards end of logical model stage, when model stable, , 1 finalising keys , optimising them. stage earlier that, wide natural keys stand.
the idea keep meaningful.
keys have meaning (surrogates have no meaning). 1 of qualities of relational key is, that meaning carried, wherever key migrated foreign key.
and per example, wherever used. including program code. writing:
if crewtype = "backup" -- meaningful fixes value if crewtype = 1 -- meaningless
is plain wrong. because (a) not key, , (b) user may change value of datum backup reserve, etc. never write code addresses data value, descriptor. fact is, backup projection of key, exposition, , code key. resolves crewtype.name, , key crewtypecode.
if crewtypecode = "b" -- key, meaningful, not fixed while on keys, please note:
in relational model, have primary keys, alternate keys, , foreign keys (migrated primary keys).
we not have "candidate keys", no such thing defined in rm. manufactured outside rm. therefore non-relational.
worse, used people implement surrogates "primary keys" a.
a physical consideration b, 1 should understood , applied throughout exercise. when data understood , known, columns fixed length. when unknown, might variable. keys, given indexed, @ least on primary side, should never variable, because requires unpacking on every access.
a. use sql keyword primary key not magically transform surrogate pk. if 1 follows rm, 1 (a) determines possible keys (no surrogates), , (b) chooses 1 primary, (c) means election over, therefore (d) nominated candidates can no longer called "candidates", event history, therefore (e) remainder, non-primary keys, alternate keys.
"candidate key" refusal conform rm , nominate pk, therefore, in , of itself, non-relational. separate fact have surrogate "primary key", second non-relational item.
b. non-technical people believe no technical knowledge , foresight, no physical considerations @ all, should evaluated during logical, that's fine, evaluate them @ physical. since not addressing physical here, making note umbra.
magicians rely on tricks, make bunny rabbits lions. scientists not need them.
case 2
two (or more) identifying relationships become composite primary key of relation (which made of foreign keys).
i think have right idea, wording incorrect generic case.
that wording correct associative table, has 2 foreign keys. yes, in case, 2 fks form pk, needed row uniqueness. nothing can better that. addition of record id superfluous.
for generic case, table:
an identifying relationship 1 causes fk (migrated parent pk) part of pk in child. hence name, parent identifies child.
that makes child dependent 1 table, meaning child rows can exist in context of parent row. such tables form intermediate , leaf nodes in data hierarchies, majority of tables in relational database.
if row can exist independently, table independent 1. such tables form top of each data hierarchy, there few in relational database.
a non-identifying relationship 1 1 fk (migrated parent pk), not used form child pk.
compound or composite keys made of more 1 column, standard fare in relational databases. every table except top of each data hierarchy have compound key. if not have any, database not relational.
please read idef1x introduction carefully.
1. "theoreticians" not differentiate identifying vs non-identifying, or dependent vs independent: files independent; "relationships" between record pointers non-identifying. regression pre-1970's isam record filing systems, devoid of relational integrity, power, , speed. understand, can teach. fraudulently labelled "relational".
case 3
identifying relationship(s) weak key attribute(s) become composite primary key.
the term "weak" or without relationship "key" not defined in relational model. fiction of "theoreticians". cannot answer question.
i note of "theoretical" papers present strong keys (normal english word, describing fact key has been established previously) "weak", , weak "keys" (normal english word, describing fact "key" has not been established previously) "strong". such nature of schizophrenia.
therefore suspect part , parcel of evidenced attempt confuse science non-science, , undermine relational model. in old days, when such people locked up, humanity healthly. write books , teach in colleges.
case 4
associative entities have 2 or more identifying relationships
yes. 2 correct.
if have more two, not normalised. codd gives explicit method normalise that, such there 2 (or more) associative entities, of 2 identifying relationships each.
- "... therefore, n-ary (more two) relations ... can ... , should be, resolved binary (two) relations."
(paraphrased context)
so junction relations (junction tables).
no. "junction" relations , "junction" tables not defined in relational model, therefore non-relational.
associative entities in logical become associative tables in physical.
answer long
the completion of answer exceeded limit answers. therefore have placed answer in single document, , provided link. splitting answer @ point proved sin, document contains entire answer, consistent formatting, etc:
to continue point (ie. answer text, above), scroll down case 4 heading.
there value in retaining above answer text, not historical purposes, text searches, etc.
Comments
Post a Comment