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.
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.
Err triggers are in the database. The declarative RI you get in most DB products is just a convinient trigger creation mechanism under the covers... Of course using declarative RI ensures you get a consistent well trigger...
Special Relativity: The person in the other queue thinks yours is moving faster.
--
--
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."
FileMaker Pro Owners
Linux Newbies with MySQL who think that alone makes them a DBA
Microsoft SQL Server Owners. Mandatory Microsoft Dig
Come on.. this book is hardly ground breaking, describes very little which is not basic knowledge. What use could a review of it on a tech website possibly have?
---
Video meliora proboque deteriora sequor - Ovidius
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.
It's not hard to create a binary tree structure in a database without use of foriegn keys. Just make a parent and two child fields.
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
It's never enough to say, here's a review of some cool thing. If it remotely has anything to do with Linux, and appears on Slashdot, one must also issue an arbitrary slam of some related thing that happens not to be GPLed, in this case, FileMaker. FileMaker happens to be very good at what it does. People are moving in droves from Access to FileMaker, which is why FileMaker, Inc.'s financials keep improving. So what if it's not MySQL / Oracle / Postgres / the SQL-flavor-of-the-week? People who need an SQL database usually know they need it, and don't bother to consider low-end solutions.
FileMaker is good for small firms with relatively simple needs (Web storefronts, schools, and the like), because it's cheap, user-maintainable, and allows you to develop your applications rapidly.
Anyone who has ever wondered why some people have to put down Open Source whenever they wax poetic about their favorite commercial solutions, this is why. They're merely reciprocating.
Am I the only one who laughed out loud over this one? :-)
"We are designing a business-to-business application with shoppingcarts, orders, ordelines and other stuff with servlets and JSP.
The java part is clear. But the database... Pff, we are programmers not databasedesigners [sic]. Can anybody give us some help?" --from the Database Debunking site
Hackers and db admins seem to have different ways of thinking. The db admin may make a query where the coder would write a script to scan variables. 2 different ways of thinking that should be mastered, yet they are not always in the same head. Seems like a good team would have both heads in on the application design process. Sheesh. Don't write any code till you have those field names!
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
The hell with that. I use trees everyday with Oracle8. There are also 2 methods for tree queries in PostgreSQL.
It's worth remembering that objects are not stored on disk/ram in tree format - it's a matter of query, or how you view the objects. In C++/Java, a tree is viewed by how a function follows pointers. In SQL, all you need is foreign keys, and connect by (which is sort of a 'ORDER BY :PREVIOUS.PK_ID = :CURRENT.PARENT_ID' - which is obviously not part of SQL92, but simple nonetheless.)
While it's true that creating a tree structure is not difficult using SQL, it is rather difficult to maintain the integrity of the data, and also to manipulate the data or even query it in useful ways.
Some examples:
A tree should have one root level node (a node with no parents). This means that the column that holds the parent keys must allow nulls, but only in one row. A trigger or constraint must be employed to ensure this.
A tree cannot have any cycles. Let's say that you're using a tree to represent an org chart. Bill reports to Jenny. Jenny reports to Frank. You need to add a constraint to ensure that Frank can never report to Bill (because then he would be reporting indirectly to Jenny, his own employee). Again, this can be done, but requires triggers, and some crafty data design.
Just one more, I promise: A common kind of query for a tree is to return all the rows that have the same ultimate parent. For example, you want to know what the total payroll is for everyone that reports to Jenny or her subordinates. Not easy to do unless you go to special lengths in the data design.
An excellent treatment of working with trees and graphs in SQL is presented in Joe Celko's book: SQL For Smarties. This is an EXCELLENT resource.
Trees are a pervasive structure in the real world, the org chart is the most common example, but once you start looking, you'll find them everywhere. It would be a great blessing to see extensions to the SQL standard to handle trees more gracefully.
- Chris
brd27@hotspammail.com
X = { the root node } union { y: y is a child of some node in X }
This is perfectly well-defined; it's just a matter of getting SQL to handle it, as is done in SQL3.
---- "If we have to go on with these damned quantum jumps, then I'm sorry that I ever got involved" - Erwin Schrodinger
I didn't say it was good or efficient, merely that it can be done.