Data Definition Basics in Sybase

Implementing Database in Sybase – Part 4

Division 4

Forward: In this part of the series, we look at the basics of SQL Anywhere data definition; SQL Anywhere is a Sybase package.

By: Chrysanthus Date Published: 26 Aug 2012

Introduction

This is part 4 of my series, Implementing Database in Sybase. This series is part of the fourth division of my larger series, Database. I assume you have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at the basics of SQL Anywhere data definition; SQL Anywhere is a Sybase package.

By data definition here, I am referring to creating of tables, altering tables, dropping tables, creating of indexes and giving different users, rights to different tables. All that should be done for a database.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Example
We shall continue with the wholesale example. The tables for the wholesale example are:

Customers(CustomerID, Name, Address, City, State, Country, Phone)
Sales(SaleID, DateAndTime, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, SoldPrice)
Products(ProductID, ProductName, Category, Number, CostPrice, SellingPrice)
Orders(OrderID, DateAndTime, EmployeeID, SupplierID)
OrderDetails(OrderID, ProductID, BoughtPrice, Discount)
Suppliers(SupplierID, Name, Address, City, State, Country, Phone)
Employee(EmployeeID, Name, Address, City, State, Country, Phone, Manager)

These are the tables we derived in division 2 of the big series. The logical relationships are as follows:
- One-to-many relationship between the Sales table and the Customers table, through the SaleID column (the same customer can come for different sales).
- One-to-many relationship between the Sales table and the SaleDetails table, through the SaleID column.
- One-to-many relationship between the Products table and the SaleDetails table, through the ProductID column.
- One-to-many relationship between the Products table and the OrderDetails table, through the ProductID column.
- One-to-many relationship between the Suppliers table and the Orders table, through the SupplierID column (the same supplier can supply for different orders).
- One-to-many relationship between the Employee table and the Sales table through the EmployeeID column.
- One-to-many relationship between the Employee table and the Orders table through the EmployeeID column.
- A reflexive relationship within the Employee table, where one manager supervises many employees.

The advice for rules on unique index are as follows:

- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column.
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.

Some Good News
SQL Anywhere 12 understands the advice for indexes and it gives indexes on our behalf to a great extent. If in SQL Anywhere 12, you declare a column or set of columns as a primary key, the row value for the column or set of columns will automatically be unique; that is non-unique values will not be allowed. When you enforce referential integrity, indexes are created automatically for the keys concerned. So, for our example (project) the only index rules left that we should think about are:

- Index frequently searched columns.
- Index columns that frequently determine the sort order.

The Key column(s) of a table is frequently searched. So, the only rule we should really worry about is this second one mentioned above. An example of such a column that frequently determines the search order is the name column of the employee table.

Creating the Tables
For the rest of this tutorial, I will give the SQL statements to create each of the tables of the example, and explain each statement. In the next part of the series, I will explain the syntax of the CREATE TABLE SQL statement and then you will create the tables practically.

Note: When creating tables in Interactive SQL, you should create the tables, which do not have foreign keys first.

The Customers Table
The Customers table notation is:

    Customers(CustomerID, Name, Address, City, State, Country, Phone)

The SQL statement to create the Customers table is:

CREATE TABLE Customers
(
    CustomerID SMALLINT DEFAULT AUTOINCREMENT,
    Name CHAR(40) INDEX,
    Address CHAR(60),
    City CHAR(30),
    State CHAR(30),
    Country CHAR(40),
    Phone CHAR(12),

    PRIMARY KEY (CustomerID ASC)
);

You have the reserved words, CREATE TABLE; then you have the table name of your choice. Next you have parentheses. Inside the table, you have two sections. The top section deals with the individual columns. The lower section deals with the whole table. In other words the lower section deals with the table constraints.

The first column is the CustomerID column. Its data type is SMALLINT. Its default value is AUTOINCREMENT; this means that as you go down the column the values would be 1, 2, 3, 4, 5, etc. For the rest of the columns, you have the column name and the data type CHAR. The numbers in the parentheses give the maximum number of characters that each cell in the column should take. The very maximum of the CHAR type is 32767 bytes (characters), but we would never have that for this table. The column descriptions are separated by commas (end in commas).

You would normally want to see (retrieve) the Customers table, based on the names (alphabetical listing) of the customers. So the name column of the Customers table is a column that frequently determines the sort order, when listing the rows of the Customers table. And so the name column is indexed; note how this is done in the SQL statement (any SQL statement ends in semicolon).

For the table constraint, the primary key for the table is indicated. If you have more than one column for the primary key, you type their names separated by commas in the parentheses. Above, we have just one column name in the parentheses. ASC means that the values in the column increases as you go down the table, numerically, alphabetically or both. The lines in the table constraint section should be separated by commas. Above, we have just one line, and so there is no need for commas.

In the table creation SQL statement, CREATE, TABLE, SMALLINT, DEFAULT, CHAR, AUTOINCREMENT, PRIMARY and KEY are reserved words. Reserved words are words that have special meaning to the syntax and you should not use them arbitrarily (in any way). In the Interactive SQL server, these words may appear in different colors.

Note: Each SQL statement ends with a semicolon.

The Products Table
The Products table notation is:

    Products(ProductID, ProductName, Category, Number, CostPrice, SellingPrice)

The SQL statement to create the Products table is:

CREATE TABLE Products
(
    ProductID SMALLINT DEFAULT AUTOINCREMENT,
    ProductName CHAR(40) INDEX,
    Category CHAR(30) INDEX,
    NUMBER INTEGER,
    CostPrice NUMERIC(19,2),
    SellingPrice NUMERIC(19,2),

    PRIMARY KEY (ProductID ASC)
);

The explanation is similar to that of the previous table. Here, the ProductName and the Category columns are indexed (columns that frequently determine sort order). I have used NUMERIC(19,2) for the MONEY data type. This means you can have up to 19 digits for all the number and 2 decimal digits. The primary key does not have to be AUTOINCREMENT (autonumber). The company may have their own way of giving values for the primary key. Sybase may have a function (code) for producing such values. With AUTOINCREMENT, the number increases by 1 generally as you go down the column. Sybase SQL Anywhere 12 has a way of producing higher incrementing values than 1; however, I will not go into that in this series. See the manual or some other document for that.

The Suppliers Table
The Suppliers table notation is,

    Suppliers(SupplierID, Name, Address, City, State, Country, Phone)

The SQL statement to create the Suppliers table is:

CREATE TABLE Suppliers
(
    SupplierID SMALLINT DEFAULT AUTOINCREMENT,
    Name CHAR(40) INDEX,
    Address CHAR(60),
    City CHAR(30),
    State CHAR(30),
    Country CHAR(40),
    Phone CHAR(12),

    PRIMARY KEY (SupplierID ASC)
);

The explanation is similar to that of the Customers table.

The Employee Table
The Employee table notation is,

    Employee(EmployeeID, Name, Address, City, State, Country, Phone, Manager)

The SQL statement to create the Employee table is:

CREATE TABLE Employee
(
    EmployeeID SMALLINT DEFAULT AUTOINCREMENT,
    Name CHAR(40) INDEX,
    Address CHAR(60),
    City CHAR(30),
    State CHAR(30),
    Country CHAR(40),
    Phone CHAR(12),
    Manager SMALLINT,

    PRIMARY KEY (EmployeeID ASC)
);

The explanation is similar to that of the Customers table.

The Sales Table
The Sales table notation is,

    Sales(SaleID, DateAndTime, EmployeeID, CustomerID)

The SQL statement to create the Sales table is:

CREATE TABLE Sales
(
    SaleID SMALLINT DEFAULT AUTOINCREMENT,
    DateAndTime TIMESTAMP DEFAULT TIMESTAMP,
    EmployeeID SMALLINT,
    CustomerID SMALLINT,

    PRIMARY KEY (SaleID ASC),
    FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ON UPDATE CASCADE ON DELETE CASCADE
);

Creation of tables is similar. However, there are certain things to note here. For the DateAndTime column, the data type is timestamp (calendar date and time of day when action took place – when row was added). DEFAULT means the DBMS should give a value automatically for you. The Default value it should give is timestamp. That is why you have TIMSTAMP in two places in the line. There are two independent foreign keys here, which are the EmployeeID column and the CustomerID column.

In the table constraint section of the SQL statement, there are two foreign key lines. The phrase “ON UPDATE CASCADE ON DELETE CASCADE” has been added to each of the lines. It says “on update cascade” and “on delete cascade”. Each of the foreign key lines in the SQL statement is complete; that is how referential integrity is enforced.

There is a one-to-many relationship between the EmployeeID column of the Sales table and the primary key column of the Employee table. There is also a one-to-many relationship between the CustomerID column of the Sales table and the primary key column of the Customer table. Each foreign key references a primary key in another table. Use commas to separate the lines in the table constraint section of the SQL statement; the very last line does not need a comma.

The SaleDetails Table
The SaleDetails table notation is,

    SaleDetails(SaleID, ProductID, SoldPrice)

The SQL statement to create the SalesDetails table is:

CREATE TABLE SaleDetails
(
    SaleID SMALLINT,
    ProductID SMALLINT,
    SoldPrice NUMERIC(19,2),

    PRIMARY KEY (SaleID, ProductID),
    FOREIGN KEY (SaleID) REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE,
);

Points to note: There is a composite (two columns) primary key here. The primary key line in the table constraint column section in the SQL statement takes care of this with the two column names (separated by comma) in its parentheses.

Now even though SaleID is part of the primary key, it behaves like a foreign key here, so far as the Sales table is concerned. So a line in the table constraint section in the SQL statement must be there to that effect; note the line.

The Orders Table
The Orders table notation is,

    Orders(OrderID, DateAndTime, EmployeeID, SupplierID)

The SQL statement to create the Orders table is:

CREATE TABLE Orders
(
    OrderID SMALLINT DEFAULT AUTOINCREMENT,
    DateAndTime TIMESTAMP DEFAULT TIMESTAMP,
    EmployeeID SMALLINT,
    SupplierID SMALLINT,

    PRIMARY KEY (OrderID ASC),
    FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID) ON UPDATE CASCADE ON DELETE CASCADE
);

The Orders table can be explained in a similar way to the Sales table, but here, you are dealing with Orders (and suppliers) and not Sales (and customers).

The OrderDetails Table
The OrderDetails table notation is,

    OrderDetails(OrderID, ProductID, BoughtPrice, Discount)

The SQL statement to create the OrderDetails table is:

CREATE TABLE OrderDetails
(
    OrderID SMALLINT,
    ProductID SMALLINT,
    BoughtPrice NUMERIC(19,2),
    Discount REAL,

    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ON UPDATE CASCADE ON DELETE CASCADE,
);

The OrderDetails table can be explained in a similar way to the SaleDetails table, but here you are dealing with orders and not sales. The data type I have given for the Discount column is REAL. This value is in percentage, (the percentage sign is not indicated).

I have explained the SQL statements for the 8 tables. These statements do not have any error. When any of these tables is implemented successfully using the Interactive SQL widows tool, the table is automatically saved in the wholesale.db database file. There is no save command to save the table.

Well, let us end there for this tutorial. I hope you are appreciated the implementation of database (design). We continue in the next part.

Chrys

Broad Network

Related Articles

Data Definition Basics in Sybase

Implementing Database in Sybase – Part 4

Division 4

Introduction

Related Courses

Comments