| The best way to understand data type selection is to design a database and implement the design using DB2. We will create a database that can be used to schedule and track the results of a certification program. This database will be used to illustrate many aspects of SQL and features of DB2. You have already seen many examples from this database throughout this chapter. This database will be used to schedule test candidates' exams, and following the completion of the test, it will contain the candidates' test scores. The database and its application will need to perform the following tasks : -
Insert/update/delete testing center information. -
Insert/update/delete test information. -
Insert/update/delete test candidate information. -
Guarantee a uniquely identified test name , regardless of the test number. -
Schedule a candidate to take a test. -
Update candidate test scores once exams have been completed. -
Determine which candidates qualify for certification. -
Generate various reports on the candidates and tests. The database will be named DB2CERT. The data to be stored in the DB2CERT database can easily be grouped into three reference tables, and a fourth table is used to relate the other tables. The primary relationship can be defined as a test candidate takes a specific test at a test cente r. Figure 2-4 shows the relationships within the problem domain. The rectangles represent the base tables: CANDIDATE, TEST_CENTER, and TEST. The fourth table is a relationship table called TEST_TAKEN. Figure 2-4. Tables for the DB2CERT database.  DB2CERT Database Table Descriptions The following is a list of tables that will be used in the DB2CERT database: -
The CANDIDATE table stores information about each test candidate for the DB2 Certification Program. Data such as candidate name, address, candidate ID, and phone number will be stored in this table. A data record represents a single test candidate (person). -
The TEST_CENTER table stores information about the test centers where a candidate can take a DB2 Certification exam. Data such as the test center name, address, number of seats at the test center, test center ID, and its phone number will be stored in this table. A data record represents a single test center location. -
The TEST table stores information about each of the DB2 Certification exams. Data such as the test name, type, test ID, cut score (passing percentage), and the length of each test will be stored in this table. A data record represents a single test. For our example, there are three tests in the DB2 Certification Program; therefore, there are only three data records in this table. A test name must be uniquely identified. -
The TEST_TAKEN table associates the records from the other three tables. It serves the dual purpose of scheduling tests and tracking each test result. Data such as the candidates' test scores, date taken, start time, and seat number will be stored in this table. This will be the most active of the four tables, since multiple exams must be taken by each candidate to become certified, and each test taken will have a corresponding data record in this table. Once you have defined the tables and their relationships, the following should be defined: -
User -defined data types -
Columns or attributes for the tables -
(Optional) primary keys (PK) for the tables -
(Optional) unique keys for the tables -
(Optional) foreign keys (referential constraints) for the tables -
(Optional) table-check constraints for the tables -
(Optional) triggers for the database In Figure 2-5, the database design is shown. The rectangles represent the entities or tables. The columns or attributes are shown as ellipses. Note that some of the columns are derived columns. A derived column is a column that represents a concept and not a physical attribute of an object. The derived columns are included in the model because their values will be populated by the database using a constraint mechanism. Figure 2-5. DB2 Certification database entity relationship diagram.  We must map the attributes shown in Figure 2-5 to DB2 as supported data types or user-defined data types. To demonstrate some of the powerful features of DB2, we have decided to create distinct types for many of the attributes. It is beneficial to have a primary key defined for each table, since this will ensure uniqueness of the data records. The attributes that are underlined will be used as primary keys. We will also create unique keys to illustrate their use. Unique key attributes are double underlined . In the previous section, we mentioned that there will be four tables in the DB2CERT database. However, the design shown in Figure 2-5 has only three tables defined: They are shown as rectangles. There is an implied table defined in the relationship candidate takes a test. A table is required to store each occurrence of a candidate taking a certification test. We will impose a restriction on the candidates: They can take the test only once on any given day. A candidate can take different tests on the same day, but not the same test. With this restriction in place, we will define a primary key for the TEST_TAKEN table as a composite key including NUMBER (test ID), CID (candidate ID), and DATE_TAKEN (the date of the test). By defining the primary key as a combination of these three values, we can enforce this constraint. Figures 2-5 and 2-6 show the entity relationship diagrams for the Certification database. Figure 2-6. DB2 Certification database entity basic relationship diagram.  Figure 2.7 shows the RI relationships between the tables in the Certification database. Figure 2-7. DB2 Certification database referential integrity.  The diamond shapes are used to describe the relationships between the tables, including the parentchild relationship. For example, there is a one-to-many relationship between the CANDIDATE and the TEST_TAKEN table because a single candidate can take many tests. This relationship is shown by denoting the values of 1 and N (many) on the appropriate side of the diamond. The database design shown in Figure 2-5 is just one type of diagramming technique. A logical database design can be represented in a number of ways, but it should not dictate the physical implementation of the database. We have included it here because it will be used in many of the SQL statements throughout the rest of the book. Defining User-Defined Data Types If you wish to utilize user-defined data types, they must exist in your database before they can be referenced in a CREATE TABLE statement. We have decided to create a number of user-defined data types, as shown in the following example. The CANDIDATE_ID, TEST_ID, CENTER_ID, and PHONE data types are all based on the fixed-length character (CHAR) data type. These attributes were chosen to be user-defined because they have meaning in their structure, and they should not be used in expressions with other character data types. For example, a telephone number data type could be defined and then a user-defined function could be created to extract the area code. The function would be used only for phone data types. We also decided to create some numeric user-defined data types, including the data types score and minutes. CREATE DISTINCT TYPE candidate_id AS CHAR(8) WITH COMPARISONS CREATE DISTINCT TYPE test_id AS CHAR(6) WITH COMPARISONS CREATE DISTINCT TYPE center_id AS CHAR(4) WITH COMPARISONS CREATE DISTINCT TYPE phone AS CHAR(10) WITH COMPARISONS CREATE DISTINCT TYPE score AS DECIMAL(6,2) WITH COMPARISONS CREATE DISTINCT TYPE minutes AS SMALLINT WITH COMPARISONS Defining Tables and Columns Designing a database involves many considerations. We will examine only some of these considerations in this book. If we use the database design in Figure 2-5 as a starting point, we can start creating database objects. The first step in creating a database is to issue the run a CREATE DATABASE statement, such as the one below. CREATE DATABASE CERTTS STOGROUP CERTSTG BUFFERPOOL BP7 INDEXBP BP8; Once the database has been created, we can start creating objects. We can create distinct (user-defined) data types as we did previously. Then we can start creating tables (if explicit tablespaces are to be used, they will need to be created first, but for this example we will assume they are already created. Refer to Appendix C, "DB2CERT Database DDL," for more of the tablespace create methods ). The database design has a number of attributes shown. Each of these attributes will be a column in the table definitions. Every DB2 table contains one or more columns. The tables and their corresponding columns are given names . In the previous sections, we discussed all of the data types that can be used for column definitions. Data is placed in a DB2 table using the SQL statement INSERT or UPDATE. (The LOAD utility is also an option.) Usually, it is desirable for each column, or data value, to have a value. Sometimes, there is no value provided for a column during the INSERT statement. If the column is defined as NOT NULL, the INSERT statement will fail. If a default value is defined, it will be stored. The table being created in the following DDL is called DB2CERT.CANDIDATE, and it contains 14 columns. Each column is given a name and a data type. There are two user-defined data types being used in the DB2CERT.CANDIDATE table. These data types are CANDIDATE_ID and PHONE There are also constraints defined for the valid values for some of the columns. For example, the null constraint is specified for all of the columns except HPHONE, WPHONE, and INITIAL. CREATE TABLE db2cert.candidate (Cid candidate_id NOT NULL, LName VARCHAR(30) NOT NULL, FName VARCHAR(30) NOT NULL, Initial CHAR(1), HPhone phone, WPhone phone, StreetNo VARCHAR(8) NOT NULL, StreetName VARCHAR(20) NOT NULL, City VARCHAR(30) NOT NULL, Prov_State VARCHAR(30) NOT NULL, Code CHAR(6) NOT NULL, Country VARCHAR(20)) NOT NULL, Cert_DBA CHAR(1) NOT NULL WITH DEFAULT 'N', Cert_APP CHAR(1) NOT NULL WITH DEFAULT 'N', CONSTRAINT Unique_Candidate PRIMARY KEY (Cid)) IN DB2CERT.CERTTS; Keys Keys are a special set of columns defined on a table. They can be used to uniquely identify a row or to reference a uniquely identified row from another table. Keys can be classified either by the columns they are composed of or by the database constraint they support. Depending on how many columns are used to define a key, you can have one of the following types: The following are types of keys used to implement constraints: -
A unique key is used to implement unique constraints. A unique constraint does not allow two different rows to have the same values in the key columns. -
A primary key is used to implement entity integrity constraints. A primary key is a special type of unique key. There can be only one primary key per table. The primary key column must be defined with the NOT NULL option. -
A foreign key is used to implement RI constraints. Referential constraints can reference only a primary key or unique key. The values of a foreign key can have values defined only in the primary key or unique key they are referencing or the null value. Defining Primary Keys It is sometimes beneficial to define a primary key for each of your DB2 tables, since this will guarantee the uniqueness of a column value or group of column values (composite key). In the previous CREATE statement, the primary key for the table candidate is defined as the column CID (candidate ID). By specifying this column as a primary key, DB2 will create a system-unique index if one does not already exist. Let us look at the other tables representing the tests and the test centers. In the following CREATE statements, the TEST and TEST_CENTER tables are shown. These tables each have a primary key defined. In our example, the primary key constraint was given a name (UNIQUE_TEST and UNIQUE_CENTER) for referencing purposes. CREATE TABLE db2cert.test (Number test_id NOT NULL, Name VARCHAR(50) NOT NULL, Type CHAR(1) NOT NULL, Cut_Score score NOT NULL, Length minutes NOT NULL, TotalTaken SMALLINT NOT NULL, TotalPassed SMALLINT NOT NULL, CONSTRAINT Unique_Test PRIMARY KEY(Number), CONSTRAINT Unique_Test_Name UNIQUE (Name) CONSTRAINT Test_Type CHECK (Type IN ('P','B'))) IN DB2CERT.CERTTS CREATE TABLE db2cert.test_center (Tcid center_id NOT NULL, Name VARCHAR(40) NOT NULL, StreetNo VARCHAR(8) NOT NULL, StreetName VARCHAR(20) NOT NULL, City VARCHAR(30) NOT NULL, Prov_State VARCHAR(30) NOT NULL, Country VARCHAR(20) NOT NULL, Code CHAR(6) NOT NULL, Type CHAR(1) NOT NULL, Phone phone NOT NULL, No_Seats SMALLINT NOT NULL, CONSTRAINT Unique_Center PRIMARY KEY (Tcid)) IN DB2CERT.CERTTS Defining Unique Keys Unique keys can be used to enforce uniqueness on a set of columns. A table can have more than one unique key (index) defined. The TEST table definition uses a unique constraint (UNIQUE_TEST_NAME) on column NAME to ensure that a test name is not used twice. There is also a primary key constraint on the column NUMBER to avoid duplicate test numbers . Having unique constraints on more than one set of columns of a table is different than defining a composite unique key that includes the whole set of columns. For example, if we define a composite primary key on the columns NUMBER and NAME, there is still a chance that a test name will be duplicated using a different test number. NOTE  | A unique index needs to be always created for primary (if one does not already exist) or unique key constraints (unless using the schema processor). If you define a constraint name, it will be used to name the index; otherwise , a system-generated name will be used for the index. |
Defining Foreign Keys A foreign key is a reference to the data values in another table. There are different types of foreign key constraints. Let us look at the remaining table in the DB2 Certification database and, in particular, its foreign key constraints. We have one composite primary key defined and three foreign key constraints. The primary key is defined as the columns CID, TCID, and NUMBER. The foreign key constraints will perform the following: -
If a record in the candidate table is deleted, all matching records in the TEST_TAKEN table will be deleted (DELETE CASCADE). -
If a test center in the TEST_CENTER table is deleted, all of the matching records in the TEST_TAKEN table will be deleted (DELETE CASCADE). -
If a test in the TEST table is deleted and there are matching records in the TEST_TAKEN table, the DELETE statement will result in an error (DELETE RESTRICT). NOTE | | A foreign key constraint always relates to the primary or unique constraint of the table in the references clause. |
CREATE TABLE db2cert.test_taken (Cid candidate_id NOT NULL, Tcid center_id NOT NULL, Number test_id NOT NULL, Date_Taken date NOT NULL WITH DEFAULT, Start_Time TIME NOT NULL, Finish_Time TIME NOT NULL, Score score, Seat_No CHAR(2) NOT NULL, CONSTRAINT number_const PRIMARY KEY (Cid,Tcid,Number), FOREIGN KEY (Cid) REFERENCES db2cert.candidate ON DELETE CASCADE, FOREIGN KEY (Tcid) REFERENCES db2cert.test_center ON DELETE CASCADE, FOREIGN KEY (Number) REFERENCES db2cert.test ON DELETE RESTRICT) IN DB2CERT.CERTTS Defining parentchild relationships between tables is known as declarative referential integrity because the child table refers to the parent table. These constraints are defined during table creation or by using the ALTER TABLE SQL statement. DB2 will enforce referential constraints for all INSERT, UPDATE, and DELETE activity. NOTE | | This database implementation created only the tables and UDTs. The complete DDL for the storage group, databases, tablespaces, tables, and indexes can be found in Appendix C. |
|
