MHB Creating tables - integrity constraints

[mathmari]

Hey!

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)

2. Produkte

• PNr (greater than 1 and Primary Key)
• Bez (not NULL, at most 10 characters and unique)
• Gew (Type FLOAT)

Think about the integrity constraints for the columns Preis, Lagerort and Vorrat.

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)

The attributes Monat, Tag, Pnr and Knr must together be unique. Think about the integrity constraints for the columns Menge, Summe and Status.
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:

 

[I like Serena]

Hey mathmari!

It seems all fine to me. (Nod)
I think there is no need to overthink the constraints. Typically we should decide if we want them to be required or not.
That is, either NOT NULL, or unspecified to allow NULL.
I believe CHECK is also considered an integrity constraint.

It depends a bit on the version of SQL that we use how to combine attributes into a unique constraint.
For a number of SQL versions we can apparently use something like "CONSTRAINT UC_Person UNIQUE (ID,LastName)" as explained in w3schools.

 

[mathmari]

It seems all fine to me. (Nod)
I think there is no need to overthink the constraints. Typically we should decide if we want them to be required or not.
That is, either NOT NULL, or unspecified to allow NULL.
I believe CHECK is also considered an integrity constraint.

So at the ones that we have to give integrity constraints it is not enough just to give the type, right?

So :

Preis FLOAT CHECK (Preis > 0) :
This one is a complete integrity constraint, right?

Lagerort CHAR(15) NOT NULL :
We want that the storage location is specified, or not?

Vorrat INTEGER CHECK (Vorrat >= 0) :
The stock must be non-negativ, or not?

Menge INTEGER CHECK(Menge >0) :
This one is a complete integrity constraint, right?

Summe FLOAT :
I suppose that this is from the description of the other thread " The invoice amount for an order is the product of the price and the ordered quantity of the ordered product minus the customer discount.". So what integrity constraint can we use here?

Status CHAR(20) :
At the description of the other thread we have " The status of a paid order must never change again." and " Der Status einer Bestellung darf sich nur in “geliefert” ¨andern. Der Status einer gelieferten Bestellung nur in “bezahlt”. ". Can we maybe use these ones to make a integrity constraint?

:unsure:

 

[I like Serena]

It doesn't say that we "have to give constraints". Instead it only says to "think about the integrity constraints".
I think that usually most constraints - if they are specified - are just NOT NULL.

It seems indeed reasonable that Vorrat must be specified. And if we have a Vorrat, then we also want to know where that Vorrat is, so the Lagerort should be NOT NULL.
Summe should probably be NOT NULL, and we might for instance require that it is non-negative.
I'm not aware of any content constraint we can set on a text attribute, other than NOT NULL. :unsure:

 

[mathmari]

So we have the following : For 1 :

CREATE TABLE Kundenstamm (
KNr PRIMARY KEY,
Name CHAR(15),
Stadt CHAR(10)
Land CHAR(10)
Saldo FLOAT
Rabatt FLOAT
);For 2 :

CREATE TABLE Produkte (
PNr PRIMARY KEY CHECK (PNr > 1) ,
Bez NOT NULL CHAR(10) UNIQUE.
Gew FLOAT
Preis FLOAT CHECK (Preis > 0)
Lagerort CHAR(15) NOT NULL
Vorrat INTEGER NOT NULL
);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)
Summe FLOAT NOT NULL CHECK(Summe >= 0)
Status CHAR(20) NOT NULL
UNIQUE (Mon, Tag, Pnr, Knr) OR CONSTRAINT Comb UNIQUE (Mon, Tag, Pnr, Knr)
);Is everything correct ? :unsure:

 

[I like Serena]

It looks correct to me.
However, in your other thread it appears there is supposed to be a dynamic constraint on Status.
After it has a value, it may not be changed.
In that case it makes sense to me that Status should be allowed to be NULL. Then we can place an order, and set its state later.