Relational Database Model

1. Relational Database Model Tables By Bob Larson

2. Relational Database Model • Introduced by E. F. Codd in 1970 • A Logical View of Data • Enables developer to view data logically rather than physically • Greater logical simplicity tends to yield simpler and more effective database design methodologies

3. Tables • Cornerstone of Relational DBMS • Advantages – structural and data independence • Conceptually Resembles a file • Note a file is actually a physical structure • Easier to understand than its hierarchical and network database predecessors

4. Table Characteristics

5. Keys – Key Attributes (Fields) • Consists of one or more attributes that determine appropriateness of other attributes • Primary Key (PK) – Attribute or a combination of attributes that uniquely identifies each entity (row) • Key’s role is based on determination • If you know the value of attribute A, you can look up (determine) the value of attribute B

6. Keys Types

7. Keys – Primary & Foreign

8. Null Values • No data entry – Not the same as zero (0) • Can represent • An unknown attribute value • A known, but missing, attribute value • A “not applicable” condition • Not permitted in primary key • Should be avoided in other fields (when possible) • Create problems with math functions such as COUNT, AVERAGE, and SUM • Can create logical problems when relational tables are linked

9. Field (Column) Redundancy • Controlled redundancy: • Makes the relational database work • Database tables share common attributes only to enable the tables to be linked • Multiple occurrences of a field in a database is not redundant when required to make the relationship work • Redundancy exists only when there is unnecessary duplication of attribute values (?) (?) (? - click)

10. Entity Integrity Rules

11. Integrity Rules

12. Database Normalization • Process of efficiently organizing data in a database • Two goals: • Eliminate redundant data (storing same data in multiple tables) • Reduce the amount of space a database consumes • Ensure data dependencies make sense (only storing related data in a table) • Ensure that data is logically stored • Database community developed a guidelines • Normal forms • Numbered from one (lowest or 1NF) through five (5NF) • Typical applications use 1NF, 2NF, and 3NF (occasional 4NF) • Fifth normal form is very rarely seen and won't be • Important to point out that they are guidelines only • Occasionally, it becomes necessary to meet business requirements

13. Normal Forms (1-4) • First normal form (1NF) • Eliminate duplicative columns from the same table • Create separate tables for each group of related data • Identify each row with a unique column or set of columns (the primary key) • Second normal form (2NF) • Meet all the requirements of the first normal form • Remove subsets of data that apply to multiple rows of a table and place them in separate tables • Create relationships between these new tables and their predecessors through foreign keys • Third normal form (3NF) • Meet all the requirements of the second normal form • Remove columns that are not dependent upon the primary key • Fourth normal form (4NF) • Meet all the requirements of the third normal form • Remove columns with multi-valued dependencies • Guidelines are cumulative • For a database to be in 2NF, it must meet the criteria of a 1NF database

14. Table Normalized

15. Indexes (Index Fields) • Used to logically access rows in a table • Index key • Index’s reference point • Points to data location identified by the key • Unique index • Index in which the index key can have only one pointer value (row) associated with it • Each index is associated with only one table

16. Table Types • Data (Entities / Nouns) • Transaction (Events / Verbs) • Subset • Validation

17. Data Tables • Sometimes called inventory tables • Generally your object (entity) tables • Important persons, places, or things • Not the processing of those things • Clearly relationships between the tables • But no common (link) fields

18. Transaction Tables • Sometimes called linking, event or junction tables • Used to link data tables which can’t be linked directly • Event tables linked at a point in time when something happened

19. Subset Tables Cruise Example Security Example • Includes fields directly related to another data table • Can’t be included in the original data table • For security reasons • They would represent multiple entries in a single field • Often we discovered as we start entering test data

20. Validation Tables • Helps maintain data integrity for a field • Used for field lookups and combo boxes on forms • Limits user choices to a drop-down list • Not data tables because they are secondary in importance • We didn’t build the database to track states or department codes

21. Problem Fields (Don’ts) • Calculated field – can be computed by mathematical calculation or text concatenation • Waste of storage space (redundant), • No assurance the calculated value is updated when the user changes the input field(s) • Multipart field – contains that should be two or more fields • Extra work when you want to analyze your data • Multivalue field – multiple correct entries for the field • Create a separate subset table with each value in its own record. • Derived field – contents of one or more fields absolutely predicts the contents of another • Should be dropped from the table

22. Table Name Guidelines • Keep it short, simple and descriptive without using acronyms, abbreviations or codes • Do not include object names like File, Records, Table and List • Use the plural form of the name • Do not use names that refer to or imply more than one subject • Materials and Supplies – should be two fields • Do not use names that unnecessarily restrict the scope of your data • Names that imply a time frame, regional location or organizational status – Manufacturing Employees

23. Field Name Guidelines • Keep it unique • Should be unique in entire DBMS except to link • Keep it short, simple and descriptive • Use the singular form of the name • Do not use names that refer to or imply more than one attribute or subject • Avoid codes and acronyms • Use abbreviations only if their meaning is absolutely clear

24. The Data Dictionary/Catalog • Data dictionary • Provides detailed accounting of all tables found within the user/designer-created database • Contains (at least) all the attribute names and characteristics for each table in the system • Contains metadata—data about data • Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures • Terms “system catalog” and “data dictionary” are often used interchangeably

25. A Sample Data Dictionary

26. Fin…