CIS4301 - Information and Database Systems I
home
syllabus
schedule
assignments
links

Assignment 1

Entity-Relationship Model

Some basic rules about the assignment:
  1. The assignment is due on [EXTENDED TO THURSDAY FEBRUARY 12th in CLASS::NO LATE SUBMISSIONS ACCEPTED] No submissions by email will be accepted, you must bring in a hard copy.
  2. This assignment is to be completed individually and although you are free to discuss it with your classmates, your design should be your own.
  3. You must use some type of software to draw your diagram and turn in the hardcopy/printout of it. No need to use a specialized software, something like Powerpoint should be enough. Many free options exist. If you are having trouble finding a software to use, ask your classmates or instructor for help. No hand-drawn diagrams will be accepted.
  4. Make sure to use all the tools provided by the ER model. You must specify all relationship cardinalities and any constraints that are required to fulfill the system specifications. Feel free to go above and beyond the specifications, be creative but make sure that your creativity doesn't violate the specification below.
  5. For this assignment, the instructor (Alejandro) will be your customer. So make sure to ask him any questions that would help you achieve a better design. As questions (and answers) pile up, they will be posted in the class website.

Design a database for a non-profit organization that takes in cars (as donations) and fixes the cars to give back to those in need in the community. Some cars are sold for profit to help fund other activities at the non-profit. These activities include a car repair academy, and an afterschool program for kids. The system must be able to handle all the donor, donation, sales, giveaway, and auto parts inventory information. The system must have security features to authenticate users that enter the system and limit access to certain parts of the system.

Donor Information
  1. The system must store information of donors which can be either individuals or companies. Information to maintain for all donors includes tax id, name, addresses (one or more), and phone numbers (one or more). Note that the tax id is not always supplied due to security concerns.
  2. For donors that are companies, the system must also maintain a list of individual contacts within the company. One and only one individual contact in each company must be marked as primary contact. For all individuals, notes must exist that would allow the user to know when and why that individual can be contacted.

Car Donation Information
     Car donations have several stages (that can be identified by a status):

  1. First the donor contacts the non-profit with the intention of donating the car. At this point a user must be able to record that "pledge" in the system. This can be referred to as a donation in "pledged" status.
  2. After this, a non-profit employee contacts the donor to arrange for pickup or delivery of the car. Once this is done, the donation is in "pickup" status. Pickup information must be entered and this may include a pickup location and time. Sometimes the donor will dropoff the car in which case the system must just keep track of the time when the donor promised to bring the car. This would allow users to call back donors to inquire about pledged cars.
  3. Often cars must be towed (at the non-profit's expense) to the non-profit's location. If this happens, the cost of the tow and tow company used must be recorded as part of the donation.
  4. Once the car arrives at the non-profit's location, the donation is said to be in "arrived" status. At this point, the car has to await an initial inspection.
  5. After the initial inspection, the car is tagged as "salvage" or "keeper". Salvage cars are used for their parts, keepers are ultimately fixed to be given back to the community or sold.
  6. After the car is tagged, the donation status is set to either "salvage" or "awaiting repair" for cars tagged as salvage and keeper respectively.
  7. Once a keeper is repaired, another decision is made and the car is tagged for "giveaway" or "sale". The donation status is then set accordingly to "awaiting giveaway" or "awaiting sale".
  8. Once all parts have been harvested from a salvage car, the car is tagged as "ready for disposal", the donation at this point is considered "finalized".
  9. Once a car that was repaired is either sold or given away, the car is tagged accordingly and the donation is considered finalized.
  10. For every finalized donation, the non-profit must determine the value of the car. This value is later used to provide donation data to the IRS. Donors will often call to inquire about the value of their donation, so that they can include it in their tax filings.
  11. For all cars, the system must keep information about their make, model, year, color, mileage, notes on the condition, VIN, and seating capacity.
  12. For all donations, the system must keep track of the donor, the car donated, the date of donation, and a log of each status change as described above. For each status change, the log keeps a date and notes about it.

Car Sale Information
  1. The following information is stored as part of a car sale:
    • Sale date
    • Sale price
    • Sale conditions
    • Buyer information: Name, Address, SSN, Phone Number

Car Giveaway Information 
  1. When people are approved for a giveaway, and even before a car is available for them, their information is entered into the system. The following is the information that is kept for customers:
    • Name, SSN
    • Home Address and Ph#
    • Work Address and Ph#
    • Notes on the customer's "need". Here the system user will input information about what type of car the customer needs (seating capacity, special needs, etc.).
  2. Once a car becomes available, the car is given away to the chosen customer and the action is recorded. For the giveaway, the following information is entered:
    • Date of giveaway
    • Conditions of giveaway (for example, often a 6 month warranty is provided on giveaway cars).
  3. Maintenance records are also kept on all giveaway cars as customers can bring their cars in for oil changes and basic maintenance. Every time a customer brings in his or her car for maintenance the record is entered with a date, cost, and notes on what was done on the vehicle.

Auto Parts Inventory
  1. The auto part inventory keeps track of all parts taken from cars that have been tagged for salvage.
  2. When a part is taken from a car, the following information is entered about the part:
    • Car that the part was retrieved from
    • Part Type, not that there is a set of specific part types such as axle, clutch, piston, timing belt, etc.
    • Notes on condition (good condition, excellent, details, etc.)
    • Notes on specific details of this type of part as retrieved that might be car or instance dependent. This would allow a user to determine whether this part might work as replacement in a different vehicle type.
    • Storage index, a code that will allow someone to locate the part if needed (like a book call number)

System Security
  1. The system must provide a way to authenticate users  (i.e., login and password)
  2. Whenever a new record is created, the system records the creation date and the user who created the record. For example, if a auto part is entered into the inventory, the user who entered it and the date entered is recorded.
  3. Users are restricted to access the system by module. For each module (donors, car donations, sales and giveaways, auto parts inventory), the user can have no access, read-only access, or read/write access.