Question

Task for today: 1. Create all the tables for Database company. Focus to the PK and FK 2. Practice INSERT/UPDATE/DELETE. 3. Test the 3 constraints What to submit? • After you complete question 1 and 2 above, provide the screenshot of the database with the tables (see my example below). + O toshosphoryddbructurephol phpMyAdmin 001) Structure Tavares Import Operations Privileges Routies Events 1 depeno - i ch Www ESSE w Structure Search Empy Drop Sector Seach Drop D we Sucre Search Himply Drops Sue Search Empty Dap Search Empy Drop Suche Sache n Doe- oplocation my + Ett w w The test code from 3 rd question above and error, if any, you received when you tested the 3 constraints

Answer 1

1
-- Database: `company`
--

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

--
-- Table structure for table `department`
--

CREATE TABLE `department` (
`dname` varchar(45) NOT NULL,
`dnumber` int(11) NOT NULL,
`mgr_ssn` int(11) NOT NULL,
`mgr_start_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `dependent`
--

CREATE TABLE `dependent` (
`essn` int(11) NOT NULL,
`dependent_name` varchar(45) NOT NULL,
`sex` varchar(45) NOT NULL,
`bdate` date NOT NULL,
`relationship` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `dept_locations`
--

CREATE TABLE `dept_locations` (
`dnumber` int(11) NOT NULL,
`dlocation` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
`fname` varchar(45) NOT NULL,
`minit` varchar(45) NOT NULL,
`lname` varchar(45) NOT NULL,
`ssn` int(11) NOT NULL,
`bdate` date NOT NULL,
`address` varchar(45) NOT NULL,
`sex` varchar(45) NOT NULL,
`salary` int(11) NOT NULL,
`super_ssn` int(11) NOT NULL,
`dno` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `project`
--

CREATE TABLE `project` (
`pname` varchar(45) NOT NULL,
`pnumber` int(11) NOT NULL,
`plocation` varchar(45) NOT NULL,
`dnum` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `works_on`
--

CREATE TABLE `works_on` (
`essn` int(11) NOT NULL,
`pno` int(11) NOT NULL,
`hours` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `department`
--
ALTER TABLE `department`
ADD PRIMARY KEY (`dnumber`),
ADD KEY `mgr_ssn` (`mgr_ssn`);

--
-- Indexes for table `dependent`
--
ALTER TABLE `dependent`
ADD PRIMARY KEY (`essn`,`dependent_name`);

--
-- Indexes for table `dept_locations`
--
ALTER TABLE `dept_locations`
ADD PRIMARY KEY (`dnumber`,`dlocation`);

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
ADD PRIMARY KEY (`ssn`),
ADD KEY `super_ssn` (`super_ssn`);

--
-- Indexes for table `project`
--
ALTER TABLE `project`
ADD PRIMARY KEY (`pnumber`),
ADD KEY `dnum` (`dnum`);

--
-- Indexes for table `works_on`
--
ALTER TABLE `works_on`
ADD PRIMARY KEY (`essn`,`pno`),
ADD KEY `pno` (`pno`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `department`
--
ALTER TABLE `department`
ADD CONSTRAINT `mgr_ssn` FOREIGN KEY (`mgr_ssn`) REFERENCES `employee` (`ssn`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `dependent`
--
ALTER TABLE `dependent`
ADD CONSTRAINT `fk_essn` FOREIGN KEY (`essn`) REFERENCES `employee` (`ssn`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `dept_locations`
--
ALTER TABLE `dept_locations`
ADD CONSTRAINT `dnumber` FOREIGN KEY (`dnumber`) REFERENCES `department` (`dnumber`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `employee`
--
ALTER TABLE `employee`
ADD CONSTRAINT `super_ssn` FOREIGN KEY (`super_ssn`) REFERENCES `employee` (`ssn`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `project`
--
ALTER TABLE `project`
ADD CONSTRAINT `dnum` FOREIGN KEY (`dnum`) REFERENCES `department` (`dnumber`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `works_on`
--
ALTER TABLE `works_on`
ADD CONSTRAINT `essn` FOREIGN KEY (`essn`) REFERENCES `employee` (`ssn`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `pno` FOREIGN KEY (`pno`) REFERENCES `project` (`pnumber`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

✓ O host/phpmyadmin/db_structure.php?server=1&db=company - Server: localhost » Database: company Structure SQL Search query Export Import Operations - Privileges More Filters Containing the word: Rows Table - Action Type Collation Size Overhead O Search Insert Empty Drop InnoDB utf8mb4_general_ci k department dependent * ☆ D Browse hi Structure Browse Structure Search i Insert Empty Drop InnoDB utf8mb4_general_ci ki KiB O dept_locations * D Browse Structure Search Insert Empty Drop InnoDB utf8mb4_general_ci k ib O employee I Browse Structure + Search Insert Empty Drop InnoDB utf8mb4_general_ci KIER O InnoDB utf8mb4_general_ci project works on 6 tables * ☆ Sum Browse Browse Structure Structure Search Insert Search 3 Insert Empty Empty Drop Drop k ik InnoDB utf8mb4_general_ci InnoDB utf8mb4_general_ci KiB OB with selected: L O Print Check all Data dictionary Create table Name: Number of columns: 4 Console

########################################################

Give a ThumbsUp if you found this answer Helpful :)
#######################################################

In the Case of multiple questions,we(Experts) should answer only certain number of sub-parts\questions.So kindly ask the remaining sub-parts\questions as a new query. Sorry for the inconvenience caused.

################################################################
............ Still having trouble understanding the answer ? Feel free to ask in the comment section.......