Code: Select all
CREATE TABLE IF NOT EXISTS `chemical` (
`chemical_code` varchar(10) NOT NULL,
`name` varchar(20) NOT NULL,
`type` varchar(10) NOT NULL,
`notes` varchar(200) DEFAULT NULL,
PRIMARY KEY (`chemical_code`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `chemical`
--
INSERT INTO `chemical` (`chemical_code`, `name`, `type`, `notes`) VALUES
('B0008', 'Br2', 'Gas', 'frfr'),
('H0001', 'HCL', 'Solid', '50%'),
('H0002', 'HF', 'Gas', ''),
('H0004', 'H2', 'Gas', ''),
('H003', 'H2SO4', 'liquid', '56'),
('HA050', 'HAC 50%', 'Liquid', 'NEW'),
('S0005', 'SO2', 'Gas', ''),
('S5060', 'SO', 'Solid', '');
CREATE TABLE IF NOT EXISTS `lab` (
`lab_id` int(10) NOT NULL AUTO_INCREMENT,
`l_name` varchar(50) NOT NULL,
`incharge` varchar(50) NOT NULL,
`notes` varchar(250) DEFAULT NULL,
PRIMARY KEY (`lab_id`),
UNIQUE KEY `name` (`l_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `lab`
--
INSERT INTO `lab` (`lab_id`, `l_name`, `incharge`, `notes`) VALUES
(1, 'Organic Lab', 'Samanthi', 'ewewdsa'),
(2, 'Physical Lab', 'Kamal', ''),
(3, 'Inorganic Lab ', 'Sahan', ''),
(4, 'Advance Lab', 'Kamal', ''),
(6, 'BioChemistry Lab', 'dili', ''),
(7, 'Card Lab', 'sahan', 'abd'),
(8, 'Elementry Lab', 'dili', ''),
(9, 'New Biochemistry Lab', 'Sachin', ''),
(10, 'Phamacy Lab', 'Sachini', '');
CREATE TABLE IF NOT EXISTS `lab_employee` (
`lab_id` int(10) NOT NULL,
`emp_id` int(10) NOT NULL,
`labemp_id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`labemp_id`),
KEY `emp_id` (`emp_id`),
KEY `lab_id` (`lab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
INSERT INTO `lab_employee` (`lab_id`, `emp_id`, `labemp_id`) VALUES
(1, 175, 12),
(2, 175, 13),
(3, 175, 14),
(8, 175, 15),
(9, 175, 16),
(1, 114, 17),
(2, 114, 18),
(3, 114, 19);
CREATE TABLE IF NOT EXISTS `lab_inventory_chemical` (
`lab_id` int(10) NOT NULL,
`chemical_code` varchar(10) NOT NULL,
`quantity` decimal(10,2) NOT NULL,
`units` varchar(10) NOT NULL,
`reorder_level` decimal(10,2) NOT NULL,
`lic_id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`lic_id`),
KEY `lab_id` (`lab_id`),
KEY `chemical_code` (`chemical_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;
INSERT INTO `lab_inventory_chemical` (`lab_id`, `chemical_code`, `quantity`, `units`, `reorder_level`, `lic_id`) VALUES
(1, 'H0001', '40000.00', 'L', '808.00', 4),
(1, 'H003', '202.00', 'L', '56.00', 7),
(1, 'H0004', '85.00', 'L', '9.00', 9),
(1, 'B0008', '5000.00', 'l', '100.00', 13),
(3, 'B0008', '500.00', 'L', '50.00', 19),
(2, 'H0004', '800.00', 'L', '50.00', 20),
(2, 'HA050', '800.00', 'L', '504.00', 21),
(3, 'H003', '806.00', 'L', '56.00', 22),
(3, 'HA050', '806.00', 'L', '56.00', 23),
(3, 'S0005', '806.00', 'L', '56.00', 24);
CREATE TABLE IF NOT EXISTS `members` (
`emp_id` int(5) NOT NULL,
`login` varchar(10) NOT NULL,
`password` varchar(100) NOT NULL,
`type` varchar(20) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`emp_id`),
UNIQUE KEY `login` (`login`),
KEY `login_2` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `members` (`emp_id`, `login`, `password`, `type`, `email`) VALUES
(114, 'Chanake', '5d58c4a8bb8332d69cb9b152e9524ff4', 'labassistant', '[email protected]'),
(154, 'bobi', '51e4a5611b485ee7d5dcd421c092563b', 'admin', '[email protected]'),
(175, 'suba', '3538fc1dd47d3e92ac62dabd91d45aef', 'admin', '[email protected]');