DBMS
A DataBase Management System (commonly abbreviated DBMS) is an application or tool which aids in the storage, manipulation, reporting, management, and control of data.
Contents
Features
INDEX
Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by".[1]
VIEW
Views are essentially queries that run before a WHERE clause (in the case of a TEMPTABLE where no INDEXes are carried over to the temporary table) or get expanded (in the case of a MERGE operation where ALIAS values are per-filled to expand out the query). Views are best used to join multiple tables into a single unified table, but depending on the DBMS can cause performance problems if over-used or if used on database tables with more than 100k rows.[3][4][5]
Stored Procedure
Trigger
ACID
Atomicity, Consistency, Isolation, Durability (ACID) is one of the key features of traditional DBMS which implement the SQL standard, such as an RDBMS. It ensures that data will be
- ACID Compliance - What It Means and Why You Should Care: http://blog.clustrix.com/2014/07/29/acid-compliance-means-care/[11]
ORM
- wikipedia: Object-relational mapping
- See also: JPA
- ORM Is an Offensive Anti-Pattern: https://www.yegor256.com/2014/12/01/orm-offensive-anti-pattern.html
Tools
- DB Visualizer: http://www.dbvis.com/
- IntelliJ - DataGrip: https://www.jetbrains.com/datagrip/
- DBeaver: https://dbeaver.io/
- RazorSQL: https://razorsql.com
- Squirrel SQL -- Universal SQL Client: http://squirrel-sql.sourceforge.net/#overview
Resources
- DB-Engines Ranking: http://db-engines.com/en/ranking
EXAMPLES
- The Sakila Database: https://www.jooq.org/sakila | SRC (one of the best example databases out there - originally created by MySQL team - is now OSS under BSD License, offering a nicely normalised schema to model a DVD rental store, featuring: films, actors, film-actor relationships, and a central inventory table that connects films, stores & rentals)[12]
Tutorials
- DBMS_PROFILER: http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php
- Oracle DBMS_PROFILER: http://wiki.oracle.com/page/DBMS_PROFILER
- Using DBMS Profiler: http://www.dba-oracle.com/t_plsql_dbms_profiler.htm
- Optimize SQL Indexes: http://msdn.microsoft.com/en-us/library/ff650692.aspx
- Optimizing SQL Server Query Performance: http://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx
- How to Optimize Queries (Theory & Practice): http://www.serverwatch.com/tutorials/article.php/2175621/How-to-Optimize-Queries-Theory-an-Practice.htm
- Optimizing SQL Server Performance: http://www.edbarlow.com/document/optimize.htm
- Creating and Optimizing Views in SQL Server: http://www.informit.com/articles/article.aspx?p=130855
- Oracle INSERT tuning: http://www.dba-oracle.com/t_insert_tuning.htm
- Foreign Key Relation Across Database : http://java.dzone.com/articles/foreign-key-relation-across
External Links
- wikipedia: DBMS
- Moving tables to another SQL2008 database (including indexes, triggers, etc.): http://dba.stackexchange.com/questions/1038/moving-tables-to-another-sql2008-database-including-indexes-triggers-etc
- How to transfer data between different tables in an Oracle 10g database? : https://forums.oracle.com/forums/thread.jspa?threadID=1518334
- ORC International Database Manageability and Productivity Cost Comparison Study - Oracle Database 11g Release 2 vs. IBM DB2 Enterprise 9.7: http://i.zdnet.com/whitepapers/Oracle_ORC_International_Database_Manageabilty_and_Productivity_Cost_Comparison_study_Oracle_Database_11g_Release-2_vs_IBM_DB2_Enterprise_9.7.pdf?tag=mantle_skin;content
- To Shard, or Not to Shard: http://java.dzone.com/articles/shard-or-not-shard
- JSON Support in PostgreSQL, MySQL, MongoDB, and SQL Server: http://java.dzone.com/articles/json-support-postgresql-mysql
- SQLite vs. MySQL: https://dzone.com/articles/sqlite-vs-mysql (battle between two of the lightest-weight yet feature-full traditional DBMS options)
- PostgreSQL ($0 up-front) vs Oracle ($20-100k up-front) -- Difference in Costs, Ease of Use, and Functionality: https://dzone.com/articles/postgresql-vs-oracle-difference-in-costs-ease-of-u
- Bitemporal History in DBMS: https://martinfowler.com/articles/bitemporal-history.html
- Don’t Use Database Generated IDs in Domain Entities: https://medium.com/swlh/dont-use-database-generated-ids-d703d35e9cc4
References
- ↑ When To Use Indexes In MySQL: http://www.howtoforge.com/when-to-use-indexes-in-mysql-databases
- ↑ Create and use an index to improve performance: http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspx
- ↑ When to use views in MySQL?: http://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql
- ↑ MySQL VIEW as performance troublemaker: http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
- ↑ When to use R, when to use SQL?: http://stackoverflow.com/questions/9795051/when-to-use-r-when-to-use-sql
- ↑ SQL Server Views: http://odetocode.com/articles/299.aspx
- ↑ SQL & Stored Procedure Optimization tips: http://moondev.wikidot.com/sql-optimization
- ↑ Stored Procedures are EVIL: http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
- ↑ How-to generate Insert statements Stored procedure for SQL server: http://ysgitdiary.blogspot.ca/2010/03/how-to-generate-insert-statement.html
- ↑ SQL Optimization Tips and Index Optimization tips - TRIGGERS AND VIEWS: http://www.computeruser.com/tutorials/sql-optimization-tips-and-index-optimization-tips/triggers-and-views.html
- ↑ Say What? RDBMSs are Not Legacy Technology: http://java.dzone.com/articles/say-what-rdbmss-are-not-legacy
- ↑ Use MULTISET Predicates to Compare Data Sets: https://blog.jooq.org/use-multiset-predicates-to-compare-data-sets/
See Also
Database | SQL | MySQL | RDBMS | Optimization | Hibernate | JPA | DBCP