SQL Questions

slipondajimmy · 07-09-2006, 11:01 AM

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

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, 11:16 AM

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, 11:38 AM

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, 12: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, 12: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, 06:43 PM

Anyone else?

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

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

slipondajimmy · 07-10-2006, 04: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, 11:16 AM	#2 (permalink)
Amnezia Professional Monkey Join Date: Jul 2005 Location: Escaped from the zoo Posts: 908 13.25 NP$ (Donate)	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 __________________ [http://www.webmasterwords.com/python-split-and-join-examples]Python Tutorials[/url]

07-09-2006, 11:38 AM	#3 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,010 117.50 NP$ (Donate)	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 __________________ Mascot Factory- Custom Plush, Promotional Plush, and Personalized Teddy Bears\|The Squidget - The tiny travel trailer you build from plans\|

07-09-2006, 12:20 PM	#5 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,010 117.50 NP$ (Donate)	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? __________________ Mascot Factory- Custom Plush, Promotional Plush, and Personalized Teddy Bears\|The Squidget - The tiny travel trailer you build from plans\|

07-09-2006, 06:43 PM	#6 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,010 117.50 NP$ (Donate)	Anyone else? __________________ Mascot Factory- Custom Plush, Promotional Plush, and Personalized Teddy Bears\|The Squidget - The tiny travel trailer you build from plans\|

07-10-2006, 04:43 PM	#8 (permalink)
slipondajimmy Senior Member Join Date: Nov 2003 Location: Florida Posts: 2,010 117.50 NP$ (Donate)	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? __________________ Mascot Factory- Custom Plush, Promotional Plush, and Personalized Teddy Bears\|The Squidget - The tiny travel trailer you build from plans\|

07-09-2006, 12:08 PM	#4 (permalink)
Peter Senior Member Join Date: Nov 2003 Location: Scotland Posts: 4,900 0.60 NP$ (Donate)	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-10-2006, 08:06 AM	#7 (permalink)
Tree NamePros Regular Join Date: Feb 2006 Location: Atlanta, GA, USA Posts: 335 13.25 NP$ (Donate)	Do some research, I'm sure you'll figure it out.

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