Database Normalization
Definition:
It is an important process to organise database schema with a set of database design model.
Benefit:
- Reduce data redundancy.
- Reduce anomalies.
- Standardize database schema.
- Reduce data retrieval(i.e. SELECT) performance because of the need of joining tables.
- End-user may have difficulties to learn and use normalized database.
Example 1:
0NF Rules:
No rules.CLUB_ENROL
| Field name | Description |
|---|---|
| SID | Student ID |
| SNAME | Student name |
| CLASS | Student's class |
| CID | Enrol club(s) ID |
| CNAME1 | First club's name |
| CNAME2 | Second club's name |
| TIC_TID | Enrol club(s) teacher-in-charge ID |
| ENROL_DATE | Date of enrol club(s) |
| CHECKBY_TID | Teacher ID who checks this enrolment |
| CHECKBY_TNAME | Teacher name who checks this enrolment |
Primary key:SID+CID
Table CLUB_ENROL
| SID | SNAME | CLASS | CID | CNAME1 | CNAME2 | TIC_TID | ENROL_DATE | CHECKBY_TID | CHECKBY_TNAME |
|---|---|---|---|---|---|---|---|---|---|
| 000001 | Abby Chan | 1A | A01,B02 | Dance Club | Drama Club | 0004,0015 | 02-12-2014,19-12-2014 | 0003 | Lily Ng |
| 000002 | Ada Chan | 1A | B02 | Drama Club | 0015 | 16-12-2014 | 0002 | Ken Yan | |
| 010122 | Chris Wong | 1A | D18 | IT Club | 0002 | 07-11-2014 | 0006 | Jane Au | |
| 010142 | Nico Lin | 1C | G30,A01 | Chess Club | Dance Club | 0001,0004 | 21-12-2014,15-10-2013 | 0006 | Jane Au |
1NF Rules:
- All attributes are single-valued.
(No multiple values can be stored in one cell.) - No repeated attributes can be under the table.
(e.g. Both CNAME1 and CNAME2 are club's name.
So,new records should be made to remove repeated attributes.)
CLUB_ENROL
| Field name | Description |
|---|---|
| SID | Student ID |
| SNAME | Student name |
| CLASS | Student's class |
| CID | Enrol club ID |
| CNAME | Enrol club's name |
| TIC_TID | Enrol club teacher-in-charge ID |
| ENROL_DATE | Date of enrol club(s) |
| CHECKBY_TID | Teacher ID who checks this enrolment |
| CHECKBY_TNAME | Teacher name who checks this enrolment |
Primary key:SID+CID
Table CLUB_ENROL
| SID | SNAME | CLASS | CID | CNAME | TIC_TID | ENROL_DATE | CHECKBY_TID | CHECKBY_TNAME |
|---|---|---|---|---|---|---|---|---|
| 000001 | Abby Chan | 1A | A01 | Dance Club | 0004 | 02-12-2014 | 0003 | Lily Ng |
| 000001 | Abby Chan | 1A | B02 | Drama Club | 0015 | 19-12-2014 | 0003 | Lily Ng |
| 000002 | Ada Chan | 1A | B02 | Drama Club | 0015 | 16-12-2014 | 0002 | Ken Yan |
| 010122 | Chris Wong | 1A | D18 | IT Club | 0002 | 07-11-2014 | 0006 | Jane Au |
| 010142 | Nico Lin | 1C | G30 | Chess Club | 0001 | 21-12-2014 | 0006 | Jane Au |
| 010142 | Nico Lin | 1C | A01 | Dance Club | 0004 | 15-10-2013 | 0006 | Jane Au |
2NF Rules:
- (1NF)All attributes are single-valued.
(No multiple values can be stored in one cell.) - (1NF)No repeated attributes can be under the table.
(e.g. Both CNAME1 and CNAME2 are club's name.
So,new records should be made to remove repeated attributes.) - No partial dependency can be occured.
(e.g. SNAME and CLASS depend on composite key SID.
CNAME and TIC_TID depend on composite key CID.
So, SNAME,CLASS,CNAME and TIC_TID should be removed.)
CLUB_ENROL
| Field name | Description |
|---|---|
| SID | Student ID |
| CID | Enrol club ID |
| ENROL_DATE | Date of enrol club(s) |
| CHECKBY_TID | Teacher ID who checks this enrolment |
| CHECKBY_TNAME | Teacher name who checks this enrolment |
Primary key:SID+CID
Foreign key:SID REFERENCES STUDENT(SID)
CID REFERENCES CLUB(CID)
Table CLUB_ENROL
| SID | CID | ENROL_DATE | CHECKBY_TID | CHECKBY_TNAME |
|---|---|---|---|---|
| 000001 | A01 | 02-12-2014 | 0003 | Lily Ng |
| 000001 | B02 | 19-12-2014 | 0003 | Lily Ng |
| 000002 | B02 | 16-12-2014 | 0002 | Ken Yan |
| 010122 | D18 | 07-11-2014 | 0006 | Jane Au |
| 010142 | G30 | 21-12-2014 | 0006 | Jane Au |
| 010142 | A01 | 15-10-2013 | 0006 | Jane Au |
3NF Rules:
- (1NF)All attributes are single-valued.
(No multiple values can be stored in one cell.) - (1NF)No repeated attributes can be under the table.
(e.g. Both CNAME1 and CNAME2 are club's name.
So,new records should be made to remove repeated attributes.) - (2NF)No partial dependency can be occured.
(e.g. SNAME and CLASS depend on composite key SID.
CNAME and TIC_TID depend on composite key CID.
So, SNAME,CLASS,CNAME and TIC_TID should be removed.) - No transitive dependency can be occured.
(e.g. Both CHECKBY_TID and CHECKBY_TNAME are non-candidate key attributes,
and CHECKBY_TNAME depends on CHECKBY_TID.
So, CHECKBY_TNAME should be removed.)
CLUB_ENROL
| Field name | Description |
|---|---|
| SID | Student ID |
| CID | Enrol club ID |
| ENROL_DATE | Date of enrol club(s) |
| CHECKBY_TID | Teacher ID who checks this enrolment |
Primary key:SID+CID
Foreign key:SID REFERENCES STUDENT(SID)
CID REFERENCES CLUB(CID)
Table CLUB_ENROL
| SID | CID | ENROL_DATE | CHECKBY_TID |
|---|---|---|---|
| 000001 | A01 | 02-12-2014 | 0003 |
| 000001 | B02 | 19-12-2014 | 0003 |
| 000002 | B02 | 16-12-2014 | 0002 |
| 010122 | D18 | 07-11-2014 | 0006 |
| 010142 | G30 | 21-12-2014 | 0006 |
| 010142 | A01 | 15-10-2013 | 0006 |
Example 2:
1NF Rules:
- All attributes are single-valued.
(No multiple values can be stored in one cell.) - No repeated attributes can be under the table.
STUDENT
| Field name | Description |
|---|---|
| SID | Student ID |
| SNAME | Student name |
| CLASS | Student's class |
| CLASSROOM | Classroom |
| CTEACHER_TID | Class teacher ID |
| CNO | Student's class no. |
| SEX | Sex |
| DOB | Date of birth |
Primary key:SID
Foreign key:CTEACHER_TID REFERENCES TEACHER(TID)
Table STUDENT
| SID | SNAME | CLASS | CLASSROOM | CTEACHER_TID | CNO | SEX | DOB |
|---|---|---|---|---|---|---|---|
| 000001 | Abby Chan | 1A | 101 | 0003 | 1 | F | 2000-01-01 |
| 000002 | Ada Chan | 1A | 101 | 0003 | 2 | F | 2000-02-09 |
| 010122 | Chris Wong | 1A | 101 | 0003 | 5 | F | 2011-02-01 |
| 010142 | Nico Lin | 1C | 202 | 0001 | 7 | M | 2011-08-21 |
| 010133 | Amy Chan | 2D | 112 | 0002 | 12 | F | 2010-03-06 |
| 010001 | Chan Tai Man | 2B | 304 | 0015 | 20 | M | 2005-07-31 |
3NF Rules:
- (1NF)All attributes are single-valued.
(No multiple values can be stored in one cell.) - (1NF)No repeated attributes can be under the table.
- (2NF)No partial dependency can be occured.
- No transitive dependency can be occured.
STUDENT
| Field name | Description |
|---|---|
| SID | Student ID |
| SNAME | Student name |
| CLASS | Student's class |
| CNO | Student's class no. |
| SEX | Sex |
| DOB | Date of birth |
Primary key:SID
Foreign key:CLASS REFERENCES CLASS(CLASS)
CLASS
| Field name | Description |
|---|---|
| CLASS | Class name |
| CLASSROOM | Classroom |
| CTEACHER_TID | Class teacher ID |
Primary key:CLASS
Foreign key:
CTEACHER_TID REFERENCES TEACHER(TID)
Table STUDENT
| SID | SNAME | CLASS | CNO | SEX | DOB |
|---|---|---|---|---|---|
| 000001 | Abby Chan | 1A | 1 | F | 2000-01-01 |
| 000002 | Ada Chan | 1A | 2 | F | 2000-02-09 |
| 010122 | Chris Wong | 1A | 5 | F | 2011-02-01 |
| 010142 | Nico Lin | 1C | 7 | M | 2011-08-21 |
| 010133 | Amy Chan | 2D | 12 | F | 2010-03-06 |
| 010001 | Chan Tai Man | 2B | 20 | M | 2005-07-31 |
Table CLASS
| CLASS | CLASSROOM | CTEACHER_TID |
|---|---|---|
| 1A | 101 | 0003 |
| 1C | 202 | 0001 |
| 2D | 112 | 0002 |
| 2B | 304 | 0015 |
‘It's hard to classify 2NF and 3NF.’By secondary 6 student
Comments
Post a Comment