Download
1 / 16
160 likes | 286 Views
Normalising a database. Create a relational database; with a primary key; and show relationships. Relational databases use the concept of normalisation or Normal forms to define “well-formed” tables. 1. Normal Forms (NF) Rules aimed at eliminating redundancy within the database;
E N D
Normalising a database Create a relational database; with a primary key; and show relationships
Relational databases use the concept of normalisation or • Normal forms to define “well-formed” tables. • 1. Normal Forms (NF) • Rules aimed at eliminating redundancy within the database; • Each table is given a primary key; • Usually results in more tables than would have otherwise been • included; • Results in independent tables that can be edited without • affecting other tables; • There should be nothing common between any of the tables.
2. Domain Key Normal Forms (DKNF) • sets out to establish common ground between the tables so • they can be related; • A field is established that is common to each set of two tables; • The common field is used to link the two tables.
The use of normalisation ensures that there are no problems • When modifying the database: • Each table can be edited and manipulated separately; • Relationships can be established between each table when • data is needed from more than one table. • Example ~ the school database • INF • all fields related to students would become one table: Students • all fields related to teachers would become the second table: Teachers • DKNF • The link between Teachers and Students is Class • A ClassCode filed will be established to allow one table to read the data in the other table; • ClassID may be the primary key of the Students table; • ClassID, containing the exact same data, may be the foreign key of the Teachers table; • The list of students in the teacher’s class can then be accessed through the relationship • Established between the two tables.
An Example ~ a school database • INF • all fields related to students would become one • table: Students • all fields related to teachers would become the second • table: Teachers • DKNF • The link between Teachers and Students is Class; • A ClassCode filed will be established to allow one table • to read the data in the other table; • ClassID may be the primary key of the Students table; • ClassID, containing the exact same data, may be the • foreign key of the Teachers table; • The list of students in the teacher’s class can then be • accessed through the relationship established between • the two tables.
When normalising a database you should achieve four goals: • Arranging data into logical groups such that each group describes a small part of the whole • Minimizing the amount of duplicated data stored in a database • Building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data storage • Organising the data such that, when you modify it, you make the changes in only one place
Normalization is a complex process with many specific rules and different intensity levels. In its full definition, normalization is the process of discarding repeating groups, minimizing redundancy, eliminating composite keys for partial dependency and separating non-key attributes. • A properly normalised design allows you to: • Use storage space efficiently • Eliminate redundant data • Reduce or eliminate inconsistent data • Ease the database maintenance burden • A bad database design usually include: • Repetition of information • Inability to represent certain information • Loss of information • Difficulty to maintain information
The Process of Normalisation While many people find the process of normalisation quite confusing, this is generally because the formal definitions tend to be quite jargon-heavy and the basic concepts become hidden beneath a murky surface of functional dependencies, determinants and key attributes.
A very good way of understanding normalisation is to use an analogy of paragraphs and good writing. • Any basic language course teaches the fact that all good writing uses single-theme paragraphs. That is, each paragraph should deal solely with a single theme; a paragraph with more than one theme should be split into separate paragraphs – one for each theme
Normalisation uses the same idea: each table should only contain information related to a single concept. – if it contains more than the table should be split into a number of smaller, related tables
The formal process of normalisation is quite simple as a concept: the basic idea is that the table designs undergo a series a refinement steps, with the results of each stage being progressively better than previous stages. • This process is performed by ensuring that the table design meets a series of more and more restrictive criteria, which are known as the normal forms (going from the first normal form to the fifth normal form, defined in such a way that a table in the second normal form is automatically in the first; a table in the third is automatically in the first and second, and so on)
For practical reasons, database designers often stop the normalisation process after the Third Normal Form. • This is due to performance reasons, since execution times of tasks increase as the number of tables involved increases. Also, some tables required by the strictest normalisation rules are impractical, such as having a separate Address table related to the Customer and Employee tables, having a separate record for each distinct address and then relating an address record to each customer and employee record is quite cumbersome.
Note that although many data models allow multivalued fields, normalisation does not (this is one of the requirements of the first stage of normalisation). • The reason for this is simple. By only allowing a single piece of information in a field, performing searches on that information becomes much more straightforward and more efficient.
