Normalising the data

Normalisation is the division of attributes or table columns into several relations or tables so that no redundancies are included.

Example

In the following example, we normalise the language in which the books were published.

1. To do this, we first create a new table languages with the columns id and language_code:

   cursor.execute(
       """CREATE TABLE languages
                     (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     language_code VARCHAR(2))"""
   

2. Then we create the values de and en in this table:

   cursor.execute(
       """INSERT INTO languages (language_code)
                     VALUES ('de')"""
   )
   
   cursor.execute(
       """INSERT INTO languages (language_code)
   

3. Since SQLite does not support MODIFY COLUMN, we now create a temporary table temp with all columns from books and a column language_code that uses the column id from the languages table as a foreign key:

   cursor.execute(
       """CREATE TABLE "temp" (
                     "id" INTEGER,
                     "title" TEXT,
                     "language_code" INTEGER REFERENCES languages(id),
                     "language" TEXT,
                     "author" TEXT,
                     "license" TEXT,
                     "release_date" DATE,
                     PRIMARY KEY("id" AUTOINCREMENT)
                     )"""
   

4. Now we transfer the values from the books table to the temp table:

   cursor.execute(
       """INSERT INTO temp (title,language,author,license,release_date)
                     SELECT title,language,author,license,release_date FROM books"""
   

5. Transfer the specification of the language in books as the id of the data records from the languages table to temp.

   cursor.execute(
       """UPDATE temp
                     SET language_code = 1
                     WHERE language = 'de'"""
   )
   

6. Now we can delete the languages column in the temp table:

   cursor.execute("""ALTER TABLE temp DROP COLUMN language""")
   

   Note

   DROP COLUMN can only be used from Python versions from 3.8 that were released after 27 April 2021.

   With older Python versions, another table would have to be created that no longer contains the languages column and then the data records from temp would have to be inserted into this table.

7. The books table can now also be deleted:

   cursor.execute("""DROP TABLE books""")
   

8. And finally, the temp table can be renamed books:

   cursor.execute("""ALTER TABLE temp RENAME TO books""")