DB table 구성하기 - 공부 내용 - 아무튼-작업일지

공부 내용

DB table 구성하기

Y

yeji Kim

Sep 5, 20242m

To efficiently implement the features you described, the database structure needs to account for several types of entities: lecture notes, textbooks, past exam questions, professors, and study tips. Additionally, the system should allow cross-referencing between these entities, version control for lecture notes, and the ability to filter data by professor.

Here’s how you can structure the database tables:

Key Tables for the Database Structure

1.

Subjects (subjects): Stores information about each subject.

2.

Professors (professors): Stores information about the professors.

3.

Lecture Notes (lecture_notes): Stores metadata for each lecture note document.

4.

Lecture Slides (lecture_slides): Stores content for individual slides within a lecture note.

5.

Textbooks (textbooks): Stores metadata about textbooks for each subject.

6.

Textbook Pages (textbook_pages): Stores the text content of each page in a textbook.

7.

Test Questions (test_questions): Stores individual test questions.

8.

Slide to Textbook Mapping (slide_textbook_mapping): Relates specific slides to textbook pages or paragraphs.

9.

Slide to Test Question Mapping (slide_question_mapping): Relates specific slides to test questions.

10.

Test Question to Textbook Mapping (question_textbook_mapping): Relates specific test questions to textbook pages or paragraphs.

11.

Study Tips (study_tips): Stores study tips from professors related to each subject.

12.

Versions (lecture_note_versions): Manages versioning for the lecture notes.

Table Structures and Relationships

1.

Subjects Table (subjects)

subject_id: INT (Primary Key)

subject_name: VARCHAR(255) (e.g., "Biochemistry")

description: TEXT (optional description)

2.

Professors Table (professors)

professor_id: INT (Primary Key)

professor_name: VARCHAR(255)

email: VARCHAR(255) (optional)

department: VARCHAR(255) (optional)

3.

Lecture Notes Table (lecture_notes)

lecture_note_id: INT (Primary Key)

subject_id: INT (Foreign Key to subjects)

professor_id: INT (Foreign Key to professors)

title: VARCHAR(255) (Title of the lecture note)

version: VARCHAR(50) (Version of the lecture notes)

upload_date: DATE (When the lecture notes were uploaded)

file_path: VARCHAR(255) (Path to the lecture note file, if necessary)

notes: TEXT (Additional notes)

4.

Lecture Slides Table (lecture_slides)

slide_id: INT (Primary Key)

lecture_note_id: INT (Foreign Key to lecture_notes)

slide_number: INT (Order of the slide in the lecture)

slide_title: VARCHAR(255) (Optional: Title or caption of the slide)

slide_content: TEXT (Content of the slide in text format)

image_path: VARCHAR(255) (Optional: Path to image associated with the slide)

notes: TEXT (Optional: additional notes)

5.

Textbooks Table (textbooks)

textbook_id: INT (Primary Key)

subject_id: INT (Foreign Key to subjects)

textbook_title: VARCHAR(255)

author: VARCHAR(255)

publication_year: YEAR

isbn: VARCHAR(13) (ISBN number)

6.

Textbook Pages Table (textbook_pages)

page_id: INT (Primary Key)

textbook_id: INT (Foreign Key to textbooks)

page_number: INT (Page number)

content: TEXT (Text of the page)

7.

Test Questions Table (test_questions)

question_id: INT (Primary Key)

subject_id: INT (Foreign Key to subjects)

professor_id: INT (Foreign Key to professors)

exam_year: YEAR

exam_type: VARCHAR(50) (E.g., "Midterm", "Final", etc.)

question_text: TEXT (The content of the test question)

correct_answer: TEXT (Optional: Correct answer)

image_path: VARCHAR(255) (Optional path to images associated with the question)

8.

Slide to Textbook Mapping Table (slide_textbook_mapping)

mapping_id: INT (Primary Key)

slide_id: INT (Foreign Key to lecture_slides)

textbook_id: INT (Foreign Key to textbooks)

page_id: INT (Foreign Key to textbook_pages)

relation_type: VARCHAR(255) (E.g., "full match", "partial match", "reference")

9.

Slide to Test Question Mapping Table (slide_question_mapping)

mapping_id: INT (Primary Key)

slide_id: INT (Foreign Key to lecture_slides)

question_id: INT (Foreign Key to test_questions)

relation_type: VARCHAR(255) (E.g., "direct", "related", "concept match")

10.

Test Question to Textbook Mapping Table (question_textbook_mapping)

mapping_id: INT (Primary Key)

question_id: INT (Foreign Key to test_questions)

textbook_id: INT (Foreign Key to textbooks)

page_id: INT (Foreign Key to textbook_pages)

relation_type: VARCHAR(255) (E.g., "direct answer", "related concept")

11.

Study Tips Table (study_tips)

tip_id: INT (Primary Key)

professor_id: INT (Foreign Key to professors)

subject_id: INT (Foreign Key to subjects)

tip_content: TEXT (Text content of the study tip)

created_at: DATETIME (Time when the tip was added)

12.

Versions Table (lecture_note_versions)

version_id: INT (Primary Key)

lecture_note_id: INT (Foreign Key to lecture_notes)

version_number: VARCHAR(50) (E.g., "v1.0", "v2.0")

uploaded_at: DATETIME (When this version was uploaded)

file_path: VARCHAR(255) (Optional: Path to the file of this version)

notes: TEXT (Optional notes for this version)

Query Examples

1.

Check Textbook Pages, Paragraphs, and Test Questions Related to Each Specific Slide in a Lecture Note

-- Fetch related textbook pages for a specific slide
SELECT tp.page_number, tp.content, t.textbook_title
FROM slide_textbook_mapping stm
JOIN textbook_pages tp ON stm.page_id = tp.page_id
JOIN textbooks t ON tp.textbook_id = t.textbook_id
WHERE stm.slide_id = ?;

-- Fetch related test questions for a specific slide
SELECT tq.question_text, tq.exam_year, tq.exam_type
FROM slide_question_mapping sqm
JOIN test_questions tq ON sqm.question_id = tq.question_id
WHERE sqm.slide_id = ?;

1.

Check Lecture Slides and Textbook Contents Related to Test Questions

-- Fetch related lecture slides for a test question
SELECT ls.slide_title, ls.slide_content
FROM slide_question_mapping sqm
JOIN lecture_slides ls ON sqm.slide_id = ls.slide_id
WHERE sqm.question_id = ?;

-- Fetch related textbook pages for a test question
SELECT tp.page_number, tp.content, t.textbook_title
FROM question_textbook_mapping qtm
JOIN textbook_pages tp ON qtm.page_id = tp.page_id
JOIN textbooks t ON tp.textbook_id = t.textbook_id
WHERE qtm.question_id = ?;

1.

Collect Lecture Notes and Test Questions from Each Professor

-- Fetch all lecture notes for a professor
SELECT ln.title, ln.version, ln.upload_date
FROM lecture_notes ln
WHERE ln.professor_id = ?;

-- Fetch all test questions from a professor
SELECT tq.question_text, tq.exam_year, tq.exam_type
FROM test_questions tq
WHERE tq.professor_id = ?;

1.

Reflect Updates to the Lecture Notes Version

When uploading a new version of a lecture note, insert a new record into the lecture_note_versions table and update the lecture_notes

table with the latest version information.

-- Insert a new version
INSERT INTO lecture_note_versions (lecture_note_id, version_number, uploaded_at, file_path, notes)
VALUES (?, ?, NOW(), ?, ?);

-- Update the lecture note to reflect the latest version
UPDATE lecture_notes
SET version = ?, upload_date = NOW()
WHERE lecture_note_id = ?;

Additional Considerations

1.

Indexes:

Add indexes on the foreign key columns (professor_id, slide_id, question_id, textbook_id) to optimize queries for faster retrieval.

2.

Full-Text Search:

Implement full-text search on fields like slide_content, question_text, and textbook_content to allow for efficient searching of large bodies of text.

3.

Versioning:

Each lecture note can have multiple versions. This is handled by the lecture_note_versions table. When fetching a lecture note, you can specify which version to retrieve, or you can retrieve the latest version.

4.

Language and Multilingual Support:

If the system needs to support multiple languages, consider adding a language_code field to relevant tables like lecture_slides, textbooks, test_questions, and study_tips to differentiate between content in different languages.

5.

Diagrams and Images:

Ensure that the tables include fields (image_path) for storing paths to images, diagrams, or charts that accompany the lecture notes or test questions. These visual aids are important for certain subjects like biochemistry or physics.

Summary

This database schema will efficiently support the cross-referencing of lecture notes, textbooks, and test questions. It will also allow the system to collect data by professor, track multiple versions of lecture notes, and store study tips from professors. The system is flexible enough to handle updates and provide a comprehensive resource for both students and professors.

Key Features to Implement:
Linking to Original PDF Content: Provide direct links to the specific parts of the original PDF for lecture notes, textbook pages, and test questions.
Handling Images and Photos in Test Questions: Ensure that questions containing images can display both the text and image properly.
Incorporating Tables and Pictures in Lecture Notes and Textbooks: Enable storage and display of tables and images that appear in textbook pages or lecture slides.
Updated Database Structure

1.

Additions to Existing Tables
Changes to the textbook_pages, lecture_slides, and test_questions tables:

PDF linking: Add fields to store the exact location (e.g., page number or coordinates) of content in the original PDF.
Tables and images: Add fields to store image paths or links for tables and pictures embedded in the textbook pages, lecture notes, and test questions.
Updated Table Definitions:
Subjects Table (subjects) — Unchanged from before.

Professors Table (professors) — Unchanged from before.

Lecture Notes Table (lecture_notes)

lecture_note_id: INT (Primary Key)
subject_id: INT (Foreign Key to subjects)
professor_id: INT (Foreign Key to professors)
title: VARCHAR(255) (Title of the lecture note)
version: VARCHAR(50) (Version of the lecture notes)
upload_date: DATE
file_path: VARCHAR(255) (Path to the original PDF)
notes: TEXT (Optional extra notes)
Lecture Slides Table (lecture_slides)

slide_id: INT (Primary Key)
lecture_note_id: INT (Foreign Key to lecture_notes)
slide_number: INT (Order in the lecture)
slide_title: VARCHAR(255) (Optional)
slide_content: TEXT (Content of the slide in text format)
image_path: VARCHAR(255) (Optional path to images used in the slide)
pdf_page: INT (Page number of the slide in the original PDF)
pdf_coordinates: VARCHAR(255) (Optional: Coordinates for specific regions in the PDF, if needed for deep linking)
table_data: TEXT (Optional: JSON or serialized format for any tables in the slide)
notes: TEXT (Additional notes)
Textbooks Table (textbooks) — Unchanged from before.

Textbook Pages Table (textbook_pages)

page_id: INT (Primary Key)
textbook_id: INT (Foreign Key to textbooks)
page_number: INT (Page number of the textbook)
content: TEXT (The text of the page)
pdf_page: INT (Page number in the original textbook PDF)
pdf_coordinates: VARCHAR(255) (Optional: Coordinates for specific parts of the page in the PDF)
image_paths: TEXT (Optional: Comma-separated list of image paths extracted from the page)
table_data: TEXT (Optional: JSON or serialized format for any tables in the page)
Test Questions Table (test_questions)
question_id: INT (Primary Key)
subject_id: INT (Foreign Key to subjects)
professor_id: INT (Foreign Key to professors)
exam_year: YEAR
exam_type: VARCHAR(50) (E.g., "Midterm", "Final")
question_text: TEXT (Content of the question)
correct_answer: TEXT (Optional)
image_path: VARCHAR(255) (Optional: Path to an image included in the test question)
pdf_page: INT (Page number in the original PDF)
pdf_coordinates: VARCHAR(255) (Optional: Coordinates for the question in the PDF)
table_data: TEXT (Optional: JSON or serialized format for any tables in the question)
notes: TEXT (Optional: additional notes)
Slide to Textbook Mapping Table (slide_textbook_mapping) — Unchanged from before.

Slide to Test Question Mapping Table (slide_question_mapping) — Unchanged from before.

Test Question to Textbook Mapping Table (question_textbook_mapping) — Unchanged from before.

Study Tips Table (study_tips) — Unchanged from before.

Lecture Note Versions Table (lecture_note_versions) — Unchanged from before.

Explanation of Changes:
PDF Linking (New Fields: pdf_page and pdf_coordinates):

These fields will allow users to directly link back to the original PDF files (lecture notes, textbook, test questions) and view the relevant sections.
pdf_page: This is the page number in the original PDF where the content is located.
pdf_coordinates: (Optional) This field can be used to store coordinates on the PDF page if the goal is to deep-link to specific sections or highlight certain areas.
Image Paths for Lecture Notes, Textbooks, and Test Questions:

image_path fields allow you to store paths to images that are part of the lecture slides, textbook pages, or test questions. This ensures that diagrams, figures, and photos are properly displayed alongside the text content.
Test questions often contain images (such as diagrams or lab results). Storing the path in the image_path field ensures that the image can be displayed when presenting the question.
Table Storage (table_data):

table_data fields are used to store table information in a structured format. You can use JSON or another serialized format (e.g., CSV) to store tables. This ensures that tables in textbook pages, lecture notes, and test questions are retained and displayed appropriately.
Example: A table extracted from a textbook can be stored as a JSON object with rows and columns, allowing you to display it in a structured format within your app.
Query Examples for New Features

1.

Check Relevant Parts of the Original PDF for a Lecture Slide
To retrieve the location of a lecture slide in the original PDF:

sql
코드 복사
SELECT ls.slide_title, ls.pdf_page, ls.pdf_coordinates
FROM lecture_slides ls
WHERE ls.slide_id = ?;
This query will return the title of the slide, the page number in the original PDF, and optionally the coordinates if provided.

1.

Check Relevant Parts of the Original PDF for a Test Question
To retrieve the location of a test question in the original PDF:

sql
코드 복사
SELECT tq.question_text, tq.pdf_page, tq.pdf_coordinates, tq.image_path
FROM test_questions tq
WHERE tq.question_id = ?;
This query will return the question text, the page number in the original PDF, the coordinates (if provided), and the path to any associated images.

1.

Check for Tables and Images in a Textbook Page
To retrieve tables and images associated with a textbook page:

sql
코드 복사
SELECT tp.page_number, tp.content, tp.table_data, tp.image_paths, tp.pdf_page
FROM textbook_pages tp
WHERE tp.page_id = ?;
This query will return the content of the page, any tables stored in the table_data field, images stored in the image_paths field, and the page number in the original PDF.

Handling Image and Table Display in the Application
Images: The image_path fields can store the relative path to image files (e.g., diagrams or photos in questions). When displaying content, your application can fetch and render the images in conjunction with the text content.

Tables: Tables stored in table_data (in JSON format) can be dynamically rendered in the UI using libraries like DataTables (for HTML) or converted to a visually appealing format.

Example JSON format for table_data:
json
코드 복사
{
"headers": ["Compound", "Boiling Point", "Melting Point"],
"rows": [
["Water", "100°C", "0°C"],
["Ethanol", "78.37°C", "-114.1°C"],
["Methane", "-161.5°C", "-182.5°C"]
]
}
This format is easy to parse in the application and display as a table.

Summary of Changes
By modifying the database schema with fields for PDF linking, image paths, and table storage, you can:

Allow users to access specific parts of the original PDF content (lecture slides, textbook pages, and test questions).
Handle images in test questions, lecture slides, and textbook pages, ensuring that all non-textual information is properly displayed.
Store tables in a structured format, allowing for easier display and retrieval in the application.
This approach ensures a richer experience when navigating between different types of educational content.

Subscribe to '아무튼-작업일지'

Welcome to '아무튼-작업일지'!
By subscribing to my site, you'll be the first to receive notifications and emails about the latest updates, including new posts.
Join SlashPage and subscribe to '아무튼-작업일지'!

Subscribe

Other posts in '공부 내용'See all

yeji KimSep 19

The organization of information

Organization of recorded information The nature of information 유용성 - data<information<knowledge<understanding<wisdom Organization of information in different contexts Libraries Descriptive cataloging creating a description choosing access point ensuring authority control Subjective cataloging conceptual analysis - aboutness. translation - aboutness → controlled subject language choosing controlled vocabulary terms choosing classification notations Retrieval tools The basic retrieval tools, their formats, and their functions Bibliographies list of resources Catalogs individual items within collections of information resources Indexes

yeji KimSep 19

인지 부하 디자인

정보 시각화와 지식 시각화 정보 시각화 - 이해를 돕기 위해 지식 시각화 - 통찰력 전달, 새로운 지식 단위 생성 Hick의 법칙 선택 사항 최소화 복잡한 작업 단계 나누기 권장 옵션 강조 점진적인 보드 추상화 지점을 단순화 X 적절한 선택을 적시에 제공. https://tammist.tistory.com/46

yeji KimSep 19

.

모델 용량 - 10억으로 나눈 뒤 데이터 타입 바이트 수 곱하기 e.g. 7B 모델 16비트(2바이트) → 7*2=14GB from llama_index.core import Document, VectorStoreIndex 상호 순위 조합 구현하기 BM25와 의미 벡터 검색 방법 벡터 데이터베이스 벡터 라이브러리 - Faiss, Annoy, NMSLIB, ScaNN 벡터 전용 데이터베이스 - pinecone, weaviate, milvus, chroma, qdrant, vespa 벡터 기능 추가 데이터베이스 - elasticSearch, PostgreSQL, MongoDB, Neo4j MLOps 데이터 준비 → 모델 학습 → (모델 저장소 ↔ 모델 평가) → 모델 배포 → 모니터링 → 재학습 ... 멀티 에이전트 - AutoGen, MetaGPT, CrewAI 사용자 맞춤형 정보