The SQL Programming Language

Resources

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

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

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:

Datatypes

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

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:

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:

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:

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

diagram
/* 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:

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

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;
SELECT Name
FROM STUDENT, GRADE
WHERE Grade > 3.0
  AND STUDENT.Login = GRADE.Login;

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:

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

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:

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: MySQL Installer 
    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

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:

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:

Exercises

Exercise 3.1

For each of the following, fill in the blanks:

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

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…

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)?

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:

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:

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

Solution 3.32

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:

The most complete lists of commands are probably at

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:

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)
);

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:

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):

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:

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.

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:

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:

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:

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

diagram for problem 3.2

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)

Solution to Problem 3.5 (Revisiting the PROF table)

Pb 3.5– Solution to Q. 1

Ignoring the LECTURE relation, we have:

diagram for problem 3.5

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:

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:

diagram for problem 3.7

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.

diagram for problem 3.12

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:

diagram for problem 3.13

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:

diagram for problem 3.15

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.
 */