Database Tutorial

Course Tutorial Site

Site Admin

Lesson #2: Tables

with 15 comments

Database management systems (DBMS) organize data into databases. Databases are containers, like directories or folders are containers in operating systems. Directories or folders hold files, which may be program, data, or link files.

Like a file system in an operating system, databases control how data is stored and retrieved. File systems maintain metadata, or data about data, and metadata keeps bookkeeping information about its files. The DBMS also keeps metadata or bookkeeping information about their database tables.

File systems store some information in a directory table inside a file, like an the Unix and Linux OS use an inode file. The DBMS also stores metadata information in a specialized database, which is called a system dictionary or catalog. The data dictionary is a separate database from the databases where the DBMS stores data.

Inside databases, the DBMS stores data in tables not files. Tables are lists of structures. A structure (struct) is a composite data type declaration that defines a physically grouped list of variables under a new name. You create a composite data by creating a table with a set of comma-delimited data types, which are called columns.

Tables differ from programming lists because they don’t exist as a collection of data in contiguous memory. While tables definitions are stored in a data dictionary, the table’s data is typically stored on non-contiguous disk space in a database.

A DBMS uses programs to manage its databases and tables, and many other structures that help make the data easier to create and access. All DBMS engines provide a SQL (Structured Query Language) implementation that lets you manage databases and data. Each implementation of a SQL is a dialect, which can be like speaking a language with an accent.

SQL should contain five major groups of commands:

  • Data Definition Language (DDL): Commands that let you CREATE, ALTER, DROP, RENAME, TRUNCATE, and COMMENT tables.
  • Data Manipulation Language (DML): Commands that let you transact with data by allowing you to INSERT, UPDATE, MERGE, and DELETE rows from tables.
  • Data Control Language (DCL): Commands that let you give and remove privileges to work with databases and tables, like the GRANT and REVOKE commands.
  • Transaction Control Language (TCL): Commands that let you group activities into transactional units, which are necessary when you want a set of things to occur without fail or fail together. The three commands are: COMMIT (or save changes), ROLLBACK (or undo changes), and SAVEPOINT (or a bookmark that sets where to start the changes).

Some SQL dialects are imperfect and the vendor may do little to change that over many years, like Microsoft SQL Server. Microsoft SQL Server has repeatedly failed to add SQL commands for certain basic operations; and as a result you have stored programming commands, like the sp_rename, sp_renamedb, and so forth. These commands take the place of ANSI-compliant SQL Data Definition Language (DDL) commands.

As lists of structures, tables implement an open architecture. You can put something meaningful or something without any meaning into any table. Fortunately, there are design rules that define how you should manage the physically grouped columns; and there are database constraints that let you impose restrictions to manage the contents of any table.

This lesson focuses on how you create tables with constraints to implement meaningful containers for real data that can run businesses.

Written by michaelmclaughlin

September 22nd, 2009 at 11:24 pm

Posted in

15 Responses to 'Lesson #2: Tables'

Subscribe to comments with RSS or TrackBack to 'Lesson #2: Tables'.

  1. What exactly is meant by “Drops a structure from the database” as used by the DROP statement?

    Andrew Thimmig

    12 Jan 10 at 12:29 pm

  2. I’ve modified it above. Do you find the explanation clearer?

    michaelmclaughlin

    12 Jan 10 at 12:43 pm

  3. If you receive this error:

    ERROR 1005 (HY000): Can’t create table ‘sampledb.tweetie_bird’ (errno: 150)

    use this command to disable foreign key checks:

    SET foreign_key_checks = 0

    Found in:
    http://www.phwinfo.com/forum/comp-databases-mysql/344594-error-1005-hy000-line-1-cant-create-table.html
    post#2

    Dan Ricks

    12 Jan 10 at 3:27 pm

  4. Dan, this is already covered here in the MySQL re-runnable script section.

    michaelmclaughlin

    13 Jan 10 at 12:29 am

  5. in the second paragraph the link says “stantards” should say “standards”.

    Marcus Norton

    4 Mar 10 at 2:13 pm

  6. Brother McLaughlin, a number of us found the short paragraph describing the purpose of the ‘set foreign_key_checks=0’ command really confusing. Can you please rephrase it?

    Kara

    3 May 10 at 1:00 pm

  7. I’ve been reading this paragraph from the MySQL section and suggest the following change:

    Sometimes you don’t want to allow your scripts to ignore the referential integrity rules between primary and foreign keys. This means you can’t simply issue the following command to the InnoDB engine to ignore the constraints:

    Referential Integrity rules in a database preserve primary and foreign key relationships between tables. Sometimes we need to ignore these rules to perform actions that usually would not be allowed, for example dropping two tables that have mutual references in their primary and foreign keys.

    However this behavior should be used sparingly, as it can compromise the integrity of the database. The following command will tell the engine to ignore Referential integrity rules:

    mysql> SET FOREIGN_KEY_CHECKS = 0;

    You should remember to re-enforce these rules after performing the required commands, by using the following.

    mysql> SET FOREIGN_KEY_CHECKS = 1;

    Daniel

    4 May 10 at 5:22 pm

  8. I took some liberty with your remarks but adopted all of it. Thanks!

    michaelmclaughlin

    21 Jun 10 at 10:23 pm

  9. I’ve adopted a change suggested by Daniel.

    michaelmclaughlin

    21 Jun 10 at 10:24 pm

  10. Just an FYI, the comment link goes directly back to this tutorial. Is there an example code of the comment command?

    Doug Kelley

    11 Jan 11 at 8:41 pm

  11. Just curious on the BOOLEAN type for MySQL. Your book states there is no Boolean data type in MySQL. This link states that BOOL or BOOLEAN are synonymous with TINYBIT(1)…so does this mean there are BOOLEAN data types for MySQL? http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

    Dean

    6 Oct 12 at 2:09 pm

  12. The link under the DDL list for COMMENT leads back to this same page.

    Brennan T

    17 Jan 14 at 8:37 pm

  13. Brennan T, Thanks for the note, it was missing the URL in the href attribute, it’s fixed. Michael

    michaelmclaughlin

    18 Mar 14 at 11:06 am

  14. Dean, As stated in the web page you cite, the BOOL and BOOLEAN data types are synonyms for the TINYINT data type.

    michaelmclaughlin

    22 Oct 14 at 1:15 am

  15. Doug, I added this COMMENT Statement web page as an outgrowth of your request.

    michaelmclaughlin

    22 Oct 14 at 2:01 am

Leave a Reply