SQL_documentation.md

"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" 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.