Database Answers

 

4.1 Stage 1 – Data Sources and Database Design

4.1.1 Overview

4.1.2 Best Practice

The Information Catalogue should be used right from the start.

The initial data to be stored would typically be a Glossary of Terms.

This will ensure that all interested parties share the same understanding of terms, phrases that are in common use within an organisation.

Other early data to be would be Data Owners

 

Question : How do I design a Database ?

Here is a series of Steps in designing a Database :-

Step 1. Establish the Scope of the Database.

Step 2. Identify the ‘Things of Interest’

Step 3. Define the Business Rules that determine how these ‘Things of Interest’ are related

Step 4. Choose the Data Modelling Tool.

Step 5. Produce first draft Data Model and review with the Users.

Step 6. Ask the Users to provide sample data.

Step 7. Load data into Database and confirm the Design.

 

 

Question : What are Agile Database Techniques ?

Agile Development is an approach that emphases close collaboration with End-Users, and incremental delivery on short time-scale, typically about six weeks.

 

The recommended reference here is a book entitled “Agile Database Techniques” by Scott Ambler, a highly-respected writer in the general field of Agile Development.

Here is the Amazon link :-

http://www.amazon.com/Agile-Database-Techniques-Application-Development/dp/0471202835/ref=sr_1_1?ie=UTF8&s=books&qid=1247041446&sr=8-1

 

 

Question : What is Database-driven Application Development ?

The use of Data Dictionaries to provide an interpretive approach to building Applications has been used on a number of occasions by different organizations in the past.

Probably the most high-profile current example is Salesfore.com which has established an On-Demand business with 1,000,000 Online Users at https://www.salesforce.com/

At Database Answers, we created an On-Demand facility several years ago to create and populate Tables.

This facility requires as a Login :-

http://www.databaseanswers.org/pi_user_registration/login.asp?ref_url=/data_models/user_extendable_database/uedb_tables.asp

 

Work is in progress to move this facility to an On-Demand facility in the Clouds and specifications for are available in a separate document.

 

In general, the State-of-the-Art indicates that Application Generation is still a very labour-intensive process.  This results in time-consuming and expensive Applications which are difficult to modify.

The necessity of solving the ‘Impedance Mismatch’ by reconciling Relational Database Tables and the Object-Oriented approach adds to the problems.

 

We favour a Data Dictionary-driven Approach which leverages the power of Templates.

This is discussed in the next Section.

 

From time to time, efforts have been made to extend the scope of Modelling techniques to include more of the System Development Lifecycle.

These have met with mixed success. Therefore, although Data Modelling remains a powerful and widely-used technique, Systems Modelling is not common practice except in the area of Object-Orientated Design, where the Rational Unified Process (RUP) is common practice.

However, Microsoft has been putting significant effort into extending the scope of Modelling Techniques with their Oslo initiative.

 

Here’s an extract from a Presentation by Bill Gates :-

[Oslo] is actually taking the kind of models that you're seeing arising in specific domains, like software management in System Center, or your data design over in SQL, or your process activities over in BizTalk and saying, we need to take all these domains and be able to put them into one model space.

In fact, we need to let people create their own domains that aren't just isolated, but that exist in this one modeling space. And that's what Oslo is about.”

 

Here’s a link to the Microsoft Oslo initiative for Model-driven Applications :-

http://www.microsoft.com/soa/products/oslo.aspx

 

Question : How do I plan a Career in Data Modelling ?

Data Modelling is a core discipline that provides a solid foundation for Data Analysis and comprehensive Data Management.

The market leader in Data Modelling Tools is ERWin from Computer Associates.

My favourite is Dezign from Datanamic.

Both of these can generate SQL Scripts to create Tables for a wide variety of Databases, including Oracle and SQL Server, IBM’s DB2, mySQL and so on.

 

Data Modelling therefore offers an excellent path away from vendor-specific thinking to a higher level of abstraction.

 

To plan a career in Data Modelling, it is best to start by creating a wide variety of Data Models and then integrate some to.

A good starting-point is this page listing over 600 Kick-Start Data Models :-

 

 

Question : How do I plan a Career as a Database Administrator ?

Being a Database Administrator or ‘DBA’ can be a very challenging and rewarding role.

The current trend is towards ‘Point and Click’. This means that many functions that used to require a DBA to learn complex command-line instructions have now been deskilled.

 

This trend has been accelerated by Microsoft with SQL Server where they have exploited their expertise in

Windows Interfaces.

 

Therefore, a Microsoft SQL Server DBA can look after more Databases than an Oracle DBA.

This means that it is important for a DBA to acquire skills in SQL Server’s T-SQL and Oracle’s PL/SQL.

 

The move in the marketplace is away from Oracle to SQL Server.

This is especially encouraged by the Global credit crunch, where Oracle might lose out to SQL Server on grounds of cost.

 

Question : Is it worth getting Certification as a DBA ?

Certification can be described as ‘Necessary but not sufficient’. In other words, some employers consider it as evidence that you have the necessary technical knowledge and skills to be a Database Administrator, but without any experience, it will not guarantee you a job.

 

If you take your profession seriously and are committed to self-improvement, then you should certainly consider getting certified in the DBMS of your choice.

 

Here are some very useful Microsoft Web Links :-

 

 

 

Question : How do I handle multiple languages in a Database ?

Answer : When Users need to logon to a system, the Database will store a preferred language field for every User. UNICODE is be used to correctly store different characters sets, such as Japanese, French or Hungarian.
More details are available in Section A.14.

 

Here is a Tutorial on Database Design on the Database Answers Web Site :-

 

Question : How do I teach Young People Data Modeling ?

Here is a Tutorial on Data Modeling for Young People :-

·          http://www.databaseanswers.org/tutorial4_data_modelling_dimple_and_toby_visit_windsor_castle/index.htm

 

Question : How do I manage Data Modelling across multiple Projects ?

Data Modelling is a very powerful discipline and can make a particular contribution in complex situations or large organisations.

In this context, it is important to manage Data Modelling in a controlled and consistent manner.

These are some factors to consider :-

 

Here is a good example of Subject Area Data Models on the Database Answers Website :-

 

 

 

 

 

Question : Can I use XML to report on Metadata in my Data Models ?

The ability to export Data Schema or MetaData in industry-standard XML is a powerful facility that should be included in the criteria you follow when choosing the right Data Modelling Tool to meet your requirements.

 

This would provide a facility to export Database Schemas into a Data Dictionary or Information Catalogue.

In other words, this very useful facility would be one that exported records of tables and fields from a Table into a CSV file which could then be imported into a Data Dictionary Table.

Of course, even better would be an ODBC or Web Service interface to the System Catalogues in the Data Modelling Tool.

 

My favourite Data Modelling Tool is Dezign from Datanamic, (http:/www.datanamic.com) because it produces diagrams that I like to look at, it has excellent features and it is very affordable.

 

Dezign stores Data Models in a .dez file, which is itself is an xml file.

This could probably be transformed into a csv file with XSL transformations.

 

This Weblink is an article contributed by Robert Allen of SlaphappyGeeks.com,  which provides an excellent introduction to the subject :-

 

This Weblink provides an example of how it can be used in practice :-

 

 

Question : What are Use Cases ?

Use Cases are not directly related to Databases but are frequently used in the specifications of Applications in an Object-Oriented environment.

Here is the Wikipedia entry for Use Cases :-

 

Here's one which has a Tool for creating Use Cases that you can download for a free trial :-

 

This one shows some small examples of Use Cases :-

 

Use Cases are defined in Use Case Diagrams, which feature Use Cases and Actors who look like

‘stick men’.  Here is a simple example :-

 

 

4.1.3 Templates

A very valuable set of over 600 Kick-Start Data Models are available on the Database Answers Web Site :-

                                - http://www.databaseanswers.org/data_models/index.htm

You will probably find something to give you an excellent start to designing a new Database.

If not, contact us by email at barryw@databaseanswers.org and we will help you to get started.

 

4.1.3.1 A Database for Local Authority Parking

Here is an example of an Entity-Relationship Diagram for a Database designed for Parking Tickets in a Local Authority in the UK :-

 

 

 

 

 

 

 

 

 

 

 

4.1.4 Tools

There is a wide choice of Data Modeling Tools and here is a sample of the most popular Tools available :-

 

4.1.5 Tutorials

 

4.1.6 How do I ?

4.1.6.1 Get Certified as a DBA ?

Certification can be described as ‘Necessary but not sufficient’. In other words, some employers consider it as evidence that you have the necessary technical knowledge and skills to be a Database Administrator, but without any experience, it will not guarantee you a job.

 

If you take your profession seriously and are committed to self-improvement, then you should certainly consider getting certified in the DBMS of your choice.

 

Here are some very useful Microsoft Web Links :-

4.1.6.2 Tune the Performance of a Database

Performance Tuning is a complex subject.

The starting-point is the Query Execution Plan (QEP), which is how the Query Optimiser interprets the SQL and decides how to execute it.

Therefore, the first step is to examine the QEP to make sure that the appropriate Indexes have been created and are being used properly.

 

Other aspects to be investigated include partitioning and clustering.

The approach for major RDBMS types, such as SQL Server and Oracle, follow the same basic principles.

If you want to learn more, this Wikipedia entry is a reasonable starting-point :-

http://en.wikipedia.org/wiki/Database_tuning

 

 

 

4.1.7 Qualities for Success as a DBA or DB Designer

Skills include T-SQL for SQL Server and PL/SQL for Oracle.

 

A good Database Administrator (DBA) likes to have responsibility for a clearly defined area, namely a production Database.   He (or she) is happy to make decisions and defend them against questions from Developers, Managers and End-Users.

 

It is useful for a Database Designer to have a DBA background, but is likely to welcome the challenge of interacting with Users, creating a design for a new Database and working with Users to get agreement on the new design.