database Normalization:
http://girfahelp.blogspot.com/2014/05/functional-dependency-and-normalization.html

minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys.

The benefits of using a database that has a suitable set of relations is that the database will be:
easier for the user to access and maintain the data;

take up minimal storage space on the computer.

  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: Contains No repeating groups, and relationship lays on ONE key (relational attribute)

      Name DeptN0
      A 1
      B 2
      C 3
      D 4

       

    2. 2nd Normal Form : In these two table, shown below, all attributes are functionally dependent on it's primary key in the first table, and corresponding Foreign key in the second table. This will reduce the chances of data anomalies while updating a table. Non-Key attributes must depend on the primary key
       
      STDid (PK) SADDR Fname Lname
      S1 A1 XYZ Smith
      S2 A2 XYZ McIntosh
      S4 A4 MNO Singh
      S3 A3 ABC Carpenter

       

      STDID (FK) SUBJECT Grade
      S1 ENG A
      S2 ENG B
      S4 MATH A+
      S3 HISTORY A

      By Law it is OK, but over simplified; who would teach the subjects ?
       

    3. 3rd Normal Forms: The above, 2nd normal form, is also true for a 3rd normal form, with a room for another level of data storage capacity, and functional dependencies.
      Student Table: (personal info)
      Sid (PK) SADDR Fname Lname
      S1 A1 XYZ Smith
      S2 A2 XYZ McIntosh
      S3 A3 MNO Singh
      S4 A4 ABC Carpenter

      Staff table: (personal info) 

      EID fname lname job Dept
      E1 Adam Corelli Teach Eng
      E2 Dan Pasco Teach Math
      E3 Peter Cisco Teach History
      E4 Dan Mathew CLERK Payroll
      E5 Pat Conner CLERK Management

     

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