138 lines
5.1 KiB
SQL
138 lines
5.1 KiB
SQL
drop database if exists IT_Concept;
|
|
create database if not exists IT_Concept;
|
|
use IT_Concept;
|
|
|
|
create table tblKunde(
|
|
Kundennr int primary key
|
|
-- mehr Attribute sind in der Aufgabe nicht genannt
|
|
)engine=innodb;
|
|
|
|
create table tblStandort(
|
|
StandortID int primary key,
|
|
Etage varchar(10),
|
|
Raum varchar(10)
|
|
)engine=innodb;
|
|
|
|
create table tblGeraet(
|
|
Geraetenr int(5) primary key,
|
|
Einkaufspreis double(6,2),
|
|
-- Wartungsvertrag und letzte Watung müssen nicht ausgelagert werden,
|
|
-- da auf Dauer nur 1 Datum und 1 boolscher Wert erfasst wird.
|
|
-- diese Werte sind direkt vom PK des Geräts abhängig.
|
|
Wartungsvertrag bool, -- boolscher Wert heißt true oder false
|
|
letzteWartung date,
|
|
Lieferdatum date,
|
|
-- Lieferdatum könnte auch in eigener Tabelle stehen,
|
|
-- wenn man davon ausgeht, dass mehr Details zur Lieferung erfasst werden
|
|
-- da im Formular nur das Datum steht, kann es auch bei Geraet erfasst werden
|
|
Kunde int,
|
|
Standort int,
|
|
foreign key(Standort) references tblStandort(StandortID),
|
|
foreign key(Kunde) references tblKunde(Kundennr)
|
|
)engine=innodb;
|
|
|
|
|
|
-- Beziehung Geraet - Zaehler braucht keine Zwischentabellen, da
|
|
-- ein Zaehler einem Gerät zugeordnet ist
|
|
-- ein Gerät genau einen Zähler hat
|
|
-- eigene Zählertabelle ist erforderlich, da merhere DS mit Zählerständen
|
|
-- zu erfassen sind.
|
|
-- ob der FK in tbl Geraet oder tblTaehler erfasst wird ist egal
|
|
create table tblZaehler(
|
|
ZaehlerID int auto_increment primary key,
|
|
Datum date,
|
|
Zaehlerstand int,
|
|
Geraet int(5),
|
|
foreign key(Geraet) references tblGeraet(Geraetenr)
|
|
)engine=innodb;
|
|
|
|
-- bei der Beziehung Zubehör kann man der Übersicht entnehmen, dass
|
|
-- ein Gerät mehrere Zubehörteile hat (mehrere Zubehör-IDs im Formular)
|
|
-- außerdem interpretiere ich mit logischem Menschenverstand, dass
|
|
-- ein Zubehörteil vermutlich auch in merheren Geräten verwendet werden kann
|
|
-- deshalb n:m, das mit Zwischentabelle aufgelöst werden muss
|
|
-- da keine Anzahl im Formualr angegeben wird, gehe ich davon aus, dass
|
|
-- jedes Zubehörteil genau einmal in einem Gerät verbaut wird
|
|
|
|
create table tblZubehoer(
|
|
ZubehoerID varchar(20) primary key
|
|
)engine=innodb;
|
|
|
|
create table tblGeraeteZubehoer(
|
|
GeraetZubehoerID int auto_increment primary key,
|
|
Zubehoer varchar(20),
|
|
Geraet int(5),
|
|
foreign key(Zubehoer) references tblZubehoer(ZubehoerId),
|
|
foreign key(Geraet) references tblGeraet(Geraetenr)
|
|
)engine=InnoDB;
|
|
|
|
-- ein Gerät verursacht verschiedene Kostenarten (Papier, Miete,...)
|
|
-- eine Kostenart fällt für verschiedene Geräte an
|
|
-- deshalb n:m-Beziehung, Zwischentabelle
|
|
create table tblKostenart(
|
|
KostenartID int auto_increment primary key,
|
|
Kostenart varchar(50)
|
|
)engine=innodb;
|
|
|
|
-- eine Kostenart kommt mehrfach in einem Gerät vor, deshalb reichen
|
|
-- GeraeteID und KostenartID nicht als zusammengesetzter PK aus,
|
|
-- da sonst Papier, Miete o-ä. nur einmalig als Kostenfaktor angesetzt werden könnte
|
|
-- Lösung: eigener PK oder Datum zum PK hinzunehen.
|
|
create table tblGeraeteKosten(
|
|
Geraet int(5),
|
|
Kostenart int,
|
|
Datum date,
|
|
Betrag double(7,2),
|
|
Bemerkung varchar(50),
|
|
primary key (Geraet, Kostenart, Datum),
|
|
foreign key(geraet) references tblGeraet(Geraetenr),
|
|
foreign key(Kostenart) references tblKostenart(KostenartID)
|
|
)engine=innodb;
|
|
|
|
-- ein Gerät kann mehrere Störungen haben,
|
|
-- eine Störung kann bei mehreren Geräten vorkommen
|
|
-- deshalb n:m-Beziehung und Auflpösung mit Zwischentabelle
|
|
create table tblStoerungsart(
|
|
StoerungsartID int auto_increment primary key,
|
|
Art varchar(50)
|
|
)engine=innodb;
|
|
|
|
-- eine Kostenart kommt mehrfach in einem Gerät vor, deshalb reichen
|
|
-- GeraetID und StoerungsartID nicht als zusamengesetzter PK
|
|
-- Lösung: eigener PK oder Datum als drittes Element in PK dazu
|
|
create table tblGeraeteStoerung(
|
|
Geraet int(5),
|
|
Stoerungsart int,
|
|
Datum date,
|
|
primary key(Geraet, Stoerungsart, Datum),
|
|
foreign key(Geraet) references tblGeraet(Geraetenr),
|
|
foreign key(Stoerungsart) references tblStoerungsart(StoerungsartID)
|
|
)engine=INNODB;
|
|
|
|
-- Alter Statements
|
|
-- Fügen Sie in Tabelle „Gerät“ ein weiteres Attribut „Gerätebeschreibung“ mit 100 Zeichen hinzu.
|
|
Alter table tblGeraet
|
|
add Geraetebeschreibung varchar(100);
|
|
|
|
-- 2.Ergänzen Sie an geeigneter Stelle ein Attribut „Bemerkung“, bei dem Sie Eintragungen bezüglich der konkreten Störung vornehmen können.
|
|
Alter table tblGeraeteStoerung
|
|
add Bemerkung varchar(100);
|
|
|
|
-- 3.Ändern Sie den Datentyp beim Attribut „Zählerstand“ von int auf double. (oder ggf. anders herum)
|
|
Alter table tblZaehler
|
|
modify Zaehlerstand double(13,0);
|
|
|
|
-- 4.Ergänzen Sie beim Gerät ein Attribut Bestelldatum.
|
|
Alter table tblGeraet
|
|
add Bestelldatum date;
|
|
|
|
-- 5.Ergänzen Sie an geeigneter Stelle ein Attribut „Störung behoben“, das nur die Werte true oder false halten kann.
|
|
Alter table tblGeraeteStoerung
|
|
add StoerungBehoben bool;
|
|
-- wenn Attributname aus 2 Wörtern besteht muss er in SQL mit Unterstrich oder zusammen geschrieben werden
|
|
-- ansonsten erkennt MySql die Begriffe zwei Eigenschaften und wirft einen Fehler
|
|
|
|
-- 6.Löschen Sie die Spalte Bemerkung bei Tabelle Kosten.
|
|
Alter table tblGeraeteKosten
|
|
drop Bemerkung;
|