The characteristics of good primary keys and how to select them

What are the characteristics of the primary key? Well, primary keys should be stable and should not change over time. The Primary Key or also known as the PK, for short; should contain the minimal attributes. Even in many cases, just, preferably a single attribute. Although a PK value should always exists. The criteria for consideration of a primary key are: Uniqueness, Irreducibility (for instance; no subset of the key uniquely identifies a row in the table), Simplicity (so that the relational representation & manipulation can be a lot simpler), Stability (should not be altered top frequently; for instance), Familiarity (meaningful to the user; for example). So in turn; Arguably, the most important characteristic of an entity is its primary key (a single attribute or combination of attributes, rather), which uniquely identifies each entity instance. The primary key’s function is to guarantee entity integrity. Good primary keys are usually short and include all numbers. They avoid using special characters or a combination of uppercase and lowercase letters. Some things that do NOT make good primary keys are zip codes, email addresses and Social Security numbers.

APPLY FLEXIBLE SOLUTIONS FOR SPECIAL DATA-MODELING CASES

Data modeling is basically the process of documenting a complex software system design as an easily understood diagram, using text and symbols to represent the way data needs to flow. The diagram can be used to ensure efficient use of data, as a blueprint for the construction of new software or for re-engineering a legacy application.

EXPLAIN NORMALIZATION AND ITS ROLE IN THE DATABASE DESIGN PROCESS

The; normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database, yet more flexible, by eliminating such redundancy and inconsistent dependency. Normalization is essentially, a process of reducing the redundancies of data in a database. Normalization is also a technique that is used when designing and redesigning a database; for instance. Normalization is a process or set of guidelines used to optimally design a database to reduce redundant data(s). The actual guidelines of normalization, or, which may also be coined normal forms. It is sometimes difficult to cover normalization because of the complexity involved in understanding the rules of the normal forms; this early on in our SQL journey. However, normalization is a crucial database process that, if understood, will increase your understanding of SQL. So; Normalization is a process to eliminate the flaws of a database with seemingly poor design. A poorly designed database is inconsistent and creates issues while adding, deleting or updating the information. Lets, view database normalization ultimately as a process used to organize a database into tables and columns. The idea is that a table should be about a specific topic and that and only supporting topics included. For instance, a spreadsheet containing information about salespeople and customers serves multiple purposes easily.

EXPLAIN HOW NORMAL FORMS CAN BE TRANSFORMED FROM LOWER NORMAL FORMS TO HIGHER NORMAL FORMS

Normalization helps in assessing and correcting database structure (i.e. the table) in order to reduce the data redundancies and therefore those anomalies. Normalization is applied and alas measured by normalization levels. Normalization levels (or, i.e., Normal forms) are defined by the Database experts. It means a database can be told in any one of normal form if it satisfies all the required criteria defined by that normal form. Normal forms are basically normalization levels, used to measure the redundancy and consistency level of the databases. The higher the normal form means less data redundancy and more data consistency or correctness of data. Normalization starts with 1NF (First Normal Form) and go further with 2NF (Second Normal Form), 3NF (Third Normal Form), BCNF (Boyce-Codd Normal Form or 3.5NF), 4NF (Fourth Normal Form), 5NF and higher. 5NF and higher normal forms are rare seen and sometimes not practically possible, so we will not cover these normal forms in this post.

APPLY NORMALIZATION RULES TO EVALUATE AND CORRECT TABLE STRUCTURES

Normalization essentially, ensures that most attributes are grouped together in such a way that there is no such redundancy or at least controlled redundancy. The bottom line is “each table should have only one source of data” if it is in the normal form. We aim at good database designs. This means also; good table structures, in most cases. Remember, Data normalization is the process in which data attributes within a data model are organized to increase the cohesion of these entity types. In other words, the goal of data normalization is to reduce and even later eliminate data redundancy, an important consideration for application developers because it is incredibly difficult to store said objects in a relational database that maintains the same information in several places; for instance.

IDENTIFY SITUATIONS THAT REQUIRE DENORMALIZATION TO GENERATE INFORMATION EFFICIENTLY

In a nut-shell; Denormalization is an approach to speeding up read-oriented data retrieval performance in a relational database, where the database administrator selectively adds back specific instances of redundant data after the data structure has been normalized. A denormalized database should not be confused with a database that has never been normalized.

REFERENCES

(n.d.). Retrieved from http://faculty.washington.edu/ocarroll/infrmatc/database/design/tsld022.htm.

(n.d.). Retrieved from https://support.microsoft.com/.

(n.d.). Retrieved from https://www.tutorialspoint.com/Importance-of-Database-Normalization.

Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, and Management (11th ed.). : Cengage Learning.

http://agiledata.org/essays/dataNormalization.html