Skip to content

Self evaluation and the 1st modelling exercise

1.1. SQL test

What do you remember about SQL? How many points you get by answering the questions in https://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL?

Write your learnings to your learning report.

1.2 Exercise 1 (the 1st modelling exercise)

Can you design a customer database? Document your solution and findings to your learning report.

Requirements: customers (individual persons, companies and other organizations) contain information (besides the name and addresses) about their contact persons, phone numbers, web site(s), and how they (customers) are connected to each other (e.g. if they have created a service to another company).

Your task:

  • create a conceptual data model preferably by using UML class diagram*.

    • e.g. [Concept1] *----> [Concept2] means that the concept1 is dependent on Concept2 and there can be many Concepts1 for the Concept2.
    • Note: just the name of the concept is enough, the properties will be added later

  • create a logical data model by using crow's feet notation also known as Information Engineering (IE) or fork notation.

    • E.g. [Entity1] |o---o< [Entity2] means that for every Entity1 there can be zero or more Entities2 and for Entity2 there are either none or just one Entity1.
    • Note: the foreign keys are not mandatory to be shown explicitely unless they appear with a different name in the child table/entity

Tip: there are hundreads of alternative tools for data modelling. You can use a drawing tools or a dedicated CASE tool for that. Check the list of CASE tools for data modelling => can you find a tool which supports forward engineering to your favourite DBMS? Tip: you can use a free online tool called diagrams.net (formerly known as Draw.io)

Optional: implement it by using your favourite RDBMS (e.g. MariaDB, PostgreSQL or MySQL, which can be used inside the Labranet or through VPN). If you can install HeidiSQL, MySQL Workbench or any other tool to your own computer (virtual machine), you can connect and use them by using the instructions mentioned above.