Relationale Datenbanken (BD 2015)

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

---

Beispieldatenbank t50

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

---