Reference no: EM132874979

Part 1

Questions:

1. What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity.

2. What is a strong (or identifying) relationship, and how is it depicted in a Crow's Foot ERD?

3. Given the business rule "an employee may have many degrees," discuss its effect on attributes, entities, and relationships. (Hint: Remember what a multivalued attribute is and how it might be implemented.)

4. What is a composite entity, and when is it used?

5. Suppose you are working within the framework of the conceptual model in Figure Q4.5.

Given the conceptual model in Figure Q4.5, answer the following:

a. Write the business rules that are reflected in it.

6. How would you (graphically) identify each of the following ERM components in a Crow's Foot model?

a. An entity
b. The multiplicity (0:M)
c. A week entity
d. a strong relationship
7. Discuss the difference between a composite key and a composite attribute. How would each be indicated in an ERD?

8. What is a derived attribute? Give an example.

PROBLEMS

1. Given the following business rules, create the appropriate Crow's Foot ERD.
a. A company operates many departments.
b. Each department employs one or more employees.
c. Each of the employees might or might not have one or more dependents.
d. Each employee might or might not have an employment history.

Part 2
1. What is an entity supertype and when it is used?

2. What is a subtype discriminator? Give an example of its use.

3. In what circumstances the composite primary key is appropriate?

4. Design the EERD for a media store that have a PRODUCT supertype containing attributes, Prod_Title, Prod_ReleaseDate, Prod_Price, Prod_Type. The subtypes can be BOOK, CD or MOVIE. The book subtype has specific attributes such as Book_CoverType, Book_PageCount, CD has specific attributes as CD_Genre, CD_Artist and Movie has specific attributes like Movie_Rating and Movie_Director.

5. Create a database for the scenario given in Question 4. Make sure that disjoint/overlapping constraints and partial/overlapping constraints are taken care of.

MULTIPLE CHOICE

1. Normalization works through a series of stages called normal forms. Typically ____ stages are processed.
a. 2
b. 3
c. 4
d. 5

2. Some very specialized applications may require normalization beyond the ____.
a. 1NF
b. 2NF
c. 3NF
d. 4NF

3 A table is in the 4NF if ____.
a. all attributes must be dependent on the primary key, and must be dependent on each other
b. all attributes are unrelated
c. no row can contain two or more multivalued facts about an entity.
d. no column contains the same values

4. A table that is in 1NF and includes no partial dependencies only is said to be in ____.
a. 1NF
b. 2NF
c. 3NF
d. 4NF

5. A table where every determinant is a candidate key is said to be in ____.
a. BCNF
b. 2NF
c. 3NF
d. 4NF

6. The ____ model views the data as part of a table or collection of tables in which all key values must be identified.
a. relational
b. object-oriented
c. conceptual
d. external

7. Normalization works through a series of normal ____.
a. schemas
b. entities
c. databases
d. forms

8. ____ yields better performance.
a. Denormalization
b. Normalization
c. Atomization
d. Compression

9. An attribute that is part of a key is known as a(n) ____ attribute.
a. important
b. nonprime
c. prime
d. entity

10. In a real-world environment, we must strike a balance between design integrity and ____.
a. robustness
b. flexibility
c. uniqueness
d. ease of use

11. When designing a database, you should ____.
a. make sure entities are in normal form before table structures are created
b. create table structures then normalize the database
c. only normalize the database when performance problems occur
d. consider more important issues such as performance before normalizing

12. Which of the following is not a valid normal form?
a. 1NF
b. BCNF
c. 3NF
d. MVNF

13. Given the table EMP_PROJ (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, HOURS), which of the following is a partial dependency?
a. PROJ_NUM --> PROJ_NAME
b. JOB_CLASS --> CHG_HOUR
c. PROJ_NUM, EMP_NUM --> HOURS
d. PROJ_NUM, EMP_NUM --> CHG_HOURS

14. A relation is not in 1NF if:
a. it has multiple candidate keys
b. all of the key attributes are defined
c. there are repeating groups in the table
d. all attributes are dependent on the primary key

15. Granularity refers to:
a. the size of a table
b. the level of detail represented by the values stored in a table's row
c. the number of attributes in a table
d. the number of rows in a table

16. All of the following are required for a table to be in BCNF except:
a. the table must be in 1NF
b. the table must be in 3NF
c. the table must be in 4NF
d. every determinant in the table must be a candidate key

Short Answer questions

1. Explain the BCNF. How is it related to other normal forms?

2. Explain normalization and its different forms.

3. Describe the dependency diagram and explain its purpose.

PROBLEM::

1. Given the dependency diagram shown in the following Figure 1, answer items 1a:

a. Identify and discuss each of the indicateddependencies.

2. Consider the database VIDEOS that you created in week 4. Check your tables for 3NF. If they are not in 3NF, modify your database so that your tables are devoid of partial and transitive dependencies.

Attachment:- Tutorial- Week.rar