Xem mẫu
22 CHAPTER 1: DATABASE DESIGN
CREATE TABLE Bar
(bar_key INTEGER NOT NULL PRIMARY KEY, other_key INTEGER NOT NULL UNIQUE, ...);
1.1.6 Overlapping Keys
But let’s get back to the nested keys. Just how far can we go with them? My favorite example is a teacher’s schedule kept in a table like this [I am leaving out reference clauses and CHECK() constraints]:
CREATE TABLE Schedule (teacher_name VARCHAR(15) NOT NULL,
class_name CHAR(15) NOT NULL, room_nbr INTEGER NOT NULL, period INTEGER NOT NULL,
PRIMARY KEY (teacher_name, class_name, room_nbr, period));
That choice of a primary key is the most obvious one, use all the columns. Typical rows would look like this:
(`Mr. Celko`, `Database 101`, 222, 6)
The rules we want to enforce are:
1. A teacher is in only one room each period. 2. A teacher teaches only one class each period. 3. A room has only one class each period.
4. A room has only one teacher in it each period.
Stop reading and see what you come up with for an answer. Okay, now consider using one constraint for each rule in the list, thus.
CREATE TABLE Schedule_1 -- version one, WRONG! (teacher_name VARCHAR(15) NOT NULL,
class_name CHAR(15) NOT NULL, room_nbr INTEGER NOT NULL, period INTEGER NOT NULL,
UNIQUE (teacher_name, room_nbr, period), -- rule #1 UNIQUE (teacher_name, class_name, period), -- rule #2
1.1 Schema and Table Creation 23
UNIQUE (class_name, room_nbr, period), -- rule #3 UNIQUE (teacher_name, room_nbr, period), -- rule #4
PRIMARY KEY (teacher_name, class_name, room_nbr, period));
We know that there are four ways to pick three things from a set of four things. While column order is important in creating an index, we can ignore it for now and then worry about index tuning later.
I could drop the PRIMARY KEY as redundant if I have all four of these constraints in place. But what happens if I drop the PRIMARY KEY and then one of the constraints?
CREATE TABLE Schedule_2 -- still wrong (teacher_name VARCHAR(15) NOT NULL,
class_name CHAR(15) NOT NULL, room_nbr INTEGER NOT NULL, period INTEGER NOT NULL,
UNIQUE (teacher_name, room_nbr, period), -- rule #1 UNIQUE (teacher_name, class_name, period), -- rule #2 UNIQUE (class_name, room_nbr, period)); -- rule #3
I can now insert these rows in the second version of the table:
(`Mr. Celko`, `Database 101`, 222, 6) (`Mr. Celko`, `Database 102`, 223, 6)
This gives me a very tough sixth-period teaching load, because I have to be in two different rooms at the same time. Things can get even worse when another teacher is added to the schedule:
(`Mr. Celko`, `Database 101`, 222, 6) (`Mr. Celko`, `Database 102`, 223, 6) (`Ms. Shields`, `Database 101`, 223, 6)
Ms. Shields and I are both in room 223, trying to teach different classes at the same time. Matthew Burr looked at the constraints and the rules, and he came up with this analysis.
CREATE TABLE Schedule_3 -- correct version (teacher_name VARCHAR(15) NOT NULL,
class_name CHAR(15) NOT NULL, room_nbr INTEGER NOT NULL,
24 CHAPTER 1: DATABASE DESIGN
period INTEGER NOT NULL,
UNIQUE (teacher_name, period), -- rules #1 and #2 UNIQUE (room_nbr, period),
UNIQUE (class_name, period)); -- rules #3 and #4
If a teacher is in only one room each period, then given a period and a teacher I should be able to determine only one room; i.e., room is functionally dependent upon the combination of teacher and period. Likewise, if a teacher teaches only one class each period, then class is functionally dependent upon the combination of teacher and period. The same thinking holds for the last two rules: class is functionally dependent upon the combination of room and period, and teacher is functionally dependent upon the combination of room and period.
With the constraints that were provided in the first version, you will find that the rules are not enforced. For example, I could enter the following rows:
(‘Mr. Celko’, ‘Database 101’, 222, 6) (‘Mr. Celko’, ‘Database 102’, 223, 6)
These rows violate the first and second rules.
However, the unique constraints first provided in Schedule_2 do not capture this violation and will allow the rows to be entered.
The following constraint:
UNIQUE (teacher_name, room_nbr, period)
checks the complete combination of teacher, room, and period, and since (`Mr. Celko`, 222, 6) is different from (`Mr. Celko`, 223, 6), the DDL does not find any problem with both rows being entered, even though that means that Mr. Celko is in more than one room during the same period.
The constraint:
UNIQUE (teacher_name, class_name, period)
does not catch its associated rule either, since (`Mr. Celko`, `Database 101`, 6) is different from (`Mr. Celko`, `Database 102`, 6). As a result, Mr. Celko is able to teach more than one class during the same period, thus violating rule #2. It seems that we’d also be able to add the following row:
1.1 Schema and Table Creation 25
(`Ms. Shields`, `Database 103`, 222, 6)
This violates the third and fourth rules.
1.1.7 CREATE ASSERTION Constraints
In Standard SQL, CREATE ASSERTION allows you to apply a constraint on the tables within a schema, but not to attach the constraint to any particular table. The syntax is:
::=
CREATE ASSERTION []
::=
CHECK
As you would expect, there is a DROP ASSERTION statement, but no ALTER statement. An assertion can do things that a CHECK() clause attached to a table cannot do, because it is outside of the tables involved. A CHECK() constraint is always TRUE if the table is empty.
For example, it is very hard to make a rule that the total number of employees in the company must be equal to the total number of employees in all the health plan tables.
CREATE ASSERTION Total_health_Coverage CHECK (SELECT COUNT(*) FROM Personnel) =
+ (SELECT COUNT(*) FROM HealthPlan_1) + (SELECT COUNT(*) FROM HealthPlan_2) + (SELECT COUNT(*) FROM HealthPlan_3);
1.1.8 Using VIEWs for Schema Level Constraints
Until you can get CREATE ASSERTION constraints, you have to use procedures and triggers to get the same effects. Consider a schema for a chain of stores that has three tables, thus:
CREATE TABLE Stores
(store_nbr INTEGER NOT NULL PRIMARY KEY, store_name CHAR(35) NOT NULL,
...);
26 CHAPTER 1: DATABASE DESIGN
CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL PRIMARY KEY, last_name CHAR(15) NOT NULL, first_name CHAR(15) NOT NULL, ...);
The first two explain themselves. The third table, following, shows the relationship between stores and personnel, namely who is assigned to what job at which store and when this happened. Thus:
CREATE TABLE JobAssignments (store_nbr INTEGER NOT NULL
REFERENCES Stores (store_nbr) ON UPDATE CASCADE
ON DELETE CASCADE,
ssn CHAR(9) NOT NULL PRIMARY KEY REFERENCES Personnel( ssn) ON UPDATE CASCADE
ON DELETE CASCADE,
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, end_date TIMESTAMP,
CHECK (start_date <= end_date),
job_type INTEGER DEFAULT 0 NOT NULL -- unassigned = 0 CHECK (job_type BETWEEN 0 AND 99),
PRIMARY KEY (store_nbr, ssn, start_date));
Let’s invent some job_type codes, such as 0 = `unassigned`, 1 = `stockboy`, etc., until we get to 99 = `Store Manager`. We have a rule that each store has, at most, one manager. In Standard SQL, you could write a constraint like this:
CREATE ASSERTION ManagerVerification CHECK (1 <= ALL (SELECT COUNT(*)
FROM JobAssignments WHERE job_type = 99 GROUP BY store_nbr));
This is actually a bit subtler than it looks. If you change the <= to =, then the stores must have exactly one manager if it has any employees at all.
But as we said, most SQL products still do not allow CHECK() constraints that apply to the table as a whole, nor do they support the scheme-level CREATE ASSERTION statement.
...
- tailieumienphi.vn
nguon tai.lieu . vn