Relationale Datenbanken (BD 2015)

Aus Philo Wiki
Wechseln zu:Navigation, Suche


Tutorials

w3schools SQL Tutorial
Tutorialspoint SQL Tutorial

Terminologische Orientierung

C.J. Date: Introduction to Database Systems (Edition 6). 1994


Date.png




Beispieldatenbank t50


T50-er.png


CREATE TABLE `personen` (
  `pers_id` smallint(6) NOT NULL,
  `vornamen` char(15) COLLATE utf8_unicode_ci NOT NULL,
  `nachname` char(25) COLLATE utf8_unicode_ci NOT NULL,
  `plz` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ort` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `strasze` char(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nummer` smallint(6) NOT NULL,
  `telefon` int(11) NOT NULL,
  `email` char(60) COLLATE utf8_unicode_ci NOT NULL,
  `homepage` char(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`pers_id`);


CREATE TABLE  `objekte` (
  `objekt_id` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `titel` char(30) COLLATE utf8_unicode_ci NOT NULL,
  `technik` char(35) COLLATE utf8_unicode_ci NOT NULL,
  `format` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jahr` smallint(6) NOT NULL,
  `notiz` char(25) COLLATE utf8_unicode_ci NOT NULL,
  `auflage` smallint(6) NOT NULL,
  PRIMARY KEY (`objekt_id`);


CREATE TABLE  `artists` (
  `pers_id` smallint(6) NOT NULL,
  `objekt_id` char(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`pers_id`,`objekt_id`),
  KEY `objekt_id` (`objekt_id`),
  KEY `pers_id` (`pers_id`);

insert into objekte
  values ('1', 'Traumata','Zeichnung','30cm x 50cm','2014','','1');

insert into artists values ('4','2');

SELECT * 
FROM personen p, artists a
WHERE p.pers_id = a.pers_id;

SELECT p.vornamen, p.nachname
FROM personen p, artists a
WHERE p.pers_id = a.pers_id

Beachte "INSERT INTO ...": So werden innerhalb der Datenbanl Kunstwerke und Künstler "geschaffen".




Was ist eine Datenbank wirklich?

Exzerpte aus Hugh Darwen: What a Databasde really is: Predicates and Propositions

"Hello. Let me introduce myself. My name is Hugh Darwen, and I am the tutor (staff number 44525) in region 4 for certain students on course M358, which is entitled Relational Databases."

3 Sätze
  • "The first, "Hello," is a mere signal, establishing contact."
  • "The second is in the form of an imperative, demanding something of you, the reader (though of course we all know that it's really just a common courtesy in this particular instance)."
  • "The third is a plain statement of fact."

...

"How do we distinguish statements of fact from other kinds of sentences, such as greetings, imperatives, and questions? Well, here's another example that might help:

I am writing this article in my study at home in Warwickshire on February 9th,1997.

Now, you cannot tell whether what I've just told you is a true statement of fact or a false one, but you do know, from its very form, that it is either true or false. By contrast, we cannot say of utterances such as "Hello," "Let me introduce myself," or "What's the time?" that they must be either true or false."

...

"A DATABASE IS A SET OF TRUE PROPOSITIONS

It's useful to view a database as representing a set of propositions (assumed to be true ones) concerning some enterprise of which the database is supposed to provide some kind of record (I'm using the term record here in its generic sense, not the special sense in which it's often used in computer contexts—in database contexts in particular). If we do take that view, there are some important questions that immediately arise:

  • How do we choose which propositions should be stated to form the record of our enterprise?
  • In what form should those propositions be stated?
  • How can we instruct the computer to remember or forget a given proposition or set of propositions?
  • Can we get the computer to prevent us from stating propositions that are ridiculous or contradictory? A ridiculous proposition might be one that states that a certain person is 200 years old; contradictory ones might state that a person is both male and female, or that a tutor in region 4 is—contrary to Open University rules—tutoring a student inregion 5.
  • In what form can we present a question (or "query") to the computer, the response to which would be a proposition or a set of propositions derived by logic from a given database? And in what form should we expect to find that response?"

...

"PREDICATES

  • First, logicians from Aristotle onwards found that reasoning based just on the notion of propositions had certain severe limitations, which they eventually overcame by studying certain generalized forms of such propositions. They found that, when several propositions are of the same generalized form, various impressive shortcuts could be taken by reasoning in terms of those general forms instead of in terms of individual propositions per se.
  • Second, commercial databases can contain billions of propositions; if propositions of the same general form cannot somehow be lumped together, such databases will surely be unmanageable and unusable.

...

It's predicates that have made databases, database management, and database queries tractable to computerization. Consider once again the proposition from my opening paragraph, which I'll now restate in a very slightly different way:

Hugh Darwen is the name of a tutor (staff number 44525) in region 4 for certain students on course M358, which is entitled Relational Databases.

It's easy to see that this statement has a certain form that could be common to a whole set of propositions one might wish to state in some record of the enterprise called the Open University.

...

Here's what is probably the most general form of the original proposition that we might all agree on:

... is the name of a tutor (staff number ... ) in region ... for certain students on course ... , which is entitled ... .

And that's a predicate!"




Relationen und Prädikate nach C.J. Date


C.J. Date: Introduction to Database Systems (Edition 6). 1994


Date-propositions1.png
Date-propositions2.png
Date-propositions3.png