By Diana Barnes,2014-01-19 22:47
12 views 0

     Design of the data model (extra)

    Example: library

    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

    We get

    ; 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

    ; FirstName

    ; Function

    ; MonthSalary

    ; Commission

    ; BirthDate

    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

Report this document

For any questions or suggestions please email