Logical Database Design
and the Relational Model
1
Logical database design
▪ Logical database design is the process of transforming the
conceptual data model into a logical data model.
▪ Conceptual data modelling
• is about understanding the organization
• getting the requirements right
▪ Logical database design
• is about creating stable database structures
• correctly expressing the requirements in a technical language
2
Logical database design
▪ The relational data model is a form of logical data model.
▪ E-R data model is not a relational data model.
▪ E-R model was developed for:
• Understanding data requirements.
• Understanding business rules about the data.
▪ E-R model was NOT developed for structuring the data for
sound database processing, which is the goal of logical
database design.
3
The Relational Data Model
▪ First introduced in 1970 by E. F. Codd.
▪ RDBMSs have become the dominant technology for
database management.
▪ Hundreds of RDBMS products for computers ranging from
smartphones and personal computers to mainframes.
4
The Relational Data Model
▪ The relational data model consists of the following three
components:
1. Data structure
–Data are organized in the form of tables, with rows and
columns.
2. Data manipulation
–Powerful operations (typically implemented using the SQL
language) are used to manipulate data stored in the relations.
3. Data integrity
–The model includes mechanisms to specify business rules that
maintain the integrity of data when they are manipulated.
5
Relational Data Structure
▪ Relation
• A named two-dimensional table of data.
• Each relation (or table) consists of:
–a set of named columns, and
–an arbitrary number of unnamed rows
• An attribute is a named column of a relation.
• Each row of a relation corresponds to a record that
contains data (attribute) values for a single entity.
6
Example Relation EMPLOYEE1
▪ Attributes describing employees: EmpID, Name, DeptName, and
Salary.
▪ The five rows of the table correspond to five employees.
7
Shorthand Notation
▪ We can express the structure of a relation by using a
shorthand notation in which the name of the relation is
followed (in parentheses) by the names of the attributes in
that relation.
EMPLOYEE1 (EmpID, Name, DeptName, Salary)
8
Relational Keys
▪ Store and retrieve a row of data in a relation, based on the data
values stored in that row.
▪ Every relation must have a primary key.
▪ Primary key
• An attribute or a combination of attributes that uniquely
identifies each row in a relation.
• Designate a primary key by underlining the attribute
name(s).
–EMPLOYEE1 (EmpID, Name, DeptName, Salary)
▪ Composite key
• A primary key that consists of more than one attribute.
9
Relational Keys
▪ Foreign key
• An attribute in a relation that serves as the primary key of
another relation in the same database.
• Designate a foreign key by using a dashed underline.
▪ Example:
• EMPLOYEE1(EmpID, Name, DeptName, Salary)
• DEPARTMENT(DeptName, Location, Fax)
• The attribute DeptName is a foreign key in EMPLOYEE1.
• It allows a user to associate any employee with the
department to which he or she is assigned.
10
Properties of Relations
1. Each relation (or table) in a database has a unique name.
2. An entry at the intersection of each row and column is
atomic.
• No multivalued attributes are allowed in a relation.
3. Each row is unique.
• No two rows in a relation can be identical.
4. Each attribute (or column) within a table has a unique
name.
5. The sequence of columns (left to right) is insignificant.
6. The sequence of rows (top to bottom) is insignificant.
11
Removing Multivalued Attributes from Tables
▪ A table containing one or more multivalued attributes is not a
relation.
▪ Eliminate the multivalued attributes in by filling the relevant data
values into the previously vacant cells.
12
Removing Multivalued Attributes from Tables
▪ The table has only single-valued attributes and now satisfies the
atomic property of relations.
▪ Any undesirable properties?
13
Schema for four relations at Pine Valley Furniture
Company
14
Schema for four relations at Pine Valley Furniture
Company
▪ Text description:
CUSTOMER(CustomerID, CustomerName, CustomerAddress,
CustomerCity, CustomerState, CustomerPostalCode)
ORDER(OrderID, OrderDate, CustomerID)
ORDER LINE(OrderID, ProductID, OrderedQuantity)
PRODUCT(ProductID, ProductDescription, ProductFinish,
ProductStandardPrice, ProductLineID)
15
Figure 4-3 Schema for four relations (Pine Valley Furniture Company)
Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)
Combined, these are a composite
primary key (uniquely identifies the
order line)…individually they are
foreign keys (implement M:N
relationship between order and product)
16 16
Integrity Constraints
▪ Rules limiting acceptable values and actions.
▪ Facilitate maintaining the accuracy and integrity of data in
the database.
▪ Major Types:
• Domain constraints
• Entity integrity
• Referential integrity
17
Domain Constraints
▪ All of the values that appear in a column of a relation must
be from the same domain.
▪ A domain is the set of values that may be assigned to an
attribute.
▪ A domain definition consists of: domain name, meaning,
data type, size (or length), and allowable values.
18
19
Entity Integrity
▪ Ensure that every relation has a primary key.
▪ Ensure that the data values for that primary key are all
valid.
▪ In some cases, a particular attribute cannot be assigned a
data value:
• There is no applicable data value.
• Applicable data value is not known when values are assigned.
▪ The relational data model allows us to assign a null value to
an attribute.
20
Entity Integrity
▪ Null
• A value that may be assigned to an attribute when no
other value applies or when the applicable value is
unknown.
• Null is not a value, but rather it indicates the absence of
a value.
▪ Entity integrity rule
• A rule that states that no primary key attribute (or
component of a primary key attribute) may be null.
21
Referential Integrity
▪ In the relational data model, associations between tables
are defined through the use of foreign keys.
▪ Referential integrity constraint
• A rule that states that either each foreign key value must
match a primary key value in another relation or the
foreign key value must be null.
▪ In the graphical version of the relational schema, an arrow
is drawn from each foreign key to the associated primary
key.
22
Referential Integrity
23
Well-structured relation
▪ A relation that contains minimal redundancy and allows
users to insert, modify, and delete the rows in a table
without errors or inconsistencies.
▪ Redundancies in a table may result in errors or
inconsistencies (called anomalies)
24
Anomalies
▪ Anomaly
• An error or inconsistency that may result when a user attempts to
update a table that contains redundant data.
▪ Types:
1. Insertion anomaly
– occurs when certain attributes cannot be inserted into the database without the
presence of other attributes.
2. Deletion anomaly
– exists when certain attributes are lost because of the deletion of other attributes.
3. Modification anomaly
– exists when one or more instances of duplicated data is updated, but not all.
25
Anomalies
26
Insertion anomaly
▪ Suppose that we need to add a new employee to
EMPLOYEE.
▪ The primary key for this relation is the combination of
EmpID and CourseTitle.
▪ Therefore, to insert a new row, the user must supply values
for both EmpID and CourseTitle (because primary key
values cannot be null or nonexistent).
▪ This is an anomaly because the user should be able to enter
employee data without supplying course data.
27
Deletion anomaly
▪ Suppose that the data for employee number 140 are
deleted from the table.
▪ This will result in losing the information that this employee
completed a course (Tax Acc) on 12/8/2015.
▪ In fact, it results in losing the information that this course
had an offering that completed on that date.
28
Modification anomaly
▪ Suppose that employee number 100 gets a salary increase.
▪ We must record the increase in each of the rows for that
employee (two occurrences); otherwise, the data will be
inconsistent.
29
Transforming EER Diagrams into Relations
▪ Logical design
• Transform the E-R (and EER) diagrams into relational
database schemas.
▪ The inputs to this process are the entity-relationship (and
enhanced E-R) diagrams.
▪ The outputs are the relational schemas.
30
Three Types of Entities
▪ Regular entities are entities that have an independent
existence and generally represent real-world objects, such
as persons and products. Regular entity types are
represented by rectangles with a single line.
▪ Weak entities are entities that cannot exist except with an
identifying relationship with an owner (regular) entity type.
Weak entities are identified by a rectangle with a double
line.
▪ Associative entities are formed from many-to-many
relationships between other entity types. Associative entities
are represented by a rectangle with rounded corners. 31
Step 1: Map Regular Entities
▪ Each regular entity type in an E-R diagram is transformed into a
relation with same name.
▪ Each simple attribute of the entity type becomes an attribute of
the relation.
▪ The identifier of the entity type becomes the primary key of the
corresponding relation.
▪ Composite Attributes
• Only the simple components of the composite attribute are
included in the new relation as its attributes.
32
Example of mapping a composite attribute
33
Step 1: Map Regular Entities
▪ Multivalued Attributes
• When the regular entity type contains a multivalued attribute,
two new relations are created.
• The first relation contains all of the attributes of the entity
type except the multivalued attribute.
• The second relation contains two attributes that form the
primary key of the second relation.
–The first attribute is the primary key from the first relation,
which becomes a foreign key in the second relation.
–The second is the multivalued attribute.
–The name of the second relation should capture the meaning
of the multivalued attribute. 34
Example of mapping an entity with a multivalued
attribute
35
Step 2: Map Weak Entities
▪ Create a relation corresponding to the identifying entity type
using Step 1.
▪ For each weak entity type, create a new relation and include all
of the simple attributes (or simple components of composite
attributes) as attributes of this relation.
▪ Include the primary key of the identifying relation as a foreign
key attribute in this new relation.
▪ The primary key of the new relation is the combination of this
primary key of the identifying relation and the partial identifier
of the weak entity type.
36
Example of mapping a weak entity
37
Surrogate primary key
▪ A serial number or other system-assigned primary key for a
relation.
▪ A surrogate key is usually created to simplify the key
structures.
▪ Example:
• Create a new attribute (called DependentID) to use as a
surrogate primary key of the DEPENDENT relation:
DEPENDENT(DependentID, EmployeeID, FirstName,
MiddleInitial, LastName, DateOfBirth, Gender)
38
Surrogate primary key
▪ Whenever a surrogate key is created, the natural key is always
kept as nonkey data in the same relation.
▪ The natural key has organizational meaning that has to be
captured in the database.
▪ Surrogate keys mean nothing to users, so they are usually never
shown to the user.
▪ Instead, the natural keys are used as identifiers in searches.
39
When to Create a Surrogate Key
▪ There is a composite primary key.
▪ The natural primary key is inefficient.
• It may be very long and hence costly for database software to
handle if it is used as a foreign key that references other tables.
▪ When the natural primary key cannot be guaranteed to
remain unique over time.
• Names or titles.
40
Step 3a: Map Binary One-to-Many Relationships
▪ For each binary 1:M relationship, create a relation for each
of the two entity types using the procedure described in
Step 1.
▪ Then, include the primary key attribute (or attributes) of the
entity on the one-side of the relationship as a foreign key in
the relation that is on the many-side of the relationship.
–The primary key migrates to the many side.
41
Example of mapping a 1:M relationship
Note the mandatory one
No null value in the
foreign key because of
the mandatory minimum
Foreign key cardinality.
42
Step 3b: Map Binary Many-to-Many Relationships
▪ Suppose that there is a binary many-to-many (M:N)
relationship between two entity types, A and B.
▪ For such a relationship, create a new relation, C.
▪ The primary keys of A and B are included as foreign key
attributes in C. Also, these attributes become the composite
key of C.
▪ Any nonkey attributes that are associated with the M:N
relationship are included as simple attributes in relation C.
43
Example of mapping a M:N relationship
The Completes relationship will need to become a separate relation.
44
Example of mapping a M:N relationship
45
Map Binary One-to-One Relationships
▪ Create two relations, one for each of the participating entity
types.
▪ The primary key of one of the relations is included as a
foreign key in the other relation.
46
Map Binary One-to-One Relationships
▪ Often in 1:1 relationships, one direction is optional, whereas
the association in the other direction is a mandatory one.
▪ Include a foreign key in the relation on the optional side of
the relationship referencing the primary key of the entity
type that has the mandatory participation in the 1:1
relationship.
▪ This approach will prevent the need to store null values in
the foreign key attribute.
▪ Include any attributes associated with the relationship in the
same relation as the foreign key.
47
Example of mapping a binary 1:1 relationship
48
Step 4: Map Associative Entities
▪ When a data modeler encounters a many-to-many
relationship, he or she may choose to model that
relationship as an associative entity in the E-R diagram.
▪ The first step is to create three relations: one for each of
the two participating entity types and a third for the
associative entity.
• Relation formed from the associative entity is called the
associative relation.
▪ The second step then depends on whether on the E-R
diagram an identifier was assigned to the associative entity.
49
Step 4: Map Associative Entities
▪ Identifier not Assigned
• The default primary key for the associative relation is a
composite key that consists of the two primary key attributes
from the other two relations. These attributes are then
foreign keys that reference the other two relations.
▪ Identifier Assigned
• The primary key for the associative relation is the identifier
assigned on the E-R diagram (rather than the default key).
The primary keys for the two participating entity types are
then included as foreign keys in the associative relation.
50
Example of mapping an associative entity
51
Example of mapping an associative entity
52
Example of mapping an associative entity with an
identifier
53
Example of mapping an associative entity with an
identifier
54