Analysis and Design of Data Systems. Introduction to Relational Database Design (Lecture 14)

one of two scenarios:
Start to design EER Model by identifying entity types, relationships and their respective attributes; and then map the conceptual model into relational database schema
Or, directly start grouping attributes into relations by using common sense

Whichever approach the designer chooses his work will result in having a set of relations forming a relational database schema.

Until now we haven’t established any criteria for goodness of design.
In other words, we couldn’t evaluate whether one grouping of attributes in relation schemas is better or worse then the other one.

Phases of Database Design

at logical level – how understandable and clear the relation schemas are for the users. Important for correct formulation of queries.

The implicit goals of the design activity are information preservation and minimum redundancy.

Information preservation implies that during the process of mapping of the conceptual design into relational database schema all the concepts, like entity types, relationships, specializations and other, be preserved.

Minimum redundancy implies minimizing redundant storage of the same information.

Levels of Goodness of Design

determine the quality of relation schema design:

Making sure that the semantics of the attributes is clear in the schema
Reducing the redundant information in tuples
Reducing the NULL values in tuples
Disallowing the possibility of generating spurious tuples (is not covered during this lecture)

belonging to one relation have certain real-world meaning and a proper interpretation associated with them.

The semantics of a relation refers to its meaning resulting from the interpretation of attribute values in a tuple.

If the conceptual design is done carefully and the mapping procedure is followed systematically the relational schema design should have a clear meaning

be explained is an informal measure of how well the relation is designed.

explain its meaning

Do not combine attributes from multiple entity types and relationship types into a single relation

of employees and projects and the WORKS_ON relationship

to minimize the storage space used by the base relations
Grouping attributes into relation schemas has a significant effect on storage space.

in department number 5, we must enter all the attribute values of department 5 correctly so that they are consistent with the corresponding values for department 5 in other tuples
It is difficult to insert a new department that has no employees yet

Deletion Anomalies
If we delete from EMP_DEPT an employee tuple that happens to represent the last employee working for a particular department, the information concerning that department is lost from the database

Modification Anomalies
if we change the value of one of the attributes of a particular department—say, the manager of department 5—we must update the tuples of all employees who work in that department; otherwise, the database will become inconsistent

or modification anomalies are present in the relations

and may also lead to problems with understanding the meaning of the attributes

Guideline 3

As far as possible, avoid placing attributes in a base relation whose values may frequently be NULL

Example:

if only 15 percent of employees have individual offices, there is little justification for including an attribute Office_number in the EMPLOYEE relation; rather, a relation
EMP_OFFICES (Essn, Office_number) can be created to include tuples for only the employees with individual offices

or meaning of the attributes.

The database designers will use their understanding of the semantics (meaning) of the attributes of R—that is, how they relate to one another—to specify the functional dependencies that should hold on all relation states of R.

The main use of functional dependencies is to describe further a relation schema R by specifying constraints on its attributes that must hold at all times.

property of the relation schema R, not of a particular instance of R. Therefore, an FD must be defined explicitly by someone who knows the semantics of the attributes of R

One cannot determine which FDs hold and which do not unless the meaning of and the relationships among the attributes are clearly known and understood

property of the relation schema R, not of a particular instance of R. Therefore, an FD must be defined explicitly by someone who knows the semantics of the attributes of R

One cannot determine which FDs hold and which do not unless the meaning of and the relationships among the attributes are clearly known and understood