Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Your final exam is based on the scenario below. Read the scenario and then answer the problems 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 ex, the database should store data indicating that four members of the Smith family attended the Crafts Fair. (It doesn’t matter which four members …)

problem 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.

problem 2: Design a set of 3NF tables for your database scenario.

problem 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, prepare a SQL query that will answer the query above.
• Using the relations you created for the church database, prepare the relational algebra that will answer the query above.

problem 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, prepare a SQL query that will answer the query above.
• Using the relations you created for the church database, prepare the relational algebra that will answer the query above.

Answer the following problems. You may use exs to support your discussion, but those exs must not duplicate those presented in the lessons or in your text books.

problem 5: Uncontrolled concurrent use of a database can lead to serious problems. What are these problems? Why do they occur?

problem 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. describe to the organization’s management the three types of problems you would expect to find in these relations.

problem 7: Discuss four major threats to database security. For each threat you identify, state at least one solution to that threat.

problem 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?

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M94857

Have any Question? 


Related Questions in DBMS

Itech 1006 - database management systems

ITECH 1006 - Database Management Systems Assignment Overview For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create t ...

Database design and use group assignmentassignment

Database Design and Use Group Assignment Assignment Description - In this assignment you will use your normalized "City Jail" database you have created in your assignment one. You will form a group (2-3 students) and imp ...

Project guidelinesoverviewthe final project for this course

Project Guidelines Overview The final project for this course is the creation of a database proposal and implementation plan report that will address the needs of a given scenario. The purpose of this assessment is to as ...

Database systemretail store database Database system Retail Store Database: Relationships

Database system Retail Store Database: Relationships • Conduct an in-depth examination and analysis of the database, Week2_RetailStore.accdb . Apply the concepts of database systems that you have learned this week. For e ...

Brief-the aim is that your team should produce a

Brief- The aim is that your team should produce a requirements document that describes the problem to be solved and captures the requirements for an information system that supports the needs of the vehicle pool manager ...

A quick guide to pivot tables is found at the end of this

A "Quick Guide to Pivot Tables" is found at the end of this document. Review it BEFORE beginning the questions. The data for this exercise (structured as a flat file) can be found in an Excel workbook located on Blackboa ...

Application creating and populating a databasesuppose you

Application: Creating and Populating a Database Suppose you have a small retail store and decide to create a database to track sales. You have information about recent sales that you want to use for testing the database. ...

1 objective named entity recognitionin this project you

1. Objective: Named Entity Recognition In this project, you will use scikit-learn and python 3 to engineer an effective clas- sifier for an important Information Extraction task called Named Entity Recognition. 2. Gettin ...

Design a relational database for a video rental store the

Design a relational database for a video rental store. The store, which rents only DVDs and has no sales other than DVD rentals, has approximately 5,000 customers and approximately 1,200 DVD titles. There are 25 supplier ...

1 what is the difference between a column constraint and a

1. What is the difference between a column constraint and a table constraint? 2. What is a recursive join? 3. Explain the difference between an ORDER BY clause and a GROUP BY clause. 4. What is a CROSS JOIN? Give an exam ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate