|
|
| Line 84: |
Line 84: |
| | | | |
| | </csharp> | | </csharp> |
| − |
| |
| − |
| |
| − | ==Events and Commands==
| |
| − |
| |
| − | {{ASPX csharp commands}}
| |
| − |
| |
| − | ==More Normal Forms==
| |
| − |
| |
| − | ===Second Normal Form===
| |
| − |
| |
| − | In order to reach 2NF the table must first be in 1NF
| |
| − |
| |
| − | Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.
| |
| − |
| |
| − | 'Remove Fields that are not dependent on the primary key'
| |
| − |
| |
| − |
| |
| − | Not Normal
| |
| − |
| |
| − | Adresses
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! CustNum !! FirstName !! LastName !! Address !! City !! State !! ZIP
| |
| − | |-
| |
| − | | 1 || John || Doe || 12 Main Street || Sea Cliff || NY || 11579
| |
| − | |-
| |
| − | | 2 || Alan || Johnson || 82 Evergreen Tr || Sea Cliff || NY || 11579
| |
| − | |-
| |
| − | | 3 || Beth || Thompson || 1912 NE 1st St || Miami || FL || 33157
| |
| − | |-
| |
| − | | 4 || Jacob || Smith || 142 Irish Way || South Bend || IN || 46637
| |
| − | |-
| |
| − | | 5 || Sue || Ryan || 412 NE 1st St || Miami || FL || 33157
| |
| − | |}
| |
| − |
| |
| − |
| |
| − | Normal
| |
| − |
| |
| − | StatesZips
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! ZIP !! City !! State
| |
| − | |-
| |
| − | | 11579 || Sea Cliff || NY
| |
| − | |-
| |
| − | |33157 || Miami || FL
| |
| − | |-
| |
| − | |46637 || South Bend || IN
| |
| − | |}
| |
| − |
| |
| − | Address
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! CustNum !! FirstName !! LastName !! Address !! ZIP
| |
| − | |-
| |
| − | |1 || John || Doe || 12 Main Street || 11579
| |
| − | |-
| |
| − | |2 || Alan || Johnson || 82 Evergreen Tr || 11579
| |
| − | |-
| |
| − | |3 || Beth || Thompson || 1912 NE 1st St || 33157
| |
| − | |-
| |
| − | |4 || Jacob || Smith || 142 Irish Way || 46637
| |
| − | |-
| |
| − | |5 || Sue || Ryan || 412 NE 1st St || 33157
| |
| − | |}
| |
| − |
| |
| − | ===In class===
| |
| − |
| |
| − | Normalize the books exmaple
| |
| − |
| |
| − | '''Books'''
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | !Author !! Title !! Pages !! Publisher !! PublisherURL !! Subject !! ISBN
| |
| − | |-
| |
| − | | Michael Allen Dymmoch || The Man Who Understood Cats || 256 || Avon Books || http://www.harpercollins.com/imprints/index.aspx?imprintid=517994 || Fiction Mystery || 0380722658
| |
| − | |-
| |
| − | | Joseph Cancellaro || Exploring Sound Design for Interactive Media || 272 || Thomson Delmar Learning || http://www.delmarlearning.com/ || Sound || 1401881025
| |
| − | |}
| |
| − |
| |
| − | ===Third Normal Form===
| |
| − |
| |
| − | Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key. Must be in second normal form.
| |
| − |
| |
| − | Not normal
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | !Company !! City !! State !! ZIP
| |
| − | |-
| |
| − | | Acme Widgets || New York || NY || 10169
| |
| − | |-
| |
| − | | ABC Corporation || Miami || FL || 33196
| |
| − | |-
| |
| − | | XYZ, Inc. || Columbia || MD || 21046
| |
| − | |}
| |
| − |
| |
| − | Normal
| |
| − |
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! KompanyID !! KompanyName !! Zip
| |
| − | |-
| |
| − | |1 || Acme Widgets || 10169
| |
| − | |-
| |
| − | |2 || ABC Corporation || 33196
| |
| − | |-
| |
| − | |3 || XYZ, Inc. || 21046
| |
| − | |}
| |
| − |
| |
| − |
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! Zip !! CityID
| |
| − | |-
| |
| − | |10169 || 1
| |
| − | |-
| |
| − | |33196 || 2
| |
| − | |-
| |
| − | |21046 || 3
| |
| − | |}
| |
| − |
| |
| − |
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! CityID !! City
| |
| − | |-
| |
| − | | 1 || New York
| |
| − | |-
| |
| − | | 2 || Miami
| |
| − | |-
| |
| − | | 3 || Columbia
| |
| − | |}
| |
| − |
| |
| − |
| |
| − | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
| − | ! StateID !! State
| |
| − | |-
| |
| − | | 1 || NY
| |
| − | |-
| |
| − | | 2 || FL
| |
| − | |-
| |
| − | | 3 || MD
| |
| − | |}
| |
| − |
| |
| − | http://home.earthlink.net/~billkent/Doc/simple5.htm.
| |
| − |
| |
| − | ==Many to Many Relationships==
| |
| − |
| |
| − | Look up tables. Are often used to define a many to many relation ship. These lookup tables often have a [http://en.wikipedia.org/wiki/Compound_key compound key].
| |
| − |
| |
| − | See the Student Courses example from the [[Data Relationships]] page.
| |
| − |
| |
| − | This is a demonstration of a logon system where users can have multiple roles. There is a compound key used on the roles table. The compound key consists of two or more foreign keys. Each combination of foreign keys must be unique.
| |
| − |
| |
| − | [[Image:ManyToMany.png]]
| |
| − |
| |
| − | ===Lookup Table===
| |
| − |
| |
| − | Here the BlogTags Tbale uses a compound key (two primary keys) to join two other tables. The Blog table is joined to the Tag table where each person can add a tag. This is a one to many one to many relationship. Person can a tag to each blog. This is often referred to as a look up table
| |
| − |
| |
| − | [[Image:DDBlogLookupTable.png]]
| |
| − |
| |
| − | ==HomeWork==
| |
| − |
| |
| − | Blog project can be done alone or XP style with one partner.
| |
| − |
| |
| − |
| |
| − | [[DD Blog Project]]
| |
Revision as of 20:42, 8 November 2009
Review First Normal Form
Let build the example for the homework.
Build Interface to GameDB
Games
Tables Games, Developers, Websites, Platforms?
Build Views
DataReader
1 Connection Object
<csharp>
//Connection string comes from web config
SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);
</csharp>
web.config connection strings
<xml>
<configuration>
<connectionStrings>
<add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/>
</connectionStrings>
</xml>
2 Command Object
<csharp>string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";
string strResultsHolder = "";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
</csharp>
3 Reader and Reading
<csharp>
SqlDataReader objDataReader = null; //Reader to read through the result
try
{
objConnection.Open(); //Open Connection
objDataReader = objCommand.ExecuteReader();
while (objDataReader.Read() == true)
{
strResultsHolder += String.Format("{0}:{1}
",
objDataReader["CheeseName"], objDataReader["CheeseDescription"]);
}
}
catch (Exception e)
{
lblErr.Text = "Connection failed to open successfully.
";
lblErr.Text += e.ToString();
}
finally
{
objDataReader.Close(); //Close Reader
objConnection.Close(); //Close Connection
}
//Write results
divListCheese.InnerHtml = strResultsHolder;
}
</csharp>
