(SQL) Normalisieren

[AC]

$all->hi,

Zwecks Wortanalyse habe ich etliche Texte auseinanderklamüsert und zeilenweise in eine tabseparierte Datei geschrieben. Auszug:

853 And
853 Beleg
853 answered
853 I
853 will
853 seek
853 Túrin
853 until
853 I
853 find
853 him
853 and
853 I
853 will
853 bring
853 him
853 back
853 to
853 Menegroth
853 if
853 I
853 can
853 for
853 I
853 love
853 him
853 also
854 Then
854 Beleg
854 departed
854 from
854 Menegroth
854 and
854 far
854 across
854 Beleriand
854 he
854 sought
854 in
854 vain
854 for
854 tidings
854 of
854 Túrin
854 through
854 many
854 perils

Das ganze landet in einer Datenbank:

create table raw (line int, word varchar);
copy raw from '/home/pg/data';

Ich kann meine Daten wieder holen:

create index word_index on raw (word);
select line from raw where word = 'Menegroth';

Aber so nehmen die Daten ziemlich viel Platz ein. Ich habe den Tipp bekommen, zu normalisieren. Dazu habe ich viele Webseiten besucht und gelesen, aber die sagen ausschließlich theoretisch, wie das Endergebnis aussieht, aber nicht, wie man dorthin kommt. :(

http://search.cpan.org/src/GMAX/DBSchema-Normalizer-0.08/docs/Normalizer.html hat Codebeispiele, aber die sind nur in MySQL gültig.

Ich hab natürlich versucht, auf der lokalen Maschine mittels eines Programms jedem einzelnen Wort seine Zeilen zuzuordnen, aber bei einem Hash mit zehntausenden verschiedenen Wörtern geht mir der Arbeitsspeicher aus, das Programm fängt an, zu swappen, und crasht letztendlich. Deshalb brauche ich eine serverseitige Lösung in SQL. Soweit:

create table lines (lineid serial primary key, line int);
insert into lines (line) select distinct line from raw;

create table words (wordid serial primary key, word varchar);
insert into words (word) select distinct word from raw;

create table lines_words (lineid int references lines (lineid), wordid int references words (wordid));

Fragen: Wie kriege ich die letzte Tabelle gefüllt? Wie sieht eine Abfrage nun aus, ist das korrekt?
select line from lines where wordid.word = 'Menegroth' on lines_words.lineid = lines_words.wordid;

Ich kann gut Perl, aber verstehe erst wenig SQL und DB-Theorie. Ich darf Postgres oder SQLite benutzen. Bitte berücksichtigt diese Fakten bei der Beantwortung.

[Cheatah]

Hi,

Aber so nehmen die Daten ziemlich viel Platz ein. Ich habe den Tipp bekommen, zu normalisieren. Dazu habe ich viele Webseiten besucht und gelesen, aber die sagen ausschließlich theoretisch, wie das Endergebnis aussieht, aber nicht, wie man dorthin kommt. :(

das liegt daran, dass viele Wege nach Rom führen, und welcher davon sinnvoll ist stark davon abhängt, wo Du Dich befindest.

Ich hab natürlich versucht, auf der lokalen Maschine mittels eines Programms jedem einzelnen Wort seine Zeilen zuzuordnen, aber bei einem Hash mit zehntausenden verschiedenen Wörtern geht mir der Arbeitsspeicher aus, das Programm fängt an, zu swappen, und crasht letztendlich. Deshalb brauche ich eine serverseitige Lösung in SQL. Soweit:

Du könntest Dir ein INSERT INTO Statement mit Subselect und DISTINCT ausdenken, anschließend ein UPDATE auf die bestehende Tabelle.

Wie sieht eine Abfrage nun aus, ist das korrekt?
select line from lines where wordid.word = 'Menegroth' on lines_words.lineid = lines_words.wordid;

Nö. Hier selektierst Du alle Zeilen mit den entsprechenden Wort, verknüpft mit allen lines_words-Einträgen, in denen die Zeilen-ID zufällig mit der Wort-ID übereinstimmt.

Cheatah

[AC]

Du könntest Dir ein INSERT INTO Statement mit Subselect und DISTINCT ausdenken

Das ist vage. Wie sieht das Statement aus?
Ich verstehe nicht, was du mit Subselect meinst.

ist das korrekt?
Nö.

Wie ist es korrekt?

[Cheatah]

Hi,

Du könntest Dir ein INSERT INTO Statement mit Subselect und DISTINCT ausdenken
Das ist vage. Wie sieht das Statement aus?

ganz gut, es sollte aber mal wieder zum Friseur gehen.

Ich verstehe nicht, was du mit Subselect meinst.

Schau Dir die Syntax zu INSERT INTO in der Dokumentation an. Es ist möglich, eine Selektion zugrunde zu legen.

ist das korrekt?
Nö.
Wie ist es korrekt?

Wenn Du fertige Lösungen erwartest, bist Du hier falsch. Stelle Fragen, durch die Du etwas lernen kannst.

Cheatah

[Ilja]

$du->yo,

Aber so nehmen die Daten ziemlich viel Platz ein. Ich habe den Tipp bekommen, zu normalisieren. Dazu habe ich viele Webseiten besucht und gelesen, aber die sagen ausschließlich theoretisch, wie das Endergebnis aussieht, aber nicht, wie man dorthin kommt. :(

ich persönlich glaube, dass es in vielen quellen zu ganz unterschiedliche aussagen bezüglich der normalisierung kommt. leider stehen dort oftmals auch viele falsche aussagen, frei nach dem motto, viele köche verderben den brei.

aber davon mal abgesehen und der tatsache, dass ich noch keine vorstellung davon habe, was du eigentlich machen willst, deine tabellen keinen eindeutigen bezeichner (primary key) haben, würde eventuell das schlagwort fulltext-index dir weiter helfen.

Ilja

[AC]

ich noch keine vorstellung davon habe, was du eigentlich machen willst,

Ich möchte die Daten der Tabelle raw auf die drei neuen Tabellen nach den Regeln der Normalisierung verteilen.

deine tabellen keinen eindeutigen bezeichner (primary key) haben,

Zwei haben sie, schau noch mal hin.

würde eventuell das schlagwort fulltext-index dir weiter helfen.

Ich glaube nicht, ich habe ja schon einzelne Wörter. Besides, Postgres hat FTI nicht im Kern und für SQLite gibt es das noch nicht, also ist das auch eine Sackgasse für mich.

[Ilja]

yo,

ich noch keine vorstellung davon habe, was du eigentlich machen willst,
Ich möchte die Daten der Tabelle raw auf die drei neuen Tabellen nach den Regeln der Normalisierung verteilen.

das ist aber nicht das, was du machen willst, sondern eine umsetzung von dem, was du machen willst. viel wichtiger wäre es zu wissen, was genau du vorhast, quasi eine projektbeschreibung.

so wie dein design im moment aussieht, speicherst du über eine n:m beziehung, welches wörter in welcher zeile vorkommen. allerdings bleiben andere fragen offen, gibt es mehrere texte oder handelt es sich nur aus einem, an welcher stelle nun genau solch ein wort in einer zeile befindet, wird nicht festgehalten, auch nicht ob es mehrere gleicher wörter in einer zeile gibt, etc.

wie bereits angesprochen ist es sichtig zu erfahren, was genau du machen willst. und dann überlegt man sich die geeignten mittel dazu.

Ilja

[Sven Rautenberg]

Moin!

Aber so nehmen die Daten ziemlich viel Platz ein. Ich habe den Tipp bekommen, zu normalisieren. Dazu habe ich viele Webseiten besucht und gelesen, aber die sagen ausschließlich theoretisch, wie das Endergebnis aussieht, aber nicht, wie man dorthin kommt. :(

Die Frage ist doch: Was willst du aus den Daten am Ende wissen? Es muß ja einen Sinn haben, dass du Tolkien-Texte in eine Datenbank packst.

Sofern du beantwortet haben willst, welche Worte in welchen Zeilen stehen, gibts hinsichtlich der Normalisierung in meinen Augen diese Stufen:

1. Jedes Wort wird mit jeder Zeilennummer separat gespeichert - so ist es in deiner Raw-Datei.

2. Jedes Wort wird mit einer Liste von Zeilen, in denen es vorkommt, verknüpft. Das spart den Speicherplatz, jedes Wort immer und immer wieder zu speichern, benötigt aber zusätzlichen Speicherplatz, um eine Wort-ID mit der Zeilennummer zu verknüpfen.

Tabellendesign wäre dann: 1. Tabelle: Wort und Wort-ID. 2. Tabelle Wort-ID und Zeilennummer.

Im Prinzip ist die zweite Tabelle genau dasselbe, wie deine jetzige Liste - nur dass anstelle der Worte jetzt IDs stehen, deren Bedeutung in einer separaten Tabelle nachgeschlagen werden kann.

3. Natürlich kann man auch noch die Zeilennummern, die möglicherweise ja nicht bloß Zahlen sind, sondern evtl. lexikalische Verweise (Verlag, Buch, Band, Seite, Zeile), in IDs umsetzen. Das bedeutet eine dritte Tabelle, die genauso aufgebaut ist, wie die Wortliste - und die Zuordnung findet dann nicht mehr zwischen Wort-ID und Zeilennummer statt, sondern zwischen Wort-ID und Zeilen-ID.

Sofern aber die Zeilennummer in deinem Ausgangsmaterial eindeutig ist und nur aus einer Zahl besteht, ist dieser Zusatzaufwand entbehrlich.

Zur Befüllung dieser Tabelle ist es nun bei jedem Wort in der Wortliste notwendig, zu prüfen, ob das Wort in der Worttabelle schon vorkommt. Wenn ja, ermittle die Wort-ID und schreibe Wort-ID und Zeilennummer in die Tabelle. Wenn nein: Schreibe das neue Wort in die Wortliste, ermittle dessen neue ID und schreibe sie dann zusammen mit der Zeilennummer in die Tabelle.

Bei diesem Vorgang wäre eventuell auch die Anwendung einer Stoppwortliste hilfreich, die typische und sehr häufig vorkommende Worte herausfiltert.

Ob das Ergebnis in der Datenbank dann aber tatsächlich weniger Speicherplatz in Anspruch nimmt, würde ich mal bezweifeln. Insbesondere wenn du hinterher einigermaßen schnelle Abfragen haben willst, mußt du Indexe einsetzen, die ihrerseits eine Menge Speicherplatz verbrauchen können - oftmals mehr, als an Nutzdaten enthalten ist. Für den großen Speicherplatzaufwand, den man hat, erhält man im Gegenzug schnelle Datenbankabfragen.

- Sven Rautenberg

[Maynard]

Yo!

Zwecks Wortanalyse habe ich etliche Texte auseinanderklamüsert und zeilenweise in eine tabseparierte Datei geschrieben.

hmmm, gut.

Das ganze landet in einer Datenbank:

Hmm, gut. In einer Textdatei?

create table raw (line int, word varchar);

OK, ein RDBMS.

Aber so nehmen die Daten ziemlich viel Platz ein. Ich habe den Tipp bekommen, zu normalisieren.

Du kannst Textinhalte nicht normalisieren, die liegen bereits normal vor.

Fragen: Wie kriege ich die letzte Tabelle gefüllt? Wie sieht eine Abfrage nun aus, ist das korrekt?

Ich kann gut Perl, aber verstehe erst wenig SQL und DB-Theorie. Ich darf Postgres oder SQLite benutzen. Bitte berücksichtigt diese Fakten bei der Beantwortung.

OK, lass die Finger von dem RDBMS, speichere die Textdaten einfach als Textdateien und analysiere mit PERL.

Liebe Grüße
Maynard