Difference between revisions of "DD Class11"

esse quam videri
Jump to: navigation, search
(→‎Logon Using the tables we made last week)
(→‎HomeWork)
 
(17 intermediate revisions by the same user not shown)
Line 3: Line 3:
  
  
−
==Logon Using the tables we made last week==
+
==More Normal Forms==
−
You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.
 
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config
+
===Second Normal Form===
  
−
Main web.config add the following to the system.web section
+
In order to reach 2NF the table must first be in 1NF
−
<xml>
 
−
<system.web>
 
−
 
 
−
    <authentication mode="Forms">
 
−
<forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All">
 
−
</forms>
 
−
    </authentication>
 
−
 
−
    </system.web>
 
−
</xml>
 
  
−
Now any link to the admin folder
+
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.
  
−
http://iam.colum.edu/dd/classsource/logon/admin/
+
'Remove Fields that are not dependent on the primary key'
  
−
should redirect to the logon page
 
  
−
'''Logon Page'''
+
Not Normal
  
−
Add a New user page
+
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
 +
|}
  
−
==Logon the Microsoft way==
 
  
−
==Sql Aggregate Functions==
+
Normal
  
−
They perform a calculation on a set to values and return a single value.
+
StatesZips
−
Aggregate functions are determinate. They will return the same value given the same dataset.
+
{| class="wikitable" cellpadding="5" cellspacing="0"
 +
! ZIP !! City !! State
 +
|-
 +
| 11579 || Sea Cliff || NY
 +
|-
 +
|33157  || Miami || FL
 +
|-
 +
|46637 || South Bend || IN
 +
|}
  
−
*AVG
+
Address
−
*MIN 
+
{| class="wikitable" cellpadding="5" cellspacing="0"
−
*CHECKSUM
+
! CustNum !! FirstName !! LastName !! Address !! ZIP
−
*SUM 
+
|-
−
*CHECKSUM_AGG
+
|1 || John || Doe || 12 Main Street || 11579
−
*STDEV 
+
|-
−
*COUNT
+
|2 || Alan || Johnson || 82 Evergreen Tr || 11579
−
*STDEVP 
+
|-
−
*COUNT_BIG
+
|3 || Beth || Thompson || 1912 NE 1st St || 33157
−
*VAR 
+
|-
−
*GROUPING
+
|4 || Jacob || Smith || 142 Irish Way || 46637
−
*VARP 
+
|-
−
*MAX
+
|5 || Sue || Ryan || 412 NE 1st St || 33157
 +
|}
  
−
<sql>
+
===In class===
−
SELECT MAX(PricePerPound) as MaxPrice from CheesePrice
 
  
−
SELECT AVG(PricePerPound) as AvgPrice from CheesePrice
+
Normalize the books exmaple
  
−
SELECT COUNT(CheeseID) as Count from CheesePrice
+
'''Books'''
−
</sql>
+
{| 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
 +
|}
  
−
==Sub Query==
+
===Third Normal Form===
  
−
A query may be a part of another query. This allows
+
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.
  
−
IN vs EXSISTS
+
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
 +
|}
  
−
Remember the Many to Many relation ship in the Student Registration System
+
Normal
  
−
[[Image:ManyToManyStudentCourses.png]]
 
−
 
−
Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes '''should not''' include the courses that they are already taking.
 
−
Course
 
 
{| class="wikitable" cellpadding="5" cellspacing="0"
 
{| class="wikitable" cellpadding="5" cellspacing="0"
−
!CourseID !! CourseName !! CourseNumber
+
! KompanyID !! KompanyName !! Zip
 
|-
 
|-
−
|9 || Science || 66-0002
+
|1 || Acme Widgets || 10169
 
|-
 
|-
−
|10 || Math || 66-0001
+
|2 || ABC Corporation || 33196
 
|-
 
|-
−
|11 || Data Design || 36-2601
+
|3  || XYZ, Inc. || 21046
 +
|}
 +
 
 +
 
 +
{| class="wikitable" cellpadding="5" cellspacing="0"
 +
! Zip !! CityID
 
|-
 
|-
−
|168 || Application Design || 36-4601
+
|10169 || 1
 
|-
 
|-
−
|169 || OOP || 36-2001
+
|33196 || 2
 +
|-
 +
|21046 || 3
 
|}
 
|}
  
−
Student
+
 
 
{| class="wikitable" cellpadding="5" cellspacing="0"
 
{| class="wikitable" cellpadding="5" cellspacing="0"
−
!StudentID !! PeopleID !! OasisID
+
! CityID !! City
 
|-
 
|-
−
|1 || 1 || 666666
+
| 1 || New York
 
|-
 
|-
−
|2 || 2 || 777777
+
| 2 || Miami
 +
|-
 +
| 3  || Columbia
 
|}
 
|}
  
  
−
StudentCourses
 
 
{| class="wikitable" cellpadding="5" cellspacing="0"
 
{| class="wikitable" cellpadding="5" cellspacing="0"
−
! StudentID !! CourseID !! SemesterID
+
! StateID !! State
 
|-
 
|-
−
|1 || 9 || 1
+
| 1 || NY
 
|-
 
|-
−
|2 || 9 || 1
+
| 2 || FL
−
|-
+
|-  
−
|1 || 10 || 1
+
| 3 || MD
 
|}
 
|}
  
−
StudentsCourses_vw
+
http://home.earthlink.net/~billkent/Doc/simple5.htm.
−
<sql>
+
 
−
SELECT dbo.Student.PeopleID, dbo.Student.OasisID, dbo.StudentCourses.SemesterID, dbo.Course.CourseName,
+
==Many to Many Relationships==
−
  dbo.Course.CourseNumber, dbo.Student.StudentID,
+
 
−
  dbo.Course.CourseID
+
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].
−
FROM dbo.StudentCourses INNER JOIN
+
 
−
  dbo.Student ON dbo.StudentCourses.StudentID = dbo.Student.StudentID INNER JOIN
+
See the Student Courses example from the [[Data Relationships]] page.
−
  dbo.Course ON dbo.StudentCourses.CourseID = dbo.Course.CourseID
+
 
−
</sql>
+
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:StudentsCourses_vw.png]]
+
[[Image:ManyToMany.png]]
  
−
<sql>
+
===Lookup Table===
−
SELECT CourseID, CourseName, CourseNumber
 
−
FROM Course
 
−
WHERE
 
−
(
 
−
  CourseID not in
 
−
  (
 
−
      SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
 
−
  )
 
−
)
 
−
</sql>
 
  
−
This query has two parts
+
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
  
−
SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
+
[[Image:DDBlogLookupTable.png]]
  
−
returns
+
==Logon Using the tables in blog==
−
{|
+
You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.
−
!CourseID
 
−
|-
 
−
|9
 
−
|-
 
−
|10
 
−
|}
 
  
−
then
+
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config
  
−
<sql>
+
Main web.config add the following to the system.web section
−
SELECT CourseID, CourseName, CourseNumber
+
<xml>
−
FROM Course
+
<system.web>
−
WHERE
+
 
−
(
+
    <authentication mode="Forms">
−
  CourseID NOT IN
+
<forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All">
−
  (
+
</forms>
−
      SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
+
    </authentication>
−
  )
+
−
)
+
    </system.web>
−
</sql>
+
</xml>
  
−
returns
+
Now any link to the admin folder
−
{|
 
−
! CourseID !! CousreName !!CourseNumber
 
−
|-
 
−
|11 || Data Design || 36-2601
 
−
|-
 
−
|168 || Application Design || 36-4601
 
−
|-
 
−
|169 || OOP || 36-2001
 
−
|}
 
  
−
Course adder example
+
http://iam.colum.edu/dd/classsource/logon/admin/
  
−
http://iam.colum.edu/dd/classsource/class8/student.aspx
+
should redirect to the logon page
  
−
SQL Subquery
+
'''Logon Page'''
  
−
==Email==
+
http://iam.colum.edu/dd/classsource/Logon/Logon.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx - source]
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx.cs
  
 +
Add a New user page
 +
http://iam.colum.edu/dd/classsource/Logon/CreateAccount.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx -source]
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx.cs
  
 +
'''Admin Page'''
 +
http://iam.colum.edu/dd/classsource/Logon/Admin/Default.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx - source]
 +
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx.cs
  
−
===Send Mail===
+
==Logon the Microsoft way==
−
<csharp><% @Page Language="C#" %>
 
−
<% @Import Namespace="System.Web.Mail" %>
 
−
<%
 
−
    //set up some strings for the email
 
−
   
 
−
    string strTo = "only_a_test@fastmail.fm";
 
−
    string strFrom = "jeff@interactive.colum.edu";
 
−
    string strSubject = "Hi jeff";
 
−
    string strBody = "A real nice body text here";
 
−
   
 
−
    //Send email
 
−
    SmtpMail.SmtpServer = "localhost";
 
−
    SmtpMail.Send(strFrom, strTo, strSubject, strBody);
 
−
%></csharp>
 
−
http://iam.colum.edu/oop/classsource/class14/mail.aspx
 
−
[[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/mail.aspx -source]]
 
  
 
==HomeWork==
 
==HomeWork==
  
 +
Create a many to many relationship with games. Create a web interface that can add and remove games from genres.
  
 +
<!--
 +
Blog project can be done alone or XP style with one partner.
  
−
Web Skeleton of your site
 
  
−
Demonstrate one feature
+
[[DD Blog Project]]
 +
-->

Latest revision as of 18:33, 26 November 2012



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.

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

DDBlogLookupTable.png

Logon Using the tables in blog

You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.

http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config

Main web.config add the following to the system.web section <xml> <system.web> 

    <authentication mode="Forms">

<forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All"> </forms> 

    </authentication>

   </system.web>

</xml>

Now any link to the admin folder

http://iam.colum.edu/dd/classsource/logon/admin/

should redirect to the logon page

Logon Page

http://iam.colum.edu/dd/classsource/Logon/Logon.aspx - source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx.cs

Add a New user page http://iam.colum.edu/dd/classsource/Logon/CreateAccount.aspx -source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx.cs

Admin Page http://iam.colum.edu/dd/classsource/Logon/Admin/Default.aspx - source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx.cs

Logon the Microsoft way

HomeWork

Create a many to many relationship with games. Create a web interface that can add and remove games from genres.


Retrieved from "http://imamp.colum.edu/mediawiki/index.php?title=DD_Class11&oldid=21255"