Normalisation

2NF

When a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

[http://en.wikipedia.org/wiki/Second_normal_form]

A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.

Multiple candidate keys occur in the following table:

Electric Toothbrush Models
Manufacturer	Model	Model Full Name	Manufacturer Country
Forte	X-Prime	Forte X-Prime	Italy
Forte	Ultraclean	Forte Ultraclean	Italy
Dent-o-Fresh	EZbrush	Dent-o-Fresh EZBrush	USA
Kobayashi	ST-60	Kobayashi ST-60	Japan
Hoch	Toothmaster	Hoch Toothmaster	Germany
Hoch	X-Prime	Hoch X-Prime	Germany

Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:

Electric Toothbrush Manufacturers
Manufacturer	Manufacturer Country
Forte	Italy
Dent-o-Fresh	USA
Kobayashi	Japan
Hoch	Germany

Electric Toothbrush Models
Manufacturer	Model	Model Full Name
Forte	X-Prime	Forte X-Prime
Forte	Ultraclean	Forte Ultraclean
Dent-o-Fresh	EZbrush	Dent-o-Fresh EZBrush
Kobayashi	ST-60	Kobayashi ST-60
Hoch	Toothmaster	Hoch Toothmaster
Hoch	X-Prime	Hoch X-Prime

[http://en.wikipedia.org/wiki/Second_normal_form]

 

BCNF

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.

Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

[http://en.wikipedia.org/wiki/Boyce-Codd_normal_form]

A relation is in BCNF, if and only if, every determinant is a candidate key.

4NF

Multivalued Dependency

If the column headings in a relational database table are divided into three disjoint groupings X, Y, and Z, then, in the context of a particular row, we can refer to the data beneath each group of headings as x, y, and z respectively. A multivalued dependency X-->>Y signifies that if we choose any x actually occurring in the table (call this choice xc), and compile a list of all the xcyz combinations that occur in the table, we will find that xc is associated with the same y entries regardless of z.

A trivial multivalued dependency X-->>Y is one where either Y is a subset of X, or X and Y together form the whole set of attributes of the relation.

A functional dependency is a special case of multivalued dependency. In a functional dependency X-->Y, every x determines exactly one y, never more than one.

Consider this example of a database of teaching courses, the books recommended for the course, and the lecturers who will be teaching the course:

Teaching database
Course	Book	Lecturer
AHA	Silberschatz	John D
AHA	Nederpelt	William M
AHA	Silberschatz	William M
AHA	Nederpelt	John D
AHA	Silberschatz	Christian G
AHA	Nederpelt	Christian G
OSO	Silberschatz	John D
OSO	Silberschatz	William M

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.
Put formally, there are two multivalued dependencies in this relation: {course}-->>{book} and equivalently {course}-->>{lecturer}.

[http://en.wikipedia.org/wiki/Multivalued_dependency]

Example

For an example see the ‘Example’ section on http://en.wikipedia.org/wiki/Fourth_normal_form

My Normalisation Guide

1NF	All attributes must be atomic. All instances of an entity must contain the same number of values. All instances of an entity must be different from one another.
2NF	Only relevant when a composite candidate key (primary key or other candidate key) exists in the entity. The entity must be in 1NF. All attributes must be a fact about the entire key.
3NF	The entity must be in 2NF. Non-key attributes cannot describe other non-key attributes.
BCNF	Only in rare cases 3NF entity does not satisfy BCNF (3NF table with two or more overlapping candidate keys may or may not be in BCNF). Every determinant is a key. If a table has no non-key attributes then it satisfy all previous normal forms up to BCNF.
4NF	The entity must be in BCNF. Either every multivalued dependency X -->> Y is trivial or for every nontrivial multivalued dependency X -->> Y, X is a superkey (or in other words, no attribute can have multiple values issue in the entity).
5NF	The entity must be in 4NF. Try to breakdown any ternary relationship into binary relationships.

Dynamic Data Features

How to add Dynamic Data to an existing site

1.       Copy ‘DynamicData’ folder, site.css, and site.master files.

2.       Add references to ‘System.ComponentModel.DataAnnotations’, ‘System.Web.Routing’, and ‘System.Web.DynamicData’.

3.  Modify global.asax file
protected void Application_Start(object sender, EventArgs e)

        {

            // Create an instance of the data model.

            MetaModel DefaultModel = new MetaModel();

            // Register the data model.

            DefaultModel.RegisterContext(typeof(

              AdventureWorksLT_DataEntities),

              new ContextConfiguration() { ScaffoldAllTables = false });

            // Create the routes.     

            RouteTable.Routes.Add(new

                DynamicDataRoute("{table}/{action}.aspx")

                {

                    Constraints = new RouteValueDictionary(new

                    {

                        action = "List|Details|Edit|Insert"

                    }),

                    Model = DefaultModel

                });

        }

Adding Dynamic Behaviour to Controls

1.       For GridView and DetailsView that uses LinqDataSource or EntityDataSource:
use ‘DynamicField’

<asp:DynamicField DataField="OrderQty" HeaderText="Order Quantity" />For sample code see ‘addDDBehaviourToGridViewAndDetailsVew.aspx’

2.       For ListView and FormView that uses LinqDataSource or EntityDataSource:
use ‘DynamicControl’
<asp:DynamicControl ID="DynamicControl1" runat="server" DataField="FirstName" Mode="Edit" /> For sample code see ‘addDDBehaviourToListViewAndFormView.aspx’

Action Links

-          Data-bound control table row action links
use ‘DynamicHyperLink’ control

<asp:TemplateField>

    <ItemTemplate>                             

        <asp:DynamicHyperLink runat="server" ID="EditHyperLink"

            Action="Edit" Text="Edit"/>

    </ItemTemplate>

</asp:TemplateField>

-          Unbound action links

<asp:DynamicHyperLink ID="InsertHyperLink" runat="server"

    Action="Insert" ContextTypeName="DynamicDataFeatures.AdventureWorksLT_DataEntities"  

    TableName="ProductModels">Insert new item

</asp:DynamicHyperLink>

<asp:DynamicHyperLink ID="DynamicHyperLink2" runat="server" Action="Edit"

    ContextTypeName="DynamicDataFeatures.AdventureWorksLT_DataEntities"

    TableName="ProductModels" ProductModelID="1">Edit item</asp:DynamicHyperLink>

For example, see ‘ActionLinks.aspx’

Customising Data Field Appearance and Behaviour

1.       Add custom field template files inside ‘FieldTemplates’ folder.
Add CustomFieldTemplateName.ascx for viewing and CustomFieldTemplateName_Edit.ascx for editing.

2.       Modify the markup and display functionality.
We may need to overwrite the ‘OnDataBinding’ method as well.

See ‘MyCustomFieldTemplate.ascx’ for more details.

3.  Refer to the custom field template in the databound row control by adding ‘UIHint’
<asp:DynamicField DataField="OrderQty" HeaderText="Order Quantity" UIHint="MyCustomFieldTemplate" />

See ‘customDataField.aspx’ for more details.

To associate with an entity that will affect the rendering in the whole project, create a partial class for that entity then use ‘UIHint’ attribute on its meta data class.

Customising a Table Layout using Custom Page Template

1.       In the ‘DynamicData\CustomPages’ folder, create a subfolder with the name of the entity

2.       Copy an existing page template from the ‘DynamicData\PageTemplates’ folder to the subfolder created

3.       Change the copied files namespace if necessary

4.       Modify the new template

See ‘CustomPages/Products’ folder for more details.

Customising a Table Layout using Entity Template

If it’s only for editing details, edit, and insert views, we can use Entity Template.

1.       Add new ascx file with the entity name as the filename in the ‘EntityTemplates’ folder

2.       In the class file change the base class from ‘UserControl’ to ‘EntityTemplateUserControl’

3.       Modify the new template file

See ‘Addresses.ascx’ inside ‘EntityTemplates’ folder.

Customising Entity Model Class

1.       Create a partial class for the entity

2.       Add references to System.Web.DynamicData and System.ComponentModel.DataAnnotations with ‘Using’ keyword

3.       Create a class that will act as the associated metadata class for the entity partial class

4.       Add ‘MetadataTypeAttribute’ attribute to the entity partial class definition with the class name that has just been created

[MetadataType(typeof(CustomerMetaData))]

public partial class Customer { }

5.       Add a property accessor for each data field that we want to provide attributes for. For any other fields that we want them to be displayed as default, ignore specifying them.

Sample:

[MetadataType(typeof(SalesOrderDetailMetadata))]

public partial class SalesOrderDetail

{

    public partial class SalesOrderDetailMetadata

    {

        [UIHint("MyCustomFieldTemplate")]

        public object OrderQty;

        [DataType(DataType.Date)]

        public object ModifiedDate { get; set; }

    }

}

Some useful attributes:

-           [Required]

-           [Range(X, Y)]

-           [DataType(DataType.DataType)]

-           [ScaffoldColumn(true/false)]

-           [DisplayName(String)]

-           [RegularExpression(Expression, ErrorMessage = String)]

-           [ScaffoldTable(true)] – This attribute is used for an entity partial class, not a data field.

Modifying Field Template to Use Customised Data Attributes

For this example, see ‘Customer.cs’ and ‘FieldTemplates/Text.ascx.cs’