Worksheet »

1. There are many possible answers to this question.  Use the suggested answers to assess your own attempts. (1) A book is an aggregation of pages (or chapters or sections etc.); (2) A university degree course is an aggregation of modules (or lectures); (3) A railway route is an aggregation of railway segments (or stations).

2. There are many possible answers to this question.  Use the suggested answers to assess your own attempts. (1) A reference book is a specialisation of a book; (2) a undergraduate degree is a specialisation of a university degree (or degree or qualification); (3) a railway train is a specialisation of a transport vehicle (or train or wheeled vehicle etc.); (4) a bus is a generalisation of a single-deck bus and a double-deck bus; (5) a computer printer is a generalisation of a laser printer and a dot-matrix printer.

3. Answer 1

4. To get to 2NF you must remove those non-key attributes which are dependent upon only invoice-number, going from:

   INVOICE LINE ITEM (invoice-number, line-number, stock-number, invoice-date, ISBN, book-title, price)

   to:

   INVOICE LINE ITEM (invoice-number, line-number, stock-number, ISBN, book-title, price)
   INVOICE (invoice-number, invoice-date)

   However if you now draw a functional dependency diagram you will notice that ISBN and book-title are functionally dependent upon stock-number (not the primary key) and so third normal form is violated.

   To get to 3NF you must remove those non-key attributes which are dependent upon another non-key attribute (stock-number) to get:

   INVOICE LINE ITEM (invoice-number, line-number, stock-number, price)
   INVOICE (invoice-number, invoice-date)
   STOCK (stock-number, ISBN, book-title)

   You may have decided to remove the attribute 'price' as well.  This would be perfectly reasonable if the assumption that 'books with the same stock-number were sold at the same price' was true.  If however the same books were sold at different prices on different invoices, then the answer shown above is correct.

5. (1) event; (2) guard; (3)  event; (4) state; (5) action; (6) state; (7) event; (8) guard.

6. (1) account opened is an event; (2) account goes overdrawn is an event (it represents the point at which the account goes from being in credit to being in debt); (3) clerk debits an account using a cheque is not an event, but an action- a correct event would be something like, account debited; (4) customer opens an account is not an event but an action- see (1) which is the correct event.  The thing to note here is that an event represents something which has happened, not something which is going to happen or is in the process of occurring.

7. Placing the event 'take order' is difficult because it can occur at any time during the book's life time (once it has been planned).  This is called an 'interleaved event' and can occur at any time and if you tried to place it on the model, it would occur after every existing event an an optional event.  The correct way to handle this would be to create a new class called 'book order'- in other words there is a new role for 'book'.  'Book order' would have a series of events such as 'take order', 'delivery books', 'send invoice' etc.