Data Management

The Linux database server demystification project, part 3: Creating a database with Sybase ASE

In part 3 of this series of Daily Drill Downs on Linux database servers, Bryan Pfaffenberger shows you how to create your own Sybase Adaptive Server Enterprise (ASE) database.

Ready to create your own Sybase Adaptive Server Enterprise (ASE) database? In “The Linux database server demystification project, part 2: Installing and configuring Sybase ASE , “ you installed and configured Sybase ASE, installed the Pubs2 database, and learned how to perform a simple SQL query with the text-only isql utility. (For a review of Linux database fundamentals, see part 1 of my series.) In this Daily Drill Down, I’ll show you how to create a simple database with Sybase ASE.

Getting the reference information you need
Before we start, note that Sybase ASE includes what may well be the best software documentation I've ever seen: a series of PostScript-encoded tutorials and reference books that you'll find in /opt/sybase/docs. While you're reading this Daily Drill Down, you'll find one of these books particularly helpful: Sybase SQL Server Transact-SQL User's Guide. It's an outstanding introduction to Sybase SQL Server. What's more, it's probably the best general introduction to SQL I've ever seen.

You'll also need to consult the Sybase SQL Server Security Administration Guide, which shows you how to add database users and manage permissions, and Sybase SQLServer System Administration Guide, which provides an overview of all system administration procedures. Load up a few reams of paper and print these babies out; you'll be very glad you did.

Do you really need to learn SQL in order to work with Linux database servers? Very few people actually use SQL to perform database queries directly; they use database clients, which are user-friendly programs that enable them to construct queries by filling out an on-screen form. As you'll see, though, much of the administrative and maintenance work you'll do with Sybase ASE requires a working knowledge of SQL, so it's worth spending some time learning the basics.

Understanding Sybase databases
A Sybase database consists of a variety of databaseobjects, including tables (data organized into columns and rows), rules, defaults, stored procedures, triggers that check data input for correctness, views (ways of presenting the data), and a variety of integrity constraints that assure the integrity of the stored data. Some databases are system databases; these databases include
  • master database—Provides storage for user databases.
  • sybsystemprocs database—Contains stored system procedures.
  • model database—A template used to create new databases.

As a Sybase ASE system administrator, you can create user databases and define their structure. In this Daily Drill Down, you'll create a simple user database by creating a table and adding data to it.

Starting the server
To get started, launch the Sybase SQL server by doing the following:
  1. Log on as the sybase user.
  2. Switch to /opt/sybase/install.
  3. Type ./startserver -f ./RUN_SYBASE.
  4. When the server messages are complete, press [Enter] to return to the Linux command prompt.
  5. Start isql by typing isql -Usa -P and pressing [Enter]. You'll see the isql prompt (1>).

Creating a user database
At this point, you'll create your first Sybase ASE database. Please note that this database contains a number of intentional design defects; you'll learn more about these defects later in this Daily Drill Down, when you try to search the database (and encounter problems). In addition, this procedure does not discuss the log on clause, which places transaction logs on a different database device. Omitting the log onclause is acceptable for experimental databases such as the one you'll create here, but you should not omit this clause when you create a production database.

Here's the scenario we'll use. Suppose you're running a yacht brokerage, and you want to keep track of the yachts your brokers have sold. In what follows, you'll create a new database called yachts by using the create database command. In the next section, you'll use the create table command to add a table to this database; subsequently, you'll use the insertcommand to add data to the table.

To create the database with thecreate databasecommand, you may specify many database characteristics. To keep things simple, you'll specify only those characteristics that must be described for the command to succeed. You'll need to use the on clause to specify the database device to be used for the database (you can use default for this purpose). You also need to specify the size of the database, in megabytes; for our purposes here, the minimum size (2 MB) will do nicely. Should you later need to increase the size of a database, you can do so with the alter databasecommand.

In the isql utility, type the following:
1>create database yachts
2>on default =2

After you create the database, you must make it available for use. To do so, type use yachts, press [Enter], type go, and press [Enter].

Creating a table
A Sybase ASE database can contain up to 2 billion tables, but let's keep it simple; for our example, you'll create just one table. When you create the table, you name it and define the table's columns. To define the columns, you name them and specify the datatype. The datatype defines the type of data that can be entered in the column.

Plan your tables before you create them. The following shows our plan for the brokers table you'll create:

Column Name
yacht_name char (25 characters)
yacht_type char (25 characters)
broker_lname char (25 characters)
broker_fname char (15 characters)
asking_price smallmoney  
selling_price smallmoney  
sale_date smalldatetime  

To create the table with the create table command, you specify the name of the table, and then you define the columns. For each column definition, you use the form (column_name datatype); be sure to enclose the entire column definition within parentheses. Also, be sure to place a comma after each line. Ready? Type the following:
1>create table brokers
2>(yacht_name char(25),
3>yacht_type char(25),
6>asking_price smallmoney,
7>selling_price smallmoney,
8>sale_date smalldatetime)

Selected System Datatypes
bit 0 or 1 (used for "yes" or "no" fields).  
char (n) Character (specify width of field with n); maximum 255 characters.
datetime Date and time
decimal Decimal number
double precision Floating point decimal number with double precision
float (precision) Floating point decimal number
int Integer
money Currency values
smalldatetime Date and time from 1/1/1900 to 6/6/2079
smallint Integer from -32,768 to +32,767
smallmoney Currency values from -2,147,483,648 to +2,147,483,657
tinyint Integer from 0 to 255
varchar(n) Character with variable width (specify maximum width of field with n); maximum 255 characters.


Adding data to the table
To add data to the table you just created, use the insertstatement. This statement enables you to add new rows to the table. To do so, you specify the data values in a values clause; you must type the values in the same order in which you defined the columns (yacht_name, yacht_type, broker_lname, broker_fname, asking_price, selling_price, sale_date).

Note: Be sure to type the following exactly. In particular, note that character and date fields require single or double quotation marks but the currency fields do not require quotation. Also, note that commas are used to separate the values, and—contrary to what your English teacher taught you—the quotation marks go inside the commas:
1>insert into brokers
2>values ('Obsession', 'sloop', 'Walker', 'Ted', 75000.00, 72500.00, 'Aug 22 2001 4:43PM')

If you see an error message, check your typing; you probably left out a comma or placed quotation marks incorrectly. Irritating, isn't it? If isql starts getting to you, bear in mind that nobody really enters data this way; there are user-friendly front ends for Sybase ASE, including a nifty KDE utility, and these provide a much more convenient way to enter and edit your data.

Now enter the following. Note that this statement deliberately introduces a couple of typos: slop instead of sloop, and Tad instead of Ted. Please type these mistakes the way they're given here. Later, you'll see why.
1>insert into brokers
2>values ('Carpe Diem', 'slop', 'Walker', 'Tad', 85000.00, 81700.00, 'Aug 24 2001 10:00AM')

Now try a simple select statement to see your data:
1>select * from brokers

If all goes well, you should see two data records (one for Obsession, a sloop sold by Ted Walker, and one for Carpe Diem, some slop sold by Tad Walker).

Why you need more than one table
This database contains only one table—and as you'll see, this is a Bad Thing. Although single-table databases are easy to understand, they have one major drawback: You must type the same data over and over. Inevitably, you'll make data entry errors, such as slop instead of sloop. To see how dangerous this is, suppose you're trying to figure out how many sloops you've sold. You enter the following:
1>select * from brokers
2>where yacht_type = "sloop" and broker_lname = "Walker"
3>order by selling_price

The result? Apparently, Mr. Walker has sold only one sloop, Obsession, for $72,500. The other record isn't retrieved because the yacht_type field contains slop, not sloop.

Slop might actually describe Carpe Diem rather more accurately than the new owner would like, but it's a serious error in this database; it prevents the select command from retrieving information about Carpe Diem's sale. Since this sale isn't retrieved by the search, neither you nor your accountant is aware of it, so Mr. Walker embezzles the $81,700.00 from the sale of Carpe Diem and goes on a six-week gambling spree. I'm exaggerating for dramatic effect, of course, but the point is very important: Single-table databases are too risky to use for any serious purpose. A mistake such as this one could prevent a valued customer from receiving a requested catalog—or an order. Fortunately, there's a cure: It's called normalization, which I’ll discuss in the next section.

Normalizing your database design
Sybase ASE is a relational database program. Put simply, a relational database program can work with two or more tables at once; the data in each of these tables can be joined by means of a common field. To take full advantage of this capability, you should normalize your database design; in plain English, this means that each table defines one and only one type of entity. As you'll see, normalization holds the key to reducing—if not quite eliminating—the bugbear of data redundancy.

Consider these tables:

yt_id type
100 sloop
101 cutter
102 yawl

br_id fname lname
TW Ted Walker
FZ Fran Zellincher

id yt_id br_id asking_price selling_price sale_date
Obsession 100 TW 75000.00 72500.00 Aug 22 2001 4:43PM
Carpe Diem 100 TW  85000.00 81700.00 Aug 24 2001 10:00AM

This design defines three entities: yacht types, broker names, and yacht sales, and each of them gets its own table.

Can you see how this design reduces the need to type the same data twice? Instead of having to type the yacht type (sloop) and broker name (Ted Walker) repeatedly, you supply identifiers (such as 100 and TW) that reference data stored in a separate table.

Normalization doesn't completely eliminate data redundancy, of course. You must still type ID codes more than once. For example, in the yt_id column, 100 means sloop. You could type the code wrong—you could type 1000 instead of 100, for example, or 1x0). There's no such thing as a completely foolproof database.

Still, Sybase ASE provides a number of ways to reduce the chance that data will be entered incorrectly. For example, you can define rulesthat specify what type of data can be entered; you could create a rule for the yt_id field, for instance, that limits entries to three numbers with a minimum value of 100. You can also create triggers, which enforce referentialintegrity. In brief, triggers ensure that users cannot supply values that are not present in the referenced table (that's where the phrase referentialintegrity comes from; with triggers, you know that your data entries are referenced to values that actually exist). Since the only values in yacht_types are 100, 101, or 102, a trigger could limit data entry to just those values (and no others). You can also create views, which automatically display the legal values (and show the plain English field contents, such as sloop or yawl, instead of a numerical ID). Properly designed, a Sybase ASE database restricts your data entry to valid values and sticks the options right in your face.

Please note that there's more to normalization than this brief discussion indicates; the same goes, of course, for everything I’ve discussed here. If you'd like to know more about the underlying theory, the text of choice is C. J. Date's masterly Introduction to Database Systems, 7th ed. (Addison-Wesley, 1999), which is regarded by many to be one of the most brilliant textbooks ever written on any subject—period.

Removing a table
As you've learned, a single-table database is worse than useless, so get rid of the brokers table. To do so, type the following:
1>drop table brokers

In a future Daily Drill Down, you'll learn how to implement the improved, three-table design. You'll also learn how to relate the common fields in the various tables so that you can perform join operationsŸand you'll be well on your way to grasping the essentials of Sybase and SQL.