MHB How Do You Manage Database Tables in SQL?

[mathmari]

Hey!

Consider the relations Personen, Hotel and BuchungH :

1. Create the three relations in the database.
2. Insert the tuples in all tables.
3. Create meaningful keystones for all tables.
4. Give an expression that deletes again the tables. Could you explain to me what we are supposed to do? Do we have to create the tables with SQL commands? :unsure:

If it is like that, then do we have the following at 1 ?

CREATE TABLE Personen (
PID int,
Name varchar(255),
);

CREATE TABLE Hotel (
HID int,
Name varchar(255),
Ort varchar(255),
);

CREATE TABLE BuchungH (
Bnr int,
PID int,
HID int,
von int,
bis int,
); Is everything correct and complete? The length of 255 is standard? Or does it depend each time on the specific table? :unsure: At 2 do we have the following ?

INSERT INTO Personen
VALUES ('1', 'Schmidt'),
('2', 'Meier'),
('3', 'Beier'),
('4', 'Bauer'),
('5', 'Wolf');

INSERT INTO Hotel
VALUES ('1', 'Beach Club', 'Mallorca'),
('2', 'Radisson', 'Mallorca'),
('3', Le Flaubert', 'Nizza'),
('4', 'Ibis', 'Nizza'),
('5', 'Ibis', 'Mailand');

INSERT INTO BuchungH
VALUES ('1', '1', '1', '20211030', '20211107'),
('2', '2', '1', '20211030', '20211107'),
('3', '3', '4', '20211031', '20211104'),
('4', '3', '2', '20211030', '20211107'),
('5', '4', '4', '20211031', '20211108');
Is everything correct and complete? :unsure: Could you explain to me questions 3 and 4 ? :unsure:

 

[I like Serena]

CREATE TABLE BuchungH (
Bnr int,
PID int,
HID int,
von int,
bis int,
);
Is everything correct and complete? The length of 255 is standard? Or does it depend each time on the specific table?

Hey mathmari!

I think "von" and "bis" should be DATE's instead of integers.

For now we can use a length of 255. If the database grows bigger, it might help to use a fixed length string to improve performance. That is not a consideration now though.

At 2 do we have the following ?

INSERT INTO Personen
VALUES ('1', 'Schmidt'),
('2', 'Meier'),
('3', 'Beier'),
('4', 'Bauer'),
('5', 'Wolf');
Is everything correct and complete?

If the type of a field is integer, then we should provide a value without quotes.

Could you explain to me questions 3 and 4 ?

"keystones" seems to be the wrong term. I think that instead we need PRIMARY KEY's.
That is, we mark a column as a primary key, which enforces that it must be unique.

In question 1 we used CREATE TABLE to create the tables. In question 4 we're supposed to use DELETE TABLE to delete them.

 

[mathmari]

So do we have the following ?

1 :

CREATE TABLE Personen (
PID INTEGER,
Name CHAR(255),
);

CREATE TABLE Hotel (
HID INTEGER,
Name CHAR(255),
Ort CHAR(255),
);

CREATE TABLE BuchungH (
Bnr INTEGER,
PID INTEGER,
HID INTEGER,
von DATE,
bis DATE,
);

2 :

INSERT INTO Personen
VALUES (1, 'Schmidt'),
(2, 'Meier'),
(3, 'Beier'),
(4, 'Bauer'),
(5, 'Wolf');

INSERT INTO Hotel
VALUES (1, 'Beach Club', 'Mallorca'),
(2, 'Radisson', 'Mallorca'),
(3, 'Le Flaubert', 'Nizza'),
(4, 'Ibis', 'Nizza'),
(5, 'Ibis', 'Mailand');

INSERT INTO BuchungH
VALUES (1, 1, 1, '20211030', '20211107'),
(2, 2, 1, '20211030', '20211107'),
(3, 3, 4, '20211031', '20211104'),
(4, 3, 2, '20211030', '20211107'),
(5, 4, 4, '20211031', '20211108');At the dates we need the quotes, don't we? :unsure:

3 :

What exactly is the primary key ? Is that the column that identifies the table? Do we chose that by ourselves?
The column that we choose must contain only uniquely defined elements, right?
So in Personen both columns can be the primary key, in Hotel the first column must be the primary key and in BuchungH the first column must be the primary key, right?

:unsure:

4 :

DROP TABLE Personen;
DROP TABLE Hotel;
DROP TABLE BuchungH;

Or do we write that in one command? :unsure:

 

[I like Serena]

At the dates we need the quotes, don't we?

Yes, we need the quotes. But I think it should be like '2021-10-30'. That is, it seems the DATE format is usually YYYY-MM-DD.

3 :
What exactly is the primary key ? Is that the column that identifies the table? Do we chose that by ourselves?
The column that we choose must contain only uniquely defined elements, right?
So in Personen both columns can be the primary key, in Hotel the first column must be the primary key and in BuchungH the first column must be the primary key, right?

The primary key is usually the first column in a table and it uniquely identifies each row in the table.
We can generally choose it ourselves, but in the questions it is already given.
What is left is to tell SQL that it is a PRIMARY KEY.
See for instance this w3schools page. It also shows that the syntax is different for different flavors of SQL.

4 :
DROP TABLE Personen;
DROP TABLE Hotel;
DROP TABLE BuchungH;
Or do we write that in one command?

We created each table in a separate command, so it makes sense to also drop them in separate commands. (Nod)

 

[mathmari]

Yes, we need the quotes. But I think it should be like '2021-10-30'. That is, it seems the DATE format is usually YYYY-MM-DD.

Ah although in the given table we have all number together wew can nwrite that in this way, right?

So do we have :

INSERT INTO BuchungH
VALUES (1, 1, 1, '2021-10-30', '2021-11-07'),
(2, 2, 1, '2021-10-30', '2021-11-07'),
(3, 3, 4, '2021-10-31', '2021-11-04'),
(4, 3, 2, '2021-10-30', '2021-11-07'),
(5, 4, 4, '2021-10-31', '2021-11-08');:unsure:

The primary key is usually the first column in a table and it uniquely identifies each row in the table.
We can generally choose it ourselves, but in the questions it is already given.
What is left is to tell SQL that it is a PRIMARY KEY.
See for instance this w3schools page. It also shows that the syntax is different for different flavors of SQL.

So the primary key is the id ofeach table, right? ;unsure:So do we write the following ?

ALTER TABLE Personen
ADD PRIMARY KEY (PID);

ALTER TABLE Hotel
ADD PRIMARY KEY (HID);

ALTER TABLE BuchungH
ADD PRIMARY KEY (Bnr);:unsure:

 

[mathmari]

Can we check that for example here or is that not possible? :unsure:

 

[I like Serena]

ALTER TABLE Personen
ADD PRIMARY KEY (PID);

ALTER TABLE Hotel
ADD PRIMARY KEY (HID);

ALTER TABLE BuchungH
ADD PRIMARY KEY (Bnr);

Yep. That works. (Nod)

Can we check that for example here or is that not possible?

Yes. We can check there. The CREATE TABLE and INSERT INTO commands can go in the left pane. The SELECT queries go into the right pane.
We can select the SQL flavor. Previously I selected PostgreSQL so that syntax would be the same as in your other thread.

 

[mathmari]

Yes. We can check there. The CREATE TABLE and INSERT INTO commands can go in the left pane. The SELECT queries go into the right pane.
We can select the SQL flavor. Previously I selected PostgreSQL so that syntax would be the same as in your other thread.

And the Primary Key goes to the left ? :unsure:

 

[I like Serena]

And the Primary Key goes to the left ?

Yes. The identification of the primary key is part of the 'schema' of the tables.