Database Management Basics - What You Need to Know
By David J. Anthony
When they start throwing terms around like schema, SQL, transaction processing or relational
design, wouldn't you like to understand what they're talking about? In this article, I'll explain
the basic fundamentals of databases in a way that will allow you to interact intelligently
with IT personnel and maybe even improve your own workload throughput.
Probably the most fundamental of database concepts is the type or model of the database.
This has to do primarily with how data is related or stored with other data. One way of storing
data is in a spreadsheet (aka flat-file), which is a common practice in small businesses. But
this model offers no ability to connect or relate the data in any way, unlike the most common
one in use today: the relational model. In a relational database, data is "connected" with
other data through a schema, using things called primary keys and foreign keys.
In order to efficiently organize data, a way of relating or connecting it must be designed.
A database schema is the design of a database based on its model. In a relational model, data
is organized into fields (columns), records(rows) and tables. A table contains many rows and
each row contains a certain number of columns.
For example, there might be a customers table whose rows represent individual customers
and whose columns represent different attributes of each customer like name, address or customer ID.
The customers table might be related to an orders table which contains rows of orders. Each order
row might have a date, product ID and customer ID column.
The two tables would be related by common data values in each table's
customer ID column. The customer ID column in the customers table would be a primary key -
since we're dealing with a single customer - while the customer ID column in an orders row
would be a foreign key because there could logically be several order rows for the one customer
ID. With a good relational design comes easy data access and look-ups using a query language.
No doubt you've come across the term "SQL". Whether you've heard it pronounced "see-quell"
or "ess que el" (no, it's not Spanish!), SQL is THE query language. It has a long history and
has been standardized to a degree. Programmers use it to get data, update it, create it and
delete it. SQL "programs" are called queries, which makes sense if you're getting data but
not much if you're deleting it! Some examples of SQL queries:
SELECT Name, Address FROM Customers WHERE CustomerID = 23
UPDATE Customers SET Name = 'Fred' WHERE CustomerID = 23
INSERT INTO Customers (Name, Address) VALUES ('Tom','123 Sycamore Ln')
DELETE FROM Customers WHERE CustomerID = 23
Note how English-like the queries are. They're so clear I don't think I even have to
explain their purpose. But, at the same time, SQL queries can be very complicated. They can
also be used to create tables, columns, rows and even the database itself! SQL queries are
essential for manipulating and creating data, but while doing this there is one thing that
must be kept in mind: keeping the data meaningful by maintaining its integrity.
What a mess it would be if there were an error while creating a transaction in your company's
general ledger database! You would have a major data integrity issue. It would probably wind
up that the debit got inserted into the General Ledger and not the credit, thereby making the
books (and the CFO) unbalanced.
What can you do to prevent this? You can run the SQL query that creates the transaction
rows through a transaction processor, which commits the rows to the database only if there
is no error. Either both the debit and credit get committed or neither does. No unbalanced
ledgers (or accountants).
Another data integrity problem that is common is concurrency conflicts. Let's say you
open a customer record (row) on your workstation, make a change to it and go to lunch before
saving the change. While you're gone, someone else opens that same record on their system and
makes a different change and saves it. You then come back from lunch and save your change,
over writing the change the other person made.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
