Normalisierung von Datenbanktabellen [3]

Den PRIMARY KEY habe ich entfernt. Dann kann man Einträge ausbessern. Dafür kann man einzelne fehlerhafte nicht mehr direkt anklicken und verbessern. Wie ich weiter aufzuteilende Einträge in zwei oder drei Einträge für die selbe Tabelle aufteilen kann, habe ich noch nicht ganz raus. Das muß wohl ein INSERT mit einer Subquery werden, beziehungsweise eine Subquery pro Tabellenspalte, aber das INSERT muß zwei oder drei Einträge aus der gleichen Subquery produzieren und danach noch den originalen Eintrag löschen.

Mit SUBSTRING_INDEX(SUBSTRING_INDEX(wort_de,'(',-1),')',1) lassen sich eingeklammerte Teilzeichenketten extrahieren. Sollte aber mehr als eine Klammer vorkommen, dann funktioniert das nicht. Da könnte ich mir vorstellen, den Teil auszuschneiden und in einer extra Datenbanktabelle zwischenzulagern. Aber dann wüßte ich bald nicht mehr, was an welche Stelle gehört....
Ranma

Ja, Datensatz ist der korrekte Ausdruck. Aber ich will nur aus dem Teil in jeweils einem Datenfeld zwei Teile machen.

Caesium {n}; Cesium {n} (Zäsium) /Cs/ [chem.]		caesium [Br.]; cesium [Am.]

Das ist einer der Beispieldatensätze. (Steht nochmal in Posting #16.) Bei diesem sind sogar beide Seiten nicht atomisiert. Auf der rechten Seite wäre es leicht, wenn alle Datensätze so aufgebaut wären. Der englische Teil dieses Datensatzes wäre am ; in zwei Teile zu teilen, jeder davon zusammen mit dem gesamtem deutschsprachigem Teil als neuer Datensatz in der Tabelle unterzubringen und der originale Datensatz dafür zu löschen.

Danach kann dann bei den beiden neuen Einträgen die Information aus den eckigen Klammern geholt werden und in einer weiteren Tabellenspalte gespeichert werden.

Die linke, deutschsprachige Seite ist nicht ganz so einfach zu weiterzuverarbeiten. [chem.] bezieht sich auf sämtliche der Synonyme, muß also vor der weiteren Aufteilung in eine extra Spalte. So weit ist es noch relativ leicht. /Cs/ ist das chemische Symbol. Das gilt, wie übrigens auch [chem.], nicht nur für alle deutschen Synonyme, sondern auch für alle englischen Synonyme in diesem Datensatz. Cs kommt also auch vor einer weiteren Aufteilung in eine extra Spalte. Das heißt, das würde es, wenn / nur als Delimiter und nicht in mehreren Bedeutungen vorkommen würde. Die runden Klammern haben hier eine besonders eigenartige Verwendung, weil darin ein weiteres Synonym steht, das eigentlich durch ; abgetrennt und nicht eingeklammert sein sollte. Behandeln wir in mal so. Danach würde der übrige Inhalt des Datenfeldes an den ; aufgeteilt und neue Einträge aus den Teilstücken gemacht werden, jeweils zusammen mit einer Kopie der Einträge der inzwischen befüllten anderen Spalten dieses Datensatzes. Das heißt, die neuen Datensätze würden aus zwei vorhandenen Datensätzen gemacht, weil aus dem originalen schon zwei entstanden wären. Die beiden würden dann entfernt und es blieben sechs Datensätze übrig. Schließlich würde noch aus jedem Datensatz das {n} in eine eigene Tabellenspalte überführt, der Inhalt der geschweiften Klammern ist hier nur zufällig gleich.

Der Inhalt geschweifter Klammern bezieht sich normalerweise auf nur ein Wort. Bei anderen Klammerungen ist das nicht so, wie man hier an den eckigen Klammern sieht, die auf der deutschsprachigen Seite sich auf den gesamten Datensatz, aber auf der englischsprachigen Seite sich nur auf ein Wort beziehen. Wegen genau solcher Unterschiede meinte ich, es sei besser die Datensätze in die Datenbank zu überführen und von dort aus weiterzuverarbeiten als alles sofort mit dem Skript, das die Wörterlisten auswertete, zu erledigen. Das hätte gut geklappt, würden die Delimiter auf der englischsprachigen Seite anders verwendet als auf der deutschsprachigen. Aber es geht wirr durcheinander. Es gibt auch auf der deutschsprachigen Seite eckige Klammern, die sich nur auf einen durch ; abgetrennten Teil beziehen. Es gibt ; innerhalb von Klammern und da kann ich den Eintrag (den Teildatensatz?) natürlich nicht trennen. Manchmal ist / ein Ersatz für ; manchmal werden Satzteile oder Sätze durch ; getrennt und innerhalb dieser Synonyme durch / und manchmal werden Ausdrücke mit zwei / eingeklammert. Das gibt es dann wiederum auch innerhalb von runden Klammern.

Also aufteilen will ich die Datensätze an ; und an /, sofern sie sich nicht in Klammern (irgendwelcher Art) befinden oder selbst als Klammern dienen.

Vielleicht geht es ganz normal mit INSERT INTO, aber an der Idee hat mich gestört, daß ich den PRIMARY KEY entfernen mußte (also IGNORE nicht mehr funktioniert) und wohl für jede Tabellenspalte eine eigene SELECT-Subquery brauche, die wiederum die Tabelle im FROM-Teil enthält. Viele Datenfelder enthalten bis dahin nur NULL und andere wurden durch CONCAT() zusammengesetzt. Irgendwie sieht es für mich so aus als riskiere ich, Spalten überschreiben zu lassen, falls das überhaupt so funktioniert.
Ranma

Das ist also ein Fremdschlüssel. Und ich habe mir schon überlegt, die entry_id eigentlich nicht zu brauchen.

Ich beschäftige mich schon seit zwei Monaten damit, Wörterlisten in die Datenbank zu übertragen und dann die vielen kaputten Einträge zu reparieren. Jetzt habe ich noch in den Japanisch-Übersetzungen viele ungepaarte Klammern gefunden, manche Einträge stehen komplett in Klammern. An den Stellen ersetzt eine Erklärung eine wörtliche Übersetzung, wenn die nicht möglich ist. Das läßt sich also nicht unbedingt besser verteilen. Jedenfalls nicht automatisch.

Darum habe ich jetzt genug von den Atomisierungsversuchen. Jetzt sollen die Datensätze auf drei Tabellen umverteilt werden. In die erste kokmmen die Wörter und die Angabe der Sprache (wegen der „false friends“). In die zweite zusätzliche Angaben zu den Wörtern, sofern sie vorhanden sind. Für viele gibt es auf zur Zeit mehrere Tabellen verteilte Angaben, aber viele Datenbankfelder stehen auf NULL. In die dritte Tabelle sollen schließlich die Kombinationen. Da denke ich, daß ich sowohl die japanischen Restriktionen (also ein japanischer Ausdruck kombiniert mit einem anderem) als auch die Übersetzungen reinpacken kann. Vielleicht gibt es noch andere Arten von Kombinationen, so bleibt mein Wörterbuch in dem Punkt flexibel. Die Sprachangabe in der Tabelle für die einzelnen Wörter bewirkt auch, daß die Tabelle offen für Wörter aus weiteren Sprachen ist und mein Wörterbuch auch da flexibel bleibt. Wären die Regeln der Normalisierung damit erfüllt?

Ich habe die drei Tabellen erstellt und gedacht, jetzt müssen nur noch die auf mehrere Tabellen (weil sie aus mehreren Listen und mehreren Dateien stammen) verteilten Datensätze in die neuen Tabellen übertragen werden. Nur übersteigt das anscheinend die Möglichkeiten von SQL. Eigentlich kann INSERT INTO mehrere Datensätze in einer Anweisung einfügen. SELECT kann tausende Datensätze gleichzeitig anzeigen. Trotzdem scheint eine Subquery, die mit SELECT gebildet wird, immer nur einen Datensatz liefern zu dürfen, sonst gibt es nur eine Fehlermeldung. Eine Schleife kann man in SQL wohl nicht realisieren?

Mit einem PHP-Skript hatte ich ja schon vorher versucht, die Kana_id und Kanji_id aus den vorhandenen Tabellen zu holen und in eine weitere einzufügen und das hatte damals schon nicht funktioniert. Ich konnte das Problem bis auf die SELECT-Query eingrenzen, aber deren Syntax war korrekt. Trotzdem lieferte sie nicht. Außerdem ist die Verbindung zwischen PHP und MySQL sehr langsam, während sowohl das eigentliche PHP als auch in phpMyAdmin ausgeführtes SQL sehr schnell sind. Sogar der Zugriff auf Dateien geht blitzschnell verglichen mit dem Zugriff auf eine Datenbank. Sind Datenbanken nicht eigentlich dafür da, um viel schneller als die Arbeit mit Dateien zu sein? Was kann ich jetzt noch versuchen, um die Datensätze umzusortieren?

Später soll mein Wörterbuchskript erlauben, Einträge auszubessern. Über die Funktion können dann kaputte Datensätze noch repariert werden.
Ranma

Die Query brauche ich nicht mehr. Ich habe festgestellt, daß bei Verwendung von Stored Procedures Schleifen möglich sind. Das wird nur in den meisten SQL-Tutorials komplett verschwiegen, ohne den geringsten Hinweis. Wenn man aber vermutet, daß es da doch Schleifen geben könnte, dann findet man die Anleitungen dafür. Ich weiß nicht mehr, falls ich es überhaupt je wußte, wo die Stored Procedures gespeichert werden. Aber man kann sie in der SQL-Box von phpMyAdmin ausführen. Das kann schonmal über eine Stunde dauern. Ist aber immer noch schneller und besser als meine bisherigen Versuche. Ein Teil der Wörter sind bereits in einer der drei neuen Tabellen. Die werden extrem lang werden, vor allem die mit den Kombinationen.

Dazu fällt mir ein, daß manchmal nicht nur ein Ausdruck mit einer Übersetzung zu kombinieren ist, sondern auch Schriftzeichen mit ihrer Aussprache. Dazu nochmal ein Datensatz aus der Chinesisch-Liste.

510 	阿巴丹	阿巴丹	a1 ba1 dan1 	Abadan  	u.E., Eig, Geo	NULL

Die entry_id ganz links wird obsolet, die beiden Spalten rechts landen in einer anderen Tabelle; natürlich nur, wenn sie nicht NULL sind. Dazwischen sind traditionelle chinesische Schriftzeichen mit reformierten chinesischen Schriftzeichen (kein Unterschied hier, weil ich den Datensatz nicht extra ausgesucht, sondern schnell aus einem früherem Beitrag hierher kopiert habe) mit der Aussprache und schließlich mit einer Übersetzung kombiniert. Das macht als Zweierkombinationen sechs Kombinationen. Drei davon würden aber auch schon alles eindeutig machen, nur eine Abfrage ginge dann sozusagen um eine Ecke. Da muß ich mir wohl eine der angegebenen Spalten für die „Ecke“ aussuchen. Am besten keine der beiden mit chinesischen Schriftzeichen, weil in dem Fall, daß die wie hier gleich sind, nur ein Eintrag in die Wörterliste erforderlich ist. In der Kombination muß ich dann wohl den Eintrag auf sich selbst abbilden. Aber normalisiert ist es dann korrekt?
Ranma

Mit der ersten Tabelle bin ich fertig, also hier ein paar Beispiele:

476312 	de	jmds. Stelle einnehmen
476313 	de	jmds. treuer (m) Gehilfe
476314 	de	jmds. übles (n) Spiel
476315 	de	jmds. verstorbene ältere (f) Schwester
476316 	de	jmds. Vertrauen haben

952766 	en	pentlandite; iron nickel pyrite; nicopyrite; folgerite
952767 	en	pentose
952768 	en	pentstemon
952769 	en	pentyl chloroformate
952770 	en	penult (penultimate syllable of a word)

1428971 	ja	マイナスせいちょう
1428972 	ja	マイナスのねじまわし
1428973 	ja	マイナスのねじ回し
1428974 	ja	マイナスめん
1428975 	ja	マイナスイオン

1619636 	nl	(4) drie genieën
1619637 	nl	(4) drie jaar oud
1619638 	nl	(4) drie op het zuiden georiënteerde trappen van het keizerlijk paleis
1619639 	nl	(4) drillen

1872619 	ru	ГДР
1872620 	ru	гёза (японское блюдо напоминающее пельмени)
1872621 	ru	гейша
1872622 	ru	гектар
1872623 	ru	гекто-
1872624 	ru	гелий (He)

1904731 	tr	tuğla
1904732 	tr	tunç
1904733 	tr	Türkiye
1904734 	tr	tutku

Manche Datensätze sehen so aus als könnte man das noch verbessern, aber dann könnten jedesmal andere Datensätze mitbetroffen sein, bei denen das eine Verschlimmbesserung wäre.

Unsinnigerweise hatte zum Beispiel mal irgendjemand einige niederländische Übersetzungen nochmal zusätzlich zu den in der Datei verwendeten Delimitern extra durchnummeriert. Das führt zu komischen führenden (4) in den Einträgen. Aber Ziffern in runden Klammern ist ein Muster, das auch in den Namen von Chemikalien auftaucht. Also kann ich nicht das eine bearbeiten, ohne auch das andere zu treffen.

Die Einträge dieser ersten Tabelle werden dann in der zweiten Tabelle weiterverwendet, in der sich bis jetzt erst Kombinationen aus Deutsch und Türkisch (die kleinste Tabelle, muß darum zum testen herhalten) befinden:

trans	281285 	1903878
trans	281285 	1904598
trans	281304 	1903955
trans	281940 	1904511
trans	281951 	1904068
trans	281951 	1904790
trans	282542 	1904628
trans	282598 	1904021
trans	283162 	1904036
trans	283179 	1904373

Linke Spalte: Hinweis, daß es sich bei der Kombination um eine Übersetzung handelt.
Mittlere Spalte: ID eines deutschen Wortes.
Rechte Spalte: ID eines türkischen Wortes.

Möglicherweise sollte jede Kombination noch eine eigene ID bekommen. Natürlich erst hinterher, damit das INSERT IGNORE funktioniert. Ich wüßte noch garnicht, wofür die eigene ID einer Kombination gebraucht werden könnte.

Eine wünschenswerte Funktion wäre natürlich eine, die es ermöglicht, einen AUTO INCREMENT -Wert beim INSERT IGNORE zu ignorieren. Dann ließe sich die Option auch später zur Laufzeit des späteren Wörterbuches verwenden.

Die Tabelle zu den Zusatzangaben enthält noch keine Einträge. Zu solchen hätte ich aber noch Erklärungen aus der Datei für Japanisch:

<!ENTITY Buddh "Buddhist term">
<!ENTITY chem "chemistry term">
<!ENTITY chn "children's language">
<!ENTITY col "colloquialism">
<!ENTITY comp "computer terminology">
<!ENTITY conj "conjunction">

Das ist nur ein kleiner Ausschnitt. Ich weiß noch nicht, was ich damit anfange. Einerseits wäre es ja sinnvoll, den teils doch recht kryptischen Abkürzungen eine Erklärung mitzugeben. Andererseits sind sie alle für Japanisch und auf Englisch. Und wo würde ich sie hinpacken? Vielleicht sollte ich sie erstmal aus ihren XML-tags befreien, indem sie in einer weiteren Tabelle landen, und dann die Abkürzungen in der anderen Tabelle (also diejenige, die jetzt noch keine Einträge enthält) mit den Erklärungen ersetzen. Das dürfte auf jeden Fall besser sein als nur eine kryptische Abkürzung.
Ranma