Question: Creating a Database

Question

Imagine setting up a new database for a company to manage information about employees, their skills, and the projects assigned to them. The objective is to create a database named Organization that will store this information in a structured manner. The database structure is as follows:

1. Employees: This table will include a unique identifier for each employee, EmpID, which will be set to AUTO_INCREMENT for automatic number generation. Additionally, it will store employee names and salaries.

2. Skills: This table will feature a unique identifier, SkillID, also set to AUTO_INCREMENT. It will include a column, EmpID, to link each skill to the corresponding employee. Each skill will be stored in the SkillName column.

3. Projects: This table will contain a unique identifier, ProjectID, which will also be set to AUTO_INCREMENT. It will include EmpID and SkillID to connect each project to the relevant employee and their skill. The ProjectName column will store the names of the projects.

The overall structure of the database, in the form of an Entity-Relationship Diagram (ERD), will be as follows.

So, put on your database explorer hat and see if you can find the answer!

Expected output

The expected output is shown below:

Try it yourself

You can write a query in the following playground:

Hints

Below are some hints to help you understand these concepts better: