Normalization

Hi all,

I am still confuse in normalization. (1st, 2nd, 3rd, and more.)

Any body have a detail idea abt that ?
Pls explain with example.

Thxs.

Regards,
Shailesh PatelThis question should be in the data forum...

a non normalized data table for a Company might have columns thus
tblCompany
company
address
city
state
firstname
lastname
telephone
fax
email

now imagine that the same company has 2 employees. the company info gets repeated for each employee. this wastes disk space and create the potential for data to also be incorrect. It also requires a lot of extra tdata entry to keep inputting the same company data.

so lets normalize it by splitting it into 2 tables. A company table and an employee table.

tblCompany
CompanyID
company
address
city
state

tblEmployee
CompanyID
firstname
lastname
telephone
fax
email

These 2 tables are linked on the CompanyID and you can now have many Employees for any single Company. Now one of those pesky employees decided to have 3 different email addresses. So lets normalize the Employee table

tblEmployee
EmployeeID
CompanyID
firstname
lastname

tblEmployeeContactInfo
EmployeeID
telephone
fax
email

These two tables are linked on EmployeeID. Now any single employee can have multiple contact records for an unlimited number of phone, fax or email data.|||Check out this page:

http://www.bkent.net/Doc/simple5.htmsql

Non-equi Joins using <>

Can we replace the where clause operator of '<>' with a join syntax condition.

Could someone explain me the dynamics behind the following query:

select a.b, c.d from a inner join c on a.b <> c.d

This could effectively replace the NOT IN and EXCEPT operators

No.. <> wont work as you expected. You query is similar to the following query,

select * from a cross join cWhere a.b <> c.d

Since it is set opertation your query will guide you to make a cross join and it only filter those values which are present on both the tables.

You have to use NOT IN or NOT EXISTS on your query. Join will be compared on both tables row by row. The IN & EXISTS will be verified with the one table value with other tables rows of value.

|||

Since, I am avoiding the use of NOT IN & NOT EXISTS due to its obvious perofrmance degradations, I also wouldn't use a Cross Join (which I think for tables with more rows would be terribly slow). Hence, would I get a Join query to have a performance upgrade over them.

|||

Hi there,

NOT IN and NOT EXISTS are no the same when it comes to performance. NOT EXISTS is much better in terms of performance especially if you're checking for a specific value as the query would return a TRUE value if a match is found and therefore avoid further iterations.

|||

I tend to use NOT EXISTS in these situations to take advantage of the semi-joins. Also, beware that the EXCEPT operator is typically slower.

Here are some previous threads that discuss NOT IN versus NOT EXISTS:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726903&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087482&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=637335&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654087&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

Here is a thread that discusses NOT IN versus EXCEPT:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1021998&SiteID=1

NOCASE attribute

Does someone could explain me the NOCASE attribute in the order by line?
I cant find that anywhere.
Thanks a lot,
venusgirrlhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvconquerydesignerconsiderationssqlserver.asp|||I read that article, bui i couldnt understand clearly for what is the NOCASE attribute.

Thank you,
venus|||The NOCASE attribute means that Upper/Lower case are considered the same.

Consider the strings "This is a test" and "this is a test". On a server that is case sensitive, those are different. On a server that is not case sensitive (in other words sp_server_info shows NOCASE on line 18), those strings are considered to be the same.

-PatP|||Thanks you !!!!!!! :))))

venus