Databases and B-Trees: What are Keys and how are they related

I confused about the description & definition of "key" occuring as terminology for databases and b-trees.

In first case dealing with theory of databases a key is defined as a choice for a in certain sense minimal (see below) subset $ K \subset A := \{A_1,A_2,…,A_n\}$ of a fixed set of attributes $ A_i$ parametrizing a data table (consisting of relations; a single relation is abstraction of a row in the table), where each attribute is literally a column specifying a property of objects = relations.

A key is characterized by property that there exist no two different relations (a relation is a row) which have exactly the same values for all attributes which belong to the key. And a key is a minimal subset with this property, ie there not exist a proper smaller subset of attributes contained in the key and having the property described in last sentence. Clearly keys are not unique. So the set of a keys is certain subset of the power set of the set attributes $ A_i$ . See also here: https://en.wikipedia.org/wiki/Unique_key

On the other hand the key concept occures as well for b-trees: https://en.wikipedia.org/wiki/B-tree#Definition

Here keys are a priori numbers or integers and different knots of b-tree contain different totally ordered subsets of keys where the total order on the space of keys is inherited from the order "$ \ge$ " for integers $ \mathbb{Z}$ . Especially the set of keys is a totally ordered subset of integers.

Question: How are the two concept of ‘key’ related to each other? My first idea was that if we consider in light of for definition (as elements of power set of attributes), we can simply randomly enumerate all the keys (that is associate to each key an number; formally speaking to specify an injection $ f:\mathcal{K} \to \mathbb{Z}, K \mapsto f(K)$ where $ \mathcal{K} \subset \Omega(A)$ )

and then treat them as numbers when working with b-trees. Is this exactly the correct connection or is there another deeper one and my approach is wrong?

Two persons-rule on MySQL databases for “manual fixes”

In order to “harden” our compliance, we wanted to enforce a two-persons rule on the MySQL production database for “manual fixes”. Such “manual fixes” frequently arise due to:

  • Bug in the application (we are a fast company :D)
  • Various customer requests that do not have an application feature implemented yet, such as GDPR update requests, special discounts, etc.

We wanted a process that does not require the two persons to be physically side-by-side. One person is on-call, rather junior and is responsible to translate customer service requests into SQL. They might need a GUI (such as MySQL Workbench) to navigate the complex data model and figure out the exact SQL script to produce. The SQL script should feature SELECTs showing the data before and after the change in a non-committed transaction (e.g., AUTOCOMMIT OFF no COMMIT at the end).

The second person is not on-call, rather senior, and fairly familiar with the application’s data model. They should be able to look at the SQL script the non-committed transaction output, and approve or reject via a mobile app during the evening.

We cannot be the first to have this or a similar requirements.

Does anyone know good documentation or tooling to implement such a process?

Here are some similar questions on the topic, but not quite as specific as the present one:

  • What can a company do against insiders going rogue and negatively affecting essential infrastructure?
  • How can two-man control be implemented efficiently?

Downloading geoip databases

Hello guys !
this is the first time i used scrapebox but when i try to open it
it has a little problem like this
“Downloading geoip databases”
i already wait around 20 minutes but still loading ?
can someone tell me how to resolve this problem ?
https://prnt.sc/s1p33l

Thankyou

Data Consistency Between 2 Mysql Databases Running Different Versions

I need to migrate an old web application using old php and mysql versions. I am intending to start by migrating the code that interacts with the database to a new server with the latest versions and allow the old server to use an API in the new server. Ideally I would like to have the data consistent between the database in the new server and the one in the old server. It seems that replication only works only when the master is only one version behind the slave. In this case the difference is a way more than one version. I also want changes to the database on the new server to reflect on the database on the old server. Are there any tools/techniques to help with this situation other than ensuring the data consistency programmatically with cron jobs …. If something is not clear please let me know and all suggestions are welcome.

Azure elastic pool: Can I create as many databases as I want?

I am sitting here trying to figure out what the elastic pool in Auzre gives me. I have a customer that wants a guess on pricing if the move to Azure. They have 3 databases, on a server. The server has 96 GBs of RAM and a 3.4 Ghz processor. That is all i know. The three databases is sharing these specs, and each database has it own area of expertise:

  1. one database handles all the CMS data
  2. the second database handles all the commerce data
  3. the third database is a database that stores user info, etc – all data that doesnt fit the two others

I think the usage pattern is pretty diverse, so sometimes the commerce is using a lot of RAM and CPU, other times it is the CMS etc.

I dont think they fit well into a single database, because they are going to need three big databases then, and almost all of them is going to be under utilized all the time. So I thought about elastic pools.

The describtion of ealstic pools fits the above very well: all databases shares eDTUs. Perfect! But one thing the docs doesnt describe is: do i pay for the elastic pool AND pr database? Or do I just create databases on the fly, and all of them shares, that is: the databases is free, but you pay for the eDTU.

I have read this https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool – but they don’t describe it.

Hope someone can give me some more info.

Data Model or Data Structure for Stored Procedures in Databases

I know that Relational Algebra is the goto data model for storing information related to SQL queries.

I am however yet to find a standard data model that can encapsulate constructs of stored procedures such as cursors, loops, in/out variables, statements etc.

I might be wrong, but usually people talk about an AST, but is there something more specific? I tried looking into MySQL code to get some idea but wasn’t too successful.

Auto-Shrinking All Databases Files To Their Minimum Size In Sql Server

I restore my all databases in production to my development environment monthly. Development server has 4 TB disk space and there are 4 different instances over it which containt databases on the production server. In Development, i can truncate and shrink some big-sized tables to expand free disk space. The purpose of doing this process is the complaint of software developers about the development server slowness. I talked to system administrator team and they informed me that the slowness was because of the insufficient disk space area. So, i make this work. My question is about how i can shrink all database files to their minimum size after truncating related tables. So, should i make it with powershell, and how?

Why is VACUUM FULL locking ALL databases on the cluster?

From everything I have read about VACUUM FULL, I would expect it to lock the database I’m running it on, but it renders every database in the cluster inaccessible. Is there perhaps something we might have wrong in our configuration? I do have autovacuum on, but we have a few unwieldy databases I’d like to clean up thoroughly. This is PostgreSQL v10. Thanks for any insights.