Benevolent Oratory, Reading and Independent Notions Group:
The Benevolent Oratory, Reading and Independent Notions Group (BORING) has hired you as a consultant to modernize the file processing system that stores information about their poetry reading programs.
The data that are stored in their current data files are:
DATA ITEM CONTENTS
R_NUMB A unique number arbitrarily assigned to each reader
R_NAME Name of a person who has given or will give a poetry reading
R_ADDRESS Home address of an poetry reader
R_PHONE Telephone number of an poetry reader
WORK Title of a poem that has been, may be, or will be read
POET Author of a poem that has been, may be, or will be read
ISBN Book International Standard Book Number (unique throughout world; different editions of same title have differentISBNs)
BOOK_TITLE Title of a book in BORING’s library
AUT_ED Author or editor of a book in BORING’s library
EDITION Edition of a book in BORING’s library
PUBLISHER Publisher of a book in BORING’s library
YEAR Year of publication of a book in BORING’s library
PAGE Page on which a poem begins in a book in BORING’s library
PROG_NUMB A unique number arbitrarily assigned to each program
PROG_TITLE Title of a specific program at which poems have been or will be read
READING_DATE Date when a specific occurence of a program will or has taken place
PLACE Name of a location where a program can, has, or will be held
CITY City where a specific location for a program is found
CAPACITY Seating capacity of a particular location
Important note: One of the restrictions that have been placed on your works is that no further data items can be added. You may not create any arbitrary unique keys that are not already present! Use concatenated keys are needed.
You are given the following facts about the way in which BORING’s poetry reading programs are operated:
1) A program is a group of people getting together at one place on one date to read a selection of poems. It is identified by a program number.
2) Each person who reads poems can be at only one program in a given day.
3) Each person who reads poems can be at many programs during the year.
4) Each program of poetry readings is given at one specific location and on one specific date. (Note: this does not mean that the titles of programs are unique. For example, there may be an annual reading of Frost’s poems with the same title each year. The poems read each year, however, may be different.)
5) Each person who reads poems can read many poems during a single program, but each poem is read only by one person.
6) While each poem is read only once during a given program, it may be read at many programs during the year.
7) A program consists of the reading of many poems.
8) Each poem is read by a specific person, as part of a specific program, at a specific location, on specific date.
9) The titles of poems are not unique, though any given poet has written only one poem with a given name.
10) Each place at which programs are given holds only one program on any day, although the same program might be given on the same day at another location, using different readers and with different poems.
11) The names of places at which programs are held are not necessarily unique, but any given city will only have one location with a given name.
12) The BORING library holds many books, each of which contains many poems. Not every poem that appears in a book in the library has been read or is scheduled to be read.
13) A poem does not need to appear in a book in the BORING library to be read at a program. For example, readers sometimes read poems that they have written themselves.
14) A poem can appear in many books and, of course, a book contains many poems.
Question 1: Draw an ER diagram for your database scenario.
Question 2: Design a set of 3NF tables for your database scenario.
Question 3: Consider the following query.
“Show me a list of poets and poem names along with the names and phone numbers of the people who are going to read them on 12/29/00 at the Elks Hall in Metropolis, NY.”
a) Using the relations you created for the BORING database, write the sequence of relational algebra operations needed to answer the query above.
b) Using the relations you created for the BORING database, write a SQL query that will answer the query above.
Question 4: Consider the following query:
“Show me a list of the poem names and poets of all poems in the BORING library that have never been read or are not scheduled to be read.”
a) Using the relations you created for the BORING database, write the sequence of relational algebra operations needed to answer the query above.
b) Using the relations you created for the BORING database, write a SQL query that will answer the query above.
Answer two, and two only, of 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?