TECH1400: Database Design and Management

TECH1400

Database Design and Management

Student ID:

Student Name:

Introduction

The main aim of this report is to create an understanding of how querying is done by using my SQL. This involves querying data from a structured database using MySQL. Using the knowledge of SQL and relational databases, the report presents an example of constructing queries that effectively answer specific questions, and also, identifies main concepts of data integrity. The whole SQL queries are written by using my SQL workbench 8.0 tool those are very well known for its working and fluency.

Part A: SQL Queries

Question 1:

In the same way, the query is written, names of employees that have that place and department as place and names in alphabetical order. It comes up in two different tables and is connected using the JOIN command. To show the name of the employee in alphabetical use the ORDER BY function and WHERE clause.

Question 2:

This query takes this information and finds out the employees who recently joined the university, selects the yearHired column of the employee table uses ALIAS to give a temporary name to the yearHired column ‘Employee Hired Year’, and then orders it in descending order using the order by clause and giving a limit of 5.

Question 3:

This query shows all employees whose age written is in palindrome. Here it identifies the palindrome age under where clause using CURRENT_DATE and applies the required condition.

Question 4:

This query in this question finds out how many employees are working under one department and gives the name of the department with a count of employees under that department. Assuming the TABLE part of the query, JOIN combines the two different tables, GROUP BY is used with the name of the department and the number of employees that will include the group as well column names, conditions, and joins where displayed in the results.

Question 5:

This database query will find the average salary of each Employee for the combination of the name of the department, salary, and the combination of 3 different tables Departments, Employees, and SalaryClass using JOIN operations then use GROUP BY to combine the name of the department and salary.

Question 6:

It lists the employee names and salaries who are above 40. For that, it joins the two different tables by using the JOIN function and then applies the condition by using the WHERE clause to display the name of the employees whose age is below 40 to find out the age based on the birth year, it applies the Year (CURRENT_DATE) function.

Question 7:

It’s an SQL query that fetches the name of that employee whose Job title is ‘Lecturer’, along with departments that applied to hire her. It does a JOIN operation to join 2 different tables and then a specific title from the department table which is performed using a WHERE clause.

Question 8:

It gives me the employee's first and last name along with their employee's salary for this it uses ALIAS (AS) to give them temporary names and it will display the incremented employee's salary for the current salary it provides the name as 'Current Salary' and the incremented salary with a bonus for increment in salary of 10%, multiplying said employee's salary by 1.10 for it provides name as '10% bonus applied salary' (Programiz 2024). Using JOIN, it joins these two different tables.

Question 9:

In this query, use the ALTER command to add a new column in the employee's table and give them the name EmployeeIntials columns to be able to get the first letters from each employee. To add or modify the table use the alter command.

Question 10:

This updated employee's table with the initials of each employee. The first name and the last name of each employee are put inside the employee's table 'Employee's Initials' column, and with the help of a substring, the first and last name of the employees and the CONCAT function are used to merge the first and last letter of the first and last name of employees.

Question 11:

Ans. Overall this database has good enough integrity but can be strengthened with relational integrity if we change some things including the addition of identity fields (EmployerID, SalaryClassID, and DepartmentID) in their related tables as foreign keys to Employees, SalaryClass, and Departments (Gabormelli 2022). Nonetheless, the ability to tune even a few aspects of the IDL is something I would like to comment on, and specifically, this is the use of VARCHAR for types whenever one is defining them for things such as ‘Gender’ and ‘Performance Bonus’. Consequently, if we come across more gender qualities, we will discover that VARCHAR does not look very suitable and very useful for us to accommodate. And, if we replace these attributes with a single lookup table, then rather than seeing VARCHAR as a negative contributing issue to the normalization and scalability, the aspect will be discovered as positive. It has a composite primary key because it has three fields, EmployeeID, SalaryID, and DepartmentID; this is also in its present structure of the Employees table. Although this ensures that each employee has a close record of his department and salary class, it brought forth the following challenge. From the perspective of the handling of data, several records are recorded for the same employee in cases of transition in the field of the employee’s working department or the salary class. The problem with such a table is that if there is such a table as Employees then it’s even better to have one unique key, and that key is EmployeeID.

Conclusion

In conclusion, this report has always focused on how to write efficient SQL queries to query, analyze, and manipulate data within the relational database. The importance of relational databases in responding to search questions and managing data and principles of database organization that would be relevant to today's world are highlighted in the project.

References

?Programiz 2024, SQL SELECT AS Alias , Programiz.com, retrieved 7 October 2024, <https://www.programiz.com/sql/select-as-alias>.

Gabormelli 2022, Relational Integrity , Gabormelli.com, retrieved 7 October 2024, <https://www.gabormelli.com/RKB/Relational_Integrity>.

?