The Colonial Heritage Foundation (the Foundation) is a 501(c)(3) public charity dedicated to the preservation of the values, culture, skills and history of America's founding. To accomplish this mission, the Foundation engages in a broad array of activities. Among these are the development and presentation of educational exhibits, the coordination of reading and discussion groups to encourage the study of America's historical writings, the presentation of lectures regarding America's founding era, the coordination of historical reenactments and skill demonstrations, the coordination of internships and apprenticeships that teach the occupational skills of early America, and the coordination of public events that showcase the culture, skills, history, and values of America’s founding era.
The first public event the Foundation sponsored is the Colonial Heritage Festival. The festival is an annual event scheduled for the week of Independence Day. Running three or four days, the event attracts around 50,000 visitors each year. Held in the Scera Park in Orem, Utah, it has become the largest colonial living and reenactment event in the western United States. The festival comprises dozens of exhibits and presentations from more than 100 volunteers. For a feeling about the scope of the festival, visit http://festival.colonialheritage.org.
Katherine Hardy coordinates the participants who perform and demonstrate at the festival. Often festival participants are willing to contribute their time to demonstrate a colonial-era skill but lack suitable attire. Over the years, the foundation has built up a substantial wardrobe of period clothing. Some items have been donated and others have been made by volunteers. Additionally, there are many privately-owned articles of clothing that are on loan to the Foundation to support events like the Colonial Heritage Festival.
In the past, Katherine has coordinated loaning out these items to festival participants. As yet, there has been no systematic way to record which items have been lent or to whom they have been loaned. Kathrine has just relied on people’s personal integrity to return items. The problem is that people sometimes forget to return items they have borrowed and because there are no records kept, it is impossible to follow up with people who have borrowed items without returning them.
To solve the wardrobe problem, the board of directors of the Foundation have decided to build an information system to track when it lends an item to an individual as well as to track when that item is returned. Although the primary current need is to handle clothing items that are loaned to festival participants, Alan Rex (a member of the board of directors) proposed that it might make sense to use the Foundation’s wardrobe as a revenue source. At a meeting to discuss management of wardrobe lending, Alan said, “Once we have a system in place to really keep track of wardrobe items, we could rent them to anyone throughout the year.” As the board members continued to discuss the possibility another member suggested that the system should not be limited just to wardrobe. Any item that the Foundation controls could potentially be rented out. Genelle, another board member, raised a concern, “Some of our items are irreplaceable, and should not be lent out at all.” Alan responded, “not a problem, we could have a way to flag each item as being rentable or not.” As the board discussed the issue, they decided that making a general system to handle rentals made more sense than making a system just to handle wardrobe items.
In the language of the Foundation, an Item is anything that the foundation controls. The foundation owns some period artifacts, and a few more replicas. Still more are on loan to the Foundation, which already has an information system in place to keep track of the ownership of these items as well as how they are used to make up various exhibits. As yet, wardrobe items have not been recorded in the system. Wardrobe items are items in every sense, but there is more information that needs to be recorded for their effective management. As shown in the following partial system diagram, an Item class already exists that has attributes to record each item’s description and value as well as a relationship to a Legal Entity class, which records who owns the item. All of this is relevant for wardrobe items, these articles of clothing have a few more important attributes.
To manage wardrobe items effectively, Kathrine needs to know the size of the item, whether it is intended for men or women to wear (or if it is gender neutral), the color of the item, a description of the pattern in the fabric, and a start year and end year to indicate the period in which the article was in fashion. For example, one wardrobe item might be a size 42 green paisley waistcoat. The particular cut of the item was in fashion from 1750 through 1780. Waistcoats were only worn by men. It is the case with most Colonial American clothing that a particular kind of item was gender specific.
Identifying the size of items can be complex. Katherine has indicated that she would like to have a single “size” field that is numeric but also needs a “size modifier” field that is a text value that can be used to describe the size more precisely. So for breeches, the waist size would be recorded in the Size field while the length of leg and cuff circumference would be recorded in the Size Modifier field. Katherine has indicated that she would also like to be able to record a free-form note about any wardrobe item.
The rental system needs to allow for all items (whether they are regular items or wardrobe items) to be rented. However, each item needs some way to mark if it is available for rental. Additionally, each item needs to have a standard daily rental fee recorded.
When a rental transaction is initiated, several pieces of information need to be recorded. Firstly, the person agreeing to the rental terms needs to be recorded. This may be a paying customer, a person acting on behalf of an organization (which is obligated by the rental contract), or it may be “customer” who is borrowing an item to do something for the foundation, such as a participant borrowing a costume to wear at the Colonial Heritage Festival. Secondly, the agent of the Foundation who works with the customer to make the rental needs to be recorded. Each rental needs to have the date the rental was made as well as the due date of the items. All items rented on the same rental event will have the same due date. Additionally, each rental has a Discount Percent. When an item is being lent to a participant of an event such as the Colonial Heritage Festival, it is recorded as a rental with a 100% discount. Late fees and damage feels may still apply.
When an item is rented, there needs to be a way to note any significant damage or the general condition of the item so the customer is not held responsible for prior damage. Payment for the rental is received at the time of rental. In addition, credit card information may be taken if the customer is not a regular volunteer or otherwise known and trusted by the foundation; however, this information is kept in a paper file outside the information system.
The Foundation wants to know which of its agents checked in retuned items. It also wants to know the date each item was returned. Typically, all items rented together are returned together, but this is not always the case. When an item is returned, there needs to be a way to record if it has sustained any new damage.
The standard daily late fee is 5 percent of the item’s value. However, this may be reduced or waived at the discretion of the agent handling the return. When the item is returned, it is examined for damage and any damage fee is recorded at that point. Typically, these fees are collected at the time of the return; however, a return would be accepted without payment.
Build a UML class diagram to show the information needs for the proposed rental system. You will need to attach to the existing classes of “item,” “person” and perhaps others. This diagram can be found at the following URL: http://cases.colonialheritage.org/educational_exhibits_diagram.pdf
The description of the case can be found at this URL: http://cases.colonialheritage.org/Educational_Exhibits_Case.docx
You do not need to handle the recording of payments. The Colonial Heritage Foundation uses Quickbooks to handle its financial records and all payments are entered there. Your diagram should record amounts due for rentals as well as fees. Be sure to name your associations appropriately and to show both minimum and maximum cardinality constraints. Points will be allocated for neatness and readability.
Also produce a Structured Query Language script to build the database tables needed to manage the information identified in the diagram. Be sure to include constraints to enforce referential integrity. You do not need to build indexes for this assignment, just the table structure and constraints (primary key and foreign key). For each table your script references from the existing Foundation table structure, generate a new “create table” statement. When building the statement for a pre-existing table, you need not worry about any foreign keys associations outside your diagram. You are just recreating the tables so your foreign key constraints will work while you are testing your script.