SQL Transactions – Example

So, as I wrote yesterday, I needed to create a Transaction for my SQL database.  If you’ll remember, my database was a hypothetical library in Africa.  A new book needs cataloged, and since the book table is interlinked with several other tables, you don’t want one of the inserts to succeed and another to fail, because then your tables won’t match, and that’s all bad.  So you group all the inserts into one batch called a transaction and thus ensure data integrity.  So here we insert a book into the book table, an author into the author table, and a category into the category table all at once.  Creative Commons license blah blah blah.

DECLARE @BookID int

DECLARE @AuthorID int

DECLARE @Category_ID int

BEGIN TRY

BEGIN TRAN;

INSERT Authors VALUES (‘Coppen’, ‘Ben’);

SET @AuthorID = @@IDENTITY;

INSERT Categories VALUES (‘Computers’);

SET @Category_ID = 3;

INSERT Books

Values(0763732303, ‘Artificial Intelligence Illuminated’, @AuthorID, 2004, 1,@Category_ID, NULL);

COMMIT TRAN;

END TRY

BEGIN CATCH

ROLLBACK TRAN;

END CATCH;

Data Validation with a SQL Trigger

For our semester long project for my SQL class, we had to create an actual database and we have been entering data and creating everything from views to scripts to triggers.

My database is a hypothetical small library in Ghana, which has been funded by a donor organization with a large number of books and several computers.  My own modest collection of 800+ books serves as the actual library (I haven’t entered them all in…yet).  The trigger I wrote is for when there is a fine to be paid.  The trigger will validate the data entered in.  Here’s the code.  Creative Commons License, and all that.

CREATE TRIGGER FineTableUpdate
ON Fines
INSTEAD OF INSERT, UPDATE
AS
DECLARE @PatronNo int, @BookNo int, @BookTitle varchar(50), @BorrowedDate Date,
@FineAmount money, @PaidStatus char(2), @TestRowCount int;
SELECT @TestRowCount = COUNT(*) FROM inserted;
IF @TestRowCount = 1
BEGIN
SELECT @PatronNo = Patron_ID, @BookNo = Book_ID, @PaidStatus = PaidStatus
FROM inserted;
BEGIN
IF NOT EXISTS (SELECT * FROM Patrons WHERE Patron_ID = @PatronNo)
THROW 50001, ‘That Patron does not exist.’, 1;
IF @PatronNo IS NULL
THROW 50001, ‘Invalid Patron Number.’, 1;
IF NOT EXISTS (SELECT * FROM Books WHERE Book_ID = @BookNo)
THROW 50001, ‘That Book does not exist.’, 1;
IF @BookNo IS NULL
THROW 50001, ‘Invalid Book Number.’, 1;
SET @BookTitle = (SELECT BookTitle FROM Books WHERE @BookNo = Books.Book_ID)
SET @BorrowedDate = (SELECT Date_Borrowed FROM Lending
WHERE @PatronNo = Patron_ID AND
@BookNo = Book_ID);
SET @FineAmount = DATEDIFF(day, @BorrowedDate, GETDATE())
IF @PaidStatus <> ‘Y’ OR @PaidStatus <> ‘N’
THROW 50001, ‘Paid Status must be Y or N’, 1;

INSERT Fines
(Patron_ID, Book_ID, Book_Title, FineAmount, PaidStatus)
VALUES (@PatronNo, @BookNo, @BookTitle, @FineAmount, @PaidStatus);
END;
END;
ELSE
THROW 50027, ‘Limit INSERT to a single row.’, 1;

Monday recap – Python, Trapezoidal Method, SQL, Samantha Powers…

Today started out frustrating, although it wasn’t a bad day. It took me three hours to figure out the trapezoidal method of integration, because none of the descriptions I could find were very clear. I coded it up in Python – The annoying thing being, that I’m learning Python concurrently. This is all for a class in Nonlinear Dynamics, one of the big tools for complexity science. The next thing I need to do is learn to plot the results; for now, I am copy-pasting into Excel and letting it create a chart. Sadly, such tutorials as there are on the web do not allow me to get up and running quickly for plotting the graphs I need. I’ll just add it to the list…

I also read through a chapter of SQL (database language) on scripts. For our semester project, I have decided to create a fictitious library that will use my not-fictitious collection of books. For this week’s assignment, we need to create a script that is logical for our business; I’m pondering what that might be for my library.

Finally, reading. I’m working my way through Samantha Power‘s book on Genocide. It’s painful reading, mainly because of the inaction of the United States on the subject over the years. I don’t know what the answer is; I’m not a huge fan of R2P (Responsibility to Protect). But inaction doesn’t seem to be a good answer either.

That’s all for today. Below is the Python code I wrote for the Trapezoidal method for a Simple Harmonic Oscillator.

print(“Welcome:”)
g = input(“Enter X Sub 0: “)
Xsub0 = float(g)
h = input(“Enter V sub 0: “)
Vsub0 = float(h)
B = input(“Enter Friction (B): “)
friction = float(B)
g = input(“Enter Gravity (g): “)
gravity = float(g)
m = input(“Enter Mass (m): “)
mass = float(m)
k = input(“Enter Spring Constant (K): “)
KConstant = int(k)
t = input(“Enter time step (del-t): “)
deltaT = float(t)
e = input(“Enter end time: “)
endTime = float(e)

i = 0.0
Xnow = Xsub0
Vnow = Vsub0
while i <= endTime:
      vprime = gravity – ((friction/mass) * Vnow) – ((KConstant/mass) * Xnow)
      vODE = vprime
      xODE = Vnow
      vectorX = deltaT * xODE
      vectorV = deltaT * vODE
      XNew = Xnow + vectorX
      VNew = Vnow + vectorV
      Xone = XNew
      Vone = VNew

      vprime = gravity – ((friction/mass) * VNew) – ((KConstant/mass) * XNew)
      vODE2 = vprime
      xODE2 = VNew
      vectorX = (deltaT * .5) * (xODE + xODE2)
      vectorV = (deltaT * .5) * (vODE + vODE2)
      XNew = Xnow + vectorX
      VNew = Vnow + vectorV

      Xnow = XNew
      Vnow = VNew

      ###print(“X at ” + str(i) + ” is ” + str(XNew) + “\t\t\t” + “V at ” + str(i) + ” is ” + str(VNew))
      print(str(XNew) + “\t\t\t” + str(VNew))
      i += deltaT