My Life and Times

An Engineer Poet explores the world

SQL Transactions – Example

Posted by macengr on November 20, 2014

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;

Posted in NaBloPoMo, SQL | Tagged: , | 1 Comment »

Playing catch-up, and Global Health, Part 0

Posted by macengr on November 19, 2014

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!

Posted in Global Health, NaBloPoMo | Tagged: , | 1 Comment »

Big Hero 6

Posted by macengr on November 16, 2014

We saw this tonight, thus pushing back my post on Global Health. This film was great.  Fun for the whole family!

Posted in NaBloPoMo | Tagged: | Leave a Comment »

Friday is for Family

Posted by macengr on November 15, 2014

Today my son had a recital at school and they got out early.  Family time!  So tomorrow, I’ll fill you in on some Global Health stuff…

Posted in NaBloPoMo | Tagged: | Leave a Comment »

Thursday the…Thirteenth!

Posted by macengr on November 14, 2014

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!

Posted in 4GW, international security, Minecraft, NaBloPoMo | Tagged: , , | Leave a Comment »

Data Validation with a SQL Trigger

Posted by macengr on November 13, 2014

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;

Posted in NaBloPoMo, SQL | Tagged: , | 1 Comment »

All gave some…Some gave all

Posted by macengr on November 11, 2014

God bless all our veterans who served, especially my father, my uncle, and my cousin.

Posted in NaBloPoMo | Tagged: | Leave a Comment »

Currently Reading…

Posted by macengr on November 11, 2014

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:

Posted in NaBloPoMo | Tagged: | Leave a Comment »

It’s Epidemiological!

Posted by macengr on November 10, 2014

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 SciencesBiostatisticsEnvironmental and Occupational HealthEpidemiologyHealth Policy and ManagementHuman 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!

Posted in epidemiology, NaBloPoMo | Tagged: , | Leave a Comment »

Saturday night…

Posted by macengr on November 9, 2014

Today was not my best day ever.  I managed to get some work done in my non-linear dynamics class.  Beyond that, at least today is done.  Tomorrow, I’ll give a rundown on the Public Health (including Epidemiology) Open House I attended at the University of Pittsburgh on Friday.

Meanwhile, cool pic of a V22 Osprey:

1-7I2bwwaJhueNKl3jM05Uzg

Posted in NaBloPoMo | Tagged: | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 980 other followers