Difference between revisions of "DD Class10"
(→Create a new user account) |
|||
Line 1: | Line 1: | ||
[[Category:Data Design]] | [[Category:Data Design]] | ||
+ | |||
+ | |||
+ | |||
+ | ==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]] | ||
==Create a new user account== | ==Create a new user account== |
Revision as of 20:43, 8 November 2009
Contents
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.
Create a new user account
To insert into a new user we need to to combine three steps.
- INSERT into the People Table
- Get the new PeopleID
- INSERT into the roles table
<sql> INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (1, 'jeff', 'meyers', 'jmeyers', 'monkey') </sql>
Check the new users PeopleID
<sql> SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers' </sql>
results PeopleID = 1
Give the new user a Role by inserting the PeopleID and a roleID into the roles table
<sql> INSERT into roles ( PeopleID, roleTypeID, RoleActive ) values ( 1, 1, 1) </sql>
Since all of these steps need to happen or fail as a complete unit we must use an transaction. Of course in .NET there are several way to accomplish this.
Test page one
http://iam.colum.edu/dd/classsource/class10/trans.aspx -source
Test page one with form
SQL transation
This can all be done in shorter statement by putting the Select query in the second INSERT
<sql> INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')
GO
INSERT INTO roles ( PeopleID, roleTypeID, RoleActive ) ( SELECT PeopleID, 1, 1 FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2' )
</sql>
By using the Keywords BEGIN TRANSACTION and COMMIT TRANSACTION we can ensure that our batched statements run as a transaction <sql> BEGIN TRANSACTION
/* Remove old trancation test so we can run it again */ DELETE from Roles where PeopleID in (SELECT PeopleID FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2')
DELETE from People where firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2'
GO
/*INSERT for tranaction */ INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')
GO
INSERT INTO roles ( PeopleID, roleTypeID, RoleActive ) ( SELECT PeopleID, 1, 1 FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2' )
COMMIT TRANSACTION </sql>
Stored Procedures
Stored procedures are a way in MSSQL to store a compiled SQL statement. The statement will run faster than a regular SQL statement because the execution plan has already been calculated. The plan is executed with a new set of parameters. There are both input parameters and output parameters. SQL parameters are prefixed with the @ sign.
Default Template
<sql> -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO </sql>
Stored Procedure (SProc) to INSERT into people
<sql> -- ============================================= -- Author: Jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE PeopleInsert -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50), @lastName varchar(50), @logonName varchar(50),
@passwd varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (@salutationID, @firstName, @lastName, @logonName, @passwd) END GO </sql>
You can test the stored procedure by running it and passing in all the required parameters.
<sql> PeopleInsert
@salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName',
@passwd ='monkey'
</sql>
all one line is fine
<sql> PeopleInsert @salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName', @passwd ='monkey' </sql>
GetPeopleID
<sql>
CREATE PROCEDURE GetPeopleID
-- Add the parameters for the stored procedure here
@firstName varchar(50),
@lastName varchar(50),
@logonName varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT PeopleID FROM People WHERE firstName = @firstName AND lastName= @lastName AND logonName= @logonName END </sql>
Again we can run it like this <sql> GetPeopleID @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName' </sql>
Transactions
Whenever you are making changes (insert/update/delete) two more than one table or the results of a select statement are used to update another table you need to use a transaction. Transaction either succeed to fail as a complete unit. Trans actions can cause a lock on a row or tables and should be kept a brief as possible. After all of the work of the transaction is complete it can either be committed or rolled back.
Commit
- Actually commits tht changes
Rollback
- rolls back all changes and the database.
Transaction can also have scope.
Single SQL SProc
full InsertPersonAndRole
<sql> -- ============================================= -- Author: jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE AddUserAndRole -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50) , @lastName varchar(50), @logonName varchar(50), @passwd varchar(50), @roleTypeID int AS BEGIN --Start transaction BEGIN TRANSACTION
--INSERT into People
INSERT into People
(salutationID, firstName, lastName, logonName, passwd)
values
(@salutationID, @firstName, @lastName, @logonName, @passwd)
--Get PeopleID DECLARE @PeopleID int
Select @PeopleID = @@identity
--INSERT into Roles INSERT into roles (PeopleID, RoleTypeID, RoleActive) values (@PeopleID, @roleTypeID, 1)
--Commit Transaction ROLLBACK TRANSACTION
--COMMIT TRANSACTION
END GO
</sql>
and the SQL statement to call the stored procedure
<sql> AddUserAndRole @salutationID = 1, @firstName = 'fnTransTest' , @lastName ='lnTransTest', @logonName ='lnTransTest', @passwd ='pwTransTest', @roleTypeID = 1 </sql>
Stored Procedures
Add edit example this will add or edit if the record exsits already
<sql> -- ============================================= -- Author: Jeff Meyers -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[AddUpdatePerson] -- Add the parameters for the stored procedure here @UserName varchar(50), @OasisID varchar(50), @OasisEmail varchar(50), @Email varchar(50), @FirstName varchar(50), @LastName varchar(50), @PhoneNumber varchar(50) AS BEGIN DECLARE @PeopleID int
-- Insert statements for procedure here
SELECT @PeopleID = PeopleID from People WHERE UserName = @UserName
IF @PeopleID is null BEGIN INSERT into People (UserName, OasisID, OasisEmail, Email, FirstName, LastName, PhoneNumber) VALUES (@UserName, @OasisID, @OasisEmail, @Email, @FirstName, @LastName, @PhoneNumber)
select @@identity as PeopleID END ELSE BEGIN UPDATE People set UserName = @UserName, OasisID = @OasisID, OasisEmail = @OasisEmail , Email = @Email , FirstName = @FirstName , LastName = @LastName, PhoneNumber = @PhoneNumber, LastUpdate = GETDATE() WHERE PeopleID = @PeopleID
select @PeopleID as PeopleID END END
</sql>
and now the ADO way
http://iam.colum.edu/dd/classsource/class10/trans.aspx - source
HomeWork
Try to create a use logon page that inserts a new user.