Fundamentals of Relational Database Yong Choi School of Business

Fundamentals of Relational Database Yong Choi School of Business CSUB, Bakersfield

Study Objectives Understand the relational database model’s basic components are entities and their attributes, and relationships among entities Identify how entities and their attributes are organized into tables Understand concept of integrity rules of relational database

Relational Model In the relational data model the database is represented as a group of related tables. The relational data model was introduced in 1970 by E. F. Codd of IBM published a paper in CACM entitled "A Relational Model of Data for Large Shared Data Banks". It is currently the most popular model. The mathematical simplicity and ease of visualization of the relational data model have contributed to its success.

Definitions of Terminology Formal relational term Informal equivalents relation table tuple row or record cardinality number of rows attribute column or field degree number of columns (unique) identifier Primary key domain pool of legal values

Characteristics of a Relation (table) Two-dimensional structure with rows and columns A relation represent a single entity Each table must have an attribute to uniquely identify each row Column values all have same data type Order of the rows and columns is immaterial to the DBMS

Properties of a Relation Based on the set theory 1. There are no duplicate tuples (rows). The body of the relation is a mathematical set (i.e., a set of tuples), and sets in mathematics by definition do not include duplicate elements. If a "relation" contains duplicate tuples, then it is not a relation.

Properties of a Relation 2. Tuples (rows) are unordered (top to bottom). Sets in mathematics are not ordered. So, even if a relation A's tuples are reversely ordered, it is still the same relation. Thus, there is no such thing as "the 5th tuple" or the last tuple. In other words, there is no concept of positional addressing.

Properties of a Relation 3. Attributes (columns) are unordered (left to right). The heading of a relation is also defined as a set. There is no such thing as "5th attribute (column)" or the last attribute.

Properties of a Relation 4. All attribute values are atomic. At every row-and-column position within the table, there always exists precisely one value, never a list of values. Or equivalently, relations do not contain repeating groups. A relation satisfying this condition is said to be in First Normal Form.

Primary Key A PK is an attribute, or collection of attributes, whose values uniquely identify each tuple in a relation. To being unique, a PK must be minimal (contain no unnecessary attributes) and must not change in value.

Primary Key (con’t) One attribute, or collection of attributes, that can serve as a PK is called candidate key, And the remaining keys that cannot be used as a PK are called alternate key. Cost of PK SS# vs. finger print

Candidate Key and Alternate key State StateAbbrev StateName CT Connecticut MI Michigan 26 Robin SD South Dakota 40 Phesant TN Tennessee 16 Mockingbird 4,877,185.00 TX Texas 28 Mockingbird 16,986,510.00 StateNumber StateBird 5 American Robin StatePopulation 3,287,116.00 9,295,297.00 696,004.00 Could any attribute (column) serve as the PK? candidate key Is there any attribute that should not be served as the PK? alternate key

Entity Integrity Rule Guarantees that each entity will have a unique identity and ensures that foreign key values can properly reference primary key values. Requirement No component of the primary key is allowed to accept nulls. By "null" here, we mean that information is missing for some reason.

Foreign Key An attribute in one table whose values must either match the primary key in another table or be null. Attribute FK of base relation R2 is a foreign key if and only if it satisfies the following two timeindependent properties: Each value of FK is either wholly null or wholly non-null. Each non-null value of FK is identical to the value of PK in some tuple of R1.

Foreign Key (con’t)

Referential Integrity Rule The database must not contain any unmatched foreign key values. Just as primary key values represent entity identifiers, so foreign key values represent entity references. The referential integrity rule simply says that if B references A, then A must exist.

Referential Integrity Enforcement Restriction: Nullification: if value is deleted, reference value will set to be null. Cascading: does not allow any deletion if value is deleted, reference value will also be deleted. if value is updated, reference value will also be updated. Default value: if value is deleted, reference value will be have default value, which is provided by the system.

Access DB Referential Integrity Cascade Update Related Fields Change of PK values in primary table automatic change of FK values Cascade Delete Related Fields Delete of a record in the primary table automatic delete of all records in the related table that have a matching FK value