CS 307, Databases

Assignment 1, due Friday

Here is a situation where storing some information in a database might be useful.
Read the description, and then do the entity-relationship modelling, see below.

Once upon a time, in the tiny village of Hatley, there was a library, where people could borow books. It worked like this:

  1. You found a book on the shelves.
  2. You took it to the Librarian, sitting at an ornate desk crafted by Jean, an excellent local woodworker.
  3. The librarian took a card out of the book (or made a new card if necessary), stamped the date due on it and a slip at the back of the book, then put the card in a pocket with your name on it (unless you already had 3 unreturned books)
  4. If you returned a book, this process was basically undone.
Books need to be identified by title, possibly year, and author. (In case of multiple authors, this attribute can hold the first author, or both, for example, 'Kernighan & Ritchie').

As the "computer guy" I was asked to "put all this on the computer" (A Bishop's discard.) I declined. Today, I might do it, using database software.
(The larger village of North Hatley now uses a computer for this.)

E/R modelling (what to do)

  1. Identify the nouns in the descriptions. For example, 'library' is a noun, but it is what the data is about. Some of the other nouns are entities, while others are attributes of those entities.
    [Example: a person could be an entity with attributes of name, address, and driver's license number.]
    Identify the entities and their attributes.

  2. Choose a reasonable key for each entity class. A key consists of one or more attributes, which together uniquely identify each member of the class.
  3. Identify relationships between entities. [Not all entities are related. There is unlikely to be a meaningful relationship between chairs and windows in our classroom, nor does the problem ask which chairs were sat in by whom.]
    Should a relationship have any attributes, identify these.

  4. What is the cardinality of each relationship?

  5. Draw the E/R diagram. Use an arrow in the direction of any 1 in the cardinality. This has the sense of determines, for example, student determines (a unique) classroom in the dynamically changing relationship above.
    You may use UML diagram syntax if you prefer, however. (See pp 171-178 in the new edition)


How to submit

Draw a (hopefully legible) diagram on a piece of paper, and hand that in.

Or if you like typing, here is an example:

Use plain text and approximate the UML style, as I do here for the model in my video:

		enrol			  teach
   STUDENT  ---------------- COURSE  ------------------  PROF
	   0..7   |     0..*  	|     0..1	   0..*
		grade     	|
			   0..2 | has
				|
			   1..1	|
			       LAB  (weak entity)

To submit (a file)