Datenmodellierung

Soccer ERM

Für eine Sportzeitschrift wird eine Fußballdatenbank entworfen: In dieser Datenbank werden verschiedene Fußballmannschaften verwaltet. Jede Mannschaft hat einen eindeutigen Namen, ist in einem bestimmten Jahr gegründet worden und ist an einer Adresse beheimatet. Zu jeder Fußballmannschaft gehören Fußballspieler. Für jeden Spieler soll die SVNr gespeichert werden, welche ihn identifiziert: Jeder Spieler hat einen Namen, eine Wohnadresse und ein Geburtsdatum, sowie eine Position an der er spielt.

Fußballmannschaften beteiligen sich an Spielen. Diese werden durch die Adresse des Stadions, Tag und Uhrzeit eindeutig festgelegt. Für sie werden die beiden beteiligten Mannschaften und der Schiedsrichter gespeichert. Das Ergebnis soll ermittelt werden können.

Falls das Spiel zu einem Turnier gehört, so ist diese Tatsache ebenfalls zu speichern. Falls ein Spieler in einem Spiel Tore geschossen hat, soll die Anzahl der Tore gespeichert werden. Für jeden Schiedsrichter werden dieselben Daten gespeichert wie für die Spieler, außer die Position. Zusätzlich wird noch das Datum der Schiedsrichterprüfung und verwaltet. Für jedes Turnier werden eine von der FIFA vergebene eindeutige Nummer, der Name, Beginn- und Enddatum, sowie die beteiligten Mannschaften in der Datenbank gespeichert.

Hotel ERM

A database has to be implemented for a hotel business. Draw the following facts in an ER diagram and create a relational model. Use the attribute names in brackets. Hotel rooms are uniquely identified by a number (NUMBER). It is also stored whether it is a single room or a double room (PERSONS). Each room is assigned to a category. Categories have a unique ID (ID) and a description (NAME). Employees are identified by a unique social security number (SVNR). Name (NAME) and address (ADDRESS) of the employee are also known. Room attendants and caretakers are employees. The system stores which room is cleaned by which room attendant. For caretakers, a subject area (e.g. sauna, masseur, swimming pool, kitchen, ...) is also stored. A guest has a unique customer number (CustomerNR), a name, (NAME) and an address (ADDRESS). A guest can make several bookings. A booking depends on the room and on a date of arrival (FROM) and departure (TO). The hotel also offers service packages. A unique designation (DESIGN) and a price (PRICE) are stored. There are basic packages and luxury packages. A luxury package contains at least one or more basic packages. Any number of different service packages can be ordered for each booking, whereby the quantity ( QUANTITY) must also be noted. For each service package, one or more carers are responsible.

Library ERM

A library manages books and journals. Each book is published by a publishing house, there may be several copies of each book, which can also be borrowed by customers. There are published issues of the journals, these are only available once, a loan is not possible. The articles published in the journals as well as the books should be managed in such a way that comfortable functions for information retrieval are possible: In addition to the assignment of a subject area, there should also be a keyword, whereby the relevance of each assigned keyword is important. Synonymous keywords should be recorded. Articles and books can each have several authors. Certain books can each have one book as a translation template, articles can reference each other: Each borrowing is handled by one employee, and one employee is also responsible for returning the book copy. Customers can also reserve books. The books and journals are stored on shelves. A journal is placed on a shelf, and each shelf is assigned a subject area for the storage of the books.

E-Learning ERM

The data structure of a new e-learning system is to be implemented. Draw the following facts in an ER diagram notation and create a relational model No null values are allowed. An e-course is clearly identified by a course number (LVANR). In addition, a designation (NAME) is stored, a maximum number of participants (MAXIMUM) and the amount of time (SIZE) required to complete the course. It is noted which e-courses are a prerequisite for which other e-courses. Learning modules have a name (NAME) and a unique identification number (ID). Courses are assigned several learning modules (at least one). Learning modules can be used for several courses. For each course the sequence (ORDER) of modules is noted. Each learning module contains several learning objects (e.g. pictures, texts, videos). Learning objects are uniquely identified by their name (NAME). The type (TYPE) is also stored for learning objects. In some cases, an e-course may also be assigned a test module (maximum). A mode (MODE) of a test module is known and a unique name (DEFINITION) - a test module can be assigned to several e-courses. MC tests and external exams are testmodules. A time (TIME) is known for each MC test. An MC test consists of several questions. Questions are clearly identified by an identification number (QID). For this purpose, the text (QUESTION), one correct (AW TRUE) and two incorrect answers (AW FALSE1, AW FALSE2) must be stored, whereby a question can occur in several different MC tests. Persons are uniquely identified by a personal number (PNR). The name (NAME) is also stored. Persons can participate in any number of courses. An e-course, is moderated by at least one and at most two persons, whereby one person can moderate a maximum of four courses.

Cinema ERM

The STAR-MOVIES company operates a cinema chain with several cinemas (Name, Address... ). Each cinema can have several halls where the films are shown. The seating plan of each hall should be recorded; a row and a seat must be indicated for each seat. A box should be managed like a row. It must be possible to create a seating plan. Of course, several films can be shown per room on one day. In order to be able to determine which seats are available for a performance, each ticket purchase must be noted. Each ticket should show: cinema, hall, film title, date, starting time, serial number, row, seat, price. Provision must be made for pricing: Each row of a hall has a standard price, but for certain performances the row prices can be set individually. For information purposes, the actors should be recorded with their personal data (surname, first name, nationality, date of birth, date of death, comments, ... ) and it should be possible to tell which actors have acted in which films. The analogous statements should also be possible for directors, whereby it can be assumed that there is only one director for a film. However, it is possible that the director also plays a part in a film. The other data of a film include: Title, type (thriller, western, youth film, ... ), year of production, country, language, duration, distribution, etc.

Create a ERD and a Relation Model for this example

Stringer ERM

Gustav ist leidenschaftlicher Tennisspieler. Aus eigenem Interesse, und auch um ein wenig Geld zu sparen, hat er sich selbst beigebgracht, wie er seinen Schläger selbst bespannen kann. Dazu hat er sich eine Besaitungsmaschine und das nötige Werkzeug gekauft. Schon bald begann Gustav auch für seine Vereinskollegen Schläger zu Besaiten. Um den Überblick nicht zu verlieren, wem er welchen Schläger wie besaitet hat, hat Gustav angefangen, in einer Excel Datei mitzuschreiben.

Mittlerweile ist das Schläger-Besaiten zu einem echten Nebenjob geworden und Gustav möchte ein wenig professioneller arbeiten. Dazu wünscht er sich eine Software, die ihm bei der Organisation hilft:

In seiner Excel Datei schreibt Gustav folgende Dinge mit:

   • Jede Besaitung (Stringjob)
       ◦ Mit Datum
       ◦ Schläger
       ◦ (Kunde)
       ◦ Saite Längs
       ◦ Saite Quer
       ◦ Bespannungshärte Längs
       ◦ Bespannungshärte Quer
       ◦ Feedback des Kunden (Optional)
       ◦ Verrechneter Preis
       ◦ Bezahlt oder noch nicht bezahlt

Gustav kennt sich gut in der Domäne aus und hat viele Informationen im Kopf. Daher genügten ihm bisher diese Aufzeichnungen. Die Software soll aber dennoch alle Informationen vollständig abbilden, für den Fall, dass Gustav mal jemanden einstellt, der nicht so viel weiß. Deshalb braucht er auch eine Speichermöglichkeiten für folgende Informationen:

   • Kunden
       ◦ Name
       ◦ Telefonnummer
       ◦ E-Mail
   • Schläger
       ◦ Gehört welchem Kunden
       ◦ Seriennummer
       ◦ Schlägermodell
       ◦ Notizen
       ◦ Gewicht
       ◦ Besaitungsmuster
       ◦ Kopfgröße
   • Schlägermodelle
       ◦ Hersteller
       ◦ Bezeichnung
       ◦ Erscheinungsjahr
   • Hersteller
       ◦ Name
       ◦ Logo
   • Saiten
       ◦ Name
       ◦ Dimension
       ◦ Hersteller
       ◦ Farbe
       ◦ Preis
   • Ausgaben
       ◦ Datum
       ◦ Preis
       ◦ Produktbezeichnung

Weitere Informationen:

   • Bei einer Besaitung kann es durchaus vorkommen, dass man unterschiedliche Saiten für die Längs- bzw. Quersaiten verwendet. 
   • Die Logos der Hersteller sollen nur als URL zu einer Bilddatei gespeichert werden
   • Die Ausgaben sind mit keinen anderen Daten verknüpft. Es handelt sich um eine reine Speicherung der Ausgaben, für eine einfache Einnahmen – Ausgabenrechnung
   • Viele Saiten gibt es in unterschiedlichen Dimensionen. So kann es bspw. die Saite “RPM Blast” vom Hersteller “Babolat” in der Dimension 1.20. 1.25 oder auch 1.30 geben. Eine Saite wird also Eindeutig identifiziert durch ihren Namen und ihre Dimension (z.B. “RPM Blast 1.25”).

Modellieren Sie für Gustav eine Datenbank, mit der er seine Besaitungen gut organisieren kann. Verwenden Sie dazu die Min-Max Notation. Gustav wünscht sich als Ergebnis ein sauberes ERD auf Papier, welches er seinem Haus- und Hofprogrammierer überreichen kann.

Automobilclub

Gegeben seien folgende relationale Schemata.

PERSON(PersAuswNr, Clubname, Name, Geburtsdatum)

AUTO(Kennzeichen, Kilometerstand, ZulassungsDatum)

AUTOMOBILCLUB(Clubname, Ort)

FAHRT(PersAuswNr, Kennzeichen, Datum, Kilometer)

Geben Sie ein äquivalentes ER-Diagramm an. Vermeiden Sie Redundanz. Geben Sie die Kardinalitäten sowohl in Chen, als auch in Min-Max Notation an.

Annahme: Es gibt keine Wechselkennzeichen in dieser Miniwelt

Example questions for competence check

Exercise 1

Represent entities using Chen notation and Min,Max notation. Choose a meaningful example!

Exercise 2

Can a relationship have attributes?

If so, how do I represent it in the ERD?

Exercise 3

What are the Codd's rules (name at least 5)?

Exercise 4

State the difference between Conceptual and Logical Schema

Exercise 5

What are the 3 components of the Entity Relationship Model?

Exercise 6

What are the data types in MySQL? (Name at least 5)

Exercise 7

What types of keys are there and what properties do they have?

Exercise 8

What types of relationships are there? Draw an example of each

Exercise 9

What does the term cardinality mean and what are the cardinalities?

Exercise 10

What does the term data integrity mean and how does integrity differ from referential integrity?

Exercise 11

Explain the 3 normal forms

Exercise 12

Explain the difference between strong and weak entities and give an example.

Exercise 13

Not relevant: What are the basic rules of the relation model? (Name at least 4)

Exercise 14

How do you resolve an M:N relation? Create an example

Exercise 15

A retailer sells a range of items that it buys from different manufacturers. The retailer has a certain group of customers who place orders regularly. An order can include several items. An item can be purchased from more than one supplier and, of course, one supplier usually supplies more than one item. Create an ERD and a relation model that corresponds to the 3rd normal form.

Exercise 16

Which anomalies do you know and what do they describe?

Exercise 17

Model the given reality segment of an airline using an entity relationship diagram. Make reasonable assumptions, if necessary, and document them comprehensibly in your solution. The airline reality segment to be considered includes the following facts: Airports have an abbreviation (= key) and belong to a city (e.g. "FRA" for Frankfurt, "FCO" for Roma Fiumicino). Flights have a flight number (e.g. "LH 306"), lead from one airport to another, each with a fixed departure and arrival time (e.g. from Frankfurt at 07:30 to Roma Fiumicino with arrival at 09:15). Each aircraft type has a name (e.g. "747-400") and a number of seats (e.g. 430 seats). Pilots have a name (e.g. "Meier"), a date of birth (e.g. "1.1.1960") and an authorisation to fly certain aircraft types (e.g. "747-400" and "A310"). Each individual aircraft is of a specific aircraft type (e.g. "747-400") and has a name (e.g. "Mozart"). In a flight mission, a flight (e.g. "LH 306") is flown on a specific date (e.g. "6.2.2011") by a specific pilot (e.g. "Meier") with a specific aircraft (e.g. "Mozart"). Map the conceptual schema into a relational schema. The relational schema should satisfy the 3rd normal form.