1 / 20

Common Field Types

Primary Key. Foreign Key. Descriptive Fields. Common Field Types. CUSTOMER ( Customer ID , LastName, FirstName, Address, City, State, Zipcode). ORDER ( Order ID , Customer ID, OrderDate). Referential Integrity. WRONG. RIGHT. Field Sequencing. Potentially Wasted Storage Space.

ralph-fox
Download Presentation

Common Field Types

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Primary Key Foreign Key Descriptive Fields Common Field Types

  2. CUSTOMER (Customer ID, LastName, FirstName, Address, City, State, Zipcode) ORDER (Order ID, Customer ID, OrderDate) Referential Integrity

  3. WRONG RIGHT Field Sequencing

  4. Potentially Wasted Storage Space Fixed Length Record with Provision for Multiple Vendors

  5. Indicator for number of Vendor Fields Variable Length Record with Provision for Multiple Vendors

  6. Sector Without Blocking Record 1 Unused Storage Space 512 bytes Sector With Blocking Record 4 Record 3 Record 2 Record 1 Record 5 Blocking Factor

  7. Record 6 Part 1 Sector 1 Unused Storage Space 512 bytes Record 4 Record 11 Part 1 Record 6 Part 2 Record 3 Record 2 Record 1 Record 5 Record 9 Record 8 Record 7 Record 10 Sector 2 Record Spanning

  8. Physical Record Position in File Record Primary Key Sequential File Organization

  9. Track Index Cylinder Index Track 1 661 703 Track 2 717 Master Index 719 583 1179 127 500 2873 1800 1500 722 723 1000 2501 2200 1320 275 729 3161 726 1500 3317 2501 1500 3317 500 1000 727 729 Track 3 Indexed File Organization

  10. The general computation for determining a relative storage address slot for a particular record is: Relative slot address = 1 + Remainder of Primary Key Value _ Total Number of Reserved Address Slots Assume a PRODUCT file with 4, 500 unique records. The storage allocation for this file is equal to the number of records, therefore 4,500 storage slots will be needed on the media. The primary key for records in this file is the PRODUCT_ID field. The location for PRODUCT_ID 734171 can be computed as follows: Relative slot address = 1 + Remainder of 734171 _ = 1 + 671 = 672 4500 Division Remainder Hashing Algorithm

  11. CUSTOMER FILE ORDER FILE ORDERED_ITEM FILE PRODUCT FILE Network Database Architecture

  12. STUDENT FILE CURRENT COURSE FILE FINANCIAL ACTIVITY FILE ACADEMIC ACTIVITY FILE Hierarchical Database Architecture

  13. RELATION A (Primary Key, Attribute 1, Attribute 2, Attribute 3, …) RELATION B (Primary Key, Foreign Key, Attribute 2, Attribute 3, …) Relational Database Architecture

  14. Object Class A Object Class C Object Attributes Object Attributes Object Class B Object Attributes Methods Methods Methods Object-Oriented Database Architecture

  15. Data Type Description CHAR(n) Alphanumeric character array with a maximum length of n. DECIMAL(m,n) Signed numerical data with a total number of digits, m, and n number of digits to the right of the decimal point. VARCHAR Alphanumeric character array of variable length. INTEGER Signed whole number typically up to 11 digits in length SMALLINT Signed whole number up to 5 or 6 digits in length. DATE Date and time including appropriate validity checks (i.e. 4-31 not allowed). NUMBER Real number FLOAT(m,n) Numerical data represented in scientific notation with a total number of digits, m, and n number of digits to the right of the decimal point. LOGICAL Binary data (i.e. TRUE/FALSE, YES/NO) LONG Variable length alphanumeric character array up to 2Gb LONGRAW Binary large object (BLOB) – no assumptions are made about format or content. Common Data Type Definitions

  16. Field Specification Description Field Name Formal name for the field that uniquely identifies it from all others. Data Type Data type associated with this field. Units The unit of measure associated with this field (if applicable). Maintenance Rules for update, change, or deletion once data is entered (i.e. accounting transaction data cannot be changed once entered). Missing Data Procedure for handling missing data during multiple record processing (i.e. IGNORE, USE AVERAGE OF OTHER RECORDS). Check Digit Algorithm for calculate and verifying any check digits used in this field. Formula Algorithm for computing the value of this field (if calculated). Coding Coding conventions or acceptable abbreviations (i.e. 2-character abbreviation for State). Domain/Range Specification of range limits or default values for the field. Referential Integrity Specification for any referential integrity constraints for the field. Data Owner Identification of the responsible party for identifying the source and meaning of data contained in this field. Common Field-Level Data Specifications

  17. Domain Control Description Default Value Automatic entry of a value commonly found for this field unless other data is entered. Range Control Imposition of acceptable or relevant limits on either alphanumeric or numerical data. Picture Control Imposition of a specific pattern for the data being entered (i.e. DATE= mm/dd/yy or dd-mm-yyyy) Null Value Control Specification of whether a value in a particular field is required or optional. Common Domain Controls

  18. Deletion Rule Explanation NO RESTRICTION Any record in the file may be deleted without regard to any other record or file. DELETE:CASCADE A deletion of a record must be automatically followed by the deletion of any matching records in any related files. DELETE:RESTRICT A deletion of a record in a table must be disallowed until any matching records are deleted from any related files. DELETE:SET NULL A deletion of a record must be automatically followed by setting any matching keys in a related file to NULL. Examples of Referential Integrity Deletion Rules

  19. File Type Description Master File  Contains records related to business data that is relatively permanent. Once a record is entered, it tends to remain indefinitely. Data values may change (i.e. CUSTOMERS, PRODUCTS, INVOICES, SUPPLIERS). Transaction File  Contains records that describe business events which can occur on a day-to-day basis. Data tends to have a limited useful life and are usually moved to an archival file after a predetermined period. Document File  Contains stored copies of historical data in document form such that the overhead associated with recreated or reprocessing the document is eliminated. Table Look-Up File  Contains reference data used to validate field values and to maintain consistency throughout the database (INCOME TAX TABLES, POSTAL CODE TABLES). Audit File Contains records of updates to other files in the database in case production files are damaged and require reconstruction. Data can be used with records in archival files to recover or restore damaged files or lost data. Archive File  Contains records that have been deleted from active transaction files but require retention for an indefinite period. Work File  Contains records stored temporarily or records containing intermediate results in a calculation process. Work files are usually created when needed and deleted upon completion of the task. Common File Types

  20. FIELD SIZE (bytes) Invoice_ID 7 Invoice_Date 8 Customer_ID 6 Product_ID 9 Quantity 3 Unit_Price 6 Salesman_ID 4 Record Size 43 Record Overhead 25% Total Record Size 53.75 Total Annual Record Volume 67,000 Expected Table Volume 3,601,250 Expected Annual Growth Rate 16% Projected Table Volume – 4 years 5,621,176 Volumetric Analysis

More Related