¢Submit the ERD including all entities, attributes, relations, and constraints.
University Bookstore
¢University Bookstore has several branches throughout Bloomington, and employees may be assigned to work at any of the branches.
¢Employees are identified by an ID and have a full name, date of birth, SSN, address, multiple phone numbers, a gender, and a hire date.
¢A Manager runs a branch and is responsible for all operations at that branch.
¢Employee cashiers sell books to customers. All purchases should have a date/time and receipt number recorded.
¢Books are identified by an ISBN number which identifies the title, one or more authors, a published date, a publisher, and an edition number. The International Standard Book Number (ISBN) is assigned to each edition and variation of a book.
¢Books are stocked on shelves by employee stockers. Each shelf has a location with in a branch, a capacity, and an ID.
¢The manager orders stock books from suppliers. The system must capture the date and invoice number of all orders. Suppliers have an ID, a name, a contact person, a website, an address, and a phone number.
¢Employees have exactly one mentor who helps them with any issues they may face. An employee may mentor more than one employee.
University Bookstore Changes
¢University Bookstore has decided to implement an emergency response training program at the request of their insurance company: ¢All staff are required to take a series of training classes on emergency procedures. Classes have an ID, a title, and a training level. ¢Each class has one or more accompanying exams which must be passed. Each exam has an ID, a title, a max score, and a passing score. If the employee fails an exam they must take the class again. ¢The system must keep track of each time employees take a class with exam. ¢University Bookstore sells used books as well as new ones. Each book has a condition, purchase price, and sale price. For each type of condition of book there is a total quantity in stock.
Note: This lab contains information that may not apply to the database such as workflow or job information.
