Sample Entity-Relationship Problems

A person, identified by a PERSON-ID and a SURNAME, can own any number of vehicles. Each vehicle is of a given MAKE and is registered in any one of a number of states identified by STATE-NAME. The registration number (REG-NO) and the registration TERMINATION DATE are of interest, and so is the address of a registration office (REG-OFFICE-ADDRESS) in each state.


An organization purchases items from a number of suppliers. It keeps track of the number of each item type purchased from each supplier, and it also keeps a record of suppliers' addresses. Items are identified by ITEM-TYPE and have a DESCRIPTION. There may be more than one such address for each supplier, and the price charged by each supplier for each item type is stored. Suppliers are identified by SUPPLIER-ID.

Each person keeps a record of documents of interest. The time and source of each document are stored, along with its location. Documents may be books, identified by author and title; journal articles, identified by journal volume and number, author, and title; or private correspondence, identified by sender and date.

Each building in an organization has a different BUILDING-NAME and a BUILDING-ADDRESS. The meeting rooms in each building have their own ROOM-NO in the building, and each room has a specified SEATING-CAPACITY. Rooms are available for hire for meetings, and each hire period must start on the hour. The hour and LENGTH-OF-USE are recorded. Each hire is made by a group in the organization, and groups are identified by a GROUP-NO and have a CONTACT-PRONE. The facilities required for each hire period also are recorded. Each facility has an EQUIP-NO and a DESCRIPTION.

Persons identified by a PERSON-ID and a SURNAME are assigned to departments identified by a DEPARTMENT-NAME. Persons work on projects, and each project has a PROJECT-ID and a BUDGET. Each project is managed by one department, and a department may manage many projects. But a person may work on only some (or none) of the projects in his or her department.

Instructors may take a special loan of textbooks for courses that they teach. Each instructor has an INSTRUCTOR-NAME and a ROOM-NO, and each course has a COURSE-NAME and a COURSE-OUTLINE. Each textbook has a CALL-ID and a TITLE. More than one instructor can be assigned to a course, and more than one textbook can be used in each course. The books are assigned to the course, and an instructor always gets a special loan of all the books assigned to each course that he or she teaches.

Would your E-R diagram change if you discovered the follow- ing information?

  1. An instructor may also obtain a special loan of books other than those assigned to courses taught by that instructor.
  2. Instructors obtain a special loan of only some of the books assigned to a course.
Treat (1) and (2) as two separate cases.

A library system contains libraries, books, authors, and patrons with key attributes libno, bookno, authno, and patno respectively. Libraries are further described by a libname and location, books by a title and page count (pages), authors by an authname, and patrons by a patname and pat- weight. Author-book is a one-to-many relationship, library-book is a many-to-many relationship. A library can hold many copies of a book, and a book can appear in many libraries. A book and a library enter into a relationship grouping by sharing a copy. This relationship instance has a cost attribute, which states how much the library paid for the book. Patron-library and patron-book are many-to-many relationships. When a patron borrows a book, the transaction creates an instance of the patron-book relationship and attaches a duedate attribute to it.

An engineering company has several projects in progress. Each occupies the full attention of sev- eral engineers. Moreover, each engineer is working on at least one project, and each project has at least one engineer. Each engineer has a crew of laboratory assistants who report only to that engi- neer. No assistant is without an engineering supervisor. Furthermore, each engineer supervises at least two assistants. Each laboratory assistant has exclusive ownership of a collection of instru- ments. Each instrument is owned by an engineering assistant. However, a particular assistant may own no instruments. The key attributes of project, engineer, assistant, and instrument are pro, eno, ano, and mo respectively. Each entity is further described with a name: pname, ename, aname, or iname.

A hospital stores data about patients, their admission and discharge from departments and their treatments, For each patient, we know the name, address, sex, social security number, and insurance code (if existing). For each department, we know the department's name, its location, the name of the doctor who heads it, the number of beds available, and the number of beds occupied. Each patient gets admitted at a given date and discharged at a given date. Each patient goes through multiple treatments during hospitalization; for each treatment, we store its name, duration, and the possible reactions to it that the patient may have.