Resource Library Item.

re: 05/23/02
The resource library will have many different types of items in it including books, videos, computer software, magazines, etc. In order to store all these different types of items, the database was designed to be as flexible as possible. The items will be stored in a series of eight database tables as depicted below.

Table Definitions:

A summary of the table definitions depicted in the ER diagram above, is listed below. The complete definition of these tables can be found on the Max Mart, Inc. project web site and will be included in the final RS documentation.

There are three groupings of tables. The RS_MEDIA_TYPE, RS_CATEGORY, and RS_ISSUE_CATEGORY are used to describe a resource center object. The RS_COMMENT and RS_CONTENT tables add information about a resource center object. The RS_ITEM, RS_ISSUE, and RS_COPY tables make up a resource center object.

Table: RS_CATEGORY

Description: Holds the categories resources are grouped by in the book. This table is recursive because it holds sub-categories. The category id is a number that is generated by the system. The user will usually not ever see this number. The UTITLE column is the Upper Case of the TITLE column. This allows searching on the category easier. Also, automatic ordering in alphabetical order is possible.

Columns:

Table: RS_ISSUE_CATEGORY

Description: Intersecting table between Category and issue. Category the Issue belongs to. An ISSUE can be in many CATEGORIES but in a category only one time. The part of a resource item that is tied to the category structure is the ISSUE. The user will never see these values directly.

Columns :

Table: RS_MEDIA_TYPE

Description: Holds the type of media, such as vhs, books, slide, lesson plan, report of the ITEM.The user will determine the ID value of the column. The ID will always be stored in Upper Case. The UNITS column holds a description of the units that will be used to enter CONTENT records about the ISSUE.

Columns :

Table: RS_ITEM

Description: Holds the items available in the resource center. An ITEM is a classification of possible many physical copies available for check out.An ITEM is the main classification level, followed by an ISSUE, and finally by a COPY.

Columns :

Table: RS_ISSUE

Description: Holds the ISSUE (Volume) information about a particular ISSUE of an ITEM. The ISSUE.ID must be unique to distinguish between issues. This table is for items that are published annually, monthly, etc. If an ITEM is a book that has only one ISSUE, it must still have a record in the ISSUE table.

Columns :

Table: RS_COPY

Description: This is the physical item loaned out. The COPY is what is stored on the shelf in the resource library. It will (may) have a BARCODE on it. The BARCODE value is required for the item. BARCODES can never be duplicated. An ISSUE may have many COPIES.

Columns :

Table: RS_CONTENT

Description: Holds the content of this issue of an item. An issue can have multiple segments for example, a vhs tape that has 10 minutes on one topic and 35 on another. The DURATION is in the units that are specified for the RS_MEDIA_TYPE.UNITS column.An ISSUE can have as many CONTENT records as MFS would like. Each CONTENT record is for exactly one ISSUE.

Columns :

Table: RS_COMMENT

Description: Contains users comments.A user can have one comment on each issue for each day.

Columns :

Examples of resource center objects

Pump Maintenance Book.

To store a book in the database, MFS will need to create an ITEM record. The ITEM has a title and a description. Once the ITEM is entered, you will need to create an ISSUE record for the book. This also has a title and a description. You will then create a COPY record for the book. The COPY record has the book’s ID and BARCODE. If desired, you could enter CONTENT records for this book indicating the chapter titles and descriptions of what the chapter covered. Below is a block diagram of what would be entered into the database to store this book. The book has a two copies and two content records. One copy is GOOD, which allows it to be reserved and checked out. The other is marked as lost so it is not available.

Wildfire VHS Library.

To store a video library consisting of multiple issues (one for each month) and multiple copies (2 tapes each), you will have to start with an ITEM record.The ITEM record will hold the title and description of the entire VHS library series. You will then make a record in the ISSUE table for each month’s release of the video. Finally, you need to make COPY records for each physical VHS tape that you have for that month. In order to distinguish the different ISSUES, you may want to enter CONTENT records for each one. These would describe the topics covered that month. Below is a block diagram of what this may look like when entered into the Resource Library database.

Resource Library Catalog

When the above items are listed in the resource library catalog, they will have an entry for each ISSUE. The ITEM information will be listed with the ISSUE. This will work nicely for the VHS library example above as each ISSUE will be listed with the title of Wildfire VHS Library and the description, along with each ISSUE’s title and description. The BOOK, however, may have its title and description listed twice.

For example, the Wildfire VHS library will have two entries, one for each ISSUE:

The BOOK entry on the other hand would look like: