Jump to content


Photo

Job Tables In Ch 9


  • Please log in to reply
7 replies to this topic

#1 ColleenDMA16

ColleenDMA16

    New Member

  • Members
  • Pip
  • 5 posts

Posted 08 January 2010 - 11:08 AM

Most of the tables are listed on this site...but now I am in Chapter 9...and they are no place in site.

Does anyone have the data from Chapter 9...the jobs tables?

Thanks,

Colleen

#2 AndrewSQLDBA

AndrewSQLDBA

    Active Member

  • Members
  • PipPip
  • 44 posts

Posted 08 January 2010 - 01:16 PM

You can create your own, they do not have to match exactly what is in the book.

I created my own for a different book. It will be good experience.

Andrew SQLDBA

#3 ColleenDMA16

ColleenDMA16

    New Member

  • Members
  • Pip
  • 5 posts

Posted 08 January 2010 - 01:28 PM

QUOTE (AndrewSQLDBA @ Jan 8 2010, 01:16 PM) <{POST_SNAPBACK}>
You can create your own, they do not have to match exactly what is in the book.

I created my own for a different book. It will be good experience.

Andrew SQLDBA


It's just easier when it matches the book.

Thanks for the reply.

#4 ColleenDMA16

ColleenDMA16

    New Member

  • Members
  • Pip
  • 5 posts

Posted 11 January 2010 - 09:50 AM

While at 1st glance this might seem like good practice...table keys are still something I am working on...and doing it myself I did not get the keys correct.

If anyone has the way to create and populate these tables it would be help me a lot.

Thanks

#5 AndrewSQLDBA

AndrewSQLDBA

    Active Member

  • Members
  • PipPip
  • 44 posts

Posted 13 January 2010 - 09:07 AM

When I need to populate things like list table, or data tables, and I already have the data in hand, I use Excel to properly layout the tables and then pump the data in to SQL. SQL Server has wonderful data pumps.

If you are using express edition, you will have to insert each row manually. I do not think Express has the ETL capability.

I would suggest that you use two stored procedures for each relational table insert. That means......
When you have one table and one related table. Then you will have two insert statements in one stored procedure. The key constraints will not allow you to make a mistake. You insert the data into the parent table and then into the child table using the Pri Key from the parent. This Table relationship is between a MemberBilling table and Order table. This would be on a shopping cart site. the tables are MemberBilling and Order. The Member can have 1 or more Orders that are in the Order. One Home, and possibly one to ship too. This will allow the site to store all history about the user, so when they come back, they will not have to fill out all the shipping address info all over again.

This is a sample of the tables that will store MemberBilling and Orders that the Member has purchased. You can see they are related by the MemberBillingRowID column in both tables. This column is an Auto-Increment in the MemberBilling table but not incrementing in the Orders table. You will take the ID value form the insert of the new row into the MemberBilling table and use that as one of the input parameters in the Orders table. So, that means that the same Member can have 1 or many orders, but they can be all shipped to the same address, or many different addresses.

The small list tables are included to give you an idea of how to store data that never changes in a list table, that is also related to the MemberBilling and the Orders table. Things like the Statenames in the list table. This keeps users from having to manually type in this data, and you will not have spelling mistakes. You will store the ID value of the text represented. Databases do not like to deal with text as quick and as easy as they deal with numeric values.

So you will see in the Order table, the MemberBilling ID listed more than once, that is because that same Member, has made more than one purchase.

CODE



State Table

StateID int
StateName varchar(50)
ShowInList bit

Country Table

CountryID int
CountryName varchar(250)
ShowInList bit

PhoneType Table

PhoneTypeID int
PhoneType varchar(25)
ShowInList bit


MemberBilling Table DDL

MemberBillingRowID int
MemberBillingFirstName varchar(25)
MemeberBillingMiddleName varchar(25)
MemberBillingLastName varchar(25)
MemberBillingAddress varchar(50)
MemberBillingSuite varchar(5)
MemberBillingCity varchar(75)
MemberBillingStateID int
MemberBillingCountryID int
MemberBillingPostalCode int
MemberEmailAddress varchar(500)
MemberPassword varchar(500)
MemberBillingAreaCode int
MemberBillingPhone int
MemberBillingPhoneTypeID int

Orders Table DDL

OrderRowID int
MemberBillingRowID int
OrderAddress varchar(50)
OrderSuite varchar(5)
OrderCity varchar(75)
OrderStateID int
OrderCountryID int
OrderPostalCode varchar(15)
OrderEmailAddress varchar(75)
OrderPhoneTypeID int
OrderAreaCode int
OrderPhoneNumber int
OrderTrackingNumber varchar(50)
OrderShipDate datetime
OrderDelivered bit



Hope this helps with the issue that you are having with Primary and Foreign Keys. They are only Constraints that allow you to relate tables together, and keep track of what data belongs to what row in another table.

Andrew SQLDBA

#6 ColleenDMA16

ColleenDMA16

    New Member

  • Members
  • Pip
  • 5 posts

Posted 21 January 2010 - 08:49 AM

Andrew thanks for the reply.

Do anyone have the data they are using for this chapter...or the next...the girls table or the piggy_bank table.

It's really hard to following the excercises...when I don't know what data is in the table...so my work does not match the books.

I contacted Head First directly...but not place.

Colleen



#7 mahv

mahv

    New Member

  • Members
  • Pip
  • 2 posts

Posted 17 September 2010 - 08:32 PM

QUOTE (ColleenDMA16 @ Jan 11 2010, 09:50 AM) <{POST_SNAPBACK}>
While at 1st glance this might seem like good practice...table keys are still something I am working on...and doing it myself I did not get the keys correct.

If anyone has the way to create and populate these tables it would be help me a lot.

Thanks


After some research I came up with a way to create and populate the job_current table with random dates and salaries.


CREATE TABLE job_current ( contact_id INT(11), title VARCHAR(20), salary INT(7), start_date DATE );

INSERT into job_current (contact_id, title, salary, start_date )
SELECT mc.contact_id, prof.profession,
FLOOR (1 + RAND() * 100000),
str_to_date( concat( floor( 1 + rand() * (12-1)), '-', floor(1 + rand() * (28 -1)), '-', '2009' ), '%m-%d-%Y')
FROM my_contacts AS mc
INNER JOIN profession AS prof WHERE mc.prof_id = prof.prof_id;

I'm still working on job_desired and job_listings but with the above RAND function it shouldn't be too hard.

Hope this helps.





#8 mgibson12

mgibson12

    New Member

  • Members
  • Pip
  • 2 posts

Posted 04 May 2014 - 03:09 PM

This is a slight improvement to MAHV's suggestion. I changed the str_to_date() to make the year have a random number between 1999 and 2013.

INSERT into job_current (contact_id, title, salary, start_date )
SELECT c.contact_id, prof.profession,
FLOOR (9000 + RAND() * 100000),
str_to_date( concat( floor( 1 + rand() * (12-1)), '-', floor(1 + rand() * (28 -1)), '-', (1999 + FLOOR(RAND() * (2013-1999 +1)))), '%m-%d-%Y')
FROM contacts AS c
INNER JOIN profession AS prof WHERE c.prof_id = prof.prof_id;

Edited by mgibson12, 04 May 2014 - 03:11 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users