Tag: tables

Multiple Primary-Foreign Key connections between Tables Redundant?

I have two tables, Reviews and Critic

enter image description here

I’m pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?

My reasoning is that a single critic can have many reviews but each review is unique so I need a way to enforce the uniqueness of the Reviews. I did this via Review.rID which is unique.

However, I am already connecting the critic to the review by including a criticID within Review, so is it necessary for me to also connect a reviewID to a critic within Critic?

How to get multiple max values for 1 column form multiple tables

I am using sqlplus and i need to achieve this result, where stipend is the max stipend for each faculty

There are 3 tables, student ( with name and surname), faculty ( with faculty name) and money (with the stipends), connections are faculty to student and student to money

This is my current code, which onyly returns one main maximums from one faculty

SELECT ROW_NUMBER() OVER (ORDER BY faculty_name DESC) AS "Nr.",  faculty.faculty_name,  student.surname, student.name, money.stipend AS "STIPEND" FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id GROUP By money.stipend, faculty.faculty_name, student.surname, student.name having max(money.stipend) = ( select max(stipend) FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id );

how would i get this end result End result

Benefits of not having a clustered index on tables (Heaps)

What are the benefits of not having a clustered index on a table in SQL server. Will a

SELECT * INTO TABLE_A FROM TABLE_B

Be faster if TABLE_A is a heap? Which operation will benefit if the table is a heap? I am quite sure UPDATE‘s and DELETE‘s will benefit from a clustered index. What about INSERTS? My understanding is that INSERT "might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage…).

What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with SELECT, INSERT, UPDATE and DELETE? What cost goes up when table is a heap and we SELECT, UPDATE and DELETE from it?

Thansk

Ensuring relationship between child tables exists prior to SQL insert

I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.

I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.

The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I’m wondering if I can accomplish both in one query though. I’m not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.

How to delete all records which are not referenced from other tables

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?

I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.

There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.

Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

Is a relational database with a dynamic number of tables a good design?

I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.

This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.

The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:

  1. On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y.
  2. On the creation date column with LIMIT X.

I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.

In future when one of these entities have fulfilled their purpose, the table would be simply deleted.

I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by. This design makes data easy to query as my application can determine the table name from the entity name.

Is this a bad design?

Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?

Should I use any different datastore to achieve this other than RDBMS? Any suggestions?

Multi-level paging where the inner level page tables are split into pages with entries occupying half the page size

A processor uses $ 36$ bit physical address and $ 32$ bit virtual addresses, with a page frame size of $ 4$ Kbytes. Each page table entry is of size $ 4$ bytes. A three level page table is used for virtual to physical address translation, where the virtual address is used as follows:

  • Bits $ 30-31$ are used to index into the first level page table.
  • Bits $ 21-29$ are used to index into the 2nd level page table.
  • Bits $ 12-20$ are used to index into the 3rd level page table.
  • Bits $ 0-11$ are used as offset within the page.

The number of bits required for addressing the next level page table(or page frame) in the page table entry of the first, second and third level page tables are respectively

(a) $ \text{20,20,20}$

(b) $ \text{24,24,24}$

(c) $ \text{24,24,20}$

(d) $ \text{25,25,24}$

I got the answer as (b) as in each page table we are after all required to point to a frame number in the main memory for the base address.

But in this site here it says that the answer is (d) and the logic which they use of working in chunks of $ 2^{11} B$ I feel ruins or does not go in with the entire concept of paging. Why the system shall suddenly start storing data in main memory in chucks other than the granularity defined by the page size of frame size. I do not get it.

How to make multiple right joins in one single command for different child tables?

On the followin image you see 3 tables: A, B , C. A: Is the table that holds the foreign keys from B and C

enter image description here

enter image description here

If I do a right join between A and B on that foreign key, it works, meaning I get the data from column fooB. Same between A and C with fooC.

But If I put both right joins under the same command it fails with null

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | NULL        | abc           | | NULL        | xyz           |

*This is slightly different as the image with the commands posted below, as this comes straight from the real deployment, however cmds below still show that both columns do not show up.

I’ve tried mixin up left joins, right joins , outer joins all with same result. I have googled about this, but the way my question is formulated is awkard and the hits I get return the traditional right joins for just one table.

How do I get this to work in one command? CMDS Below:

enter image description here

Same commands as above but in text, as comments are asking for text rather than images:

MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooC from tableA right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooC | +------+ | c1   | | c2   | | c3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+

–HERE IS THE ENTIRE SCRIPT–

-- MySQL Workbench Forward Engineering  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `joinPOC` ;  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `joinPOC` ; USE `joinPOC` ;  -- ----------------------------------------------------- -- Table `joinPOC`.`tableB` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableB` (   `idtableB` INT NOT NULL,   `fooB` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableB`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableC` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableC` (   `idtableC` INT NOT NULL,   `fooC` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableC`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableA` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableA` (   `idtableA` INT NOT NULL,   `fooA` VARCHAR(10) NOT NULL,   `tableB_idtableB` INT NOT NULL,   `tableC_idtableC` INT NOT NULL,   PRIMARY KEY (`idtableA`),   INDEX `fk_tableA_tableB_idx` (`tableB_idtableB` ASC),   INDEX `fk_tableA_tableC1_idx` (`tableC_idtableC` ASC),   CONSTRAINT `fk_tableA_tableB`     FOREIGN KEY (`tableB_idtableB`)     REFERENCES `joinPOC`.`tableB` (`idtableB`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   CONSTRAINT `fk_tableA_tableC1`     FOREIGN KEY (`tableC_idtableC`)     REFERENCES `joinPOC`.`tableC` (`idtableC`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;   SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableB` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (1, 'b1'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (2, 'b2'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (3, 'b3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableC` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (1, 'c1'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (2, 'c2'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (3, 'c3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableA` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (1, 'a1', 1, 1); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (2, 'a2', 2, 2); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (3, 'a3', 3, 3);  COMMIT;

Desired output in a single command:

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | b1          | c1            | | b2          | c2            |

Are MS SQL’s temporal tables here to stay?

We’re considering different approaches to save records’ history in an application.

Some of the managers in charge are concerned that as this is a relatively new feature of SQL server, Microsoft may cease to support it.

Are these concerns justified?
Is there a roadmap to this feature that says what MS intends to do with this feature in the future?