Difference between revisions of "DD Class9"
(→HomeWork) |
|||
Line 1: | Line 1: | ||
[[Category:Data Design]] | [[Category:Data Design]] | ||
+ | |||
+ | ==Review First Normal Form== | ||
+ | |||
+ | Let build the example for the homework. | ||
+ | |||
+ | ==Build Interface to GameDB== | ||
+ | |||
+ | '''Games''' | ||
+ | {| class="wikitable" cellpadding="5" cellspacing="0" | ||
+ | !GameTitle !! GameGenre !! DeveloperName !! Platform(s) !! Year !! DeveloperWebsite !! GameWebsite | ||
+ | |- | ||
+ | | Quake1 || FPS || id || Dos || 1996 || http://www.idsoftware.com/ || http://www.idsoftware.com/games/quake/quake/ | ||
+ | |- | ||
+ | | Diablo|| RPG|| Blizzard || Windows 95|| 1996 || http://www.blizzard.com/ || http://www.blizzard.com/diablo/ | ||
+ | |- | ||
+ | | SimCity || Sim || Interplay || Dos || 1993 || http://www.interplay.com/ || http://www.maxis.com/ | ||
+ | |} | ||
+ | |||
+ | Tables Games, Developers, Websites, Platforms? | ||
+ | |||
+ | Build Views | ||
+ | |||
+ | |||
+ | ==DataReader== | ||
+ | |||
+ | *DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/ado/dataReader.aspx source] | ||
+ | |||
+ | |||
+ | |||
+ | 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("<b>{0}</b>:{1}<br /><br />", | ||
+ | objDataReader["CheeseName"], objDataReader["CheeseDescription"]); | ||
+ | } | ||
+ | } | ||
+ | catch (Exception e) | ||
+ | { | ||
+ | lblErr.Text = "Connection failed to open successfully.<br/>"; | ||
+ | lblErr.Text += e.ToString(); | ||
+ | } | ||
+ | finally | ||
+ | { | ||
+ | objDataReader.Close(); //Close Reader | ||
+ | objConnection.Close(); //Close Connection | ||
+ | |||
+ | } | ||
+ | //Write results | ||
+ | divListCheese.InnerHtml = strResultsHolder; | ||
+ | } | ||
+ | |||
+ | </csharp> | ||
+ | |||
==Events and Commands== | ==Events and Commands== |
Revision as of 20:42, 8 November 2009
Contents
Review First Normal Form
Let build the example for the homework.
Build Interface to GameDB
Games
GameTitle | GameGenre | DeveloperName | Platform(s) | Year | DeveloperWebsite | GameWebsite |
---|---|---|---|---|---|---|
Quake1 | FPS | id | Dos | 1996 | http://www.idsoftware.com/ | http://www.idsoftware.com/games/quake/quake/ |
Diablo | RPG | Blizzard | Windows 95 | 1996 | http://www.blizzard.com/ | http://www.blizzard.com/diablo/ |
SimCity | Sim | Interplay | Dos | 1993 | http://www.interplay.com/ | http://www.maxis.com/ |
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>
Events and Commands
Events in c# and aspx
Event handlers
OnClick
http://iam.colum.edu/oop/classsource/class9/events/events1.aspx events1.aspx - source
OnCommand
http://iam.colum.edu/oop/classsource/class9/events/events2.aspx events2.aspx - source
http://iam.colum.edu/oop/classsource/class9/events/events3.aspx events3.aspx - source
<csharp>
<%@ Page language="c#" debug="True" trace="false"%>
<script language="c#" runat="server">
Dog fido; public void Page_Load() { fido = new Dog(); }
public void Button1_OnClick(Object sender, EventArgs e) { Response.Write("Hello from Button1_OnClick"); lblBark.Text = fido.Bark(); }
public class Dog { public string Name; // the dog's name public int Age; // the dog's age public int Weight; // the dog's weight public string BarkSound; // the sound of the dog's bark
public Dog() { BarkSound = "Woof!!!"; }
public string Bark() { return this.BarkSound; } public void Eat() { //put eat code here } }
</script>
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
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
ZIP | City | State |
---|---|---|
11579 | Sea Cliff | NY |
33157 | Miami | FL |
46637 | South Bend | IN |
Address
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
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
Company | City | State | ZIP |
---|---|---|---|
Acme Widgets | New York | NY | 10169 |
ABC Corporation | Miami | FL | 33196 |
XYZ, Inc. | Columbia | MD | 21046 |
Normal
KompanyID | KompanyName | Zip |
---|---|---|
1 | Acme Widgets | 10169 |
2 | ABC Corporation | 33196 |
3 | XYZ, Inc. | 21046 |
Zip | CityID |
---|---|
10169 | 1 |
33196 | 2 |
21046 | 3 |
CityID | City |
---|---|
1 | New York |
2 | Miami |
3 | Columbia |
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 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.
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
HomeWork
Blog project can be done alone or XP style with one partner.