Difference between revisions of "DD Class10"
(→Fifth Normal Form) |
(→Create a new user account) |
||
| Line 2: | Line 2: | ||
==Create a new user account== | ==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 acomplish this. | ||
| + | |||
| + | Test page one | ||
| + | |||
| + | Test page one with form | ||
| + | |||
| + | ===Stored Porcedures=== | ||
| + | |||
| + | <sql> | ||
| + | sproc demo | ||
| + | </sql> | ||
==Transactions== | ==Transactions== | ||
Revision as of 03:13, 4 April 2007
Contents
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 acomplish this.
Test page one
Test page one with form
Stored Porcedures
<sql> sproc demo </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 tracation.
Locks
Commit
Rollback
Stored Procedures
Fourth Normal Form
Fourth Normal Form
- separates independent multi-valued facts stored in one table into separate tables.
Fifth Normal Form
Fifth Normal Form
- breaks out data redundancy that is not covered by any of the previous normal forms.
HomeWork
Course adder example
http://iam.colum.edu/dd/classsource/class8/student.aspx
SQL Subquery
<sql>SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (
CourseID not in
(
SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
)
) </sql>
