Retrieval data from website by script (SP20100918)

Project Description:
** Brief description
Retrieve all companies information/addresses from a web dictonary (exhibition) and store them in a SQL database
for future processing in a marketing departement.

** Decription
Retrieve (automatically, programly, using a program) and fill-in
- SQL table company (addresses, type of business and type of companies)
- SQL table contactpersons,
from a exhibition website and store these data into a SQL database (mysql). A basic draft of the database schema is defined below.

To avail mistakes and also because of the amount of companies
(about 4k to 6k), the data retrieval should be done using a small, selfwritten program/script.

This program/script is also part of the project.


** Task 1:
Create inital database by retrieving
all companies and contact persons from the 12 "display categories" on
http://www.cebit.de/search_akl (linked from linked from http://www.cebit.de/exhibitorsproducts)
like "ICT Infrastructure", "Business IT", ..

Sample: categories "ICT Infrastructure": 594 companies

This task will fill-in the SQL-table "Company" and "ContactPerson"

Do avoid duplicates of companies. Be carefull - companies could be
in more than one categories.


** Task 2:
Qualify all retrieved companies by "Section" and "ProductCategory"
using the qualification links

ProductCategory # http://www.cebit.de/search_pg
Section # http://www.cebit.de/search_zb

Therefore, setup initialize first the 2 qualification tables
ProductCategory
Section
manual (?) and than set the relating ProductCategory and Section
to each company in the database:
company.PC_ID # foreign key to ProductCategory.ID with the higest level
company.SI_ID # foreign key to Section.ID with the higest level



** Project output:
- SQL-Dump with all companies, related information and qualifications like defined in the tasks.
- Program doing the retrieval of data from the website.
including a small README/HOWTO how to use this script.


** Draft Database schema

TABLE company # source: http://www.cebit.de/search_akl (go throught 12 display categories)
ID
# main information
name1
name2
address1
address2
postcal code
city
country

Telephone1
Fax1
website

subject

# PertinentFacts
Type of company
AnnualTurnover
AnnualTurnoverDate
Employees
EmployeesDate

# location cebit2010
hall:
Stand:

PC_ID # foreign key to ProductCategory.ID with the higest level
SI_ID # foreign key to Section.ID with the higest level

TABLE ContactPerson # source: http://www.cebit.de/search_akl (go throught 12 display categories)
ID
Name
Type
Telephone
Fax
Email
Mobile
companyId # foreign key to company.ID


TABLE ProductCategory # source: http://www.cebit.de/search_pg
ID
Name
ParentPC_ID # foreign key to ProductCategory.ID, NULL if it's the root ProductCategory (no parent)

TABLE Section # source: http://www.cebit.de/search_zb
ID
Name
ParentSI_ID # foreign key to Section.ID, NULL if it's the root section (no parent)