Question

Databases

a) For person entities, under what circumstances would Social Security number (SSN) NOT be a good, unique key? Think globally...

b) Why would Name be a potentially good key attribute for an organizational entity like Department, but not for people?

c) What is a potential advantage of creating an index for an attribute?

d) Why not create an index on every column that could conceivably be used to look up particular rows?

Answer 1

Answer)
a) For the entities persons, the circumstances would Social Security number (SSN) NOT be a good, unique key, will be the circumstances in which the same SSN is there in different countries. It maybe possible that the same SSN is used in US and Canada for differentiating 2 persons in each country, which is not valid globally.

b) The name will be a potentially good attribute for the Department entity as there will be only one department of the unique names. Thus different departments will surely have different names. Whereas different people can have the exact same names, thus they cannot be the key attribute.

c) The potential advantages of creating an index for an attribute will be that the indexes make the queries run faster than non-indexed. Indexes such as the primary key and also the unique index make to avoid duplicate data.

d) Indexing on every column does not make any sense as it defeats the purpose of indexing of the columns in the database. The insertion and updation on such a table again will be slower as these changes will cause an update of all indexes on the table. All indexes take up more space on the table, thus creating an index on every column is not advisable.