Karibu kwenye Somo la 2 la MySQL Database Full Course kwa Kiswahili.

Katika somo la kwanza tulijifunza maana ya Database, MySQL, SQL, Table, Column, Row na CRUD.

Sasa tunaingia hatua inayofuata: kuelewa database inavyopanga data na jinsi tables zinavyohusiana.

Hili ni somo muhimu sana kwa sababu ukielewa relationships vizuri, utaweza kutengeneza database zenye mpangilio mzuri na zinazoweza kukua bila matatizo.

Malengo ya Somo

Baada ya kumaliza somo hili utaweza kuelewa:

• Entity ni nini
• Attribute ni nini
• Record ni nini
• Relational Database ni nini
• Primary Key ni nini
• Foreign Key ni nini
• Relationship kati ya tables
• Kwa nini hatupaswi kuhifadhi kila kitu kwenye table moja
• One-to-One ni nini
• One-to-Many ni nini
• Many-to-Many ni nini
• Jinsi ya kuanza kufikiria Database Design

1. Database Inavyofanya Kazi

Database huhifadhi data kwa mpangilio.

Kwa relational database kama MySQL, data nyingi huhifadhiwa kwenye tables.

Mfano tunaweza kuwa na database:

school_management

Ndani yake tuna tables:

students
classes
subjects
teachers
marks

Kila table ina kazi yake.

Mfano:

students
↓
Inahifadhi wanafunzi

classes
↓
Inahifadhi madarasa

subjects
↓
Inahifadhi masomo

marks
↓
Inahifadhi alama

Huu ndio mwanzo wa database yenye muundo mzuri.

2. Entity ni Nini?

Entity ni kitu halisi tunachotaka kuhifadhi taarifa zake kwenye database.

Mfano kwenye mfumo wa shule tunaweza kuwa na entities kama:

Student
Teacher
Class
Subject
School
Exam

Kwa hiyo:

Student ni entity.

Teacher ni entity.

Subject ni entity.

Mara nyingi entity moja inaweza kuwakilishwa na table moja.

Mfano:

Entity: Student

Table: students

3. Attribute ni Nini?

Attribute ni sifa au taarifa inayohusu entity.

Mfano entity ni:

Student

Attributes zake zinaweza kuwa:

student_name
gender
date_of_birth
phone_number
address

Kwa database, attributes mara nyingi huwa columns.

Mfano:

students
│
├── id
├── student_name
├── gender
├── date_of_birth
└── phone_number

Kwa hiyo:

Entity
↓
Table

Attribute
↓
Column

4. Record ni Nini?

Record ni taarifa kamili ya entity moja.

Mfano:

id: 1
student_name: Asha Mohamed
gender: Female
phone_number: 0712345678

Hii ni record moja ya mwanafunzi.

Kwenye table inaweza kuonekana hivi:

+----+---------------+--------+--------------+
| id | student_name  | gender | phone_number |
+----+---------------+--------+--------------+
| 1  | Asha Mohamed  | Female | 0712345678   |
+----+---------------+--------+--------------+

5. Relational Database ni Nini?

Relational Database ni database inayohifadhi data kwenye tables zinazoweza kuwa na mahusiano.

Mfano tuna tables mbili:

students
classes

Mwanafunzi anahitaji kuwa kwenye darasa fulani.

Badala ya kuandika jina la darasa kila mara, tunaweza kuunganisha students na classes.

Mfano:

classes

+----+-----------+
| id | class_name|
+----+-----------+
| 1  | Form One  |
| 2  | Form Two  |
+----+-----------+

Kisha:

students

+----+---------------+----------+
| id | student_name  | class_id |
+----+---------------+----------+
| 1  | Asha Mohamed  | 1        |
| 2  | John Peter    | 2        |
+----+---------------+----------+

class_id inatuambia mwanafunzi yupo kwenye darasa gani.

Huo ndio mwanzo wa relationship.

6. Primary Key ni Nini?

Primary Key ni column inayotumika kutambua record moja uniquely.

Mfano:

students

id
student_name
gender

Column ya:

id

inaweza kuwa Primary Key.

Mfano:

+----+---------------+
| id | student_name  |
+----+---------------+
| 1  | Asha Mohamed  |
| 2  | John Peter    |
| 3  | Neema Joseph  |
+----+---------------+

Hapa kila mwanafunzi ana ID yake tofauti.

Hatupaswi kuwa na:

id = 1

kwa wanafunzi wawili tofauti.

7. Kwa Nini Primary Key ni Muhimu?

Primary Key inatusaidia:

• Kutambua record moja
• Kuzuia records kuchanganyika
• Kuunganisha tables
• Kufanya UPDATE kwa record sahihi
• Kufanya DELETE kwa record sahihi
• Kutengeneza relationships

Mfano:

UPDATE students
SET student_name = 'Asha Juma'
WHERE id = 1;

id = 1 inatusaidia kujua record gani ibadilishwe.

8. Foreign Key ni Nini?

Foreign Key ni column inayotumika kuunganisha table moja na table nyingine.

Mfano tuna:

classes

na:

students

Table ya classes:

+----+------------+
| id | class_name |
+----+------------+
| 1  | Form One   |
| 2  | Form Two   |
+----+------------+

Table ya students:

+----+---------------+----------+
| id | student_name  | class_id |
+----+---------------+----------+
| 1  | Asha Mohamed  | 1        |
| 2  | John Peter    | 2        |
+----+---------------+----------+

class_id kwenye students inaweza kuwa Foreign Key inayorejea:

classes.id

Kwa hiyo:

students.class_id
        ↓
classes.id

9. Primary Key na Foreign Key Tofauti Yake

Kumbuka hivi:

Primary Key
↓
Inatambulisha record ndani ya table yake

Foreign Key
↓
Inaunganisha record na table nyingine

Mfano:

classes.id

ni Primary Key.

Lakini:

students.class_id

ni Foreign Key.

10. Kwa Nini Tusiweke Kila Kitu Kwenye Table Moja?

Fikiria tuna table hii:

students

ikiwa na:

student_name
class_name
class_teacher
subject_name
subject_teacher
exam_name
marks

Tatizo ni kwamba data itajirudia sana.

Mfano:

Asha | Form One | Mr John | Mathematics | Mr Peter | Midterm | 80
Asha | Form One | Mr John | English     | Ms Neema | Midterm | 75
Asha | Form One | Mr John | Science     | Mr Musa  | Midterm | 68

Asha, Form One, na Mr John zinajirudia bila sababu.

Hii inaweza kusababisha:

• Data duplication
• Database kuwa kubwa bila sababu
• Errors wakati wa update
• Data kutokuwa consistent
• Queries kuwa ngumu

Suluhisho ni kugawanya data kwenye tables.

11. Mfano wa Muundo Bora

Badala ya table moja kubwa, tunaweza kuwa na:

students
classes
subjects
teachers
exams
marks

Mfano:

students
+----+---------------+----------+
| id | student_name  | class_id |
+----+---------------+----------+

classes
+----+------------+
| id | class_name |
+----+------------+

subjects
+----+--------------+
| id | subject_name |
+----+--------------+

marks
+----+------------+------------+-------+
| id | student_id | subject_id | marks |
+----+------------+------------+-------+

Hii ni structure nzuri zaidi.

12. Relationship ni Nini?

Relationship ni uhusiano kati ya records katika tables tofauti.

Kuna aina kuu tatu:

One-to-One
One-to-Many
Many-to-Many

Tutazisoma kwa undani zaidi baadaye, lakini hapa tunaanza kuzitambua.

13. One-to-One Relationship

One-to-One maana yake:

Record moja katika table A inahusiana na record moja katika table B.

Mfano:

User
↓
Profile

User mmoja ana profile moja.

Na profile moja ni ya user mmoja.

Mfano:

users
+----+----------+
| id | username |
+----+----------+

profiles
+----+---------+-------------+
| id | user_id | phone       |
+----+---------+-------------+

Hapa:

users.id
↓
profiles.user_id

14. One-to-Many Relationship

Hii ndiyo relationship utakayokutana nayo mara nyingi sana.

Mfano:

Class
↓
Students

Class moja inaweza kuwa na wanafunzi wengi.

Lakini mwanafunzi mmoja anaweza kuwa kwenye class moja kwa wakati fulani.

Mfano:

classes
+----+------------+
| id | class_name |
+----+------------+
| 1  | Form One   |
+----+------------+

Students:

+----+---------------+----------+
| id | student_name  | class_id |
+----+---------------+----------+
| 1  | Asha          | 1        |
| 2  | John          | 1        |
| 3  | Neema         | 1        |
+----+---------------+----------+

Class 1 ina students wengi.

Kwa hiyo:

ONE CLASS
    ↓
MANY STUDENTS

Hii ni One-to-Many.

15. Many-to-Many Relationship

Many-to-Many hutokea pale ambapo records nyingi katika table moja zinaweza kuhusiana na records nyingi katika table nyingine.

Mfano:

Students
↕
Subjects

Student mmoja anaweza kusoma subjects nyingi.

Subject moja inaweza kusomwa na students wengi.

Kwa hiyo:

Many Students
↕
Many Subjects

Hii ni Many-to-Many.

16. Tatizo la Many-to-Many

Hatuwezi kawaida kuweka relationship ya Many-to-Many moja kwa moja.

Tunatumia table ya kati.

Mfano:

students
student_subjects
subjects

Muundo unaweza kuwa:

students
+----+--------------+
| id | student_name |
+----+--------------+

subjects
+----+--------------+
| id | subject_name |
+----+--------------+

student_subjects
+----+------------+------------+
| id | student_id | subject_id |
+----+------------+------------+

Table ya:

student_subjects

inaunganisha students na subjects.

Hii huitwa:

• Junction Table
• Bridge Table
• Associative Table
• Pivot Table

Majina yanaweza kutofautiana lakini concept ni ileile.

17. Mfano wa Student na Subject

Tuna:

students

1 | Asha
2 | John

Subjects:

subjects

1 | Mathematics
2 | English
3 | Biology

Table ya kati:

student_subjects

student_id | subject_id
-----------+-----------
1          | 1
1          | 2
1          | 3
2          | 1
2          | 2

Hii ina maana:

Asha anasoma:

Mathematics
English
Biology

John anasoma:

Mathematics
English

18. Referential Integrity ni Nini?

Referential Integrity ni kuhakikisha relationships kati ya tables zinabaki sahihi.

Mfano:

students.class_id = 5

lakini table ya classes haina:

id = 5

Hii inaweza kuwa data isiyo sahihi.

Foreign Keys zinaweza kusaidia database kuzuia hali kama hiyo.

Mfano:

class_id = 2

inapaswa kurejea class halisi yenye:

id = 2

19. Mfano wa Database ya Shule

Tunaweza kuanza kufikiria structure kama hii:

schools
   ↓
classes
   ↓
students

Halafu:

subjects
   ↓
marks
   ↑
students

Pia:

exams
   ↓
marks

Kwa hiyo table ya marks inaweza kuhusiana na:

student
subject
exam

20. Mfano wa Marks Table

Table inaweza kuwa:

marks

+----+------------+------------+---------+-------+
| id | student_id | subject_id | exam_id | marks |
+----+------------+------------+---------+-------+

Hapa tuna Foreign Keys tatu:

student_id
subject_id
exam_id

Zinaweza kurejea:

student_id
↓
students.id

subject_id
↓
subjects.id

exam_id
↓
exams.id

Hii ndiyo nguvu ya relational database.

21. Database Design Inaanza na Requirements

Kabla ya kuandika SQL, developer anapaswa kujua mfumo unatakiwa kufanya nini.

Mfano mteja anasema:

Nataka mfumo wa kusajili schools, classes, students, subjects na marks.

Developer anaweza kuanza kuona entities:

School
Class
Student
Subject
Mark

Kisha kugeuza entities kuwa tables:

schools
classes
students
subjects
marks

Baadaye tunaamua columns na relationships.

22. Mfano wa Requirements

Tuna requirements hizi:

School inaweza kuwa na classes nyingi.

Class inaweza kuwa na students wengi.

Student anaweza kusoma subjects nyingi.

Subject inaweza kusomwa na students wengi.

Student anaweza kupata mark kwa subject fulani kwenye exam fulani.

Tunaweza kuanza kupata relationships:

School
1
↓
Many Classes

Class
1
↓
Many Students

Students
Many
↕
Many
Subjects

23. Database Diagram Rahisi

Structure inaweza kuonekana hivi:

SCHOOLS
   |
   | 1
   |
   | Many
   ↓
CLASSES
   |
   | 1
   |
   | Many
   ↓
STUDENTS
   |
   |
   ↓
MARKS
   ↑
   |
SUBJECTS
   ↑
   |
EXAMS

Kadri tunavyoendelea tutajifunza kutengeneza ER Diagram vizuri zaidi.

24. Mfano Mbaya wa Database Design

Mfano usio mzuri:

students

id
student_name
class_name
school_name
subject1
subject2
subject3
subject4
subject5
teacher1
teacher2
marks1
marks2
marks3

Hii inaweza kufanya mfumo kuwa mgumu sana kusimamia.

Kwa nini?

Kwa sababu ukiongeza subject mpya unaweza kulazimika kuongeza column mpya.

Mfano:

subject6
marks6
teacher6

Hii si scalable.

25. Mfano Bora

Badala yake:

students
subjects
teachers
student_subjects
marks

Unaweza kuongeza subjects 10, 100 au 1,000 bila kubadilisha structure ya students.

Unaongeza records mpya tu.

Hii ndiyo database design nzuri.

26. Scalability ni Nini?

Scalability ni uwezo wa mfumo kukua bila structure yake kuvunjika au kuwa ngumu sana kusimamia.

Mfano mfumo unaweza kuanza na:

100 students

baadaye ukawa:

10,000 students

Database iliyopangwa vizuri inaweza kukabiliana vizuri zaidi na ukuaji huo.

27. Data Duplication

Data duplication maana yake ni data moja kujirudia mara nyingi bila sababu.

Mfano:

Asha | Form One
John | Form One
Neema | Form One
Peter | Form One

Jina:

Form One

linajirudia.

Kwa database ndogo inaweza kuonekana hakuna tatizo.

Lakini kwa system kubwa inaweza kusababisha inconsistencies.

Mfano mtu anaandika:

Form One
Form 1
FORM ONE
Form-One

Sasa database ina majina manne yanayomaanisha kitu kimoja.

28. Kutumia ID Badala ya Kurudia Data

Tunaweza kuwa na:

classes

1 | Form One

Kisha students wanahifadhi:

class_id = 1

Mfano:

Asha  | 1
John  | 1
Neema | 1
Peter | 1

Hapa tukibadilisha jina la class kuwa:

Form I

tunabadilisha record moja tu kwenye table ya classes.

Hii ni faida kubwa ya relational database.

29. Unique Identifier

Record nyingi zinahitaji identifier ya kipekee.

Mfano students wawili wanaweza kuwa na jina sawa:

John Peter
John Peter

Hatuwezi kutumia jina pekee kutambua mwanafunzi.

Lakini tunaweza kuwa na:

1 | John Peter
2 | John Peter

ID zinawatofautisha.

Ndiyo maana Primary Key ni muhimu.

30. Kumbuka Hii

Database nzuri si database yenye tables chache.

Database nzuri ni ile ambayo:

Data imepangwa vizuri
Relationships ziko wazi
Duplication imepunguzwa
Records zinatambulika vizuri
Data integrity inalindwa
Mfumo unaweza kukua

31. Terms Muhimu za Somo

Entity
Kitu tunachotaka kuhifadhi taarifa zake.

Attribute
Sifa ya entity.

Record
Taarifa za entity moja.

Relational Database
Database yenye tables zinazohusiana.

Primary Key
Column inayotambulisha record uniquely.

Foreign Key
Column inayounganisha table na table nyingine.

Relationship
Uhusiano kati ya tables.

One-to-One
Record moja inahusiana na record moja.

One-to-Many
Record moja inahusiana na records nyingi.

Many-to-Many
Records nyingi zinahusiana na records nyingi.

Junction Table
Table inayosaidia kuunganisha Many-to-Many relationship.

32. Maswali ya Kujipima

Swali la 1

Entity ni nini?

Swali la 2

Attribute ni nini?

Swali la 3

Primary Key ni nini?

Swali la 4

Foreign Key ni nini?

Swali la 5

Kuna tofauti gani kati ya Primary Key na Foreign Key?

Swali la 6

Relational Database ni nini?

Swali la 7

Relationship ya class moja yenye students wengi ni aina gani?

Swali la 8

Student wengi wanaweza kusoma subjects nyingi. Hii ni relationship gani?

Swali la 9

Kwa nini si vizuri kuweka taarifa zote za mfumo kwenye table moja?

Swali la 10

Junction Table hutumika kufanya nini?

33. Zoezi la Vitendo

Fikiria unataka kutengeneza mfumo wa:

Online Shop

Una entities hizi:

Customers
Products
Orders
Categories

Jaribu kujibu:

1. Customer mmoja anaweza kuwa na orders ngapi?
2. Category moja inaweza kuwa na products ngapi?
3. Order moja inaweza kuwa na products nyingi?
4. Product moja inaweza kuwepo kwenye orders nyingi?
5. Ni sehemu gani inaweza kuhitaji Many-to-Many relationship?

Jaribu pia kuchora structure yako mwenyewe:

customers
    ↓
orders
    ↓
?
    ↑
products
    ↑
categories

Katika masomo yajayo utaweza kujibu na kutengeneza structure hii vizuri zaidi kwa SQL.

Hitimisho

Katika Somo la 2 tumejifunza kwamba relational database haimaanishi tu kuhifadhi data kwenye tables.

Muhimu zaidi ni:

TABLES
   ↓
RELATIONSHIPS
   ↓
DATA INTEGRITY

Tumejifunza:

Primary Key
Foreign Key
One-to-One
One-to-Many
Many-to-Many
Junction Table

Concept hizi zitatumika karibu katika kila project kubwa ya MySQL.

Usiwe na haraka ya kukariri SQL commands kabla hujaelewa logic hii.

Ukiielewa vizuri Database Design, SQL itakuwa rahisi zaidi.

Somo Linalofuata

Somo la 3: Jinsi ya Ku-install XAMPP, MySQL na Kutumia phpMyAdmin

Katika Somo la 3 tutaanza maandalizi ya kufanya mazoezi ya MySQL kwenye computer.

Tutajifunza:

• XAMPP ni nini
• MySQL Server ni nini
• phpMyAdmin ni nini
• Localhost ni nini
• Jinsi PHP, Apache na MySQL zinavyohusiana
• Jinsi ya kufungua XAMPP Control Panel
• Jinsi ya kuwasha Apache na MySQL
• Jinsi ya kufungua phpMyAdmin
• Jinsi ya kuthibitisha kuwa MySQL inafanya kazi

Baada ya hapo tutaanza kuandika SQL kwa vitendo.