Introduction

Last week, we continued our discussion of what makes a set of relations a "reasonable" relational schema for one's data.
We said BCNF can be too restrictive in that it is possible for a decomposition into BCNF to not be dependency preserving.
We weakened BCNF to get 3NF which supports lossless joins, is dependency preserving, and minimizes anomalies.
We begin today by giving an algorithm to decompose a relation satisfying a set of functional dependencies into 3NF relations.
Then, as HW2 says to implements various relations and queries in Mysql, I am going to give a quick tutorial on Mysql before continuing to talk about table decomposition.
As I was late to talk about Mysql, I am giving people to Wednesday to complete the homework.

The Synthesis Algorithm for 3NF Schemas

Input

A relation `R` and a set of FDs `F` that hold on `R`.

Output

A decomposition of `R` into relations, each of which is in 3NF. Further, the decomposition satisfies the LJD and DP properties.

Method

Perform the following steps:

Find a minimal basis for `F`, say `G`.
For each FD `X->A` in `G`, use `XA` as the schema of one of the relations in the decomposition.
If none of the relation schema from Step 2 is a superkey for `R`, add another relation whose schema is a key for `R`.

Example of the Synthesis Algorithm

Consider the relation `R(A,B,C,D,E)` with FDs `AB->C`, `C->B`, and `A->D`.
To start, notice the FDs are already minimal (can't eliminate any FDs from set or shrink num attributes on left hand side of existing FDs).
Step 2 of the synthesis algorithm, then gives the relations `R_1(A,B,C)`, `R_2(B, C)`, and `R_3(A,D)`.
As the attributes of `R_2` are a subset of those of `R_1`, we can safely delete `R_2` from our list (even though not said in algorithm).
`R` has two keys `{A,B,E}` and `{A,C,E}` neither of which is contained in the attributes of `R_1` or `R_3`. So we add a table `R_4(A,B,E)`.
We thus obtain the final decomposition `R_1(A,B,C)`, `R_3(A,D)`, and `R_4(A,B,E)`.

Why the Synthesis Algorithm Works

To show the algorithm works we need to show the resulting relations have LJD and DP properties and the result is in 3NF:
1. Lossless Join. Let `K` be the attributes of a relation in the decomposition whose attributes form a superkey. Consider the sequence of FDs used in our algorithm to compute `K^+`. Since `K` is a superkey `K^+` will be all attributes. The same sequence of FDs when applied in the lossless join chase algorithm will cause the subscripted symbols in the row corresponding to `K` to be equated to unsubscripted symbols in the same order as attributes were added to the closure with eventually all attributes become unsubscripted. Thus, it has the LJD property.
2. Dependency Preservation. Each FD of the minimal basis has all its attributes in some relation of the decomposition. Therefore, each dependency can be checked in the decomposed relations.
3. 3NF. If we have a relation whose schema is a key, then this relation will be in 3NF. This is because all attributes of this relation are prime, so won't cause a 3NF violation. For relations whose schemas are from FDs of a minimal basis, we skip the argument, but as a hint we want to show a 3NF violation in them implies that the basis is not minimal.

Quiz

Which of the following is true?

Starting on a single relation, the BCNF decomposition algorithm from class always outputs two relations.
In order to run the chase, lossless join algorithm you need a set of functional dependencies and a set of decomposed tables each with at least 5 rows.
There exists table decompositions into 3NF that are not in BCNF.

MySQL

MySQL was originally developed in Sweden by Michael "Monty" Widenius and David Axmark in 1994-1995 based on the msql DBMS from Hughes Technologies that had been created in 1993.
The original MySQL AB company was sold to Sun in 2008, which in turn became part of Oracle in 2010.
In 2009, around the time the Sun-Oracle deal was announced, Widenius forked the MySQL code to create MariaDB.
MySQL was declared DBMS of the year 2019 from the DB-Engines ranking.
As of Fall 2023, MySQL 8.1.x is the latest version of MySQL.

Ways to Install MySQL

If your computer already has Mysql (say you took CS174), you don't need to install another copy of it -- likely that will only cause headaches.
There are three typical ways to install Mysql:
1. Go to the Mysql Downloads, download the software, and follow the instructions (if you're really nerdy, you could also download the source and compile it).
2. Use a command line package manager such as Advanced package tool (apt), for Ubuntu Linux, Homebrew (brew) for Macos, or Chocolatey (choco) for Windows. Each of these tools is run in roughly the same way (the name of what the mysql package is called might be slightly different). For example, to install and get running mysql using Homebrew, open a terminal and type:
```
brew install mysql
brew services start mysql
```
3. Using a software bundle such as: MAMP, WAMP, or XAMPP. AMP stands for Apache (a webserver), Mysql, P languages (PHP, Perl, and Python). So the GUI installer you download will give you all three things.

Interacting with MySQL

The Mysql server is a program, mysqld, which typically runs in the background.
As we saw on the previous slide, if you installed Mysql using a package manager, you can start and stop this process using that manager's services functions.
If you installed using a software bundle, it will typically have a control panel that lets you toggle on an off the service.
If you installed directly from the Mysql site, you can start and stop the program from a shell, directly by using the mysqld command (Windows), by using the mysqld_safe script, or, preferably (Linux/Macos), by using the mysql.server script with options:
```
mysql.server start
mysql.server stop
```
The command line tool mysqladmin allows you to do some command line configuration of a Mysql server. For example, to set the root password of the server you can do:
```
mysqladmin -uroot password what_password_is
```
Interaction with the Mysql DBMS takes a client-server approach...
When mysqld (the server program) is running, it binds itself to a TCP networking socket port (usually 3306) and starts listening for connections.
Client programs then open TCP network connections to that port and send request messages to the server, the server executes commands such as SQL instructions, and returns responses back to the client.
Mysql comes with a shell client program called mysql which handles making connections for a user, and then lets you type SQL command for the DBMS.
You can get a mysql shell by typing:
```
mysql -uroot -pwhat_password_is
```

MySQL Command Line

To see what databases are available you can use the command:
```
show databases;
```
To use a particular database type:
```
use dbname;
```

To create/delete a database you can use the commands:

CREATE DATABASE some_name;
DROP DATABASE some_name;

To see what tables the currently use database has type:
```
show tables;
```

To add a new user you can type at the prompt:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES 
  on dbname.* TO username@127.0.0.1 IDENTIFIED BY 'password';

To quit mysql type \q or quit. For help type \h or help.
To login using this new user type at the command prompt:
```
mysql -uusername -ppassword
```
From a shell mysql prompt, once you can your favorite SQL commands like: CREATE TABLE, INSERT, UPDATE, DELETE, SELECT, etc.

Multivalued Dependencies

Back to table decompostions...

Consider the relation below which models where stars have lived and the movies they have been in:

name      | street        | city      | title               | year
C. Fisher | 123 Maple St. | Hollywood | Star Wars           | 1977
C. Fisher | 5 Locust Ln.  | Malibu    | Star Wars           | 1977
C. Fisher | 123 Maple St. | Hollywood | Empire Strikes Back | 1980
C. Fisher | 5 Locust Ln.  | Malibu    | Empire Strikes Back | 1980
C. Fisher | 123 Maple St. | Hollywood | Return of the Jedi  | 1983
C. Fisher | 5 Locust Ln.  | Malibu    | Return of the Jedi  | 1983

There are no nontrivial FDs in the above. No attribute is determined by the other four. So the above is in BCNF.
For example, a star might have two homes with the same street address, but different cities, so city is not determined by the four other attributes.
On the other hand there is obviously some kind of redundancy in the above table: If we know the star, then there is only a fixed set of addresses they can live at.
So rather than name fixing a single possible address, it is fixing a fixed possible list of addresses.
We call such a dependency, a multi-valued dependency (MVD), and write `X ↠ Y` to say `X` multivalue determines `Y`.
In the above, we have `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y`

Multivalued Dependencies

We can more formally say the MVD
`A_1A_2...A_n ↠ B_1...B_m`
holds on `R` if for each pair of tuples `t` and `u` of relation `R `that agree on all the `A_i`s, we can find in `R` some tuple `v` that agrees:
1. With both `t` and `u` on the `A_i`s,
2. With `t` in the `B_j`'s, and
3. With `u` on all attributes of `R` that are not among the `A_i`s or `B_j`s.

As an example, let `t` and `u` be the tuples:

name      | street        | city      | title               | year
C. Fisher | 123 Maple St. | Hollywood | Star Wars           | 1977
C. Fisher | 5 Locust Ln.  | Malibu    | Empire Strikes Back | 1980

Then `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` asserts that there is a tuple with street and city given by 123 Maple St. Hollywood and whose title and year are Empire Strikes Back and 1980. This row in fact does exist, it is the third row in our table.

Reasoning about MVDs

There are a number of rules about MVDs that are similar to those we had for FDs:
- Trivial MVDs. The MVD
  `A_1A_2...A_n ↠ B_1...B_m`
  holds in any relation if `{B_1, ..., B_m} subseteq {A_1,A_2,...,A_n}`.
- Transitive MVDs. If the MVDs
  `A_1A_2...A_n ↠ B_1...B_m` and `B_1...B_m ↠ C_1...C_k`
  hold, then `A_1A_2...A_n ↠ C_1...C_k` will hold. Here any `C_t`s which are also `A_i`'s must be deleted from the right side.
- FD Promotion. Every FD is an MVD.
- Complementation Rule. Given a relation `R(A_1,A_2,...,A_n, B_1...B_m, C_1...C_k)`, if `A_1A_2...A_n ↠ B_1...B_m` then `A_1A_2...A_n ↠ C_1...C_k`.
- More Trivial MVDs. Given a relation `R(A_1,A_2,...,A_n, B_1...B_m)` with all attributes listed, then `A_1A_2...A_n ↠ B_1...B_m` holds in `R`.

Fourth Normal Form

We now modify BCNF to get a normal form on MVDs which avoids MVD-style redundancy.
We say a relation is in fourth normal form (4NF) if whenever
`A_1A_2...A_n ↠ B_1...B_m`
is a nontrivial MVD, `{A_1,A_2,...,A_n}` is a superkey.
In our table earlier `\n\a\m\e` wasn't a superkey, but `n\a\m\e ↠ s\t\r\e\e\t \ \ c\i\t\y` was non-trivial, so that relation was not in 4NF.
Since every FD is an MVD, if one is in `4NF` one will be in `BCNF`.

Finish 3NF, MySQL, Multi-valued Dependencies, High Level Database Models

Outline

Introduction

The Synthesis Algorithm for 3NF Schemas

Example of the Synthesis Algorithm

Why the Synthesis Algorithm Works

Quiz

MySQL

Ways to Install MySQL

Interacting with MySQL

MySQL Command Line

Multivalued Dependencies

Multivalued Dependencies

Reasoning about MVDs

Fourth Normal Form