http://yunus.hacettepe.edu.tr/~tonta/courses/spring2004/dok322/project2004.htm/
Hacettepe University
Department of Information Management
DOK 322 Database Management Systems (Spring
2004) Yaşar Tonta
FINAL
PROJECT:
MODIFYING THE COOKIE DATABASE
(Worth 20 points; due 10 May 2004
09:00 a.m.)
You are already familiar with the
Cookie, a database of a union catalog of books, from your first MS Access
assignment. You used Cookie to obtain
bibliographic data about books as well as their whereabouts. Then you prepared as an assignment an Entity-Relationship (E-R) diagram for the Cookie database to better grasp its structure and relations
among different files (tables). However,
the Cookie database has a number of design limitations and therefore cannot
answer certain types of questions. For
example, one cannot enter multiple author names (for the same book) into the
BIBFILE. Cookie has no author authority
control. Certain types of information
such as edition and language are missing. Serial monograph titles cannot be
recorded . Cookie has no keyword searching capability. Moreover, books cannot be checked out (and
checked in) as that in a circulation system.
The
purpose of this assignment is to modify the Cookie database to improve its
design so that some of its drawbacks can be removed and it can answer more and
varied questions.
Here are some of the areas where Cookie could benefit from such
improvements:
1.
A separate authority
file for author names. Currently, all author names of a given book with more than one author cannot be
recorded. Such an authority file will
not only allow users to enter (and search for) multiple author names but also
provide authority control and consistency in author names. This file may contain corporate authors as
well.
2.
A keyword search
facility. In
addition to search by author, title and broad subjects, it would be useful to
add the keyword search facility to the Cookie database.
3.
A facility to record
multivolume works or serial monographs published periodically. Currently, each monograph in a
series is entered as a separate book. This may not create much of a problem if
each monograph has its own title. However, there is no way of distinguishing
books with the same title that come out regularly (i.e., Annual Review of
Information Science and Technology).
4.
A facility to circulate
books to patrons. Currently, books cannot be
checked out as there is no patron (borrower) file. By adding patron and circulation information,
Cookie can function as a circulation system.
You
may select one of the areas above and improve the design of the Cookie
database. You should identify the problems and propose solutions. The E-R diagram of the modified Cookie
database should be prepared. Evaluation
of your proposed solution will be based on whether it solved the problem
without introducing new ones.
The
Cookie database is already available in your folder under Network
Neigborhood/lib30/<dizin-no> in the Computer Lab. Go to the Lab and find your folder. You will be asked to enter a password. Enter your password assigned to you
earlier. You will be allowed to get
access to your folder.
Here
are the steps you should follow:
1.
Make your decision as to which area (1, 2, 3 or 4 given above) you wish to improve the
design of Cookie database.
2.
Identify problems and
propose a solution. Describe your solution as
detailed as possible.
3.
Prepare an E-R diagram
for the modified Cookie database. Use a drawing program to prepare it. Your
diagram should show the additions or changes that you make to the original
Cookie E-R diagram including table(s), fields, types of relations (1:1, 1:N,
M:N), keys, etc. Sometimes you might
need to move certain fields in the existing tables to the new one(s) that you
will create.
4.
Do not create a new
database. You
are to use the existing Cookie database to create the table(s) with their
attributes (fields) that you wish to add.
5.
Enter data in to the
tables that you created and the existing Cookie files
such as BIBFILE. The objective of this
project is not to
enter data.
So, between 10 and 20 records should be more than enough. It is important that the data you enter will
show the capabilities of the solution that you developed. For example, if you wish to create an
authority file, make sure you have enough records to show how you handle books
with more than one authors. Similarly, you need to create some data from
scratch (e.g., patron or circulation information).
6.
Create a data entry
screen for the table(s) you defined and save it. Do not use the default screen
format.
7.
Develop three queries
and run them against the database. Queries should be
answered by using the table(s) that you developed. Save the results of queries separately (as in
“Myquery1”, “Myquery2”, and “Myquery3”.
8.
Create a report using
the report generator. Report should get data from the
table(s) you developed (in addition to the existing ones). Save it.
9.
Evaluate the merits and
deficiencies of the database design and how you would improve it the next time.
Data
entry screen, queries, and the report should be saved on Cookie. Problems you identified and the solution you
proposed, along with your evaluation of the merits and deficiencies of your
design, should be written as a Word document and saved under your folder. The
E-R diagram that you develop should also be saved as a file under your folder.
(Use a drawing program to do this.)
GRADING: 50% identification of problems and proposed solutions; 50% E-R diagram
correctly depicting the proposed solutions, data entry screen(s),queries,
report generation, etc.
Good
luck.
Yaşar Tonta
Last updated: !9 February 2004