SQL Questions

slipondajimmy · 07-09-2006, 12:01 PM

Hey everyone I posted in the break room about a opp. My boss presented me with and it involves learning SQL. I have been reading for a few days and its starting to sink in somewhat. But I have a few questions . Lets start by telling you what he wants me to do.... and he told me yesterday i should be able to do it by tommorow (monday)

Accounting system
Set up a database on local comp.
Set up acual database
Select/insert/Update/Delete with joins
????: NamePros.com http://www.namepros.com/programming/215159-sql-questions.html

Transaction
TransactionID
AccountID
EntityID
TypeID
ClassificationID
Amount
DateWritten
DateCleared
CheckNumber

Account
AccountID
Name
StartDate
StartAmount

AccountStatement
AccountID
DateStart
DateEnd
Deposits
Withdrawals

Entity
EntityID
Name

Classification-For if it's and expense example"Expense-Employee" or "Deposit-MErchant Account"
ClassificationID
NAme

Type-Check/Credit Card
TypeID
Name

I have no problem setting up the Database in Enterprise manager. The tables are easy. were I am having problems in desiding what is a primary key and such. If I am reading correct a primary key is to distinguish unique info? Also I can figure out what the best way to join this all together.

Is there anyone out there that can help me on this one? Here is what I have so far.

Amnezia · 07-09-2006, 12:16 PM

Yes the primary key is used to distinguish each individual record in a table. It is good practise to have at least one primary key per table. When creating the joins the easiest kind is an equi-join where the primary key of one table maps directly to a field in another table. For example you could join the tables Account and Transaction by linking the AccountID in transaction to the AccountID in Account using something like this:

SELECT* FROM Account,Transaction
WHERE Account.AccountID = Transaction.AccountID

slipondajimmy · 07-09-2006, 12:38 PM

OK so would both the AccountID fields need to be primary keys to join them? It seems to me there is alot that needs to be joined, but not much that needs to be unique

Peter · 07-09-2006, 01:08 PM

no if possible Transaction.AccountID should be a foreign key (but depending on the database engine and version number it is not always possible, for example versions before mysql 5 did not support foreign keys).

slipondajimmy · 07-09-2006, 01:20 PM

Yeah im pretty sure I wont be able to do this by tommorow..BLAH

Cant anyone just look at the diagram and tell me what I need to do?

slipondajimmy · 07-09-2006, 07:43 PM

Anyone else?

Tree · 07-10-2006, 09:06 AM

Do some research, I'm sure you'll figure it out.

slipondajimmy · 07-10-2006, 05:43 PM

I do have another dumb question. I have sql enterprise manager set up on my home computer. is it possible to have more then one server setup? if so how do you do it?

07-09-2006, 12:16 PM	#2 (permalink)
Amnezia Professional Monkey Join Date: Jul 2005 Location: Escaped from the zoo Posts: 907	Yes the primary key is used to distinguish each individual record in a table. It is good practise to have at least one primary key per table. When creating the joins the easiest kind is an equi-join where the primary key of one table maps directly to a field in another table. For example you could join the tables Account and Transaction by linking the AccountID in transaction to the AccountID in Account using something like this: SELECT* FROM Account,Transaction WHERE Account.AccountID = Transaction.AccountID __________________ Webmaster Words

07-09-2006, 12:38 PM	THREAD STARTER #3 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,026	OK so would both the AccountID fields need to be primary keys to join them? It seems to me there is alot that needs to be joined, but not much that needs to be unique

07-09-2006, 01:08 PM	#4 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,069	no if possible Transaction.AccountID should be a foreign key (but depending on the database engine and version number it is not always possible, for example versions before mysql 5 did not support foreign keys).

07-09-2006, 01:20 PM	THREAD STARTER #5 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,026	Yeah im pretty sure I wont be able to do this by tommorow..BLAH Cant anyone just look at the diagram and tell me what I need to do?

07-09-2006, 07:43 PM	THREAD STARTER #6 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,026	Anyone else?

07-10-2006, 09:06 AM	#7 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335	Do some research, I'm sure you'll figure it out.

07-10-2006, 05:43 PM	THREAD STARTER #8 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,026	I do have another dumb question. I have sql enterprise manager set up on my home computer. is it possible to have more then one server setup? if so how do you do it?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)