Database

A database is a storage mechanism for raw data which adheres to a certain format or number of possible formats. There are various types of databases, ranging from File Systems to text-based lists, or from full fledged DBMS's to Triple-stores following formats such as RDF or N3.

Architectures

ETL

Extract, Transform, and Load (ETL) is a common organizational process or task for a Database Administrator. It typically involves obtaining (extracting) data from outside sources or disparate internal sources, converting or otherwise massaging (transforming) that data to fit operational needs which could include quality levels, filtering and/or mapping to integrate with other data sources or data points, and lastly moving the data (loading) into the end target database or other data archiving/storage system such as an Operational Data Store (ODS), Data Mart (DM), or Data Warehouse (DW/DWH/EDW).

• wikipedia: Extract, transform, load

ERD

Entity Relationship Diagram (ERD) is a type of diagram which describes inter-relations between things of interest in a specific domain of knowledge. It takes standard tabular data and associates it together. The support for ERDs (typically via Primary Key & Foreign Key relationships) is what differentiates a regular DBMS from an RDBMS. Within an ERD, the "ER model" is comprised of entity types (which classify the things of interest) and specifications of relationships that can exist between instances of those entity types. In software engineering an ER model is commonly formed to represent things that a business needs to remember in order to perform business processes. Consequently, the ER model becomes an abstract data model that defines a data or information structure that can be implemented in a database, typically a relational database.

• wikipedia: Entity Relationship Diagram

Data Lakes

Data Lakes are systems for long-term data storage for raw/unprocessed data formats such as JSON, XML, PDF, CSV, Excel, etc.

• Data Lakes - All You Need to Know: https://dzone.com/articles/data-lakes-all-you-need-to-know

• wikipedia: Data lake

Data Warehouses

Data Warehouses (DW or DWH), also known as an Enterprise Data Warehouse (EDW) are systems for long-term data storage for data from various databases and/or data sources which has already undergone some form of ETL and/or more basic archival & journaling processes (validation, redaction, joins/mergers, "expiration" shelf-life timestamping, etc).

• wikipedia: Data warehouse

Tools

• GenerateData: http://www.generatedata.com/#generator (allows you to generate sample data up to 5000 records big in SQL, XML, CSV, TSV, HTML and more formats)
• DB Designer: http://dbdesigner.net/designer (online Database Schema creation tool)
• Oracle SQL Developer: http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html (for Oracle 11g+)
• PL/SQL Developer: https://www.allroundautomations.com/plsqldev.html (also works well with Oracle DB products, specifically PL/SQL but can work with others)
• Toad SQL Optimizer: https://www.toadworld.com/products/sql-optimizer | SQL Server
• MySQL Workbench: https://www.mysql.com/products/workbench/
• SQL Workbench/J: http://www.sql-workbench.net/ (an alternative to the MySQL-specific [MySQL Workbench], but works more like PHPMyAdmin)
• SchemaSpy: http://schemaspy.org/ | SRC (command-line tool for generating SQL Schema Diagrams & ERDs from an existing database)

Resources

• lscache -- A localStorage-based, memcache-inspired library: http://blog.pamelafox.org/2010/10/lscache-localstorage-based-memcache.html
• Database Abstraction class (access different types of SQL database): http://www.phpclasses.org/package/6535-PHP-Access-different-types-of-SQL-database.html
• ADOdb Database Abstraction Library for PHP: http://adodb.sourceforge.net
• What is Northwind Traders database?: https://www.geeksengine.com/article/northwind.html | SRC

Tutorials

• Data Modeling 101: http://www.agiledata.org/essays/dataModeling101.html
• Introduction to Data Normalization - A Database "Best" Practice: http://www.agiledata.org/essays/dataNormalization.html
• A (Probably Incomplete) Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL : http://dzone.com/articles/a-probably-incomplete-comprehensive-guide-to-the-many-different-ways-to-join-tables-in-sql
• Database Concepts for a java Dev - Database Normalization: http://architects.dzone.com/articles/database-concepts-java-dev
• 7 Steps to Database Change Control - Database Change Request (DCR) form: http://www.sqlmag.com/article/auditing/7-steps-to-database-change-control
• PHP Portable Data Objects (PDO): http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
• Creating a JPA Persistence Unit using NetBeans 6.8: http://blogs.sun.com/arungupta/entry/totd_122_creating_a_jpa
• Migrating from Microsoft Access to MySQL: http://www.kitebird.com/articles/access-migrate.html
• How to Convert Access to MySQL: http://www.ehow.com/how_2058142_convert-access-mysql.html
• Storing Hierarchical Data in a Database (not as good as RDF, but it works): http://articles.sitepoint.com/article/hierarchical-data-database
• Command Query Responsibility Segregation (CQRS): http://martinfowler.com/bliki/CQRS.html\
• How to import sql files which are more than 10mb in mysql database: http://stackoverflow.com/questions/3210713/how-to-import-sql-files-which-are-more-than-10mb-in-mysql-database (mysql -u user -p databasename < file.sql)
• Upper limit for Upload Limit and Post Limit in PHP: http://stackoverflow.com/questions/10988214/upper-limit-for-upload-limit-and-post-limit-in-php
• Importing large files into mysql with phpmyadmin: http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/

External Links

• wikipedia: Database
• wikipedia: Data Transformation Services (DTS, is a way to export and/or share ETL tasks)
• wikipedia: System of record
• 10 Common Database Design Mistakes: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/
• The State of the Open Source Database Market -- MySQL Leads the Way: http://www.scalebase.com/the-state-of-the-open-source-database-market-mysql-leads-the-way/
• The Mixology of Databases: http://java.dzone.com/articles/mixology-databases
• What Every Developer Should Know About Database Consistency: https://robertovitillo.medium.com/what-every-developer-should-know-about-database-consistency-cff3183913cb

See Also

DBMS | SQL | SQL Schema | NoSQL | BigData