Newer
Older
labs / tiddlers / content / labs / lab03 / _Labs_03_Task _ Implementing the ERD.md

Now that you have details for the main entities, you can begin writing SQL CREATE TABLE statements, but first, fill out the relevant parts of the worksheet supplied in the Lab.

Creating a Table

Each entity type in the ERD will become a table in the database. Attributes will become columns. Each column definition within the CREATE TABLE statement begins with the column name and data type. In SQL, lists (such as the columns within a table) are written in parentheses, with commas separating the items, e.g. “(a, b, c)”, so don’t forget the comma after each column (except for the last one).
Here’s an example of a (very minimal!) CREATE TABLE statement that you can use as a guide for writing your own:

create table Employee
(
    Employee_ID    varchar2(6),
    First_Name    varchar2(20) not null,
    Last_Name    varchar2(25) not null,
    Hire_Date    date default sysdate,
    Salary        number(8,2),
    Commission_Percent    number(2,2) default 0
);

This example has deliberately left out any primary and foreign key constraints, as well as additional constraints implementing business rules such as the salary being greater than 0. We will look at these shortly.

Table and Column Names

Generally, you should base your SQL names (identifiers) on the specification provided by the ERD. However, SQL identifiers cannot normally contain spaces, which are usually replaced by the underscore character, “_”. There are a few other prohibited characters, and they cannot start with a digit. Identifiers should be descriptive but not too long.

Identifiers must also be unique, of course (within the scope of the table, for column names, or across the entire database for table and constraint names).

SQL Developer will apply syntax-highlighting to keywords, but note that not all keywords are reserved words; that is, just because an identifier is highlighted does not necessarily mean you can’t use it as an identifier. SAMPLE is an example of this.

Column Data Types

When defining a column, you must choose a suitable data type. Your main choices are:

• CHAR for fixed-length character strings (default length = 1), e.g. CHAR(3).

• VARCHAR for variable length strings (size limit required), e.g. VARCHAR(50). H2 will only store the characters needed, so be generous with the size limit.

• NUMERIC (or equivalently DECIMAL, or NUMBER in H2) for integers and decimal numbers. Specify a precision and scale to control the number of significant figures and decimal places respectively, for example NUMERIC(2) for integers up to 99, or NUMERIC(5,2) for decimal numbers up to 999.99.

• DATE or TIMESTAMP for date/time data (with optional time zone). These do not require a size.

• INTERVAL for relative time (durations), such as for lap times in a race, or the playing time of music tracks.

• BLOB or CLOB for binary or character large objects (such as PDFs, video, and Web pages).

For comprehensive information on the data types provided by SQL, as well as further useful information about expressing and comparing values, consult the documentation:

Link required here - maybe w3 ??

Questions

What criteria would you use to choose a data type for a column?

Why might it be unwise to use a numeric type for storing phone numbers?