Understanding Primary and Foreign Keys in Database Relations

[mathmari]

Hey! :giggle:

Consider the followingrelations for the database of a mail-order company.
Kunde(KNr, Nachname, Vorname, Strasse, Hausnr, PLZ, Ort)
Artikel(ArtNr, PreisProStuck, Bezeichnung, Gewicht)
Bestellung(KNr, ArtNr, Anzahl, Bestelldatum)

1. Think about which attributes are useful primary keys in the individual relations.
2. Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?
3. Are there reasonable conditions for the individual attributes? Give for each attribute at least one property (at least the data type) and possibly a condition that must always be fulfilled. Hint: Conditions refer to the type, key and reference integrity. I have done the following :

1. Primary keys is the column that is characteristic for a table, like the ID number, where each entry is unique, right?
Therefore, at the table "Kunde" the primary key is the attrubute "KNr", at the table "Artikel" the primary key is the attrubute "ArtNr", right?
At the table "Bestellung" which is the primary key? Maybe one of the first two attributes? Or can it not be that one attribute is the primary key for more than one table? The "Anzahl" which gives us the number of items that have been ordered must not be unique, right? The same holds also for the date of order, right?

:unsure: 2. Foreign keys are the primary keys of one table that are also in an other table,or non? That means that in the table "Bestellung" we have "KNr" and "ArtNr" which are the primary keys of the first two tables.
Is that correct? Or have I misunderstood the meaning of foreign keys? :unsure: 3. I haven't really understood what this question asks for. Could you explain that further to me? :unsure:

 

[I like Serena]

1. Primary keys is the column that is characteristic for a table, like the ID number, where each entry is unique, right?
Therefore, at the table "Kunde" the primary key is the attrubute "KNr", at the table "Artikel" the primary key is the attrubute "ArtNr", right?
At the table "Bestellung" which is the primary key? Maybe one of the first two attributes? Or can it not be that one attribute is the primary key for more than one table? The "Anzahl" which gives us the number of items that have been ordered must not be unique, right? The same holds also for the date of order, right?

Hey mathmari!

Indeed. Neither KNr nor ArtNr is unique. And the combination of all columns won't necessarily be unique either.
After all, it is possible that the same customer orders the same number of articles twice on the same day.

Instead we should introduce a new attribute, say BNr, as the first column.

2. Foreign keys are the primary keys of one table that are also in an other table,or non? That means that in the table "Bestellung" we have "KNr" and "ArtNr" which are the primary keys of the first two tables.
Is that correct? Or have I misunderstood the meaning of foreign keys?

Correct. KNr and ArtNr are foreign keys in the table Bestellung. (Nod)

3. I haven't really understood what this question asks for. Could you explain that further to me?

Basically it asks to give CREATE TABLE statements (schemas) "that makes sense".

Let's start with KNr in the table Kunde.
Which properties should we apply to it (at least the data type)?

The name KNr already implies that it is a number, so the type should be integer.
We want to use it as a primary key, so the condition should be PRIMARY KEY, which implies that it is both NOT NULL and UNIQUE.

So we should have:

CREATE TABLE Kunde (
  KNr integer PRIMARY KEY,
  ...
);

 

[mathmari]

Indeed. Neither KNr nor ArtNr is unique. And the combination of all columns won't necessarily be unique either.
After all, it is possible that the same customer orders the same number of articles twice on the same day.

Instead we should introduce a new attribute, say BNr, as the first column.

Ah can we just do that, adding a new attribute? :unsure:

Correct. KNr and ArtNr are foreign keys in the table Bestellung. (Nod)

The new attribute of 1, BNr, is not a foreign key, since we don't use this somewhere else, right? :unsure:

What does the second part of the question means ?
"If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?"

I haven't really understood what this means. :unsure:

Basically it asks to give CREATE TABLE statements (schemas) "that makes sense".

Let's start with KNr in the table Kunde.
Which properties should we apply to it (at least the data type)?

The name KNr already implies that it is a number, so the type should be integer.
We want to use it as a primary key, so the condition should be PRIMARY KEY, which implies that it is both NOT NULL and UNIQUE.

So we should have:

CREATE TABLE Kunde (
  KNr integer PRIMARY KEY,
  ...
);

So do we have the following ?

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225),
Vorname char(225),
Strasse char(225),
Hausnr integer,
PLZ integer,
Ort char(225)
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float,
Bezeichnung char(225),
Gewicht float
);

CREATE TABLE Bestellung (
KNr integer,
ArtNr integer,
Anzahl integer,
Bestelldatum date,
);:unsure:

 

[I like Serena]

Ah can we just do that, adding a new attribute?

It's the best that I can think of. :unsure:

The new attribute of 1, BNr, is not a foreign key, since we don't use this somewhere else, right?

BNr would be a primary key in the table Bestellung.
For the record, KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde.
Since BNr is not referred to in other tables, there is no other table in which it is a foreign key.

What does the second part of the question means ?
"If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?"

I haven't really understood what this means.

Suppose we want to delete a customer.
Then what should happen to their orders that are still in the table Bestellung?
That table would still refer to the customer through its KNr.
In other words, deletion of the customer would break the so called referential integrity.
What ways can we think of to prevent referential integrity from being broken?

So do we have the following ?

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225),
Vorname char(225),
Strasse char(225),
Hausnr integer,
PLZ integer,
Ort char(225)
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float,
Bezeichnung char(225),
Gewicht float
);

CREATE TABLE Bestellung (
KNr integer,
ArtNr integer,
Anzahl integer,
Bestelldatum date,
);

That works. I think we can and should identify a couple more properties though.
Can we mark for instance KNr in Bestellung as a FOREIGN KEY, which will enforce referential integrity?

 

[mathmari]

BNr would be a primary key in the table Bestellung.
For the record, KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde.
Since BNr is not referred to in other tables, there is no other table in which it is a foreign key.

So at the first part of question 2 ("Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate.") the answer is to say that KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde and ArtNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Artikel, right? :unsure:
For the second part of question 2 we have :

Suppose we want to delete a customer.
Then what should happen to their orders that are still in the table Bestellung?
That table would still refer to the customer through its KNr.
In other words, deletion of the customer would break the so called referential integrity.
What ways can we think of to prevent referential integrity from being broken?

We can delete only a costumer that has no order in the table Bestellung, right?
Respectively, if we can update a costumer only if the costumer has no order in the table Bestellung, right?

Is that correct? Or can we delete or update also a costumer if he has an order and in that case we have to update the respective row in the table Bestellung?

:unsure:

That works. I think we can and should identify a couple more properties though.
Can we mark for instance KNr in Bestellung as a FOREIGN KEY, which will enforce referential integrity?

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225) NOT NULL ,
Vorname char(225) NOT NULL ,
Strasse char(225) NOT NULL ,
Hausnr integer NOT NULL ,
PLZ integer NOT NULL ,
Ort char(225) NOT NULL
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float NOT NULL ,
Bezeichnung char(225) NOT NULL ,
Gewicht float NOT NULL
);

CREATE TABLE Bestellung (
BNr integer PRIMARY KEY
KNr integer,
ArtNr integer,
Anzahl integer NOT NULL ,
Bestelldatum date NOT NULL ,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
); Do we need that "NOT NULL" ? Is the rest correct so far? What other properties can we use? :unsure:

 

[I like Serena]

So at the first part of question 2 ("Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate.") the answer is to say that KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde and ArtNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Artikel, right?

Correct. (Nod)

For the second part of question 2 we have :

We can delete only a costumer that has no order in the table Bestellung, right?
Respectively, if we can update a costumer only if the costumer has no order in the table Bestellung, right?

Is that correct? Or can we delete or update also a costumer if he has an order and in that case we have to update the respective row in the table Bestellung?

I think there are 2 options here:
1. The deletion of the customer causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically deleted as well if we try to delete a customer.


In the case of a change of KNr we have more or less the same thing:
1. A change of KNr in Kunde causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically updated as well if we try to change the KNr of a customer.

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225) NOT NULL ,
Vorname char(225) NOT NULL ,
Strasse char(225) NOT NULL ,
Hausnr integer NOT NULL ,
PLZ integer NOT NULL ,
Ort char(225) NOT NULL
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float NOT NULL ,
Bezeichnung char(225) NOT NULL ,
Gewicht float NOT NULL
);

CREATE TABLE Bestellung (
BNr integer PRIMARY KEY
KNr integer,
ArtNr integer,
Anzahl integer NOT NULL ,
Bestelldatum date NOT NULL ,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
);Do we need that "NOT NULL" ? Is the rest correct so far? What other properties can we use?

It looks correct to me. (Nod)

Do we need NOT NULL? That depends. Does it make sense if we leave it out?
If we have a customer, then it seems that the least we need is a nachname, so I think that one should be NOT NULL.
Is it a problem if we don't have a vorname? Maybe not. So perhaps we shouldn't have it there. :unsure:

As for other properties, if we want to, we can add a CHECK on the value of an attribute.
For instance Gewicht should probably be greater than zero if it is specified.
Then again, we may be overthinking it if we start specifying things like that.

 

[mathmari]

I think there are 2 options here:
1. The deletion of the customer causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically deleted as well if we try to delete a customer.


In the case of a change of KNr we have more or less the same thing:
1. A change of KNr in Kunde causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically updated as well if we try to change the KNr of a customer.

So you mean either the deletion/change of KNr in Kunde (ArtNr in Artikel respectively) would cause an error or the corresponding row in Bestellung is deleted/updated automatically? :unsure:

Do we need NOT NULL? That depends. Does it make sense if we leave it out?
If we have a customer, then it seems that the least we need is a nachname, so I think that one should be NOT NULL.
Is it a problem if we don't have a vorname? Maybe not. So perhaps we shouldn't have it there. :unsure:

As for other properties, if we want to, we can add a CHECK on the value of an attribute.
For instance Gewicht should probably be greater than zero if it is specified.
Then again, we may be overthinking it if we start specifying things like that.

So should it be as follows ?

CREATE TABLE Kunde (
KNr INTEGER PRIMARY KEY,
Nachname CHAR(225) NOT NULL,
Vorname CHAR(225),
Strasse CHAR(225),
Hausnr INTEGER,
PLZ INTEGER,
Ort CHAR(225)
);

CREATE TABLE Artikel (
ArtNr INTEGER PRIMARY KEY,
PreisProStuck FLOAT CHECK (PreisProStuck > 0),
Bezeichnung CHAR(225),
Gewicht FLOAT CHECK (Gewicht > 0)
);

CREATE TABLE Bestellung (
BNr INTEGER PRIMARY KEY
KNr INTEGER,
ArtNr INTEGER,
Anzahl INTEGER NOT NULL,
Bestelldatum DATE,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
); Or could we add/improve something ? :unsure:

 

[I like Serena]

I think it is fine. (Nod)