Database Design Section 6 Study Guide

When all attributes are single-valued, the database model is said to conform to: -2nd Normal Form -4th Normal Form -1st Normal Form -3rd Normal Form

-1st Normal Form

Examine the following Entity and decide which rule of Normal Form is being violated: ENTITY: CLIENT ATTRIBUTES: # CLIENT ID FIRST NAME LAST NAME ORDER ID STREET ZIP CODE -1st Normal Form. -2nd Normal Form. -3rd Normal Form. -None of the above, the entity is fully normalised.

-1st Normal Form.

Examine the following Entity and decide which rule of Normal Form is being violated: ENTITY: CLIENT ORDER ATTRIBUTES: # CLIENT ID # ORDER ID FIRST NAME LAST NAME ORDER DATE CITY ZIP CODE -1st Normal Form. -2nd Normal Form. -3rd Normal Form. -None of the above, the entity is fully normalised.

-2nd Normal Form.

What is the rule of Second Normal Form? -No non-UID attributes can be dependent on any part of the UID. -Some non-UID attributes can be dependent on the entire UID. -All non-UID attributes must be dependent upon the entire UID. -None of the above

-All non-UID attributes must be dependent upon the entire UID.

A UID can be made up from the following: (Choose Two)(Choose all correct answers) -Entities -Attributes -Synonyms -Relationships

-Attributes -Relationships

Where an entity has more than one attribute suitable to be the Primary UID, these are known as _____________ UIDs. -Composite -Simple -Secondary -Candidate

-Candidate

If an entity has a multi-valued attribute, to conform to the rule of 1st Normal Form we: -Create an additional entity and relate it to the original entity with a 1:M relationship. -Make the attribute optional -Create an additional entity and relate it to the original entity with a M:M relationship. -Do nothing, an entity does not have to be in 1st Normal Form

-Create an additional entity and relate it to the original entity with a 1:M relationship.

Examine the following entity and decide how to make it conform to the rule of 2nd Normal Form: ENTITY: RECEIPT ATTRIBUTES: #CUSTOMER ID #STORE ID STORE LOCATION DATE -Do nothing, it is already in 2nd Normal Form. -Delete the attribute STORE ID -Move the attribute STORE -LOCATION to a new entity, STORE, with a UID of STORE ID, and create a relationship to the original entity. -Move the attribute STORE LOCATION to a new entity, STORE, with a UID of STORE LOCATION, and create a relationship to the original entity.

-Move the attribute STORE -LOCATION to a new entity, STORE, with a UID of STORE ID, and create a relationship to the original entity.

To resolve a 2nd Normal Form violation, we: -Move the attribute that violates 2nd Normal Form to a new entity with a relationship to the original entity. -Move the attribute that violates 2nd Normal Form to a new ERD. -Do nothing, an entity does not need to be in 2nd Normal Form. -Delete the attribute that was causing the violation.

-Move the attribute that violates 2nd Normal Form to a new entity with a relationship to the original entity.

Examine the following Entity and decide which rule of Normal Form is being violated: ENTITY: CLIENT ATTRIBUTES: # CLIENT ID FIRST NAME LAST NAME STREET CITY ZIP CODE -1st Normal Form. -2nd Normal Form. -3rd Normal Form. -None of the above, the entity is fully normalized.

-None of the above, the entity is fully normalized

When data is only stored in one place in a database, the database conforms to the rules of ___________. -Reduction -Normality -Multiplication -Normalization

-Normalization

When data is stored in more than one place in a database, the database violates the rules of ___________. -Decency -Normalcy -Replication -Normalization

-Normalization

Examine the following entity and decide which attribute breaks the 2nd Normal Form rule: ENTITY: RECEIPT ATTRIBUTES: #CUSTOMER ID #STORE ID STORE LOCATION DATE -STORE LOCATION -CUSTOMER ID -DATE -STORE ID

-STORE LOCATION

A candidate UID that is not chosen to be the Primary UID is called: -Composite -Secondary -Simple -Artificial

-Secondary

Which of the following would be suitable UIDs for the entity EMPLOYEE: (Choose Two) -Social Security Number -Last Name -Employee ID -Address

-Social Security Number -Employee ID

When any attribute in an entity is dependent on any other non-UID attribute in that entity, this is known as: -Functional dependency -Dependency -Transitive dependency -Non-dependency

-Transitive dependency

A transitive dependency exists when any attribute in an entity is dependent on any other non-UID attribute in that entity. True or False?

-True

An entity can only have one Primary UID. True or False?

-True

If an entity has no attribute suitable to be a Primary UID, we can create an artificial one. True or False?

-True

The Rule of 3rd Normal Form states that No Non-UID attribute can be dependent on another non-UID attribute. True or False?

-True

When is an entity in 2nd Normal Form? -When all non-UID attributes are dependent upon the entire UID. -When attributes with repeating or multi-values are present. -When no attritibutes are mutually independent and all are fully dependent on the primary key. -None of the Above.

-When all non-UID attributes are dependent upon the entire UID.

The candidate UID that is chosen to identify an entity is called the Primary UID; other candidate UIDs are called Secondary UIDs. -Yes, this is the way UID's are named. -No, each Entity can only have one UID, the secondary one. -No, it is not possible to have more than one UID for an Entity. -No, after UIDs are first sorted, the first one is called the Primary UID, the second is the Secondary UID, etc.

-Yes, this is the way UID's are named.

A unique identifier can only be made up of one attribute. True or False?

False

An entity ORDER has the attributes Order ID, Order Date, Product id, Customer ID. This entity is in 1st Normal Form. True or False?

False

An entity can have repeated values and still be in 1st Normal Form. True or False?

False

As a database designer, you do not need to worry about where in the datamodel you store a particular attribute; as long as you get it onto the ERD, your job is done. True or False?

False

To convert an entity with a multi valued attribute to 1st Normal Form, we create an additional entity and relate it to the original entity with a 1:1 relationship. True or False?

False

Any Non-UID attribute must be dependent upon the entire UID. True or False?

True

Normalizing an Entity to 1st Normal Form is done by removing any attributes that contain multiple values. True or False?

True

There is no limit to how many columns can make up an entity's UID. True or False?

True