Slashdot Mirror
Practical Issues In Database Management
Most of the time, when a computer book has the word "practical" in the title, it means one thing: examples. Lots and lots of real-world, cut-and-paste examples intended to solve the exact problem you're facing. This book departs from that stereotype by containing little in the way of practical examples. I don't think it even mentions any specific database products. Instead, it mainly discusses the platonic ideal of a database from a scholarly standpoint, and never touches actual examples of database products. As such, it is a relatively timeless book, but it is not what I would describe as "practical".
Essentially, it is a scholarly overview of the whole concept of databases, some common pitfalls that database administrators (DBA's) run into, and where actual database systems fall short of the platonic ideal. It would be a good book for an "Intro to Databases" class (and I don't mean a How to Use Excel course, I mean a CompSci course).
Let's skim through the chapters. I'll try to make this review accessible to all readers, even those who don't know much about databases.
Chapter 1 discusses datatypes (how data is stored in the database), and suggests that DBA's should not fall into the trap of using complex, proprietary datatypes over standard character and numeric fields. Chapter 1 also includes the oddest section of the book: 20 pages of Webpage print-outs whose sole unifying theme seems to be "Look what weird stuff people want to put in databases - and here's a ZDNet printout to prove it!". This section almost turned me off the book entirely, but thankfully it wasn't repeated. I don't know what they were thinking...
Chapter 2 discusses integrity rules. Integrity constraints are rules that your data should obey - enforcing the rules is the problem. For instance, no two employees should have the same employee number. Essentially, the author's advice boils down to implementing integrity in the database itself rather than via triggers or external logic.
Chapter 3 discusses keys. A key is a field in a record with data that you plan to use to pull that record from the table - for instance, if you were getting information about employees, you might use that employee number as a key, because one employee number should correspond to one record and one employee. The author discusses the various types of keys and makes obvious recommendations.
Chapter 4 talks about duplicate rows. It's actually an insightful discussion about a serious flaw in many databases designed by amateurs, and the author provides a few possible paths for how to do something that is surprisingly difficult in large tables: getting rid of duplicate rows. A valuable chapter.
Chapter 5 discusses normalization. Good overview, good recommendations.
Chapter 6 discusses entity subtypes and supertypes - essentially, what do you do when you have items to store in a database that have some traits in common but some not in common. The nomenclature was a little confusing. He discusses some oddities in the most recent SQL standard, which mostly went over my head.
Chapter 7 discusses data heirarchies and trees. In a nutshell: there are no trees in SQL. The author is distressed by this.
Chapter 8 covers redundancy, more or less an extension of chapter 4. Good coverage, mostly seems to be common-sense, but then I've seen plenty of databases that lacked this common sense, so perhaps it isn't as common as one would hope.
Chapter 9 is about quota queries, a common task in any database project, and one that usually seems to have exactly one example in any set of documentation. (Not enough!) Some good tips are hidden in here, and it should be helpful to many DBA's.
Chapter 10 covers missing information, the difference in database-land between a field with (say) Yes, No, an empty string, or a null value, which has given everyone who does any sort of database programming problems at one time or another. The author's analysis is sound and useful.
To sum up, it's a decent book covering a wide range of areas pertaining to databases from a scholarly viewpoint. Perhaps it could be compared to Sun Tzu's Art of War - it doesn't really discuss YOUR situation, but it gives a lot of tips, and if you pay attention, you'll probably find something in there that will help you in your present crisis. The author is more of a scholar than a hands-on instructor, but he obviously knows what he's talking about. The book title should probably be "The Zen of Databases" or something like that, though, rather than implying it will be some sort of practical guide to administering SQL Server 7 or anything along those lines. Probably the people who will get the most benefit from it will be DBA's who have learned database administration from the school of hard knocks - learn by doing - but find themselves doing it more often than they would like, and want to get a little book-learning in to help them past the problems they are encountering. Novices won't get a lot out of it because they won't have hit the problems he describes. Experts will already know the solutions he recommends, although they'll probably get something out of it nonetheless.The author has a website, Database Debunking, which has a similar tone to the book. There is also online errata for the book.
Purchase this book at Fatbrain.
Yes, I know it's a textbook, and has maybe entirely useless examples, and is, by definition, too expensive to be entirely practical, but I learned a lot from this book. It's written by not entirely incompetent professors, and has been taught for several years successfully at a few major universities. The chapter layout is nearly exactly the same as the book reviewed above, and it has examples in both SQL and relational algebra for many problems, so you actually get a feel for how you can use one to get better with the other. Just do a google search for 'A First Course in Database Systems' and you'll come up with quite a few class pages that use it, and use it well.
--
--
fat lenny's gonna lick your brain today.
Practical books are good for something like operating system administration, where design mistakes are much more easily corrected. A database design tends to hang around for years, and should be very thoughtfully executed.
However, that doesn't mean I know it all. :) The company I work for swears by the application. I've been designing, developing, and maintaining a human resources package for them for the last 1 1/2 years.
Eventually we'll be running the system over a WAN form 8-12 different locations. I know that FileMaker will not cut it for the needs that we have. Maybe as a thin-client. I've been lobbying for a couple of months now for us to use a SQL/XML web based solution that does the same thing. It will lower our costs (one server and no software expenses - we could just use a browser as the interface).
The main location (and possibly one other) are diehard Mac users. So far I've been able to switch our heavy dependednt servers over to linux whether it's on a PPC or Intel machine.
FileMaker is a great program because there is hardly any learning curve at all. However, I don't think it has the power for long range, data intensive applications over a WAN.
Anybody have any ideas as to what might be better?
"Power corrupts. PowerPoint corrupts absolutely."
Ethics. Anyone who runs a database has an important role in making sure that the data is used only for ethical means. This means that it should not be used for spam, spying, or other illicit purposes.
My databases course actually had a lecture on the legal issues of data protection. It was pointed out in this how and why it is wrong to store data on people without their knowledge or permission. This book doesn't seem to discuss this
I'm not saying that the author is amoral in ommitting this, but I feel that people have a duty to consider how their knowledge is being used, and people need to be reminded about their responsibilities. Not mentioning this when you have an opportunity is simply wrong.
That's the way I feel. Every time someone buys Filemaker Pro or something similar, like Adobe Photoshop/Premiere/Illustrator/Go Live/etc., Macromedia Director, Visual Cafe, or JBuilder, they automatically assume that they are well educated in the topic just by making the purchase. This arrogance must stop before it spreads like a plague (wait a minute, that bum across the street is holding a copy of JBuilder, too late).
"Ancillary does not mean you get to rule the world." --U.S. Circuit Judge Harry Edwards, speaking to the FCC's lawyer
I'll have you know that it's a copy of Access. Thank you very much.
Greg
This is a bad idea. I'm aware that point & click, drag & drop GUI interfaces are very tempting to manage such complex systems as a relational database. Let me elaborate:
It gives joe end-user the impression that, hey, it's only a little Access after all. Planning an enterprise database that has to support 100s of users with guaranteed response times and throughput is never an easy task and requires quite some insight into the DB engine. Another issue is that desaster is at your finger tips: Are you sure ...? From using desktop applications we all know how easy it is to just click yes. The yes on such a window might be the sure path to major desaster.
An even worse issue is recoverability. It is a pain in the ass to create the scripts for every single database object, to version control them and to treat them like source code. Let me assure you that when the crap touches the ventilator you thank [whomever you believe in] that you went real slow, conservative and have scripts prepared to recover the db from scratch and re-apply the table data you where barely able to bulk copy from your corrupt database. When you dragged and clicked a bit, I can guarantee you, that there's no way to recover the proper database structure (Hey, but Joe, ran the reverse engineering option in March 98...)
The very, very best Sybase DBA I've ever gotten to know did everything with plain straight forward ascii scripts. It's a system that processes $10'000'000'000.00 of real cash every day. It's up and running since 1996 and if it's down for three days the first companies will file for bankruptcy. It handles up to 9'000'000 bookings a day (each consisting of 8 tpc/c transactions).
This guy never had a bad night since the system is in production.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
Am I the only one who laughed out loud over this one? :-)
I think you mean CONNECT BY.
...);
:interestingDate BETWEEN FromDate and ToDate
:foo
Unfortunately, Oracle's CONNECT BY isn't very useful - it only works if you have only one table in a query - it doesn't allow you to join the table containing the parent/child pointers to other tables. This is especially problematic if you tree structure is implemented this way:
CREATE TABLE Node
( NodeId NUMBER
, NodeName VARCHAR2(100)
, NodeValue VARCHAR2(100)
, CreateUser VARCHAR2(100)
,
CREATE TABLE NodeRelationship
( ParentNodeId NUMBER
, ChildNodeId NUMBER
, FromDate DATE
, ToDate DATE
);
Oracle's CONNECT BY will not let you join the NodeRelationship table to the Node table. You can maybe make it work by creating a virtual table in your from clause that looks like this:
SELECT NodeName
, NodeValue
, Level
FROM ( SELECT ChildNodeId
, RowNum
, Level
FROM NodeRelationship
WHERE
START WITH NodeId =
CONNECT BY PRIOR ChildNodeId = ParentNodeId
) tree
, Node
WHERE Node.NodeId = tree.ChildNodeId
ORDER BY tree.RowNum
But it isn't documented anywhere what the behavior of RowNum is when used inside subqueries, so this technique makes me nervous.
In any case, it only works for Oracle. All other systems have to use client-side code or make use of temp tables or cursors to acheive the same result, both of which are not a part of standard SQL, which always gives academician's (like this book's author) fits, but is rarely a problem in the real world (unless you have to migrate to a new RDBMS).
-BbT