Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

Database Management Basics - What You Need to Know

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.

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro

Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268