Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Computer Networking, Database Normalization, Routing, Schemes and Mind Maps of Software Engineering

Computer Networking, Database Normalization, Routing

Typology: Schemes and Mind Maps

2020/2021

Uploaded on 10/04/2021

berhane-kifle-1
berhane-kifle-1 🇺🇸

1 document

1 / 7

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Chapter 12 Normalization
ADRIENNE WATT
Normalization should be part of the database design process. However, it is difficult to separate the
normalization process from the ER modeling process so the two techniques should be used concurrently.
Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization’s data
requirements and operations. This is created through an iterative process that involves identifying relevant
entities, their attributes and their relationships.
Normalization procedure focuses on characteristics of specific entities and represents the micro view of
entities within the ERD.
What Is Normalization?
Normalization%is the branch of relational theory that provides design insights. It is the process of
determining how much redundancy exists in a table. The goals of normalization are to:
Be able to characterize the level of redundancy in a relational schema
Provide mechanisms for transforming schemas in order to remove redundancy
Normalization theory draws heavily on the theory of functional dependencies. Normalization theory
defines six normal forms (NF). Each normal form involves a set of dependency properties that a schema
must satisfy and each normal form%gives guarantees about the presence and/or absence of update
anomalies. This means that higher normal forms have less redundancy, and as a result, fewer update
problems.
Normal Forms
All the tables in any database can be in one of the normal forms we will discuss next.% Ideally we only
want minimal redundancy for PK to FK. Everything else should be derived from other tables.% There are
six normal forms, but we will only look at the first four, which are:
First%normal form (1NF)
Second%normal form (2NF)
Third normal form (3NF)
Boyce-Codd normal form (BCNF)
BCNF is rarely used.
First Normal Form (1NF)
In the%first normal form, only single values are permitted at the intersection of each row and column;
hence, there are no repeating groups.
To normalize a relation that contains a repeating group, remove the repeating group and form two new
relations.
The PK%of the new relation is a combination of the PK%of the original relation plus an attribute from the
newly created relation for unique identification.
pf3
pf4
pf5

Partial preview of the text

Download Computer Networking, Database Normalization, Routing and more Schemes and Mind Maps Software Engineering in PDF only on Docsity!

Chapter 12 Normalization ADRIENNE WATT Normalization should be part of the database design process. However, it is difficult to separate the normalization process from the ER modeling process so the two techniques should be used concurrently. Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization’s data requirements and operations. This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships. Normalization procedure focuses on characteristics of specific entities and represents the micro view of entities within the ERD. What Is Normalization? Normalization is the branch of relational theory that provides design insights. It is the process of determining how much redundancy exists in a table. The goals of normalization are to:  Be able to characterize the level of redundancy in a relational schema  Provide mechanisms for transforming schemas in order to remove redundancy Normalization theory draws heavily on the theory of functional dependencies. Normalization theory defines six normal forms (NF). Each normal form involves a set of dependency properties that a schema must satisfy and each normal form gives guarantees about the presence and/or absence of update anomalies. This means that higher normal forms have less redundancy, and as a result, fewer update problems. Normal Forms All the tables in any database can be in one of the normal forms we will discuss next. Ideally we only want minimal redundancy for PK to FK. Everything else should be derived from other tables. There are six normal forms, but we will only look at the first four, which are:  First normal form (1NF)  Second normal form (2NF)  Third normal form (3NF)  Boyce-Codd normal form (BCNF) BCNF is rarely used. First Normal Form (1NF) In the first normal form , only single values are permitted at the intersection of each row and column; hence, there are no repeating groups. To normalize a relation that contains a repeating group, remove the repeating group and form two new relations. The PK of the new relation is a combination of the PK of the original relation plus an attribute from the newly created relation for unique identification.

Process for 1NF We will use the Student_Grade_Report table below, from a School database, as our example to explain the process for 1NF. Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)  In the Student Grade Report table, the repeating group is the course information. A student can take many courses.  Remove the repeating group. In this case, it’s the course information for each student.  Identify the PK for your new table.  The PK must uniquely identify the attribute value (StudentNo and CourseNo).  After removing all the attributes related to the course and student, you are left with the student course table ( StudentCourse ).  The Student table ( Student ) is now in first normal form with the repeating group removed.  The two new tables are shown below. Student (StudentNo, StudentName, Major) StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade) How to update 1NF anomalies StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)  To add a new course, we need a student.  When course information needs to be updated, we may have inconsistencies.  To delete a student, we might also delete critical information about a course. Second Normal Form (2NF) For the second normal form , the relation must first be in 1NF. The relation is automatically in 2NF if, and only if, the PK comprises a single attribute. If the relation has a composite PK, then each non-key attribute must be fully dependent on the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or augmentation). Process for 2NF To move to 2NF, a table must first be in 1NF.  The Student table is already in 2NF because it has a single-column PK.  When examining the Student Course table, we see that not all the attributes are fully dependent on the PK; specifically, all course information. The only attribute that is fully dependent is grade.  Identify the new table that contains the course information.  Identify the PK for the new table.  The three new tables are shown below. Student (StudentNo, StudentName, Major)

 FD: full dependency (Note: FD typically stands for functional dependency. Using FD as an abbreviation for full dependency is only used in Figure 12.1.) Boyce-Codd Normal Form (BCNF) When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd normal form is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key. BCNF Example 1 Consider the following table ( St_Maj_Adv ). Student_id Major Advisor 111 Physics Smith 111 Music Chan 320 Math Dobbs 671 Physics White 803 Physics Smith The semantic rules (business rules applied to the database) for this table are:

  1. Each Student may major in several subjects.
  2. For each Major, a given Student has only one Advisor.
  3. Each Major has several Advisors.
  4. Each Advisor advises only one Major.
  5. Each Advisor advises several Students in one Major. The functional dependencies for this table are listed below. The first one is a candidate key; the second is not.
  6. Student_id, Major ——> Advisor
  7. Advisor ——> Major Anomalies for this table include:
  8. Delete – student deletes advisor info
  9. Insert – a new advisor needs a student
  10. Update – inconsistencies Note : No single attribute is a candidate key. PK can be Student_id, Major or Student_id, Advisor. To reduce the St_Maj_Adv relation to BCNF, you create two new tables:
  11. St_Adv (Student_id, Advisor)
  12. Adv_Maj (Advisor, Major) St_Adv table Student_i Advisor

d 111 Smith 111 Chan 320 Dobbs 671 White 803 Smith Adv_Maj table Advisor Major Smith Physics Chan Music Dobbs Math White Physics BCNF Example 2 Consider the following table ( Client_Interview). ClientNo InterviewDate InterviewTime StaffNo RoomNo CR76 13-May-02 10.30 SG5 G CR56 13-May-02 12.00 SG5 G CR74 13-May-02 12.00 SG37 G CR56 1-July-02 10.30 SG5 G FD1 – ClientNo, InterviewDate –> InterviewTime, StaffNo, RoomNo (PK) FD2 – staffNo, interviewDate, interviewTime –> clientNO (candidate key: CK) FD3 – roomNo, interviewDate, interviewTime –> staffNo, clientNo (CK) FD4 – staffNo, interviewDate –> roomNo A relation is in BCNF if, and only if, every determinant is a candidate key. We need to create a table that incorporates the first three FDs ( Client_Interview2 table) and another table ( StaffRoom table) for the fourth FD. Client_Interview2 table ClientN o InterviewDate InterViewTim e StaffNo CR76 13-May-02 10.30 SG CR56 13-May-02 12.00 SG CR74 13-May-02 12.00 SG CR56 1-July-02 10.30 SG StaffRoom table StaffNo InterviewDate RoomNo SG5 13-May-02 G SG37 13-May-02 G SG5 1-July-02 G Normalization and Database Design

2. Normalize this table to third normal form. State any assumptions.

9. Fill in the blanks:

1. ____________________ produces a lower normal form.

2. Any attribute whose value determines other values within a row is called a(n)

____________________.

3. An attribute that cannot be further divided is said to display

____________________.

4. ____________________ refers to the level of detail represented by the values

stored in a table’s row.

5. A relational table must not contain ____________________ groups.