what are essntial steps in doccumenting a database other then following'
for example
External Schema
Conceptual data model
People Involved
Approaches for Database Development
Software architecture document
Information Systems Architecture
Identify Corporate Planning Objects
System requirement specification
erd
dfd

There are no essential steps for documenting a database. Most of the databases that I'm asked to fix have worked for months or years without any documentation at all.

-PatP

Almost all our customers are Australian banks and financial institutions, and they have both an IT Audit, as well as a "power" user requirement; the latter demand to use the database directly, from various (simple to complex) reporting tools.

When I deliver databases (fixed price, fixed delivery), it must have a fully detailed Data Model, that's one step deeper than the ERD. Full technical (not laymans) commentary at the Entity and relation level is mandatory. If you use good naming standards, then commentary at the column level is highly desirable but not mandatory. Most modern data modelling tools (ERWin, PowerDesigner, ERStudio) provide this capability within the tool itself. The final hardcopy consists of a few chapters on approach, design requirements, standards, etc; and the appendices are produced directly from the modelling tool. Plus a few scripts.

For developers (working with me on the project long before the final documentation is available, actively developing against the database, which I am continually extending), the current version of the model, included the said commentary, in hardcopy and electronic format, is adequate. This assumes some stability, predictability (no sudden undiscussed changes), and of course coding standards, so that their code is isolated from the effects of change. During an active database/app development project I usually have one two-hour meeting per week, plus I make myself available to answer problems only. This is possible precisely because the data model is documented, and standards are in place.

To give you context: just as I am working on the data model, and it is changing within the identified project stages, they are working on the DFDs, and changing that. I am dependent on them for that. Sometimes I supply that as well, in which case the DFDs have to be supplied along with a matching Data Model, at defined project timeframes. They go hand-in-hand; they have to match.

I always provide an Architecture doc. The rest of the items you list as non-essential. No one produces a System Spec any more; unless the project is sent offshore, then it is considered mandatory. I disagree, my experience is the Definition document and the fully defined Data Model is enough.

Hey derek,

I don't understand why you are not freezing the specs Does it not make the programmers have a rough time?

An approach that could be followed is to design and define the basic entities and attributes in full. Freeze these structures. Programmers then would have to define only temporary tables etc.

End

thanks for kind reply sir actually my question was that which reports are used to document a database
e.g capacity planing
data resource palaning etc

You funny

(But true dat)

Change is good. A business that isn't changing is dying. Naturally your software and data solutions should reflect that change.

Not if your programmers embrace an iterative and Agile approach.

1 I did not mean it like that. Yes, of course, for any single deliverable, a release, the specs are frozen. But the spec you start the project with ... and the spec that is the final documentation of the release, are two different things. The project handles all issues that were not complete in the initial definition; resolves all conflicts and bugs; there is more precision is the final doc.

Second (read my para in context), during the project, when you are working closely with a team of developers, the data model and the data dictionary are in a state of change. Not change as in change of scope of functionality, but change in terms of deliverable between me and the developers. I do not deliver the entire complete data model and dictionary at the beginning; just the initial data model (no dictionary). I deliver it in stages, to match the functions they are delivering. Each minor release corrects any issues in previous releases and provides complete new tables and columns for the new functions. That progression, and state of reasonable flux within the scope of the release, is normal in a development project. At the end, I deliver the final data model and the full dictionary.

(Really, the point I was making in the original post was that developers do not need a final, complete data dictionary [the users and auditors do]; they need an electronic working [known not to be final] model.)

2 The reason programmers do not find it rough is, the strict use of coding standards, which isolates their code from the effects of the database structure changing. Iterative or progressive development is normal in this industry/class of servers/value of project. It is far removed from what they do on MS systems.

3 Programmers are not allowed to define temporary tables. The overuse of temp tables is a classic hallmark of (a) unnormalised database [therefore it would be my fault, and I had better fix it in the db, so that the programmer does not have to use a temp table] or (b) programmers who cannot code SQL for a normalised db [we help them find employment elsewhere]. On my projects, they are not allowed to create #tables, they have to get me to do it; this is a policing action to prevent the misuse of tempdb.

3.1 For those very few temp tables that are valid, in addition to making sure the data modeller does it, and does it correctly, I have methods that ensure they are high performance, and thus do not have the common problematic impact on the server.

Derek,

The context is clear. Now some clarifications.

(1) What do people normally do on MS systems?
(2) In my case also, progressive developments are normal and additionally since I work in a large tier II automobile company, I have to face the end user and thus have to give effective systems from day zero. The trick is of course to minimise scope-creep (in a release cycle) by doing a good system study. Regarding my post about freezing the specs, I was going by your earlier post where you said (emphasis is mine).

Hope this clarifies the point.

End

naumanarshadkiyan

Sorry, I missed your post.My pleasure.Those are operational reports, not really db documentation. At the outset, we give them a clear idea of the requirement (Estimate), usually that is used purchase the machine, and allocate SAN resources. It is part of the Definition doc. Somewhere in the early stages of the full development, after some data loading has been done, those figures are formally Confirmed. At Delivery, those figures are Confirmed Delivered; it is part of the inventory of components Delivered.

We have scripts that produce all database metrics used for capacity planning and resource planning; they work directly from the database catalogue (works anywhere, on what is really in the db (not what we think is in the db). You can request any level you want; table; index; partition; segment; device; etc as well as size; performance; activity; fragmentation characteristics. Point is, you want it all in the onereport but you want to be able to specify the columns. It takes seconds to run. These are handed over to Operations, and they run them (along with update stats, db consistency checks, backups, etc), every week or whatever, from crontab entries or ad hoc.

I use these same scripts as the first step in either a Technical Audit or Performance Tuning assignment. It gives me a complete physical inventory of objects. They are not a substitute for GUI based Administration tools, but they are the batch or character/text equivalent. The reports from the scripts end up as Appendices (before and after snapshots) in the TA or PT report. They are also extremely important as an Audit List before and after a change (either a minor db/app release or a PT reconfiguration).

Here's a few to give you a flavour:
Database-Device Allocation
Segment with Activity columns
Index with Performance columns (Be sure to check p32 for the summary and errors)
Partition with Fragmentation & Stats columns

Really? Cuz, I dropped out of Sybase seven years ago after attending a conference in Florida where it became abundantly clear to me that it is a dead and stagnant platform. I suppose if you never want to learn anything new and more powerful, or are incapable of adapting to change, then legacy systems such as sybase are the best career choice for you.

Do tell?

Anathap
Actually, I answered both your questions in fair detail. But the post has been deleted by an unknown person for unknown reasons. It not only stated the answer to (1), but explained why that segment of the market is what it is, and why people do what they "normally" do there. I can only speculate that someone took my considered and experienced technical analysis of the MS segment personally (even though there was no attack of any kind, and certainly no personal attack). The post was not "bait" or "trolling" (I do not engage in such behaviour, but I cannot speak for other peoples interpretation of my posts), it was merely a deep and honest statement of the realities that have to be dealt with commercially, which I understood to be your concern.

I dare not post again, since they will merely identify it as justification to ban me.

I cannot imagine what was wrong with my detailed response to (2).

Therefore, in the meantime, feel free to email me from my profile.

Blindman,

This thread is quite revealing, clearly you are not as blind as some others are. I was wondering why the question I posted got such a scathing response encouraging me to rebuild my entire system lest I kill people. Now I see I too was blinded by the Microsoft ineffectiveness. I guess having sub-second response times from my lousy old Microsoft SQL Server 2000 data warehouse that has 20 billion rows in several facts isn't up to the standards of some folks. Funny how that just seems to be a spreadsheet.

It amazes me how someone can be so authoritative in judging a product to be inadequate and yet have little to no experience using it. If all Mr. D saw of MS SQL was an Excel front end, he clearly didn't see any adequate usage. But then again, MS SQL is also not priced to compete with German Luxury cars either.

Anyway, to get back to the original query.

As systems get more complex and encompass more and more features, they tend to interact more and more with other systems. In this context, I would add an overall "context" diagram that tells the interaction between the system on hand and other external systems. Here I would detail the nature of the links, whether internal or external, volumes etc. Tis would carried overinto specific FRS.

To take an example from well known financial systems, it could be links to and from payment gateways, stock quotes, queries of retail customers, corporate bulk postings, statutory and regulatoy information, audit runs etc.

End

AnanthaP

Yes. When you have system interaction you need a context or interaction diagram.

For external systems (payment gateways; SWIFT/FIX; etc) we normally have the gateway fully defined once, that includes the interaction/protocol requirements, once. Then each consumer system needs just a chapter on the context/interaction it has with the external system/gateway.