2http://yunus.hacettepe.edu.tr/~tonta/courses/spring2003/dok322/project2003.htm/
Hacettepe University Department of Information
Management
DOK 322 Database Management Systems (Spring 2003) Yaşar Tonta
FINAL PROJECT:
MODIFYING THE COOKIE DATABASE
(Worth 20 points; due 27 May 2003 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: 28 Nisan 2003