## "SQL" - Relational database
This relational database presents the _Digest_ in interlinked data tables chained together by unique keys. The `digest.db` database can be used for generating advanced analytical insights about Roman law as represented by the _Digest_ and for searching text and associated data in a structured and efficient manner.
The SQLite database was built with `sqlite3` on the command line and the `sqlite3` Python package.
### 1. Create database: `digest.db`
1. `D_sql_create_db.py > digest_skeleton.db`
The Python script imports `sqlite3` and its `Error` function and creates an empty database stored in the `sql` folder of the repository.
2. `Manual schema > D_sql.graphml`
The core structure of the `digest` database is drafted in the yEd graph editor and stored in the `images` folder of the repository. It includes the name and datatype of the fields in the various tables and notes their primary (PK) and foreign keys (FK). The graph's edges uses the arrow symbols to indicate one-and-only-one to many-or-one relations between the tables.
The graph below is a visual representation of the tables in the database. Each table includes an "id" as primary key (PK) and one or more foreign keys (FK) which point to the "id" columnn of a different table. It is by foreign keys that the tables are chained together in many-to-one relationships. The entity boxes for the tables include the names and datatypes of the columns where "tinyint" and "smallint" are two types of integer, "varchar" is a limited length, while "text" is an unlimited length text type. The three colours indicate the three different sources from which the tables are created. Tables in shaded blue are created from the same source as the core "text" table, i.e. the _Digest_ text as presented in Amanuensis, but they were separated to structure the information.
3. `D_sql_create_tables.py > digest_skeleton.db`
The Python script imports `sqlite3` and its `Error` function. It defines a `Connection` object for accessing `digest_skeleton.db` and a `cursor` object to create tables in it. The file's `main` function defines six tables with their primary keys, fields and datatypes as well as their foreign key restrictions which create connections between tables. The structure follows the structure drafted in the `D_sql` graph above.
Running the file creates the tables which are checked by looking at `digest.db` in the command line by `sqlite3` with `> .tables`. Fields and datatypes are checked by the following sequence of commands: `> .header on > .mode column > pragma table_info('text');`.
4. `D_sql_load_data.py > digest.db`
A copy of `digest_skeleton.db` is made in the same directory. This `digest.db` file will be used to populate the database with information from the flat files created in the ["Ddf"](/Ddf_documentation.md) stage of the project. The script establishes a connection with the database by creating a `Connection` object. Six `create_` functions are defined for each of the six empty tables in `digest.db`: (1) `create_jurist`, (2) `create_work`, (3) `create_book`, (4) `create_bko`, (5) `create_section`, and (6) `create_text`. These functions create a `Cursor` object with a method of the `Connection` object and include a `SQL` statement which enters one row into the table with values matching the table's column labels. As the struture of flat files do not match the structure of the tables, the script creates some temporary dataframes collecting information in the right order, right format and adhering to the restrictions (keys, data types restrictions etc.) of the `SQL` schema. The script's main function populates the tables with the `create_` functions by looping over the rows in the relevant flat files. The script ends with the command of running the `main` function. No error message is received which means that the loaded data adheres to the restrictions. The populated `digest.db` database file is inspected with the `sqlite3` command line application as well as in the graphical interface of `DB Browser for SQLite`.
### 2. Instructions for using `digest.db`
Further information and instructions for use are included in the [`README.md` file in the `sql` folder](/sql/README.md).