Month: November 2014

SQL Transactions – Example

~ macengr ~ 1 Comment

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;

Playing catch-up, and Global Health, Part 0

~ macengr ~ 1 Comment

I missed two days, but I am not going to stop my NaBloPoMo because of that.  What have I been doing?

Well, Sunday was church.  I have had a certain issue on my mind that I’ve been praying about, and while I don’t have a clear answer yet, things have been moving, so we’ll see.

I finished my International Affairs textbook, and wrote another essay for the class.  Two left before the end of the year – and one I’m not sure what I’m going to write about.  In SQL, I read about transactions, and I started coding one.  I think I know how to finish it, and when I do, I’ll post it here.  One more optional program after this!  I should mention, that when I’m done with these two classes, I will graduate with a Certificate in Software Development.

We finally got Minecraft installed for my son – the web page Minecraft.net does not allow you to register.  We ended up being able to register at Mojang’s homepage.

So, exploring the field of Epidemiology, as I mentioned previously, is one thing I’ve been doing.  I am taking an online class on it.  I am also working through another go-at-your-own-pace class from Harvard / EDX on Global Health.  That one has been very interesting as it has tied into a lot of things I’ve studied in International Security and International Affairs.  It even has brought back some of the reading I did on feminism; namely, philosophers like Foucault.  I would like to write more about this, so hopefully tomorrow!

Thursday the…Thirteenth!

~ macengr ~ Leave a comment

13 for 13 on my NaBloPoMo!  Sweet!

I’m struggling with what to write about today.  I wrote an essay today on identifying the aggressor in international conflict and why it does or does not matter.  I started with Thucydides, drew on Aquinas (Just War theory) and then referenced several UN treaties and articles.  I wrote about whether or not the advent of so-called fourth generation warfare would have any effect on these criteria, since for the most part they refer to nations as the primary actors.  My paper hasn’t been graded yet; I’ll put it up once it is.  I was, however, disappointed to find that Wikipedia is still not regarded as a valid source, despite the fact that it has been shown to have no more errors than the Encyclopedia Britannica.

In other news, we gave my son Minecraft for his birthday.  However, for the last few nights we haven’t been able to download it because we keep getting a message that they are updating their servers.  As you can imagine, this is quite frustrating.

Other than that, nothing new and exciting!

Data Validation with a SQL Trigger

~ macengr ~ 1 Comment

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;

Currently Reading…

~ macengr ~ Leave a comment

Here are some books I am currently reading.

First, the two textbooks for the classes I am taking at Community College. I’ll wrap up my Software Development Certificate next month!

  

Next, I am reading a book by an English Professor at West Point.  It’s a fascinating read on what it’s like to teach literature to soldiers during the Long War.  She sees her mission as helping them learn to think.  She provides great anecdotes and analysis about this mission.

Finally, the Samantha Power book I’ve mentioned before:

It’s Epidemiological!

~ macengr ~ Leave a comment

Friday I went down to the University of Pittsburgh’s School of Public Health for an Open House.  This division of Pitt has several departments:  Behavioral and Community Health Sciences, Biostatistics, Environmental and Occupational Health, Epidemiology, Health Policy and Management, Human Genetics, and Infectious Diseases and Microbiology.  I went down to take a look at the Epidemiology Department. I’ve noted before that I have an interest in data science, and that I want to start exercising my Christian faith by helping others.  I also maintain an interest in international affairs and Human Security; by combining this with a Certificate in Global Health it looks like I can tie all of these together.

The day started out with a session on the latter certificate, where Pitt offers two tracks – one local and the other part of the Peace Corps.  Then we went to the main session for all of the schools, where we learned about the various departments and student organizations, as well as career services, financial aid, and the application process.  The school is expensive but it seems like there are many possibilities for financing, although as a middle-aged male, I’m not sure how many I am eligible for.  After the main session there was a “marketplace” where you could talk with representatives from the various departments.  I was interested to see that a number of professors perform dual roles – not just teaching, but as part of the administration.  There is apparently a ratio of one professor for every 4.5 students.  The students are involved in real and active research on actual contemporary – and often cutting edge – problems.

As noted, I am male and as I’ve pointed out before, my background is in engineering.  The ratio here (among the prospective applicants) appeared to be 80% women.  That is quite a change for me.  I was pleased to note that even the Biostatistics department representatives were mostly female; I was not happy to find out that they knew nothing of Daniela Witten at the University of Washington, whose book I have been reading through.

All in all, it was an informative day and gave me much to think about in pondering my future.  I have made no decisions; however, if I do choose to do Epidemiology I think that Pitt will be an excellent choice!