# The SQL Programming Language

## Resources

- (Elmasri and Navathe 2010, ch. 4–5), (Elmasri and Navathe 2015, ch. 6–7) describes `SQL`, but none of its implementation.
- To compare DBMS, you can look at their features, on Wikipedia, and at their popularity at DB-Engines and Stack Overflow (if you sum up MySQL and MariaDB, they are first in both).
- MySQL and MariaDB have some differences, you can look them up at MySQL Features and MySQL Compatibility.

This chapter will be “code-driven”: the code will illustrate and help you understand some concepts. You may want to have a look at the “Setting Up Your Work Environment” Section, as early as possible in this lecture. On top of being a step-by-step guide to install and configure a relational database managment system, it contains a list of useful links.

## Actors

### Technologies

- There are other models than relational: document-based, graph, column-based, and key-value models. Those corresponds to the “NoSQL” data-model, that are often more flexible, but only defined by opposition. They will be studied separately, in the Presentation of NoSQL Chapter.
- The most commons DBMS are relational database management system (RDBMS):

- Oracle Database
- MySQL and its fork, MariaDB
- Microsoft SQL Server
- PostgreSQL
- IBM DB2
- Microsoft Access
- SQLite

Most of them supports semi-structured data, i.e., models that are not strictly speaking relational, some are “multi-model DBMS”.
- The Structured Query Language (SQL) is the language for RDBMS, it is made of 4 sublanguages:

- Data Query Language,
- Data Definition Language (schema creation and modification),
- Data Control Language (authorizations, users),
- Data Manipulation Language (insert, update and delete).

The three last sublanguages being dubbed “Data Manipulation Language”.

### SQL

#### Yet Another Vocabulary

“Common”/Relational | SQL
--- | ---
“Set of databases” | Catalog (named collection of schema)
“Database” | Schema
Relation | Table
Tuple | Row
Attribute | Column, or Field

#### Schema Elements

A schema is made of

- Tables (≈ relation)
- Type (≈ datatype)
- Domain (≈ more complex datatype)
- View (result set of a stored query on the data, ≈ saved search)
- Assertion (constraints, transition constraints)
- Triggers (tool to automate certain actions after pre-defined operations are performed)
- Stored procedures (≈ functions)

Type and domains are two different things in some implementations, cf. for instance PostgreSQL, where a domain is defined to be essentially a datatype with constraint.

#### Syntax

`SQL` is a programming language: it has a strict syntax, sometimes cryptic error messages, it evolves, etc. Some of its salient aspects are:

- `SQL` is “kind of” case-insensitive, does not care about spaces and new lines
- In-line comments are what is after `--`, multi-line comments uses `/* …*/`.
- Every statement ends with a ;.
- The exact syntax is left as an exercise in Pb 3.1.
- The list of reserved words can be found at MySQL or MariaDB
- We will focus in this chapter to MariaDB and MySQL (no domain, limited data type definition).

#### Datatypes

The following is an adaptation of W3Resource, the canonical source being MySQL’s documentation:

- For integer types, you can use `INTEGER` (or its short-hand notation `INT`) or `SMALLINT`.
- For floating-point types, you can use `FLOAT` and `DOUBLE` (or its synonym, `REAL`). MySQL also allows the syntax `FLOAT(M,D)` or `REAL(M,D)`, where the values can be stored up to `M` digits in total where `D` represents the decimal point.
- For monetary amounts, it is recommended to use `DECIMAL(10, 2)` (or its synonym in MySQL `NUMERIC`).
- Characters can be stored using `CHAR` and `VARCHAR`: the length (resp. maximal length) of the `CHAR` (resp. `VARCHAR`) has to be declared, and `CHAR` are right-padded with spaces to the specified length. Historically, 255 was the size used, because it is the largest number of characters that can be counted with an 8-bit number, but, whenever possible, the “right size” should be used.
- You can store a single bit using `BIT(1)`, and a boolean using `BOOLEAN`.
- For date and time types, you can use `DATE`, `TIME`, `DATETIME` and `TIMESTAMP`.

There are many other datatypes, but they really depends on the particular implementation, so we will not consider them too much.

## First Commands

>     /* code/sql/HW_Faculty.sql */
>     -- We first drop the schema if it already exists:
>     DROP SCHEMA IF EXISTS HW_Faculty;

>     -- Then we create the schema:
>     CREATE SCHEMA HW_Faculty;

>     /*
>      Or we could have use the syntax:

>      CREATE DATABASE HW_FACULTY;
>      */
>     -- Now, let us create a table in it:
>     CREATE TABLE HW_Faculty.PROF (
>      Fname VARCHAR(15),
>      /*
>      No String!
>      The value "15" vas picked randomly, any value below 255 would
>      more or less do the same. Note that declaring extremely large
>      values without using them can impact the performance of
>      your database, cf. for instance https://dba.stackexchange.com/a/162117/
>      */
>      Room INT,
>      /*
>      shorthand for INTEGER, are also available: SMALLINT, FLOAT, REAL, DEC
>      The "REAL" datatype is like the "DOUBLE" datatype of
>      C# (they are actually synonyms in SQL):
>      more precise than the "FLOAT" datatype, but not as exact as the
>      "NUMERIC" datatype.
>      cf. https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html
>      */
>      Title CHAR(3),
>      -- fixed-length string, padded with blanks if needed
>      Tenured BIT(1),
>      Nice BOOLEAN,
>      -- True / False (= 0) / Unknown
>      Hiring DATE,
>      /*
>      The DATE is always supposed to be entered in a YEAR/MONTH/DAY
>      variation. To tune the way it will be displayed, you can use
>      the "DATE_FORMAT" function
>      (cf. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format),
>      but you can enter those values only using the "standard"
>      literals (cf. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html )
>      */
>      Last_seen TIME, FavoriteFruit ENUM ('apple', 'orange',
>      'pear'), PRIMARY KEY (Fname, Hiring)
>     );

>     /*
>      Or, instead of using the fully qualified name HW_Faculty.PROF,
>      we could have done:

>      USE HW_Faculty;
>      CREATE TABLE PROF(…)
>      */
>     -- Let us use this schema, from now on.
>     USE HW_Faculty;

>     -- Let us insert some "Dummy" value in our table:
>     INSERT INTO PROF
>      VALUES ("Clément", -- Or 'Clément'.
>      290, 'PhD', 0, NULL, '19940101', -- Or '940101',  '1994-01-01',  '94/01/01'
>      '090500', -- Or '09:05:00', '9:05:0',  '9:5:0',  '090500'
>      -- Note also the existence of DATETIME, with 'YYYY-MM-DD

>      --   HH:MM:SS'
>      'Apple' -- This is not case-sensitive, oddly enough.
>     );

## Useful Commands

The following commands are particularly useful. They allow you to get a sense of the current state of your databases.

### For Schemas

In the following, `<SchemaName>` should be substituted with an actual schema name.

>     SHOW SCHEMAS; -- List the schemas.
>     SHOW TABLES; -- List the tables in a schema.
>     DROP SCHEMA <SchemaName>; -- "Drop" (erase) SchemaName.

You can also use the variation

>     DROP SCHEMA IF EXISTS <SchemaName>;

that will not issue an error if `<SchemaName>` does not exist.

### For Tables

In the following, `<TableName>` should be substituted with an actual table name.

>     SHOW CREATE TABLE <TableName>-- Gives the command to "re-construct" TableName.
>     DESCRIBE <TableName>; -- Show the structure of TableName.
>     DROP TABLE <TableName>; -- "Drop" (erase) TableName.

You can also use the variation

>     DROP TABLE IF EXISTS <TableName>;

that will not issue an error if `<TableName>` does not exist.

### See Also

>     SELECT * FROM <TableName> -- List all the rows in TableName.
>     SHOW WARNINGS; -- Show the content of the latest warning issued.

## Overview of Constraints

There are six different kind of constraints that one can add to an attribute:

- Primary Key
- Foreign Key
- `NOT NULL`
- `UNIQUE`
- `DEFAULT`
- `CHECK`

We already know the first two from the relational model. The other four are new, and could not be described in this model.

We will review them below, and show how they can be specified at the time the table is declared, or added and removed later. For more in-depth examples, you can refer to W3Resource.

### Declaring Constraints

We will now see how to declare those constraints when we create the table (except for the foreign key, which we save for later).

>     /* code/sql/HW_ConstraintsPart1.sql */

>     DROP SCHEMA IF EXISTS HW_ConstraintsPart1;

>     CREATE SCHEMA HW_ConstraintsPart1;

>     USE HW_ConstraintsPart1;

>     CREATE TABLE HURRICANE (
>       NAME VARCHAR(25) PRIMARY KEY, WindSpeed INT DEFAULT 76
>         CHECK  (WindSpeed > 74 AND WindSpeed < 500),
>       -- 75mph is  the minimum to be considered as a hurricane
>       --    cf. https://www.hwn.org/resources/bws.html
>       Above VARCHAR(25)
>     );

>     CREATE TABLE STATE (
>       NAME  VARCHAR(25) UNIQUE, Postal_abbr CHAR(2) NOT NULL
>     );

If we wanted to combine multiple constraints, we would have to follow the order described at MySQL.

`MySQL` can output a description of those tables for us:

>     MariaDB [HW_ConstraintsPart1]> DESCRIBE HURRICANE;
>     +-----------+-------------+------+-----+---------+-------+
>     | Field     |  Type       | Null | Key | Default | Extra |
>     +-----------+-------------+------+-----+---------+-------+
>     | Name      | varchar(25) | NO   | PRI | NULL    |       |
>     | WindSpeed | int(11)     | YES  |     | 76      |       |
>     | Above     | varchar(25) | YES  |     | NULL    |       |
>     +-----------+-------------+------+-----+---------+-------+
>     3 rows in set (0.01 sec)

>     MariaDB [HW_ConstraintsPart1]> DESCRIBE STATE;
>     +-------------+-------------+------+-----+---------+-------+
>     | Field       | Type        | Null | Key | Default | Extra |
>     +-------------+-------------+------+-----+---------+-------+
>     | Name        | varchar(25) | NO   | PRI | NULL    |       |
>     | Postal_abbr | char(2)     | NO   | UNI | NULL    |       |
>     +-------------+-------------+------+-----+---------+-------+
>     2 rows in set (0.00 sec)

Note that more than one attribute can be the primary key, in which case the syntax needs to be something like the following:

>     /* code/sql/HW_PKtest.sql */
>     DROP SCHEMA IF EXISTS HW_PKtest;

>     CREATE SCHEMA HW_PKtest;

>     USE HW_PKtest;

>     CREATE TABLE TEST (
>       A INT, B INT,  PRIMARY KEY (A, B)
>     );

Note that in this case, a statement like

>     INSERT INTO TEST VALUE (1, NULL);

would result in an error: all the values that are part of the primary key needs to be non-`NULL`.

For the `UNIQUE` constraint, note that `NULL` can be inserted: the rationale is that all the values need to be different from one another or `NULL`.

A couple of comments about the `CHECK` constraint:

- Before MariaDB 10.2.1, `WindSpeed INT CHECK (WindSpeed > 74 AND WindSpeed < 500)` would have been parsed but would not have any effect, cf. MariaDB. Since MariaDB 10.2.1, the `CHECK` constraint are enforced.
- If we try to violate the `CHECK` constraint, with a command like

  >     INSERT INTO HURRICANE VALUES ("Test1", 12,  NULL);

  then the insertion would not take place, and the system would issue an error message:

  >     ERROR 4025 (23000): CONSTRAINT `HURRICANE.WindSpeed`  failed for `HW_ConstraintsPart1]>`.`HURRICANE`

  Note that you could still insert a value of NULL for the wind, and it would not triggered the error.

To use the `DEFAULT` value, use

>     INSERT INTO HURRICANE VALUES ("Test2", DEFAULT, NULL);

Note that, by default, the `DEFAULT` value is `NULL`, regardless of the datatype:

>     /* code/sql/HW_DefaultTest.sql */

>     DROP SCHEMA IF EXISTS HW_DEFAULT_test;

>     CREATE SCHEMA HW_DEFAULT_test;

>     USE HW_DEFAULT_test;

>     CREATE TABLE TEST (
>       TestA VARCHAR(15),  TestB INT, TestC FLOAT, TestD BOOLEAN,
>       TestE BIT(1),  TestF DATE
>     );

>     INSERT INTO TEST
>       VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

>     SELECT *
>     FROM TEST;

### Editing Constraints

Let us know pretend that we want to edit some attributes, by either adding or removing constraints. `SQL`’s syntax is a bit inconsistent on this topic, because it treats the constraints as being of different natures.

#### Primary Keys

Adding a primary key:

>     ALTER TABLE STATE ADD PRIMARY KEY (Name);

Removing the primary key:

>     ALTER TABLE STATE DROP PRIMARY KEY;

#### `UNIQUE` Constraint

Adding a `UNIQUE` constraint:

>     ALTER TABLE STATE ADD UNIQUE (Postal_abbr);

Removing a `UNIQUE` constraint:

>     ALTER TABLE STATE DROP INDEX Postal_abbr;

Note the difference between adding and removing the `UNIQUE` constraint: the parenthesis around `(Postal_abbr)` are mandatory when adding the constraint, but would cause an error when removing it!

#### `NOT NULL` Constraint

Adding the `NOT NULL` constraint:

>     ALTER TABLE STATE MODIFY Postal_abbr CHAR(2) NOT NULL;

Removing the `NOT NULL` constraint:

>     ALTER TABLE STATE MODIFY Postal_abbr CHAR(2);

The syntax of `NOT NULL` comes from the fact that this constraint is taken to be part of the datatype.

#### Default value

Changing the default value:

>     ALTER TABLE HURRICANE ALTER COLUMN WindSpeed SET DEFAULT 74;

Removing the default value:

>     ALTER TABLE HURRICANE ALTER COLUMN WindSpeed DROP DEFAULT;

#### Foreign key

Adding a foreign key constraint:

>     ALTER TABLE HURRICANE ADD FOREIGN KEY (Above) REFERENCES STATE(Name);

Removing a foreign key constraint is out of the scope of this lecture. If you are curious, you can have a look at W3Schools: dropping a foreign key constraint requires your constraint to have a name, something we did not introduce.

Two important remarks:

- The datatype of the foreign key has to be the exactly the same as the datatype of the attribute that we are referring.
- The target of the foreign key must be the primary key.

Refer to Problem 3.4 (Constraints on foreign keys) for a slightly more accurate picture of the constraints related to the creation of foreign keys. Note that a foreign key could be declared at the time of creation of the table as well, using the syntax we will introduce below.

### Testing the Constraints

Let us test our constraints:

>     INSERT INTO STATE VALUES('Georgia', 'GA');
>     INSERT INTO STATE VALUES('Texas', 'TX');
>     INSERT INTO STATE VALUES('FLORIDA', 'FL');
>     UPDATE STATE SET Name = 'Florida'
>       WHERE  Postal_abbr = 'FL';

>     -- There's an error with the following request. Why?
>     INSERT INTO HURRICANE VALUES('Irma', 150, 'FL');

>     /*
>     ERROR 1452 (23000): Cannot add or update a child row:  a foreign
>     key constraint fails (`HW_ConstraintsPart1`.`HURRICANE`, CONSTRAINT
>     `HURRICANE_ibfk_1` FOREIGN KEY (`Above`) REFERENCES `STATE` (`Name`))
>     */

>     INSERT INTO HURRICANE VALUES('Harvey', DEFAULT,  'Texas');
>     INSERT INTO HURRICANE VALUES('Irma', 150, 'Florida');
>     DELETE FROM HURRICANE
>       WHERE Name  = 'Irma';
>     INSERT INTO HURRICANE VALUES('Irma', 150, 'Georgia');

>     UPDATE HURRICANE SET Above = 'Georgia'
>       WHERE Name = 'Irma';

>     /*
>     MariaDB [HW_ConstraintsPart1]> SELECT * FROM  HURRICANE;
>     +--------+-----------+---------+
>     | Name   | WindSpeed | Above   |
>     +--------+-----------+---------+
>     | Harvey |        74 | Texas   |
>     | Irma   |       150 | Georgia |
>     +--------+-----------+---------+
>     */

>     -- There's an error with the following request. Why?
>     UPDATE HURRICANE SET Above = 'North Carolina'
>       WHERE Name  = 'Irma';

>     -- Let's patch it, by adding North Carolina to our  STATE table.
>     INSERT INTO STATE VALUES('North Carolina', 'NC');
>     UPDATE HURRICANE SET Above = 'North Carolina'
>       WHERE Name  = 'Irma';

## Foreign Keys

Let us come back more specifically to foreign key.

### A First Example

In the example below, we introduce the foreign key update and delete rules. We also introduce, passing by, the enumerated data type, and how to edit it.

>     CREATE TABLE STORM (
>       NAME VARCHAR(25) PRIMARY KEY, Kind ENUM ('Tropical
>         Storm',  'Hurricane'), WindSpeed INT,
>       Creation DATE
>     );

>     -- We can change the enumerated datatype:
>     ALTER TABLE STORM MODIFY Kind ENUM ('Tropical Storm',
>       'Hurricane',  'Typhoon');

>     CREATE TABLE STATE (
>       NAME  VARCHAR(25) UNIQUE, Postal_abbr CHAR(2) PRIMARY KEY,
>         Affected_by  VARCHAR(25), FOREIGN KEY (Affected_by)
>         REFERENCES  STORM (NAME) ON DELETE SET NULL ON UPDATE
>         CASCADE
>     );

Note that we can “inline” the foreign key constraint like we “inlined” the primary key constraint (cf. Stack Overflow), but that it will not be enforced!

Let us now illustrate this table by introducing some data in it:

>     INSERT INTO STORM
>       VALUES  ('Harvey', 'Hurricane', 130, '2017-08-17');

>     --  In the following, the entry gets created, but date  is
>     --    "corrected" to '2017-17-08'!
>     --    INSERT  INTO STORM
>     --      VALUES  ('Dummy', 'Hurricane', 120, '2017-17-08');
>     --    The error  message returned is
>     --      ERROR  1292 (22007) at line 34: Incorrect date value:
>     --      '2017-17-08' for column `HW_STORM`.`STORM`.`Creation`
>     --      at row 1
>     --    In the  following, we explicitely use 'DATE', and since
>     --      the date  is incorrect, nothing gets inserted.
>     --    INSERT  INTO STORM
>     --      VALUES  ('Dummy2', 'Hurricane', 120, DATE
>     --      '2017-17-08');
>     --    ERROR  1525 (HY000) at line 40: Incorrect DATE value:
>     --      '2017-17-08'
>     --    The next  one sets NULL for DATE.

>     INSERT INTO STORM
>       VALUES  ('Irma', 'Tropical Storm', 102, DEFAULT);

MySQL will always notify you if there is an error in a date attribute when you use the `DATE` prefix.

>     INSERT INTO STATE
>       VALUES  ('Georgia', 'GA', NULL);

>     INSERT INTO STATE
>       VALUES  ('Texas', 'TX', NULL);

>     INSERT INTO STATE
>       VALUES  ('Florida', 'FL', NULL);

>     --  This instruction is not using the primary key, is  that a
>     --    problem?
>     UPDATE
>       STATE
>     SET Affected_by = 'Harvey'
>     WHERE Name = 'Georgia';

>     UPDATE
>       STORM
>     SET Name = 'Harley'
>     WHERE Name = 'Harvey';

>     DELETE FROM STORM
>     WHERE Name = 'Harley';

We will see in the “Reverse-Engineering” section why this schema is poorly designed, but for now, let’s focus on the foreign keys and their restrictions.

### Foreign Keys Restrictions

The following is a code-driven explanation of the foreign key update and delete rules (or “restrictions”). It is intended to make you understand the default behavior of foreig keys, and to understand how the system reacts to the possible restrictions.

>     CREATE TABLE F_Key(
>       Attribute  VARCHAR(25) PRIMARY KEY
>     );

>     CREATE TABLE Table_default(
>       Attribute1  VARCHAR(25) PRIMARY KEY,
>       Attribute2  VARCHAR(25),
>       FOREIGN KEY  (Attribute2) REFERENCES F_Key(Attribute)

>     );

>     -- By default, this foreign key will restrict.

>     CREATE TABLE Table_restrict(
>       Attribute1  VARCHAR(25) PRIMARY KEY,
>       Attribute2  VARCHAR(25),
>       FOREIGN KEY  (Attribute2) REFERENCES F_Key(Attribute)
>         ON  DELETE RESTRICT
>         ON  UPDATE RESTRICT
>     );

>     CREATE TABLE Table_cascade(
>       Attribute1  VARCHAR(25) PRIMARY KEY,
>       Attribute2  VARCHAR(25),
>       FOREIGN KEY  (Attribute2) REFERENCES F_Key(Attribute)
>         ON  DELETE CASCADE
>         ON  UPDATE CASCADE
>     );

>     CREATE TABLE Table_set_null(
>       Attribute1  VARCHAR(25) PRIMARY KEY,
>       Attribute2  VARCHAR(25),
>       FOREIGN KEY  (Attribute2) REFERENCES F_Key(Attribute)
>         ON DELETE  SET NULL
>         ON  UPDATE SET NULL
>     );

>     /*
>     * You might encounter a
>     * ON UPDATE SET DEFAULT
>     * but this reference option (cf.  https://mariadb.com/kb/en/library/foreign-keys/ )
>     * worked only with a particular engine (  https://mariadb.com/kb/en/library/about-pbxt/ )
>     * and will not be treated here.
>     */

>     INSERT INTO F_Key VALUES('First Test');
>     INSERT INTO Table_default VALUES('Default', 'First  Test');
>     INSERT INTO Table_restrict VALUES('Restrict', 'First  Test');
>     INSERT INTO Table_cascade VALUES('Cascade', 'First  Test');
>     INSERT INTO Table_set_null VALUES('Set null', 'First  Test');

>     SELECT * FROM Table_default;
>     SELECT * FROM Table_restrict;
>     SELECT * FROM Table_cascade;
>     SELECT * FROM Table_set_null;

>     -- The following will fail because of the  Table_default table:
>     UPDATE F_Key SET Attribute = 'After Update'
>     WHERE  Attribute = 'First Test';
>     DELETE FROM F_Key
>     WHERE  Attribute = 'First Test';

>     -- Let us drop this table, and try again.
>     DROP TABLE Table_default;

>     -- The following fails too, this time because of the  Table_restrict table:
>     UPDATE F_Key SET Attribute = 'After Update'
>       WHERE  Attribute = 'First Test';
>     DELETE FROM F_Key
>       WHERE  Attribute = 'First Test';

>     -- Let us drop this table, and try again.
>     DROP TABLE Table_restrict;

>     -- Let's try again:
>     UPDATE F_Key SET Attribute = 'After Update' WHERE  Attribute = 'First Test';

>     -- And let's print the situation after this update:
>     SELECT * FROM Table_cascade;
>     SELECT * FROM Table_set_null;

>     /*
>     MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM  Table_cascade;
>     +------------+--------------+
>     | Attribute1 | Attribute2   |
>     +------------+--------------+
>     | Cascade    | After Update |
>     +------------+--------------+
>     1 row in set (0.00 sec)

>     MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM  Table_set_null;
>     +------------+------------+
>     | Attribute1 | Attribute2 |
>     +------------+------------+
>     | Set null   |  NULL      |
>     +------------+------------+
>     1 row in set (0.00 sec)
>     */

>     -- Let's make a second test.
>     INSERT INTO F_Key VALUES('Second Test');
>     INSERT INTO Table_cascade VALUES('Default', 'Second  Test');
>     INSERT INTO Table_set_null VALUES('Restrict', 'Second  Test');

>     DELETE FROM F_Key
>       WHERE  Attribute = 'Second Test';

>     /*
>     MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM Table_cascade;
>     +------------+--------------+
>     | Attribute1 | Attribute2   |
>     +------------+--------------+
>     | Cascade    | After Update |
>     +------------+--------------+
>     1 row in set (0.00 sec)

>     MariaDB [HW_CONSTRAINTS_PART3]> SELECT * FROM  Table_set_null;
>     +------------+------------+
>     | Attribute1 | Attribute2 |
>     +------------+------------+
>     | Restrict   |  NULL      |
>     | Set null   |  NULL      |
>     +------------+------------+
>     2 rows in set (0.00 sec)
>     */

### Constructing and Populating a New Example

#### Construction

- Remember, we start by creating a schema and tables inside of it.
- What if foreign keys are mutually dependent? What if we have something like:

>     /* code/sql/HW_ProfExample.sql */
>     CREATE TABLE PROF (
>       Login  VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25),
>         Department  CHAR(5)
>     );

>     CREATE TABLE DEPARTMENT (
>       Code CHAR(5)  PRIMARY KEY, NAME VARCHAR(25), Head
>         VARCHAR(25), FOREIGN KEY (Head) REFERENCES PROF (LOGIN)
>         ON UPDATE  CASCADE
>     );

>     ALTER TABLE PROF
>       ADD FOREIGN  KEY (Department) REFERENCES DEPARTMENT (Code);

Note the structure of the `ALTER TABLE` command:

- … `KEY` Department `REFERENCES` Code;⇒ error
- … `KEY` (Department) `REFERENCES` (Code);⇒ error
- … `KEY` PROF(Department) `REFERENCES` DEPARTMENT(Code); ⇒ ok

>     CREATE TABLE STUDENT (
>       Login  VARCHAR(25) PRIMARY KEY, NAME VARCHAR(25),
>         Registered  DATE, Major CHAR(5),
>       FOREIGN KEY  (Major) REFERENCES DEPARTMENT (Code)

>     );

>     CREATE TABLE GRADE (
>       Login  VARCHAR(25), Grade INT, PRIMARY KEY (LOGIN, Grade),
>       FOREIGN KEY  (LOGIN) REFERENCES STUDENT (LOGIN)
>     );

#### Populating

We can insert multiple values at once:

>     INSERT INTO DEPARTMENT
>       VALUES  ('MATH', 'Mathematics', NULL), ('CS', 'Computer
>         Science',  NULL);

We can specify which attributes we are giving:

>     INSERT INTO DEPARTMENT (Code, Name)
>       VALUES ('CYBR',  'Cyber Secturity');

And we can even specify the order (even the trivial one):

>     INSERT INTO PROF (LOGIN, Department, Name)
>       VALUES  ('caubert', 'CS', 'Clément Aubert');

>     INSERT INTO PROF (LOGIN, Name, Department)
>       VALUES  ('aturing', 'Alan Turing', 'CS'), ('perdos', 'Paul
>         Erdős',  'MATH'), ('bgates', 'Bill Gates', 'CYBR');

>     INSERT INTO STUDENT (LOGIN, Name, Registered, Major)
>       VALUES  ('jrakesh', 'Jalal Rakesh', DATE '2017-12-01',
>         'CS'),  ('svlatka', 'Sacnite Vlatka', '2015-03-12',
>         'MATH'),  ('cjoella', 'Candice Joella', '20120212',
>         'CYBR'),  ('aalyx', 'Ava Alyx', 20121011, 'CYBR'),
>         ('caubert',  'Clément Aubert', NULL, 'CYBR');

>     INSERT INTO GRADE
>       VALUES  ('jrakesh', 3.8), ('svlatka', 2.5);

Note the date literals.

### A Bit More on Foreign Keys

Note that we can create foreign keys to primary keys made of multiple attributes, and to the primary key of the table we are currently creating.

>     /* code/sql/HW_AdvancedFK.sql */

>     CREATE TABLE T1(A1 INT,
>                     A2 INT,
>                     B INT,
>                     PRIMARY KEY(A1, A2)
>     );

>     CREATE TABLE T2(A1 INT,
>                     A2 INT,
>                     B1 INT PRIMARY KEY,
>                     B2 INT,
>     --  We can create a "pair" of foreign key in one line, as follows:
>                     FOREIGN KEY (A1,A2) REFERENCES T1(A1,A2),
>     --  We can create a foreign key that references the
>           primary key of the table we are currently creating, and name it,
>           as follows:
>                     CONSTRAINT My_pk_to_T1
>                     FOREIGN KEY (B2) REFERENCES T2(B1)
>     );

>     --  The benefit of naming our fk constraint is that,
>           if we violate it, for instance with
>     --  INSERT INTO T2 VALUES (1, 1, 1, 3);
>     --  then the name of the constraint (here "My_pk_to_T1") would be displayed
>           in the error message:
>     --  Cannot add or update a child row: a foreign key constraint fails
>           (`db_9_9837c1`.`t2`, CONSTRAINT `My_pk_to_T1` FOREIGN KEY (`B2`)
>           REFERENCES `t2` (`B1`))

## A First Look at Conditions

Order of clauses does not matter, not even for optimization purpose.

>     UPDATE <table>
>     SET <attribute1> = <value1>, <attribute2> = <value2>, …
>     WHERE <condition>;
>     SELECT <attribute list, called projection attributes>
>     FROM <table list>
>     WHERE <condition>;
>     DELETE FROM <table list>
>     WHERE <condition>;

Conditions can

- use the comparison operators:

- `=`, equal to
- `>`, greater than,
- `<` less than,
- `>=` greater than or equal to,
- `<=` less than or equal to,
- `<>` not equal to.

- be compounded:

- condition1 `AND` condition2
- condition1 `OR` condition2
- `NOT` condition
- Usage of parenthesis is possible

- be trivial or even absent,
- use regular expressions:

- uses the expression `LIKE`,
- escape character is `\`,
- `_` will match one character (any character), `%` will match any number of character,
- advanced regular expression possible using the `REGEXP` keyword.

>     SELECT LOGIN
>     FROM STUDENT;

>     UPDATE
>       DEPARTMENT
>     SET Head = "aturing"
>     WHERE Code = "MATH";

>     UPDATE
>       DEPARTMENT
>     SET Head = "bgates"
>     WHERE Code = "CS"
>       OR Code = "CYBR";

>     SELECT LOGIN
>     FROM STUDENT
>     WHERE NOT Major = "CYBR";

>     SELECT LOGIN, Name
>     FROM PROF
>     WHERE Department = "CS";

>     SELECT LOGIN
>     FROM STUDENT
>     WHERE Major = "CYBR"
>       AND Registered > DATE "20121001";

>     SELECT LOGIN
>     FROM STUDENT
>     WHERE Name LIKE "Ava%";

>     SELECT Name
>     FROM PROF
>     WHERE LOGIN LIKE "_aubert";

Note that `LIKE` is by default case-insensitive, both in MariaDB and in MySQL. The COLLATE operator can be used to force the search to be case-sensitive, as well as `LIKE` `BINARY`.

## Three-Valued Logic

Cf. (Elmasri and Navathe 2010, 5.1.1), (Elmasri and Navathe 2015, 7.1.1)

### Meaning of `NULL`

`NULL` is

1. Unknown value (“Nobody knows”)

   > What is the date of birth of Jack the Ripper?
   > Does P equal NP?

2. Unavailable / Withheld (“I do not have that information with me at the moment”)

   > What is the number of english spies in France?
   > What is the VIN of your car?
   > What is the identity of the Tiananmen Square person?

3. Not Applicable (“Your question does not make sense”)

   > What is the US SSN of a french person?
   > What is the email address of an author of the XIXth century?

### Comparison with Unknown Values

If `NULL` is involved in a comparison, the result evaluates to “Unknown.” If `NULL` is involved in a comparison, the result evaluates to “Unknown”.

AND | T | F | U
--- | --- | --- | ---
T | T | F | U
F | F | F | F
U | U | F | U

OR | T | F | U
--- | --- | --- | ---
T | T | T | T
F | T | F | U
U | T | U | U

NOT |
--- | ---
T | F
F | T
U | U

You can test if a value is `NULL` with `IS NULL`.

>     INSERT INTO DEPARTMENT
>       VALUES ("Hist", "History", NULL);

>     SELECT *
>     FROM DEPARTMENT
>     WHERE Head IS NULL;

>     SELECT *
>     FROM DEPARTMENT
>     WHERE Head IS NOT NULL;

>     SELECT COUNT(*)
>     FROM GRADE
>     WHERE Grade IS NULL;

Note that you can not use `IS` to compare values: this key word is reserved to test if a value is (not) `NULL`, and nothing else.

There are no `if…then…else` statements in `SQL`, but you can do something similar with `CASE` (cf. MySQL). However, note that `SQL` is probably not the right place to try to control the flow of execution.

This probably depends on the system a lot, but one could wonder if MySQL uses some form of short-cut evaluation when comparing with `NULL`. Unfortunately, even with three times (!) the verbose option, MySQL does not give more insight as to whenever it drops comparing values once a NULL was encountered (cf. MySQL, you can log-in using `mysql -u testuser -p --password=password -v -v -v` to activate the most verbose mode). Normally, `EXPLAIN` (MySQL) should be useful in answering this question, but failed to answer it as well.

## Various Tools

For `DISTINCT`, `ALL` and `UNION`, cf. (Elmasri and Navathe 2010, 4.3.4) or (Elmasri and Navathe 2015, 6.3.4). For `ORDER BY`, cf. (Elmasri and Navathe 2010, 4.3.6) or (Elmasri and Navathe 2015, 6.3.6). For aggregate functions, cf. (Elmasri and Navathe 2010, 5.1.7) or (Elmasri and Navathe 2015, 7.1.7).

### `AUTO_INCREMENT`

Something that is not exactly a constraint, but that can be used to “qualify” domains, is the `AUTO_INCREMENT` feature of MySQL. Cf. MySQL, you can have MySQL increment a particular attribute (most probably intended to be your primary key) for you.

### Transactions

We can save the current state, and start a series of transactions, with the command

>     START TRANSACTION;

All the commands that follows are “virtually” executed: you can undo them all using

>     ROLLBACK;

This puts you back in the state you were in before starting the transaction. If you want all the commands you typed in-between to be actually enforced, you can use the command

>     COMMIT;

Nested transactions are technically possible, but they are counter-intuitive and should be avoided, cf. SQL Skills.

### `DISTINCT` / `ALL`

The result of a `SELECT` query, for instance, is a table, and `SQL` treats tables as multi-set, hence there can be repetitions in the result of a query, but we can remove them:

>     SELECT DISTINCT Major FROM STUDENT;

The default behaviour is equivalent to specifying ALL, and it display the duplicates. In this case, it would be

>     > SELECT Major FROM STUDENT;
>     +-------+
>     | Major |
>     +-------+
>     | CS    |
>     | CYBR  |
>     | CYBR  |
>     | CYBR  |
>     | MATH  |
>     +-------+

### `UNION`

Set-theoretic operations are available as well. For instance, one can use:

>     (SELECT Login FROM STUDENT) UNION (SELECT Login FROM  PROF);

to collect all the logins from both tables.

There is also `INTERSECT` and `EXCEPT` in the specification, but MySQL does not implement them (cf. Wikipedia).

### `ORDER BY`

You can have `ORDER BY` specifications:

>     SELECT LOGIN
>     FROM GRADE
>     WHERE Grade > 2.0
>     ORDER BY Grade;

>     SELECT LOGIN
>     FROM GRADE
>     WHERE Grade > 2.0
>     ORDER BY Grade DESC;

>     SELECT LOGIN, Major
>     FROM STUDENT
>     ORDER BY Major, Name;

`ORDER BY` order by ascending order by default.

### Aggregate Functions

You can use `MAX`, `SUM`, `MIN`, `AVG`, `COUNT`:

>     SELECT MAX(Registered) FROM STUDENT;

returns the “greatest” date of registration of a student, i.e., the date of the latest registration.

>     SELECT COUNT(Name) FROM STUDENT;

returns the number of names, i.e., the number of students.

>     SELECT COUNT(DISTINCT Name) FROM STUDENT;

returns the number of different names (which in this case is the same as the number of names, since we have no homonyms).

Note that `AVG` returns the average of all non-`NULL` values, as we can see on the following example:

>     /* code/sql/HW_Avg.sql */
>     CREATE TABLE TEST (
>       Test INT
>     );

>     INSERT INTO TEST
>       VALUES (NULL), (0), (10);

>     SELECT AVG(Test)
>     FROM TEST;

>     -- Returns 5.0

### Aliases for Columns

We can use aliases for the columns. Compare

>     SELECT Login FROM PROF;
>     +---------+
>     | Login   |
>     +---------+
>     | aturing |
>     | caubert |
>     | bgates  |
>     | perdos  |
>     +---------+

with

>     SELECT Login AS "Username" FROM PROF;
>     +----------+
>     | Username |
>     +----------+
>     | aturing  |
>     | caubert  |
>     | bgates   |
>     | perdos   |
>     +----------+

Aliases can also be used on table names. Aliases for columns are a helpful way of describing the result of the query, while alias on table have a specific purpose that will be clearer as we study select-project-join queries.

## More Select Queries

For select-project-join, cf. (Elmasri and Navathe 2010, 4.3.1) or (Elmasri and Navathe 2015, 6.3.1). For aliases, cf. (Elmasri and Navathe 2010, 4.3.2) or (Elmasri and Navathe 2015, 6.3.2), For nested queries, cf. (Elmasri and Navathe 2010, 5.1.2) or (Elmasri and Navathe 2015, 7.1.2).

### Select-Project-Join

>     SELECT LOGIN
>     FROM PROF, DEPARTMENT
>     WHERE DEPARTMENT.Name = "Mathematics"
>       AND Department = Code;

- `Department.Name = 'Mathematics'` is the selection condition
- `Department = Code` is the join condition, because it combines two tuples.
- Why do we use the fully qualified name attribute for `Name`?
- We have to list all the tables we want to consult, even if we use fully qualified names.

>     SELECT Name
>     FROM STUDENT, GRADE
>     WHERE Grade > 3.0
>       AND STUDENT.Login = GRADE.Login;

- `Grade > 3.0` is the selection condition
- `STUDENT.Login = GRADE.Login` is the join condition

We can have two join conditions!

>     SELECT PROF.Name
>     FROM PROF, DEPARTMENT, STUDENT
>     WHERE STUDENT.Name = "Ava Alyx"
>       AND STUDENT.Major = DEPARTMENT.Code
>       AND DEPARTMENT.Head = PROF.Login;

Note that for the kind of join we are studying (called “inner joins”), the order does not matter.

In Problem 3.3 (Duplicate rows in `SQL`), we saw that `SQL` was treating tables as multi-sets, i.e., repetitions are allowed. This can lead to strange behaviour when performing Select-Project-Join queries. Consider the following example:

### Aliasing Tuples

We can use aliases on tables to shorten the previous query:

>     SELECT PROF.Name
>     FROM PROF, DEPARTMENT, STUDENT AS B
>     WHERE B.Name = "Ava Alyx"
>       AND B.Major = DEPARTMENT.Code
>       AND DEPARTMENT.Head = PROF.Login;

We can use multiple aliases to make it even shorter (but less readable):

>     SELECT A.Name
>     FROM PROF AS A, DEPARTMENT AS B, STUDENT AS C
>     WHERE C.Name = "Ava Alyx"
>       AND C.Major = B.Code
>       AND B.Head = A.Login;

For those two, aliases are convenient, but not required to write the query. In some cases, we cannot do without aliases. For instance if we want to compare two rows in the same table:

>     SELECT Other.Login
>     FROM GRADE AS Mine, GRADE AS Other
>     WHERE Mine.Login = "aalyx"
>       AND Mine.Grade < Other.Grade;

Generally, when you want to perform a join within the same table, then you have to “make two copies of the tables” and name them differently using aliases. Let us try to write a query that answers the question

> What are the login of the professors that have the same department as the professor whose login is caubert?

We need a way of distinguising between the professors we are projecting on (the one whole login is caubert) and the one we are joining with (the ones that have the same department). This can be done using something like:

>     SELECT JOINT.Login
>     FROM PROF AS PROJECT, PROF AS JOINT
>     WHERE PROJECT.Login = "caubert"
>       AND PROJECT.Department = JOINT.Department;

Note that we are “opening up two copies of the PROF tables”, and naming them differently (`PROJECT` and `JOINT`).

Another (improved) example of a similar query is

>     SELECT Fellow.Name AS "Fellow of Ava"
>     FROM STUDENT AS Ava, STUDENT AS Fellow
>     WHERE Ava.Name = "Ava Alyx"
>       AND Fellow.Major = Ava.Major
>       AND NOT Fellow.Login = Ava.Login;

A couple of remarks about this query:

- At the beginning of the query, AS `"Fellow of Ava"` is another kind of aliasing, mentioned in a previous section.
- In the condition, `NOT Fellow.Login = Ava.Login` guarantees that we will not select Ava again, and exclude her from the results (Ava is not supposed to be a fellow of herself).
- In the (unlikely, but possible) case of an homonym, writing `NOT Fellow.Name = Me.Name;` instead of `NOT Fellow.Login = Ava.Login` would prevent the homonym from occuring in the results.
- In the condition, substituting `AND NOT Me = Fellow` by `NOT Fellow.Login = Ava.Login` would not work: you have to compare attributes of the tuples, not the tuples.

### Nested Queries

Let us look at a first example

>     SELECT LOGIN
>     FROM GRADE
>     WHERE Grade > (
>         SELECT AVG(Grade)
>         FROM GRADE);

A nested query is made of an outer query (`SELECT Login…`) and an inner query (`SELECT AVG(Grade)…`). Note that the inner query does not terminate with a ;.

Logical operators such as `ALL` or `IN` can be used in nested queries. To learn more about those operators, refer to W3Schools.

An example could be

>     SELECT LOGIN
>     FROM GRADE
>     WHERE Grade >= ALL (
>         SELECT Grade
>         FROM GRADE
>         WHERE Grade IS NOT NULL);

Note that

- We have to use `>=`, and not `>`, since no grade is strictly greater than itself.
- The part `IS NOT NULL` is needed: otherwise, if one of the grade is `NULL`, then the comparison would yelds “unknown”, and no grade would be greater than all of the others.
- This query could be simplified, using `MAX`:

>     SELECT LOGIN
>     FROM GRADE
>     WHERE Grade >= (
>         SELECT MAX(Grade)
>         FROM GRADE);

Answering the question

> What are the logins of the professors belonging to a department that is the major of at least one student whose name ends with an “a”?

–that sounds like the what would ask a police officer in a whodunit– could be answer using

>     SELECT LOGIN
>     FROM PROF
>     WHERE DEPARTMENT IN (
>         SELECT Major
>         FROM STUDENT
>         WHERE LOGIN LIKE "%a");

For this query, we could not use `=`, since more than one major could be returned.

Furthermore, nested query that uses `=` can often be rewritten without being nested. For instance,

>     SELECT LOGIN
>     FROM PROF
>     WHERE DEPARTMENT = (
>         SELECT Major
>         FROM STUDENT
>         WHERE LOGIN = "cjoella");

becomes

>     SELECT PROF.Login
>     FROM PROF, STUDENT
>     WHERE DEPARTMENT = Major
>       AND STUDENT.Login = "cjoella";

Conversly, you can sometimes write select-project-join as nested queries For instance,

>     SELECT Name
>     FROM STUDENT, GRADE
>     WHERE Grade > 3.0
>       AND STUDENT.Login = GRADE.Login;

becomes

>     SELECT Name
>     FROM STUDENT
>     WHERE LOGIN IN (
>         SELECT LOGIN
>         FROM GRADE
>         WHERE Grade > 3.0);

## Procedures

A “stored” procedure is a `SQL` function statements that can take arguments and may be called from another part of your program. Stated differently, a procedure is a serie of statements stored in a schema, that can easily be executed repeatedly, cf. MySQL or MariaDB.

Imagine we have the following:

>     /* code/sql/HW_ProcedureExamples.sql */
>     CREATE TABLE STUDENT (
>       Login INT PRIMARY KEY, NAME VARCHAR(30), Major
>         VARCHAR(30), Email VARCHAR(30)
>     );

>     INSERT INTO STUDENT
>       VALUES (123, "Test A", "CS", "a@a.edu"), (124, "Test B",
>         "IT", "b@a.edu"), (125, "Test C", "CYBR", "c@a.edu");

`SQL` is extremely literal: when it reads the delimiter ;, it must execute the command that was shared. But a procedure, being composed of commands, will contain the ; symbol. To “solve” this (weird) issue, and be able to define a procedure, we have to “temporarily alter the language”, using `DELIMITER //` that makes the delimiter being // instead of ;.

> The symbols `$$` are often used too, and the documentation, at MySQL, reads:

> > You can redefine the delimiter to a string other than // and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash (`\`) character because that is the escape character for MySQL.

> The minus sign twice is also a poor choice, since it is used for commenting.

In any case, we can then define and execute a simpe procedure called `STUDENTLIST` as follows:

>     DELIMITER $$
>     CREATE PROCEDURE STUDENTLIST ()
>     BEGIN
>       SELECT *
>       FROM STUDENT;

>     --  This ";" is not the end of the procedure definition!
>     END;
>     $$
>     --  This is the delimiter that marks the end of the procedure
>     --    definition.
>     DELIMITER ;

>     --  Now, we want ";" to be the "natural" delimiter again.
>     CALL STUDENTLIST ();

>     --  Now, we want ";" to be the "natural" delimiter again.
>     CALL STUDENTLIST ();

A procedure an also take arguments, and an example could be:

>     DELIMITER $$
>     CREATE PROCEDURE STUDENTLOGIN (
>       NameP VARCHAR(30)
>     )
>     BEGIN
>       SELECT LOGIN
>       FROM STUDENT
>       WHERE NameP = Name;

>     END;
>     $$
>     DELIMITER ;

>     SHOW CREATE PROCEDURE STUDENTLOGIN;

>     --  This display information about the procedure just created.
>     --    We can pass quite naturally an argument to our
>     --    procedure.
>     CALL STUDENTLOGIN ("Test A");

## Triggers

A trigger is a series of statements stored in a schema that can be automatically executed whenever a particular event in the schema occurs. Triggers are extremely powerfull, and are a way of automating part of the work in your database. In MariaDB, you could have the following program.

Imagine we have the following:

>     CREATE TABLE STUDENT (
>       Login VARCHAR(30) PRIMARY KEY, Average FLOAT
>     );

>     CREATE TABLE GRADE (
>       Student VARCHAR(30), Exam VARCHAR(30), Grade INT, PRIMARY
>         KEY (Student, Exam), FOREIGN KEY (Student) REFERENCES
>         STUDENT (LOGIN)
>     );

We want to create a trigger that counts the number of times something was inserted in our `STUDENT` table. `SQL` supports some primitive form of variables (cf. MySQL and MariaDB). There is no “clear” form of type, MySQL reads:

> In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type. To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or ’’ to define its type before you use it.

In other words, `SQL` just “guess” the type of your value and go with it. Creating a simple trigger that increment a variable every time an insertion is performed in the `STUDENT` table can be done as follows:

>     SET @number_of_student = 0;

>     CREATE TRIGGER NUMBER_OF_STUDENT_INC
>       AFTER INSERT ON STUDENT
>       FOR EACH ROW
>       SET @number_of_student = @number_of_student + 1;

Now, assume we want to create a trigger that calculates the average for us. Note that the trigger will need to manipulate two tables (`STUDENT` and `GRADE`) at the same time.

>     CREATE TRIGGER STUDENT_AVERAGE
>       AFTER INSERT ON GRADE
>       FOR EACH ROW -- Woh, a whole query inside our trigger!
>       UPDATE STUDENT
>       SET STUDENT.Average = (
>       SELECT AVG(Grade)
>       FROM GRADE
>       WHERE GRADE.Student = STUDENT.Login)
>     WHERE STUDENT.Login = NEW.Student;

>     --  The "NEW" keyword here refers to the "new" entry
>     --    that is being inserted by the INSERT statement
>     --    triggering the trigger.

The source code contains examples of insertion and explanations on how to witness the trigger in action.

## Setting Up Your Work Environment

This part is a short tutorial to install and configure a working relational DBMS. We will proceed in 5 steps:

1. Install the required software,
2. Creat a user,
3. Log-in as this user,
4. Create and populate our first database,
5. Discuss the security holes in our set-up.

### Installation

You will install the MySQL DataBase Managment System, or its community-developed fork, MariaDB. Below are the instruction to install MySQL Community Edition on Windows 10 and macOS, and MariaDB on Linux-based distribution, but both are developped for every major operating system (macOS, Windows, Debian, Ubuntu, etc.): feel free to pick one or the other, it will not make a difference in this course (up to some minor aspects). MySQL is more common, MariaDB is growing, both are released under GNU General Public License, well-documented and free of charge for their “community” versions.

It is perfectly acceptable, and actually encouraged, to install MySQL or MariaDB on a virtual machine for this class. You can use the Windows Subsystem for Linux, VMware or Virtual Box to run a “sandboxed” environment that should keep your data isolated from our experimentations.

Below are precise and up-to-date instructions, follow them carefully, read the messages displayed on your screen, make sure a step was correctly executed before moving to the next one, and everything should be all right. Also, remember:

1. Do not wait, set your system early.
2. To look for help, be detailed and clear about what you think went wrong.

The following links could be useful:

- MariaDB on Installing and Upgrading
- MySQL on Installation Workflow, and particularly the page on windows installation and the page on Linux installation using package-managers,
- MySQL on Connecting and Disconnecting
- Linode on Installing MySQL

#### Installing MySQL on Windows 10

1. Visit MySQL, click on “Download” next to

   > Windows (x86, 32-bit), MSI Installer XXX YYY (mysql-installer-web-community-XXX.msi)

   where XXX is a number version (e.g., 8.0.13.0.), and YYY is the size of the file (e.g., 16.3M). On the next page, click on the (somewhat hidden) “No thanks, just start my download.” button.
2. Save the “mysql-installer-web-community-XXX.msi” file, and open it. If there is an updated version of the installer available, agree to download it. Accept the license term.
3. We will now install the various components needed for this class, leaving all the choices by defaults. This means that you need to do the following:
   1. Leave the first option on “Developer Default” and click on “Next”, or click on “Custom”, and select the following:
   2. Click on “Next” even if you do not meet all the requirements
   3. Click on “Execute”. The system will download and install several softwares (this may take some time).
   4. Click on “Next” twice, leave “Type and Networking” on “Standalone MySQL Server / Classic MySQL Replication” and click “Next”, and leave the next options as they are (unless you know what you do and want to change the port, for instance) and click on “Next”.
   5. You now need to choose a password for the MySQL root account. It can be anything, just make sure to memorize it. Click on “Next”.
   6. On the “Windows Service” page, leave everything as it is and click on “Next”.
   7. On the “Plugins and Extensions” page, leave everything as it is and click on “Next”.
   8. Finally, click “Execute” on the “Apply Configuration” page, and then on “Finish”.
   9. Click on “Cancel” on the “Product Configuration” page and confirm that you do not want to add products: we only need to have MySQL Server XXX configured.
4. We now want to make sure that MySQL is running: launch Windows’ “Control Panel”, then click on “Administrative Tools”, and on “Services”. Look for “MySQLXX”, its status should be “Running”. If it is not, right-click on it and click on “Start”.
5. Open a command prompt (search for cmd, or use PowerShell) and type

   >     cd "C:\Program Files\MySQL\MySQL Server  8.0\bin"

   If this command fails, it is probably because the version number changed: open the file explorer, go to `C:\Program Files\MySQL\`, look for the right version number, and update the command accordingly. Then, enter

   >     mysql -u root -p

   and enter the password you picked previously for the root account. You are now logged as root in your database management system, you should see a brief message, followed by a prompt

   >     mysql >

6. Now, move on to “Creating a User”.

#### Installing MySQL on macOS

The instructions are almost the same as for Windows. Read MySQL OSX Installation and download the file from MySQL Downloads once you selected “macOS” as your operating system. Install it, leaving everything by default but adding a password (refer to the instructions for windows). Then, open a command-line interface (the terminal), enter

>     mysql -u root -p

and enter the password you picked previously for the root account. You are now logged as root in your database management system, you should see a brief message, followed by a prompt

>     mysql >

Now, move on to “Creating a User”.

#### Installing MariaDB on Linux

- Install, through your standard package management system (`apt` or `aptitude` for debian-based systems, `pacman` for Arch Linux, etc.), the packages `mysql-client` and `mysql-server` (or `default-mysql-client` and `default-mysql-server`) as well as their dependencies.

  > Yes, the package is called `mysql-server`, but it actually install the package `mariadb-server-10.3` or higher… So do not be confused: we are, indeed, installing MariaDB!

- Open a terminal and type

  >     /etc/init.d/mysql status

  or, as root,

  >     service mysql status

  to see if MySQL is running: if you read something containing

  >     Active: active (running)

  then you can move on to the next step, otherwise run (as root)

  >     service mysqld start

  and try again.
- As root, type in your terminal

  >     mysql_secure_installation

  You will be asked to provide the current password for the root MySQL user: this password has not be defined yet, so just hit “Enter”. You will be asked if you want to set a new password (that you can freely chose, just make sure to memorize it). Then, answer “n” to the question “Remove anonymous users?”, “Y” to “Disallow root login remotely?”, “n” to “Remove test database and access to it?” and finally “Y” to “Reload privilege tables now?”.
- Still as root, type in your terminal

  >     mysql -u root -p

  and enter the password you picked previously for the root account. You are now logged as root in your database management system: you should see a brief message, followed by a prompt

  >     MariaDB [(none)]>

- Now, move on to “Creating a User”.

### Creating a User

This step will create a non-root user and grant it some rights. Copy-and-paste or type the following three commands, one by one (that is, enter the first one, hit “enter”, enter the second, hit “enter”, etc.). This step will create a non-root user and grant it some rights. Copy-and-paste or type the following three commands, one by one (that is, enter the first one, hit “enter”, enter the second, hit “enter”, etc.).

> By default, MySQL and MariaDB only create a root user with all privileges and no password, but we added a password at the previous step.

We first create a new user called testuser on our local installation, and give it the password `password`:

>     CREATE USER 'testuser'@'localhost' IDENTIFIED BY  'password';

Then, we grant the user all the privileges on the databases whose name starts with `HW_`:

>     GRANT ALL PRIVILEGES ON  `HW\_%` . * TO 'testuser'@'localhost';

Be careful: backticks (```) are surrounding `HW\_%` whereas single quotes (`'`) are surrounding `testuser` and `localhost`.

And then we quit the DBMS, using

>     EXIT;

The message displayed after the two first commands should be

>     Query OK, 0 rows affected (0.00 sec)

and the message displayed after the last command should be

>     Bye

### Logging-In as testuser

We now log in as the normal user called “testuser”.

Linux users should type as a normal user, i.e., not as root, in their terminal the following, and Windows users should type in their command prompt the following:

>     mysql -u testuser -p

> Provided the working directory is still `C:\Program Files\MySQL\MySQL Server 8.0\bin` or similar. Cf. MySQL to add the MySQL bin directory to your Windows system `PATH` environment variable.

Enter `password` as your password. If you are prompted with a message

>     ERROR 1045 (28000): Access denied for user  'testuser'@'localhost' (using password: YES)

then you probably typed the wrong password. Otherwise, you should see a welcoming message from MySQL or MariaDB and a prompt.

To save yourself the hassle of typing the password, you can use

>     mysql -u testuser -ppassword

or

>     mysql -u testuser -p --password=password

to log-in as testuser immediately.

If at some point you want to know if you are logged as root or testuser, simply enter

>     \s;

### Creating Our First Database

Now, let us create our first schema, our first table, populate it with data, and display various information.

We first create the schema (or database) `HW_FirstTest`:

>     CREATE DATABASE HW_FirstTest; -- Or CREATE SCHEMA  HW_FirstTest;

Let us make sure that we created it:

>     SHOW DATABASES;

Let us use it:

>     USE HW_FirstTest;

And see what it contains now:

>     SHOW TABLES;

We now create a table called `TableTest`, with two integer attributes called `Attribute1` and `Attribute2`:

>     CREATE TABLE TableTest (Attribute1 INT, Attribute2  INT);

And can make sure that the table was indeed created:

>     SHOW TABLES;

We can further ask our DBMS to display the structure of the table we just created:

>     DESCRIBE TableTest; -- Can be abbreviated as DESC  TableTest;

And even ask to get back the code that would create the exact same structure (but without the data!):

>     SHOW CREATE TABLE TableTest;

Now, let us populate it with some data:

>     INSERT INTO TableTest
>       VALUES (1,2),
>              (3,4),
>              (5,6);

Note that the SQL syntax and your DBMS are completely fine with your statement spreading over multiple lines. Let us now display the data stored in the table:

>     SELECT * FROM TableTest;

After that last command, you should see

>     +------------+------------+
>     | Attribute1 | Attribute2 |
>     +------------+------------+
>     |          1 |          2 |
>     |          3 |          4 |
>     |          5 |          6 |
>     +------------+------------+

Finally, we can erase the content of the table, then erase (“drop”) the table, and finally the schema:

>     DELETE FROM TableTest; -- Delete the rows
>     DROP TABLE TableTest; -- Delete the table
>     DROP DATABASE HW_FirstTest; -- Delete the schema

You’re all set! All you have to do is to quit, using the command

>     EXIT;

### Security Concerns

Note that we were quite careless when we set-up our installation:

- We installed a software without checking its signature. MySQL has a short tutorial on how to check the signature of their packages.
- We did not impose any requirement on the root password of our installation. Using a good, secure, and unique password, should have been required / advised.
- We left all the options on default, whereas a good, secure, installation, always fine-tune what is enabled and what is not.
- We chosed a very weak password for `testuser` that is common to all of our installation.

All of those are obvious security risks, and make this installation unsafe to be a production environment. We will only use it as a testing / learning environment, but it is strongly recommended to:

- Install it on a virtual machine, so that your personal files would not be impacted by any mis-use of your DBMS,
- Perform a fresh, secured installation if you want to use a DBMS for anything but testing / learning purposes.

## Exercises

Exercise 3.1

For each of the following, fill in the blanks:

- In SQL, a relation is called a (blank).
- In SQL, every statement ends with (blank), and in-line comments start with a (blank).
- In SQL, there is no string datatype, so we have to use (blank).
- The Data Control Language of SQL’s role is to (blank).

Exercise 3.2

What does it mean to say that SQL is at the same time a “data definition language” and a “data manipulation language”?

Exercise 3.3

Name three kind of objects (for lack of a better word) a `CREATE` statement can create.

Exercise 3.4

Write a `SQL` statement that adds a primary key constraint to an attribute named `ID` in an already existing table named `STAFF`.

Exercise 3.5

Complete each row of the following table with either a datatype or two different examples:

Data type | Examples
--- | ---
| `4`, `-32`
Char(4) |
VarChar(10) | `'Train'`, `'Michelle'`
Bit(4) |
| `TRUE`, `UNKNOWN`

Exercise 3.6

In the datatype `CHAR(3)`, what does the 3 indicate?

Exercise 3.7

Explain this query: `CREATE SCHEMA FACULTY;`.

Exercise 3.8

Write code to

- declare a first table with two attributes, one of which is the primary key,
- declare a second table with two attributes, one of which is the primary key, and the other references the primary key of the first table,
- insert one tuple in the first table,
- insert one tuple in the second table, referencing the only tuple of the first table,

You are free to come up with an example (even very simple or cryptic) or to re-use an example from class.

Exercise 3.9

Explain this query:

>     ALTER TABLE TABLEA
>       DROP INDEX Attribute1;

Exercise 3.10

If I want to enter January 21, 2016, as a value for an attribute with the `DATE` datatype, what value should I enter?

Exercise 3.11

Write a statement that inserts the values `"Thomas"` and `4` into the table `TRAINS`.

Exercise 3.12

If `PkgName` is the primary key in the table `MYTABLE`, what can you tell about the number of rows returned by the following statement?

>     SELECT * FROM MYTABLE WHERE PkgName = 'MySQL';

Exercise 3.13

If you want that every time a referenced row is deleted, all the refering rows are deleted as well, what mechanism should you use?

Exercise 3.14

By default, does the foreign key restrict, cascade, or set null on update? Can you justify this choice?

Exercise 3.15

If a database designer is using the `ON UPDATE SET NULL` for a foreign key, what mechanism is (s)he implementing (i.e., describe how the database will react a certain operation)?

Exercise 3.16

If the following is part of the design of a table:

>     FOREIGN KEY (DptNumber) REFERENCES DEPARTMENT(Number)
>       ON DELETE SET DEFAULT
>       ON UPDATE CASCADE;

What happen to the rows whose foreign key `DptNumber` are set to `3` if the row in the `DEPARTMENT` table with primary key Number set to `3` is…

- … deleted?
- … updated to `5`?

Exercise 3.17

If the following is part of the design of a `WORKER` table:

>     FOREIGN KEY  WORKER(DptNumber) REFERENCES DEPARTMENT(DptNumber)
>       ON UPDATE CASCADE;

What happen to the rows whose foreign key `DptNumber` are set to `3` if the row in the `DEPARTMENT` table with primary key `Number` set to `3` is…

1. … deleted?
2. … updated to `5`?

Exercise 3.18

Given a relation `TOURIST(Name, EntryDate, Address)`, write a `SQL` statement printing the name and address of all the tourists who entered the territory after the 15 September, 2012.

Exercise 3.19

Describe what the star do in the statement

>     SELECT ALL * FROM MYTABLE;

Exercise 3.20

What is the fully qualified name of an attribute? Give an example.

Exercise 3.21

If `DEPARTMENT` is a database, what is `DEPARTMENT.*`?

Exercise 3.22

What is a multi-set? What does it mean to say that MySQL treats tables as multisets?

Exercise 3.23

What is the difference between

>     SELECT ALL * FROM MYTABLE;

and

>     SELECT DISTINCT * FROM  MYTABLE;

How are the results the same? How are they different?

Exercise 3.24

What is wrong with the statement

>     SELECT * WHERE Name = 'CS' FROM  DEPARTMENT;

Exercise 3.25

Write a query that returns the number of row (i.e., of entries, of tuples) in a table named `BOOK`.

Exercise 3.26

When is it useful to use a select-project-join query?

Exercise 3.27

When is a tuple variable useful?

Exercise 3.28

Write a query that changes the name of the professor whose `Login` is `'caubert'` to `'Hugo Pernot'` in the table `PROF`.

Exercise 3.29

Can an `UPDATE` statement have a `WHERE` condition using an attribute that is not the primary key? If no, justify, if yes, tell what could happen.

Exercise 3.30

Give the three possible meaning of the `NULL` value, and an example for each of them.

Exercise 3.31

What are the values of the following expressions (i.e., do they evaluate to `TRUE`, `FALSE`, or `UNKNOWN`)?

- `TRUE AND FALSE`
- `TRUE AND UNKNOWN`
- `NOT UNKNOWN`
- `FALSE OR UNKNOWN`

Exercise 3.32

Write the truth table for `AND` for the three-valued logic of `SQL`.

Exercise 3.33

What comparison expression should you use to test if a value is different from `NULL`?

Exercise 3.34

Explain this query:

>     SELECT Login
>       FROM PROF
>       WHERE Department IN ( SELECT Major
>                             FROM STUDENT
>                             WHERE Login = 'jrakesh');

Can you rewrite it without nesting queries?

Exercise 3.35

What is wrong with this query?

>     SELECT Name FROM STUDENT
>       WHERE Login IN
>       ( SELECT Code FROM Department WHERE head =  'aturing');

Exercise 3.36

Write a query that returns the sum of all the values stored in the `Pages` attribute of a `BOOK` table.

Exercise 3.37

Write a query that adds a `Pages` attribute of type `INT` into a (already existing) `BOOK` table.

Exercise 3.38

Write a query that removes the default value for a `Pages` attribute in a `BOOK` table.

Exercise 3.39

Under which conditions does `SQL` allow you to enter the same row in a table twice?

## Solution to Exercises

Solution 3.1

The blanks can be filled as follow:

- In `SQL`, a relation is called a table.
- In `SQL`, every statement ends with a semi-colon (;), and in-line comments start with a two minus signs (--).
- In `SQL`, there is no `string` datatype, so we have to use VARCHAR(x) or CHAR(x) where x is an integer reflecting the maximum (or fixed) size of the string.
- The Data Control Language of `SQL`’s role is to control access to the data stored, by creating users and granting them rights.

Solution 3.2

It can specify the conceptual and internal schema, and it can manipulate the data.

Solution 3.3

Database (schema), table, view, assertion, trigger, etc.

Solution 3.4

>     ALTER TABLE STAFF ADD PRIMARY KEY(ID);

Solution 3.5

Data type | Examples
--- | ---
`INT` | `4`, `-32`
`CHAR(4)` | `'abCD'`, `"dEfG"`
`VARCHAR(10)` | `'Train'`, `'Michelle'`
`BIT(4)` | `B'1010'`, `B'0101'`

`BOOL``` |TRUE,FALSE,NULL`

`NULL` is actually a valid answer for every single type of

Solution 3.6

That we can store exactly three characters.

Solution 3.7

It creates a schema, i.e., a database, named `Faculty`.

Solution 3.8

A simple and compact code could be:

>     /* code/sql/HW_Short.sql */
>     CREATE TABLE A (
>       Att1 INT PRIMARY KEY,
>       Att2 INT
>     );

>     CREATE TABLE B (
>       Att3 INT PRIMARY KEY,
>       Att4 INT,
>       FOREIGN KEY (Att4) REFERENCES A (Att1)
>     );

>     INSERT INTO A
>     VALUES (
>       1,
>       2);

>     INSERT INTO B
>     VALUES (
>       3,
>       1);

Solution 3.9

It removes the `UNIQUE` constraint on the `Attribute1` in the `TABLEA` table.

Solution 3.10

>     DATE'2016-01-21', '2016-01-21', '2016/01/21', '20160121'

Solution 3.11

>     INSERT INTO TRAINS VALUES('Thomas', 4);

Solution 3.12

We know that at most one (but possibly 0) row will be returned.

Solution 3.13

We should use a referential triggered action clause, `ON DELETE CASCADE`.

Solution 3.14

By default, the foreign key restricts updates. This prevents unwanted update of information: if an update needs to be propagated, then it needs to be “acknowledged” and done explicitely.

Solution 3.15

If the referenced row is updated, then the attribute of the referencing rows are set to `NULL`.

Solution 3.16

In the referencing rows,

1. the department number is set to the default value.
2. the department number is updated accordingly.

Solution 3.17

1. This operation is rejected: the row in the `DEPARTMENT` table with primary key `Number` set to `3` cannot be deleted if a row in the `WORKER` table references it.
2. In the referencing rows, the department number is updated accordingly.

Solution 3.18

We could use the following:

>     SELECT Name, Address
>       FROM TOURIST
>       WHERE EntryDate > DATE'2012-09-15';

Solution 3.19

It selects all the attributes, it is a wildcard.

Solution 3.20

The name of the relation with the name of its schema and a period beforehand. An example would be `EMPLOYEE.Name`.

Solution 3.21

All the tables in that database.

Solution 3.22

A multiset is a set where the same value can occur twice. In MySQL, the same row can occur twice in a table.

Solution 3.23

They both select all the rows in the `MYTABLE` table, but `ALL` will print the duplicate values, whereas `DISTINCT` will print them only once.

Solution 3.24

You cannot have the `WHERE` before `FROM`.

Solution 3.25

>     SELECT COUNT(*) FROM BOOK;

Solution 3.26

We use those query that projects on attributes using a selection and join conditions when we need to construct for information based on pieces of data spread in multiple tables.

Solution 3.27

It makes the distinction between two different rows of the same table, it is useful when we want to select a tuple in a relation that is in a particular relation with a tuple in the same relation. Quoting Stack Overflow:

> They are useful for saving typing, but there are other reasons to use them:
> - If you join a table to itself you must give it two different names otherwise referencing the table would be ambiguous.
> - It can be useful to give names to derived tables, and in some database systems it is required… even if you never refer to the name.

Solution 3.28

We could use the following:

>     UPDATE PROF SET Name = 'Hugo  Pernot'
>       WHERE Login = 'caubert';

Solution 3.29

Yes, we can have select condition that does not use primary key. In that case, it could be the case that we update more than one tuple with such a command (which is not necessarily a bad thing).

Solution 3.30

Unknown value (“Will it rain tomorrow?”), unavailable / withheld (“What is the phone number of Harry Belafonte?”), N/A (“What is the email address of Abraham Lincoln?”).

Solution 3.31

- `TRUE AND FALSE → FALSE`
- `TRUE AND UNKNOWN → UNKNOWN`
- `NOT UNKNOWN → UNKNOWN`
- `FALSE OR UNKNOWN → FALSE`

Solution 3.32

- `TRUE AND TRUE → TRUE`
- `TRUE AND FALSE → FALSE`
- `TRUE AND UNKNOWN → UNKNOWN`
- `FALSE AND FALSE → FALSE`
- `UNKNOWN AND UNKNOWN → UNKNOWN`
- `FALSE AND UNKNOWN → FALSE`
- The other cases can be deduced by symmetry.

For a more compact presentation, refer to the three-valued truth table.

Solution 3.33

`IS NOT`

Solution 3.34

It list the login of the professors teaching in the department where the student whose login is “jrakesh” is majoring. It can be rewritten as

>     SELECT PROF.Login
>       FROM PROF, STUDENT
>       WHERE Department = Major
>       AND STUDENT.Login = 'jrakesh';

Solution 3.35

It tries to find a `Login` in a `Code`.

Solution 3.36

>     SELECT SUM(Pages) FROM BOOK;

Solution 3.37

>     ALTER TABLE BOOK ADD COLUMN Pages INT;

Solution 3.38

>     ALTER TABLE BOOK ALTER COLUMN Pages DROP DEFAULT;

Solution 3.39

Essentially, if there are no primary key in the relation, and if no attribute has the `UNIQUE` constraint.

## Problems

Problem 3.1 (Discovering the documentation)

The goal of this problem is to learn where to find the documentation for your DBMS, and to understand how to read the syntax of `SQL` commands.

You can consult (Elmasri and Navathe 2010, Table 5.2, p. 140) or (Elmasri and Navathe 2015, Table 7.2, p. 235), for a very quick summary of the most common commands. Make sure you are familiar with the Backus–Naur form (BNF) notation commonly used:

- non-terminal symbols (i.e., variables, parameters) are enclosed in angled brackets,

  >     <…>

- optional parts are shown in square brackets,

  >     […]

- repetitons are shown in braces

  >     {…}

- alternatives are shown in parenthesis and separated by vertical bars,

  >     (…|…|…)

The most complete lists of commands are probably at

- MariaDB and
- MySQL

Those are the commands implemented in the DBMS you are actually using. Since there are small variations from one implementation to the other, it is better to take one of this link as a reference in the future.

As a starting point, looking at the syntax for `CREATE TABLE` commands is probably a good start, cf. MariaDB or MySQL.

Problem 3.2 (Create and use a simple table in `SQL`)

This problem will guide you in manipulating a very simple table in `SQL`.

Pb 3.2 – Question 1

Log in as `testuser`, create a database named `HW_Address`, use it, and create two tables:

>     CREATE TABLE NAME(
>         FName VARCHAR(15),
>         LName VARCHAR(15),
>         ID INT,
>         PRIMARY KEY(ID)
>     );

>     CREATE TABLE ADDRESS(
>         StreetName VARCHAR(15),
>         Number INT,
>         Habitants INT,
>         PRIMARY KEY(StreetName, Number)
>     );

Pb 3.2 – Question 2

Observe the output produced by the command `DESC ADDRESS;`.

Pb 3.2 – Question 3

Add a foreign key to the `ADDRESS` table, using

>     ALTER TABLE ADDRESS
>         ADD FOREIGN KEY (Habitants)
>         REFERENCES NAME(ID);

And observe the new output produced by the command DESC ADDRESS;.

Is it what you would have expected? How informative is it? Can you think of a command that would output more detailled information, including a reference to the existence of the foreign key?

Pb 3.2 – Question 4

Draw the relational model corresponding to that database and identify the primary and foreign keys.

Pb 3.2 – Question 5

Add this data to the `NAME` table:

>     INSERT INTO NAME VALUES ('Barbara', 'Liskov', 003);
>     INSERT INTO NAME VALUES ('Tuong Lu', 'Kim', 004);
>     INSERT INTO NAME VALUES ('Samantha', NULL, 080);

What command can you use to display this infomation back? Do you notice anything regarding the values we entered for the `ID` attribute?

Pb 3.2 – Question 6

Add some data into the ADDRESS table:

>     INSERT INTO ADDRESS
>       VALUES
>       ('Armstrong Drive', 10019, 003),
>       ('North Broad St.', 23, 004),
>       ('Robert Lane', 120, NULL);

What difference do you notice with the insertions we made in the `NAME` table? Which syntax seems more easy to you?

Pb 3.2 – Question 7

Write a `SELECT` statement that returns the `ID` number of the person whose first name is “Samantha”.

Pb 3.2 – Question 8

Write a statement that violates the entity integrity constraint. What is the error message returned?

Pb 3.2 – Question 9

Execute an `UPDATE` statement that violates the referential integrity constraint. What is the error message returned?

Pb 3.2 – Question 10

Write a statement that violates another kind of constraint. Explain what constraint you are violating and explain the error message.

Problem 3.3 (Duplicate rows in `SQL`)

Log in as `testuser` and create a database titled `HW_REPETITION`. Create in that database a table (the following questions refer to this table as `EXAMPLE`, but you are free to name it whatever you want) with at least two attributes that have different data types. Do not declare a primary key yet. Answer the following:

Pb 3.3 – Question 1

Add a tuple to your table using

>     INSERT INTO EXAMPLE  VALUES(X, Y);

where the `X` and `Y` are values have the right datatype. Try to add this tuple again. What do you observe? (You can use `SELECT * FROM EXAMPLE;` to observe what is stored in this table.)

Pb 3.3 – Question 2

Alter your table to add a primary key, using

>     ALTER TABLE EXAMPLE ADD PRIMARY KEY (Attribute);

where `Attribute` is the name of the attribute you want to be a primary key. What do you observe?

Pb 3.3 – Question 3

Empty your table using

>     DELETE FROM EXAMPLE;

and alter your table to add a primary key, using the command we gave at the previous step. What do you observe?

Pb 3.3 – Question 4

Try to add the same tuple twice. What do you observe?

Problem 3.4 (Constraints on foreign keys)

From the notes, recall the following about foreign keys:

> Two important remarks:
> - The datatype of the foreign key has to be the exactly the same as the datatype of the attribute to which we are referring.
> - The target of the foreign key must be the primary key.

But, the situation is slightly more complex. Test for yourself by editing the following code as indicated:

>     /* code/sql/HW_FKtest.sql */
>     DROP SCHEMA IF EXISTS HW_FKtest;

>     CREATE SCHEMA HW_FKtest;

>     USE HW_FKtest;

>     CREATE TABLE TARGET (
>       Test VARCHAR(15) PRIMARY KEY
>     );

>     CREATE TABLE SOURCE (
>       Test VARCHAR(25),
>       FOREIGN KEY (Test) REFERENCES TARGET (Test)
>     );

- Remove the `PRIMARY KEY` constraint.
- Replace `PRIMARY KEY` with `UNIQUEM`.
- Replace one of the `VARCHAR(25)` with `CHAR(25)`.
- Replace one of the `VARCHAR(25)` with `INT`.
- Replace one of the `VARCHAR(25)` with `VARCHAR(15)`
- Once you have edited and run the program in all of its modified versions, adjust the remarks above to better reflect the reality of the implementation we are using.

Problem 3.5 (Revisiting the PROF table)

Create the `PROF`, `DEPARTMENT`, `STUDENT` and `GRADE` tables as in the “Constructing and populating a new example” section. Populate them with some data (copy it from the notes or come up with your own data).

To obtain exactly the same schema as the one we developed and edited, you can use mysqldump to “dump” this table, with a command like

>     mysqldump -u testuser  -ppassword\
>       -h localhost --add-drop-database\
>       --skip-comments --compact\
>       HW_ProfExample > dump.sql

The code we studied during the lecture is more or less the following.

>     /* code/sql/HW_ProfExampleRevisitedRevisited.sql */
>     DROP SCHEMA IF EXISTS HW_ProfExampleRevisited;

>     CREATE SCHEMA HW_ProfExampleRevisited;

>     USE HW_ProfExampleRevisited;

>     CREATE TABLE PROF (
>       Login VARCHAR(25) PRIMARY KEY,
>       NAME VARCHAR(25),
>       Department CHAR(5)
>     );

>     CREATE TABLE DEPARTMENT (
>       Code CHAR(5) PRIMARY KEY,
>       NAME VARCHAR(25),
>       Head VARCHAR(25),
>       FOREIGN KEY (Head) REFERENCES PROF (LOGIN) ON UPDATE CASCADE
>     );

>     ALTER TABLE PROF
>       ADD FOREIGN KEY (Department) REFERENCES DEPARTMENT (Code);

>     CREATE TABLE STUDENT (
>       Login VARCHAR(25) PRIMARY KEY,
>       NAME VARCHAR(25),
>       Registered DATE,
>       Major CHAR(5),
>       FOREIGN KEY (Major) REFERENCES DEPARTMENT (Code)
>     );

>     CREATE TABLE GRADE (
>       Login VARCHAR(25),
>       Grade INT,
>       PRIMARY KEY (LOGIN, Grade),
>       FOREIGN KEY (LOGIN) REFERENCES STUDENT (LOGIN)
>     );

>     INSERT INTO DEPARTMENT
>     VALUES (
>       'MATH',
>       'Mathematics',
>       NULL),
>     (
>       'CS',
>       'Computer
>         Science',
>       NULL);

>     INSERT INTO DEPARTMENT (
>       Code,
>       Name)
>     VALUES (
>       'CYBR',
>       'Cyber Secturity');

>     INSERT INTO PROF (
>       LOGIN,
>       Department,
>       Name)
>     VALUES (
>       'caubert',
>       'CS',
>       'Clément Aubert');

>     INSERT INTO PROF (
>       LOGIN,
>       Name,
>       Department)
>     VALUES (
>       'aturing',
>       'Alan Turing',
>       'CS'),
>     (
>       'perdos',
>       'Paul
>         Erdős',
>       'MATH'),
>     (
>       'bgates',
>       'Bill Gates',
>       'CYBR');

>     INSERT INTO STUDENT (
>       LOGIN,
>       Name,
>       Registered,
>       Major)
>     VALUES (
>       'jrakesh',
>       'Jalal Rakesh',
>       DATE '2017-12-01',
>       'CS'),
>     (
>       'svlatka',
>       'Sacnite Vlatka',
>       '2015-03-12',
>       'MATH'),
>     (
>       'cjoella',
>       'Candice Joella',
>       '20120212',
>       'CYBR'),
>     (
>       'aalyx',
>       'Ava Alyx',
>       20121011,
>       'CYBR'),
>     (
>       'caubert',
>       'Clément Aubert',
>       NULL,
>       'CYBR');

>     INSERT INTO GRADE
>     VALUES (
>       'jrakesh',
>       3.8),
>     (
>       'svlatka',
>       2.5);

We will resume working on this model, and enhance it.

Pb 3.5 – Question 1

Draw the complete relational model for this database (i.e., for the `PROF`, `DEPARTMENT`, `STUDENT` and `GRADE` relations).

Pb 3.5 – Question 2

Create and populate a `LECTURE` table as follows:

- It should have four attributes: `Name`, `Instructor`, `Code`, and `Year`, of types `VARCHAR(25)` for the first two, `CHAR(5)` for `Code`, and `YEAR(4)` for `Year`.
- The `Year` and `Code` attributes should be the primary key (yes, have two attributes be the primary key).
- The `Instructor` attribute should be a foreign key referencing the `Login` attribute in `PROF`.
- Populate the `LECTURE` table with some made-up data.

Try to think about some of the weakenesses of this representation. For instance, can it accomodate two instructors for the same class? Write down two possibles scenarios in which this schema would not be appropriate.

Pb 3.5 – Question 3

The `GRADE` table had some limitations too. For example, every student could have only one grade. Add two columns to the `GRADE` table using:

>     ALTER TABLE GRADE
>       ADD COLUMN LectureCode CHAR(5),
>       ADD COLUMN LectureYear YEAR(4);

Add a foreign key:

>     ALTER TABLE GRADE
>       ADD FOREIGN KEY (LectureYear, LectureCode)
>       REFERENCES LECTURE(Year, Code);

Use `DESCRIBE` and `SELECT` to observe the schema of the `GRADE` table and its rows. Is it what you would have expected?

Pb 3.5 – Question 4

Update the tuples in `GRADE` with some made-up data. Now every row should contain, in addition to a login and a grade, a lecture year and a lecture code.

Pb 3.5 – Question 5

Update the relational model you previously drew to reflect the new situation of your tables.

Pb 3.5 – Question 6

Write `SELECT` statements answering the following questions (where `PROF.Name`, `LECTURE.Name`, `YYYY`, `LECTURE.Code` and `STUDENT.Login` should be relevant values considering your data):

- “Could you give me the logins and grades of the students who took `LECTURE.Name` in `YYYY`?”
- “Could you list the instructors who taught in year `YYYY` without any duplicates?”
- “Could you list the name and grade of all the student who ever took the class `LECTURE.Code`?”
- “Could you tell me which years was the class `LECTURE.Code` taught?”
- “Could you list the other classes taught the same year as the class `LECTURE.Code`?”
- “Could you print the names of the students who registered after `STUDENT.Login`?”
- “Could you tell me how many departments’ heads are teaching this year?”

Problem 3.6 (TRAIN table and more advanced `SQL` coding)

Look at the SQL code below and then answer the following questions.

>     /* code/sql/HW_Train.sql */
>     CREATE TABLE TRAIN (
>       ID VARCHAR(30),
>       Model VARCHAR(30),
>       ConstructionYear YEAR (4)
>     );

>     CREATE TABLE CONDUCTOR (
>       ID VARCHAR(20),
>       NAME VARCHAR(20),
>       ExperienceLevel VARCHAR(20)
>     );

>     CREATE TABLE ASSIGNED_TO (
>       TrainId VARCHAR(20),
>       ConductorId VARCHAR(20),
>       Day DATE,
>       PRIMARY KEY (TrainId, ConductorId)
>     );

Pb 3.6 – Question 1

Modify the `CREATE` statement that creates the `TRAIN` table (lines 1–5), so that `ID` would be declared as the primary key. It is sufficient to only write the line(s) that need to change.

Pb 3.6 – Question 2

Write an `ALTER` statement that makes `ID` become the primary key of the `CONDUCTOR` table.

Pb 3.6 – Question 3

Modify the `CREATE` statement that creates the `ASSIGNED_TO` table (lines 13–18), so that it has two foreign keys: `ConductorId` references the `ID` attribute in `CONDUCTOR` and `TrainId` references the `ID` attribute in `TRAIN`. It is sufficient to only write the line(s) that need to change.

Pb 3.6 – Question 4

Write `INSERT` statements that insert one tuple of your choosing in each relation (no `NULL` values). These statements should respect all the constraints (including the ones we added in the previous questions) and result in actual insertions. (Remember that four digits is a valid value for an attribute with the `YEAR(4)` datatype.)

Pb 3.6 – Question 5

Write a statement that sets the value of the `ExperienceLevel` attribute to “Senior” in all the tuples where the `ID` attribute is “GP1029” in the `CONDUCTOR` relation.

Pb 3.6 – Question 6

Write a `SELECT` statement that answers each of the following questions:

- “What are the identification numbers of the trains?”
- “What are the names of the conductors with a”Senior" experience level?"
- “What are the construction years of the”Surfliner" and “Regina” models that we have?"
- “What is the ID of the conductor that was responsible of the train referenced”K-13" on 2015/12/14?"
- “What are the models that were ever conducted by the conductor whose ID is”GP1029“?”

Problem 3.7 (Read, correct, and write `SQL` statements for the COFFEE database)

Suppose we have the relational model depicted below, with the indicated data in it:

COFFEE

Ref | Origin | TypeOfRoast | PricePerPound
--- | --- | --- | ---
001 | Brazil | Light | 8.90
121 | Bolivia | Dark | 7.50
311 | Brazil | Medium | 9.00
221 | Sumatra | Dark | 10.25

CUSTOMER

CardNo | Name | Email
--- | --- | ---
001 | Bob Hill | b.hill@isp.net
002 | Ana Swamp | swampa@nca.edu
003 | Mary Sea | brig@gsu.gov
004 | Pat Mount | pmount@fai.fr

SUPPLY

Provider | Coffee
--- | ---
Coffee Unl. | 001
Coffee Unl. | 121
Coffee Exp. | 311
Johns & Co. | 221

PROVIDER

Name | Email
--- | ---
Coffee Unl. | bob@cofunl.com
Coffee Exp. | pat@coffeex.dk
Johns & Co. | NULL

In the following, we will assume that this model was implemented in a DBMS (MySQL or MariaDB), the primary keys being `COFFEE.Ref`, `CUSTOMER.CardNo`, `SUPPLY.Provider` and `SUPPLY.Coffee`, and `PROVIDER.Name`, and the foreign keys being as follows:

FavCoffee in the CUSTOMER relation | refers to | Ref in the COFFEE relation
--- | --- | ---
Provider in the SUPPLY | refers to | Name in the PROVIDER relation
Coffee in the SUPPLY | refers to | Ref in the COFFEE relation

Read and write `SQL` commands for the following “what-if” scenarios. Assume that:

1. Datatype do not matter: we use only strings and appropriate numerical datatypes.
2. Every statement respects `SQL`’s syntax (there’s no “a semi-colon is missing” trap).
3. None of these commands are actually executed; the data is always in the state depicted above.

You can use COFFEE.1 to denote the first tuple (or row) in COFFEE, and similarly for other relations and tuples (so that, for instance, SUPPLY.4 corresponds to `"Johns & Co".`, `221`).

Pb 3.7 – Question 1

Draw the relational model of this table.

Pb 3.7 – Question 2

Determine if the following insertion statements would violate the the entity integrity constraint, (“primary key cannot be `NULL` and should be unique”), the referential integrity constraint (“the foreign key must refer to something that exists”), if there would be some other kind of error (ignoring the plausability / revelance of inserting that tuple), or if it would result in successful insertion.

>     INSERT INTO CUSTOMER VALUES(005, 'Bob Hill', NULL, 001);
>     INSERT INTO COFFEE VALUES(002, "Peru", "Decaf", 3.00);
>     INSERT INTO PROVIDER VALUES(NULL, "contact@localcof.com");
>     INSERT INTO SUPPLY VALUES("Johns  Co.", 121);
>     INSERT INTO SUPPLY VALUES("Coffee Unl.", 311, 221);

Pb 3.7 – Question 3

Assuming that the referential triggered action clause `ON UPDATE CASCADE` is used for each of the foreign keys in this database, list the tuples modified by the following statements:

>     UPDATE CUSTOMER SET FavCoffee = 001
>       WHERE CardNo = 001;

>     UPDATE COFFEE SET TypeOfRoast = 'Decaf'
>       WHERE Origin = 'Brazil';

>     UPDATE PROVIDER SET Name = 'Coffee Unlimited'
>       WHERE Name = 'Coffee Unl.';

>     UPDATE COFFEE SET PricePerPound = 10.00
>       WHERE PricePerPound > 10.00;

Pb 3.7 – Question 4

Assuming that the referential triggered action clause `ON DELETE CASCADE` is used for each of the foreign keys in this database, list the tuples modified by the following statements:

>     DELETE FROM CUSTOMER
>       WHERE Name LIKE '%S%';

>     DELETE FROM COFFEE
>       WHERE Ref = 001;

>     DELETE FROM SUPPLY
>       WHERE Provider = 'Coffee Unl.'
>         AND Coffee = 001;

>     DELETE FROM PROVIDER
>       WHERE Name = 'Johns & Co.';

Pb 3.7 – Question 5

Assume that there is more data in our table than what was given at the beginning of the problem. Write `SQL` queries that answer the following questions:

1. “What are the origins of your dark coffees?”
2. “What is the reference of Bob’s favorite coffee?” (note: it does not matter if you return the favorite coffee of all the Bobs in the database.)
3. “What are the names of the providers who did not give their email?”
4. “How many coffees does Johns & co. provide us with?”
5. “What are the names of the providers of my dark coffees?”

Problem 3.8 (Write select queries for the DEPARTMENT table)

Consider the following `SQL` code:

>     /* code/sql/HW_Department.sql */
>     CREATE TABLE DEPARTMENT (
>       ID INT PRIMARY KEY,
>       NAME VARCHAR(30)
>     );

>     CREATE TABLE EMPLOYEE (
>       ID INT PRIMARY KEY,
>       NAME VARCHAR(30),
>       Hired DATE,
>       Department INT,
>       FOREIGN KEY (Department) REFERENCES DEPARTMENT (ID)
>     );

>     INSERT INTO DEPARTMENT
>     VALUES (
>       1,
>       "Storage"),
>     (
>       2,
>       "Hardware");

>     INSERT INTO EMPLOYEE
>     VALUES (
>       1,
>       "Bob",
>       20100101,
>       1),
>     (
>       2,
>       "Samantha",
>       20150101,
>       1),
>     (
>       3,
>       "Mark",
>       20050101,
>       2),
>     (
>       4,
>       "Karen",
>       NULL,
>       1),
>     (
>       5,
>       "Jocelyn",
>       20100101,
>       1);

Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.

1. The name of the employees working in the `Storage` department (`"Bob"`, `"Samantha"`, `"Karen"` and `"Jocelyn"`),
2. The name of the employee that has been hired for the longest period of time (`"Mark"`),
3. The name(s) of the employee(s) from the Storage department who has(have) been hired for the longest period of time. Phrased differently, the oldest employees of the Storage department (`"Bob"` and `"Jocelyn"`).

Problem 3.9 (Write select queries for the COMPUTER table)

Consider the following `SQL` code:

>     /* code/sql/HW_Computer.sql */
>     DROP SCHEMA IF EXISTS HW_Computer;

>     CREATE SCHEMA HW_Computer;

>     USE HW_Computer;

>     CREATE TABLE COMPUTER (
>       ID VARCHAR(20) PRIMARY KEY,
>       Model VARCHAR(40)
>     );

>     CREATE TABLE PRINTER (
>       ID VARCHAR(20) PRIMARY KEY,
>       Model VARCHAR(40)
>     );

>     CREATE TABLE CONNEXION (
>       Computer VARCHAR(20),
>       Printer VARCHAR(20),
>       PRIMARY KEY (Computer, Printer),
>       FOREIGN KEY (Computer) REFERENCES COMPUTER (ID),
>       FOREIGN KEY (Printer) REFERENCES PRINTER (ID)
>     );

>     INSERT INTO COMPUTER
>     VALUES (
>       'A',
>       'DELL A'),
>     (
>       'B',
>       'HP X'),
>     (
>       'C',
>       'ZEPTO D'),
>     (
>       'D',
>       'MAC Y');

>     INSERT INTO PRINTER
>     VALUES (
>       '12',
>       'HP-140'),
>     (
>       '13',
>       'HP-139'),
>     (
>       '14',
>       'HP-140'),
>     (
>       '15',
>       'HP-139');

>     INSERT INTO CONNEXION
>     VALUES (
>       'A',
>       '12'),
>     (
>       'A',
>       '13'),
>     (
>       'B',
>       '13'),
>     (
>       'C',
>       '14');

Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.

1. The number of computers connected to the printer whose `ID` is `'13'` (`2`).
2. The number of different models of printers (`2`).
3. The model(s) of the printer(s) connected to the computer whose `ID` is `'A'` (`'HP-140'` and `'HP-139'`).
4. The ID(’s) of the computer(s) not connected to any printer (`'D'`).

Problem 3.10 (Write select queries for the SocialMedia schema)

Consider the following `SQL` code:

>     /* code/sql/HW_SocialMedia.sql */
>     CREATE TABLE ACCOUNT (
>       ID INT PRIMARY KEY,
>       NAME VARCHAR(25),
>       Email VARCHAR(25) UNIQUE
>     );

>     CREATE TABLE SUBSCRIBE (
>       Subscriber INT,
>       Subscribed INT,
>       DATE DATE,
>       FOREIGN KEY (Subscriber) REFERENCES ACCOUNT (ID),
>       FOREIGN KEY (Subscribed) REFERENCES ACCOUNT (ID),
>       PRIMARY KEY (Subscriber, Subscribed)
>     );

>     CREATE TABLE VIDEO (
>       ID INT PRIMARY KEY,
>       Title VARCHAR(25),
>       Released DATE,
>       Publisher INT,
>       FOREIGN KEY (Publisher) REFERENCES ACCOUNT (ID)
>     );

>     CREATE TABLE THUMBS_UP (
>       Account INT,
>       Video INT,
>       DATE DATE,
>       PRIMARY KEY (Account, Video),
>       FOREIGN KEY (Account) REFERENCES ACCOUNT (ID),
>       FOREIGN KEY (Video) REFERENCES VIDEO (ID)
>     );

>     INSERT INTO ACCOUNT
>     VALUES (
>       1,
>       "Bob Ross",
>       "bob@ross.com"),
>     (
>       2,
>       NULL,
>       "anon@fai.com"),
>     (
>       3,
>       "Martha",
>       NULL);

>     INSERT INTO SUBSCRIBE
>     VALUES (
>       2,
>       1,
>       DATE "2020-01-01"),
>     (
>       3,
>       1,
>       DATE "2019-03-03"),
>     (
>       3,
>       2,
>       DATE "2019-03-03"),
>     (
>       2,
>       2,
>       DATE "2019-03-03"),
>     (
>       1,
>       2,
>       DATE "2019-03-03");

>     --  The first entry means that 2 subscribed to 1, not the
>     --    other way around.
>     --    And similarly for the other entries.
>     INSERT INTO VIDEO
>     VALUES (
>       10,
>       "My first video!",
>       DATE "2020-02-02",
>       1),
>     (
>       20,
>       "My second video!",
>       DATE "2020-02-03",
>       1),
>     (
>       30,
>       "My vacations",
>       DATE "2020-02-04",
>       2);

>     INSERT INTO THUMBS_UP
>     VALUES (
>       2,
>       10,
>       DATE "2020-02-02"),
>     (
>       3,
>       10,
>       DATE "2020-02-02"),
>     (
>       2,
>       20,
>       DATE "2020-02-02"),
>     (
>       1,
>       30,
>       DATE "2020-02-05");

Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.

1. The title of all the videos (`"My first video!"`, `"My second video!"`, `"My vacations"`).
2. The release date of the video whose title is `"My first video!"` (`"2020-02-02"`).
3. The ID of the account(s) where the “Name” attribute was not given (`"2"`).
4. The ID of the videos whose title contains the word `"video"` (`"10"`, `"20"`).
5. The number of thumbs up for the video with title `"My vacations"` (`"1"`).
6. The title of the oldest video (`"My first video!"`).
7. The names of the accounts who gave a thumbs up to the video with ID 30 (`"Bob Ross"`).
8. The ID of the account with the greatest number of subscribers (`"2"`).

Problem 3.11 (Write select queries for a variation of the COMPUTER table)

Consider the following `SQL` code:

>     CREATE TABLE COMPUTER (
>       ID VARCHAR(20) PRIMARY KEY,
>       Model VARCHAR(40)
>     );

>     CREATE TABLE PERIPHERAL (
>       ID VARCHAR(20) PRIMARY KEY,
>       Model VARCHAR(40),
>       TYPE ENUM ('mouse', 'keyboard', 'screen', 'printer')
>     );

>     CREATE TABLE CONNEXION (
>       Computer VARCHAR(20),
>       Peripheral VARCHAR(20),
>       PRIMARY KEY (Computer, Peripheral),
>       FOREIGN KEY (Computer) REFERENCES COMPUTER (ID),
>       FOREIGN KEY (Peripheral) REFERENCES PERIPHERAL (ID)
>     );

>     INSERT INTO COMPUTER
>     VALUES (
>       'A',
>       'Apple IIc Plus'),
>     (
>       'B',
>       'Commodore SX-64');

>     INSERT INTO PERIPHERAL
>     VALUES (
>       '12',
>       'Trendcom Model',
>       'printer'),
>     (
>       '14',
>       'TP-10
>         Thermal Matrix',
>       'printer'),
>     (
>       '15',
>       'IBM Selectric',
>       'keyboard');

>     INSERT INTO CONNEXION
>     VALUES (
>       'A',
>       '12'),
>     (
>       'B',
>       '14'),
>     (
>       'A',
>       '15');

Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.

- The model of the computer whose `ID` is `'A'` (`'Apple IIc Plus'`).
- The type of the peripheral whose `ID` is `'14'` (`printer`).
- The model of the printers (Trendcom Model, TP-10 Thermal Matrix).
- The model of the peripherals whose `NAME` starts with `'IBM'` (`'IBM Selectric'`).
- The model of the peripherals connected to the computer whose `ID` is `'A'` (`Trendcom Model`, `IBM Selectric`).
- The number of peripheral connected to the computer whose model is Apple IIc Plus (`2`).

Problem 3.12 (Improving a role-playing game with a relational model)

A friend of yours wants you to review and improve the code for a role-playing game.

The original idea was that each character has a name, a class (e.g., Bard, Assassin, Druid), a certain amount of experience, a level, one or more weapons (providing bonuses) and the ability to complete quests. A quest has a name and rewards the characters who completed it with a certain amount of experience and, on rare occaisions, with a special item.

Your friend came up with the following code:

>     CREATE TABLE CHARACTER(
>       Name VARCHAR(30) PRIMARY KEY,
>       Class VARCHAR(30),
>       XP INT,
>       LVL INT,
>       Weapon_Name VARCHAR(30),
>       Weapon_Bonus INT,
>       Quest_Completed VARCHAR(30)
>     );

>     CREATE TABLE QUEST(
>       ID VARCHAR(20) PRIMARY KEY,
>       Completed_By VARCHAR(30),
>       XP_Gained INT,
>       Special_Item VARCHAR(20),
>       FOREIGN KEY (Completed_By) REFERENCES CHARACTER(Name)
>     );

>     ALTER TABLE CHARACTER
>       ADD FOREIGN KEY (Quest_Completed) REFERENCES QUEST(ID);

However, there are several problems with the code:

- A character can have only one weapon. (All the attempts to “hack” the `CHARACTER` table to add an arbitrary number of weapons ended up creating horrible messes.)
- Every time a character completes a quest, a copy of the quest must also be created. (Your friend is not so sure why, but nothing else works. Also it seems that a character can complete only one quest, but your friend is not sure about that either.)
- It would be nice to be able to store features that are tied to the class, not the character, like the bonuses they provide and their associated elements (e.g., all bards use fire, all assassins use wind, etc.), but your friend simply cannot figure out how to make that happen.

Can you provide a relational model (there is no need to write the `SQL` code, but do remember to indicate the primary and foreign keys) that would solve all of your friend’s troubles?

Problem 3.13 (A simple database for books)

Consider the following code:

>     /* code/sql/HW_SimpleBook.sql */
>     DROP SCHEMA IF EXISTS HW_SimpleBook;

>     CREATE SCHEMA HW_SimpleBook;

>     USE HW_SimpleBook;

>     CREATE TABLE AUTHOR (
>       FName VARCHAR(30),
>       LName VARCHAR(30),
>       Id INT PRIMARY KEY
>     );

>     CREATE TABLE PUBLISHER (
>       NAME VARCHAR(30),
>       City VARCHAR(30),
>       PRIMARY KEY (NAME, City)
>     );

>     CREATE TABLE BOOK (
>       Title VARCHAR(30),
>       Pages INT,
>       Published DATE,
>       PublisherName VARCHAR(30),
>       PublisherCity VARCHAR(30),
>       FOREIGN KEY (PublisherName, PublisherCity) REFERENCES
>         PUBLISHER (NAME, City),
>       Author INT,
>       FOREIGN KEY (Author) REFERENCES AUTHOR (Id),
>       PRIMARY KEY (Title, Published)
>     );

>     INSERT INTO AUTHOR
>     VALUES (
>       "Virginia",
>       "Wolve",
>       01),
>     (
>       "Paul",
>       "Bryant",
>       02),
>     (
>       "Samantha",
>       "Carey",
>       03);

>     INSERT INTO PUBLISHER
>     VALUES (
>       "Gallimard",
>       "Paris"),
>     (
>       "Gallimard",
>       "New-York"),
>     (
>       "Jobs Pub.",
>       "New-York");

>     INSERT INTO BOOK
>     VALUES (
>       "What to eat",
>       213,
>       DATE '20170219',
>       "Gallimard",
>       "Paris",
>       01),
>     (
>       "Where to live",
>       120,
>       DATE '20130212',
>       "Gallimard",
>       "New-York",
>       02),
>     (
>       "My Life, I",
>       100,
>       DATE '18790220',
>       "Gallimard",
>       "Paris",
>       03),
>     (
>       "My Life, II",
>       100,
>       DATE '18790219',
>       "Jobs Pub.",
>       "New-York",
>       03);

The values inserted in the database is just to provide some examples; you should assume there is more data in it than what we have inserted. In this long problem, you will be asked to write commands to select, update, delete, insert data, and to improve upon the relational model.

Pb 3.13 – Question 1

Write a command that selects:

- The `Title` of all the books.
- The distinct `Name` of the publishers.
- The `Titles` and `Published` dates of the books published since January 31, 2012.
- The first and last names of the authors published by `"Gallimard"` (from any city).
- The first and last names of the authors who were not published by an editor in `"New-York"`.
- The `ID` of the authors who published a book whose name starts with `"Where"`.
- The total number of pages in the database.
- The number of pages in the longest book written by the author whose last name is `"Wolve"`.
- The titles of the books published in the 19th century.

Pb 3.13 – Question 2

Write a command that updates the title of all the books written by the author whose `ID` is `3` to `"BANNED"`. Is there any reason for this command to be rejected by the system? If yes, explain the reason.

Pb 3.13 – Question 3

Write one or multiple commands that would delete the author whose `ID` is `3` and all the books written by that author. Make sure you do not violate any foreign key constraints.

Pb 3.13 – Question 4

Write a command that would create a table used to record the awards granted to authors for particular books. Assume that each award has its own name, is awarded every year, and that it is awarded to an author for a particular book. Pick appropriate attributes, datatypes, primary and foreign keys, and, as always, avoid redundancy.

> You can use the `DATE` datatype to store a year.

Pb 3.13 – Question 5

Draw the relational model of the database you created (including all the relations given in the code and the ones you added).

Pb 3.13 – Question 6

Discuss two limitations of the model and how to improve it.

Problem 3.14 (A database for website certificates)

A certificate for a website has a serial number (`SN`) and a common name (`CN`). It must belong to an organization and be signed by a certificate authority (`CA`). The organization and `CA` must both have an `SN` and a `CN`. A `CS` can be trusted, not trusted, or not evaluated. The code below is an attempt to represent this situation and is populated with examples.

>     CREATE TABLE ORGANIZATION(
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30)
>     );

>     CREATE TABLE CA(
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30),
>       Trusted BOOL
>     );

>     CREATE TABLE CERTIFICATE(
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30),
>       Org VARCHAR(30) NOT NULL,
>       Issuer VARCHAR(30) NOT NULL,
>       Valid_Since DATE,
>       Valid_Until DATE,
>       FOREIGN KEY (Org)
>         REFERENCES ORGANIZATION(SN)
>         ON DELETE CASCADE,
>       FOREIGN KEY (Issuer) REFERENCES CA(SN)
>     );

>     INSERT INTO ORGANIZATION VALUES
>       ('01', 'Wikimedia Foundation'),
>       ('02', 'Free Software Foundation');

>     INSERT INTO CA VALUES
>       ('A', "Let's Encrypt", true),
>       ('B', 'Shady Corp.', false),
>       ('C', 'NewComer Ltd.', NULL);

>     INSERT INTO CERTIFICATE VALUES
>       ('a', '*.wikimedia.org', '01', 'A',
>         20180101, 20200101),
>       ('b', '*.fsf.org', '02', 'A',
>         20180101, 20191010),
>       ('c', '*.shadytest.org', '02', 'B',
>         20190101, 20200101),
>       ('d', '*.wikipedia.org', '01', 'C',
>         20200101, 20220101);

1. Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.
   1. The `CN`’s of all certificates (`"*.wikimedia.org, \*.fsf.org, \*.shadytest.org, \*.wikipedia.org"`).
   2. The `SN`’s of the organizations whose `CN` contains `"Foundation"` (`"01, 02"`).
   3. The `CN`’s and expiration dates of all the certificates that expired, assuming today is the 6th of December 2019 (`"\*.fsf.org"`, 2019-10-10).
   4. The `CN`’s of the `CA`’s that are not trusted (`"Shady Corp., NewComer Ltd."`),
   5. The CN’s of the certificates that are signed by a CA that is not trusted ("\*.shadytest.org, \*.wikipedia.org").
   6. The number of certificates signed by the `CA` whose `CN` is `"Let's encrypt"` (`2`).
   7. A table listing the `CN`’s of the organizations along with the `CN`’s of their certificates (`"Wikimedia Foundation, \*.wikimedia.org, Free Software Foundation, \*.fsf.org, Free Software Foundation, \*.shadytest.org, Wikimedia Foundation, \*.wikipedia.org"`).
2. In this set-up, what happens if the following commands are issued? List all the entries that are modified or deleted, or specify if the command would not change anything and explain why.
   1. `DELETE FROM` CA `WHERE` SN = `'A'`;
   2. `UPDATE` ORGANIZATION `SET` CN = `"FSF"` `WHERE` SN = `'02'`;
   3. `UPDATE` ORGANIZATION `SET` SN = `"01"` `WHERE` SN = `'02'`;
   4. `DELETE FROM` ORGANIZATION;

Problem 3.15 (A simple database for published pieces of work)

Consider the following code:

>     /* code/sql/HW_Work.sql */
>     CREATE TABLE AUTHOR (
>       NAME VARCHAR(30) PRIMARY KEY,
>       Email VARCHAR(30)
>     );

>     CREATE TABLE WORK (
>       Title VARCHAR(30) PRIMARY KEY,
>       Author VARCHAR(30),
>       FOREIGN KEY (Author) REFERENCES AUTHOR (NAME) ON DELETE
>         CASCADE ON UPDATE CASCADE
>     );

>     CREATE TABLE BOOK (
>       ISBN INT PRIMARY KEY,
>       Work VARCHAR(30),
>       Published DATE,
>       Price DECIMAL(10, 2),
>       FOREIGN KEY (WORK) REFERENCES WORK (Title) ON DELETE
>         RESTRICT ON UPDATE CASCADE
>     );

>     CREATE TABLE EBOOK (
>       ISBN INT PRIMARY KEY,
>       Work VARCHAR(30),
>       Published DATE,
>       Price DECIMAL(10, 2),
>       FOREIGN KEY (WORK) REFERENCES WORK (Title) ON DELETE
>         RESTRICT ON UPDATE CASCADE
>     );

>     INSERT INTO AUTHOR
>     VALUES (
>       "Virginia W.",
>       "vw@isp.net"), -- A.1
>     (
>       "Paul B.", "pb@isp.net"), -- A.2
>     (
>       "Samantha T.", "st@fai.fr") -- A.3
>     ;

>     INSERT INTO WORK
>     VALUES (
>       "What to eat",
>       "Virginia W.") -- W.1
>     ;

>     INSERT INTO BOOK
>     VALUES (
>       15155627,
>       "What to eat",
>       DATE '20170219',
>       12.89) -- B.1
>     ;

>     INSERT INTO EBOOK
>     VALUES (
>       15155628,
>       "What to eat",
>       DATE '20170215',
>       9.89) -- E.1

Assume the following:

1. Every statement respects `SQL`’s syntax (there’s no “a semi-colon is missing” trap).
2. None of the commands in the rest of this problem are actually executed; they are for hypothetical “what if” questions.

Also, note that each row inserted between line 39 and 50 is given a name in comment (`"A.1, A.2, A.3, W.1"`, etc.).

Pb 3.15 – Question 1

Draw the relational model corresponding to this series of commands.

Pb 3.15 – Question 2

Determine if the following insertion statements would violate the the entity integrity constraint, the referential integrity constraint, if there would be some other kind of error, or if it would result in sunsuccessful insertion.

>     INSERT INTO EBOOK VALUES (0, NULL, 20180101, 0);
>     INSERT INTO AUTHOR VALUES("Mary B.", "mb@fai.fr", NULL);
>     INSERT INTO WORK VALUES("My Life", "Claude A.");
>     INSERT INTO BOOK VALUES(00000000, NULL, DATE'20001225', 90.9);
>     INSERT INTO AUTHOR VALUES("Virginia W.", "alt@isp.net");

Pb 3.15 – Question 3

List the rows (A.2, W.1, etc.) modified by the following statements. Be careful about the conditions on foreign keys!

>     UPDATE AUTHOR SET Email =  'Deprecated' WHERE Email LIKE '%isp.net';
>     UPDATE WORK SET Title =  "How to eat" WHERE Title = "What to eat";
>     DELETE FROM WORK;
>     DELETE FROM AUTHOR WHERE  Name = "Virginia W.";

Pb 3.15 – Question 4

Assume that there is more data than what we inserted. Write a command that selects:

- The prices of all the ebooks.
- The distinct names of the authors who have authored a piece of work.
- The names of the authors using fai.fr for their email domain.
- The prices of the ebooks published after 2018.
- The price of the most expensive book.
- The number of the pieces of work written by the author whose name is “Virginia W..”
- The email of the author who wrote the piece called “My Life.”
- The ISBN’s of the books containing a work written by the author whose email is “vw@isp.net.”

Pb 3.15 – Question 5

Write a command that updates the title of all the pieces of work written by the author whose name is “Virginia W.” to “BANNED.” Is there any reason for this command to be rejected by the system? If yes, explain the reason.

Pb 3.15 – Question 6

Write one or multiple commands that would delete the work whose title is “My Life”, as well as all of the book and ebook versions of it.

Pb 3.15 – Question 7

Discuss two limitations of the model and how to improve it.

Problem 3.16 (A simple database for authors of textbooks)

Consider the following code:

>     /* code/sql/HW_TextbookAuthored.sql */
>     DROP SCHEMA IF EXISTS HW_TEXTBOOKAUTHORED;

>     CREATE SCHEMA HW_TEXTBOOKAUTHORED;

>     USE HW_TEXTBOOKAUTHORED;

>     CREATE TABLE TEXTBOOK (
>       Title VARCHAR(50),
>       ISBN CHAR(13) PRIMARY KEY,
>       Price DECIMAL(10, 2)
>     );

>     CREATE TABLE AUTHOR (
>       LName VARCHAR(30),
>       FName VARCHAR(30),
>       Email VARCHAR(30),
>       PRIMARY KEY (Lname, Fname)
>     );

>     CREATE TABLE AUTHORED (
>       Book CHAR(13),
>       FOREIGN KEY (Book) REFERENCES TEXTBOOK (ISBN),
>       AuthorLName VARCHAR(30),
>       AuthorFName VARCHAR(30),
>       FOREIGN KEY (AuthorLName, AuthorFName) REFERENCES AUTHOR
>         (LName, Fname)
>     );

>     INSERT INTO TEXTBOOK
>     VALUES (
>       'Starting Out with Java: Early Objects',
>       9780133776744,
>       30.00),
>     (
>       'NoSQL for Mere Mortals',
>       9780134023212,
>       47.99);

>     INSERT INTO AUTHOR
>     VALUES (
>       'Sullivan',
>       'Dan',
>       NULL),
>     (
>       'Gaddis',
>       'Tony',
>       NULL);

>     INSERT INTO AUTHORED
>     VALUES (
>       9780134023212,
>       'Sullivan',
>       'Dan'),
>     (
>       9780133776744,
>       'Gaddis',
>       'Tony');

The meaning of the AUTHORED table is that a tuple <I, L, F> represents that the author whose last name is L and whose first name is F wrote the textbook whose ISBN is I.

Answer the following:

1. Write a command that updates the email address of ‘Gaddis’, ‘Tony.’
2. Write a command that inserts a textbook of your choice into the `TEXTBOOK` table. No value should be `NULL`.
3. Write a command that makes ‘Gaddis’, ‘Tony’ the author of the textbook you just added to our database.
4. Write a command that makes “0.01” the default value for the `Price` attribute of the `TEXTBOOK` relation.
5. Write a command that inserts a textbook of your choice in the `TEXTBOOK` table and have the price set to the default value.
6. Write a command that creates a table called `EDITOR` with three attributes: `Name`, `Address`, and `Website`. The `Name` attribute should be the primary key. Insert two tuples in the `EDITOR` table, making sure that one should has the `Name` attribute set to “Pearson.”
7. Write a command that creates a table called `PUBLISHED` with two attributes: `Editor` and `Textbook`. The `Editor` attribute should reference the `EDITOR` table and the `Textbook` attribute should reference the `TEXTBOOK` table.
8. Write a command that makes “Pearson” the editor of the textbook whose ISBN is 9780133776744.

Answer the following short questions based on what is in our model so far:

1. Can an author have authored more than one textbook?
2. Can a textbook have more than one author?
3. Can a textbook without an `ISBN` be inserted in the `TEXTBOOK` relation?
4. Can the price of a textbook be negative?
5. Can two authors have the same first and last names?
6. Can two textbooks have the same title?
7. Can two editors have the same address?

Problem 3.17 (A database for residencies)

Consider the following code:

>     /* code/sql/HW_Residency.sql */
>     DROP SCHEMA IF EXISTS HW_RESIDENCY;

>     CREATE SCHEMA HW_RESIDENCY;

>     USE HW_RESIDENCY;

>     CREATE TABLE PERSON (
>       FName VARCHAR(40),
>       LName VARCHAR(40),
>       SSN VARCHAR(11) PRIMARY KEY,
>       Birthdate DATE
>     );

>     CREATE TABLE HOUSE (
>       Address VARCHAR(40) PRIMARY KEY,
>       Color ENUM ("blue", "white", "green")
>     );

>     CREATE TABLE RESIDENCY (
>       Person VARCHAR(11),
>       House VARCHAR(40),
>       PrincipalResidence BOOLEAN,
>       Status ENUM ("own", "rent", "squat", "other"),
>       FOREIGN KEY (Person) REFERENCES PERSON (SSN),
>       FOREIGN KEY (House) REFERENCES HOUSE (Address) ON DELETE CASCADE
>     );

>     INSERT INTO PERSON
>     VALUES (
>       NULL,
>       "Doe",
>       "000-00-0000",
>       NULL), -- P.1
>     (
>       "Michael", "Keal", "000-00-0001", DATE "1983-02-11"), -- P.2
>     (
>       "James", "Baldwin", "000-00-0002", DATE
>         "1967-01-01"), -- P.3
>     (
>       "Mridula", "Warrier", "000-00-0003", DATE "1990-02-11");

>     -- P.4
>     INSERT INTO HOUSE
>     VALUES (
>       "123 Main St.",
>       "blue"), -- H.1
>     (
>       "456 Second St.", "white"), -- H.2
>     (
>       "11 Third St.", "blue");

>     -- H.3
>     INSERT INTO RESIDENCY
>     VALUES (
>       "000-00-0001",
>       "123 Main St.",
>       TRUE,
>       "own"), -- R.1
>     (
>       "000-00-0001", "456 Second St.", FALSE, "own"), -- R.2
>     (
>       "000-00-0002", "123 Main St.", TRUE, "rent"), -- R.3
>     (
>       "000-00-0003", "456 Second St.", TRUE, "own");

>     -- R.4

Note that each row inserted in the `PERSON`, `HOUSE` and `RESIDENCY` tables is given the name and noted as afterwards as a comment (`"P.1, P.2, P.3, P.4, H.1"`, etc.).

Answer the following questions and problems, assuming that none of the commands in the rest of the problem are actually executed.

Pb 3.17 – Question 1

Draw the relational model corresponding to this series of commands (it is not necessary to include the state).

Pb 3.17 – Question 2

Write a command that violates the entity integrity constraint.

Pb 3.17 – Question 3

Write a command that violates the referential integrity constraint.

Pb 3.17 – Question 4

List the rows (‘“P.2, H.1, or“none””’{sqlmysql}) modified by the following statements:

1. `UPDATE HOUSE SET COLOR = "green";`
2. `DELETE FROM RESIDENCY WHERE House LIKE "1%";`
3. `DELETE FROM HOUSE WHERE Address = "456 Second St.";`
4. `DELETE FROM PERSON WHERE Birthdate=DATE"1990-02-11";`

Pb 3.17 – Question 5

Write queries that return the following information. The values returned in this set-up will be in parenthesis, but keep the queries general.

1. The `Addresses` of the houses in the system (`“11 Third St., 123 Main St., 456 Second St.”`).
2. The `SSN`’s of the people whose first name was not entered in the system (`"000-00-0000"`).
3. All the different colors of houses (`"white, blue"`).
4. The Address of the residency of `"James Baldwin"` (`"123 Main St."`).
5. The first name of the oldest person in the database (`"James"`).
6. `"Michael Keal"’`s principal residency address (`"123 Main St."`).
7. The distinct first and last names of the homeowners (`"Michael Keal, Mridula Warrier"`).
8. The `SSN`’s of the people that have the same principal residency as `"James Baldwin"` (`"000-00-0001"`).

Pb 3.17 – Question 6

Write a command that updates the `SSN` of `"James Baldwin"` to `"000-00-0010"`. Is there any reason for this command to be rejected by the system? If yes, explain the reason.

Pb 3.17 – Question 7

Answer the following short questions from the data in our model, as it is currently:

1. Is it possible for two people to have the same last name?
2. Is it possible for a person to have multiple principal residencies?
3. Is it possible for a house to not be yellow?
4. Is it possible for the `SSN` to be any series of 11 characters?
5. Is it possible for a person to own any number of houses?
6. Is it possible for a person to rent at most one house?

Pb 3.17 – Question 8

Consider the data currently in the `RESIDENCY` table and give a possible primary key.

Pb 3.17 – Question 9

Discuss why the primary key identified from the previous question for the `RESIDENCY` table is a good choice.

## Solutions to Selected Problems

Solution to Problem 3.2 (Create and use a simple table in SQL)

This problem is supposed to be a straightforward application of what we studied in class. Look back at Setting Up Your Work Environment if you feel like you are stuck before referencing this solution.

Pb 3.2 – Solution to Q. 1

We simply log-in as indicated in the “Logging-in as testuser” section. Then we enter:

>     CREATE DATABASE HW_Address;
>       USE HW_Address;

This creates the tables asked for in the problem.

Pb 3.2 – Solution to Q. 2

Ommiting the `Extra` column, we have:

>     MariaDB [HW_Address]>     DESC ADDRESS;
>     +------------+-------------+------+-----+---------+
>     | Field      | Type        | Null | Key | Default |
>     +------------+-------------+------+-----+---------+
>     | StreetName | varchar(15) | NO   | PRI | NULL    |
>     | Number     | int(11)     | NO   | PRI | NULL    |
>     | Habitants  | int(11)     | YES  |     | NULL    |
>     +------------+-------------+------+-----+---------+

Pb 3.2 – Solution to Q. 3

We add the foreign key, still omitting the `Extra` column:

>     MariaDB [HW_Address]> DESC ADDRESS;
>     +------------+-------------+------+-----+---------+
>     | Field      | Type        | Null | Key | Default |
>     +------------+-------------+------+-----+---------+
>     | StreetName | varchar(15) | NO   | PRI | NULL    |
>     | Number     | int(11)     | NO   | PRI | NULL    |
>     | Habitants  | int(11)     | YES  | MUL | NULL    |
>     +------------+-------------+------+-----+---------+

The only difference is the `MUL` value, which is a bit surprising: quoting MySQL,

> If `Key` is `MUL`, then the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

In other words, this does not carry any information about the fact that `ADDRESS.Habitants` is now a foreign key referencing `NAME.ID`. A way of displaying information about that foreign key is using `SHOW CREATE TABLE`:

>     MariaDB [HW_Address]> SHOW CREATE TABLE ADDRESS;
>     +---------+----------------------+
>     | Table   | Create Table
>     +---------+----------------------+
>     | ADDRESS | CREATE TABLE `ADDRESS` (
>     `StreetName` varchar(15) NOT NULL,
>     `Number` int(11) NOT NULL,
>     `Habitants` int(11) DEFAULT NULL,
>     PRIMARY KEY (`StreetName`,`Number`),
>     KEY `Habitants` (`Habitants`),
>     CONSTRAINT `ADDRESS_ibfk_1` FOREIGN KEY (`Habitants`) REFERENCES `NAME` (`ID`)
>     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
>     +---------+----------------------+
>     1 row in set (0.01 sec)

Pb 3.2 – Solution to Q. 4

Pb 3.2 – Solution to Q. 5

To display the information back, we can use

>     SELECT * FROM NAME;

We should notice that the `ID` attribute values lost their leading zeros.

Pb 3.2 – Solution to Q. 6

This syntax is better for “bulk insertion” since it allows for us to write fewer commands and to focus on the data being inserted. However, if an error occurs, then nothing gets inserted.

Pb 3.2 – Solution to Q. 7

>     SELECT ID FROM NAME WHERE FName = 'Samantha';

Pb 3.2 – Solution to Q. 8

This is a command that violates the entity integrity constraint:

>     INSERT INTO NAME VALUES  ('Maria', 'Kashi', NULL);

The error message that it returns is:

>     ERROR 1048 (23000): Column  'ID' cannot be null

Another way of violating the entity integrity constraint is:

>     INSERT INTO NAME VALUES  ('Maria', 'Kashi', 80);

The error message that it returns is:

>     ERROR 1062 (23000):  Duplicate entry '80' for key 'PRIMARY'

Pb 3.2 – Solution to Q. 9

This is an `UPDATE` statement that violates the entity integrity constraint:

>     UPDATE ADDRESS SET Habitants  = 340 WHERE Number = 120;

The error message that it returns is:

>     ERROR 1452 (23000): Cannot  add or update a child row: a
>     foreign key constraint fails  (`HW_Address`.`ADDRESS`, CONSTRAINT
>     `ADDRESS_ibfk_1` FOREIGN KEY (`Habitants`)  REFERENCES `NAME` (`ID`))

Pb 3.2 – Solution to Q. 10

Here is the query that violates another type of constraint:

>     INSERT INTO NAME VALUE  ('Hi');

The error message that it returns is:

>     ERROR 1136 (21S01): Column  count does not match value count at row 1

The query statement violates the implicit constraint by trying to insert a row with fewer values than there are attributes in the table.

Another example of a statement that violates another type of constraint is:

>     INSERT INTO ADDRESS VALUES  ('Maria', 'Random', 98);

This is a violation of an explicit constraint, which is that the value must match the domain (datatype) of the attribute where it is inserted. However, MySQL and MariaDB do not return an error, they simply replace `'Random'` with `0`.

Solution to Problem 3.3 (Duplicate rows in SQL)

Here is how we created our table:

>     CREATE SCHEMA HW_REPETITION;
>     USE HW_REPETITION;

>     CREATE TABLE EXAMPLE(
>         X VARCHAR(15),
>         Y INT
>     );

Pb 3.3 – Solution to Q. 1

The command to add a tuple to our table is:

>     INSERT INTO EXAMPLE  VALUES('Train', 4);

If we execute this command twice, then SQL is OK with it, and inserts the same tuple twice:

>     SELECT * FROM EXAMPLE;

Displays:

>     +-------+------+
>     | X     | Y    |
>     +-------+------+
>     | Train |    4 |
>     | Train |    4 |
>     +-------+------+

This is an illustration of the fact that the data in a table in SQL is not a set, as opposed to a state in a relation in the relational model.

Pb 3.3 – Solution to Q. 2

The command:

>     ALTER TABLE EXAMPLE ADD  PRIMARY KEY (X);

Should return:

>     ERROR 1062 (23000):  Duplicate entry 'Train' for key 'PRIMARY'

We tried to declare that `X` was a primary key, but SQL disagreed, since two rows have the same value for that attribute.

Pb 3.3 – Solution to Q. 3

Once the table is empty, X now qualifies as a candidate key, and can now be made a primary key. SQL stops complaining and lets us assign it as a primary key.

Pb 3.3 – Solution to Q. 4

After trying this insertion statement twice:

>     INSERT INTO EXAMPLE  VALUES('Train', 4);

SQL refuses to insert the tuple after the second attempt:

>     ERROR 1062 (23000):  Duplicate entry 'Train' for key 'PRIMARY'

Notice that this is exactly the same error message as before, when we tried to add the primary key while we had a duplicate row of tuples!

Solution to Problem 3.4 (Constraints on foreign keys)

- Removing the `PRIMARY KEY` constraint, SQL throws the following error message:

  >     ERROR 1005 (HY000): Can't  create table `HW_FK_test`.`SOURCE`
  >     (errno: 150 "Foreign key constraint is  incorrectly formed")

- Replacing `PRIMARY KEY` with `UNIQUE` does not generate any error messages.
- Replacing one of the `VARCHAR`(`25`) with `CHAR`(`25`) does not generate any error messages.
- Replacing `VARCHAR`(`25`) with `INT` results in this error message:

  >     ERROR 1005 (HY000): Can't  create table `HW_FK_test`.`SOURCE`
  >     (errno: 150 "Foreign key constraint is  incorrectly formed")

- Replacing one of the `VARCHAR`(`25`) with `VARCHAR`(`15`) does not generate any error messages.
- The remarks become:

  > - The datatype of the foreign key has to be “compatible” with the datatype of the attribute to which we are referring.
  > - The target of the foreign key must be the primary key or have the `UNIQUE` constraint.

Solution to Problem 3.5 (Revisiting the PROF table)

Pb 3.5– Solution to Q. 1

Ignoring the LECTURE relation, we have:

Pb 3.5– Solution to Q. 2

The code is straightforward:

>     CREATE TABLE HW_Lecture (
>       NAME VARCHAR(25),
>       Instructor VARCHAR(25),
>       Year YEAR(4),
>       Code CHAR(5),
>       PRIMARY KEY (Year, Code),
>       FOREIGN KEY (Instructor) REFERENCES PROF (LOGIN)
>     );

>     INSERT INTO HW_Lecture
>     VALUES (
>       'Intro to CS',
>       'caubert',
>       2017,
>       '1304'),
>     (
>       'Intro
>         to Algebra',
>       'perdos',
>       2017,
>       '1405'),
>     (
>       'Intro to
>         Cyber',
>       'aturing',
>       2017,
>       '1234');

However, this representation can not handle the following situations:

- If multiple instructors teach the same class,
- If the lecture is taught more than once a year (either because it is taught in the Fall, Spring and Summer, or if multiple sections are offered at the same time),
- If a Lecture is cross-listed, then some duplication of information will be needed.

We come back to those short-coming in the “Reverse-Engineering” section, using more abstract tools (such as Entity Diagrams) that have not been introduced yet.

Pb 3.5– Solution to Q. 3

The statements are immediate:

>     DESCRIBE GRADE;
>     SELECT *
>     FROM GRADE;

What may be surprising is that the values for `LectureCode` and `LectureYear` are set to `NULL` in all the tuples.

Pb 3.5– Solution to Q. 4

We use `UPDATE` statements:

>     UPDATE
>       GRADE
>     SET LectureCode = '1304',
>       LectureYear = 2017
>     WHERE LOGIN = 'jrakesh'
>       AND Grade = '2.85';

>     UPDATE
>       GRADE
>     SET LectureCode = '1405',
>       LectureYear = 2017
>     WHERE LOGIN = 'svlatka'
>       OR (LOGIN = 'jrakesh'
>         AND Grade = '3.85');

>     UPDATE
>       GRADE
>     SET LectureCode = '1234',
>       LectureYear = 2017
>     WHERE LOGIN = 'aalyx'
>       OR LOGIN = 'cjoella';

Pb 3.5– Solution to Q. 5

We refer back to the solution to Q. 1.

Pb 3.5– Solution to Q. 6

We use `SELECT` statements:

>     SELECT LOGIN,
>       Grade
>     FROM GRADE
>     WHERE Lecturecode = '1304'
>       AND LectureYear = '2017';

>     SELECT DISTINCT Instructor
>     FROM HW_Lecture
>     WHERE Year = 2017;

>     SELECT Name,
>       Grade
>     FROM STUDENT,
>       GRADE
>     WHERE GRADE.LectureCode = 1405
>       AND STUDENT.Login = GRADE.Login;

>     SELECT Year
>     FROM HW_Lecture
>     WHERE Code = '1234';

>     SELECT Name
>     FROM HW_Lecture
>     WHERE Year IN (
>         SELECT Year
>         FROM HW_Lecture
>         WHERE CODE = '1234');

>     SELECT B.name
>     FROM STUDENT AS A,
>       STUDENT AS B
>     WHERE A.Name = 'Ava Alyx'
>       AND A.Registered > B.Registered;

>     SELECT COUNT(DISTINCT PROF.Name) AS 'Head Teaching This Year'
>     FROM HW_Lecture,
>       DEPARTMENT,
>       PROF
>     WHERE Year = 2017
>       AND Instructor = Head
>       AND Head = PROF.Login;

Solution to Problem 3.6 (TRAIN table and more advanced SQL coding)

The code below includes the answers to all of the questions for this problem:

>     -- Question 1:
>     CREATE TABLE TRAIN (
>       Id VARCHAR(30) PRIMARY KEY, -- This line was changed.
>       Model VARCHAR(30),
>       ConstructionYear YEAR(4)
>     );

>     -- Question 2 :
>     CREATE TABLE CONDUCTOR (
>       Id VARCHAR(20),
>       NAME VARCHAR(20),
>       ExperienceLevel VARCHAR(20)
>     );

>     ALTER TABLE CONDUCTOR
>       ADD PRIMARY KEY (Id);

>     -- Question 3
>     CREATE TABLE ASSIGNED_TO (
>       TrainId VARCHAR(20),
>       ConductorId VARCHAR(20),
>       Day DATE,
>       PRIMARY KEY (TrainId, ConductorId),
>       FOREIGN KEY (TrainId) REFERENCES TRAIN (Id), -- This line was changed
>       FOREIGN KEY (ConductorId) REFERENCES CONDUCTOR (Id) -- This line was changed
>     );

>     -- Question 4:
>     /*
>      We insert more than one tuple, to make the SELECT statements that follow easier
>      to test and debug.
>      */
>     INSERT INTO TRAIN
>     VALUES (
>       'K-13',
>       'SurfLiner',
>       2019),
>     (
>       'K-12',
>       'Regina',
>       2015);

>     INSERT INTO CONDUCTOR
>     VALUES (
>       'GP1029',
>       'Bill',
>       'Junior'),
>     (
>       'GP1030',
>       'Sandrine',
>       'Junior');

>     INSERT INTO ASSIGNED_TO
>     VALUES (
>       'K-13',
>       'GP1029',
>       DATE '2015/12/14'),
>     (
>       'K-12',
>       'GP1030',
>       '20120909');

>     -- Question 5:
>     UPDATE
>       CONDUCTOR
>     SET ExperienceLevel = 'Senior'
>     WHERE Id = 'GP1029';

>     -- Question 6:
>     -- 1.
>     SELECT Id
>     FROM TRAIN;

>     -- 2.
>     SELECT Name
>     FROM CONDUCTOR
>     WHERE ExperienceLevel = 'Senior';

>     -- 3.
>     SELECT ConstructionYear
>     FROM TRAIN
>     WHERE Model = 'SurfLiner'
>       OR Model = 'Regina';

>     -- 4.
>     SELECT ConductorId
>     FROM ASSIGNED_TO
>     WHERE TrainId = 'K-13'
>       AND Day = '2015/12/14';

>     -- 5.
>     SELECT Model
>     FROM TRAIN,
>       ASSIGNED_TO
>     WHERE ConductorID = 'GP1029'
>       AND TrainId = TRAIN.ID;

Solution to Problem 3.7 (Read, correct, and write SQL statements for the COFFEE database)

Solution to Question 1:

The answers to the rest of the questions are in the following code:

>     /* code/sql/HW_DBCoffee.sql */
>     -- Question 2:
>     START TRANSACTION;

>     INSERT INTO CUSTOMER
>     VALUES (
>       005,
>       'Bob Hill',
>       NULL,
>       001);

>     INSERT INTO COFFEE
>     VALUES (
>       002,
>       "Peru",
>       "Decaf",
>       3.00);

>     -- The following statement raises an error.
>     --	 INSERT INTO PROVIDER
>     --	   VALUES (NULL, "contact@localcof.com");
>     --	 ERROR 1048 (23000) at line 68: Column 'Name'
>     -- cannot
>     --    be
>     --	 null
>     INSERT INTO SUPPLY
>     VALUES (
>       "Johns & Co.",
>       121);

>     -- The following statement raises an error.
>     --	 -INSERT INTO SUPPLY
>     --	    VALUES ("Coffee Unl.", 311, 221);
>     --	  ERROR 1136 (21S01): Column count doesn't match
>     --   value
>     --	 count at row 1
>     --	  Rest the changes:
>     ROLLBACK;

>     -- Question 3:
>     START TRANSACTION;

>     UPDATE
>       CUSTOMER
>     SET FavCoffee = 001
>     WHERE CardNo = 001;

>     -- Rows matched: 1  Changed: 1  Warnings: 0
>     SELECT *
>     FROM CUSTOMER;

>     ROLLBACK;

>     START TRANSACTION;

>     UPDATE
>       COFFEE
>     SET TypeOfRoast = 'Decaf'
>     WHERE Origin = 'Brazil';

>     -- Rows matched: 2  Changed: 2  Warnings: 0
>     SELECT *
>     FROM COFFEE;

>     ROLLBACK;

>     START TRANSACTION;

>     UPDATE
>       PROVIDER
>     SET Name = 'Coffee Unlimited'
>     WHERE Name = 'Coffee Unl.';

>     -- Rows matched: 1  Changed: 1  Warnings: 0
>     SELECT *
>     FROM PROVIDER;

>     SELECT *
>     FROM SUPPLY;

>     ROLLBACK;

>     START TRANSACTION;

>     UPDATE
>       COFFEE
>     SET PricePerPound = 10.00
>     WHERE PricePerPound > 10.00;

>     -- Rows matched: 1  Changed: 1  Warnings: 0
>     SELECT *
>     FROM COFFEE;

>     ROLLBACK;

>     -- Question 4:
>     START TRANSACTION;

>     DELETE FROM CUSTOMER
>     WHERE Name LIKE '%S%';

>     -- Query OK, 2 rows affected (0.01 sec)
>     SELECT *
>     FROM CUSTOMER;

>     ROLLBACK;

>     START TRANSACTION;

>     DELETE FROM COFFEE
>     WHERE Ref = 001;

>     -- Query OK, 1 row affected (0.00 sec)
>     SELECT *
>     FROM COFFEE;

>     SELECT *
>     FROM SUPPLY;

>     ROLLBACK;

>     START TRANSACTION;

>     DELETE FROM SUPPLY
>     WHERE Provider = 'Coffee Unl.'
>       AND Coffee = '001';

>     -- Query OK, 1 row affected (0.00 sec)
>     SELECT *
>     FROM SUPPLY;

>     ROLLBACK;

>     START TRANSACTION;

>     DELETE FROM PROVIDER
>     WHERE Name = 'Johns & Co.';

>     -- Query OK, 1 row affected (0.00 sec)
>     SELECT *
>     FROM PROVIDER;

>     SELECT *
>     FROM SUPPLY;

>     ROLLBACK;

>     -- Question 5:
>     -- 1.
>     SELECT Origin
>     FROM COFFEE
>     WHERE TypeOfRoast = 'Dark';

>     -- 2.
>     SELECT FavCoffee
>     FROM CUSTOMER
>     WHERE Name LIKE 'Bob%';

>     -- 3.
>     SELECT Name
>     FROM PROVIDER
>     WHERE Email IS NULL;

>     -- 4.
>     SELECT COUNT(*)
>     FROM SUPPLY
>     WHERE Provider = 'Johns & Co.';

>     -- 5.
>     SELECT Provider
>     FROM COFFEE,
>       SUPPLY
>     WHERE TypeOfRoast = 'Dark'
>       AND Coffee = Ref;

Solution to Problem 3.8 (Write select queries for the DEPARTMENT table)

1. >     SELECT EMPLOYEE.Name
   >     FROM EMPLOYEE,
   >       DEPARTMENT
   >     WHERE DEPARTMENT.Name = "Storage"
   >       AND EMPLOYEE.Department = DEPARTMENT.ID;

2. >     SELECT Name
   >     FROM EMPLOYEE
   >     WHERE Hired <= ALL (
   >         SELECT Hired
   >         FROM EMPLOYEE
   >         WHERE Hired IS NOT NULL);

3. >     SELECT EMPLOYEE.Name
   >     FROM EMPLOYEE,
   >       DEPARTMENT
   >     WHERE Hired <= ALL (
   >         SELECT Hired
   >         FROM EMPLOYEE
   >         WHERE Hired IS NOT NULL
   >           AND DEPARTMENT.Name = "Storage"
   >           AND EMPLOYEE.Department = DEPARTMENT.ID)
   >       AND DEPARTMENT.Name = "Storage"
   >       AND EMPLOYEE.Department = DEPARTMENT.ID;

Solution to Problem 3.11 (Write select queries for a variation of the COMPUTER table)

>     SELECT Model
>     FROM COMPUTER
>     WHERE ID = 'A';

>     SELECT TYPE
>     FROM PERIPHERAL
>     WHERE ID = '14';

>     SELECT Model
>     FROM PERIPHERAL
>     WHERE TYPE = 'printer';

>     SELECT Model
>     FROM PERIPHERAL
>     WHERE Model LIKE 'IBM%';

>     SELECT Model
>     FROM PERIPHERAL,
>       CONNEXION
>     WHERE Computer = 'A'
>       AND Peripheral = PERIPHERAL.ID;

>     SELECT COUNT(Computer)
>     FROM CONNEXION,
>       COMPUTER
>     WHERE Model = 'Apple IIc Plus'
>       AND Computer = COMPUTER.ID;

Solution to Problem 3.10 (Write select queries for the SocialMedia schema)

>     /* code/sql/HW_SocialMedia.sql */
>     -- … the title of all the videos ("My first video!", "My
>     --	 second video!", "My vacations").
>     SELECT TITLE
>     FROM VIDEO;

>     -- … the release date of the video whose title is "My first
>     --	 video!" ("2020-02-02").
>     SELECT Released
>     FROM VIDEO
>     WHERE Title = "My first video!";

>     -- … the ID of the account(s) where the "Name" attribute
>     --	 was not given ("2").
>     SELECT ID
>     FROM ACCOUNT
>     WHERE Name IS NULL;

>     -- … the ID of the videos whose title contains the word
>     --	 "video" ("10", "20").
>     SELECT ID
>     FROM VIDEO
>     WHERE TITLE LIKE "%video%";

>     -- or
>     SELECT ID
>     FROM VIDEO
>     WHERE Title REGEXP 'video';

>     -- … the number of thumbs up for the video with title "My
>     --	 vacations" ("1").
>     SELECT COUNT(*)
>     FROM THUMBS_UP,
>       VIDEO
>     WHERE VIDEO.Title = "My vacations"
>       AND VIDEO.ID = THUMBS_UP.Video;

>     -- … the title of the oldest video ("My first video!").
>     SELECT Title
>     FROM VIDEO
>     WHERE Released <= ALL (
>         SELECT Released
>         FROM VIDEO);

>     -- or
>     SELECT Title
>     FROM VIDEO
>     WHERE Released = (
>         SELECT Min(Released)
>         FROM VIDEO);

>     -- or even
>     SELECT Title
>     FROM VIDEO
>     ORDER BY Released ASC
>     LIMIT 1;

>     -- … the names of the accounts who gave a thumbs up to the
>     --	 video with id 30 ("Bob Ross").
>     SELECT Name
>     FROM ACCOUNT,
>       THUMBS_UP
>     WHERE THUMBS_UP.Video = 30
>       AND THUMBS_UP.Account = ACCOUNT.ID;

>     -- … the ID of the account with the greatest number of
>     --	 subscribers ("2").
>     SELECT Subscribed
>     FROM SUBSCRIBE
>     GROUP BY Subscribed
>     ORDER BY COUNT(Subscriber) DESC
>     LIMIT 1;

Solution to Problem 3.12 (Improving a role-playing game with a relational model)

The following solves all the issues with your friend’s code design. As quests only rarely provide a special item, we added a relation to avoid having a `Special-item` in the `QUEST` table since that would be `NULL` too often.

Solution to Problem 3.13 (A simple database for books)

Pb 3.13 – Solution to Q. 1

Here are possible ways of getting the required information:

1. The `Title` of all the books:

   >     SELECT Title FROM BOOK;

2. The distinct `Name` of the publishers.

   >     SELECT DISTINCT Name FROM  PUBLISHER;

3. The `Titles` and `Published` dates of the books published since January 31, 2012.

   >     SELECT Title, Published FROM  BOOK
   >       WHERE Published >  DATE'20120131';

4. The first and last names of the authors published by `"Gallimard"` (from any city).

   >     SELECT FName, LName FROM  AUTHOR, BOOK
   >       WHERE PublisherName =  "Gallimard"
   >       AND Author = ID;

5. The first and last names of the authors who were not published by an editor in `"New-York"`.

   >     SELECT FName, LName FROM  AUTHOR, BOOK
   >       WHERE NOT PublisherCity=  "New-York"
   >       AND Author = ID;

6. The `ID` of the authors who published a book whose name starts with `"Where"`.

   >     SELECT Author FROM BOOK
   >       WHERE Title LIKE 'Where%';

7. The total number of pages in the database.

   >     SELECT SUM(Pages) FROM BOOK;

8. The number of pages in the longest book written by the author whose last name is `"Wolve"`.

   >     SELECT MAX(PAGES) FROM BOOK,  AUTHOR
   >       WHERE LName =  "Wolve"
   >       AND Author = ID;

9. The title of the books published in the 19th century.

   >     SELECT Title FROM BOOK
   >       WHERE Published >=  DATE'18010101'
   >       AND Published <= DATE'19001231';

Pb 3.13 – Solution to Q. 2

We can use the following command:

>     UPDATE BOOK SET Title = "BANNED"
>     WHERE Author = 3;

The pair (title, publication date) is the primary key in the `BOOK` table, so if the author whose ID is 3 has published more than one book at a particular date, then our update will be rejected, as applying it would result in violating the entity integrity constraint.

Pb 3.13 – Solution to Q. 3

To delete the required rows, we can use:

>     DELETE FROM BOOK WHERE Author = 3;
>     DELETE FROM AUTHOR WHERE ID = 3;

Note that trying to delete the rows in the `AUTHOR` table before deleting the rows in the `BOOK` table could cause a referential integrity violation, since the `BOOK` table has a foreign key assigned to the `AUTHOR` table’s `Id` field.

Pb 3.13 – Solution to Q. 4

We could design that table as follows:

>     CREATE TABLE AWARD(
>       Name  VARCHAR(30),
>       Year DATE,
>       BookTitle  VARCHAR(30),
>       BookPubDate  DATE,
>       FOREIGN KEY  (BookTitle, BookPubDate)
>         REFERENCES BOOK(Title, Published),
>       PRIMARY KEY  (Name, Year)
>       );

Note that there is no need to store the name of the author in this relation: this information can be recovered by looking in the `BOOK` table for the name of the author of the awarded book.

Pb 3.13 – Solution to Q. 5

We obtain something as follows:

Note that having two attributes as the primary key makes the referencing of foreign keys more cumbersome.

Pb 3.13 – Solution to Q. 6

Two of the flaws that come to mind are:

1. The choice of the primary key for the `BOOK` relation: two books with the same title cannot be published on the same day, which is a serious limitation. Using a primary key like ISBN would be much more appropriate.
2. This design makes it impossibile to deal with books written by multiple authors or published by multiple publishers. We could address this by having two separate tables, `IS_THE_AUTHOR_OF` and `PUBLISHED_BY`, that “maps” the book’s ISBN with author’s or editor’s primary key.

Solution to Problem 3.14 (A database for website certificates)

The solution can be read from the following code:

>     /* code/sql/HW_Certificate.sql */
>     DROP SCHEMA IF EXISTS HW_Certificate;

>     CREATE SCHEMA HW_Certificate;

>     USE HW_Certificate;

>     /*
>     SN = Serial Number
>     CN = Common Name
>     CA = Certificate Authority
>      */
>     CREATE TABLE ORGANIZATION (
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30)
>     );

>     CREATE TABLE CA (
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30),
>       Trusted BOOL
>     );

>     CREATE TABLE CERTIFICATE (
>       SN VARCHAR(30) PRIMARY KEY,
>       CN VARCHAR(30) NOT NULL,
>       Org VARCHAR(30) NOT NULL,
>       Issuer VARCHAR(30),
>       Valid_Since DATE,
>       Valid_Until DATE,
>       FOREIGN KEY (Org) REFERENCES ORGANIZATION (SN) ON DELETE CASCADE,
>       FOREIGN KEY (Issuer) REFERENCES CA (SN)
>     );

>     INSERT INTO ORGANIZATION
>     VALUES (
>       '01',
>       'Wikimedia Foundation'),
>     (
>       '02',
>       'Free
>         Software Foundation');

>     INSERT INTO CA
>     VALUES (
>       'A',
>       "Let's Encrypt",
>       TRUE),
>     (
>       'B',
>       'Shady Corp.',
>       FALSE),
>     (
>       'C',
>       'NewComer Ltd.',
>       NULL);

>     INSERT INTO CERTIFICATE
>     VALUES (
>       'a',
>       '*.wikimedia.org',
>       '01',
>       'A',
>       20180101,
>       20200101),
>     (
>       'b',
>       '*.fsf.org',
>       '02',
>       'A',
>       20180101,
>       20191010),
>     (
>       'c',
>       '*.shadytest.org',
>       '02',
>       'B',
>       20190101,
>       20200101),
>     (
>       'd',
>       '*.wikipedia.org',
>       '01',
>       'C',
>       20200101,
>       20220101);

>     -- CN of all certificates.
>     SELECT CN
>     FROM CERTIFICATE;

>     -- (*.wikimedia.org | *.fsf.org | *.shadytest.org |
>     --	 *.wikipedia.org)
>     --	  The SN of the organizations whose CN contains
>     --	 "Foundation"
>     SELECT SN
>     FROM ORGANIZATION
>     WHERE CN LIKE "%Foundation%";

>     -- (01 | 02)
>     --	  The CN and expiration date of all the
>     -- certificates
>     --      that
>     --	 expired (assuming we are the 6th of December
>     -- 2019).
>     SELECT CN,
>       Valid_Until
>     FROM CERTIFICATE
>     WHERE Valid_Until < DATE '20191206';

>     -- (*.fsf.org,  2019-10-10)
>     --	  The CN of the CA that are not trusted.
>     SELECT CN
>     FROM CA
>     WHERE Trusted IS NOT TRUE;

>     -- (Shady Corp. |  NewComer Ltd.)
>     --	  The CN of the certificates that are signed by a
>     -- CA
>     --      that
>     --	 is not trusted.
>     SELECT CERTIFICATE.CN
>     FROM CERTIFICATE,
>       CA
>     WHERE Trusted IS NOT TRUE
>       AND CA.SN = CERTIFICATE.Issuer;

>     -- (Shady Corp. | NewComer Ltd.)
>     --	  The number of certificates signed by the CA whose
>     --   CN
>     --      is
>     --	 "Let's encrypt".
>     SELECT COUNT(CERTIFICATE.SN) AS "Number of certificates signed
>         by Let's encrypt"
>     FROM CERTIFICATE,
>       CA
>     WHERE CERTIFICATE.Issuer = CA.SN
>       AND CA.CN = "Let's encrypt";

>     -- (2)
>     --	  A table listing the CN of the organizations along
>     --     with
>     --	 the CN of their certificates.
>     SELECT ORGANIZATION.CN AS Organization,
>       CERTIFICATE.CN AS Certificate
>     FROM ORGANIZATION,
>       CERTIFICATE
>     WHERE CERTIFICATE.Org = ORGANIZATION.SN;

>     -- ( Wikimedia Foundation,  *.wikimedia.org | Free Software
>     --	 Foundation, *.fsf.org | Free Software Foundation ,
>     --	 *.shadytest.org | Wikimedia Foundation ,
>     --      *.wikipedia.org
>     --	)
>     /*
>     DELETE FROM CA WHERE SN = 'A';
>     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
>     constraint fails (`HW_Certificate`.`CERTIFICATE`, CONSTRAINT
>     `CERTIFICATE_ibfk_2` FOREIGN KEY (`Issuer`) REFERENCES `CA` (`SN`))

>     => Rejected, because an entry in CERTIFICATE references this tuple
>     (referential integrity constraint).

>     UPDATE ORGANIZATION SET CN = "FSF" WHERE SN = '02';
>     Query OK, 1 row affected (0.008 sec)
>     Rows matched: 1  Changed: 1  Warnings: 0

>     => Ok, change
>     ('02', 'Free Software Foundation');
>     into
>     ('02', 'FSF');
>     in ORGANIZATION

>     MariaDB [HW_Certificate]> UPDATE ORGANIZATION SET SN = "01" WHERE SN = '02';
>     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
>     constraint fails (`HW_Certificate`.`CERTIFICATE`, CONSTRAINT
>     `CERTIFICATE_ibfk_1` FOREIGN KEY (`Org`) REFERENCES `ORGANIZATION` (`SN`)
>     ON DELETE CASCADE)

>     => Rejected, because an entry in CERTIFICATE references this tuple (referential
>     integrity constraint).
>     This query would have been rejected even if this tuple was not referenced,
>     since it would have violated the entity integrity constraint.

>     DELETE FROM ORGANIZATION;

>     => Deletes all the content of organization and of certificate.
>      */

Solution to Problem 3.15 (A simple database for published pieces of work)

Pb 3.15 – Solution to Q. 1

The relational model for this code is:

Pb 3.15 – Solution to Q. 2

The solution to the next questions can be read from the following code:

>     /* code/sql/HW_Work.sql */
>     /*
>      Determine if the following insertion statements would violate the the Entity
>      integrity constraint, the Referential integrity constraint, if there would be
>      some Other kind of error, or if it would result in successful insertion.
>      */
>     START TRANSACTION;

>     -- We don't want to perform the actual insertions.
>     INSERT INTO EBOOK
>     VALUES (
>       0,
>       NULL,
>       20180101,
>       0);

>     /*
>      Query OK, 1 row affected (0.003 sec)
>      So, "Successful insertion".
>      */
>     -- The following statement raises an error.
>     --	 INSERT INTO AUTHOR
>     --	   VALUES ("Mary B.", "mb@fai.fr", NULL);
>     /*
>      ERROR 1136 (21S01): Column count doesn't match value count at row 1
>      So, "Other kind of error".
>      */
>     -- The following statement raises an error.
>     --	 INSERT INTO WORK
>     --	   VALUES ("My Life", "Claude A.");
>     /*
>      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
>      (`HW_EXAM_1`.`WORK`, CONSTRAINT `WORK_ibfk_1` FOREIGN KEY (`Author`) REFERENCES `AUTHOR` (`Name`)
>      ON DELETE CASCADE ON UPDATE CASCADE)
>      So, "Referential integrity constraint"
>      */
>     INSERT INTO BOOK
>     VALUES (
>       00000000,
>       NULL,
>       DATE '20001225',
>       90.9);

>     /*
>      Query OK, 1 row affected (0.000 sec)
>      So, "Successful insertion".
>      */
>     -- The following statement raises an error.
>     --	  INSERT INTO AUTHOR
>     --	   VALUES ("Virginia W.", "alt@isp.net");
>     /*
>      ERROR 1062 (23000): Duplicate entry 'Virginia W.' for key 'PRIMARY'
>      So, "Entity integrity constraint".
>      */
>     ROLLBACK;

>     -- We go back to the previous state.
>     /*
>      List the rows (i.e., A.2, W.1, etc.) modified by the following statements
>      (be careful about the conditions on foreign keys!):
>      */
>     START TRANSACTION;

>     -- We don't want to perform the following operations.
>     UPDATE
>       AUTHOR
>     SET Email = 'Deprecated'
>     WHERE Email LIKE '%isp.net';

>     /*
>      Query OK, 2 rows affected (0.010 sec)
>      Rows matched: 2  Changed: 2  Warnings: 0
>      This changed A.1 and A.2
>      */
>     UPDATE
>       WORK
>     SET Title = "How to eat"
>     WHERE Title = "What to eat";

>     /*
>      Rows matched: 1  Changed: 1  Warnings: 0
>      SQL returns only the number of row changed in the WORK table,
>      but other rows have been changed as well.
>      This changed W.1, B.1, E.1.
>      */
>     -- The following statement raises an error.
>     -- DELETE FROM WORK;
>     /*
>      ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
>      (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE)
>      Does not change any row.
>      */
>     -- The following statement raises an error.
>     --	  DELETE FROM AUTHOR
>     --	  WHERE Name = "Virginia W.";
>     /*
>      ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
>      (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE)
>      Does not change any row.
>      */
>     ROLLBACK;

>     -- We go back to the previous state.
>     --	  You can now assume that there is more data than
>     --   what
>     --      we
>     --	 inserted, if that helps you. Write a command that
>     --       selects
>     --	 …
>     --	  We insert some dummy values for this next part.
>     INSERT INTO WORK
>     VALUES (
>       "My Life",
>       "Paul B."),
>     (
>       "What to eat, 2",
>       "Virginia W.");

>     INSERT INTO BOOK
>     VALUES (
>       15355627,
>       "My Life",
>       DATE '20180219',
>       15.00),
>     (
>       12912912,
>       "What to eat, 2",
>       DATE '20200101',
>       13);

>     INSERT INTO EBOOK
>     VALUES (
>       15150628,
>       "My Life",
>       DATE '20190215',
>       10.89),
>     (
>       42912912,
>       "What to eat, 2",
>       DATE '20200115',
>       12);

>     -- … the price of all the ebooks.
>     SELECT Price
>     FROM EBOOK;

>     -- … the (distinct) names of the authors who have authored
>     --	 a piece of work.
>     SELECT DISTINCT Author
>     FROM WORK;

>     -- … the name of the authors using fai.fr for their email.
>     SELECT Name
>     FROM AUTHOR
>     WHERE Email LIKE '%fai.fr';

>     -- … the price of the ebooks published after 2018.
>     SELECT Price
>     FROM BOOK
>     WHERE Published >= 20180101;

>     /*
>      Note that
>      SELECT Price FROM BOOK WHERE Published > 2018;
>      would return all the prices, along with a warning:
>      Incorrect datetime value: '2018'
>      */
>     -- … the price of the most expensive book.
>     SELECT MAX(Price)
>     FROM BOOK;

>     -- … the number of pieces of work written by the author
>     --	 whose name is “Virginia W.”.
>     SELECT COUNT(*)
>     FROM WORK
>     WHERE WORK.Author = "Virginia W.";

>     -- … the email of the author who wrote the piece of work
>     --	 called “My Life”.
>     SELECT Email
>     FROM AUTHOR,
>       WORK
>     WHERE WORK.Title = "My Life"
>       AND WORK.Author = AUTHOR.Name;

>     -- the isbn(s) of the book containing a work written by the
>     --	 author whose email is "vw@isp.net".
>     SELECT ISBN
>     FROM BOOK,
>       WORK,
>       AUTHOR
>     WHERE AUTHOR.Email = "vw@isp.net"
>       AND WORK.Author = AUTHOR.Name
>       AND BOOK.Work = WORK.Title;

>     /*
>      Write a command that updates the title of all the pieces of work written by the author whose name is “Virginia W. to”BANNED".
>      Is there any reason for this command to be rejected by the system? If yes, explain which one.
>      */
>     -- The following statement raises an error.
>     /*
>     UPDATE
>      WORK
>     SET
>      Title = "BANNED"
>     WHERE
>      Author = "Virginia W.";
>      */
>     /*
>      Gives an error, since "Title" is the primary key in the WORK table, and Virginia W. has authored two pieces of work or more,
>      they are both given the title "BANNED", which violates the unicity of value in primary keys.
>      */
>     -- Write one or multiple commands that would delete the work
>     --	 whose title is “My Life”, as well as all of
>     -- the
>     --       books
>     --	 and ebooks versions of it.
>     --	  The following statement raises an error.
>     --	 DELETE FROM WORK
>     --	 WHERE Title = "My Life";
>     /*
>      Fails
>      ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
>      (`HW_EXAM_1`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`Work`) REFERENCES `WORK` (`Title`) ON UPDATE CASCADE)
>      */
>     -- We have to first delete the corresponding publications:
>     DELETE FROM BOOK
>     WHERE WORK = "My Life";

>     DELETE FROM EBOOK
>     WHERE WORK = "My Life";

>     -- And then we can delete the work:
>     DELETE FROM WORK
>     WHERE Title = "My Life";

>     /*
>      And, no, we cannot delete "simply" from multiple tables in one command.
>      Some workaround exists, cf. https://stackoverflow.com/q/1233451/ .
>      */

Pb 3.15 – Solution to Q. 3

Finally, to answer the last question, here is a list of the possible limitations:

1. Having the name or the title as a primary key (in the `AUTHOR` and `WORK` tables) is not a good idea: we cannot have two authors with the same name or two pieces of work with the same title!
2. If all the attributes in the `BOOK` and the `EBOOK` tables are going to be the same, then we should probably have only one table called `PUBLICATION` with a boolean to indicate whenever the publication is digital or on paper.
3. Having a mix of `ON DELETE CASCADE` and `ON DELETE RESTRICT` is not really justified and makes the tables harder to use. We should have used the same update policy on both tables.

Solution to Problem 3.16 (A simple database for authors of textbooks)

The answers can be found in the following snippet:

>     /* code/sql/HW_TEXTBOOK_AUTHORED_SOL.sql  */
>     /*
>      EXERCISE 1

>      Write a command that updates the email address of 'Gaddis', 'Tony' to "tgaddis@pearson.com"
>      */
>     UPDATE
>       AUTHOR
>     SET Email = "tgaddis@pearson.com"
>     WHERE LName = 'Gaddis'
>       AND FName = 'Tony';

>     /*
>      You can use
>      SELECT * FROM AUTHOR;
>      to check that the modification took place.
>      */
>     /*
>      EXERCISE 2

>      Write a command that inserts the textbook of your choice in the
>      TEXTBOOK table. No value should be NULL, but you can invent
>      the values.
>      */
>     INSERT INTO TEXTBOOK
>     VALUES (
>       'Fundamentals of Database Systems',
>       9780133970777,
>       165.89);

>     /*
>      You can use
>      SELECT * FROM TEXTBOOK;
>      to check that the insertion was correctly made.
>      */
>     /*
>      EXERCISE 3

>      Write a command that makes 'Gaddis', 'Tony' the author of the
>      textbook you just added to our database.
>      */
>     INSERT INTO AUTHORED
>     VALUES (
>       9780133970777,
>       'Gaddis',
>       'Tony');

>     /*
>      You can use
>      SELECT * FROM AUTHORED;
>      to check that the insertion was correctly made.

>      EXERCISE 4

>      Write a command that makes "0.01" becomes the
>      default value for the Price attribute of the
>      TEXTBOOK relation.
>      */
>     ALTER TABLE TEXTBOOK
>       ALTER COLUMN Price SET DEFAULT 0.01;

>     /*
>      You can use
>      DESCRIBE TEXTBOOK;
>      to check that the Price attribute now has a default
>      value.

>      EXERCISE 5

>      Write a command that insert a textbook of
>      your choice in the TEXTBOOK table, with the
>      price set to the default value.
>      */
>     INSERT INTO TEXTBOOK
>     VALUES (
>       'Proof Theory',
>       9780486490731,
>       DEFAULT);

>     /*
>      You can use
>      SELECT * FROM TEXTBOOK;
>      to check that the insertion was correctly made.

>      EXERCISE 6

>      Write a command that creates a table called EDITOR
>      with 3 attributes, "Name", "Address" and "Website".
>      The "Name" attribute should be the primary key.
>      Then, insert two tuples in the EDITOR table, one
>      should have the "Name" attribute set to "Pearson".
>      */
>     CREATE TABLE EDITOR (
>       NAME VARCHAR(30) PRIMARY KEY,
>       Address VARCHAR(255),
>       Website VARCHAR(100)
>     );

>     INSERT INTO EDITOR
>     VALUES (
>       'Pearson',
>       NULL,
>       'http://pearsoned.com/'),
>     (
>       'Dover',
>       NULL,
>       'https://store.doverpublications.com/');

>     /*
>      You can use
>      DESCRIBE EDITOR;
>      to check that the table was actually created, and
>      SELECT * FROM EDITOR;
>      to check that the values were inserted.

>      EXERCISE 7

>      Write a command that creates a table called PUBLISHED
>      with 2 attributes, "Editor", and "Textbook".
>      The "Editor" attribute should references the EDITOR
>      table, and the "Textbook" attribute should reference
>      the TEXTBOOK table.
>      */
>     CREATE TABLE PUBLISHED (
>       Editor VARCHAR(30),
>       FOREIGN KEY (Editor) REFERENCES EDITOR (NAME),
>       Textbook CHAR(13),
>       FOREIGN KEY (Textbook) REFERENCES TEXTBOOK (ISBN)
>     );

>     /*
>      You can use
>      DESCRIBE PUBLISHED;
>      to check that the table was actually created.

>      EXERCISE 8

>      Write a command that makes "Pearson" the editor of
>      the textbook whose ISBN is 9780133776744.
>      */
>     INSERT INTO PUBLISHED
>     VALUES (
>       "Pearson",
>       9780133776744);

>     /*
>      You can use
>      SELECT * FROM PUBLISHED;
>      to check that the table was actually created.

>      EXERCISE 9

>      Answer the following short questions. In our model, as it is, …

>      Can an author have authored more than one textbook?
>      Yes.

>      Can a textbook have more than one author?
>      Yes.

>      Can a textbook without ISBN be inserted in the TEXTBOOK relation?
>      No, unless you create a "dummy" (fake) value for it,
>      like 0000000000000, but this value can be used only
>      once, since ISBN is the primary key.

>      Can the price of a textbook be negative?
>      Yes. We can actually test it:
>      INSERT INTO TEXTBOOK VALUES ("Test", 0000000000000, -1);

>      Can two author have the same first and last name?
>      No. The query:
>      INSERT INTO AUTHOR VALUES ('Smith', 'Bob', NULL), ('Smith', 'Bob', NULL);
>      returns
>      ERROR 1062 (23000): Duplicate entry 'Smith-Bob' for key 'PRIMARY'

>      Can two textbooks have the same title?
>      Yes, as long as they have different ISBN. The command
>      INSERT INTO TEXTBOOK VALUES ("Test", 0000000000001, NULL), ("Test", 0000000000002, NULL);
>      is processed just fine.

>      Can two editiors have the same address?
>      Yes. The command:
>      INSERT INTO EDITOR VALUES ("Test 1", "123 Main St.", NULL), ("Test 2", "123 Main St.", NULL);
>      is processed just fine.
>      */