I have created the following SQL code found at the bottom of this forum post
It seems however, in terms of feedback, I have these two issues:-
- One of my DML statements are broken
- I may have not considered enough many-to-many relationships for this problem which may indicate missing steps from the normalization
As I am extremely new to SQL, I am not too sure where I am able to fix these issues. If anyone could take a look at my code and tell me exactly what I need to modify, I would be so appreciative. Thank you so much.
-- Table structure for table `clients`
--
CREATE TABLE `clients` (
`client_id` int(11) NOT NULL,
`organisation_name` varchar(255) NOT NULL,
`contact_first_name` varchar(255) NOT NULL,
`contact_last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`preferred_contact_method` enum('post','email') NOT NULL,
`project_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `clients`
--
INSERT INTO `clients` (`client_id`, `organisation_name`, `contact_first_name`, `contact_last_name`, `email`, `address`, `preferred_contact_method`, `project_id`) VALUES
(1, 'Acme Corporation', 'John', 'Doe', 'john.doe@acme.com', '123 Main St, Anytown, USA', 'email', 1),
(2, 'Globex Corporation', 'Jane', 'Doe', 'jane.doe@globex.com', '456 Oak Ave, Anycity, USA', 'post', 2),
(3, 'Stark Industries', 'Tony', 'Stark', 'tony.stark@starkindustries.com', '10880 Malibu Point, Malibu, USA', 'email', 3);
-- --------------------------------------------------------
--
-- Table structure for table `pool_members`
--
CREATE TABLE `pool_members` (
`pool_member_id` int(11) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone_number` varchar(20) NOT NULL,
`work_address` varchar(255) NOT NULL,
`home_address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `pool_members`
--
INSERT INTO `pool_members` (`pool_member_id`, `first_name`, `last_name`, `email`, `phone_number`, `work_address`, `home_address`) VALUES
(1, 'Alice', 'Smith', 'alice.smith@gmail.com', '123-456-7890', '789 Elm St, Anytown, USA', '123 Maple St, Anytown, USA'),
(2, 'Bob', 'Johnson', 'bob.johnson@yahoo.com', '555-555-5555', '456 Oak Ave, Anycity, USA', '789 Elm St, Anycity, USA'),
(3, 'Charlie', 'Brown', 'charlie.brown@hotmail.com', '555-123-4567', '321 Pine St, Anytown, USA', '456 Oak Ave, Anytown, USA'),
(4, 'Dave', 'Wilson', 'dave.wilson@outlook.com', '555-987-6543', '567 Oak St, Anycity, USA', '321 Pine St, Anycity, USA'),
(5, 'Alice', 'Smith', 'alice.smith@gmail.com', '123-456-7890', '789 Elm St, Anytown, USA', '123 Maple St, Anytown, USA'),
(6, 'Bob', 'Johnson', 'bob.johnson@yahoo.com', '555-555-5555', '456 Oak Ave, Anycity, USA', '789 Elm St, Anycity, USA'),
(7, 'Alice', 'Smith', 'alice.smith@gmail.com', '123-456-7890', '789 Elm St, Anytown, USA', '123 Maple St, Anytown, USA'),
(8, 'Bob', 'Johnson', 'bob.johnson@yahoo.com', '555-555-5555', '456 Oak Ave, Anycity, USA', '789 Elm St, Anycity, USA'),
(9, 'Alice', 'Smith', 'alice.smith@gmail.com', '123-456-7890', '789 Elm St, Anytown, USA', '123 Maple St, Anytown, USA'),
(10, 'Bob', 'Johnson', 'bob.johnson@yahoo.com', '555-555-5555', '456 Oak Ave, Anycity, USA', '789 Elm St, Anycity, USA');
-- --------------------------------------------------------
--
-- Table structure for table `pool_members_skills`
--
CREATE TABLE `pool_members_skills` (
`pool_member_id` int(11) NOT NULL,
`skill_id` int(11) NOT NULL,
`experience_level` enum('Expert','Intermediate','Junior') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `pool_members_skills`
--
INSERT INTO `pool_members_skills` (`pool_member_id`, `skill_id`, `experience_level`) VALUES
(1, 1, 'Expert'),
(1, 2, 'Intermediate'),
(1, 3, 'Junior'),
(2, 2, 'Expert'),
(2, 4, 'Intermediate'),
(2, 6, 'Expert'),
(3, 1, 'Junior'),
(3, 5, 'Intermediate'),
(3, 7, 'Expert'),
(4, 2, 'Expert'),
(4, 3, 'Intermediate'),
(4, 5, 'Expert'),
(4, 8, 'Intermediate'),
(4, 10, 'Expert');
-- --------------------------------------------------------
--
-- Table structure for table `projects`
--
CREATE TABLE `projects` (
`project_id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`budget` decimal(10,2) NOT NULL,
`description` varchar(255) NOT NULL,
`phase` enum('Design','Development','Testing','Deployment','Complete') NOT NULL,
`skill1` varchar(255) NOT NULL DEFAULT '',
`skill2` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `projects`
--
INSERT INTO `projects` (`project_id`, `title`, `start_date`, `end_date`, `budget`, `description`, `phase`, `skill1`, `skill2`) VALUES
(1, 'Project A', '2023-01-01', '2023-06-30', '100000.00', 'Description for Project A', 'Design', 'Coding', 'Database Management'),
(2, 'Project B', '2023-03-01', '2023-12-31', '200000.00', 'Description for Project B', 'Development', '', ''),
(3, 'Project C', '2023-05-01', '2024-04-30', '300000.00', 'Description for Project C', 'Testing', '', '');
-- --------------------------------------------------------
--
-- Table structure for table `project_members`
--
CREATE TABLE `project_members` (
`project_members_id` int(11) NOT NULL,
`project_id` int(11) NOT NULL,
`pool_member_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `project_members`
--
INSERT INTO `project_members` (`project_members_id`, `project_id`, `pool_member_id`) VALUES
(25, 1, 1),
(26, 1, 2),
(27, 2, 2),
(28, 2, 3),
(29, 2, 4);
-- --------------------------------------------------------
--
-- Table structure for table `skills`
--
CREATE TABLE `skills` (
`skill_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`type` enum('Backend','Frontend','Testing','Other') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `skills`
--
INSERT INTO `skills` (`skill_id`, `name`, `type`) VALUES
(1, 'Java', 'Backend'),
(2, 'JavaScript', 'Frontend'),
(3, 'HTML', 'Frontend'),
(4, 'CSS', 'Frontend'),
(5, 'PHP', 'Backend'),
(6, 'Python', 'Backend'),
(7, 'React', 'Frontend'),
(8, 'Angular', 'Frontend'),
(9, 'JUnit', 'Testing'),
(10, 'Selenium', 'Testing'),
(11, 'Other Skill 1', 'Other'),
(12, 'Other Skill 2', 'Other'),
(13, 'Java', 'Backend'),
(14, 'JavaScript', 'Frontend'),
(15, 'HTML', 'Frontend'),
(16, 'CSS', 'Frontend'),
(17, 'PHP', 'Backend'),
(18, 'Python', 'Backend'),
(19, 'Java', 'Backend'),
(20, 'JavaScript', 'Frontend'),
(21, 'HTML', 'Frontend'),
(22, 'CSS', 'Frontend'),
(23, 'PHP', 'Backend'),
(24, 'Python', 'Backend'),
(25, 'Java', 'Backend'),
(26, 'JavaScript', 'Frontend'),
(27, 'HTML', 'Frontend'),
(28, 'CSS', 'Frontend'),
(29, 'PHP', 'Backend'),
(30, 'Python', 'Backend');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
ADD PRIMARY KEY (`client_id`),
ADD KEY `fk_project_id` (`project_id`);
--
-- Indexes for table `pool_members`
--
ALTER TABLE `pool_members`
ADD PRIMARY KEY (`pool_member_id`);
--
-- Indexes for table `pool_members_skills`
--
ALTER TABLE `pool_members_skills`
ADD PRIMARY KEY (`pool_member_id`,`skill_id`),
ADD KEY `skill_id` (`skill_id`);
--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
ADD PRIMARY KEY (`project_id`);
--
-- Indexes for table `project_members`
--
ALTER TABLE `project_members`
ADD PRIMARY KEY (`project_members_id`),
ADD KEY `project_id` (`project_id`),
ADD KEY `pool_member_id` (`pool_member_id`);
--
-- Indexes for table `skills`
--
ALTER TABLE `skills`
ADD PRIMARY KEY (`skill_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
MODIFY `client_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `pool_members`
--
ALTER TABLE `pool_members`
MODIFY `pool_member_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
MODIFY `project_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `project_members`
--
ALTER TABLE `project_members`
MODIFY `project_members_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;
--
-- AUTO_INCREMENT for table `skills`
--
ALTER TABLE `skills`
MODIFY `skill_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `clients`
--
ALTER TABLE `clients`
ADD CONSTRAINT `fk_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`);
--
-- Constraints for table `pool_members_skills`
--
ALTER TABLE `pool_members_skills`
ADD CONSTRAINT `pool_members_skills_ibfk_1` FOREIGN KEY (`pool_member_id`) REFERENCES `pool_members` (`pool_member_id`) ON DELETE CASCADE,
ADD CONSTRAINT `pool_members_skills_ibfk_2` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`skill_id`) ON DELETE CASCADE;
--
-- Constraints for table `project_members`
--
ALTER TABLE `project_members`
ADD CONSTRAINT `project_members_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`),
ADD CONSTRAINT `project_members_ibfk_2` FOREIGN KEY (`pool_member_id`) REFERENCES `pool_members` (`pool_member_id`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;