One to one relationships(1:1)
When a one to one relationship exists between two entity
classes, knowledge of the value of either class is sufficient to uniquely
identify an occurrence of the other class. Thus, it is very easy to establish a
chain of inference across a one to one relationship (Hernandez, 2013).
In fact, structures that can be represented by sets of entities with a one to
one relationship to each other often can be represented as a single
consolidated entity class.
Other types of Relationship
Optional Relationship: a
relationship is optional if it is not required that every occurrence of one
entity be associated with an occurrence of the related entity.
Mandatory relationship: a
relationship is mandatory if every occurrence in one entity class must be
associated with at least one occurrence in the related class.
(Elmasri and Navathe, n.d.)
A relationship should be considered mandatory only if we
can think of no valid situation in which the relationship could fail to exist.
For example, we might be tempted to say that all organizations must surely have
at least one member and, there-fore, the relationship from organizations to
students is mandatory. But what about a new organization that we are trying to
establish? Might we not have data to record about the organization even though
it does not yet have any members? What if an organization suspends operation
due to lack of interest, or for other reasons? If we expect the organization to
be revived in the future, we might want to retain our data about the
organization even though it has no members currently. A further distinction can
be drawn between relationships that are fixed and relationships that are
mandatory but not fixed in nature. With a mandatory relationship each
occurrence of one entity must be tied to some occurrence of the related entity
but the occurrence it is tied to may change.
Fixed relationship: With
a fixed relationship, once an occurrence of the entity is associated with a
given occurrence of the second entity (Hernandez, 2013). it
must remain associated with that specific occurrence. The relationship of
orders to customers is logically of this fixed type. The order is placed by a
specific customer and logically must always be related to that customer. If we
correctly associate an order with a customer, the order must continue to be
associated with that customer for as long as we retain it.
Intra-table relationship: A
relationship between occurrences of the same entity class. Relationships similar to those we have just
described can exist within a table to tie related occurrences of that entity
together. These are called intra-table relationships.
Processes are actions(verbs) rather than things(nouns),
therefore we cannot directly represent a process as an entity. However,
Processes cause changes in the status of the entities involved in those
Conceptual and Physical Design
mechanism for representing data structures. A number of data models are
available to support the conceptual and physical design processes. (Silberschatz, Korth and Sudarshan, 2011)
of designing a database focuses on how data will be represented and how units
and blocks of data will be organized and interconnected rather than on
specifying the values of specific data items.
design process builds a structure that can be used to store and process data
and is analogous to designing and building a factory to produce physical
products. Just as the factory must be designed and built before the products
can be produced, the database structure must be designed and implemented before
actual data items can be stored and processed.
designing a database, we first create a conceptual data model that describes
the structure of the data to be stored in the database without specifying how
and where it will be physically stored, or the physical methods used to
retrieve it. Once a conceptual design has been developed and analyzed, we move
on to the physical design stage. Here, alternative methods of physically
implementing the structures specified in the conceptual design are examined and
an appropriate method is selected and implemented. This produces a database
that supports the structures for storage and retrieval developed in the
conceptual design phase. These steps are roughly equivalent to first designing
a factory layout on paper, and then physically building the structure and
installing the equipment.
data model most commonly used to support the conceptual design stage is the
entity-relationship (E-R) model. Some form of the E-R model is used for the
conceptual design of most current database development projects regardless of
the data model that will be used for physical design and implementation. Data
models supporting the physical design stage include the hierarchical model, the
network model, the relational model, and the object-oriented model.
Design-The entity-relationship Model
conceptual data model must be able to represent entities and their attributes
and relationships that exist between those entities. (Elmasri and Navathe, n.d.) The entity-relationship model
was among one of the first methodologies developed for conceptual design of
databases. It has become by far the most widely used model for conceptual
entity-relationship, or E-R, model was developed by Peter Chen (1976) and it
has undergone substantial modification and refinement through the years. No
uniformly accepted set of notation exists for the elements of the E-R model.
Entities are represented as rectangles and attributes are represented as
elliptical shapes with lines connecting them to the entity to which they
shape represents something in an ERD:
Conceptual Designs in the relational database models
To physically implement a conceptual database design, we
must have physical structures that allow us to store and retrieve logically
related units of data both within entities and across related entity classes.
In other words, we must be able to create physically implementable structures
that correspond to all the logical structures rep-resented in our conceptual
The attribute, entity occurrence, and entity class
structures within entity classes correspond to the field, record, and file
structures of file-oriented processing. In relational databases, the terms
column, row, and table are commonly used to describe these units. A field or
column name identifies a specific attribute of interest, and the value of the
primary key attribute identifies a specific occurrence—the row of the table
identified by the primary key value. The entire set of data for an entity class
is stored in a table. The relational model requires that all tables be flat
files with records of a fixed size.
Relationships among entity types are represented in the
relational model by repeating identifying data to link related tables. The
relational model requires that all data structures be represented by sets of
tables (entities) that can have one to one or one to many relationships with
each other (Silberschatz, Korth and Sudarshan, 2011). In addition, each table must have a
primary key. All other attributes of a table must be functionally determined by
the primary key. Each entity is described by an appropriate table structure,
and these tables are interconnected by lines representing the relationships
that exist between them.
-Redmond, E., Wilson, J. and Carter, J.
(2012). Seven databases in seven weeks. Dallas, Tex. Raleigh, N.C.:
-Silberschatz, A., Korth, H. and Sudarshan,
S. (2011). Database system concepts. New York: McGraw-Hill.
-Augsten, N. and Bo?hlen, M.
(n.d.). Similarity joins in relational database systems.
-Elmasri, R. and Navathe, S.
(n.d.). Fundamentals of database systems.
-Hernandez, M. (2013). Database design
for mere mortals. Harlow: Addison Wesley.