Print this chapterPrint this chapter

CS403 Study Guide

Unit 5: The Relational Database Model

5a. describe the relational database model

  • What are the elements of a relational model?
  • Which ER elements do they correspond to?
  • What is the mathematical foundation of relational modeling?
  • What is the benefit of a mathematical foundation?

We can think of the relational database model as a 'transition' model, for several reasons:

  1. An ER model can be easily converted to a relational model.
  2. A relational model can be used to communicate with both technical and non-technical stakeholders.
  3. Relational models are logical models which 'connect' conceptual design to database high level and detailed design. 
  4. Relational modeling has a formal mathematical foundation, which enables formal methods, techniques, and tools to support the transition from requirements to database high level design, detailed design, implementation, and operation.
  5. Relational models have an associated database language for creating and using a relational database, SQL (Structured Query Language). 

As with ER modeling, relational modeling begins with a real world domain of application. A primary element of an ER model is 'entity', which represents a collection of specific instances from a domain. For example, 'Student' is an entity because it has a separate existence. Given a set of constraints, 'Student', represents a collection of specific students that satisfy the constraints. Constraints include attributes and various other properties (such as cardinality, participation, or business rules). Another primary element of ER modeling is the relationship. On the other hand, relational modeling has just one primary element, the relation, from which all the other elements are derived. What are some other elements of a relational model? Which correspond to ER relationships, ER attributes, and ER constraints? To review, see pages 24-26 of Database Design. This chapter does not explicitly outline the correspondence between ER model elements and relational model elements, but the names of the elements should make that clear to you.

Formally, a relation is a subset of the universe of discourse, which is a subset of D1 x D2 x D3 x ... x Dm, the cartesian product of domains of application. A relation is represented by a table of n rows and m columns, written as an n x m table. A cell of a table (the intersection of a row and a column) represents an atomic value from a domain of application. Rows and columns are also just tables themselves. A row is a 1 x m table (using the first component to represent the number of rows and the second to represent the number of columns), and a column is an n x 1 table. Operations on tables combine tables to produce a result table. Thus, the name for the mathematical description of relations is relational algebra.

Each fundamental concept of an ER model corresponds to a formal relational model concept. For example, an attribute corresponds to a column. An entity in an application domain (or in an ER model) corresponds to a table in relational algebra. A relationship in a domain of application (or in an ER model) also corresponds to a table in relational algebra! To think about this, consider a university application where 'Student' is an entity. Constraints restrict the entity to a particular class of student, such as by year, by major, or by advisor - (attributes). The addition of a student ID number identifies a specific student in the particular class. Therefore, 'Student' model consists of a related set of attributes. But, an attribute is just a n x 1 table; and, thus, 'Student' (in the E-R model) is an n x m table (in the relational model). Now, think about a relationship in the E-R model, for example, 'Enrolls', which associates two entities, 'Student' and 'Course'. We have seen that an Entity corresponds (called mapping discussed in the next learning objective, below) to a table in the relational model. Since an E-R relationship is an association of entities, it corresponds to an association of tables in the relational model. Since an association of tables is just another table, an E-R relationship corresponds to a table in the relational model!

To review, read page 26 of Database Design and pages 6-1 to 6-4 of Database Systems for Management.

 

5b. discuss how to map an entity-relationship diagram to a relational model

  • What relational model elements correspond to the ER model elements?
  • How are constraints in an ER model mapped to a relational model?

The mapping from ER to relational is defined in stages: single entity and single-valued attributes, composite attribute, 1:1 relationship, 1:n relationship, m:n relationship, a multi-valued attribute, and n-ary relationship. A single entity with attributes is mapped to a table whose columns are the ER attributes and whose primary key is the entity primary key.

The mapping for a composite key is the same as for a single entity, except the name of the composite attribute is replaced by the names of the composites. The mapping for a 1:1 binary relationship has several cases, depending on which entity is a totally participating entity. If an ER binary relationship is an association of two ER entities where one has total participation in the other, the association naturally maps to two tables that are associated by adding a foreign key to one of them. It is represented by adding the primary key of the strong entity as a foreign key to the weak entity. If both of the entities are totally participating, a new entity is created by merging the two original entities. In each case, the ER relationship is mapped to a table that uses foreign keys to represent the relationships.

The mapping for a 1:n relationship to a relational model is similar to the mapping of a 1:1 relationship, except that the primary key of the 1-side of the relationship is added as a foreign key to the n-side. An m:n relationship is mapped to a new relational table that includes two attributes, the primary key from one of the original entities and the primary key for the other. The primary key of the new relational table is a composite key of those two primary keys. The portions of the composite key serve as foreign keys to the respective original tables. Any ER relationship attributes become attributes of the new relational table.

The next case is the mapping of an ER multi-valued attribute. It is mapped to a new table, having these attributes: the primary key of the original ER entity and the name of the multi-valued ER attribute. Both become the composite primary key of the new table. The primary key portion of this composite key is a foreign key to the original entity table. 

An n-ary relationship is mapped to a new relational table whose attributes are the primary keys of the entity tables in the n-ary relationship.

To review E-R to relational mapping, watch this video, which covers the above cases in detail.

 

5c. explain the various types of integrity constraints

  • What is integrity? 
  • What are some integrity constraints?

For this objective, we examine constraints that are used by database analysts, administrators, and designers to specify semantics for a database, and that are used by a database to check that the semantics are satisfied. 

Integrity for a database means that the information in the database is correct, satisfies the requirements (valid), and is consistent. Constraints and rules are used to assure integrity when data is entered into a database and when data is modified. 

Many integrity constraints are defined during database requirements analysis or design. They can be described ER modeling or relational modeling terminology. If ER modeling is performed, the ER to relational mapping is used to identify corresponding relational model constraints. Here, constraints are defined using both either ER terminology (if they come from the application problem domain) and relational terminology (if they come from the design solution domain).

In the context of a domain of application, familiar to users and database administrators, we almost always encounter a rich language that spans from the very general to the very detailed. For example, the word 'car' includes all models, colors, engine types, and so on. If we are interested in a specific model or a specific car, then we have to add detail that restricts or constrains the scope of 'car' to clearly identify the car we're interested in. These details are entity constraints and relationship constraints. 

In the context of conceptual modeling for high-level design and logical modeling for detailed design, both used by database administrators and designers, the 'language' we use to describe constraints includes several E-R and relational terms: domain value constraints for table columns; uniqueness constraints via primary and foreign keys that correspond to the ER term, relationship; column constraints; strong and weak types of inter-table relationships via foreign key references from one table to another; minimum and maximum association restrictions; and 'relationship' tables where related foreign keys are the column names. This video explains integrity constraints for a relational model, from the perspective of a mapping from ER model integrity constraints. 

The integrity constraints summarized above are primarily constraints on data. Chapter 9 of Database Design also describes information constraints, including enterprise rules and business rules, for example, 'an instructor cannot teach more than 3 courses'. In this resource, the constraints mentioned above, in the preceding paragraph, are called domain integrity constraints, entity constraints, referential integrity constraints, primary key rules, foreign key rules. Enterprise and business rules are primarily addressed by cardinality and participation constraints, and relationship types (optional or mandatory). 

Database requirements, in addition to effectiveness also address efficiency. Data and information integrity fall under effectiveness. Performance (speed and amount of storage) and cost of resources fall under efficiency. Often, effectiveness and efficiency are a tradeoff, and one must be 'reduced' to achieve the other, such as by giving up data consistency for 'eventual' data consistency to improve performance.

 

Unit 5 Vocabulary

This vocabulary list includes terms that might help you with the review items above and some terms you should be familiar with to be successful in completing the final exam for the course. 

Try to think of the reason why each term is included. 

  • Relational database
  • Map (model to model; language to language)
  • Syntax
  • Relational model
  • E-R model
  • Integrity
  • Semantics
  • Integrity constraint
  • Relational algebra
  • Entity
  • Relation
  • Relationship
  • Attribute
  • Cartesian product
  • Table
  • Row
  • Column
  • Key
  • Relationship types
  • 1:1 relationship
  • 1:n relationship
  • m:n relationship
  • Degree of a relationship
  • N-ary relationship
  • Data correctness
  • Data validity
  • Data consistency
  • Data constraints
  • Entity constraints
  • Attribute constraints
  • Relationship constraints
  • Information constraints
  • Enterprise rules
  • Business rules
  • Effectiveness
  • Efficiency
  • Performance
  • Tradeoffs of effectiveness and efficiency