Practical Issues In Database Management 55
| Practical Issues in Database Management | |
| author | Fabian Pascal |
| pages | 256 |
| publisher | Addison-Wesley |
| rating | 7.5 |
| reviewer | Michael Sims |
| ISBN | 0-201-48555-9 |
| summary | Not a practical, hands-on book at all; contains high-level database problems and theoretical solutions |
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.
A First Course in Database Systems (Score:3)
Integrity (Score:2)
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...
what's wrong with FileMaker Pro? (Score:3)
--
Maybe not practical, but very very useful (Score:5)
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.
Trees (Score:1)
Chapter 7 discusses data heirarchies and trees. In a nutshell: there are no trees in SQL. The author is distressed by this.
Given that a foreign key is basically a pointer you can store trees in databases, it might not be pretty and there may not be easy way to read them and it might not be a good thing to do - but if you feel the need then get right in there. Of course I could be totally wrong about this...
I am that idiot! (Score:3)
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?
DataBase Ease (Score:1)
it's extremely difficult to setup databases and get them to work correctly. I think some company could benefit from writing some software that would have a very easy point and click, or very simple command line, creat a config file, type of engiene to write a database. And all you would have to do is drag and drop options. Of course, this could also open up another can of worms, in that then anyone and his brother might try to write a databse and succeed, but not know what to do when the databse crashes!
And so this helps .... who? (Score:2)
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?
---
It misses an important chapter (Score:5)
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.
Re:I am that idiot! (Score:1)
Re:Maybe not practical, but very very useful (Score:2)
Database is one area that needs more conceptual information in the marketplace. You can know every practical command for your database system, but if you don't know how to design the system properly to begin with, you will have trouble and headaches from it forever.
A lot of people look at it like programming - they think, you "know" this database, OK, you're hired, just as they would hire someone who "knows" a programming language. At least Microsoft distinguishes between Database Administration and Database Design in their certification.
I know this because a previous employer sent all the programmers on an Administration course rather than a Design one. Then we had to design our database (luckily I knew a bit about this from prior experience). We couldn't even use our freshly-learned Admin skills because we didn't have enough privilege on the NT servers. :-)
Oh well, I've since moved to a larger company, where I only do front-end stuff. My database skills will now slowly stagnate.
Re:Trees (Score:1)
Re:DataBase Ease (Score:1)
The problem with using the wizard is, of course, that one size doesn't really fit all circumstances. Database design requires skill and thought - for any sizeable project, it's not something you should just 'whip together'.
-jerdenn
Re:Trees (Score:2)
Re:Trees (Score:1)
"Purchased knowledge" is a disease. (Score:3)
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).
FileMaker (Score:1)
Humph... (Score:3)
I'll have you know that it's a copy of Access. Thank you very much.
Greg
Re:I am that idiot! (Score:1)
if you are already skilled with ORACLE, then great, it's a powerful system. but it could be overkill for most projects.
Of course if you are in the *nix world then SQL server isn't for you. but there are still other options than oracle.
(does anyone else feel completely drained from this election?)
Re:Trees (Score:1)
Trees can be implemented in databases, although, as Lozzer points out, it might not necessary be pretty. However, the review says there are no trees in *SQL*, which is the language used to get data out of databases.
Even if you implement a tree in your database, the usual way to access the data is by reading the "root" record, then using some programming language to follow the pointers to other records. You almost never try to follow the pointers using SQL, because that quickly becomes difficult.
SQL is designed to grab sets of records out of tables. It's good at doing set operations such as union, intersection, and so forth. A tree structure isn't really a set operation. It is specialized for operations like that. It is not the right tool for implementing trees.
That's disappointing, because you very often want to use trees and similar data structures. It means you need more than just SQL to implement a useful database system, but as long as you have those other tools at your disposal, things usually work out OK.
Re:I am that idiot! (Score:2)
I to am a FileMakerPro Developer. Actually I am just wrapping up my first commercial solution with it right now (within the next two hours), but for the question at hand, where I usually work in is more importatnt: WebObjects!
For the project I am finnishing up right now, FP5 is the right platform, but I did find myself a little constrained by the close association between tables (called databases in FP5), and reports/views/layouts. I would up having to create a lot of extra calculation fields to get the exact behavior that the cutomer wanted. Not a huge problem as the datatbase is only 5K records big (counting centeral records), but somethign the WO Developer in me shuddered at.
Well, my point here was that with WebObjects now in the $800 range, it is suddenly closer to the range yoiu are looking at, and it does come with the lite version of OpenBase (no seurity, but if you have LAN firewalling, you can deal with this untill you get management to pop for the full version, or another DataBase... no MySQL is not a good idea for WO Development, EOF does not play nice with it yet).It is my sicere hope that the FileMaker and WebObjects teams can be slowly brought together on MacOS X. I have no real evidence for this, but if you look at the directons that the two teams are moving it is not totally out of the question (WebObjects new easy to use "Direct to Web" and "Direct to Java Client" assistants, and FileMaker Pro's XML moves).
My recomendation, start looking into WebObjects!
Re:I am that idiot! (Score:1)
Re:It misses an important chapter (Score:1)
Legality wise I assume the author is American and as far as I know their data protection laws are very scant, in comparison to the UK certainly. The point is that the book is available to an international audience and so the author can't really talk abou the legal issues.
Ethics is an interesting issue. Sure spam, whatever is a pain in the arse but one still requires a well strucured database to do it. Ethics are not an issue when it comes to writing a good database, whether the database is being used to store personal data for your hosipital patients or people you plan to kill is not an issue at a design level.
It seems to me to be a whole separate issue. Web design books don't discuss the ethics of creating an inflamatory site. I don't dispute the DB creator *should* be aware of these issues. I dispute whether you should criticise the author for omitting this topic.
Not to be elitist, but... (Score:3)
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.
How typical (Score:2)
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.
Stamp out, eliminate, and eradicate redundancy! (Score:5)
Am I the only one who laughed out loud over this one? :-)
Re:I am that idiot! (Score:2)
Yes - Visual FoxPro and Web Connection [west-wind.com].
It's only for Windows, but if you use a browser as the interface, only the fileserver and webserver have to be Windows - the people accessing it can use any browser on any platform. This works out really well for our foreign clients, because there is no application to be installed anywhere. Their users just have to have a browser and an internet connection.
They can log in, enter orders, update information, view reports - all the pages are generated on-the-fly from the live database running at the client's office, served up by our Web Connection apps.
However, this is not for everyone - you definitely need to be a database programmer who understands stateless access, HTML, etc.
2 ways of thinking (Score:2)
"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 [firstsql.com]
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!
Re:Maybe not practical, but very very useful (Score:2)
Re:FileMaker (Score:1)
You obviously have never used older versions that had no concept of relationships before. What a friggin' nightmare pain in the ass. You had to write logic *IN THE FIELDS THEMSELVES* too. YUCK.
Everything we had to do with filemaker we had to pull some trick to do it. I was so happy leaving that job.
Re:Attention: (Score:1)
Hmph. Purple. (Score:1)
Overview says it all .... (Score:1)
Re:Trees (Score:3)
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
No trees? (Score:2)
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.)
Re:Humph... (Score:2)
*grins*
Quidquid latine dictum sit, altum viditur.
Re:Trees (Score:2)
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
Re:It misses an important chapter (Score:1)
Depends on the context (Score:1)
Generating scripts by hand ? Nope. During the years in which you work with RDBs you acquire a tool chest. Mostly shell and perl scripts which are essentially fed some templates and do the job.
The resulting scripts however are regarded as the source, kept up to date and are version controlled. Not that much of a hussle really.
Of course you can usually reverse engineer the databases with most GUI tools. But when was that done last time ? Are all changes applied ? Is it done consequently.
Distributed environments are a good example. There are tools that do the job in the meantime. But, from architecting and designing quite a few distributed systems here's my breakdown on the effort:
Gathering business requirements 20 - 30 %
System Architecture 30 - 40 %
System Design 20%
Setting up object generation infrastructure 10%
Implementing, testing fixing: The rest
The more you invest into the architecture and design the less fixing, debugging, tinkering and praying is applicable. So the part where the gui really helps is minimal. Chances that you seriously fuck up a really complex environment are exorbitant.
The basics are important... (Score:1)
Do you really want people who work for you to be designing databases on a hunch only? Wouldn't you rather there be some sembelence of real engineering? Granted, even strict adherence to normalization isn't REAL engineering (in the hard-core measure it, prove it, test it kind of way), but it's better than nothing.
Anyway, I'm all for any material which attempts to alleviate this sort of (complete preventable) ignorance. It can only help.
Re:Trees (Score:1)
Book Name Complaints (Score:1)
While I agree the final name is a bit wordy, it's much more descriptive than the publisher's suggested title: Securing Your Applications -- no reference to VB or Cryptography.
Re:Integrity (Score:1)
triggers or external logic
...implies that they are NOT one and the same. nobody said triggers were external. there's nothing wrong with that statement at face value, and unless you've read the book, you're not providing much of a critique of the author's opinion. if you have read it, i'd be curious to know why you think what you do, because you're still not explaining yourself given the author's supposed criticism.
Re:It misses an important chapter (Score:2)
Re:Trees (Score:1)
Also, the CONNECT BY predicate is an Oracle extension, rather than part of the SQL standard, so for many of us, is not a solution.
Don't get me wrong, it is definitely possible to deal with trees in SQL (I do it all the time), it simply requires a lot of overhead that could be nicely handled behind the scenes, thus improving performance, making code easier to read, standardizing behavior, et al.
- Chris
brd27@hotspammail.com
***************
Re:Trees (Score:0)
by Anonymous Coward on Thursday November 09, @12:52PM EST (#55)
Oracle:
Definition of table MYTREETAB
ID, NAME, PARENTID
SELECT NAME FROM MYTREETAB
START WITH ID = [INSERT ID FOR ROOTNODE HERE]
CONNECT BY PRIOR ID = PARENTID
Very useful. Depth first. Loop detection included.
Database Ethics (Score:1)
canofworms.load
grindingnoise.sound.play
Ethics. Hmmm, gotta get a bigger can. Ethic value can be situationally/occupationally dependant. I have written databases for conducting surveilance, including connecting such databases into other databases in order to produce capability that the original db's creator didn't foresee, mainly because he did not know what other databases I had.
Now, this is ethical for me: I work in law enforcement. If a friend told me he was stalking his ex and wanted me to write a DB for him to log it, that would not be ethical.
In both cases, a person's privacy is being invaded. In the former, law enforcement is trying to catch criminals. In the latter, a sicko is trying to spy on a private individual who has done no wrong. Both are individuals.
Now, you can argue "for the benefit of society", but then you can bring in Libertarian-esque arguments that everyone is entitled to full privacy.
Where do you draw the line?
--
Recursive SQL reference (Score:2)
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.
Re: another option? 4d (Score:1)
that said, you could use 4d for that, but what 4d really gives you are cool rad tools and a network client you can fall back on if your browser dreams don't pan out.
i currently develop/administrate a 4d publishing system that is used to publish some 16000 catalog pages per year... yeah, and i too would love migration to oracle and xml and blah-blah, for a chance to cash-in on a consulting gig someday, but i serve editors/writers/art directors well and lord over buckets of code, routinely passing a dozen or more parameters through a chain of methods indirectly via pointer to a blob, this is not filemaker pro! you can code quick and stupid, or commented and clever, with complete control of scope in a multi-processing/multi-user client-server system with an awesome debugger...
uh, the bottom line: you can be a compsci guru and not puke, and then have some confidence the organization won't fall apart when you leave--it just ain't that tough to work with, otoh 4d is not a popular platform.
Re:DataBase Ease (Score:1)
Re:Integrity (Score:1)
I've not read the book, I'm only mildly criticizing the review statement. I agree with the sentiment of what he saying - I'd always use declarative constraints when possible, triggers as my next preference and external logic as a final option. I just think the statement is a bit imprecise:
Essentially, the author's advice boils down to implementing integrity in the database itself rather than via triggers or external logic.
Taken as a whole this phrase says to me that triggers don't implement integrity internal to database. As you say it also implies that triggers are not external logic - so if they are not internal and not external what are they?
Re:I am that idiot! (Score:1)
postgreSQL (Score:1)
The methods are a new DB API function db_dfs (depth-first search); and an older tcl-layer fxn which I am unfamiliar with, but I know of its existence. Ask at the above.
Re:Is this the Fabian Pascal I remember? (Score:1)
This book sucked (Score:1)
Re:Trees (Score:2)