I want to create a trigger which copies the record from **maintable** to **undostack** before deleting it in **maintable** and undostack table acts a stack of 5 records space and this trigger performs a push operation. This is my trigger

CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN

When undostack is not full

IF (SELECT COUNT(*) FROM (SELECT * FROM undostack)u)<5 THEN
INSERT INTO undostack VALUES(((SELECT COUNT(*) FROM undostack)+1),
(SELECT id FROM maintable WHERE id=OLD.id),
(SELECT descp FROM maintable WHERE descp=OLD.descp),
(SELECT cat FROM maintable WHERE cat=OLD.cat),
(SELECT dat FROM maintable WHERE dat=OLD.dat),
(SELECT amt FROM maintable WHERE amt=OLD.amt));

When undostack is full and has to delete the oldest record entered by updating. here undo_no acts as pointer.

ELSE

1<-2

UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=2)
WHERE undo_no=1;

2<-3

UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=3),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=3),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=3)
WHERE undo_no=2;

3<-4

UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=4),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=4),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=4)
WHERE undo_no=3;

4<-5

UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=5),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=5),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=5)
WHERE undo_no=4;

5<-update with the values which are to be deleted from maintable

UPDATE undostack SET
id=(SELECT id FROM maintable WHERE id=OLD.id),
descp=(SELECT descp FROM maintable WHERE descp=OLD.descp),
cat=(SELECT id FROM maintable WHERE cat=OLD.cat),
dat=(SELECT id FROM maintable WHERE dat=OLD.dat),
amt=(SELECT amt FROM maintable WHERE amt=OLD.amt)
WHERE undo_no=5;
END IF;
END

The trigger has no errors but when I execute

DELETE FROM maintable WHERE id=10;

There is following error ERROR 1242 (21000):Subquery returns more than 1 row Please help me with this error I'm using MySQL 5.7.3

I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies. The two popular replacement startegies mentioned are least recently used - LRU and clock replacement. I am interested in details about LRU. LRU is implemented via queue. When a pin count is set to 0, page in memory is added to queue. When a replecement is needed, memory page from the start of the queue is used. The book does not describe how pin count (and dirty bit) for a page is stored. Is it stored inside the queue entries? Is it stored as part of the header in buffer pool frames? Moreover, what happens if a page is accessed while being in the queue? Is it deleted from the queue immediately?

I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know the number of columns then I can create a temp table with that amount of columns and then copy the csv file in like so: CREATE TEMPORARY TABLE temp ( col1 VARCHAR(80), col2 VARCHAR(80), .... coln VARCHAR(80) ); COPY temp FROM 'C:/Users/postgres/Boost.txt' CSV HEADER DELIMITER E' ' However if I try to just simply copy the csv file to a temp table with no columns in the temp table, Postgresql (version 8.4) complains that I'm working with a table that has less columns than in the csv file. I've been researching and can't seem to find anything in the Postgresql docs about this. Does anyone know if copying a csv file into a temp table with an arbitrary number of columns decided at run time is possible in Postgresql? Once the temp table is loaded with the csv file I plan on doing some comparisons to other tables with the temp table before it is destroyed. Also the first row in the csv file contains headers.

I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables. CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name)); CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id), foreign key(dept_name) references Department); CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID), foreign key (dept_name) references Department); I had a look at https://dba.stackexchange.com/questions/212787/error-1239-incorrect-foreign-key-definition but it didn't seem to help.

I have moved into a DBA role where I now manage multiple types of DBs. I used to manage only MS SQL and MySQL, but now I am managing MS SQL, Azure SQL, MySQL, MariaDB and PostgreSQL. I am currently using: - SSMS: MS SQL and Azure SQL - Workbench: MySQL and MariaDB - pgAdmin: PostgreSQL Because of the number of DBs and clients I service, I would like to use only 2 DBMSs (SSMS for MS DBs and another for the other DBs). I just want some real-world experience with DBMS that will be able to manage MySQL, MariaDB and PostgresDBs. I know Heidi is popular, but I don't like the interface. If it is my only real option, I will give it another shot. I am willing to look at paid versions as well if it can assist in managing the 3 environments from the 1 DBMS.

Suppose I have this database

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Roll INT NOT NULL,
    CustomID INT UNIQUE
);


INSERT INTO Students (StudentID, Name, Roll, CustomID)
VALUES
    (1, 'John Doe', 101, 1),
    (2, 'Jane Smith', 102, 2),
    (3, 'Bob Johnson', 103, 3);


CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    ClassName VARCHAR(255) NOT NULL,
    CustomID INT UNIQUE,
    FOREIGN KEY (CustomID) REFERENCES Students(CustomID)
);


INSERT INTO Classes (ClassID, ClassName, CustomID)
VALUES
    (1, 'Class 10', 1),
    (2, 'Class 11', 2),
    (3, 'Class 12', 3);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255) NOT NULL,
    CustomID INT,
    FOREIGN KEY (CustomID) REFERENCES Students(CustomID)
);


INSERT INTO Courses (CourseID, CourseName, CustomID)
VALUES
    (101, 'Mathematics', 1),
    (102, 'Physics', 1),
    (103, 'Chemistry', 1),
    (104, 'Literature', 2),
    (105, 'History', 2),
    (106, 'Biology', 3),
    (107, 'Computer Science', 3);


CREATE TABLE StudentCourses (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

INSERT INTO StudentCourses (EnrollmentID, StudentID, CourseID)
VALUES
    (1, 1, 101),
    (2, 1, 102),
    (3, 1, 103),
    (4, 2, 104),
    (5, 2, 105),
    (6, 3, 106),
    (7, 3, 107);

CREATE VIEW StudentInformation AS
SELECT
    Students.StudentID,
    Students.Name AS StudentName,
    Students.Roll,
    Classes.ClassName,
    STRING_AGG(Courses.CourseName, ', ') AS EnrolledCourses
FROM
    Students
JOIN
    Classes ON Students.CustomID = Classes.CustomID
LEFT JOIN
    StudentCourses ON Students.StudentID = StudentCourses.StudentID
LEFT JOIN
    Courses ON StudentCourses.CourseID = Courses.CourseID
GROUP BY
    Students.StudentID, Students.Name, Students.Roll, Classes.ClassName;

If I create a view, I get this output

StudentID StudentName Roll ClassName EnrolledCourses
1 1 John Doe 101 Class 10 Mathematics, Physics, Chemistry
2 2 Jane Smith 102 Class 11 Literature, History
3 3 Bob Johnson 103 Class 12 Biology, Computer Science

Now suppose I have a grid where I provide this view data for displaying, also the grid can provide me filter options like Roll greater than X, Name starts with 'Y'. For example, the grid can provide me query like, 'WHERE StudentName = 'A', the grid filter will provide me only the part of the WHERE clause. I want to perform another query after the filter, using my previous view create query and the filter query provided by the grid. So it can look this,

'SELECT * ..... from Studente WHERE StudentName = 'A'....'

. But here StudentName is aliased which should be Students.Name. Is there any way to keep track of the column name without managing a separate table to map the column names and aliases? Also, I can't use Select ... from StudentInformation'. The view is not directly passed to the grid.

In teradata sql I get an error when I use sel * from table where sk = null However if I include this in a stored procedure and pass a null value in the parameter I don't. replace procedure(In name Integer,...) .... local = sel * from table where sk = name ...... Call procedure(name = null,...) What is happening in both these cases? Shouldn't it give an error in the second case too? I understand we need to use **is null** in the first case but why does it work in the second scenario?

In DBMSes like MySQL, MariaDB, PostgreSQL and MongoDB, data is saved as BSON, JSON or JSONB for JSON columns or document. All of these data types use space for key names in JSON. Does internal data on disk also require space for every key name on every row? If yes, shouldn't a developer/admin care about it, for performance and storage cost reasons? **Is there any solution or are there any attempts to respond to these concerns?**

I am a java full stack developer who want to write oracle sql queries to simplify writing is there any mysql workbench like similar gui tool for oracle by oracle or any other please sugest

# The situation # I have two MySQL instances, deployed on two different remote machines. Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is provided by Gluster, but that's not really relevant, I'm only saying this for the sake of completeness). I'm aware that it is strongly discouraged to run two MySQL servers using the same data directory, but it's not an impossible thing to do, as long as one takes the right precautions. In order to do this, I followed the steps suggested by the official documentation ( ), more specifically in the paragraph named **Warning**. To sum it up: - I'm using MySQL 5.6. - I'm using MyISAM as default engine, as required. - I specified log file names that are unique to each server. - One server is going to be read-write, while the other server is going to be read-only. So, I set everything up, I imported a test database and everything worked flawlessly. Both servers could read data from the same data directory. # The problem # Now comes the problem. As soon as one server performs a write (by inserting a row into a table), the other server marks the table as corrupted for itself. After looking into the problem for a while, I found out why this happened. Basically each server holds its own metadata describing each table. So, as soon as the first server updated a table, it also updated its own metadata, but it didn't update the metadata of the second server. As the second server checked the table, it noticed a mismatch between the current number of rows in the table and the number of rows it had previously saved in its metadata, therefore it marked the table as corrupted. After some research, it turns out that the metadata is saved inside a default database named *information_schema*. At first, I thought of making the two server share the same *information_schema*, but this was nowhere to be found. As I later discovered, it's saved inside the memory of the program and there's no way you can manually update or access it. # The questions # 1. Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place? 2. Most importantly, do you know how to make this work, using MySQL? 3. If not, do you know of any other DBMSs that allow you to obtain this result (that is, running two servers that use the same data directory)? Just keep in mind that I don't want to set up master-slave replication. All other suggestions are welcome, thanks everyone for the help. EDIT: I ended up not doing it, as everyone discouraged me from doing it. Thanks everyone for your input!

I am creating an double entry accounting software where I have ledger table along with other tables. My cash account ledger table for is similar to following: | ledger_id | ledger_name | ledger_trnx_ammount| ledger_trnx_type | ledger_last_balance| |-----------|---------------|--------------------|------------------|--------------------| |1 | cash | 500 | dr | 500 | |2 | cash | 300 | dr | 800 | |3 | cash | 500 | dr | 1200 | |4 | cash | 200 | cr | 1000 | |5 | cash | 400 | dr | 1400 | |6 | cash | 300 | cr | 1100 | Supose I have edited the entry where ledger_id is 2. I have updated it's ledger_trnx_ammount from 300 to 400, now ledger_last_balance should also go up from 800 to 900. This make following entries inconsistent. To mitigate this issue I have to update all the following entry's ledger_last_balance to be consistent with the change like following table: | ledger_id | ledger_name | ledger_trnx_ammount| ledger_trnx_type | ledger_last_balance| |-----------|---------------|--------------------|------------------|--------------------| |1 | cash | 500 | dr | 500 | |2 | cash | 400 | dr | 900 | |3 | cash | 500 | dr | 1300 | |4 | cash | 200 | cr | 1100 | |5 | cash | 400 | dr | 1500 | |6 | cash | 300 | cr | 1200 | In this table I have updated the entry no. ledger_id = 2. To make all the entries last balance consistent I just updated all the following entry's last balance. This will not be an issue if there are only few entries, but, if there are millions of entries in that table, will updating half the entries or all the entries from that table in this way be efficient? If not, then what should be the approach? Should I redesign the schema or just change the strategy to update the table? Footnote: I am using MySQL with NodeJS.

In a DBMS like MySQL, are the underlying files that contain the data encrypted since we want to have role and user based control? If they are encrypted, what encryption keys and algorithms are used? If they are not, how is the data safe from access (by a malicious user directly accessing the file)? My guess is that directly opening a file wont work as the DBMS might use a proprietary binary format for writing data to files. As for encryption, I couldn't find any good reference detailing the encryption process. I am curious about how the DBMS prevents a malicious user from directly accessing the underlying file and also how a subset of authorized users can be dynamically granted access.

Here, Read_item(B); and B;=B+temp; have written parallelly. what happen here? What is the problem associated with the above transaction schedule?

Currently I am working on a project of a management software. This software is currently in built with an old technology (Codeigniter-PHP). My job is to convert this software to latest technology (Express-NodeJS). But the database will remain the same (MySQL). Whatever, currently this software has **400**+ users (The number is going to increase to **3000+** soon). Each user is given the different codebase along with different database (Just the copy of the main code and the database schema for everyone). Which means software and database schema is different for every one. Each database has **212** tables. As my job is to upgrade this software I will make sure that everyone will use the software from one code base. **Here comes my question:** As there are already **400+** databases (each database has 212 tables), should I use all those database from a single codebase (application) or should I use single database for all the customers? I was thinking about sharding, but sharding is done at table level, my problem is at database level.

I'm comparing the advantages and disadvantages of File system (journaling and COW) and Database (SQL and Non-SQL). Not only from the developer's point of view, I also want to know further what are the challenges and difficulties in maintaining these two systems. Could you guys share your thoughts?

What is the difference between covering and overlapping constraints use in DBMS?

In following table how to print the student number when age occur two or more times, The result should be only column Sno i.e.
**Result** | Sno | Reason | |:-----:|:------:| |1 |because 18 occur two times| |2 |because 20 occur three times| |4 |because 20 occur three times| |6 |because 20 occur two times| |8 |because 18 occur two times| |10 |because 18 occur two times| **Student Table** |Sno|Sname|Smarks|Sage| |:----:|:----:|:----:|:----:| |1|A|90|18| |2|B|94|20| |3|C|87|21| |4|D|95|20| |5|E|97|19| |6|F|80|20| |7|G|85|22| |8|H|96|17| |9|I|84|20| |10|J|93|18|

I want to make an ER diagram for scientific research papers, one of the constraints is that a paper shouldn't cite itself. I'm not very sure how to achieve this, as [this](https://dba.stackexchange.com/questions/162539/entity-set-that-has-a-relationship-with-itself) question points out, the ER diagram doesn't convey whether a paper can cite itself or not. Is there a way to achieve this in ER diagrams?

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, animated GIF do not deserve CPU time slices), the same way I do for C/C++ software builds, using a nice on Darwin and Linux supervisors. **Can I do that?** **Can I do that in vanilla PostgreSQL?** Could I even couple _initial_ job prioritisation with increasing to normal priority after a given timeout? Highways as real world analogy: in a normal situation there are slow traffic lanes and fast traffic lanes; but in a dense traffic situation all lanes get relatively slow, then unless for a very specific exceptional emergency services _job_ that appropriates the lane. Is this an oversight for the need of another family of DBMS (o-o, columnar, dwh...)?

I am trying to get a better understanding of an answer by @GarethD in https://stackoverflow.com/questions/24188350/incorrect-syntax-near-keyword-option-in-cte-statement?rq=1#answer-24188586 . In that answer he states: > If you think of a view more as a stored subquery than a stored query … and remember that its definition is expanded out into the main query … this seems to imply that a view is more-or-less a saved string which is inserted as a subquery at the right spot. In the context of the answer it was to explain why a view in SQL Server can’t include hints, because you can include them in subqueries. I know that DBMSs are free to do things their own way, and that it may not _literally_ be as I described, but it seems to boil down to the same. Is this a correct understanding of views? Would this also apply to Table Valued Functions, which are effectively parameterised views? Does this vary from DBMS to DBMS. That is, do some DBMSs add more features to views?