SQL Exercise - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Exercise

Author

Topic

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 10:55:39

Here are the results from table

1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:06 AM
1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:07 AM
1000874 EEER 4/15/2008 60 452.32 S 4/13/08 8:06 AM
1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:08 AM
1000874 EES 4/13/2008 80 125.4 B 4/13/08 5:06 AM
1000874 EES 4/13/2008 450 126.8 B 4/13/08 5:06 AM

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 10:57:10

In this table, could you write the composite key?
What columns you would use from composite key?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-22 : 11:01:02

Dude. What's the point of taking a SQL class (which you obviously are) if you aren't going to at least try and present an answer.

why not post your answer and your reasoning. Or at least as much of an answer as you can work through. Then we'll be happy to correct you or offer you hints n tips. You won't learn anything if we just spoon feed you.

Hint -- try and work out the minimum information that uniquely identifies a row

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 11:02:15

Sorry, but I don't now answer for this question

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 11:03:06

quote:
Originally posted by y0zh

Sorry, but I don't now answer for this question

have you just started on your sql course? seems so seeing the load of questions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 11:04:28

This is the last question...yea, I've started and I have got a lot of question. I'll try to read much more but please, could you answer for this last question?
Thank you!

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 11:43:28

Could you explain why?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-22 : 11:45:51

Opps, didn't intend to post that.

Given what you've posted I'd have no idea what the answer is supposed to be. I have no idea what those columns represent.

Do you know what a composite key is? The definition of what a composit key is should be enough to give you the answer.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-22 : 11:47:52

OK. why don't you take a step back. This is actually a straightforward question but take it in small chunks.

Can I ask you:
What is a primary key?
&
What is a composite key?

~~Once you define those then the question should be pretty obvious from the data set that you posted.~~

From the data posted it's very hard to work out a good candidate.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 11:50:52

The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique

A composite key is a combination of more than one column to identify a unique row in a table.
For example, A customer table with Customer_ID, First Name, Last Name, Date of Birth may have a unique key in the customer ID, but a composite key may be LAST NAME, FIRST NAME and Date of Birth (Although, theoretically, there can be two John Smiths born on the 4th of July, 1776).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-22 : 12:03:12

OK -- so you've more or less defined what a primary key and a composite key are.

So, for your example dataset


1000874	EEER	4/13/2008	100	452.32	B	4/13/08 5:06 AM
1000874	EEER	4/13/2008	100	452.32	B	4/13/08 5:07 AM
1000874	EEER	4/15/2008	60	452.32	S	4/13/08 8:06 AM
1000874	EEER	4/13/2008	100	452.32	B	4/13/08 5:08 AM
1000874	EES	4/13/2008	80	125.4	B	4/13/08 5:06 AM
1000874	EES	4/13/2008	450	126.8	B	4/13/08 5:06 AM

You would need to work out which combinations of columns uniquely identify a row. That would then be a potential candidate for you compound primary key. Whether that combinations of columns is a good candidate based on other criteria (does it make business sense) then you may or may not wish to actually implement the key.

Btw -- why no column headers? makes it a bit hard to talk about the data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 12:24:01

ID NAMELEVEL DATEBEGIn COUNTSS SALARY TRUEFALSE DATEINSERT

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 12:32:57

What columns would you recommend for composition key?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-22 : 14:34:34

what's the min numbers of columns combined together make them unique?
can you tell?

quote:
Originally posted by y0zh

What columns would you recommend for composition key?

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 15:24:27

DATEINSERT & SALARY
DATEINSERT $ COUNTSS

Am I right?

So there are 2 composite keys?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:01:09

quote:
Originally posted by y0zh

DATEINSERT & SALARY
DATEINSERT $ COUNTSS

Am I right?

So there are 2 composite keys?

make one set which you feel will be most suitable based on business relevance as pk then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 04:40:00

How does the ID column relate to other tables in the the database?

Obviously this table is meaningless on its own. It doesn't tell you who has had the salary changed.......

If the ID is a relationship to another table (like an employee table) then you'll want to include that column in your key.

Otherwise you'd only allow one copy of an event rather the one copy of an event per employee..

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 06:53:53

I have not got any additional information. Perhaps, this is trick question, and I should choose the most suitable columns for compository key.

What columns would choose you ? I've already selected 2 choices

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 08:19:52

Without knowing what the table is for or how it is supposed to relate to other tables then there is no way to answer the question properly.

This is a tiny dataset and it obviously pertains to the same entity (they have the same ID value). I'm sorry but I really feel that from the question information there is no reliable way to identify the best key.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:24:26

yup...at least some idea on business relevance of data would have helped...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:59:56

And what is bad/wrong in this table?

1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:06 AM
1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:07 AM
1000874 EEER 4/15/2008 60 452.32 S 4/13/08 8:06 AM
1000874 EEER 4/13/2008 100 452.32 B 4/13/08 5:08 AM
1000874 EES 4/13/2008 80 125.4 B 4/13/08 5:06 AM
1000874 EES 4/13/2008 450 126.8 B 4/13/08 5:06 AM

I mean, if you see this data what should you think is not good and what you're willing to change?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources