Questions tagged [rdbms]

Relational Database Management System, a widely used type of database management system characterised by its extensive use of joining as a core operational principle.

Filter by
Sorted by
Tagged with
0
votes
0answers
14 views

Should I create a table replicating a CDN (ex. S3 storage bucket)? [closed]

This question can pertain to any CDN, but I am specifically using AWS' S3 and MySQL. Currently I have about 50 different tables that have columns referencing media stored in a single S3 bucket. For ...
-4
votes
1answer
45 views

test if the solution & SQL understanding is correct [closed]

Employee table- has several attributes such as EMP_ID, TITLE, DEPARTMENT, etc. a date column enabling us to know when was it updated. what does this query do? SELECT emp_id ,COUNT(*) as ...
0
votes
2answers
52 views

How to store daily records in SQL Server 2017 database? [closed]

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries. Example:...
0
votes
1answer
30 views

in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

i was wondering how a sql update works in two scenarios: table with 3 columns: a, b, c say the data value for a has to be changed. 1: update on a row setting all columns a, b, c (where b and c have ...
1
vote
1answer
56 views

MYSQL - How to optimize VIEW performance?

Total profiles - 100000 Each profile is connected to one or more platform Each profile have 50 Tags Each profile have 4 themes I have a user view (total users around 100000) where I am joining all ...
0
votes
0answers
13 views

Custom methods in MySQL / MariaDB ORDBMS

According to the book I am reading ORDBMSs come with an option to write a program (method) in Java (or other programming language), compile it into Java byte code and register via some SQL command. ...
1
vote
1answer
48 views

Physical co-clustering of two or more logical relations/tables in modern RDBMS

One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join ...
0
votes
2answers
63 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
2answers
35 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
0
votes
1answer
40 views

Where MySQL is taking time either in disk read or computation? [closed]

From SQl case study System configuration is Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G) Query is select ...
0
votes
1answer
50 views

MySQL block read time?

My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory....
0
votes
1answer
60 views

Big data table processing with RDBMS like MySQL?

I have two tables Customers and Orders and each has 100 million records. Now I have to join these two tables on the column customer_id in both Customer and Order table to generate a report. ...
0
votes
1answer
28 views

Disk block per table?

At T1, insert comes for table_1, goes to disk block_1. At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ? At T3, ...
1
vote
0answers
38 views

Understanding connection between different database concurrency control protocols

I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.): I don't know ...
1
vote
1answer
77 views

Schema design for products with redundant values

so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the ...
1
vote
1answer
61 views

Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, ...
5
votes
2answers
198 views

Does the concept of candidate key exist only in theory?

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
-1
votes
1answer
103 views

Speed up LEFT JOIN with OR operator in ON clause

I have a query that uses a LEFT JOIN with an OR operator within the ON clause. When commenting out the OR operator the query runs in ~150ms. With the OR operator kept in the query takes over 80s to ...
0
votes
1answer
272 views

MySQL schema design - how to decide between an `enum` vs. a table join

In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as: Stackoverflow post on efficiency of ...
0
votes
1answer
50 views

Unable to create DB trace using DEA

I want to create a DB trace file using DEA(Database Experimentation Assistant). I am using below softwares: -SQL Server Express 2012 on Win10 machine where my DB is present. -DEA is installed on a ...
0
votes
1answer
140 views

How can Update table column with the virtual column?

I want to update repetitive columns, And I build virtual column for did this. I can "select" but can't "update" Here is SQL Fiddle : http://sqlfiddle.com/#!9/d09189/3 Also Code review : I have ...
0
votes
1answer
30 views

Predefined rows conflicting with user entered rows

Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them? I could want to distribute an application with a table called ...
1
vote
2answers
58 views

How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow? [closed]

In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier; https://aws.amazon.com/ec2/instance-types/t2/ It has: 20 ...
0
votes
1answer
352 views

Designing MySQL Database For Two Different User Types (Staff and Client)?

I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page. Both Staff and Client share the following ...
0
votes
1answer
250 views

What is the point of a consistent and partition tolerant multi-master DB Architecture as opposed to Master-Slave

I should emphasize that my question is in regard to CP (Consistent and Partition Tolerant) systems. A lot of widely used NoSQL databases such as Mongo and Redis seem to fall in the CP category. And ...
1
vote
0answers
76 views

Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has ...
0
votes
1answer
34 views

efficient primary key that does not expose business internals

I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks. Keys are short, efficient, but are auto incremented in a ...
0
votes
1answer
71 views

Oracle Sequential or parallel read?

From web server i initiate two separate concurrent threads/requests to read from two different DBMS(oracle) tables(employee and department). Both web server and oracle are on separate node(quad core ...
0
votes
1answer
98 views

Skip ERD in database implementation [closed]

AS we know that entity relation diagram(ERD) is an important concept in database implementation, But I was wondering that can we skip this step when creating a small database containing a few tables i....
1
vote
1answer
33 views

@OneToMany mapping but only one is needed

I have to create a new table to store version numbers of Terms&Conditions user have accepted and the time stamp of when they click accept. (This table only contains info related to user. Info ...
1
vote
1answer
40 views

How does this replication behaviour work in a RDBMS?

Consider a 3 node (N1, N2, N3) system N1 is master (used for writes) and N2, N3 (used for reads) are slaves Write request comes to update R to R1, N1 writes to disk and crashed without replication ...
1
vote
4answers
2k views

Handling currencies in a database

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies? ...
1
vote
1answer
202 views

Using RDBMS with pseudo foreign keys VS using NoSQL solutions

According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this ...
5
votes
1answer
635 views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

As a follow-up to this question, I have one of my own. The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
0
votes
2answers
175 views

what is difference between RDBMS and NoSql on basis of how information is store in them?

I am searching web to understand the underlying concept of how actually information is stored in RDBMS and Nosql databases and found this: In the world of database technology, there are two main ...
1
vote
0answers
55 views

Does two phase locking apply to individual transactions, or the schedule as a whole?

Problem I have conflicting perceptions of when a schedule violates 2PL or not. My first perception is that 2PL only applies to the level of a transaction at a time, since this is what I learned in a ...
1
vote
0answers
36 views

Equivalent of stored procedures for other database systems?

I am trying to compare centralized, distributed database systems and blockchains in several points and got stuck when searching about 'stored procedures'. So far, I have thought that this is a ...
3
votes
1answer
261 views

Modelling the structure of a database involving products with multiple barcodes

What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 ...
1
vote
1answer
122 views

Is normalizing further to index on INT FK rather than VARCHAR worth it?

I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not ...
1
vote
0answers
857 views

RDBMS sports league database

I am trying to create a database structure that will allow me to store records of league matches across multiple seasons and divisions. I have the following rules that need to be maintained: A league ...
2
votes
2answers
69 views

Should I repeat UNIQUE constraint on similar multi-index?

The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns. I have MySQL/MariaDB in mind but my questions certainly ...
1
vote
1answer
234 views

Organizing a comments table: How to justify the choice?

Business domain description Some of the entity types of interest with respect to the relevant business domain are money, user and progress. The users may need to post comments about money and ...
0
votes
1answer
316 views

What is a staging table and how do you protect data loss when transferring the data?

What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with ...
-1
votes
1answer
57 views

Authorized, all privileged DB user and a DB instance with the same name - does it have a name? [closed]

Is there a term that encompasses an authorized, all-privileged DB user and a DB instance with the same name? Should I call this a DB stack / DB unit / DB packet / something else? Does this "stack" ...
0
votes
1answer
613 views

Postgresql.conf not found

I was trying to install postgresql by this tutorial http://powa.readthedocs.io/en/latest/quickstart.html to use the tool for bd Powa. I am use S.O. debian 8 Jessie. I ran: apt-get install ...
2
votes
3answers
3k views

What is the difference between views and materialized views

What is the difference between PostgreSQL's materialized views and MySQL's views?
0
votes
1answer
295 views

Postgresql install extension

I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8. I would like to use the HIPOPG extension 1, 2 ...
1
vote
0answers
145 views

DBMS Query Preprocessor Checks Semantics or Syntax?

I encountered the following statement when reading about the constituents of a query processor in a DBMS: ... (b) A query preprocessor, which performs semantic checks on the query (e.g., making ...
0
votes
1answer
23 views

_Batch_ priorities in PostgreSQL

I would want to tag many queries and mostly materialised view refreshing tasks as low priority in my RDBMS, in the fashion I run the web browser on the local machine as low priority (because JS, CSS, ...
2
votes
0answers
134 views

Understanding multivalued dependencies

This is my first time studying multivalued dependencies and there is a question that was posted by my lecturer as a class exercise. I have an answer, but I'm not 100% sure it was right. Please let me ...