Your final exam is based on the scenario below. Read the scenario and then answer the questions that follow it.
Metropolitan City Church Management:
Metropolitan City Church Management (MCCM) has hired you as a consultant to modernize the file processing system that stores information about churches, priests, church members, and attendance at church events.
The data that are currently stored in the file processing system are:
Church_Name Name of a church
Church_Address Address of a church
Church_Phone Church’s phone number
Priest_Name Name of a priest
Priest_Birthdate Priest’s birthday
Title Priest’s title (rank)
Family_Number A unique identifier for a family
Family_Name Last name of a family that is a member of a church
First_Name_Head_of_Household First name of a head-of-household
First_Name_Significant_Other First name of the significant other of a head-of-household
First_Name_Child First name of a child
Birthdate_Child Birthdate of a child
Event_Date Date an event occurs
Event_Name Name of an event
Total_Attendance Number of people attending an event
Family_Attendnace Number of people from one family attending an event
Important note: One of the restrictions that have been placed on your work is that no further attributes can be added. You may not create any arbitrary unique keys that are not already present! Use concatenated keys where needed.
You are given the following facts about the situation:
• A church can have many priests assigned to it.
• A priest may be assigned to more than one church.
• A family belongs to only one church.
• A family has only one head-of-household; a head-of-household may have a significant other (spouse or other life partner). Of course, there may be more one than child in a family.
• An event is something that takes place on a specific date.
• A person can attend many events and (hopefully) an event is attended by many people.
• The church wants to know which families attended which events and how many people from each family were there. For example, the database should store data indicating that four members of the Smith family attended the Crafts Fair. (It doesn’t matter which four members …)
Question 1: Draw an ER diagram for your database scenario.
The ER diagram should use only three relationships:
- one to one relationship
- one to many relationship
- many to many relationship
please go through visual paradigm for making ER diagram using those relationships itself.
Question 2: Design a set of 3NF tables for your database scenario.
Question 3: Consider the following query.
“Show me a list of the last names of all the families who attended the Holiday Bizzare on December 12, 2000.”
• Using the relations you created for the church database, write a SQL query that will answer the query above.
• Using the relations you created for the church database, write the relational algebra that will answer the query above.
Question 4: Consider the following query:
“Show me the name and birthdate of all priests not assigned to a church.”
• Using the relations you created for the church database, write a SQL query that will answer the query above.
• Using the relations you created for the church database, write the relational algebra that will answer the query above.
Answer the following questions. You may use examples to support your discussion, but those examples must not duplicate those presented in the lessons or in your text books.
Question 5: Uncontrolled concurrent use of a database can lead to serious problems. What are these problems? Why do they occur?
Question 6: Assume that you have been asked to look at the database design being used by an organization. When you see the tables, you realize that they are all in first normal form; none are higher. Explain to the organization’s management the three types of problems you would expect to find in these relations.
Question 7: Discuss four major threats to database security. For each threat you identify, state at least one solution to that threat.
Question 8: Discuss the characteristics of an organization for which a data warehouse would be suitable. Aside from huge amounts of money, what resources does an organization need to support a data warehouse or data mart? For what purposes is a database warehouse best used?