Chapter 2 The Relational Database Model Database Systems Design Implementation and Management Fifth Edition Rob and Coronel TOPICS ENTITIES ATTRIBUTES TABLES KEYS INTEGRITY RULES RELATIONAL ALGEBRA DATA DICTIONARY CONTROLLED REDUNDANCY INDEXING 2 Database Design Determine Entities Identify each entities characteristics attributes Name attributes Assign data types and size Determine constraints and domain Identify candidate keys Assign Primary key Assign secondary alternate keys 3 Logical View of Data Relational Database Designer focuses on logical what representation rather than physical how Use of table advantageous Structural and data independence Related records stored in independent tables Logical simplicity Allows for more effective design strategies 4 Logical View of Data con t Entities and Attributes Entity is a person place event or thing about which data is collected Entity names are SINGULAR NOUNS Attributes are characteristics of the entity Tables Holds related entities or entity set Also called relations Comprised of rows and columns 5 Summary of the Characteristics of a Relational Table Table 2 1 6 Example Tables Figure 2 1 7 Keys Controlled redundancy shared common attributes makes the relational database work The primary key is an attribute that uniquely identifies any given entity row To have entity integrity a table must have a primary key and must not have a null value in the primary key 8 Keys The primary key of one table appears again as the link foreign key in another table This is where controlled redundancy comes into place If the foreign key contains either matching values or nulls the table s that make use of such a foreign key are said to exhibit referential integrity 9 Keys A key helps define entity relationships The key s role is based on a concept known as determination which is used in the definition of functional dependence The attribute B is functionally dependent on A if A determines B An attribute that is part of a key is known as a key attribute A multi attribute key is known as a composite key If the attribute B is functionally dependent on a composite key A but not on any subset of that composite key the attribute B is fully functionally dependent on A 10 Relational Database Keys 11 Integrity Rules Revisited 12 Figure 2 4 An Illustration of Integrity Rules 13 Relational Database Operators The degree of relational completeness can be defined by the extent to which relational algebra is supported Relational algebra defines the theoretical way of manipulating table contents using the eight relational functions SELECT PROJECT JOIN INTERSECT UNION DIFFERENCE PRODUCT and DIVIDE 14 Union Combines all rows Figure 2 5 15 Intersect Yields rows that appear in both tables Figure 2 6 16 Difference Yields rows not found in other tables Figure 2 7 17 Product Yields all possible pairs from two tables 18 Divide Requires use of single column table and two column table Figure 2 17 19 Select Yields a subset of rows based on specified criterion 20 Project Yields all values for selected attributes columns 21 Join Information from two or more tables is combined Figure 2 11 Figure 2 14 22 Relational Database Operators Natural JOIN links tables by selecting only the rows with common values in their common attribute s It is the result of a three stage process A PRODUCT of the tables is created Figure 2 12 A SELECT is performed on the output of the first step to yield only the rows for which the common attribute values match Figure 2 13 A PROJECT is performed to yield a single copy of each attribute thereby eliminating the duplicate column Figure 2 14 23 Natural Join Step 1 PRODUCT Figure 2 12 24 Figure 2 13 Natural Join Step 2 SELECT Figure 2 14 Natural Join Step 3 PROJECT 25 Join Natural join EquiJoin with the duplicate column removed Performed by a Project on the result of equijoin aka Inner Join When the term Join is mentioned without any prefix it is implied to be Natural Join Outer Join Unmatched rows from the participating tables are retained in the result table with unmatched attributes left blank or null Left Outer join keeps all tuples from the left relation Right Outer Join keeps all tuples from the right relation Theta Join EquiJoin with the equality operator replaced by any other comparison operator such as greater than less than etc 26 Outer JOIN Examples 27 Left Outer Join Example 28 Right Outer Join Example 29 Data Dictionary and System Catalog Data dictionary Provides detailed account of all tables found within database Metadata Attribute names and characteristics System catalog Detailed data dictionary System created database Stores database characteristics and contents Tables can be queried just like any other tables Automatically produces database documentation 30 A Sample Data Dictionary 31 Relationships within Relational Database Relationship classifications 1 1 1 M M N E R Model ERD Maps E R model Chen Crow s Feet 32 ERD Symbols Rectangles represent entities Diamonds represent the relationship s between the entities 1 side of relationship Number 1 in Chen Model Bar crossing line in Crow s Feet Model Many relationships Letter M and N in Chen Model Three pronged Crow s foot in Crow s Feet Model 33 Example 1 M Relationship Figure 2 18 34 Example 1 M Relationship 35 Example M N Relationship 36 Example M N Relationship Figure 2 24 37 Converting M N Relationship to Two 1 M Relationships Figure 2 25 38 Converting M N Relationship to Two 1 M Relationships con t Figure 2 26 39 Converting M N Relationship to Two 1 M Relationships con t Figure 2 27 40 Converting M N Relationship to Two 1 M Relationships con t Figure 2 28 41 Data Redundancy Revisited Foreign keys can reduce redundancy Some redundancy is desirable Called controlled redundancy Speed Information requirements 42 Indexes Points to location Makes retrieval of data faster Figure 2 31 43