Study_Materials_Exchange_Task09_Presentation

1.

TEAM PRESENTATION
WEEK 9 CHECKPOINT
STUDY MATERIALS
EXCHANGE
users
Task 9 / Database Check-up
One upload workflow. Four connected tables.
Real SQL evidence. Clear next steps.
Ibrashov Anuar · Kozhakhmetov Talgat · Abylay Daryn
materials
courses
categories
Anuarbek Alibek · Ainakul Arnur
github.com/ibrashov/study-materials-exchange
TASK 09 / BASICS OF INFORMATION SYSTEMS
01

2.

01 / IBRASHOV ANUAR
MAIN ACTION
One action connects the whole project
Digitize and upload a study material — the central workflow carried forward from previous tasks.
01
02
→
Log in
Authenticated
student
03
Select file
Notes or slides
→
04
Add metadata
Title, category, course
→
Validate & save
05
→
File + database record
Find material
Search in one place
ONE CONSISTENT PROJECT STORY
TASK 4
Requirements
TASK 5
Hardware workflow
TASK 6
Backup decision
TASK 7
Data quality
Current scope: database prototype — not a completed upload website.
TASK 09 / BASICS OF INFORMATION SYSTEMS
02

3.

01 / IBRASHOV ANUAR
DATA & TABLES
Four tables, one upload record
Each entity has its own purpose; all four tables contain at least four typed fields.
USERS
user_id
Student identities
INTEGER
full_name
email
[PK]
TEXT
TEXT
password_hash
[UNIQUE]
TEXT
·
role
COURSES
course_id
TEXT
University subjects
INTEGER
course_name
TEXT
course_code
TEXT
description
TEXT
[PK]
CATEGORIES
Material types
category_id
INTEGER
[PK]
category_name
TEXT
[UNIQUE]
description
TEXT
created_at
TEXT
MATERIALS
material_id
File metadata + connections
INTEGER
[PK]
uploader_id / category_id / course_id
[UNIQUE]
title / file_path
file_size_bytes
INTEGER
[FK]
TEXT
INTEGER
·
uploaded_at
TEXT
Design principle: materials stores IDs and file metadata — not repeated user emails or course names.
TASK 09 / BASICS OF INFORMATION SYSTEMS
03

4.

02 / KOZHAKHMETOV TALGAT
PRIMARY KEYS
Stable IDs and data rules protect each record
SQLite uses a unique, non-empty integer primary key in every table.
PRIMARY KEY
PRIMARY KEY
user_id
category_id
PRIMARY KEY
course_id
PRIMARY KEY
material_id
DATABASE VALIDATION RULES
UNIQUE
email, category_name, course_code
Prevents duplicate business identifiers
NOT NULL
required IDs and key material details
Prevents missing essential information
CHECK
nonblank title; valid student/admin role
Rejects invalid values
RANGE
1–52,428,800 bytes per material
Enforces the metadata size limit
TASK 09 / BASICS OF INFORMATION SYSTEMS
04

5.

03 / ABYLAY DARYN
FOREIGN KEYS
Foreign keys connect a material to real records
Three 1:M relationships prevent metadata from pointing to missing users, categories, or courses.
USERS
1:M
user_id = 1
CATEGORIES
Example Student
1:M
category_id = 1
COURSES
uploader_id = 1
category_id = 1
Notes
1:M
course_id = 1
course_id = 1
Introduction to Programming
EXAMPLE: materials.material_id = 1 → student 1 · Notes · Introduction to Programming
Referential integrity: PRAGMA foreign_keys = ON (on every SQLite connection).
TASK 09 / BASICS OF INFORMATION SYSTEMS
05

6.

ENTITY RELATIONSHIP DIAGRAM
04 / ANUARBEK ALIBEK
Our ERD matches the SQL schema
One student, category or course may connect to many materials; each material has one of each.
Relationship type: 1:M • No many-to-many bridge table is needed in this initial model.
TASK 09 / BASICS OF INFORMATION SYSTEMS
06

7.

04 / ANUARBEK ALIBEK
NORMALIZATION
Store each fact once — not in every upload
Avoid repeating account and course details in hundreds of material records.
BEFORE / REPEATED DATA
AFTER / CONNECTED TABLES
material_id
email
category
1
student@example.test
Notes
2
student@example.test
Notes
Same email and category repeated in multiple material
rows.
→
users
user_id
categories
category_id
courses
course_id
course_name
materials
material_id
email
name
three FK IDs
One update to users.email or categories.category_name updates the shared source — not every upload.
Future improvement: add real file integrity checks and evaluate indexes once search is implemented.
TASK 09 / BASICS OF INFORMATION SYSTEMS
07

8.

05 / AINAKUL ARNUR
SQL & BUSINESS USE
SQL turns records into a useful decision
Question: Which courses currently have few or no materials?
database/queries.sql
VERIFIED SAMPLE RESULT
SELECT c.course_name,
COUNT(m.material_id) AS material_count
FROM courses AS c
LEFT JOIN materials AS m
ON m.course_id = c.course_id
GROUP BY c.course_id, c.course_name
ORDER BY material_count, c.course_name;
Database Systems
0
Introduction to Programming
1
Fictional dataset · SQLite unit-tested
DATA
INFORMATION
DECISION
Material and course records
Count per course
Encourage materials for empty courses
TASK 09 / BASICS OF INFORMATION SYSTEMS
08

9.

05 / AINAKUL ARNUR
SECURITY & TRANSACTIONS
Control access and handle partial uploads safely
Planned application controls — distinct from the database checks already tested.
GUEST
STUDENT
ADMIN
View & search
Upload after login
Manage & review
No upload permission
Own material metadata
Moderation / reports
PLANNED MULTI-STEP UPLOAD
1
Authenticate
IF A STEP FAILS
→
2
Validate
→
3
Store temporary
file
→
4
Save
metadata
→
5
Finalize
Roll back the database change AND remove incomplete files.
Sensitive information such as password hashes must not be visible to guests. Full authentication and file handling are not implemented yet.
TASK 09 / BASICS OF INFORMATION SYSTEMS
09

10.

TEAM CHECKPOINT
ACTUAL PROGRESS
A working database prototype — not a finished website
Evidence from the shared GitHub repository and executed SQLite tests.
4
3
7/7
4
CONNECTED TABLES
FOREIGN KEYS
TESTS PASSED
INITIAL COMMITS
VERIFIED IN GITHUB
database/schema.sql
database/ERD.md
database/sample_data.sql
STILL IN DEVELOPMENT
• Login and authorization
• Real file upload / download
database/queries.sql
• Search interface and performance
tests/test_database.py
• Admin panel and backups
github.com/ibrashov/study-materials-exchange
TASK 09 / BASICS OF INFORMATION SYSTEMS
10

11.

ALL FIVE MEMBERS
TEAM PLAN
One real risk. One practical plan.
Database design is clear; keeping file storage and metadata consistent is the next engineering challenge.
CURRENT PROBLEM
PROPOSED SOLUTION
A material record may exist
without a successful file upload.
Verify file storage before finalizing the record.
On failure: roll back + clean up incomplete files.
NEXT STEPS / RESPONSIBILITIES
TALGAT
DARYN
ARNUR
ALIBEK
ANUAR
Backend + integrity
File storage + search
Test failures + admin
Upload UI + language
Requirements +
coordination
Target: before the next project checkpoint
TASK 09 / BASICS OF INFORMATION SYSTEMS
·
Current project status: initial database tested, integration pending
11

12.

STUDY MATERIALS EXCHANGE
CHECKPOINT SUMMARY
DATABASE: DESIGNED.
PROTOTYPE: TESTED.
INTEGRATION: NEXT.
ANUAR
TALGAT
DARYN
ALIBEK
ARNUR
Main action & tables
Primary keys
Foreign keys
ERD & normalization
SQL & security
Project repository
github.com/ibrashov/study-materials-exchange
TASK 09 / BASICS OF INFORMATION SYSTEMS
12