- #1
mathmari
Gold Member
MHB
- 5,049
- 7
Hey!
Create the following tables:
1. Kundenstamm
3. Bestellungen
So we write for 1 the following :
CREATE TABLE Kundenstamm (
KNr PRIMARY KEY,
Name CHAR(15),
Stadt CHAR(10)
Land CHAR(10)
Saldo FLOAT
Rabatt FLOAT
);
:unsure:For 2 :
CREATE TABLE Produkte (
PNr PRIMARY KEY CHECK (PNr > 1) ,
Bez NOT NULL CHAR(10) UNIQUE.
Gew FLOAT
Preis FLOAT CHECK (Preis > 0) // Is checking if it is positiv an integrity constraint?
Lagerort CHAR(15) // What integrity constraint do we use here? If it is not Null for example?
Vorrat INTEGER // What integrity constraint do we use here? If it is not negativ for example?
);
:unsure:For 3 :
CREATE TABLE Bestellungen (
BestNr INTEGER PRIMARY KEY CHECK (BestNr > 0) ,
Mon INTEGER NOT NULL CHECK(Mon >= 1 and Mon <=12)
Tag INTEGER NOT NULL CHECK(Tag >= 1 and Tag <=31)
FOREIGN KEY (PNr) REFERENCES Kundenstamm (PNr),
FOREIGN KEY (KNr) REFERENCES Produkte (KNr)
Menge INTEGER CHECK(Menge >0) // It is the ordered quantity, or not? What integrity constraints can we consider?
Summe FLOAT // Is this the sum of invoices? Or what is this meant? What integrity constraints can we consider?
Status CHAR(20) // It is meant if is paid, delivered, etc? So this contains words, right? What integrity constraints can we consider?
UNIQUE (Mon, Tag, Pnr, Knr)
);
Do we write that as in the last line that the attributes Monat, Tag, Pnr and Knr must together be unique ?:unsure:
Create the following tables:
1. Kundenstamm
- KNr (Primary Key)
- Name (at most 15 characters)
- Stadt (at most 10 characters)
- Land (at most 10 characters)
- Saldo (Type FLOAT)
- Rabatt (Type FLOAT)
- PNr (greater than 1 and Primary Key)
- Bez (not NULL, at most 10 characters and unique)
- Gew (Type FLOAT)
3. Bestellungen
- BestNr (Type INTEGER, greater than 0 and Primary Key)
- Mon (Type INTEGER, not NULL and between 1 and 12)
- Tag (Type INTEGER, not NULL and between 1 and 31)
- PNr (Foreign Key)
- KNr (Foreign Key)
So we write for 1 the following :
CREATE TABLE Kundenstamm (
KNr PRIMARY KEY,
Name CHAR(15),
Stadt CHAR(10)
Land CHAR(10)
Saldo FLOAT
Rabatt FLOAT
);
:unsure:For 2 :
CREATE TABLE Produkte (
PNr PRIMARY KEY CHECK (PNr > 1) ,
Bez NOT NULL CHAR(10) UNIQUE.
Gew FLOAT
Preis FLOAT CHECK (Preis > 0) // Is checking if it is positiv an integrity constraint?
Lagerort CHAR(15) // What integrity constraint do we use here? If it is not Null for example?
Vorrat INTEGER // What integrity constraint do we use here? If it is not negativ for example?
);
:unsure:For 3 :
CREATE TABLE Bestellungen (
BestNr INTEGER PRIMARY KEY CHECK (BestNr > 0) ,
Mon INTEGER NOT NULL CHECK(Mon >= 1 and Mon <=12)
Tag INTEGER NOT NULL CHECK(Tag >= 1 and Tag <=31)
FOREIGN KEY (PNr) REFERENCES Kundenstamm (PNr),
FOREIGN KEY (KNr) REFERENCES Produkte (KNr)
Menge INTEGER CHECK(Menge >0) // It is the ordered quantity, or not? What integrity constraints can we consider?
Summe FLOAT // Is this the sum of invoices? Or what is this meant? What integrity constraints can we consider?
Status CHAR(20) // It is meant if is paid, delivered, etc? So this contains words, right? What integrity constraints can we consider?
UNIQUE (Mon, Tag, Pnr, Knr)
);
Do we write that as in the last line that the attributes Monat, Tag, Pnr and Knr must together be unique ?:unsure:
Last edited by a moderator: