Database Management Basics – What You Need to Know if You Want to Impress the Nerds in IT
Database Management Basics – What You Need to Know if You Want to Impress the Nerds in IT
By David J. Anthony
Don t you dread talking to those arrogant nerds in IT? You can t avoid doing it, so the next best thing might be to learn a little about what they (and you) work with every day: databases. 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. That person thinks their change is there, not knowing it was over written by you. Concurrency checking prevents this kind of dilemma. With concurrency checking in force, your change could not be written to the database. Of course you could just reopen the record and make your change, but it s on you if you don t tell the other person!
By now you re probably thinking this database stuff isn t so hard…and you re right! You were able to get through what a database model is, how that model is realized through a schema, how programmers manipulate data with a query language and how some sticky database wickets are avoided with data integrity techniques. I think you re now good and ready to head on down to the IT dept and have your chat with the local nerds!
David J. Anthony is a database systems developer and is webmaster of a site that provides a ton of info about databases, DBMS software, security concerns and more. If you want to go to the next level of database management proficiency and really give it to the nerds, check out Dave s site.
Article Source: http://EzineArticles.com/?expert=David_J._Anthony
http://EzineArticles.com/?Database-Management-Basics—What-You-Need-to-Know-if-You-Want-to-Impress-the-Nerds-in-IT&id=558895
Related post
Database And Document Management Systems
...
Contract Management Databases
...
Contact Management Databases: A Top Asset for Systems Integrators
...
Desktop Management Interface
...
Make the Most of the Database Management for Membership Software
...