ERD Exercises
- Company Employees
A company has a number of employees, identified by Employee Numbers. The company wants to keep track of the employees’ names, addresses and ages. The company also has several projects. Projects are assigned a unique project identifier, and also have the attributes project name and start date. Each employee may be assigned to one or more projects, or may not be assigned to any projects. A project must have at least one employee assigned to it, and may have any number of employees assigned. An employee’s billing rate depends on the project he or she is assigned to. The company wishes to keep track of the employees’ billing rates on every project, and when the employees started to work on their projects.
- Courses
A university has a large number of courses in its catalog. Attributes of Course include Course_Number (identifier), Course_Name, and Credits. Each course may have one or more different courses as prerequisites, or may have no prerequisites. Similarly, a particular course maybe a prerequisite for any number of courses.
- Laboratory
A laboratory has several chemists who work on one or more projects. Chemists also may use certain types of equipment on each project. Attributes of chemists include employee ids, names and phone numbers. The lab also wants to keep track of project ids and start dates, and equipment serial numbers and costs. The organization wishes to keep track of the date a specific piece of equipment was assigned to a specific chemist to work on a specific project.
- Hospital
A hospital has a large number of registered physicians, each with a unique physician number. Physicians all have at least one specialty. Patients are admitted to the hospital by physicians. The hospital keeps track of the patients’ names and addresses, and assigns each patient a unique patient identifier. Any patient who is admitted must have one and only one admitting physician. Once admitted, a patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the date and time of the treatment.
- Attic Antiques
Attic Antiques buys and sells one-of-a-kind antiques of all kinds (e.g. furniture, china, clothing, etc.). Each item is uniquely identified by a serial number, and is also characterized by asking price and condition. Attic works with several individuals who sell and buy items from the store. Some clients only sell items to Attic, others only buy items, and some both buy and sell. Attic keeps track of its clients through the assigning of client numbers. They also keep track of clients’ names and addresses. When Attic sells an item to a client, they need to keep track of the actual selling price, the date of the sale, and the sales tax. When Attic buys an item, they wish to track the purchase cost, condition at the time of purchase, and the date.
- Robot Realty
Robot Realty is a real estate firm that lists property for sale. The firm has a number of sales offices in several states. They assign each sales office an office number. Each sales office is assigned one or more employees. An employee can only be assigned to one sales office. For each sales office, there is always one employee assigned to manage that office. The employee can only manage the office to which he or she is assigned. The firm lists property for sale. They need to track the property address, and which sales office the property is listed with (it may only be listed with one sales office). A sales office may have any number of properties listed. Each unit of property has one or more owners. Robot needs to track the percent of the property owned by each owner.
- Bank Accounts
WV Bank has three different types of bank accounts available for their customers. Each customer may have any number of bank accounts, and all accounts have a unique account number. For Savings Accounts, WV Bank must keep track of the account’s balance, interest rate, and the date the account was opened. Checking Accounts pay no interest, so the bank keeps track of just the balance and date opened. The third type of account, Loans, requires tracking the date the loan was taken, the balance due, and the interest rate of the loan. Loan Accounts are also assigned to a Loan Officer, who keeps track of the account and ensures that the client is making their payments on-time.
- Rental Car
A rental car agency classifies the vehicles it rents into four categories: compact, mid-size, full-size and sport utility. The agency wants to record the following data for all vehicles: Vehicle_ID, Make, Model, Year and Colour. There are no unique attributes for any of the four categories of vehicle. Vehicle has a relationship with a Customer entity type. None of the four classes of Vehicle has a unique relationship with an entity type.
- Non-Profit
A non-profit organization depends on a number of different types of persons for its successful operation. The organization is interested in the following attributes for all of these persons: Social Security Number, Name, Address, City, State and Telephone. Three types of persons are of interest: employees, volunteers and donors. Employees have only a Date_Hired attribute, and volunteers have only a Skill attribute. Donors have a relationship (named Donates) with an Item. A donor must have donated one or more Items, and an Item can only be donated by one donor. There are persons other than employees, volunteers and donors who are of interest to the organization, so a person does not have to belong to one of these groups. A person may also belong to one or more of these groups, at any one time.
- Mail Order Company
A mail order company takes orders from Customers. When a Customer calls in, they gather information regarding the Customer's name, address, and credit card number. Customers can be of two types – individuals and corporate. Corporate customers have a tax id number that has to be stored by the mail order company. The Clerk taking the order writes out a Product Order Form, on which they write out the Customer's credit card number (a customer can have more than one) and list the products that the Customer wants to order. A copy of a sample form can be found below:
ORDER FORM NUMBER: 2344 CUSTOMER CREDIT CARD: 234-5622-5678-1234
PRODUCT / PRODUCT NUMBER / COSTShovel / 354355 / $49.99
Rake / 35153 / $25.00
The products are kept in a back room in order by Product Number (each individual product has a product number – i.e. the product number does not refer to a class of products). They are eventually mailed to the Customer.
Desert Good Mail Order
Desert Goods Mail Order is a company that sells exclusive desert-related memorabilia over the Internet. Customers go to Desert Goods' web site and select items from an online catalogue (much like Amazon.com). Once they have selected the items that they wish to purchase, they enter in their Customer information (if they are a returning customer, they can just enter in their Customer Number). The program generates an invoice that looks like that shown below. This invoice is mailed with the products to the Customer, who is then expected to mail back a check for the correct amount. Desert Goods needs to track whether or not the Customer has paid the bill and the number of the check used for payment. At the end of each month, Desert Goods' management generates a report showing total sales for the month (broken down by Product). At the end of each year, the management needs a printout of each Customer's purchases for the year.
When a sale is made, Desert Goods' employees take the items out of inventory and send them to the customer with the invoice (as stated above). Each product has a bar code that is read into the system. The program automatically updates the inventory numbers when a product is sold. When a product reaches an inventory level less than five items in stock, the program automatically contacts the supplier of that product via EDI. Only one supplier provides each product. Desert Goods keeps track of each supplier's name, address and phone number. The system stores when (date and time) an order is placed to a supplier and what products were ordered. When the products arrive at Desert Goods, the bar codes are scanned in and the program updates the inventory levels.
Invoice
Invoice Number: 53623
Due Date:September 22, 2004
Customer Name:Rico Fata
Customer Number:8426
AD11 / Desert Sand Bag / $10.00 / 2 / $20.00
BC33 / Desert Cactus / $15.00 / 3 / $45.00
Total Due: $65.00
Please send a check for payment!
Peace Manufacturing
Peace Manufacturing needs an automated database management system to organize their information. Currently, all information is kept on paper and in filing cabinets. They have hired you to create the database. Here are the details:
Peace Manufacturing (PM) makes computers. Each computer is made of a base pc configuration, combined with various parts, including CPUs, cases and power supplies. Each part has a Part Number (e.g. all power supplies are Part Number 34). Computers can be either laptops or desktops. All computers are custom made, based on the order. An order looks like the following:
ORDER NUMBER: 21
DATE ORDERED: January 3, 2004
CUSTOMER NUMBER: 99
CUSTOMER INFO:
Leslie Smith
203 Smith Road
Morgantown, WV 26808
COMPUTER TYPE: Laptop
CONFIGURATION:
Part Number / Part Name / Cost34 / Power Supply / $55.29
23 / CPU / $190.00
12 / Case / $12.50
76 / Motherboard / $178.88
99 / Base PC / $499.00
TOTAL COST: $935.67
SALES REPRESENTATIVE: Sally Jones (# 1002)
PM wants to keep track of their customers and exactly what they have ordered, including which sales representative made the sale. Sales reps can be one of two types – Associates or Full. Full Sales Representatives are paid a salary, which needs to be stored in the database. Associate Sales Reps are paid on a commission basis only. PM needs to track the commission percentage for each Associate Sales Rep (it can be different for each). Each Sales Rep has multiple phone numbers.
They also want to keep track of their inventory of parts, including how many of each are in the inventory.