Saturday, 28 February 2009


I didn't put the whole thing in an organised manner, but still it gives you everything you need except ERD. I made an ERD on paper and try to put that on internet as soon as possible.

OK, let's start. :)

I put the sql code for creating database and tables in the last post. You can check it first. After creating the database and tables, you need to populate the tables, meaning you need to enter some information into the database.

This sql code below will do the work for you:


INSERT INTO `employees` (`employee_id`, `name`, `address`, `city`, `dob`, `current_job`, `skills`, `projects`) VALUES
(NULL, 'John Paypal', '96 Shadwell Gardens', 'London', '1970-10-20', 'engineer', 'prepare requisitions, checking drawings', 'Millenium Dome, London Crossing'),
(NULL, 'Max Levchin', '126 Kingston Avenue', 'Liecester', '1978-09-25', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Sergey Brin', '95 Queenland Square', 'Manchester', '1980-10-30', 'secretary', 'typing', 'Millenium Dome, London Crossing'),
(NULL, 'Larisa Palmall', '97 Maryland Street', 'Newcastle', '1980-10-25', 'engineer', 'prepare requisitions', 'Millenium Dome, London Crossing, Channel Tunnel'),
(NULL, 'Eric Schmit', '124 Google Street', 'London', '1968-10-22', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Marissa Meyer', '18 Mary Street', 'Blackpool', '1982-10-24', 'secretary', 'typing', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Maisha Frost', '12 Lower Thames Street', 'London', '1965-10-20', 'secretary', 'typing', 'Millenium Dome'),
(NULL, 'Paul Scott', '23 Lisbon Street', 'Blackpool', '1972-09-15', 'engineer', 'checking drawings', 'London Crossing, Channel Tunnel'),
(NULL, 'Andrew Miliband', '10 Case Street', 'Manchester', '1982-04-10', 'secretary', 'typing', 'London Crossing'),
(NULL, 'Patrick Batchmen', '97 Russkin Street', 'Kent', '1959-11-25', 'engineer', 'checking drawings', 'Channel Tunnel'),
(NULL, 'Adam Garcia', '12 Gobbles Street', 'Kent', '1978-06-20', 'foreman', 'prepare requisitions, checking drawings', 'Millenium Dome, Channel Tunnel'),
(NULL, 'Harriet Harman', '77 Bow Street', 'Blackpool', '1982-10-24', 'secretary', 'typing', 'Channel Tunnel');

INSERT INTO `engineer` (`engineer_id`, `employee_id`, `type_of_degree`) VALUES
(NULL, 1, 'electrical'),
(NULL, 4, 'mechanical'),
(NULL, 8, 'mechanical'),
(NULL, 10, 'electrical');

INSERT INTO `secretary` (`secretary_id`, `employee_id`, `typing_speed`) VALUES
(NULL, 3, 100),
(NULL, 6, 70),
(NULL, 7, 85),
(NULL, 9, 75),
(NULL, 12, 110);

INSERT INTO `married` (`employee_id`, `marriage_date`, `to_whom`) VALUES
(1, '1995-02-08', 'Larisa Palmall'),
(3, '2007-03-19', 'Marissa Meyer'),
(7, '1988-10-26', 'Andrew Miliband');

INSERT INTO `skills` (`skill_no`, `skill_name`, `description`) VALUES
(101020, 'preparing requsitions', 'identifying and checking the quality of the materials'),
(102030, 'checking drawings', 'checking drawings of all the newly installed cupboards');

INSERT INTO `departments` (`dept_name`, `tel_no`) VALUES
('engineering', 44021234561),
('secretarial', 44021234562),
('research', 44021234563),
('sales', 44021234564),
('aqusitions', 44021234565),
('human resources', 44021234566),
('technitions', 44021234567),
('programmers', 44021234568),
('database admins', 44021234569),
('health and safety', 440212345610),
('scientists', 440212345611);

INSERT INTO `vendors` (`name`, `address`, `equipment_type`, `last_meet_date`) VALUES
('Supplyforce inc', '96 shadwell avenue, London', 'soil densimeter', '2009-02-10'),
('Forgenet suppliers inc', '87 monty python street, London', 'ss shape sensor', '2008-12-29'),
('Magnesium Engineering', '123 Fleet Street, Blackpool', 'electric sample ejector', '2009-03-04'),
('Procter inc', '67 Densel Street, Manchester', 'permeameter', '2009-03-17');

INSERT INTO `projects` (`project_no`, `project_name`, `est_cost`) VALUES
(123, 'East London Crossing', 12000000),
(124, 'Channel Tunnel', 15000000),
(125, 'Millennium Dome', 1000000);

INSERT INTO `city` (`city_name`, `city_state`, `population`) VALUES
('London', 'London', 5000000),
('Manchester', 'Manchester', 2000000),
('Kent', 'Kent', 1000000),
('Blackpool', 'Southwestern', 30000),
('Liecester', 'Liecester', 35000);


See, if it works fine when you copy and paste this code on phpmyadmin.
Give me a shout, if you have a problem.
Good luck!


No comments:

Post a Comment