database « The Platypus Database

Recently I have been reading ‘Microsoft SQL Server 2012 – A Beginner’s Guide 5^th Edition’. I like the way it describes and gives an overview of database terminology. There are database components which make up a database system. The components are database application programs, client components, database servers and the databases.

A database application program is software that is written to access a database. Most people who use a special program at work that retrieves customer data or vendor data or product information are using a database application program.

A client component is a special program written to interact with a database. These are usually written by the companies that create databases.

A database server is used to manage the data stored in the database and to interact with the requests sent by database application programs or client components.

A database is a collection of data arranged in a logical manner that can be accessed via a user interface which allows the user to create databases, add, retrieve and modify data and manage the data.

I thought that broke down the components nicely and in a very understandable way.

Unfortunately in the real world the database is rarely “arranged in a logical manner”. You can only hope that the programmer that was hired to develop some custom application or add onto an Off-the-Shelf application had some clue about the rules of database design and you get to work with data that is normalized. However, if that was the case you probably wouldn’t be at The Platypus Database web site looking for help. Most likely the database you are accessing is not normalized, has bad data or some other tragedy of a design flaw.

Hopefully you are here to learn about databases BEFORE you design one and you will be able to pass along a database that follows the rules and will be a joy for someone to work with in the future.

So, let’s start with a few basics. You can build a database in a variety of database programs: Microsoft Access, Microsoft SQL Server, IBM DB2, MySQL, Oracle, Paradox, and a few others. These are just the ones that I can come up with off the top of my head. Some of these are database servers: MySQL, Oracle, MS SQL Server and the DB2. MS Access is usually just a local database but can be set up to act like a database server. Some are free and some cost money and some offer both a free and paid version.

Most of these programs come with a client component that allow you to access the server or write queries or create stored procedures. MS SQL Server has SQL Server Management Studio. MS Access is the client component to access MS Access databases. Oracle Enterprise Manager Database Control or SQL Developer can be used to connect and manage Oracle databases.

All of these applications that connect to databases depend on SQL – Structured Query Language. This is how the users tell the database what they want it to do: Create a table, return some data, insert or delete some data. (If you are a programmer creating or modifying a database application program, you will need to consult the specific programming language documentation to determine how to connect to your database and the requirements for sending queries and processing results – specific programming languages are beyond the scope of The Platypus Database.) Second only to understanding normalization, the ability to write and understand how queries work is key to efficiently extracting data from the database.

Back to our main question, what is a database? When most people talk about a database they are usually talking about the collection of tables that hold data. If you are familiar with spreadsheets you can easily transfer that understanding to database tables. A column in a spreadsheet correlates to a FIELD in a database. A row in a spreadsheet equals a RECORD in a database. Now imagine that all the worksheets in a workbook are TABLES in a database. So for most people, a good basic conceptual understanding of what databases are, can be summed up as: a collection of DATA, held in RECORDS, which are collected in TABLES and accessed using QUERIES.

But you are not most people, you are Platypus Hunters in training. So next time we will cover some basics of Cobb’s Relational Model.

Until then, Happy Hunting!

Welcome to the Platypus Database – a blog dedicated to the databases all over the world that were not designed, but seemingly put together with very little thought – like the Platypus

Not to malign the poor Platypus, but it does look like no one really thought about how it would look with all those different parts stuck together like that – and it lays eggs, which for a mammal is kind of weird.

About ten years ago I posted this discussion at www.tek-tips.com asking if anyone else had to deal the same issues in their databases that I was finding – no normalization, shortcuts to avoid having to do things the right way, dates, validation (mostly the lack of), and other situations. (The thread quickly deteriorated into a treatise on Chimera Database – which almost became the name of this blog but I couldn’t get it to work as well.)

So why am I writing this blog?

I developed many applications against the Platypus Database over the past ten years and learned many things that are useful in certain situations when dealing with bad data and bad design. You need to learn monster SQL skills and really understand the power of OUTER JOINS to overcome some of the issues you will find. I would like to share those tips with other developers to help them learn how to use the power of the query to only get the data you really need into your application and minimize database calls.

As a way to continually improve and learn myself, I am working towards my MS SQL Server 2012 certification. I want to blog about things I find as I learn about MS SQL Server and the studying and testing that I find exciting and want to share.

At this same time, my friend Jenny is trying to find a way to get into the IT world and I have been looking and looking for some web pages that can explain some basics about databases. Every page that I found was either someone asking for help to design their database or did not mention normalization at all or if it did mention it, didn’t apply it correctly. If you are going to work with databases in any way, you should understand the basic concepts and how to create a real database that follows the rules.

So, I am finally spurred into action to write about these topics so my friend can benefit and learn things the right way and we can all move towards certification as Platypus Hunters.

Thanks for dropping by, I hope to see you again soon!

Leslie Andrews, Platypus Hunter Extraordinaire