1. From the following business rules draw the Crow’s Foot ERD. (5 points)
The business rules may be written as follows:
- A teacher can tutor many classes.
- Each class is tutored by one instructor.
- A teacher can advise many pupils.
- Each pupil is advised by one teacher
2. Explain mandatory participation in an entity relationship. (1 point)
3. What is a weak relationship? Provide an example. (2 points)
4. Describe the use of the INTERSECT operator. (1 points)
5. What is a key and how is it important in a relational model? Give example (3 points)
6. Define entity integrity. What are the two requirements to ensure entity integrity? (3 points)
7. Describe the three basic characteristics of Big Data databases. (6 points)
8. What do business rules require to be effective? (2 points)
9. List the 6 sources of business rules, and what is the database designer’s role with regard to business rules? (5 points)
10. What are the problems associated with file systems? How do they challenge the types of information that can be created from the data as well as the accuracy of the information? (5 points)
11. List and briefly explain any three functions performed by the DBMS that guarantee the integrity and consistency of the data in the database. (3 points)
12. List and describe the different types of databases in relation to site location and data structure. (5 points)
13. Create a Crow’s Foot ERD for each of the following descriptions. (Note: The word many merely means “more than one” in the database modeling environment.)
a. Each
of the OmwandoCo. Corporation’s divisions is composed of many departments. Each
of those departments has many workers assigned to it, but each worker works for
only one department. Each department is managed by one worker, and each of
those managers can manage only one department at a time. (2 points)
- During some period of time, a student can take many classes. Each of the class can be taken by many students during that period of time. (Hint: Only 2 entities) (2 points)
14. Create the ERD from the relationship diagram between DIRECTOR and PLAY shown in figure 14. For each entity identify all the keys, and business rules (5 points)
Figure 14 The Relational Diagram
15. Identify and describe the components of the table shown in Figure 15, using correct terminology. Use your knowledge of naming conventions to identify the table’s probable foreign key(s). (5 points)
FIGURE 15. The Ch03_NoComp Database EMPLOYEE Table
16 Based on the following business rules, draw the crow’s foot ERM with only 1:M relationship. (5 Points)
Hint: During some time interval, a DRIVER can drive many CARs and any CAR can be driven by many DRIVERs
Business rules:
- A driver may receive many (driving) assignments.
- Each (driving) assignment is made for a single driver.
- A car may be driven in many (driving) assignments.
- Each (driving) assignment is made for a single car.
17. From the Crow’s Foot ERD for the Benji Company write six business rules. (3 points)
Use the database shown in Figure Ch03_StoreCo to answer Problems 18-22.
FIGURE Ch03_StoreCo Database Tables
18. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. (3 points)
| TABLE | PRIMARY KEY | FOREIGN KEY(S) |
| EMPLOYEE | ||
| STORE | ||
| REGION |
19. Do the tables exhibit entity integrity? Answer yes or no and then explain your answer. Particularly explain in terms of unique values and “null” values. Is it unique? Can it be Null? (3 points)
| TABLE | ENTITY INTEGRITY | EXPLATION |
| EMPLOYEE | ||
| STORE | ||
| REGION |
20. Do the tables exhibit referential integrity? Answer yes or no and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. (3 points)
| TABLE | REFERENTIAL INTEGRITY | EXPLATION |
| EMPLOYEE | ||
| STORE | ||
| REGION |
21. Describe the type(s) of relationship(s) between STORE and REGION. And, create the ERD to show the relationships. (5 points)
22. Describe the type(s) of relationship(s) between EMPLOYEE and STORE. And draw the ERD to show the relationships among EMPLOYEE, STORE, and REGION. (Hint: Each store employs many employees, one of whom manages the store.) (6 points)
23. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure 23 into a dependency diagram that is in at least 3NF or BNCF. (2 points)
An initial dependency diagram depicting only the primary key dependencies is shown in Figure 23 below.
Figure 23 Initial dependency diagram for Problem.
In your answer you can show functional dependencies with arrows: i.e
Doc_EmpID –> (Doc_FName, Doc_LName, Doc_CellPhone) or draw using MS word tools for shapes
24. Use the dependency diagram shown in Figure 24 to work the following problems.
Figure 24 Initial Dependency Diagram for Problem 24
a. Break up the dependency diagram in Figure 24 to create two new dependency diagrams, one in 3NF and one in 2NF. (2 points)
b. Modify the dependency diagrams you created in Problem 24a to produce a collection of dependency diagrams that are all in 3NF. (Hint: You will have more than two diagrams). (3 points)
c. Modify the dependency diagrams you created in Problem 24b to produce a collection of dependency diagrams that are all in BCNF. (4 points)
25. a. What are the applications of ERD and normalization? Give an example. (1.5 points)
b. Briefly describe the conditions that must be met for a table(s) to be in 1NF, 2NF, 3NF, BCNF and 4NF. (2.5 points)
26. Bonus 2 points for neat work, responding to all questions and following instructions to the latter. (2 points)
