I need to be able to search for **compound words** like "*menneskerettighedsforkæmperens*" (the human rights activist's) and find the words "*menneske*" (human), "*rettighed*" (right) and "*forkæmper*" (activist). I have all the words (and hundreds of thousands of other words) listed in MySQL, all in separate rows with additional information about each word (like hyphenation, which is what I need the DB to return), but I need to search a lot of words at the same time, which can be painfully slow. I'm currently using a MySQL database, but willing to switch it out for something better suiting my needs, maybe some kind of NoSQL or elasticsearch. But I haven't been able to find any examples of how to accomplish what I'm looking for in any other type of databases. So if anyone can help me out, I would really appreciate it.

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text relationships id autoincrement term_id references terms(id) post_id references posts(id) vs a single table: relationships post_id references posts(id) tax_name text term_name text term_value text I think that if I use a single table the db will use slightly more space, because it will store the tax name and term name for each record (relationship). But would it be faster when performing queries that select records based on multiple terms and term values?

Lets say I have a table X with following definition: CREATE TABLE X( id INT PRIMARY KEY, field1 varchar(100), field2 int, field3 double); Then I issue a query: SELECT field1, field2 from X; Let's say this query returned 10 records. Is there a way to write a query to update the record #5 from the SELECT? What would the query will look like? Or I will have to include id in that query, somehow find its value in a record #5 and then use it in a query below? Something like UPDATE X SET field1 = 'def' WHERE.... I have a problem with the condition - what to write for the WHERE clause. I'm working with multiple DBMSes, so need a generic way if possible. **More details:** I can have a query like this: SELECT field1, field2 FROM X WHERE field3 = "abc"; or I can have a query like this: SELECT X.field1, Y.field2 FROM X, Y WHERE X.pk = Y.pk AND X.field3 = "abc"; Let's say when I skip to record 5, X.field1 will show "abc". I want this field to be updated to "def". So I type "def" and skip to record 6. In both cases I want to update record 5 X.field1 of the resulting recordset. As you can see I'm going by the records in a recordset (results of executing a query). I want to emulate what Access is doing. Let's say I open the new form in Access and I base it on the query. I re-arrange the field on the form to my liking and execute the form. Then I go to display the record #5 in the form and then update a field. This update is instantaneous, no matter the complexity of the query behind the form.

I know there is optimistic record locking, but do we have optimistic table locking too? I want to optimistically lock a whole table not just a record.

Suppose I have the following query that uses window function:

SELECT id
     , var
     , num
     , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
    (1, 0.1, 7),
    (2, 0.7, 1),
    (3, 0.3, 9),
    (4, 0.9, 5),
    (5, 0.5, 3)
) AS t(id, var, num)

And the following result:

id | var | num | calc | explanation
1  | 0.1 | 7   | 0.7  | 0.1*7
2  | 0.7 | 1   | 1.4  | 0.1*7 + 0.7*1
3  | 0.3 | 9   | 4.1  | 0.1*7 + 0.7*1 + 0.3*9
4  | 0.9 | 5   | 7.9  | 0.7*1 + 0.3*9 + 0.9*5
5  | 0.5 | 3   | 8.7  | 0.3*9 + 0.9*5 + 0.5*3

Is is possible to reference the var column _from the outside_ inside the SUM() OVER ()? For example: ```none id | var | num | calc | sum of f(r.var, w.var, w.num) 1 | 0.1 | 7 | ... | iif(0.1fiddle][1] where I was able to achieve the result with correlated queries but I want to use window functions.

Which of these is true: 1. If a table is in 2nd normal-form then it must be in 3rd normal-form. 2. If a table is in 3rd normal-form then it must be in 2nd normal-form. I know only one of them is correct, if it's the first it makes sense. If it's the second -doesn't make any sense to me. Maybe an example?

I created two different tables and then decided to put them (=) to each other however, what I am confused about is whether (=) is allowed to be used like that. I tried using IN but I didn't know where to go from there either. Is what I did correct? Consider a relational schema for storing information related to movies: ActorMovie(a_name, a_YofB, m_title, m_year) Movie(title, year, genre, budget, cost, gross_earnings) Q: List the names of all actors who acted in an equal number of comedies and tragedies.

Create View V1 AS (
Select 		name
	From 		ActorMovie a, Movie m
Where 		a.m_title = m.title AND a.m_year = m. year AND Genre = ‘Comedy’)

Create View V2 (
Select 		name
	From 		ActorMovie a, Movie m
Where 		a.m_title = m.title AND a.m_year = m. year AND Genre = 
‘Tragedies’)

Select 		COUNT (name) = (Select 	Count (name) FROM 	V2)
From  		V1

I'll be using Postgres for the examples, but feel free to show examples in other databases if needed. The simplified schema:

create table factory (
  id serial primary key,
  detail text not null
);

create table process (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null
);

I'll refer Factory as [F], Process as [P] and Item as [I]. The schema has the following conceptual relationships: - Each Process is exclusive to one Factory, so [P] n -> 1 [F] - Each Item is exclusive to one Factory, so [I] n -> 1 [F] - Each Item can be made by multiple Processes, so [P] n -> 1 [I] To express this relationships I came up with:

create table factory_item_process (
  factory_id integer not null references factory(id),
  process_id integer not null references process(id),
  item_id integer not null references item(id),
  constraint pk_factory_item_process primary key (
    factory_id, process_id, item_id
  ),
  constraint uq_factory_item_process_process unique (process_id)
);

This takes care of [P] n -> 1 [I] and [P] n -> 1 [F], but doesn't solve [I] n -> 1 [F]. There is no way to convey [I] n -> 1 [F] with unique key constraints, so I created a simple function that checks if [I] already belongs to an [F]:

create function in_other_factories(
  factory_id integer, 
  item_id integer
) returns boolean
language sql returns null on null input
  return true in (
    select 
      true 
    from factory_item_process
    where factory_id  $1
      and item_id = $2
  );
alter table factory_item_process 
  add constraint chk_factory_item check(not in_other_factories(factory_id, item_id));

What is right & wrong about this to express these relationships? Making a check with a user-defined function is a code smell. I don't if the tables are correctly normalized. I chose to use another table for this relationship because this relationship will have attributes that are exclusive to itself.

Suppose we have a relation R(A, B, C) with the FD A -> B. Let's say that, for two tuples t and u in R, if both t[C] and u[C] satisfy a boolean condition - say less than a constant C - then t[B] = u[B]. Does this constitute a functional dependency? On a more practical sense, how to normalize this relation and eliminate the clear redundancy in R(A, B, C) and allow for lossless joins?

From Codd's 12 rules (emphasis mine): > For any system that is advertised as, or claimed to be, a relational > data base management system, that system must be able to manage data > bases entirely through its ***relational capabilities***. What does *"relational capabilities"* mean here? Does it mean that you could manage the database by only using a language like SQL? Does it mean anything beyond this?

I have gone through the Wikipedia definition several times: > A database engine (or "storage engine") is the underlying software component that a database management system (DBMS) uses to **create, read, update and delete** (CRUD) data from a database. What I don't understand is what is left to do, isn't CRUD all that the databases do? **If the database engine performs these functions, what does the rest of the database do?**

Is it possible that after a certain pattern of random inserts and deletes **leaf data nodes** become fragmented with **clustered** index? I.e, that the physical order does not reflect the logical order (by say, INT primary key) imposed by the clustered index? This way, range queries would require random I/O even after finding the beginning of the interval. Most university courses (ex. CMU Introduction to Database Systems by Andy Pavlo) say that data is physically ordered according to the key. While definitely approximate to the reality, that looks as unrealistic to me counting the cost of frequent file defragmentation that would be required.

# Table WSHOSHO SELECT * FROM WSHOSHO; # Table RRP SELECT * FROM RRP; # Join to illustrate issue The results of this join can be used to illustrate the issue: SELECT * FROM WSHOSHO RIGHT JOIN RRP ON (WSHOSHO.DATE = RRP.DATE); Result: I.e.:

For any given row of RRP
    If there's a WSHOSHO row with the same date, use that

    Otherwise, use the most recent WSHOSHO before RRP.DATE

# Question What's a good query to achieve this? # Example data Here's some T-SQL code to setup the example data used here.

DROP TABLE WSHOSHO;

DROP TABLE RRP;

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='WSHOSHO')
	CREATE TABLE WSHOSHO (
		DATE    varchar(255), 
		WSHOSHO decimal
	)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='RRP')
	CREATE TABLE RRP (
		DATE    varchar(255), 
		RRP decimal
	)

INSERT INTO WSHOSHO
VALUES 
('2023-01-11', 8100175.0), 
('2023-01-18', 8079010.0), 
('2023-01-25', 8062665.0), 
('2023-02-01', 8024470.0), 
('2023-02-08', 8024104.0), 
('2023-02-15', 7990467.0), 
('2023-02-22', 7986887.0), 
('2023-03-01', 7948534.0), 
('2023-03-08', 7948335.0), 
('2023-03-15', 7940014.0), 
('2023-03-22', 7936558.0), 
('2023-03-29', 7926131.0), 
('2023-04-05', 7877114.0);

INSERT INTO RRP
VALUES
('2023-01-03', 2188272000000), 
('2023-01-04', 2229542000000), 
('2023-01-05', 2242486000000), 
('2023-01-06', 2208265000000), 
('2023-01-09', 2199121000000), 
('2023-01-10', 2192942000000), 
('2023-01-11', 2199170000000), 
('2023-01-12', 2202989000000), 
('2023-01-13', 2179781000000), 
('2023-01-17', 2093328000000), 
('2023-01-18', 2131678000000), 
('2023-01-19', 2110145000000), 
('2023-01-20', 2090523000000), 
('2023-01-23', 2135499000000), 
('2023-01-24', 2048386000000), 
('2023-01-25', 2031561000000), 
('2023-01-26', 2024069000000), 
('2023-01-27', 2003634000000), 
('2023-01-30', 2048714000000), 
('2023-01-31', 2061572000000), 
('2023-02-01', 2038262000000), 
('2023-02-02', 2050063000000), 
('2023-02-03', 2041217000000), 
('2023-02-06', 2072261000000), 
('2023-02-07', 2057958000000), 
('2023-02-08', 2059604000000), 
('2023-02-09', 2058942000000), 
('2023-02-10', 2042893000000), 
('2023-02-13', 2107775000000), 
('2023-02-14', 2076548000000), 
('2023-02-15', 2011998000000), 
('2023-02-16', 2032457000000), 
('2023-02-17', 2059662000000), 
('2023-02-21', 2046064000000), 
('2023-02-22', 2113849000000), 
('2023-02-23', 2147417000000), 
('2023-02-24', 2142141000000), 
('2023-02-27', 2162435000000), 
('2023-02-28', 2188035000000), 
('2023-03-01', 2133950000000), 
('2023-03-02', 2192355000000), 
('2023-03-03', 2186150000000), 
('2023-03-06', 2190793000000), 
('2023-03-07', 2170195000000), 
('2023-03-08', 2193237000000), 
('2023-03-09', 2229623000000), 
('2023-03-10', 2188375000000), 
('2023-03-13', 2126677000000), 
('2023-03-14', 2042579000000), 
('2023-03-15', 2055823000000), 
('2023-03-16', 2066319000000), 
('2023-03-17', 2106166000000), 
('2023-03-20', 2098393000000), 
('2023-03-21', 2194631000000), 
('2023-03-22', 2279608000000), 
('2023-03-23', 2233956000000), 
('2023-03-24', 2218458000000), 
('2023-03-27', 2220131000000), 
('2023-03-28', 2231749000000), 
('2023-03-29', 2264862000000), 
('2023-03-30', 2271531000000), 
('2023-03-31', 2375171000000), 
('2023-04-03', 2221010000000), 
('2023-04-04', 2219375000000), 
('2023-04-05', 2243011000000), 
('2023-04-06', 2173663000000);

The above code is in T-SQL for SQL Server. However, I'm open to any SQL language solutions that would be considered 'idiomatic SQL'. # PowerShell approach I have an approach that works for data in PowerShell shown below. However, I'm wondering what the idiomatic SQL might look like.

$wshosho_data = Invoke-RestMethod 'https://fred.stlouisfed.org/graph/fredgraph.csv?id=WSHOSHO ' | ConvertFrom-Csv
$rrp_data     = Invoke-RestMethod ('https://markets.newyorkfed.org/api/rp/reverserepo/propositions/search.json?startDate={0} ' -f '2022-04-08')
# ----------------------------------------------------------------------
$wshosho_sorted = $wshosho_data             | Sort-Object DATE
$rrp_sorted     = $rrp_data.repo.operations | Sort-Object operationDate
# ----------------------------------------------------------------------
$wshosho_sorted | Select-Object -Last 10 | ft *
$rrp_sorted     | Select-Object -Last 10 | ft operationDate, totalAmtAccepted
# ----------------------------------------------------------------------
$wshosho_dates = $wshosho_sorted | ForEach-Object DATE
$rrp_dates     = $rrp_sorted     | ForEach-Object operationDate
# ----------------------------------------------------------------------
$rrp_earliest     = $rrp_dates     | Sort-Object | Select-Object -First 1
$wshosho_earliest = $wshosho_dates | Sort-Object | Select-Object -First 1

$earliest = $rrp_earliest, $wshosho_earliest | Sort-Object | Select-Object -Last 1
# ----------------------------------------------------------------------
$dates = $rrp_dates + $wshosho_dates | Sort-Object | Select-Object -Unique | Where-Object { $_ -GE $earliest }
# ----------------------------------------------------------------------
$table = foreach ($date in $dates)
{
    $rrp_record     = $rrp_sorted.Where(     { $_.operationDate -le $date }, 'Last' )
    $wshosho_record = $wshosho_sorted.Where( { $_.DATE          -le $date }, 'Last' )

    $rrp_item     = [decimal] $rrp_record.totalAmtAccepted
    $wshosho_item = [decimal] $wshosho_record.WSHOSHO

    [PSCustomObject]@{
        date    = $date
        wshosho = $wshosho_item
        rrp     = $rrp_item
    }
}

# White Owl's approach Here's an approach based on White Owl's answer below.

SELECT
RRP.DATE,
(
	CASE WHEN WSHOSHO.DATE IS NULL
		THEN
		(
			SELECT TOP(1) TBL.WSHOSHO FROM WSHOSHO as TBL WHERE TBL.DATE < RRP.DATE ORDER BY TBL.DATE DESC
		)
		ELSE
			WSHOSHO.WSHOSHO
		END
) AS WSHOSHO,
RRP.RRP
FROM RRP LEFT JOIN WSHOSHO ON (WSHOSHO.DATE = RRP.DATE);

As most, if not all, implementation of SQL use the ASC keyword by default in an ORDER BY clause when it is omitted. Are there valid use case for the ASC keyword? I am NOT asking for psychological reasons, opinions, good practices, readability or anything fabricated around SQL that can change from one person's perspective to another.

If I create a table **T**, I become the owner of the table. Can DBA access **T**? Can DBA also revoke my access or grant privileges on **T**?

I am trying to design a database relational model for an assignment. First, I design the ER diagram, and then the relational model with tables (schemas). Here's my question: Is it possible for an entity to have only one attribute? To put a bit more to this: is it possible or logical to come to a relation with a single attribute in the course of decomposition? If that happens, and is accepted, how should one proceed?

Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these stored properties. For example, say I've got a database with kitchen equipment. Base properties would include things like make, model, and description. Of the pieces of kitchen equipment, some use electricity, some use gas, some use water, some need a drain, some need fire protection, some need routine maintenance, etc. These properties are generally not Boolean flags, but are more of a rich collection of classes themselves. So, if I want to keep all of my "equipment" in one table, I've got to find a way to store all of these properties. I'm using a decorator to add properties to objects instead of using inheritance to reduce the number of classes, as I don't want to have 2^(number of properties) classes. Two things I've come up with so far are table per property and field per property. Table per property has many more joins to reconstruct the object and field per property has a lot of empty fields for many objects, and requires adding a field whenever I introduce a new unique property. Is there some (sane) way to address this other than to use one of the two methods mentioned above? I know that this can be done in some NoSQL databases, this question specifically concerns relational databases.

I wonder how databases guarantee that two transactions with different isolation levels run concurrently correctly. That is, different sessions are allowed to use different isolation levels. For example, one session may use "serializable" and the other may use "read committed". How do databases guarantee that two transactions with different isolation levels run concurrently correctly? I tried to google this topic but could not find any material explaining this topic in detail.

Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about other dbms?

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that it can only be set on a single row.