Database Administration Practical Exercise.
Consider an example of a company database with three tables: Employees,
Departments, and Projects. Each table will be populated with about 5 rows of
sample data.
1. Employees Table:
EmployeeID EmployeeName DepartmentID
1 John Smith 1
2 Mary Johnson 1
3 David Lee 2
4 Lisa Davis 2
5 Brian Wilson 3
2. Departments Table:
DepartmentID DepartmentName
1 Sales
2 Engineering
3 Marketing
4 HR
5 Finance
3. Projects Table:
ProjectID ProjectName DepartmentID
1 Project A 1
2 Project B 2
3 Project C 3
4 Project D 2
5 Project E 4
1. As the root user, create a database named "company_db".
2. Create a table named "Employees" in the "company_db" database with
the following columns: "EmployeeID" (INT), "EmployeeName"
(VARCHAR), and "DepartmentID" (INT). Ensure appropriate data types
and constraints are applied.
3. Populate the "Employees" table in the "company_db" database with the
following data:
• EmployeeID: 1, EmployeeName: "John Smith", DepartmentID: 1
• EmployeeID: 2, EmployeeName: "Mary Johnson", DepartmentID: 1
• EmployeeID: 3, EmployeeName: "David Lee", DepartmentID: 2
• EmployeeID: 4, EmployeeName: "Lisa Davis", DepartmentID: 2
• EmployeeID: 5, EmployeeName: "Brian Wilson", DepartmentID: 3
4. Create a table named "Departments" in the "company_db" database
with the following columns: "DepartmentID" (INT), "DepartmentName"
(VARCHAR). Ensure appropriate data types and constraints are applied.
5. Populate the "Departments" table in the "company_db" database with
the following data:
• DepartmentID: 1, DepartmentName: "Sales"
• DepartmentID: 2, DepartmentName: "Engineering"
• DepartmentID: 3, DepartmentName: "Marketing"
• DepartmentID: 4, DepartmentName: "HR"
• DepartmentID: 5, DepartmentName: "Finance"
6. Create a table named "Projects" in the "company_db" database with the
following columns: "ProjectID" (INT), "ProjectName" (VARCHAR), and
"DepartmentID" (INT). Ensure appropriate data types and constraints are
applied.
7. Populate the "Projects" table in the "company_db" database with the
following data:
• ProjectID: 1, ProjectName: "Project A", DepartmentID: 1
• ProjectID: 2, ProjectName: "Project B", DepartmentID: 2
• ProjectID: 3, ProjectName: "Project C", DepartmentID: 3
• ProjectID: 4, ProjectName: "Project D", DepartmentID: 2
• ProjectID: 5, ProjectName: "Project E", DepartmentID: 4
