Homework # 3
Information Resources Management
Make sure you document any additional assumptions you make in order to complete the E-R model. Your E-R models for #2 and #3 do not need to include attributes, but you should include attributes for #1.
1. Build an E-R model for the following (include attributes) (15 points)
Information is to be kept about students, courses and instructors. Details about each are as follows:
A student is identified by a student id. Information to be kept includes name, email address, address (consisting of street address, city, state, and zip code), college, major (only one allowed), minor (multiples allowed), and activity information (name, year joined, number of years member; multiples possible.) Students register for one or more courses.
A course is identified by a course number. Information to be kept includes title, minimum number of units, maximum number of units, and offering department. A course may have one or more different courses as prerequisites and may be a prerequisite for any number of other courses. Some courses have no prerequisites while others are not prerequisites for any other courses. Many students can register for the same course, but a course may not currently have anyone registered for it. Each course is taught by one instructor.
An instructor is identified by an email address. Information to be kept includes name, office number, phone number, and department. Instructors teach up to three courses at any time but may not currently be teaching anything.
2. Build an E-R model for the following (25 points)
Publishers publish many different types of professional journals and books. Some publishers only publish books, some journals, and some both. No book or journal is published by more than one publisher. An author may write books, journal articles, or both. A journal typically contains several articles, each one written by one or more authors. No article appears in more than one journal. Any journal may have one or more abbreviations, or none.
Every book and article is reviewed by several professionals in the field who may or may not be authors as well. Of course, an author never reviews his or her own books or articles. Each book reviewer and book author works for and is paid by a single publisher. Article authors and reviewers are not paid, however. Authors and reviewers who are not paid by a publisher are known as independent professionals.
Each journal has an independent professional who serves as the editor for that journal. The editor is responsible for assigning one or more (usually two or three) reviewers for each article.
3. Build an E-R model for the following (10 points)
3A (5 points)
A laboratory has several chemists who work on one or more projects. Chemists may use certain kinds of equipment on each project. The organization wishes to record the date when a given equipment item was assigned to a particular chemist working on a specified project. A chemist must be assigned to at least one project and one equipment item. A given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.
3B (5 points)
Redraw the model using only binary relationships. No ternary relationships or associative entities are to be used. If you cannot successfully represent all the information contained in part 2A above using binary relationships, explain what information will be lost.
Turn In: Your computer generated E-R models (4 or 3 plus an explanation).