commit a1f57f7e155f767209686cd6da6711b0f9cf8899
Author: David Friesecký <frieseck@students.zcu.cz>
Date:   Wed Mar 31 10:27:55 2021 +0200

    Re #8471 - Repaired datamodel (SQLite)
    - inserted new table for NxN relation (Certificates - UsageTypes)
    - edited .sql scripts

diff --git a/SQLite_database.sql b/SQLite_database.sql
index 05bf14b..24505f8 100644
--- a/SQLite_database.sql
+++ b/SQLite_database.sql
@@ -1,60 +1,70 @@
 /* ---------------------------------------------------- */
 /*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 27-b�e-2021 17:17:14 				*/
+/*  Created On : 31-b�e-2021 10:24:41 				*/
 /*  DBMS       : SQLite 								*/
 /* ---------------------------------------------------- */
 
 /* Drop Tables */
 
-DROP TABLE IF EXISTS 'CertificateType'
+DROP TABLE IF EXISTS 'PrivateKeys'
 ;
 
-DROP TABLE IF EXISTS 'CertificateUsage'
+DROP TABLE IF EXISTS 'CertificateTypes'
 ;
 
-DROP TABLE IF EXISTS 'PrivateKey'
+DROP TABLE IF EXISTS 'UsageTypes'
 ;
 
-DROP TABLE IF EXISTS 'Certificate'
+DROP TABLE IF EXISTS 'Certificates'
+;
+
+DROP TABLE IF EXISTS 'CertificatesUsages'
 ;
 
 /* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */
 
-CREATE TABLE 'CertificateType'
+CREATE TABLE 'PrivateKeys'
 (
-	'certificate_type_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'certificate_type' TEXT NOT NULL
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'private_key' TEXT NOT NULL,
+	'password' TEXT NOT NULL
 )
 ;
 
-CREATE TABLE 'CertificateUsage'
+CREATE TABLE 'CertificateTypes'
 (
-	'certificate_usage_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'certificate_usage' TEXT NOT NULL
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_type' TEXT NOT NULL
 )
 ;
 
-CREATE TABLE 'PrivateKey'
+CREATE TABLE 'UsageTypes'
 (
-	'private_key_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'private_key' TEXT NOT NULL,
-	'password' TEXT NULL
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'usage_type' TEXT NOT NULL
 )
 ;
 
-CREATE TABLE 'Certificate'
+CREATE TABLE 'Certificates'
 (
-	'certificate_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 	'common_name' TEXT NOT NULL,
 	'valid_from' TEXT NOT NULL,
 	'valid_to' TEXT NOT NULL,
 	'pem_data' TEXT NOT NULL,
-	'type_id' INTEGER NULL,
-	'private_key_id' INTEGER NULL,
-	'usage_id' INTEGER NULL,
-	CONSTRAINT 'FK_Certificate_Type' FOREIGN KEY ('type_id') REFERENCES 'CertificateType' ('certificate_type_id') ON DELETE No Action ON UPDATE No Action,
-	CONSTRAINT 'FK_Certificate_Usage' FOREIGN KEY ('usage_id') REFERENCES 'CertificateUsage' ('certificate_usage_id') ON DELETE No Action ON UPDATE No Action,
-	CONSTRAINT 'FK_Parent_Certificate' FOREIGN KEY ('certificate_id') REFERENCES 'Certificate' ('certificate_id') ON DELETE No Action ON UPDATE No Action,
-	CONSTRAINT 'FK_Private_Key' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKey' ('private_key_id') ON DELETE No Action ON UPDATE No Action
+	'private_key_id' INTEGER NOT NULL,
+	'certificate_type_id' INTEGER NOT NULL,
+	CONSTRAINT 'FK_CertificateTypes' FOREIGN KEY ('certificate_type_id') REFERENCES 'CertificateTypes' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_PrivateKeys' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKeys' ('id') ON DELETE No Action ON UPDATE No Action
+)
+;
+
+CREATE TABLE 'CertificatesUsages'
+(
+	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	'certificate_id' INTEGER NULL,
+	'usage_type_id' INTEGER NULL,
+	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('certificate_id') REFERENCES 'Certificates' ('id') ON DELETE No Action ON UPDATE No Action,
+	CONSTRAINT 'FK_UsageTypes' FOREIGN KEY ('usage_type_id') REFERENCES 'UsageTypes' ('id') ON DELETE No Action ON UPDATE No Action
 )
 ;
diff --git a/SQLite_default_values.sql b/SQLite_default_values.sql
index 34bbc7b..d5bb5d8 100644
--- a/SQLite_default_values.sql
+++ b/SQLite_default_values.sql
@@ -6,11 +6,11 @@
 
 /* Insert default values */
 
-INSERT INTO CertificateType(certificate_type) VALUES('ROOT_CA');
-INSERT INTO CertificateType(certificate_type) VALUES('INTERMEDIATE_CA');
-INSERT INTO CertificateType(certificate_type) VALUES('CERTIFICATE');
+INSERT INTO CertificateTypes(certificate_type) VALUES('ROOT_CA');
+INSERT INTO CertificateTypes(certificate_type) VALUES('INTERMEDIATE_CA');
+INSERT INTO CertificateTypes(certificate_type) VALUES('CERTIFICATE');
 
-INSERT INTO CertificateUsage(certificate_usage) VALUES('CA');
-INSERT INTO CertificateUsage(certificate_usage) VALUES('SSL');
-INSERT INTO CertificateUsage(certificate_usage) VALUES('SIGNATURE');
-INSERT INTO CertificateUsage(certificate_usage) VALUES('AUTHENTICATION');
+INSERT INTO UsageTypes(usage_type) VALUES('CA');
+INSERT INTO UsageTypes(usage_type) VALUES('SSL');
+INSERT INTO UsageTypes(usage_type) VALUES('SIGNATURE');
+INSERT INTO UsageTypes(usage_type) VALUES('AUTHENTICATION');
diff --git a/design.EAP b/design.EAP
index 3675ed9..1f5dc0b 100644
Binary files a/design.EAP and b/design.EAP differ
