|
|
So here you are, starting to build the catalog. First, you should create a new database for your product catalog. Create a new database named ProductCatalog on your favorite database server using the steps you learned in Chapter 12, "Working with Database Objects."
Let's see again what tables you want to create. Figure 14-6 was created with SQL Server Enterprise Manager.
None of the diagrams presented so far lists the data types (and other properties) for each column. Most of the times the decision of which data type to use will not be hard to make, and you'll deal with this when you create the tables. Still, there's an issue you should consider before starting to construct the tables: How should you generate new IDs for the primary key column(s) when inserting new data records to the tables? Here are the common solutions to this question:
In many cases (as well as in this case study), you rely on the database to automatically generate new IDs values.
Another common solution is to get the maximum value for the ID column and add one unit in order to generate a new unique value. This solution is straightforward, but it's not a recommended one because problems can appear when multiple users try to add new rows at the same time (which could result in a failed attempt to insert two identical values for the primary key).
Generate a new Globally Unique Identifier (GUID) every time. GUIDs are randomly generated values that are guaranteed to be unique across time and space. There are a number of scenarios (such as replication scenarios) where these prove to be helpful.
In some cases, you'll know beforehand the values for the primary key columns, such in the case of the junction table where, for each record, you need to add an existing product ID and an existing category ID (these two values forming the primary key).
Except for the junction table, you'll use autonumbered columns on the primary key columns of your tables.
The Department table has three columns:
DepartmentID (autonumbered)
Name
Description
Remember that there are different ways to implement autonumber functionality into the data table: You use IDENTITY columns for SQL Server, AUTOINCREMENT for Access, AUTO_INCREMENT columns for MySQL, GENERATED AS IDENTITY columns for DB2, and sequences for Oracle. We covered these in the previous chapters. Let's now create the Department table.
With SQL Server, use the following command to create the Department table:
CREATE TABLE Department ( DepartmentID INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL);
This is the similar command for DB2:
CREATE TABLE Department ( DepartmentID INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR (200));
Note that DB2 doesn't let you specify a default value of NULL for the Description field as you've done in the rest of these statements.
Access requires similar syntax:
CREATE TABLE Department ( DepartmentID AUTOINCREMENT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL);
With MySQL, you use the InnoDB table type, which supports FOREIGN KEY constraints (the default table type, MyISAM, doesn't). Because of the way categories relate to departments, you'll need to add a FOREIGN KEY constraint to Category that will reference the DepartmentID column in Department. Even if only the Category column has a FOREIGN KEY constraint defined, both tables that take part in the relationship need to be InnoDB tables. You'll see the other end of this relationship when you create the Category table. Here's the code for creating the Department table in MySQL:
CREATE TABLE Department ( DepartmentID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL) Type=InnoDB;
Because Oracle doesn't have an out-of-the-box way to automatically generate unique numbers like the other platforms, you can't specify that DepartmentID is an autonumbered column when creating it:
CREATE TABLE Department ( DepartmentID INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(200) NULL);
Instead, you create a sequence and trigger that implements the autonumber functionality:
CREATE SEQUENCE DepartmentIDSeq; CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger BEFORE INSERT ON Department FOR EACH ROW BEGIN SELECT DepartmentIDSeq.NEXTVAL INTO :NEW.DepartmentID FROM DUAL; END; /
After creating the sequence and trigger, you'll be able to add new rows the same way as with MySQL and SQL Server.
The Category table is similar to Department except that it has an additional column—DepartmentID—that references the DepartmentID column of the Department table.
Here's how you create this table with SQL Server:
CREATE TABLE Category ( CategoryID INT IDENTITY NOT NULL PRIMARY KEY, DepartmentID INT NOT NULL FOREIGN KEY REFERENCES DEPARTMENT, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL);
This is the command in Access:
CREATE TABLE Category (
CategoryID AUTOINCREMENT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Description VARCHAR (200) NULL,
CONSTRAINT fk_DepartmentID FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID));
Notice that Access doesn't allow you to use the shorthand constraint syntax that you used for SQL Server.
This is the command that creates the table with DB2:
CREATE TABLE Category ( CategoryID INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, DepartmentID INT NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR (200), FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));
With MySQL, when adding FOREIGN KEY constraints, apart from using the InnoDB table type and defining the FOREIGN KEY constraint, you also need to create an index on the FOREIGN KEY column:
CREATE TABLE Category ( CategoryID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, DepartmentID INT NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL, FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID), INDEX idxDepartmentID (DepartmentID) ) Type=InnoDB;
| Caution |
If you forget to specify InnoDB for Category or Department, no errors will be generated by MySQL when adding the foreign key. Instead, the FOREIGN KEY constraint will simply be ignored. |
This is how you create the table on Oracle:
CREATE TABLE Category ( CategoryID INT NOT NULL PRIMARY KEY, DepartmentID INT NOT NULL, Name VARCHAR(50) NOT NULL, Description VARCHAR (200) NULL, FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));
After creating the Category table with Oracle, you implement the autonumbering functionality using the same method as before:
CREATE SEQUENCE CategoryIDSeq; CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger BEFORE INSERT ON Category FOR EACH ROW BEGIN SELECT CategoryIDSeq.NEXTVAL INTO :NEW.CategoryID FROM DUAL; END; /
With Product, you have a few more values to store than with Department or Category.
You have the OnCatalogPromotion and OnDepartmentPromotion columns, which hold binary values, and you have a Price column, which needs to store monetary values.
With SQL Server, for OnCatalogPromotion and OnDepartmentPromotion you use the BIT data type, which stores zero or one. This is just fine for what you need. Also, you have a specialized MONEY data type that stores monetary data with fixed precision:
CREATE TABLE Product ( ProductID INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(1000) NOT NULL, Price MONEY NULL, ImagePath VARCHAR(50) NULL, OnCatalogPromotion BIT NULL, OnDepartmentPromotion BIT NULL);
Access uses similar syntax:
CREATE TABLE Product ( ProductID AUTOINCREMENT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(255) NOT NULL, Price MONEY NULL, ImagePath VARCHAR(50) NULL, OnCatalogPromotion BIT NULL, OnDepartmentPromotion BIT NULL);
Notice that the maximum field length for a VARCHAR in Access is 255 characters.
The command you use for DB2 is slightly different here because DB2 doesn't have a MONEY or BIT data type, so you use DECIMAL for the price and SMALLINT for the two Boolean values:
CREATE TABLE Product ( ProductID INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(1000) NOT NULL, Price DECIMAL(7,2), ImagePath VARCHAR(50), OnCatalogPromotion SMALLINT, OnDepartmentPromotion SMALLINT);
With MySQL you record monetary data using the DECIMAL data type. You record the description as a VARCHAR(255) instead of VARCHAR(1000) because MySQL doesn't support higher dimensions. The alternative would be to use BLOB (binary large object) fields, which are usually best avoided because of the impact they have on performance:
CREATE TABLE Product ( ProductID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(255) NOT NULL, Price DECIMAL NULL, ImagePath VARCHAR(50) NULL, OnCatalogPromotion BIT NULL, OnDepartmentPromotion BIT NULL) Type=InnoDB;
Oracle has the universal NUMBER data type, which you'll use for Price, OnCatalogPromotion, and OnDepartmentPromotion columns. For the binary values, you specify the minimum size for number length and number of decimal places to ensure you occupy the smallest space possible:
CREATE TABLE Product ( ProductID INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Description VARCHAR(1000) NOT NULL, Price NUMBER NULL, ImagePath VARCHAR(50) NULL, OnCatalogPromotion NUMBER(1,0) NULL, OnDepartmentPromotion NUMBER(1,0) NULL);
Once again, you create the trigger and sequence:
CREATE SEQUENCE ProductIDSeq; CREATE OR REPLACE TRIGGER ProductAutonumberTrigger BEFORE INSERT ON Product FOR EACH ROW BEGIN SELECT ProductIDSeq.NEXTVAL INTO :NEW.ProductID FROM DUAL; END; /
With ProductCategory, the new challenge is to create the multivalued primary key. You also have two foreign keys that reference the primary keys of the Category and Product tables.
With SQL Server, Access, Oracle, and DB2, here's how you create the ProductCategory table. Note that here you're using the shorter syntax, which doesn't give explicit names to the PRIMARY KEY and FOREIGN KEY constraints:
CREATE TABLE ProductCategory ( ProductID INT NOT NULL, CategoryID INT NOT NULL, PRIMARY KEY (ProductID, CategoryID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID) );
Alternatively, with SQL Server you can create the foreign keys with the simpler syntax:
CREATE TABLE ProductCategory ( ProductID INT NOT NULL FOREIGN KEY REFERENCES PRODUCT, CategoryID INT NOT NULL FOREIGN KEY REFERENCES CATEGORY, PRIMARY KEY (ProductID, CategoryID) );
With MySQL, you need to create indexes on the columns used in foreign keys and make sure the table type is InnoDB:
CREATE TABLE ProductCategory ( ProductID INT NOT NULL, CategoryID INT NOT NULL, PRIMARY KEY (ProductID, CategoryID), FOREIGN KEY (ProductID) REFERENCES Product (ProductID), INDEX idxProductID (ProductID), FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID), INDEX idxCategoryID (CategoryID) ) Type=InnoDB;
|
|