Database Design (En)

In this lesson we are going to practice to create a datamodel. When developing software a good datamodel is very important, it is the foundation of the application. Any mistake in the datamodel could lead to large issues in the application later.

A good applicaiton begins with a well designes datamodel!

Introduction

The steps you have to follow in order to create a datamodel is called normalization.

An ERD, the Entity Relation Diagram, is schematic representation of the datamodel.

An ERD consists of entities, attributes and relations and keys.

Entity

These are (almost always) people, things or events you want to store information about. For exampl, students (people), sickness notification (event), an article (thing).

Attributes

Atrtibutes are characteristics of entities. For example, the name of a student, the date of a sickness notification or a price of an article.

Relaties

Relations are connections between entitiies. You can indentify relations by creating a sentence like :

Each student has registered 0,1 or more sickness notifications.

Every sickness notifactions is about excactly one student.

This example is an 1:N realtion. One student has 0,1, or more sickness notifications and every sickness notiifcation has excactly 1 student related to thsi notifaction. So we say the relation student:sickness notification = 1:N

Keys

Keys are (special) attributes that are needed in order to create the relation.

To begin with,every entitiy has an primary key. The primary key uniquely identifies an instance of the entity. For example, a student number (identifies a student) or a license plate number (identiefies a car).

The foreign key is an atrtibute in the entity that has an N:1 relation to another entitiy. The Foreign key is a copy of the primary key it is refering to. In the above mentioned example the sickness notification entity should get an FK with consists of the student number. This way the realtion is made to excactly one student.

How to create an ERD?

Every assignment begins wit a story, we usually call this the case. Start by reading the case and determine what you want to register in the database. Try to identify the entitiies (people, things and events). Determine of every entitiy what to register, these are the attributes of the entity. Determine of every attribute the data type (integer, float, date, time, datetime of boolean). Determine the realtions. Determine or create the primary keys (PK's) and insert the foreign keys (FK's).

The steps are:

Identify the entities,
Determine what you want to register of every entity, these are called the attributes.
Determine teh data-type of every attribute.
Determin the relation between the entities.
Determine the PK's en FK's.

Datamodels can be compiled in Lucichart, when the model is ready you can export a PDF file.

In order to create an ERD in Lucichart you have to create a free account. Once registered, search for the ERD-template "Database ER Diagram", see picture above.

Naming conventions

The ERD is created in the English language. For naming entity- and attributes names are Snake Case (in lower case) is used, for example:

Entiteit:  user
Attribuut 1: user_name
Attribuut 2: user_login_count
Attribuut 3: last_login_date
Attribuut 4: password

Also note that entity names are singular. For example, use student (not students).

Example ERD

This diagram is made with Lucichart. Do you recognize the entities and attributes? What can you say about the relation? What information isbeing stoired in this database? Do you recognize the foreign key and where it refers to?

The normalisation rules

The process for creating a valid database design is called normalisation. It consists out of a few concrete steps.

When you don't see all entities right away you can use the formal normalisation steps. Put all data in one big overview, for example in Excel. Start with this one entity and devide the entity in two or more entities until these rules are satified:

Every attribute consists out of one data item.
Data in the entire columns has the same meaning.
Every data line is unique.
Columns cannot be repeated like (field_1, field_2, field_3,etc.)
Try to minimize double data of strings. In order to edit data on one location.
Editing data can always be done by modifing only one column, so there are no dependencies between data in the different columns.

These rules can all be summirzed as follows: make sure that every field on every line can be changed without the need to change other data items.

If your datamodel does not comply to all these rulesm you have to split your data into more entities. There are three ways to do this.

De normalisatie technieken

Kolommen splitsen

Splits een kolom in twee of meer kolommen.

Tabel splitsen (1:N)

Splits een tabel in twee tabellen en verbind de tabellen met door met de foreign key (FK) te verwijzen naar de primary key (PK) van de andere tabel.

Tabel splitsen met koppeltabel (N:M)

Als je een tabel splitst en de relatie is een many to many relatie (N:M) dan heb je een koppeltabel nodig om de twee tabellen te verbinden. In de koppel tabel zitten de FK's die verwijzen naar de PK's van de twee tabellen.

Opgave 1

Lees de tekst door en beantwoord de volgende vragen.

Hoeveel normalisatieregels zijn er?
Hoeveel normalisatie technieken zijn er?
Waarvoor dient een koppeltabel?
Tabellen zijn aan elkaar gekoppeld, wat/hoe zorg je voor deze koppeling?
Zet je voor- en achternaam in één kolom, in twee kolommen of in twee tabellen?
Met welk programma maak jij in jouw examen een ERD?
Een tabel heet "ExamenUitslagen", is dit een juiste naam? Weet jij een betere naam?
Waarom een goed database-ontwerp belangrijk?

Opgave 2

Hieronder zie je een tabel met gegevens over gespeelde voetbalwedstrijden.

Op elke regel staat het thuis-land en gast-land.
De score van biede landen staat in de volgende twee kolommen.
De winnaar is 0,1 of 2. Bij 0 is er geen winnaar, bij 1 is het thuis-land winnaar en bij 2 is het gast-land winnaar.
De laatste twee kolommen laten het aantal kaarten (rode- en gele-) en het aantal penalties zien.

Deze opgave staat in de Excel sheet.

a) Aan welke normalisatieregels voldoet deze tabel niet?
b) Verander de bovenstaade tabel zodat deze wel aan alle normalistie-regels voldoet.

Opgave 3

Kopieer de onderstaande tabel naar Excel (Copy/Paste, of gebruik de Excel Sheet) en normaliseer de gegevens. In de tabel staan studenten die één of meer cursussen volgens. Iedere student heeft een coach en de coach is bereikbaar via het telefoonnummer.

Gebruik de drie normalisatie technieken (kolommen en tabellen splitsen) zodat je aan de zes normalisatieregels voldoet.

Splits de gegevens in aparte kolommen en/of tabellen zodat je aan alle regels voldoet.

Student	Cursus	Coach	telefoon_coach
Kevin Drum	Digitale Vaardigheden, Rekenen, PHP	Ayoub	06 1221 3268
Murvin Drake	PHP, JavaScript	Ayoub	06 1221 3268
John Jones, 1234	C++	Ayoub	06 1221 3268
Sally-Jane Jones	HTML, Pyton	Ayoub	06 1221 3268
David (getrouwd)	Rekenen, PHP	Ayoub	06 1221 3268
Murvin Drake	PHP, JavaScript	Samina	06 2100 3485
Murvin Drake	C++	Samina	06 2100 3485

Opgave 4

In een bibliotheek wil men bijhouden welke klanten welk boek van welke periode tot periode hebben geleend.

Elke klant kan meerdere boeken gelijktijdig lenen. De bibliiotheek heeft soms meerdere exemplaren van één title. Verder wil men de klant een whatsapp kunnen sturen twee dagen voor het verstrijken van de inleverdatum.

Maak een databaseontwerp.

Begin met de onderstaande gegevens (copy paste naar Excel of gebruik de Excel sheet).

klant	boek_id	boek ISBN	Titel	uitgeleend	uitleentermijn	ingeleverd
Caris Villa	234189	817525766-0	De boze wollf	09/06/2021	21
Caris Villa	234288	817333566-1	De lastige wereld	07/05/2021	21	14/05/2021
Caris Villa	134585	817525766-0	Op reis met JJ	28/04/2021	21	29/04/2021
Caris Villa	232269	817528866-3	Het grote feest	04/05/2021	21	25/05/2021
Ayana Tucker	232270	817528866-4	Het grote feest	29/05/2021	35	07/06/2021
Shola Greig	134585	817525766-0	Op reis met JJ	28/05/2021	21

Zet je databaseontwerp in Lucichart.

Opgave 5a

Een recycle bedrijf wil bijhouden uit welke verkoopbare onderdelen een aparaat bestaat. Zo bestaat een mobieltje uit een lithium-ion batterij, scherm en moederbord. Een draadloze koptelefoon bestaat uit een lithium-ion batterij en magneten.
Van elk aparaat wil men een inkoopprijs vasstellen en van alle onderdelen wil men een verkoopprijs bepalen.

Maak een database in Lucichart

Opgave 5b

De inkooprijs van elk aparaat is een richtprijs. Als van een telefoon het scherm bijvoorbeeld is gebarsten dan is deze minder waard.
Het recycle bedrijf heeft een aantal inkopers. Elke inkoper bepaald de werkelijke prijs voor een ingekocht product.

Je wilt dus deze data ook vastleggen in de database van opgave 3a.

Inkoper	Datum	Aparaat	Standaard	Inkoop
Jan	10/May	Espresso aparaat	25,00	27,00
Jan	12/May	koptelefoon	6,00	7,00
Jan	12/May	koptelefoon	6,00	5,00
Jan	12/May	iPhone 6	50,00	45,00
Mimi	12/May	iPhone 6	50,00	40,00
Mimi	13/May	Laptop Lenovo G7800	80,00	85,00
Mimi	13/May	Espresso aparaat	25,00	30,00

Zorg ervoor dat deze data in de juiste tabellen kan worden opgeslagen. Maak het volledige datamodel dat aan alle normalisatieregels voldoet.

De tabel staat ook in de Excel sheet.

Voorbeeld 6

Bij autohandel “Krakkemik” staan er auto's van verschillende merken op het terrein. In hun database staat bijvoorbeeld dat er; een rode Opel Astra uit 1998 voor 1500 euro te koop is, een groene Ford Escort uit 2002 voor 2000 euro en een Peugeot waarvan de prijs 4000 euro is.

Herken jij de entiteit? Van wat wordt hier gegevens vastgelegd?

En wat zijn de attributen van de entiteit? Benoem alle attributen.

Maak het ERD in Lucichart.

opgave 7, ToDo App

Maak een datamodel (ERD) voor een ToDo applicatie. In de Todo applicatie wil je de naam van een ToDo item kunnen vastleggen. Van elk ToDo item wil je de titel kunnen vastleggen, bijvoorbeeld "paspoort verlengen". Verder wil je van elk ToDo item vastleggen wanneer hij is aangemaakt en of hij afgerond is.

Verder wil je de ToDo items opdelen in categorieën. Bijvoorbeeld, de categorie "boodschappen", "privé" of "werk". Van elke categorie wil je vastleggen wanneer deze voor het laatst gewijzigd is.

Maak het ERD in Lucichart.

opgave 8, Restaurant en Corona

In verband met Corona wil het restaurant de "Pittige Stake", een applicatie laten ontwikkelen waarin ze kunnen vastleggen welke klanten bij elkaar aan de tafel hebben gezeten. Op die manier kunnen ze als iemand positief op Corona is getest, bepalen bij wie deze persoon aan tafel heeft gezeten en kunnen deze tafelgenoten worden gevraagd om zich ook te laten testen.

Van elke klant dient de voor- en achternaam en zijn telefoonnummer te worden vastgelegd. Verder moet er worden vastgelegd aan welke tafel de persoon zat en wanneer dit was (datum en tijd). De tafels in het restaurant zijn genummerd van 1 t/m 6 en alleen gasten aan dezelfde tafel dienen te worden gewaarschuwd als een tafelgenoot positief wordt getest op Corona.

Maak het ERD in Lucichart.

opgave 9, Covid-19 test

Het RIVM wil een eenvoudige database waarin ze kunnen zien welke persoon wanneer is getest en wat de uitslag was (positief of negatief). Van alle geteste hoeft alleen de voor- en achternaam en het e-mailadres te worden vastgelegd.

Het RIVM wil ook graag weten of de geteste persoon de Corona app heeft geactiveerd. Als dat zo is dan kunnen ze namelijk via de app andere mensen op de hoogte stellen als zij langere tijd bij een positief getest persoon in de buurt zijn geweest.

Maak het ERD in Lucichart.

opgave 10, Fietsenmaker Snelle Jelle

Fietsenmaker Snelle Jelle wil na een reparatiebeurt zijn klanten per SMS of Whatspapp op de hoogte stellen dat de reparatie klaar is. In dit bericht wil hij ook vertellen hoe hoog de reparatiekosten zijn.

Omdat de veel klanten meer dan één fiets hebben, wil hij van de fietsen ook wat kenmerken vastleggen. Hij wil het merk, model, type en kleur kunnen vastleggen.

Van elke reparatiebeurt wil hij verder vastleggen wanneer het onderhoud plaatsvond, hoe lang de reparatie duurde, wat er is uitgevoerd en de prijs.

Maak het ERD in Lucichart.

opgave 11, Eagle Dev **

Maak het gedeelte van het datamodel van EagleDev waarin van de studenten wordt vastgelegd welke module ze hebben uitgevoerd. Van de studenten wil je de voor- en achternaam, hun email en studentennummer vastleggen en van de modules wil je de naam, de omschrijving, de status en het niveau vastleggen. De status is open, bezig of klaar. En het niveau is opgedeeld in 12 levels, 1 tot en met 12.

Maak het ERD in Lucichart.

opgave 12, garagebedrijf

Garagebedrijf Sneller B.V. wil graag automatisch een orderbon kunnen afdrukken. Op de orderbon moeten de klantgegevens staan en er moet op staan welke artikelen de klant heeft besteld.

Verder wil het garage bedrijf ook vastleggen welke type auto de klant heeft, wat de uitvoering is, wat het kenteken is, wat het bouwjaar is en wat de kleur is. Een klant kan meer dan één auto bezitten.

Maak een database ontwerp (ERD) in Lucichart.