ER-modelling

1. You are to plan a small student database. Following things are required:
   • The most essential information about the students, at least the ID number, e-mail address, name, address, phone numbers and the starting year of the studies must be recorded.
   • The names of the faculties and departments of the university must be included in the database as well as the information that tells in which department the students belong to.
   • Also the information about the courses is needed: The names of the courses, their codes, the amount of (ECTS) credits, the name of the department giving the course, the names of the students that have passed the course, when they have passed it (the date) and their credits.
2. So, let's draw the ER-diagram describing the database.

3. Entities

   First, find all the obvious entities.
   • The student
   • The department
   • The faculty
   • The course
4. Start Microsoft Excel. You'll find yourself drawing the diagram using the drawing tool in Excel.
   • Make sure you have the drawing tools visible (View|Toolbars|Drawing)
   • You can see the drawing tools at the bottom of the display. Select AutoShapes|Flowchart from them and drav a small rectangle to your worksheet.
   • Write "Student" (opiskelija) inside the rectangle. Align the the text to the center.
     
   • Copy the rectangle to the clip board and paste it three times to your worksheet. Name the three rectagles as Department, (Laitos) Faculty (Tiedekunta) and Course (Kurssi) the same way you just did with the student box.

5. Relationships

   Next we'll trouble our minds about the relationships between the entities.
   • Students take the exams associated with the courses.
   • Every student is a student in a department.
   • Every department is a part of a faculty.
   • Every course is given by a department.
6. Draw a diamod shaped figure to describe one of the relationships. Write "takes an exam" (tenttii) inside the figure and align the text to the center. Copy the formatted figure for every relationship.
7. Put the takes an exam relationship between the entities Student and Course. Connect the relationsip to the entities with the line that you'll find under the Autoshapes|Connectors menu.
   
8. Connect the other relationships and entities in the same way (järjestää = organizes, kuuluu = belongs to).
   

9. Cardinalities

   Cardinality indicates the amount of relationships an entity can have. The options are:
   • One to one (1-to-1) like a marriage (a person and a person)
   • One to many (1-to-M) / Many to one (M-to-1) for example the citicens and a municipality (a town and a person)
   • Many to many (M-to-M) for example borrowing a library item (a book and a person)
   In the database you are working on, the cardinalities go as follows:
   • Many students can take exams in one or several courses which indicates a clear M-to-M relationship.
   • A student is a student in one department and a department has many students. So, we are talking about a M-to-1 relationship. (Student, Department)
   • Every department is a part of one faculty. A faculty includes many departments which makes a M-to-1 relationship. (Department, Faculty)
   • A department organizes several courses and a course is organized by one department. We are happy to declare this as a 1-to-M relationhip (Department, Course)
   Mark the cardinalities to your ER diagram by writing either 1 or M between te entities (depending on the relationship, of course). Use the TextBox tool in Excel.
   

10. Attributes (Properties)

    Next step: Add the attributes belonging to every entity end relationship.
    • Student
      • ID number
      • E-mail address
      • Name (first and last)
      • Address (street, postal code, town)
      • Phone numbers
      • The year of starting the studies
    • Course
      • Name
      • Code
      • Credits
    • Department
      • Name
    • Faculty
      • Name
    • Exam
      • Date
      • Credits
11. Attributes are shown inside ellipses that are connected to the entity or the relationship with a line. Parts of the composite attributes have their own ellipses each and they are in turn connected to the composite attribute. Multi-valued attributes have double edges on their ellipses. Add all the attributes to you diagram. (Sähköpostiosoite = e-mail address, name = nimi, sukunimi = last name, etunimi = first name, puhelinnumero = phone number)

    Attributes connected to an entity

    

    Composite attribute

    

    Multi-valued attribute

    

12. Keys

    The attributes that uniquely identify an entity must be selected as candidate keys. When weak entities or relationships containing attributes are in question it may happen that their own attributes are not enough to identify them. Then some entites connected to these problematic items should be included in the key.
    • Student
      • ID number
      • E-mail address
    • Course
      • Name
      • Code
    • Department
      • Name
    • Faculty
      • Name
    • Takes an exam
      • The student, the course and the date combined
    The best of all candidates should be selected as the key. The best one is usually the smallest in size and the one that is most likely to stay unique. In order to improve the performance of the database, it is sometimes clever to create a new attribute that only serves as a key.
    • Student
      • ID-number ( = sotu) since it's likely to be shorter (always 11 characters) than an e-mail address. Also handling a field with fixed size is often more effective than handling one with varying size. ID number is also quaranteed to be unique.
    • Course
      • The code since it's shorter than the name and also unique for sure.
    • Department
      • Department ID: Let's make a new field that serves as a key. The name is too long to be a good primary key.
    • Faculty
      • Faculty ID: Same thing!
    • Takes an exam
      • Let's make a composite key that combines the Student, the Course and the Date. The date as such is not adequate for indentifying purposes as a student can participate many exams on the same day. Neither is the date + the course enough as several students can take the same exam on the same day. All the three combined will indentify the relationship since one student cannot take the same exam twice on the same day. When drawing the ER diagram it's enough to undeline just the attribute Date. The composite key itself will be made when turning the diagram to an actual relation of the database.
      Mark the keys in the ER diagram by underlining them.
      

13. Datatypes

    A datatype needs to be defined for every attribute. When selecting a datatype it is common to reserve the space for the data as well. Attention should be payed for reserving the space as there is nothing as annoying as too short a field for your last name... A datatype can be:
    • Numeric
      • Integer
        • INTEGER
        • SMALLINT
      • Real number
        • NUMERIC(precision, scale)
        • FLOAT(precision)
        • REAL
        • DOUBLE
      In the latest versions of database management systems it is possible to use the so called AUTOINCREMENT type which means that a numeric type can be defined as one that grows automaticly with a constant number. This type is commonly used for ID fields that only serve as keys. It is not necessary to put any value to such a field, it's good enough that every entity automaticly gets its own unique ID.
    • character string
      • Character string with fixed length
        • CHAR(length)
      • Character string with varying length
        • VARCHAR(length)
    • Time
      • Date (year, month and day)
        • DATE
      • Time (hour, minute and second)
        • TIME
      • Time stamp (year, month, day, hour, minute and second)
        • TIMESTAMP
      • Time interval
        • INTERVAL
    Write the attributes associated with every entity as small tables that have the name of the entity as the title. Write down the datatype, possible default value and possible restrictions and other remarks. You can underline or mark the key(s) in some other way. Restrictions that can come into question are for example
    • Only certain inputs are allowed. For example marking of an exercise can only be either "passed" or "failed".
    • Input value can only be between certain values like a numeric value between 1 and 100.
    • Special cases: The lecturer of the course cannot be a participant on the same course.
    • The value has an upper or a lower limit.
    • The correctness of the value is defined by a function or formula (like in ID number)

    Course

    Attribute	Datatype	Required	Default value	Restrictions
    Code	CHAR(6)	Yes		The first three characters are letters and the last three are numbers.
    Name	VARCHAR(64)	Yes
    Credits	FLOAT	Yes	1	Only integers and halves.

14. Save your ER diagram to drive U: to folder databases (tietokannat) as demo1.xls.
15. You own a company where every member of the personnell has a special professional title like the project manager or programmer or whatever. The staff participates in different projects. Every project has an orderer, one or several managers and employer or employers. Employers in the project are also classified by their job (programming, designing, testing etc.). Starting and finishing dates as well as a name are also associated with a project. The names of the staff and their phone numbers and e-mail addresses are also wanted in the database. Plan and draw an ER diagram of a database for your company. You can use the same Excel workbook as in previous exercise but use another worksheet.
    1. Find and draw the entities
    2. Draw the relationships
    3. Define cardinalities
    4. Add the attributes to the entities and relationships
    5. Select the keys
    6. Define the datatypes, requirednesses, default values and restrictions for the attributes
16. Ask the teacher (the person hanging around in the computer lab hoping someone to ask something) to check your diagram. Make corrections if necessary. Remember to save!
17. You are organizing a soccer tournament. What kind of a database would you need to help you? Write a list of the requirements including the properties needed in the database system and the data that needs to be recorded in it. Write it to a new worksheet and let the teacher check your list.
18. Draw an ER diagram based on your requirements and again, let the teacher check it.
19. Some examples for good solutions to this week's exercises will be handed out next week.