You are here

Basic Relational Concepts

19 January, 2016 - 12:35

Review Question

What is a relation?

 

Relational databases are often perceived to be a collection of tables. This is a reasonable perception in that the logical view of the data is a tabular type format referred to as a relation. A relation is defined as a collection of data representing multiple occurrences of an object, event, or agent. Similar to an entity, objects include such things as inventory, equipment, and cash. Events may include orders, sales, and purchases. Agents could include customers, employees, and vendors.

Review Question

What is a tuple? What is an attribute in a relational data model?

 

Figure 3.10 displays an example relation along with labels for each of its components. Consistent with a tabular representation, a relation consists of rows and columns. Rows are referred to as tuples and columns are referred to as attributes. Tuples are sets of data that describe an instance of the entity represented by a relation (e.g., one employee in the EMPLOYEE relation). We may think of a tuple as being akin to a record in a traditional file structure. While technically they are different, logically they are similar. Attributes, as they do in an E-R diagram, represent an item of data that characterizes an object, event, or agent. In terms of traditional file structures, we would parallel attributes (i.e., the columns in a relation) to fields.

In viewing the relation in Figure 3.10, note that the data contained in the table do not appear to be in any particular order. In a relational database model there is no ordering of tuples contained within a relation. This is different from the traditional file structures you studied earlier in this chapter, where sequence or keyed location was usually critical. Rather, ordering of the tuples is unimportant since the tuples are recalled by the database through matching an attribute’s value with some prescribed value, or through a query by which ordering could be established if desired (e.g., by sorting on one of the attributes—such as by Pay_Rate or Billing_Rate).

Review Question

Compare and contrast the data structures for data stored in a file and data stored in a relational table.

 

In order to uniquely identify a tuple, it is critical that each be distinct. This means that each tuple in a relation can be uniquely identified by a single attribute or some combination of multiple attributes. Similar to the rules used for constructing an E-R diagram, a primary key (which is equivalent to a key attribute in an E-R diagram) is specified to uniquely identify each tuple in the relation. Notice in Figure 3.10 that Employee_Number is the primary key (the attribute name is underlined) and that it is unique for every tuple. There may be other attributes in the relation that also have the ability to serve as a key attribute, and in a relation these additional attributes can form secondary keys referred to as candidate keys. For any attribute specified as a key attribute, that attribute must have a unique and “non-null” value (i.e., there has to be some value assigned to the attribute for each tuple). Notice that Soc_Sec_No would also be unique and could possibly be used as a candidate key, but constraints would have to be put in place to ensure every tuple has a value since it is possible that an employee could, at least temporarily, not have a social security number.

media/image45.JPG
Figure 3.10 Example of a Relation (EMPLOYEE) and Its Parts 
 

Review Question

What is referential integrity?

 

Additionally, constraints should be put in place to assure that the referential integrityof the database is maintained. Referential integrity requires that for every attribute value in one relation that has been specified in order to allow reference to another relation, the tuple being referenced must remain intact. In other words, as you look at the relation, EMPLOYEE, in Figure 3.10, notice that EMPLOYEE is party to a recursive relation [also modeled in Appendix A in Figure 3.11, part (b)]. In this recursive relation, Supervisor_No is used to reference the Employee_Number of the supervising employee. If the tuple for Greg Kinman were deleted from the database, note that four other employees would no longer have a valid Supervisor_No (e.g., the Supervisor_No would be referencing a tuple [A632] that no longer exists). Hence, a referential integrity constraint would require the user to reassign the four employees to a new supervisor before the tuple for Greg Kinman could be deleted.