Design of the data model (extra)
To get an idea of the entities and attributes involved, we make a list of the nouns from above description (underlined). To get an idea of the relations we list the verbs (italic). The library has several branch libraries scattered over the city, each with their own opening
A member may be enrolled in one of the branches and must be known with full name and address.
When a book is borrowed, the date is registered. When the book is returned this date is stored
We eliminate immediately a number of words: library indicates the complete database (we
eliminate also has) and is registered, is stored indicate remember, i.e. keep in the database.
We then get the following list.
We krijgen op die manier volgend lijstje
; branch scattered over the city
; branch with opening hour
; member is enrolled to branch library
; member is known with name and address
; member borrows book, borrow date and return date are associated here
; misleading: it concerns the address of the branch library: Address becomes an attribute and
BRANCH becomes an entity
; opening hour of branch: OpeningsHour becomes an attribute
; clearly a relation: “is member of” between the entities READER (member) and BRANCH
; misleading: it concerns the name and the address of a member. Name and Address become
attributes of READER
; clearly a relation “borrows” between the entities READER and BOOK (better between
READER and COPY, see above). In our (simplified) solution we will only use BOOK. ; borrow date and return date cannot be attributes of READER. A READER can borrow
several books and not all at the same time. We could them place as attributes of BOOK
(COPY) but what to do if a book is borrowed more than once (and we also want to store that
to calculate the fines)? In that case the attributes are attributes of the relation. This gives rise to the following ERD (after we have added some extra attributes). Design of the data model 1
Example: internal training
We rewrite the text from above.
; course has name (fixed), start date (can change), duration (fixed), teacher (can change),
location (can change), type (fixed; GEN, BLD of DSG), unique code (fixed)
; teacher is an employee
; employee enrols for a course implementation
; teacher assigns a score (11 to 10) to the course member (is employee)
; employee has name, first name, function, manager (again an employee, not necessarily the
head of the department), birth date, month salary, commission, department, unique
identification number (4 digits)
; department has number (2 or 3 digits), one location and a head (employee)
; every employee belongs to a department. A department can have many employees but not
necessarily has one.
; employee (of the sales department) receives a commission ; employee can receive a bonus
; scale has number (5 possibilities) and determines lower and upper bound of month salary and
bonus (the higher the scale, the higher the salary and the bonus) We have underlined the nouns that were further explained. These are our candidate entities. The
other nouns rather indicate attributes. We place the fixed data of a course in an entity COURSE
and the variable data in an entity IMPLEMENTATION. We then have the following.
; Code (unique code) ; StartDate
; Description (name) ; Location (where)
; Type (GEN, BLD or DSG)
; Length (duration of the course)
; EmployeeNr (unique identification ; DepartmentNr
Design of the data model 2
number) ; Name
; Name ; Location
We have the following relations.
; “EMPLOYEE works in DEPARTMENT”
; “EMPLOYEE teaches the course IMPLEMENTATION”
; “EMPLOYEE enrolls for a course IMPLEMENTATION” (*)
; “EMPLOYEE is assigned a score for course IMPLEMENTATION” (*)
; “EMPLOYEE is the direct manager of an EMPLOYEE”
; “EMPLOYEE is the head of a DEPARTMENT”
(*) has to do with the same relation. The score rather is an attribute of this relation.
There is also the self-evident relation between COURSE and IMPLEMENTATION namely
; “COURSE has as implementation IMPLEMENTATION”.
Some sentences do not give rise to a relation.
; “EMPLOYEE receives a commission” becomes no relation because commission can be an
attribute of EMPLOYEE.
; “EMPLOYEE receives a bonus” becomes no relation because bonus can be calculated using
month salary, scale and some extra conditions.
Design of the data model 3