Data Dictionary and Database Normalization

¶ … Dictionaries

Neo-looked confused. "Programs hacking programs?" To which the Oracle replied, "At some time, a program was written to govern everything. The only time you see a program is when it's malfunctioning, and needs to be replaced, or exiled." Jack, I know you are new in the IT department, but I can't emphasize enough the need for you to take the Oracle's advice. We want our software, CRM modules, and sales processing databases to function without being seen. We don't need any programs fowling up other programs. I don't want to have to fight any 'vampires' or 'ghost twins' because you have allowed the database data dictionary to get corrupted.

The data dictionary is a tool which keeps the other programs up-to-date, and feeds information to the other querying programs as to what to expect from the data they are receiving. The data dictionary is a separate, and important indexing database which we use in database management systems. It is composed of a file that defines the basic organization of a database, and contains a list of all files in the database, including the number of records in each file, and the names and types of each field, Etc. Most database management systems, including the system we use, keep the data dictionary hidden from users to prevent them from accidentally destroying its contents. Part of your job responsibility is to maintain this dictionary, and re-index it on a regular basis so as to keep the rest of the company software glitch-free.

While this sound like an important task and it is, the job is much simpler now that we have installed a self updating data dictionary. In the past, at the end of each sales day, the data dictionary had to be manually updated from summary reports which were received from every department. You can imagine the snafu that occurred when a department forgot to run the report at the end of the day. Many of our sales, and customer service problems were created because at the start of the next day, the data dictionary indexing wasn't complete.

However, your predecessor convinced management to install a meta-learning data dictionary. This program automatically runs an indexing program at the end of each sales day, and updates the data dictionary as to the number of records, type of data stored in each file, etc. The nicest feature about our meta-learning data dictionary is that it also scans the individual fields in every new entry, to ensure all the entries are in the proper format. R& D. is forever forgetting to label currency with 2 decimal places, and I think one of the guys in marketing is a bit dyslexic. Someone regularly transposes letters in the customer's last names between when the item is initially entered in the new customer database, and the accounting department. The data dictionary review program will flag the entries which don't match up, and you will need to notify the departments in order to get the records corrected.

Our data dictionaries do not contain any actual data from the database. The Data dictionary is simply a bookkeeping and information management system for monitoring, and massaging the data which makes our company run. Without a data dictionary, our database management system cannot access data from the database.

Jack, since you are new to the company, I am including a review of the process we used to construct the parameters for the data dictionary. In the near future, and new program may be rolling out of the upstairs planning offices, (no, I can't say any more than that) and you will be called upon to construct a new database control system, via a data dictionary, and an entirely new database.

The process of building a Database included consulting with all the departments involves, and designing a system implementation, as well as employee training. Mistakes once the program hits the floor are expensive, so you must dedicate your efforts to getting this fright the first time. The first step is to graph out an entity relationship (ER) diagram. The ER diagram is a useful tool for managing the development of a database information system. From this document you will also get the parameters for the data diagram, because all the aspects and involved departments must be included in the ER diagrams. This tool will systematically and visually model data into logical and easy-to-understand graphical representations.

The 10-Step Process for designing an ER is summarized in the following list. Diagramming and modeling a new DB is a 10-step process, and the first five consist of drawing the ER diagram:

1. Identify DB entities from all departments

2. Map relationships between the entities, and the flow of information

3. Identify attributes of the entities, including type of information for each field, and what department is responsible for the information.

4. Reduce all attributes to coded values.

5. Select attributes for primary keys which drive the database queries.

At this point your ER diagram is complete. The next five steps model the data tables so they conform to relational properties.

6. Build data tables (relations) from the ER diagram constructed in steps 1 to 5.

7. Eliminate or modify all attributes (columns) that are sequentially ordered.

8. Eliminate or modify all records (rows) that are sequentially ordered.

9. Eliminate all redundant attributes and records.

10. Construct a data dictionary as a control document. (Tribunella, 2002)

You will find as you build the data dictionary, and review each field that different departments will want to use the same field, but their designation is different, and the way the department wants to build the query, and utilize the results will differ from department to department also. The data dictionary will help you find, and define all these discrepancies, and then you will need to work with department heads to determine a common filed valuation. Good luck with George down in the accounting office.