Xem mẫu
32 CHAPTER 1: DATABASE DESIGN
they should be one table with a column for a sex code. I would have split a table on sex. This is very obvious, but it can also be subtler.
Consider a subscription database that has both organizational and individual subscribers. There are two tables with the same structure and a third table that holds the split attribute, subscription type.
CREATE TABLE OrgSubscriptions (subscr_id INTEGER NOT NULL PRIMARY KEY
REFERENCES SubscriptionTypes(subscr_id), org_name CHAR(35),
last_name CHAR(15), first_name CHAR(15), address1 CHAR(35)NOT NULL,
...);
CREATE TABLE IndSubscriptions (subscr_id INTEGER NOT NULL PRIMARY KEY
REFERENCES SubscriptionTypes(subscr_id), org_name CHAR(35),
last_name CHAR(15), first_name CHAR(15), address1 CHAR(35)NOT NULL,
...);
CREATE TABLE SubscriptionTypes (subscr_id INTEGER NOT NULL PRIMARY KEY,
subscr_type CHAR(1) DEFAULT `I` NOT NULL CHECK (subscr_type IN (`I`, `O`));
An organizational subscription can go to just a person (last_name, first_name), or just the organization name (org_name), or both. If an individual subscription has no particular person, it is sent to an organization called {Current Resident} instead.
The original specifications enforce a condition that subscr_id be universally unique in the schema.
The first step is to replace the three tables with one table for all subscriptions and move the subscription type back into a column of its own, since it is an attribute of a subscription. Next, we need to add constraints to deal with the constraints on each subscription.
1.1 Schema and Table Creation 33
CREATE TABLE Subscriptions
(subscr_id INTEGER NOT NULL PRIMARY KEY REFERENCES SubscriptionTypes(subscr_id),
org_name CHAR(35) DEFAULT `{Current Resident}`, last_name CHAR(15),
first_name CHAR(15),
subscr_type CHAR(1) DEFAULT `I` NOT NULL CHECK (subscr_type IN (`I`, `O`),
CONSTRAINT known_addressee
CHECK (COALESCE (org_name, first_name, last_name) IS NOT NULL);
CONSTRAINT junkmail
CHECK (CASE WHEN subscr_type = `I` AND org_name = `{Current Resident}`
THEN 1
WHEN subscr_type = `O` AND org_name = `{Current Resident}`
THEN 0 ELSE 1 END = 1), address1 CHAR(35)NOT NULL,
...);
The known_addressee constraint means that we have to have a line with some addressee for this to be a valid subscription. The junk mail constraint ensures that anything not aimed at a known person is classified as an individual subscription.
Attribute Split Rows
Consider this table, which directly models a sign-in/sign-out sheet.
CREATE TABLE RegisterBook (emp_name CHAR(35) NOT NULL,
sign_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, sign_action CHAR (3) DEFAULT `IN` NOT NULL
CHECK (sign_action IN (`IN`, `OUT`)), PRIMARY KEY (emp_name, sign_time));
To answer any basic query, you need to use two rows in a self-join to get the sign-in and sign-out pairs for each employee. The correction design would have been:
34 CHAPTER 1: DATABASE DESIGN
CREATE TABLE RegisterBook (emp_name CHAR(35) NOT NULL,
sign_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, sign_out_time TIMESTAMP, -- null means current
PRIMARY KEY (emp_name, sign_in_time));
The single attribute, duration, has to be modeled as two columns in Standard SQL, but it was split into rows identified by a code to tell which end of the duration each one represented. If this were longitude and latitude, you would immediately see the problem and put the two parts of the one attribute (geographical location) in the same row.
1.1.11 Modeling Class Hierarchies in DDL
The classic scenario in an object-oriented (OO) model calls for a root class with all of the common attributes and then specialized subclasses under it. As an example, let’s take the class of Vehicles and find an industry standard identifier (the Vehicle Identification Number, or VIN), and add two mutually exclusive subclasses, sport utility vehicles and sedans (`SUV`, `SED`).
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY, vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN (`SUV`, `SED`)), UNIQUE (vin, vehicle_type),
...);
Notice the overlapping candidate keys. I then use a compound candidate key (vin, vehicle_type) and a constraint in each subclass table to ensure that the vehicle_type is locked and agrees with the Vehicles table. Add some DRI actions and you are done:
CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY, vehicle_type CHAR(3) DEFAULT `SUV` NOT NULL
CHECK(vehicle_type = `SUV`), UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Vehicles(vin, vehicle_type) ON UPDATE CASCADE
ON DELETE CASCADE,
1.1 Schema and Table Creation 35
...);
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY, vehicle_type CHAR(3) DEFAULT `SED` NOT NULL
CHECK(vehicle_type = `SED`), UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Vehicles(vin, vehicle_type) ON UPDATE CASCADE
ON DELETE CASCADE, ...);
I can continue to build a hierarchy like this. For example, if I had a Sedans table that broke down into two-door and four-door sedans, I could build a schema like this:
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY, vehicle_type CHAR(3) DEFAULT `SED` NOT NULL
CHECK(vehicle_type IN (`2DR`, `4DR`, ‘SED`)), UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Vehicles(vin, vehicle_type) ON UPDATE CASCADE
ON DELETE CASCADE, ...);
CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY, vehicle_type CHAR(3) DEFAULT `2DR` NOT NULL
CHECK(vehicle_type = `2DR`), UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Sedans(vin, vehicle_type) ON UPDATE CASCADE
ON DELETE CASCADE, ...);
CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
36 CHAPTER 1: DATABASE DESIGN
vehicle_type CHAR(3) DEFAULT `4DR` NOT NULL CHECK(vehicle_type = `4DR`),
UNIQUE (vin, vehicle_type), FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type) ON UPDATE CASCADE
ON DELETE CASCADE, ...);
The idea is to build a chain of identifiers and types in a UNIQUE() constraint that goes up the tree when you use a REFERENCES constraint. Obviously, you can do variants of this trick to get different class structures.
If an entity doesn’t have to be exclusively one subtype, you play with the root of the class hierarchy:
CREATE TABLE Vehicles (vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL CHECK(vehicle_type IN (`SUV`, `SED`)),
PRIMARY KEY (vin, vehicle_type), ...);
Now, start hiding all this stuff in VIEWs immediately and add an INSTEAD OF trigger to those VIEWs.
1.2 Generating Unique Sequential Numbers for Keys
One common vendor extension is using some method of generating a sequence of integers to use as primary keys. These are very nonrelational extensions that are highly proprietary, and have major disadvantages. They all are based on exposing part of the physical state of the machine during the insertion process, in violation of Dr. E. F. Codd’s rules for defining a relational database (i.e., rule 8, physical data independence). Dr. Codd’s rules are discussed in Chapter 2.
Early SQL products were built on existing file systems. The data was kept in physically contiguous disk pages, in physically contiguous rows, made up of physically contiguous columns, in short, just like a deck of punch cards or a magnetic tape. Most of these sequence generators are an attempt to regain the physical sequence that SQL took out of its
...
- tailieumienphi.vn
nguon tai.lieu . vn