Chapter 2 Notes for Module 2

Why is data modeling so important to the database designer?

We are said to live in an information age, and data constitute the most basic information units employed by an information system. Data modeling provides a way to reconcile the very different end-user views of the nature and role of data. A data model is an abstraction, providing a relatively simple representation of complex real-world data structures. The model’s function is thus to help us understand the complexity of a real-world data environment. Such understanding is likely to yield useful solutions to the problems inherent in creating, using, and managing data. If a database is to be useful and flexible, it must be well designed. The database design process must be based on an appropriate data model if it is to yield a proper blueprint of the design.

What are business rules, what is their source, and why are they crucial?

Business rules are precise statements, derived from a detailed description of the organization’s operations, that define one or more of the following modeling components.

1. entities in the E-R model correspond to a table
2. relationships are associations between entities
3. attributes are characteristics of entities
4. connectivities are used to describe the relationship classification
5. cardinalities express the specific number of entity occurrences associated with one occurrence of the related entity
6. constraints are limitations on the type of data accepted

Because the business rules form the basis of the data modeling process, their precise statement is crucial to the success of the database design. And, because the business rules are derived from a precise description of operations, much of the design’s success depends on the accuracy of the description of operations. Examples of business rules are: An invoice contains one or more invoice lines, but each invoice line is associated with a single invoice. A store employs many employees, but each employee is employed by only one store. A college has many departments, but each department belongs to a single college. (This business rule reflects a university that has multiple colleges such as Business, Liberal Arts, Education, Engineering, etc.) A driver may be assigned to drive many different vehicles, and each vehicle can be driven by many drivers. (Note: Keep in mind that this business rule reflects the assignment of drivers during some period of time.) A client may sign many contracts, but each contract is signed by only one client. A sales representative may write many contracts, but each contract is written by one sales representative. Note that each of the preceding business rules describes a two-way relationship between the main components. This practice reflects the implementation by many of the available CASE tools.

Naturally, the business rules may be decomposed to show only one direction of a relationship at a time. For example, the first bulleted business rule may be decomposed into these two components: An invoice contains one or more invoice lines. Each invoice line is associated with a single invoice. Keep in mind that the E-R diagrams cannot always reflect all of the business rules. For example, the business rule a customer cannot be given a credit line over $10,000 unless that customer has maintained a satisfactory credit history (as determined by the credit manager) during the past two years is a constraint that cannot be shown in the E-R diagram. The business rule reflected in this constraint would be handled at the applications software level through the use of a trigger or a stored procedure.

What is the evolution of data models?

Reasons for studying file systems:

• Complexity of database design is easier to understand
• Understanding file system problems helps to avoid problems with DBMS systems
• Knowledge of file system is useful for converting file system to database system
• File systems typically composed of collection of file folders, each tagged and kept in cabinet
• Organized by expected use
• Contents of each file folder are logically related
• Manual systems
• Served as a data repository for small data collections
• Cumbersome for large collections
• Computerized file systems
• Data processing (DP) specialist converted computer file structure from manual system
• Wrote software that managed the data
• Designed the application programs
• Initially, computer file systems resembled manual systems
• As number of files increased, file systems evolved
• Each file used its own application program to store, retrieve, and modify data
• Each file was owned by individual or department that commissioned its creation

What are the degrees of data extraction?

The External Model: The external model is the end users’ view of the data environment. The Conceptual Model: The conceptual model represents a global view of the entire database as viewed by the entire organization. The Internal Model: The internal model is the representation of the database as “seen” by the DBMS. In other words, the internal model requires the designer to match the conceptual model’s characteristics and constraints to those of the selected implementation model. The Physical Model: The physical model operates at the lowest level of abstraction, describing the way data are saved on storage media such as disks or tapes.

Chapter 3 Notes for Module 2

What is a relational database, and how does its logical structure differ from that of the network and hierarchical databases?

In hierarchical and network databases, the physical structure closely resembles the logical structure. For example, the hierarchical database’s preorder traversal — its hierarchic sequence — matches the database’s physical access paths to the data. This arrangement makes it difficult to visualize the logical database structure and it requires the database designer to have a firm understanding of the database’s physical environment. In contrast, the relational model’s focus is on the logical (conceptual) structure, rather than on the physical aspects of the database. The relational DBMS handles most of the physical details. Therefore, the relational database model, with its conceptual tables, is much more “people-friendly” than its hierarchical and network predecessors. Contrast the hierarchical/network database structure with that of the relational database, in which a logical view of data is easily maintained by letting each entity set be contained in a separate table. These tables are then linked through the use of shared attributes known as primary/foreign keys. It is worthwhile to remember that, although the relational database’s logical focus and its DBMS make its design, implementation, and management relatively easy, the relational database does not entirely eliminate the need to understand its physical components. For example, access efficiency may be greatly affected by the location of tables and indexes, as well as their composition. Oracle, for example, uses cluster tables to improve the efficiency of pointer movements.

What is a relational database table, and what are its components?

The relational database table is a logical structure that houses related entities. This logical structure is visualized as a matrix composed of intersecting rows, one for each entity, and columns, one for each attribute. Make sure you understand that the table stores an entity set. Note that, unfortunately, entity and entity set are often used interchangeably in the literature. In fact, the Entity Relationship (E-R) model’s language never mentions the entity set and just uses entity instead of entity set. Since we must abide by the E-R model’s conventions when we use E-R modeling techniques, it is useful to remember this potentially confusing vocabulary usage. Note that the logical (matrix) view of a table is not physically stored on disk this way. However, the RDBMS takes care of the physical storage details to let us concentrate on the logical structure, thereby making it much easier to visualize what’s happening in the database. Remember that tables are related through the use of common columns.

What are keys? What types of keys may be found within a relational database, and what are their functions?

Keys are used as entity identifiers. The following descriptions are important.

• SUPERKEY: 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 entries.
• 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 match the primary key in another table or whose value must be null.

How is database integrity assured within the relational database environment?

Data integrity is important. Realize that the effects of uncontrolled data redundancy can be devastating. The relational database makes use of controlled redundancy to maintain integrity while linking related tables.

Why would you want to have a data dictionary, and how would you use it?

Most RDBMSs automatically create data dictionaries when a database design is implemented. Database designers must create and update a simple data dictionary to keep track of the entities and attributes as the design begins to take shape. The lack of a data dictionary makes it difficult to keep track of attributes and virtually guarantees the insertion of homonyms and synonyms.

• ENTITY INTEGRITY : All entries are unique. No null entries in a primary key. Guarantees that each entity will have a unique identity.
• REFERENTIAL INTEGRITY: Foreign key must have either a null entry or a matching entry. Makes it possible for an attribute NOT to have a corresponding attribute, but it will be impossible to have an invalid entry.

What are relational operators, and what is the purpose of having them?

The reason for having databases in the first place is to be able to retrieve and manipulate data easily and reliably. Relational operators, based on the relational algebra functions SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE, help make it possible to let us have effective and efficient data retrieval and manipulation. Note that the degree to which these operators are supported defines the extent of “relational completeness.” SQL will be used in Chapter 5 to demonstrate the use of relational operators.

How are entity relationships displayed, and why is it so important to display them?

Such relationships are displayed through the use of E-R models. E-R models will be the basis for our database design activities. In other words, the E-R models will become the database blueprints.

How are M:N relationships handled in the development of an E-R diagram?

Although M:N relationships may properly be viewed in a relational database model at the conceptual level, such relationships should not be implemented, because their existence creates undesirable redundancies. Therefore, M:N relationships must be decomposed into 1:M relationships to fit into the E-R framework.

For example, if you were to develop an E-R model for a video rental store, you would note that tapes can be rented more than once and that customers can rent more than one video tape. It seems reasonable to keep in mind that newly-arrived tapes that have just been entered into inventory have not yet been rented and that some tapes may never be rented at all if there is no demand for them.

Therefore, CUSTOMER is optional to TAPE. Assuming that the video store only rents videos and that a CUSTOMER entry will not exist unless a person coming into the video store actually rents that first tape, TAPE is mandatory to CUSTOMER.

The relationship between customers and tapes would thus be M:N. TheM:N relationship must be broken up into two 1:M relationships through the use of a bridge entity, also known as a composite entity. The composite entity, named RENTAL in the example is composed of at least the primary key components of the two entities it bridges, because the RENTAL’s foreign keys must point to the primary keys of the two entities CUSTOMER and TAPE.

And, because a tape that is never rented will never show up in the RENTAL entity, RENTAL has become optional to TAPE. Each rental transaction, i.e., each record in the RENTAL table, will reference one and only one customer and one and only one tape.