Index
This is the parent page for database info.Child pages include:
- Data modeling tools:
- Sybase Power Deseigner;
- MySQL Workbench; - Data Access Layer (or Data Abstraction Layer): concepts and design patterns on how to implement a DAL;
- SQL
- PL/SQL
- MS SQL Server: couple of pages with info for this DBMS;
- MySQL
- PostGreSQL
Database procedural languages (ex. PL/SQL and T-SQL):
SQL is designed for a specific purpose: to query data contained in a relational database. SQL is a set-based, declarative query language, not an imperative language like C or BASIC. However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs.- SQL: is a query language to operate on sets. It is more or less standardized and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.
- PL/SQL is a proprietary procedural language used by Oracle
- T-SQL is a proprietary procedural language used by Microsoft in SQL Server.
- Like PL/SQL and T-SQL there are many other proprietary procedural languages. Check the wikipedia procedural languages list.
They can be used for example to write stored procedures: pieces of code residing on the server to manage complex business rules that are hard or impossible to manage with pure set-based operations. I.e.: they are used to include logic in the database.
Database jargon
- DDL (Data Definition Language) statements: manages table and index structure. The most basic items of DDL statements are:
- CREATE;
- ALTER;
- RENAME;
- DROP;
- TRUNCATE; - DML (Data Manipulation Language) Statements: is the subset of SQL used to add, update and delete data ex:
- INSERT;
- UPDATE;
- DELETE;
- MERGE; - DCL (Data Control Language) statements: used to create roles/permissions to control access to database by securing it, ex:
- GRANT (authorizes one or more users to perform an operation or a set of operations on an object);
- REVOKE (eliminates a grant, which may be the default grant); - TCL (Transactional Control Language): used to manage different transactions occurring within a database. This statements wrap DML operations, ex:
- COMMIT;
- ROLLBACK;
- SAVEPOINT; - DQL (Data Query Language): basically used to query data from table...
- SELECT;
Query Optimization
Use the Execution Plan of your DBMS to determine how your queries are being executed.If you're new to this try googling something like: "execution plan mysql".
- MySQL execution plan documentation: Understanding the Query Execution Plan
DataBase Auditing
Auditing is monitoring and recording all user actions on a database. Among others you can base auditing on individual actions, such as database backup, change of user logins, insert (etc.) or on combination of factors. [1]
[1] - Auditing in SQL Server 2008
[2] - SQL Server Audit Articles
[3] - Understanding SQL Server Audit
Why Audit? [1]
Though auditing has become a popular buzzword, many DBAs and developers still do not appreciate the requirement for auditing. Properly implemented, auditing can achieve the below:- Ensure accountability for all actions - in particular server, database, schema, and table operations.
- Discourage users from inappropriate actions due to their accountability.
- Assist the investigation of suspicious activity.
- Notify an auditor of the actions of an unauthorized user.
- Monitor and gather data about specific database activities.
- Detect problems with an authorization or access control implementation.
- Ensure compliance with legal requirements, such as: Sarbanes-Oxley Act; Health Insurance Portability and Accountability Act (HIPAA); International Convergence of Capital Measurement and Standards: a Revised Frame work; Japan Privacy Law; European Union Directive of Privacy and Electronic Communications; Data Protect Act (UK);
[1] - Auditing in SQL Server 2008
[2] - SQL Server Audit Articles
[3] - Understanding SQL Server Audit
TODO
No comments:
Post a Comment