Creating tables - integrity constraints

  • Context: MHB 
  • Thread starter Thread starter mathmari
  • Start date Start date
  • Tags Tags
    Constraints
Click For Summary

Discussion Overview

The discussion revolves around the creation of SQL tables with a focus on defining integrity constraints for various columns. Participants explore the necessary constraints for attributes in three tables: Kundenstamm, Produkte, and Bestellungen, considering aspects such as data types, uniqueness, and conditions for validity.

Discussion Character

  • Technical explanation
  • Debate/contested
  • Mathematical reasoning

Main Points Raised

  • Some participants propose that integrity constraints should include NOT NULL and CHECK conditions, while others suggest that it may not be necessary to overthink these constraints.
  • There is discussion about whether CHECK constraints, such as ensuring Preis is greater than 0, are valid integrity constraints.
  • Some participants question how to handle the integrity constraints for Lagerort and Vorrat, suggesting NOT NULL for both.
  • Concerns are raised about the Summe attribute, with one participant suggesting it should be non-negative and NOT NULL.
  • Participants discuss the dynamic nature of the Status attribute, with one suggesting it should allow NULL initially to facilitate order placement.
  • There is uncertainty about the correct syntax for defining unique constraints across multiple attributes, with references to SQL documentation for clarification.

Areas of Agreement / Disagreement

Participants generally agree on the need for integrity constraints but have differing views on the specifics of which constraints to apply and how to implement them. The discussion remains unresolved regarding the best approach to certain attributes and their constraints.

Contextual Notes

Limitations include varying interpretations of integrity constraints and the potential for different SQL dialects to influence how constraints are defined. There is also uncertainty about the implications of allowing NULL values for certain attributes.

mathmari
Gold Member
MHB
Messages
4,984
Reaction score
7
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:
 
Last edited by a moderator:
Physics news on Phys.org
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. 🤔
 
Klaas van Aarsen said:
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:
 
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:
 
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:
 
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. 🤔