My SQL Expert Book and Practice Exams

I recently read in an online forum the online bonus exam for my book, OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047), is “no longer available” at the Oracle Press website.  I’m inquiring into this to determine if its true, and if so, to see if we can get it restored.  If you’ve had a similar experience, please email me to let me know.

In the meantime:

  • I have created additional practice exams here:  They are available at no charge, and only require that you registered an email address so you can take the exams in multiple login sessions if you wish.
  • I am currently working on a revision to my book, to upgrade it for Oracle 12c.  Lots of new feature we’re adding, and I’ll blog about those soon.  The second edition will be completed in early 2016.

In the meantime, if you’re looking for a practice exam, please check out!

Java, SQL, and the ORM Mismatch

I just can’t take it any more.

There is an incorrect perception floating around out there in Java world about the issue of mapping Java classes to SQL tables.  This issue falls within a larger topic often referred to as the object-relational mapping (ORM) mismatch.  This “mismatch” is based on the fact that Java is object-oriented, and SQL is not, so using Java classes to represent SQL tables involves some issues, and requires some clever work.  That is true.  But it’s led to a major misperception about SQL being supposedly “defective”.  It is not, but this notion has crept its way into two books I’ve read lately, books by otherwise very smart people, published by major cutting edge IT book publishers.

(For the record, no, neither publisher in question is my own publisher, the incomparable McGraw-Hill Education and their awesome imprint with Oracle Corporation, Oracle Press.)

I’m not going to verbally quote either work, but the idea goes like this:

  • To support persistence (storing data), you map a Java class (ex., “Customer”) to a corresponding SQL table (ex., “CUSTOMERS”)
  • When creating two Java classes, it’s possible to design them with a many-to-many relationship to each other
  • SQL doesn’t support the creation of tables with many-to-many relationships
  • Therefore SQL is defective.

Uh – no.

Folks, the presence of a many-to-many relationship in your data model is a sign that you aren’t finished with your data model.

This isn’t a Java thing, or a SQL thing, it’s a real world thing.

It’s possible to build a defective data model in Java.  You can do a lot of stupid stuff in Java.  You can also build bad data models in SQL, and technically you actually can create a many-to-many in SQL, but it won’t work.  That’s a good thing.  I’ll explain shortly with an example.

But first, a word about the classic mismatch.


The authors of these two books I’ve read are Java developers first and foremost.  I get that, and I know where they’re coming from.  I’m a Java developer myself, and have been since Java was first introduced into the Oracle database as an alternative to PL/SQL, which I also love – so much that my first book was the official Oracle Press certification exam guide OCP Developer PL/SQL Program Units Exam Guide.

But about the time that book hit the shelves, I began working with Java, which had become available as an in-database alternative to PL/SQL.  I soon thereafter began teaching Java.  In fact, this is me with then-CEO of Sun Microsystems, on the first day I launched my first ever course in Java:

Scott McNealy, Steve O'Hearn, at the National Press Club

That was taken at the National Press Club.  McNealy was hysterically funny that day and a great guy. He’s responsible for, and oversaw, a lot of great developments in Silicon valley that reverberate to this day – like Java.

And back at that time, the first question about Java was – how do we get Java and SQL to interact?  That, after all, was Oracle’s whole point of embedding Java in the database.

Java has come a long, long way since then, and all for the better.  There is a well established and growing library of packages and tools for implementing various interactions between Java and SQL.  And at the heart of them all is often the same issue:

Java is object oriented, and SQL isn’t.

True.  So what?  Newcomers to Java act like this is some kind of ultimate nightmare scenario, a sign that SQL is out of sync with the world.  Nonsense.  SQL isn’t inherently object-oriented, but neither is your file system.  And yet Java interacts with files just fine.  Now granted, the idea behind SQL is more sophisticated than file systems.  But on the other hand, SQL isn’t in the same category as a third-generation language (3GL) like Java, SQL is a fourth generation language (4GL). It might not support object-oriented dynamics, but neither does it clash with them.  This is demonstrably obvious – otherwise we wouldn’t have JDBC, the Java Persistence API (JPA), and Hibernate.  So the two can and do work together, and effectively.  But it takes a bit of work; SQL doesn’t necessarily provide built-in support for all object-oriented concepts, such as inheritance. (Caveat: Inheritance actually can be supported in data modeling – see this article – and there’s a way to do it within SQL itself, sort of, but as to whether it’s helpful or not, that’s a different story, and perhaps the topic of a future blog post.  Or book.  Hm …. )

Frankly, when I hear about the “mismatch” between Java and SQL, I think of the issue of transactions, that’s the only real issue to me. The object life cycle and nature of Java is such that it introduces challenges in the way persistence to the database is best done in a multi-user/multi-threaded environment.

But let’s get back to topic and dispel this “SQL is defective” myth.


Data modeling is the act of representing real world business processes through diagrams of the things (“entities”) that comprise a real world process, and the relationships (or “associations”) among those entities.  The most obvious entities are easy to identify – customers, products, office buildings, vendors, cars, etc.  But the more abstract entities are not always so obvious – work schedules, change orders, reservations, that sort of thing.  This is where the importance of understanding many-to-many relationships can be very helpful.


I’m going to use an example taken from the airline industry, as I’ve been meeting lately with Lisa Ray, a lifelong aviation data expert closely involved with a series of legacy migrations in that area. (And for the record, she fully gets this.)

Let’s start with two obvious entities:

  • Planes – as in “individual planes”, not just types of planes.
  • Pilots – individual people.

Every one plane might be flown by more than one pilot.  Every one pilot will be qualified to fly more than one plane.  Sounds like a many-to-many relationship, right?  And as you might know, an entity-relationship diagram illustrates the “many” side of a relationship with the classic crow’s-feet line:

ERD Diagram A

So that’s our initial logical diagram for these two entities.

Now folks, this blog post is not going to be a complete tutorial on how to perform data modeling, but we will explore one key aspect of modeling: whenever you encounter a many-to-many relationship, you ALWAYS transform it using the following steps:

  • Add a third entity in between – “Entity_3” in our diagram below.
  • Create foreign keys within the new entity for the primary keys of the existing entities – in this case, PLANE_ID as a foreign key to the PLANES entity’s PLANE_ID primary key, and PILOT_ID as a foreign key to the PILOTS entity’s PILOT_ID primary key.
  • Establish “one-to-many” relationships between the new entity and the existing entities, using the “single-line” to indicate the “1” side, and the “crow’s foot” to indicate “many” side of the relationship.

Like this:

ERD Diagram B

The reason we do that is simple:  the presence of a many-to-many relationship is a clear indicator that something else goes in between the two entities.  The question is not “if” something belongs there.  Something does belong there.  The only question is – what exactly is it?

In our example, it’s going to be something like a “roster”, or a “schedule”, or “flight assignments”, or “rentals”:

ERD Diagram C
Call it what you will, but something goes in between and it must be included in your data model.  That new entity will have its own attributes.  Perhaps:

  • A start and end date to the time of assignment
  • A name of a key staff member authorized to approve the assignment

Who knows what might be there? But something is there, and the presence of the many-to-many relationship is your clue to its existence.

That third entity is often a bit more abstract.  You can see pilots and planes, but a “flight assignment” isn’t necessarily as obvious.  But it’s an important entity in the data model nonetheless.

One more point:  this third entity isn’t some necessary crutch to get around a “SQL defect”. I can’t believe what I’ve read in certain Java books that suggest such a thing.  No.  This third entity is a real world “thing” that definitely exists in order to enable the relationship with the real world, and it’s up to you to identify it. Whether you figure it out or not, it exists nonetheless.

The rule is simple: no complete data model exists with any “many-to-many” relationships.  If such are present, you aren’t done modeling yet.

So now that you understand this, imagine reading a Java manual in which someone explains that SQL is “defective” because it “doesn’t support many-to-many relationships”.


Now you know!

O’Hearn is the Java leader of the only officially recognized JUG for the DC metro area.  He is also the author of the first-ever expert-level certification exam guide from Oracle Press, titled OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047).  The 2nd edition of his SQL Expert book, revised for Oracle 12c, is due out in 2016.

I remember the World Trade Center

My mother once nearly fell off the top floor of the World Trade Center.

Back around 1973 or so, my parents were invited to visit the brand new yet-to-be-opened World Trade Center in NYC.  My father is a retired civil engineer and was part of a small group invited to tour the modern marvel.  I stayed with close friends during their trip.

My parents, with their tour group, elevatored to the top interior floor of the WTC, which was – what – 90-something?  As the guide reminded the team to be careful because the top floor was still under construction and the exterior walls were not yet installed, my photographer mother made a beeline for the edge and – I swear I’m not making this up – walked right up to the edge of the top floor, leaning out and snapping photos, before an engineer with a hard hat and a harness grabbed her and pulled her back inside, suggesting she step back before a random wind gust might blow her out of the building.  She did.  Thankfully both of my parents are still with me.  And my mother’s photos are amazing.

So at this moment in 2001, when I was on a work related phone call, my interest was captured by a television in the background showing the first World Trade Center already cascading smoke.  That’s when I saw the second plane hit the second tower on live television.

The events of that day are another story.  I may write about them later.  But that evening about 7 p.m. or so, I left my office at Tyson’s Corner, Virginia, to return to my apartment two blocks away. I found myself on Route 7, a road that is typically is one of the busiest in the nation, located near the DC beltway. On a typical weekday afternoon that area is jammed with multiple cars in multiple lanes heading in multiple directions. But not that evening.  I was literally the only car on the road.  It was so striking, I literally stopped my car, turned off the engine, and got out.  I walked out onto Route 7, and stood in front of my car, on Route 7. There was not a car in sight.  I was deliberately still, and held my breath to listen.  There was not a sound anywhere.  Not a sound of a car from the nearby DC beltway or from anywhere around.  Not a sound of a plane in the sky – and keep in mind, that location is near the flight paths of both Reagan and Dulles airports. But there was not a sound anywhere.  Not a child in the distance.  Absolutely nothing.  It was the eeriest experience.

That silence continued.

Three days later I kept a dinner appointment at the Chart House in Old Town Alexandria.  It was the first night I think anyone was emerging from their homes since 9/11, yet still there weren’t many of us out and about yet.  Flights were still grounded and remained that way for days.  Television was still in a weird all-news commercial-free mode that continued for about two weeks.  And at one point that evening, at the Chart House, the only sound I could hear outside was a single jet fighter that was slowly circling DC in a deliberate pattern, ordered temporarily to guard DC, as it slowly circled the DC region, over and over, 24 hours per day.  It was the only sound I could hear outside on a Friday night in September, a time when the DC area is generally bustling with activity, but not then.  Eerily quiet and somber.


B.B. King

I just heard this morning that legendary blues guitarist B.B. King is now being cared for by hospice in his home.

Here’s a great video of King performing with a number of other outstanding guitarists and celebrity musicians: Slash, Mick Hucknall (of Simply Red), Ron Wood (of the Rolling Stones, Jeff Beck Group, and others), Derrick Trucks, and Susan Tedeschi – all jamming at the Royal Albert Hall, 2011. B.B. King was 85 at the time. His solo starts at about 5:40.


Best Super Bowl Show Ever: XXXVI and U2

Today is Super Bowl Sunday, and I’m sure the halftime performer – Katy Perry – will do a great job.

But I think it will be a long time before anyone outdoes the 2002 Super Bowl XXXVI halftime show featuring U2, which was – in the humble opinion of this author- the best Super Bowl show ever.

I think it’s fair to say it was the first major public nationally televised event after the September 11th attacks.

The first song is Beautiful Day, a celebration about simply being alive.

During the transition to the second song, you can hear U2’s lead singer Bono muttering something.  It’s a prayer from Psalm 51:15: “O Lord, open my lips, and my mouth shall show forth Your praise.”  What follows is a cry that – under other circumstances – would just be a typical rock star thing for Bono to do.  But in this context I think a great many viewers took it as a collective national (and perhaps international) cry of anguish, anger, hope, and catharsis. I know that was my sense at the time and I read comments and interviews with many, many others who took it the same way.

What follows is Where The Streets Have No Name, a song about Bono’s wish for a class-less society, where people are not judged as U2, who wrote the lyrics, perceived they were judged in their hometowns.  Locals knew the poor streets and the rich streets and formed opinions about townspeople based on where they lived.  The song expresses hope for a time when no such judgement would occur.

I read one article at the time in which Bono, lead singer for the Irish band, was said to have had a strong desire and sense of history that it was their role to encourage America at that time.

See below for the full video.

Doc Scantlin’s New Website

Well it’s about time …

The world received a unique and whimsically fun Christmas present this year: the newly revamped and remarkably hip new Doc Scantlin and His Imperial Palms Orchestra website!  This is more than just a long-overdue celebration of what Forbes magazine calls the “world’s best band”, it’s filled with little surprises – such as the old timey radio featuring a bunch of Doc’s classic hits that – believe or not – have never been available before in any form of any kind, YouTube-ish or otherwise.

If you who is reading this are a living breathing entity who has any interest in living your life in an entertaining manner, then stop wasting any more time and check this out immediately, it’s a historic milestone of 21st century proportions.

Click here to read about the brilliant folks at Transmedia who are behind this technically creative masterpiece.

Click here for the press release!

Click here for the website itself!