-- http://www.phpmyadmin.net
--




-- --------------------------------------------------------

--
-- Table structure for table `Dependent`
--

CREATE TABLE IF NOT EXISTS `Dependent` (
  `Essn` char(9) COLLATE utf8_bin NOT NULL,
  `Name` varchar(15) COLLATE utf8_bin NOT NULL,
  `Sex` char(1) COLLATE utf8_bin DEFAULT NULL,
  `Bdate` date DEFAULT NULL,
  `Relationship` varchar(8) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Works_on table';

--
-- Dumping data for table `Dependent`
--

INSERT INTO `Dependent` (`Essn`, `Name`, `Sex`, `Bdate`, `Relationship`) VALUES
('123456789', 'Alice', 'F', '1988-12-30', 'Daughter'),
('123456789', 'Elizabeth', 'F', '1967-05-05', 'Spouse'),
('123456789', 'Michael', 'M', '1988-01-04', 'Son'),
('333445555', 'Alice', 'F', '1986-04-05', 'Daughter'),
('333445555', 'Joy', 'F', '1958-05-03', 'Spouse'),
('333445555', 'Theodore', 'M', '1983-10-25', 'Son'),
('987654321', 'Abner', 'M', '1942-02-28', 'Spouse');

-- --------------------------------------------------------

--
-- Table structure for table `Dept_locations`
--

CREATE TABLE IF NOT EXISTS `Dept_locations` (
  `Dnumber` int(11) NOT NULL,
  `Dlocation` varchar(15) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Department locations';

--
-- Dumping data for table `Dept_locations`
--

INSERT INTO `Dept_locations` (`Dnumber`, `Dlocation`) VALUES
(1, 'Houston'),
(4, 'Stafford'),
(5, 'Bellaire'),
(5, 'Houston'),
(5, 'Sugarland');

-- --------------------------------------------------------

--
-- Table structure for table `Project`
--

CREATE TABLE IF NOT EXISTS `Project` (
  `Pname` varchar(15) COLLATE utf8_bin NOT NULL,
  `Pnumber` int(11) NOT NULL,
  `Plocation` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `Dnum` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Project table';

--
-- Dumping data for table `Project`
--

INSERT INTO `Project` (`Pname`, `Pnumber`, `Plocation`, `Dnum`) VALUES
('ProductX', 1, 'Bellaire', 5),
('ProductY', 2, 'Sugarland', 5),
('ProductZ', 3, 'Houston', 5),
('Computerization', 10, 'Stafford', 4),
('Reorganization', 20, 'Houston', 1),
('Newbenefits', 30, 'Stafford', 4);

-- --------------------------------------------------------

--
-- Table structure for table `Works_on`
--

CREATE TABLE IF NOT EXISTS `Works_on` (
  `Essn` char(9) COLLATE utf8_bin NOT NULL,
  `Pno` int(11) NOT NULL,
  `Hours` decimal(3,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Works_on table';

--
-- Dumping data for table `Works_on`
--

INSERT INTO `Works_on` (`Essn`, `Pno`, `Hours`) VALUES
('123456789', 1, 32.5),
('123456789', 2, 7.5),
('333445555', 2, 10.0),
('333445555', 3, 10.0),
('333445555', 10, 10.0),
('333445555', 20, 10.0),
('453453453', 1, 20.0),
('453453453', 2, 20.0),
('666884444', 3, 40.0),
('888665555', 20, 0.0),
('987654321', 20, 15.0),
('987654321', 30, 20.0),
('987987987', 10, 35.0),
('987987987', 30, 5.0),
('999887777', 10, 10.0),
('999887777', 30, 30.0);

--
-- Indexes for table `Dependent`
--
ALTER TABLE `Dependent`
  ADD PRIMARY KEY (`Essn`,`Name`);

--
-- Indexes for table `Dept_locations`
--
ALTER TABLE `Dept_locations`
  ADD PRIMARY KEY (`Dnumber`,`Dlocation`);

--
-- Indexes for table `Project`
--
ALTER TABLE `Project`
  ADD PRIMARY KEY (`Pnumber`),
  ADD UNIQUE KEY `Pname` (`Pname`),
  ADD KEY `ProjDept` (`Dnum`);

--
-- Indexes for table `Works_on`
--
ALTER TABLE `Works_on`
  ADD PRIMARY KEY (`Essn`,`Pno`),
  ADD KEY `WorkProj` (`Pno`);

--
-- Constraints for table `Dependent`
--
ALTER TABLE `Dependent`
  ADD CONSTRAINT `EmpDep` FOREIGN KEY (`Essn`) REFERENCES `Employee` (`Ssn`) ON UPDATE CASCADE;

--
-- Constraints for table `Dept_locations`
--
ALTER TABLE `Dept_locations`
  ADD CONSTRAINT `DepLoc` FOREIGN KEY (`Dnumber`) REFERENCES `Department` (`Dnumber`) ON UPDATE CASCADE;

--
-- Constraints for table `Project`
--
ALTER TABLE `Project`
  ADD CONSTRAINT `ProjDept` FOREIGN KEY (`Dnum`) REFERENCES `Department` (`Dnumber`) ON UPDATE CASCADE;

--
-- Constraints for table `Works_on`
--
ALTER TABLE `Works_on`
  ADD CONSTRAINT `EmpWorks` FOREIGN KEY (`Essn`) REFERENCES `Employee` (`Ssn`) ON UPDATE CASCADE,
  ADD CONSTRAINT `WorkProj` FOREIGN KEY (`Pno`) REFERENCES `Project` (`Pnumber`) ON UPDATE CASCADE;

