Databases

I have often needed to clone a table without it’s data in MySQL. Oftentimes this has been because I have a continually growing temporary table and I just need to clear up some disk space. And it’s a lot faster and more guaranteed than trying to delete and optimise the table. Clone a table structure without it’s data Let’s say that your table in question is called table1: create table table1tmp like table1; drop table table1; rename table table1tmp to table1; If you run the above queries either in quick succession, or altogether in a single statement, you will notice that this is a very fast method even on very big tables....

Percona has a great toolkit that allows you to perform schema changes on a MySQL or MariaDB database without any downtime, it works by creating a new table with the same schema, making the changes to it and applying triggers for insertion, deletion and all updates, all while performing your valuable schema updates for you! How to Add an Index to a MySQL or MariaDB database without downtime pt-online-schema-change --alter 'add index ix_<COLUMN_NAME> (<COLUMN_NAME>)' D=<DATABASE_NAME>,t=<TABLE_NAME> --dry-run...

Relational databases first made an appearance in the mid-1970s, between the years 1974 and 1977 with the creation of Ingres and System R which led to the creation of MS SQL Server, Sybase, Wang?s PACE and Britton-Lee to name a few (Quickbase, n.d.). It was only until the 1980s that SQL (Structured Query Language) became the standard query language used by relational databases. Basic features of the relational data model:...

As a product update; The “Autonomous Distributed Database”, otherwise known as simply ADD has been renamed to Serengeti. The Serengeti is a vast ecosystem in east-central Africa. It spans 12,000 square miles (30,000 square kilometers), according to NASA, giving rise to its name, which is derived from the Maasai language and means “endless plains.” This region of Africa is located in north Tanzania and extends to southwestern Kenya. Much the same, the Serengeti database project is a truly autonomous distributed system that has no visible ending nor capacity....

A database “is a structured collection of data. Card indices, printed catalogues of archaeological artefacts and telephone directories are all examples of databases”. We are going to examine databases that are prevalent in computer systems and go through the core differences between Local and Distributed Databases. Local A local database is an isolated database that is stored on a single computer and does not share its data outside of itself or its direct access network....

Traditional database systems were a centralised powerful database server that housed all operations pertaining to the database. There are countless reasons that this is unsustainable when larger databases are employed, specifically for globally distributed companies and popular applications as already known today. A single database server may suit many database’s requirements, but at some point, there may be a need to scale up to support higher demand or for global capacity requirements of sorts (Shalom, 2017)....

There are essentially two different approaches one can take when designing databases; these, from a high-level analytic point of view, narrow down to what is typically called “Top-down” and “Bottom-up” philosophies or methods. While these methodologies can appear profoundly unique, they share the shared objective of joining a system by portraying the greater part of the association between the procedures. Top-down What is top-down approach to database design Top-down design, is characterized by an extensive planning and research phase that leads into the development of the database (Maxey, 2012)....

Standard Query Language – or SQL for short – is a language designed for relational databases that allows end users such as developers and database administrators (DBAs) to manipulate data. Whether that be INSERT-ing, UPDATE-ing, DELETE-ing, SELECT-ing or otherwise ALTER-ing almost any amount of data; SQL provides an effective way of utilising the database system/engine by providing standardised language to do so. The American National Standards Institute (ANSI) SQL became the standard in 1986 when the specification described the official features of the SQL language (Karmin, 2015)....

Database Management Systems (DBMS?) are designed to store any amount of data that can then be retrieved or manipulated at a later date. In order to make sure that the data can be concurrently utilised in parallel by numerous processes or users, it is important to have some form of Locking enforced so that if a particular record is being modified or created, then another process is not allowed to simultaneously adjust the same record....

If you are getting the mysterious error: [ERROR] InsufficientPrivilege: permission denied for schema errors Then you can easily resolve this by running the following SQL: GRANT USAGE ON SCHEMA <schema_name> TO <redshift_user>; Another common error that often follows this, is: [ERROR] InsufficientPrivilege: permission denied for relation <table> Luckily, this too can be resolved by running the following SQL: GRANT USAGE ON SCHEMA <schema_name> TO <redshift_user>; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <redshift_user>;