Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Joe Celko s SQL for Smarties - Advanced SQL Programming P13. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 92 CHAPTER 2 NORMALIZATION Consider this actual problem which appeared on CompuServe s ORACLE forum some years ago. A pharmaceutical company has an inventory table and price changes table that look like this CREATE TABLE Drugs drug_nbr INTEGER NOT NULL PRIMARY KEY drug_name CHAR 30 NOT NULL drug_qty INTEGER NOT NULL CONSTRAINT positive_quantity CHECK drug_qty 0 . CREATE TABLE Prices drug_nbr INTEGER NOT NULL start_date DATE NOT NULL end_date DATE NOT NULL CONSTRAINT started_before_endded CHECK start_date end_date price DECIMAL 8 2 NOT NULL PRIMARY KEY drug_nbr start_date Every order has to use the order date to find what the selling price was when the order was placed. The current price will have a value of eternity a dummy date set so high that it will not be reached such as 9999-12-31 . The end_date INTERVAL 1 DAY of one price will be equal to the start_date of the next price for the same drug. While this is normalized performance was bad. Every report invoice or query will have a JOIN between Drugs and Prices. The trick might be to add more columns to the Drugs like this CREATE TABLE Drugs drug_nbr INTEGER PRIMARY KEY drug_name CHAR 30 NOT NULL drug_qty INTEGER NOT NULL CONSTRAINT positive_quantity CHECK drug_qty 0 current_start_date DATE NOT NULL current_end_date DATE NOT NULL CONSTRAINT current_start_before_endded CHECK current_start_date current_end_date current_price DECIMAL 8 2 NOT NULL 2.11 Key Types 93 prior_start_date DATE NOT NULL prior_end_date DATE NOT NULL CONSTRAINT prior_start_before_endded CHECK prior_start_date prior_end_date AND current_start_date prior_end_date INTERVAL 1 DAY prior_price DECIMAL 8 2 NOT NULL . This covered more than 95 of the orders in the actual company because very few orders have more than two price changes before they are taken out of stock. The odd exception was trapped by a procedural routine. The other method is to add check constraints that will enforce the rules destroyed by denormalization. We will discuss this later
