How to use the delete query correctly

Question

I'm pretty new to DBMS and needed some advice on how to write delete for this question. I've been googling around and this is what I came up so far. The query will not run and is throwing an error:

Violated - child record found

This is the question:

Delete member David Jones and all the relevant records. Make sure you delete related tuples from all the relevant relations, and delete them in the right order.

Code:

CREATE TABLE Book
    (bookID INT,
    ISBN INT,
    title varchar (25),
    author  varchar (20),
    publish_year INT,
    category varchar(20),
    PRIMARY KEY (bookID));  

CREATE TABLE Member
    (memberID INT,
    lastname varchar (20),
    firstname varchar (20),
    address varchar(20),
    phone_number INT,   
    limit_ INT,
    PRIMARY KEY (memberID));

CREATE TABLE CurrentLoan
    (memberID INT ,
    bookID INT,
    loan_date DATE,
    due_date DATE,
    PRIMARY KEY (memberID, bookID),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

CREATE TABLE History
    (memberID INT,
    bookID INT,
    loan_date DATE,
    return_date DATE,
    PRIMARY KEY (memberID, bookID, loan_date),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

INSERT INTO Book VALUES (10, 1113312336, 'The Dog', 'Jack Crow', 1990, 'fiction');
INSERT INTO Book VALUES (12, 2221254896, 'Worms', 'Jim Kan', 2013, 'childrens');
INSERT INTO Book VALUES (13, 3332546987, 'Crow', 'Jan Flo', 2000, 'fiction'); 
INSERT INTO Book VALUES (14, 4443456215, 'Big Dog', 'Lan Big', 1993, 'children');
INSERT INTO Book VALUES (15, 5552314569, 'Green Apple', 'Theo Brown', 1978, 'children');
INSERT INTO Book VALUES (16, 6664581631, 'Red Bean', 'Khang Nk', 2017, 'fiction');
INSERT INTO Book VALUES (17, 7771452369, 'XML and XQuery Knowledge', 'Author Le', 2017, 'non-fiction');
INSERT INTO Book VALUES (18, 8881245525, 'The Dark Room', 'Jack Se', 2017, 'fiction');
INSERT INTO Book VALUES (19, 9991123546, 'Lonely Mens', 'Geen Brown', 2014, 'refrence');
INSERT INTO Book VALUES (20, 1122112356, 'XML or XQuery', 'Heart Le', 2002, 'fiction');
INSERT INTO BOOK VALUES (21, 1212121212, 'The Giving Tree', 'Shel Silverstein', 1964, 'fiction');
INSERT INTO BOOK VALUES (22, 1212121212, 'The Giving Tree', 'Shel Silverstein', 1964, 'fiction');
INSERT INTO BOOK VALUES (23, 1313131313, 'The Lazy Dog', 'Jake Red', 2016, 'children');
INSERT INTO BOOK VALUES (24, 1313131313, 'The Lazy Dog', 'Jake Red', 2016, 'children');
INSERT INTO BOOK VALUES (25, 1414141414, 'The Very Blue Boy', 'Ben Jen', 2006, 'fiction');

INSERT INTO Member VALUES (001, 'Lee', 'Nancy', 'Brownlea Drive', 1254896325, 2);
INSERT INTO Member VALUES (002, 'Le', 'Ray', '10th Street', 1234561256, 2);
INSERT INTO Member VALUES (003, 'Kan', 'Charlie', '5th Street', 1234567236, 2);
INSERT INTO Member VALUES (004, 'Brown', 'Joe', 'Elm Street', 1234567845, 2);
INSERT INTO Member VALUES (005, 'Smith', 'John', '33 East', 1234567890, 2);
INSERT INTO Member VALUES (006, 'Khang', 'Nkaujyi', '358 Spencer', 2145345625, 2);
INSERT INTO Member VALUES (007, 'Jones', 'David', '4th Street', 14812365789, 2);

INSERT INTO CurrentLoan VALUES (001, 10, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (002, 19, '13-JAN-17', '15-NOV-17');
INSERT INTO CurrentLoan VALUES (003, 16, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (005, 15, '12-OCT-17', '09-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 18, '13-APR-17', '12-MAY-17');
INSERT INTO CurrentLoan VALUES (007, 25, '02-OCT-17', '25-DEC-17');

INSERT INTO History VALUES (001, 10, '14-Jan-17', '04-OCT-17');
INSERT INTO History VALUES (003, 19, '12-Jan-17', '04-NOV-17');
INSERT INTO History VALUES (002, 13, '14-APR-17', '08-OCT-17');
INSERT INTO History VALUES (005, 20, '14-MAY-17', '04-DEC-17');
INSERT INTO History VALUES (007, 25, '02-OCT-16', '25-DEC-16');
COMMIT;

My Query:

DELETE FROM Book
WHERE bookID = 25;

DELETE FROM MEMBER 
WHERE lastname = 'Jones' AND firstname = 'David';

DELETE FROM CurrentLoan
WHERE memberID = 007;

DELETE FROM History
WHERE memberID = 007;

Answer 1

Method to delete all member records named David Jones amd related records. Use the following to create the schema and dummy data. Notice there are two records for David Jones.

CREATE TABLE Book
    (bookID INT,
    ISBN INT,
    title varchar (25),
    author  varchar (20),
    publish_year INT,
    category varchar(20),
    PRIMARY KEY (bookID));  

CREATE TABLE Member
    (memberID INT,
    lastname varchar (20),
    firstname varchar (20),
    address varchar(20),
    phone_number INT,   
    limit_ INT,
    PRIMARY KEY (memberID));

CREATE TABLE CurrentLoan
    (memberID INT ,
    bookID INT,
    loan_date DATE,
    due_date DATE,
    PRIMARY KEY (memberID, bookID),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

CREATE TABLE History
    (memberID INT,
    bookID INT,
    loan_date DATE,
    return_date DATE,
    PRIMARY KEY (memberID, bookID, loan_date),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

INSERT INTO Book VALUES (10, 1113312336, 'The Dog', 'Jack Crow', 1990, 'fiction');
INSERT INTO Book VALUES (12, 2221254896, 'Worms', 'Jim Kan', 2013, 'childrens');
INSERT INTO Book VALUES (13, 3332546987, 'Crow', 'Jan Flo', 2000, 'fiction'); 
INSERT INTO Book VALUES (14, 4443456215, 'Big Dog', 'Lan Big', 1993, 'children');
INSERT INTO Book VALUES (15, 5552314569, 'Green Apple', 'Theo Brown', 1978, 'children');
INSERT INTO Book VALUES (16, 6664581631, 'Red Bean', 'Khang Nk', 2017, 'fiction');
INSERT INTO Book VALUES (17, 7771452369, 'XML and XQuery Knowledge', 'Author Le', 2017, 'non-fiction');
INSERT INTO Book VALUES (18, 8881245525, 'The Dark Room', 'Jack Se', 2017, 'fiction');
INSERT INTO Book VALUES (19, 9991123546, 'Lonely Mens', 'Geen Brown', 2014, 'refrence');
INSERT INTO Book VALUES (20, 1122112356, 'XML or XQuery', 'Heart Le', 2002, 'fiction');
INSERT INTO BOOK VALUES (21, 1212121212, 'The Giving Tree', 'Shel Silverstein', 1964, 'fiction');
INSERT INTO BOOK VALUES (22, 1212121212, 'The Giving Tree', 'Shel Silverstein', 1964, 'fiction');
INSERT INTO BOOK VALUES (23, 1313131313, 'The Lazy Dog', 'Jake Red', 2016, 'children');
INSERT INTO BOOK VALUES (24, 1313131313, 'The Lazy Dog', 'Jake Red', 2016, 'children');
INSERT INTO BOOK VALUES (25, 1414141414, 'The Very Blue Boy', 'Ben Jen', 2006, 'fiction');

INSERT INTO Member VALUES (001, 'Lee', 'Nancy', 'Brownlea Drive', 1254896325, 2);
INSERT INTO Member VALUES (002, 'Le', 'Ray', '10th Street', 1234561256, 2);
INSERT INTO Member VALUES (003, 'Kan', 'Charlie', '5th Street', 1234567236, 2);
INSERT INTO Member VALUES (004, 'Brown', 'Joe', 'Elm Street', 1234567845, 2);
INSERT INTO Member VALUES (005, 'Smith', 'John', '33 East', 1234567890, 2);
INSERT INTO Member VALUES (006, 'Khang', 'Nkaujyi', '358 Spencer', 2145345625, 2);
INSERT INTO Member VALUES (007, 'Jones', 'David', '4th Street', 14812365789, 2);
INSERT INTO Member VALUES (008, 'Jones', 'David', '5th Street', 1234561256, 2);

INSERT INTO CurrentLoan VALUES (001, 10, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (002, 19, '13-JAN-17', '15-NOV-17');
INSERT INTO CurrentLoan VALUES (003, 16, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (005, 15, '12-OCT-17', '09-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 18, '13-APR-17', '12-MAY-17');
INSERT INTO CurrentLoan VALUES (007, 25, '02-OCT-17', '25-DEC-17');

INSERT INTO History VALUES (001, 10, '14-Jan-17', '04-OCT-17');
INSERT INTO History VALUES (003, 19, '12-Jan-17', '04-NOV-17');
INSERT INTO History VALUES (002, 13, '14-APR-17', '08-OCT-17');
INSERT INTO History VALUES (005, 20, '14-MAY-17', '04-DEC-17');
INSERT INTO History VALUES (007, 25, '02-OCT-16', '25-DEC-16');
COMMIT;

Two records are named David Jones

select * from member where firstName = 'David' and lastName = 'Jones' ;

memberID    lastname    firstname   address phone_number    limit_
7   Jones   David   4th Street  2147483647  2
8   Jones   David   5th Street  1234561256  2

SQL to delete the member records and any related records using the in clause.

/* 
    delete records from the tables with relations first using the memberIDs
    related to any David Jones
    */
delete from currentloan where memberID in (
    select memberId from member where firstName = 'David' and lastName = 'Jones' 
)  ;
commit ;

delete from history where memberID in (
    select memberId from member where firstName = 'David' and lastName = 'Jones' 
)  ;
commit ;

/*
    delete any members called David Jones using the firstName and lastName
    */
delete from member where firstName = 'David' and lastName = 'Jones' ; 
commit ;

/* 
    no records for David Jones 
    */

select count(*) count from member where firstName = 'David' and lastName = 'Jones' ;

/*
    results
    */
count
0

Answer 2

/* 
    delete records from the tables with relations first using the memberID 
    */
delete from currentloan where memberID = 7 ;
delete from history where memberID = 7 ;
/*
    delete the member using the memberID
    */
delete from member where memberID = 7 ;

Answer 3

I am not sure about DBMS but following any relational database logic you cannot delete FK (foriegn keys) until you delete records that depend on the FK. You will need to delete any records that reference a foreign key before deleting said key. If I'm reading your data correctly, you have two tables (Currentloan and History) that depend on foreign Keys in Book and Member. You will need to delete the records in Currentloan and History before you delete the foreign keys in Book and member.

New query

DELETE FROM CurrentLoan
WHERE memberID = 007;

DELETE FROM History
WHERE memberID = 007;

DELETE FROM Book
WHERE bookID = 25;

DELETE FROM MEMBER 
WHERE lastname = 'Jones' AND firstname = 'David';