data modeling
1. Describe third normal form?
Level: Low
Expected answer: Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
2. Is the following statement true or false:
"All relational databases must be in third normal form"
Why or why not?
Level: Intermediate
Expected answer: False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
3. What is an ERD?
Level: Low
Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
4. Why are recursive relationships bad? How do you resolve them?
Level: Intermediate
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn’t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
5. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
Level: Low to intermediate
Expected answer: This means the two entities should probably be made into one entity.
6. How should a many-to-many relationship be handled?
Level: Intermediate
Expected answer: By adding an intersection entity table
7. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
Level: Intermediate
Expected answer: A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
8. When should you consider denormalization?
Level: Intermediate
Expected answer: Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
Level: Low
Expected answer: Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
2. Is the following statement true or false:
"All relational databases must be in third normal form"
Why or why not?
Level: Intermediate
Expected answer: False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
3. What is an ERD?
Level: Low
Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
4. Why are recursive relationships bad? How do you resolve them?
Level: Intermediate
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn’t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
5. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
Level: Low to intermediate
Expected answer: This means the two entities should probably be made into one entity.
6. How should a many-to-many relationship be handled?
Level: Intermediate
Expected answer: By adding an intersection entity table
7. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
Level: Intermediate
Expected answer: A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
8. When should you consider denormalization?
Level: Intermediate
Expected answer: Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
posted by dwhfaq | 1:51 PM
<< Home