The Relational Database Model Relational Database Model ISM 318 Spring 2000 Dr Hamid Nemati Objectives 4 The Relational Database Model 4 4 4 Understand the relational model s logical structure Understand components of a relational database and their characteristics and relationships Understand basic concepts of table design Understand characteristics of good and bad tables A Logical View of Data l The Relational Database Model l l l Relational database model s structural and data independence enables us to view data logically rather than physically The logical view allows a simpler file concept of data storage The use of logically independent tables is easier to understand Logical simplicity yields simpler and more effective database design methodologies A Logical View of Data l Entities and Attributes u The Relational Database Model An entity is simply a person place event or thing for which we intend to collect data l Examples v v u University Students Faculty Members Courses Airlines Pilots Aircraft Routes Suppliers Each entity has certain characteristics known as attributes l Examples v v Student Student Number Name GPA Date of Enrollment Data of Birth Home Address Phone Number Major Aircraft Aircraft Number Data of Last Maintenance Total Hours Flown Hours Flown since Last Maintenance A Logical View of Data l Entities and Attributes u The Relational Database Model A grouping of related entities becomes an entity set l Examples v v v The STUDENT entity set contains all student entities The FACULTY entity set contains all faculty entities The AIRCRAFT entity set contains all aircraft entities Tables and Their Characteristics u The Relational Database Model contains a group of related entities i e an entity set u A table is also called a relation A Logical View of Data l Characteristics of a Relational Table u The Relational Database Model u u u u A table is perceived as a two dimensional structure composed of rows and columns Each table row tuple represents a single entity within the entity set Each column represents an attribute and each column has a distinct name Each row column intersection represents a single data value Each table must have a primary key that uniquely identifies each row A Logical View of Data l Characteristics of a Relational Table u The Relational Database Model u u u All values in a column must conform to the same data format Each column has a specific range of values known as the attribute domain Each row carries information describing one entity occurrence The order of the rows and columns is immaterial to the DBMS A Logical View of Data l Keys u The Relational Database Model u u Controlled redundancy shared common field makes the relational database work The primary key of one table appears again as the link foreign key in another table 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 A Logical View of Data The Relational Scheme l A vendor supplies one or more products The Relational Database Model Keys The Relational Database Model l l A key relationships It is an attribute that determines the values of other attributes within the entity The key s role is based on a concept known as determination functional dependence The attribute B is functionally dependent determines B key attribute A multi attribute key is known as a Relational Database Keys Superkey The Database Model An attribute or combination of attributes that uniquely identifies each entity in a table Candidate Key A minimal superkey A superkey that does not contain a subset of attributes that is itself a superkey Primary Key A candidate key selected to uniquely identify all other attribute values in any given row Cannot contain null entities Secondary Key An attribute or combination of attributes used strictly for data retrieval purposes Foreign Key An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null Integrity Rules Revisited ENTITY INTEGRITY Requirement All entities are unique and no null entries in a primary key Purpose The Relational Database Model Guarantees that each entity will have a unique identity REFERENTIAL INTEGRITY Requirement Foreign key must have either a null entry or an entry that matches the primary key value in a table to which it is related Purpose Makes it possible for an attribute NOT to have a corresponding value but it will be impossible to have an invalid entity The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table Example A customer might not yet have an assigned sales representative number but it will be impossible to have an invalid sales representative number Relational Database Operators l The Relational Database Model l 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 Relational Database Operators l The Relational Database Model UNION combines all rows from two tables The two tables must be union compatible Relational Database Operators l INTERSECT produces a listing that contains only the rows that appear in both tables The two tables must be union compatible The Relational Database Model Relational Database Operators l The Relational Database Model DIFFERENCE yields all rows in one table that are not found in the other table i e it subtracts one table from the other The tables must be union compatible Relational Database Operators l The Relational Database Model PRODUCT produces a list of all possible pairs of rows from two tables Relational Database Operators l SELECT yields values for all attributes found in a table It yields a horizontal subset of a table The Relational Database Model Relational Database Operators l PROJECT produces a list of all values for selected attributes It yields a vertical subset of a table The Relational Database Model Relational Database Operators l The Relational Database Model JOIN allows us to combine information from two or more tables JOIN is the real power behind the relational database allowing the use of independent tables linked by common attributes Relational Database Operators u u The