大型数据库设计ch04.pptVIP

Chapter Premise We have received one or more tables of existing data The data is to be stored in a new database QUESTION: Should the data be stored as received, or should it be transformed for storage? How Many Tables? Assessing Table Structure Counting Rows in a Table To count the number of rows in a table use the SQL built-in function COUNT(*): SELECT COUNT(*) AS NumRows FROM SKU_DATA; Examining the Columns To determine the number and type of columns in a table, use an SQL SELECT statement To limit the number of rows retrieved, use the SQL TOP {NumberOfRows} keyword: SELECT TOP (10) * FROM SKU_DATA; Checking Validity of Assumed Referential Integrity Constraints Given two tables with an assumed foreign key constraint: SKU_DATA (SKU, SKU_Description, Department, Buyer) BUYER (BuyerName, Department) Where SKU_DATA.Buyer must exist in BUYER.BuyerName Checking Validity of Assumed Referential Integrity Constraints To find any foreign key values that violate the foreign key constraint: SELECT Buyer FROM SKU_DATA WHERE Buyer NOT IT (SELECT Buyer FROM SKU_DATA, BUYER WHERE SKU_DATA.BUYER = BUYER.BuyerName; Type of Database Updateable database or read-only database? If updateable database, we normally want tables in BCNF If read-only database, we may not use BCNF tables Designing Updateable Databases Normalization:Advantages and Disadvantages Non-Normalized Table:EQUIPMENT_REPAIR Normalized Tables:ITEM and REPAIR Copying Data to New Tables To copy data from one table to another, use the SQL command INSERT INTO TableName command: INSERT INTO ITEM SELECT DISTINCT ItemNumber, Type, AcquisitionCost FROM EQUIPMENT_REPAIR; INSERT INTO REPAIR SELECT ItemNumber, RepairNumber, RepairDate, RepairAmmount FROM EQUIPMENT_REPAIR; Choosing Not to Use BCNF BCNF is used to control anomalies from functional dependencies There are times when BCNF is not desirable The classic example is ZIP codes: CUSTOMERS (Nu