Week 1

Day 3

Logical Database
Design

The foundation of any database application is a logical data model. Like any model, a logical data model is an idealization of a real system. A model is only as useful as it is accurate. Like any real enterprise, a logical data model is dynamic rather than static. It needs to evolve as the enterprise upon which it is based changes.

A logical data model is a representation of both the data elements used by an enterprise and the relationships between those data elements.

One of the most common methods used for developing a logical data model is entity-relationship modeling. Entities are people, places, objects, or concepts. Each entity is described by a set of attributes. In the sample database, the college registrar views an instructor as a set of attributes, such as instructor ID, last name, first name, and department. A department administrator views an instructor with a different set of attributes for the same instructor; for instance, the instructor's current position. As you can see, business requirements are what drives the data model. Entities don't exist in a vacuum—you define relationships between entities. These relationships are used to enforce business rules.

For example, in the sample application—an information system for a small college—a class must be taught by one—and only one—instructor.

Why bother to develop a logical data model—why not just jump right into database design? By developing a logical data model, you are forced to focus on an organization's data and its internal relationships without initially worrying about implementation details such as a column's datatype. You can think of the logical data model at a higher level of abstraction than you can think of the database design.

Relational Database Theory

No book that deals with relational database systems can be considered complete unless it discusses the basic concepts of relational database theory. In a nutshell, those concepts are as follows:

• Every entity has a set of attributes that describe the entity. For example, an entity named Course would describe the courses offered at a college.
• A row is a single instance of attribute values. For example, a row in the Course entity would describe a single course offered by the college.
• Some of an entity's attributes uniquely identify each row in that entity. This set of attributes is called the primary key. For the entity that describes students, the Student ID attribute is the primary key because it uniquely identifies each student.
• None of the attributes that compose the primary key can be null.
• A foreign key is an attribute in one entity whose values must exist as the primary key in another entity.
• Entities are related to one another.
• The order of the rows in an entity is arbitrary.
• The order of the attributes in an entity is arbitrary.

A Table Is Rows and Columns

From a practitioner's perspective, an entity is implemented as a database table. An entity's attributes are implemented as a table's columns. A single set of attribute or column values is known as a row. The terms row and record are used interchangeably.

A column in a database table represents an entity's attributes.

A row is a single set of entity attributes or column values in a database table.

A record is the same as a row—it is a single set of entity attributes or column values.

The Order of Rows Is Arbitrary

A key tenet of relational database theory is that a table has no implied ordering. The only way to know the order in which rows will be retrieved from a table is to specify the order. The concept of no implied order is powerful because it enables you to think abstractly about tables and, for the most part, to ignore the physical implementation of a database's structures.

The Order of Columns Is Arbitrary

As with a table's rows, a table's columns have no implied ordering. If you use SQL*Plus to describe a table, SQL*Plus returns the columns in the order in which they were created. You can, however, specify any order for retrieving columns. You also can modify a column's definition without affecting any of the other columns. For example, you can increase the width of a column without having to modify any of your existing table definitions or SQL statements. Taking care of the physical details related to the change is the job of the Oracle relational database management system (RDBMS). A relational database is said to provide logical data independence because column definitions are independent from one another.

Data Integrity

According to relational theory, every entity has a set of attributes that uniquely identify each row in that entity. Relational theory also states that no duplicate rows can exist in a table, which is really just another way of saying that every table must have a primary key. This concept is referred to as data integrity. For example, the Social Security number for each current employee is unique.

Primary Key

Every entity has a set of attributes that uniquely define an instance of that entity. This set of attributes is referred to as the primary key. The primary key may be composed of a single attribute—a student is uniquely identified by a Student ID—or of several attributes—a course is uniquely identified by both the Department ID and the Course ID. Sometimes, the attributes that compose the primary key are obvious, and other times they are not. To test your understanding of the primary key, you must look at existing data. However, you also