{"id":1273,"date":"2025-10-27T08:08:02","date_gmt":"2025-10-27T05:08:02","guid":{"rendered":"https:\/\/khusseintakhmazov24.thkit.ee\/wp\/eng\/?p=1273"},"modified":"2025-10-27T12:23:53","modified_gmt":"2025-10-27T09:23:53","slug":"db-administration-notes","status":"publish","type":"post","link":"https:\/\/khusseintakhmazov24.thkit.ee\/wp\/eng\/?p=1273","title":{"rendered":"DB administration notes"},"content":{"rendered":"\n
AB user roles:\n\n-AB user - regular user who can modify, add, filter, and search as needed\n\n-AB programmer - creates functions and procedures (triggers)\n\n- DBA - AB administrator - ensures that the right users have the right permissions\n\n- AB designer - creates tables and the AB structure<\/code><\/pre>\n\n\n\n
DBA - AB administrator types:\n*system administrator\n*AB architect\n*AB analyst\n*Data warehouse administrator -  \u0430\u0434\u043c\u0438\u043d \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 (admeladu)<\/code><\/pre>\n\n\n\n
The two main objectives (tasks) of the DBA:
1. User support and access sharing and configuration
2. Ensuring AB security and performance<\/pre>\n\n\n\n
AB security - 3 key aspects:
*data confidentiality
GRANT role to user [identified by pwd] [with grant option];
REVOKE role from user;

*data availability (availability) - \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u043e\u0441\u0442\u044c - data is available at the right time and to the right user
*data integrity (integrity) - \u0446\u0435\u043b\u043e\u0441\u0442\u043d\u043e\u0441\u0442\u044c - reliable data sources<\/pre>\n\n\n\n
Risk<\/strong><\/td>Safety aspect<\/strong><\/td><\/tr>
Human errors<\/td>confidentiality, availability, integrity<\/td><\/tr>
physical defects (hardware)<\/td>availability, integrity<\/td><\/tr>
operating system failures<\/td>availability, integrity, confidentiality<\/td><\/tr>
database system failures<\/td>availability, integrity, confidentiality<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
-COMMIT - The COMMIT command is used to save all changes made during the current transaction to the database. Once the transaction is committed, the changes become permanent. 

-ROLLBACK - If an error occurs, such as a query problem, you can use ROLLBACK to undo all changes made during the transaction.

-ROLLFORWARD - This command restores the database by applying the transactions stored in the database log files. 

-Data mining - Discovering valuable patterns and insights from large data sets using machine learning, statistics, and database system methods.

-Data warehouse - a central repository of data integrated from various sources. These store current and historical data, organized in a way that is optimized for data analysis, reporting, and drawing conclusions based on integrated data.

GROUP BY - this statement groups rows with the same values into summary rows, for example, \"find the number of customers in each country\"

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

UNION ALL - command combines the result sets of two or more SELECT statements (allows duplicate values)

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

GROUPING - Determines whether the column expression in the GROUP BY list is aggregated or not

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'  
FROM Sales.SalesPerson  
GROUP BY SalesQuota WITH ROLLUP;  
GO

- ROLLUP - operator enhances the capabilities of the GROUP BY clause, allowing

GROUP BY - this clause groups rows with the same values into summary rows, for example, \"find the number of customers in each country\"

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

UNION ALL - command combines the result sets of two or more SELECT statements (allows duplicate values)

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

GROUPING - Determines whether the column expression in the GROUP BY list is aggregated or not

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'  
FROM Sales.SalesPerson  
GROUP BY SalesQuota WITH ROLLUP;  
GO  

- ROLLUP - The operator enhances the capabilities of the GROUP BY clause by allowing the calculation of subtotals and totals for a set of columns.

select COALESCE(Department, 'Total') as Department SUM(Salary)
from Employee1
Group By ROLLUP (Department);

- CUBE - is an extension of the GROUP BY clause that allows you to generate all possible combinations of groups for specified columns, including subtotals and totals.

SELECT state, SUM(salary) AS salary
FROM salary_reports
GROUP BY CUBE(state)
ORDER BY state;

Difference between ROLLUP and CUBE - ROLLUP summarizes based on the hierarchy of columns used in the GROUP BY clause. CUBE groups by all combinations of values. \/ Rollup sums the hierarchically selected columns CUBE calculates the sums in each column\/combination<\/pre>\n\n\n\n
Keys<\/h2>\n\n\n\n
PRIMARY KEY<\/h3>\n\n\n\n
PRIMARY KEY \u2013 is a column or group of columns in a table that uniquely identifies each row in the table. A primary key cannot be duplicated, which means that the same value cannot appear more than once in the table. A table cannot have more than one primary key.<\/p>\n\n\n\n
In this example, StudID is the primary key:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Rules for defining the primary key:<\/p>\n\n\n\n