Set Up Database

[leukschrauber]

MySQL 8 Database Develop a SQL script that sets up the table structures as shown in the ER diagram: https://drive.google.com/drive/folders/1wuTfx6KtINIxB--kIOJI6rF1abE5CrTV?usp=drive_link

Include foreign-key, pimary key and not null constraints where necessary

[marianmair]

@leukschrauber I only have to create the SQL query to set up the database. The DB is running local on your PC right? In which file type do you need the script. Is the SQL querys alone enough or do you need in python or somting else?

[marianmair]

@leukschrauber Hier mal mein Ergebnis, falls wir noch etwas anpassen, können wir das machen:

`-- Open existing database USE existing_database_name;

-- Create tbKnowledge table CREATE TABLE tbKnowledge ( knowledge_id INT AUTO_INCREMENT, created_at DATE, updated_at DATE, question_vector TEXT, answer TEXT, created_by ENUM('System', 'User'), PRIMARY KEY (knowledge_id) );

-- Create tbKnowledgeResource table CREATE TABLE tbKnowledgeResource ( knowledgeResource_id INT AUTO_INCREMENT, knowledge_id INT, resource_path TEXT, created_at DATE, updated_at DATE, created_by ENUM('System', 'User'), PRIMARY KEY (knowledgeResource_id), FOREIGN KEY (knowledge_id) REFERENCES tbKnowledge(knowledge_id) );

-- Create the tbConversation table CREATE TABLE tbConversation ( conversation_id INT PRIMARY KEY, created_at DATE, updated_at DATE, question_vector TEXT, closed BOOLEAN, language ENUM('English', 'French', 'Italian', 'German'), rating INT, user_id TEXT );

-- Create the tbMessage table CREATE TABLE tbMessage ( knowledgeResource_id INT, conversation_id INT, message TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, User ENUM('System', 'User'), PRIMARY KEY (knowledgeResource_id), FOREIGN KEY (conversation_id) REFERENCES tbConversation(conversation_id) );

-- Create the tbIncidentReport table CREATE TABLE tbIncidentReport ( incident_report_id INT PRIMARY KEY, conversation_id INT, text TEXT, FOREIGN KEY (conversation_id) REFERENCES tbConversation(conversation_id) );`

Ich hab jetzt mal ein bisschen was noch angepasst. Hab ein paar FK löschen müssen, damit es funktioniert. Da müssten wir das Datenmodell noch anpassen.

Converstation Message ist ein 1:n beziehung conversation IncidentReport ist eine 1:1 beziehung.

Sonst können wir das gern nächste Woche besprechen.

LG Marian

[leukschrauber]

@marianmair: Thanks! DB wird lokal auf meinem Rechner laufen, ja. :) Ich nehm das Skript her, sobald ich mit dem Programmieren anfange. Eventuell pass ich nochmal was an, wenn es sich bei der Entwicklung ergibt, aber da würde ich dir bescheid geben!

[leukschrauber]

start transaction read write;

-- Create jpa_knowledge table CREATE TABLE jpa_knowledge ( id bigint not null auto_increment, created_at datetime(6), updated_at datetime(6), question_vector varchar(255) not null, question varchar(255) not null, answer varchar(255) not null, created_by varchar(255) not null, PRIMARY KEY (id) );

-- Create jpa_knowledge_resource table CREATE TABLE jpa_knowledge_resource ( id bigint not null auto_increment, knowledge_id bigint not null, resource_path varchar(255) not null, created_at datetime(6), updated_at datetime(6), created_by varchar(255) not null, PRIMARY KEY (id) );

-- Create the jpa_conversation table CREATE TABLE jpa_conversation ( id bigint not null auto_increment, created_at datetime(6), updated_at datetime(6), question_vector varchar(255) not null, closed BOOLEAN, language varchar(255) not null, rating BOOLEAN, user_id varchar(255) not null, knowledge_id bigint not null, PRIMARY KEY (id) );

-- Create the jpa_message table CREATE TABLE jpa_message ( id bigint not null auto_increment, conversation_id bigint not null, message varchar(255) not null, created_at datetime(6), updated_at datetime(6), created_by varchar(255) not null, PRIMARY KEY (id) );

-- Create the jpa_incident_report table CREATE TABLE jpa_incident_report ( id bigint not null auto_increment, conversation_id bigint not null, text varchar(255), PRIMARY KEY (id) );

alter table jpa_incident_report add constraint FK29cplly21tatp0o9ccjnj32r5 foreign key (conversation_id) references jpa_conversation (id); alter table jpa_message add constraint FK29cplly21tat20o9ccjnj32r5 foreign key (conversation_id) references jpa_conversation (id); alter table jpa_conversation add constraint FK29cpl2y21tat20o9ccjnj32r5 foreign key (knowledge_id) references jpa_knowledge (id); alter table jpa_knowledge_resource add constraint FK29cpllyn1tatp0o9ccjnj32r5 foreign key (knowledge_id) references jpa_knowledge (id);

commit;