1) What is the difference between a primary key and a superkey in a DBMS? 2) Can a primary key and a superkey **both** have multiple columns? 3) Is a primary key a subset of a superkey or vice versa?

One of the differences between a candidate key and a primary key is that "candidate keys can contain nulls." I have been unable to find a more precise definition of what this means. One explanation I have seen is that in a CK with only one attribute, exactly one value can be NULL. Presumably this is because that NULL can uniquely identify a tuple/row. *Is this correct?* What I have had even more difficulty finding is what happens in a candidate key that contains more than one attribute: 1. I think I saw one reference state that **each attribute** can contain only **one** NULL. 2. However, by definition of CK, it seems there can be any number of NULLs as long as the set of attributes in the CK still uniquely identifies a single tuple/row. Which one is correct, or are they both wrong?

I have the following functional dependencies which includes all attributes of the relation: {AB -> C, BC -> AD, D -> E, CF -> B} CF supposedly is a candidate key. The task is: Show that CF is a candidate key given the functional dependencies listed ACF -> AB (Augmentation) AB -> C (Follows directly form functional dependencies) BC -> BAD (Augmentation) BC -> D (Decomposition) D -> E (Follows directly from functional dependencies) This came up as part of an algorithm, which has no name in my script. In line 10 the candidate key {CF} was discovered and I wanted to verify that it is indeed a candidate key. 1. LEFT = {F} 2. NOWHERE = {} 3. MIXED = {ABCD} 4. RIGHT = {E} 5. 2^MIXED = {{}, {A}, {B}, {C}, {D}, {AB}, {AC}, {AD}, {BC}, {BD}, {CD}, {ABC}, {ABD}, {ACD}, {BCD}, {ABCD}} 6. LEFT ∪ NOWHERE = {F} 7. {F}G+ (Closure) = {F} 2^MIXED = {{A}, {B}, {C}, {D}, {AB}, {AC}, {AD}, {BC}, {BD}, {CD}, {ABC}, {ABD}, {ACD}, {BCD}, {ABCD}} 8. {AF}G+ = {AF} 2^MIXED = {{B}, {C}, {D}, {AB}, {AC}, {AD}, {BC}, {BD}, {CD}, {ABC}, {ABD}, {ACD}, {BCD}, {ABCD}} 9. {BF}G+ = {BF} 2 MIXED = {{C}, {D}, {AB}, {AC}, {AD}, {BC}, {BD}, {CD}, {ABC}, {ABD}, {ACD}, {BCD}, {ABCD}} 10. {CF}G+ = {CFBADE} Candidate key! 2 MIXED = {{D}, {AB}, {AD}, {BD}, {ABD}} 11. {DF}G+ = {DFE} 2 MIXED = {{AB}, {AD}, {BD}, {ABD}} 12. {ABF}G+ = {ABFCDE} Candidate key! 2 MIXED = {{AD}, {BD}} 13. {ADF}G+ = {ADFE} 2 MIXED = {{BD}} 14. {BDF}G+ = {BDFE} 2 MIXED = {} I also have no idea why the author choose the description 2^mixed for the combinations of all the attributes

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value. I know some advantages, for example that there is no need to identify unique columns without NULLs for primary keys, and there is no need to manage composite primary keys, normal forms are easier to manage, and uniqueness is guaranteed. I want to know, is there any good reason regarding to performance, or index structures, etc. that should make us use real world factful keys instead of surrogate ones? Thanks.

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate key in any of the SQL database management systems, say SQL Server, Postgres, MySQL, Oracle etc.? Is there any reserved keyword for designating column(s) as a candidate key like PRIMARY KEY or UNIQUE in case of primary key column and unique column? I feel UNIQUE constraint itself provides implementation of the candidate key concept. I don't see any practical value of having a separate CANDIDATE KEY keyword. Is it so?

I am trying to build a database about *locations* like so: - Country → Region → Town Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users. Region may not apply to every location and so a blank string will be allowed (hence the many to many link table). This is the best design I have come up with: The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables. The town column on its own is not UNIQUE as you may have the same town name in multiple places. Is the towns table correct? The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)? I would welcome feedback if this approach is correct.

I am facing the following problem: I have a table with customer information like the *customer number*, i.e. customerID set up as the primary key, and, besides a number of other data, also a country value in the form of a *country code*, countryID. However, customer data from other countries should also be imported into this table. Although the customer numbers are unique within a country, they are not cross-country. Customer numbers should not be changed for organizational reasons. How can a key still be ensured for each customer in this table?

In the definition, it says *minimal key* is a minimal set of attributes needed to identify the table. Does the "minimal" part mean "minimal number of attributes" or "cannot be eliminated"? For example: - For a relation R(A, B, C, D, E, F), the closure of A, BC, and DEF are all {A, B, C, D, E, F}. Are they all minimal keys? Or is only A the minimal key?

Excuse the looseness of any definitions here, but I am trying to explore a simple concept. A __Primary Key__ uniquely identifies a row. There are possibly other columns in a table with unique values, so they can also uniquely identify a row (Candidate Keys), but the Primary Key is the one designated for the task. Properties which make a Primary Key more useful include: - guaranteed to be unique; some other unique column values may be duplicated as the table grows - unlikely to change; although Foreign Key constraints can include cascading, it is better not to require this - will not be recycled; a row which is deleted for some reason should not free the old PK value to be reused For these reasons, I generally recommend that the Primary Key have no intrinsic value, so there would never be a reason to change or recycle a value. That is, it should be otherwise __meaningless__. I have seen Primary Keys which include some sort of code, such as a client code based on the name. The obvious problems are (a) if the client name changes then the PK should change too, and (b) there is too much of a risk of conflict with clients with similar names. A half exception is the use of an auto-incremented number, which has a minor meaning of a sequence number. However, it is still stable. The question is, under what circumstances, if ever, is preferable to use a primary key with some other real meaning? That is, is there anything wrong with the advice that the PK should be arbitrary, and you can generally get by with a sequence number?