database Normalization: (Work In Progress)
 
  1. Entity and referential integrity : Use of Primary and Secondary Key, to prevent NULL entries and also to ensure that all entries are unique
  2. Functional Dependency: Employee name is functionally dependent on  Unique Social Security Number
  3. Normalization
    1. 1st Normal Forms : Each table should have a primary key
       
      Name DEPT-1 DEPT-2 DEPT-3 DEPT-4
      A X      
      B   X    
      C     X  
      D       X

      Normalized

      Name DeptN0
      A 1
      B 2
      C 3
      D 4
    2. 2nd Normal Form
       
      STDid SADDR SName  
      S1 A1 XYZ  
      S2 A2 XYZ  
      S4 A4 MNO  
      S3 A3 ABC  

       

      STDID SUBJECT
      S1 ENG
      S2 ENG
      S4 MATH
      S3 HISTORY
    3. 3rd Normal Forms.
What is normalization?
  •  A process of  ensuring data integrity and eliminating data redundancy.
    • Data integrity : consistent and satisfies data constraint rules.
  •  Normalization should remove redundancy, but not at the
    expense of data integrity.
  • An integrity enforce a rule that restricts the input of values/attributes of a set of data present in a DB
    •  entity integrity - The rows (or tuples) in a relation represent
      entities, and each one must be uniquely identified. Hence the master table must have the primary key, that must have a unique non-null value for each row.
    • referential integrity - This constraint imports a reference of a row in the master table as a  foreign key. Thus a relationship is enforced between two tables.
 
Relation Ship

In a one-to-one relationship, each record in one table relates directly to a single record in another table.

An example is an Employees table and a company Soccer Players table. An employee will be listed in the Soccer Players table only once, and vice versa. Each soccer player has one matching record in the Employees table. This set of data is a subset of the Employee ID records in the Employees table. 

Tables with a one-to-one relationship

  1. Each soccer player has one matching record in the Employees table.
  2. This set of data is a subset of the Employee ID records in the Employees table.
     

 

One to Many

In a one-to-many relationship, a record in one table relates to multiple records in a second table, but the records in the second table relate to only one record in the first table.

An example is a Suppliers table and a Products table. One supplier might be the only source of multiple products, all of which relate only to that supplier.
 

  1. Figure 1 One supplier ...
  2. Figure 2 can supply more than one product ...
  3. Figure 3 ... but each product has only one supplier
Tables with a one-to-many relationship

Many to Many

In a many-to-many relationship, a record in one table relates to multiple records in a second table, and a record in the second table relates to multiple records in the first table.

This type of relationship requires a third table, called a junction table. The junction table refers the primary key in the master table,  with (one or more) foreign keys.



For example, the Orders table and the Products table have a many-to-many relationship. One order can contain many products, and each product can appear in many orders.

The third table in this example, the Order Details table contains primary keys from the Orders and Products tables. The Order Details table is the junction table for the other two.

 All junction tables connect tables that have one-to-many relationships with the junction tables, and the primary keys of those connected tables are the foreign keys of the junction table.

When one table can relate to many others simply by referencing information in them, the parts of your database are like the cells of your body. Each is active, each is the right size for its business, and together they can achieve effects vastly beyond their individual abilities.
  

  1.  Primary key from the Orders table.
  2. Primary key from the Products table.
  3. One order can have many products ...
  4. and each product can appear on many orders