2/21/2012

ICSE 6203:
Database Management Systems

Lecture 3a
Logical Database Design and the
Relational Model
(part 1)

2/21/2012 Dr. E. A. Kalinga, CSE, CoICT, UDSM 1

What Questions are to be Answered?

• How is data represented in the relational

model?
• What integrity constraints can be expressed?
• How can data be created and modified?
• How can data be manipulated and queried?
• How can we create, modify, and query tables
using SQL?
• How do we obtain a relational database
design from an ER diagram?
• What are views and why are they used?

1
 2/21/2012

What Questions are to be Answered?

• Key concepts:
—relation, schema, instance, tuple, field, domain, degree,
cardinality;
—SQL DDL, CREATE TABLE, INSERT, DELETE, UPDATE;
—integrity constraints, domain constraints, key constraints,
PRIMARY KEY, UNIQUE, foreign key constraints, FOREIGN
KEY;
—referential integrity maintenance, deferred and immediate
constraints; relational queries;
—logical database design, translating ER diagrams to
relations, expressing ER constraints using SQL;
—views, views and logical independence, security;
—creating views in SQL, updating views, querying views,
dropping views

The Relational Model

• Was introduced in 1970 by Dr. E. F. Codd (of IBM)
• Commercial relational databases began to appear in the 1980s
• Today relational databases have become the dominant
technology for database management
• Data is represented in the form of tables, and the model has 3
components
1. Data structure – data are organised in the form of tables
with rows and columns
2. Data manipulation – powerful operations (using the SQL
language) are used to manipulate data stored in the relations
3. Data integrity – facilities are included to specify business
rules that maintain the integrity of data when they are
manipulated

2
 2/21/2012

The Relational Model

• A relation is a named, two-dimensional table of data

• Every relation has a unique name, and consists of a
set of named columns and an arbitrary number of
unnamed rows
• An attribute is a named column of a relation, and
every attribute value is atomic (single).
• Every row is unique, and corresponds to a record
that contains data attributes for a single entity.
—The order of the columns is irrelevant.
—The order of the rows is irrelevant.

The Relational Structure

• The main construct for representing data in the relational model is a
relation.
• A relation consists of a relation schema and a relation instance.
— The relation instance is a table, and
— The relation schema describes the column heads for the table.
• The schema specifies the relation's name, the name of each
field relation (or column, or attribute), and the domain of each
field.
• A domain is referred to in a relation schema by the domain name
and has a set of associated values.
• We can express the structure of a relation by a Tuple, a shorthand
notation
• The name of the relation is followed (in parentheses) by the names
of the attributes of that relation,
Students(sid: string, name: string, login: string, age: integer, gpa: real)

3
 2/21/2012

The Relational Structure

• An instance of a relation is a set of tuples, also called
records, in which each tuple has the same number of
fields as the relation schema.
• A relation instance can be thought of as a table in
which each tuple is a row, and all rows have the same
number of fields.
• An instance of the Students relation appears in next
figure.
—The instance contains six tuples and has, as we expect
from the schema, five fields.
—Note that no two rows are identical.
• This is a requirement of the relational model-each
relation is defined to be a set of unique tuples or rows.

The Relational Structure

Login

4
 2/21/2012

A Relational Database
• A relational database is a collection of relations with
distinct relation names.
• The relational database schema is the collection of
schemas for the relations in the database.
• For example, a university database with relations
called Students, Faculty, Courses, Rooms, Enrolled,
Teaches, and Meets_In.
• An instance of a relational database is a collection of
relation instances, one per relation schema in the
database schema; of course, each relation instance
must satisfy the domain constraints in its schema.

Relational Keys

• Must be able to store and retrieve a row of data

in a relation, based on the data values stored in
that row

• A primary key is an attribute (or combination of

attributes) that uniquely identifies each row in a
relation.
• The primary key in the EMPLOYEE1 relation is
EMP_ID (this is why it is underlined) as in:
EMPLOYEE1(Emp_ID, Name, Dept, Salary)

5
 2/21/2012

Composite and Foreign Keys

• A Composite key is a primary key that consists of
more than one attribute.
• e.g., the primary key for the relation DEPENDENT
would probably consist of the combination Emp-ID
and Dependent_Name
• A Foreign key is used when we must represent the
relationship between two tables and relations
• A foreign key is an attribute (possibly composite)
in a relation of a database that serves as the
primary key of another relation in the same
database

Foreign Keys
• Consider the following relations:
EMPLOYEE1(Emp_ID, Name, Dept_Name, Salary)
DEPARTMENT(Dept_Name, Location, Fax)

• The attribute Dept_Name is a foreign key in

EMPLOYEE1.
• It allows the user to associate any employee with
the department they are assigned to.
• Some authors show the fact that an attribute is a
foreign key by using a dashed underline.

6
 2/21/2012

Removing Multivalued Attributes From Tables

• In the table, an entry at the intersection of each
row and column is atomic (single-valued) - there
can be no multivalued attributes in a relation, an
example of this would be if each employee had
taken more than one course, e.g.:
Emp_ID Name Dept_Name Course
A1 Fred Bloggs Info Sys Delphi

• To avoid this, we should create a new relation

(EMPLOYEE2) which has a new instance for each
course the employee has taken, e.g.:
—A1 Fred Bloggs Info Sys Delphi
—A1 Fred Bloggs Info Sys VB

Example of a Database
• The structure of the database is described by the
use of a conceptual schema, which is a description
of the overall logical structure of a database.
• There are two common methods for expressing a
conceptual schema:
a) Short text statements, in which each relation is
named and the names of its attributes follow in
parentheses
b) A graphical representation, in which each relation is
represented by a rectangle containing the attributes for
the relation.

7
 2/21/2012

Expressing the Conceptual Schema

• Text statements have the advantage of simplicity,
whilst the graphical representation provides a better
means of expressing referential integrity constraints
(discussed later)
• Here is a text description for four relations:

CUSTOMER(Customer_ID, Customer_Name, Address, City, State, Zip)

ORDER(Order_ID, Order_Date, Customer_ID)

ORDER_LINE(Order_ID, Product_ID, Quantity)

PRODUCT(Product_ID, Product_Description, Product_Finish,

Standard_Price, On_Hand)

Expressing the Conceptual Schema

• Note that the primary key for ORDER_LINE is a
composite key consisting of the attributes Order_ID
and Product_ID
• Also, Customer_ID is a foreign key in the ORDER
relation, allowing the user to associate an order with
a customer
• ORDER_LINE has two foreign keys, Order_ID and
Product_ID, allowing the user to associate each line
on an order with the relevant order and product
• A graphical representation of this schema is shown
in the following Fig.

8
 2/21/2012

Schema for Four Relations (Pine Valley Furniture)

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)

Integrity constraints
• These help maintain the accuracy and integrity of the
data in the database
• Domain Constraints -
—a domain is the set of allowable values for an attribute.
• Domain definition usually consists of 4 components:
—domain name,
—meaning,
—data type,
—size (or length), allowable values/allowable range (if
applicable)
• Entity Integrity ensures that every relation has a
primary key, and that all the data values for that primary
key are valid. No primary key attribute may be null.

9
 2/21/2012

Entity integrity
• In some cases a particular attribute cannot be
assigned a data value, e.g. when there is no
applicable data value or the value is not known
when other values are assigned

• In these situations we can assign a null value to an

attribute (null signifies absence of a value)

• But still primary key values cannot be null – the

entity integrity rule states that “no primary key
attribute (or component of a primary key
attribute) may be null”

Integrity constraints
• A database is only as good as the information stored
in it, and a DBMS must therefore help prevent the
entry of incorrect information.
• An integrity constraint (IC) is a condition specified
on a database schema and restricts the data that can
be stored in an instance of the database.
• If a database instance satisfies all the integrity
constraints specified on the database schema, it is a
legal instance.
• A DBMS enforces integrity constraints, in that it
permits only legal instances to be stored in the
database.

10
 2/21/2012

Integrity constraints
• A Referential Integrity constraint is a rule
that maintains consistency among the rows of
two relations – it states that any foreign key
value (on the relation of the many side) MUST
match a primary key value in the relation of the
one side. (Or the foreign key can be null)
• In the following Fig., an arrow has been drawn
from each foreign key to its associated primary
key. A referential integrity constraint must be
defined for each of these arrows in the schema

Referential integrity constraints (Pine Valley Furniture)

Referential
integrity
constraints are
drawn via arrows
from dependent to
parent table

11
 2/21/2012

Referential integrity

• How do you know if a foreign key is allowed

to be null?
• In this example, as each ORDER must have a
CUSTOMER the foreign key of Customer_ID
cannot be null on the ORDER relation
• Whether a foreign key can be null must be
specified as a property of the foreign key
attribute when the database is designed

Referential integrity

• Whether foreign key can be null can be complex

to model, e.g. what happens to order data if we
choose to delete a customer who has submitted
orders? We may want to see sales even though
we do not care about the customer anymore.
• 3 choices are possible:
1. Restrict
don’t allow delete of “parent” side if related
rows exist in “dependent” side, i.e. prohibit
deletion of the customer until all associated
orders are first deleted

12
 2/21/2012

Referential integrity
2. Cascade
automatically delete “dependent” side rows that
correspond with the “parent” side row to be
deleted, i.e. delete the associated orders, in which
case we lose not only the customer but also the
sales history
3. Set-to-Null
set the foreign key in the dependent side to null if
deleting from the parent side - an exception that
says although an order must have a customer_ID
value when the order is created, Customer_ID can
become null later if the associated customer is
deleted [not allowed for weak entities]

Action assertions

• Are business rules such as “A person may

purchase a ticket for the celebrity football
game only if that person is a season-ticket
holder”

• There are various techniques for defining and

enforcing such rules, that will be discussed
later

13
 2/21/2012

Creating relational tables

• These example tables are created using

CREATE TABLE statements from SQL
• In practice, they are usually created in the
implementation phase later on in the
development process
• However, we create them here to explain
some concepts
• One table is created for each table shown
in the relational schema (previous Fig.)

Creating relational tables

• Each attribute is defined, taking the data type

and length from the domain definitions
• For example, the attribute Customer_Name can
be defined as a VARCHAR (variable character)
type with length 25
• By specifying NOT NULL, each attribute can be
constrained from being assigned a null value
• The primary key for each table is specified using
the PRIMARY KEY clause at the end of each
table definition

14
 2/21/2012

Creating relational tables

CREATE TABLE CUSTOMER

(CUSTOMER_ID VARCHAR(5) NOT NULL
CUSTOMER_NAME VARCHAR(25) NOT NULL
Etc.
PRIMARY KEY (CUSTOMER_ID);

Creating relational tables

CREATE TABLE ORDER

(ORDER_ID CHAR(5) NOT NULL
ORDER_DATE DATE NOT NOT NULL
CUSTOMER_ID VARCHAR(5) NOT NULL
PRIMARY KEY (ORDER_ID)
FOREIGN KEY (CUSTOMER_ID) REFERENCES
CUSTOMER(CUSTOMER_ID);

15
 2/21/2012

Creating relational tables

• Referential integrity constraints are easily defined using
the graphical schema

• An arrow originates from each foreign key and points to

the related primary key in the associated relation

• In SQL, a FOREIGN KEY REFERENCES statement

corresponds to one of these arrows

• The foreign key CUSTOMER_ID references the primary

key of CUSTOMER, which is also CUSTOMER_ID

• Although here the foreign and primary keys have the

same name, this need not be the case – but the foreign
and primary keys must be from the same domain

Creating relational tables

• The ORDER_LINE table illustrates how to

specify a primary key when that key is a
composite attribute of two foreign keys:

CREATE TABLE ORDER_LINE

(ORDER_ID CHAR(5) NOT NULL
PRODUCT_ID CHAR(5) NOT NULL
QUANTITY INT NOT NULL
PRIMARY KEY(ORDER_ID, PRODUCT_ID)
FOREIGN KEY (ORDER_ID) REFERENCES ORDER(ORDER_ID)
FOREIGN KEY (PRODUCT_ID) REFERENCES
PRODUCT(PRODUCT_ID);

16
 2/21/2012

Well-structured relations

• A well-structured relation contains minimal

redundancy and allows users to insert, modify
and delete the rows in a table without errors
and inconsistencies
• Redundancies in a table (such as more than
one entry for each EMPLOYEE) may result in
errors and inconsistencies (anomalies) when
the table is updated
• 3 Types of anomaly are possible, insertion,
deletion and modification anomalies

Insertion anomaly

• Insertion anomaly – looking at EMPLOYEE2:

A1 Fred Bloggs Info Sys Delphi
A1 Fred Bloggs Info Sys VB

• Suppose that we want to add a new employee –

the primary key for this relation is the combination
of Emp_ID and Course_Title. Therefore, to insert a
new row, the user must supply both these values
(since primary keys cannot be null or nonexistent)
• This is an anomaly, since the user should be able to
enter employee data without supplying course data

17
 2/21/2012

Deletion and modification anomalies

• Suppose that the data for a particular

employee are deleted from the table
• This results in losing the information that this
employee completed a particular course
• This results in losing the information that this
course was offered – deletion anomaly
• If employee A1 changes the department they
work in, this must be recorded in both the rows
of the table otherwise the data will be
inconsistent – modification anomaly

Anomalies

• These anomalies indicate that EMPLOYEE2

is not a well-structured relation
• We should use normalisation theory
(discussed later) to divide EMPLOYEE2 into
2 relations, one called EMPLOYEE1 and
one called EMP_COURSE that keeps track
of the course details

18
 2/21/2012

Transforming ER diagrams into relations

• This can be done automatically by many CASE

tools, but it is important to understand because:
 Case tools often cannot model complex data
relationships such as ternary relationships
and supertype/subtype relationships. For
these situations you may have to perform
these steps manually
 Sometimes alternative solutions exist, and
you must choose the best
 You must be able to quality check the CASE
tool results

Remember entity types!

• Regular entities – have an independent existence

and generally represent real-world objects =
[rectangles with a single line]
• Weak entities cannot exist on there own, they
exist with an identifying relationship with an owner
regular entity type = [[rectangles with a double
line]]
• Associative entities are formed from many-to-
many relationships between other entity types =
[<rectangle enclosing the diamond relationship
symbol>]

19
 2/21/2012

Step 1: map regular entities

• Each regular entity type in an ER diagram is

transformed into a relation
• The name given to the relation is generally the
same as the entity type
• Each simple attribute of the type becomes an
attribute of the relation
• The identifier of the entity type becomes the
primary key of the corresponding relation
• The following 2 Figs. show an example of this

Mapping a regular entity

(a) CUSTOMER
entity type with
simple
attributes

(b) CUSTOMER relation

20
 2/21/2012

Composite attributes

• When a regular entity type has composite

attributes, only the simple component
attributes of the composite attribute are
included in the new relation

• The following Fig. Shows a variation on the

previous one, where Customer_Address is
represented as a composite attribute with
components Street, City, State and Zip

Mapping a composite attribute

(a) CUSTOMER
entity type with
composite
attribute

(b) CUSTOMER relation with address detail

21
 2/21/2012

Multi-valued attributes
• Here two new relations (rather than one) are
created

• First relation contains all of the attributes of the

entity type except the multivalued attribute
• Second relation contains two attributes that form
the primary key of the second relation
• The first of these is the primary key for the first
relation, which becomes a foreign key in the
second relation
• The second is the multivalued attribute

Multi-valued attributes

• In the following Fig. EMPLOYEE has ‘Skill’ as a

multi-valued attribute
• The first relation EMPLOYEE has the primary key
Employee_ID
• The second relation EMPLOYEE_SKILL has the two
attributes Employee_ID and Skill, which form the
primary key
• The relationship between foreign and primary keys
is indicated by the arrow in the figure

22
 2/21/2012

Mapping a multivalued attribute

Multivalued attribute becomes a separate relation with foreign key

(b)

1 – to – many relationship between original entity and new relation

Step 2: map weak entities

• You must already have created a relation
corresponding to the identifying type
• 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
• Then 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
and the partial identifier of the weak entity type

23
 2/21/2012

Map weak entities

• The following figure shows the weak identity type

DEPENDENT and its identifying entity type EMPLOYEE,
linked by the identifying relationship ‘Has’

• The attribute Dependent_Name (the partial identifier for

this relation) is a composite attribute with components
First_Name, Middle_Initial and Last_Name – so we
assume that for a given employee these items will
uniquely identify a dependent.

• The primary key of DEPENDENT consists of four

attributes: Employee_ID, First_Name, Middle_Initial and
Last_Name. The foreign key relationship with its
primary key is indicated by the arrow in the Fig.

Example of mapping a weak entity

(a) Weak entity DEPENDENT

24
 2/21/2012

Relations resulting from weak entity

NOTE: the domain constraint

for the foreign key should
NOT allow null value if
DEPENDENT is a weak
entity

Foreign key

Composite primary key

Step 3: map binary relationships

• The procedure for representing

relationships depends on both the degree
of the relationships (unary, binary, ternary)
and the cardinalities of the relationships

25
 2/21/2012

Map binary one-to-many (1:M) relationships

• First create a relation for each of the two entity

types participating in the relationship
• Next include the primary key attribute(s) of the
entity on the one-side as a foreign key in the
relation that is on the many-side

• ‘Submits’ relationship in the following Fig. shows

the primary key Customer_ID of CUSTOMER (the
one-side) included as a foreign key in ORDER (the
many-side) (signified by the arrow)

Example of mapping a 1:M relationship

Relationship between customers and orders

Note the mandatory one

26
 2/21/2012

Figure 5-12(b) Mapping the relationship

Again, no null value in

the foreign key…this is
because of the mandatory
minimum cardinality

Foreign key

Map binary many-to-many (M:N) relationships

• If such a relationship exists between entity types A

and B, we create a new relation C, then include as
foreign keys in C the primary keys for A and B, then
these attributes become the primary key of C

• In the following Fig., first a relation is created for

VENDOR and RAW_MATERIALS, then a relation
QUOTE is created for the ‘Supplies’ relationship –
with primary key formed from a combination of
Vendor_ID and Material_ID (primary keys of
VENDOR and RAW_MATERIALS). These are foreign
keys that point to the respective primary keys

27
 2/21/2012

Example of mapping an M:N relationship

ER diagram (M:N)

The Supplies relationship will need to become a separate relation

Three resulting relations

Composite primary key

New
Foreign key intersection
relation
Foreign key

28
 2/21/2012

Map binary one-to-one relationships

• These can be viewed as a special case of one-to-many
relationships. Firstly, two relations are created, one for
each of the participating entity types

• Secondly, the primary key of one of the relations is

included as a foreign key in the other relation

• In a 1:1 relationship, the association in one direction is

nearly always optional one, whilst the association in
the other direction is mandatory one

• You should include in the relation on the optional side

of the relationship the foreign key of the entity type
that has the mandatory participation in the 1:1
relationship

Map binary one-to-one relationships

• This approach avoids the need to store null values in
the foreign key attribute

• Any attributes associated wit the relationship itself are

also included in the same relation as the foreign key

• The following Fig. Shows a binary 1:1 relationship

between NURSE and CARE_CENTER, where each care
centre must have a nurse who is in charge of that
centre – so the association from care centre to nurse is
a mandatory one, while the association from nurse to
care centre is an optional one (since any nurse may or
may not be in charge of a care centre)

29
 2/21/2012

Map binary one-to-one relationships

• The attribute Date_Assigned is attached to the

In_Charge relationship
• Since CARE_CENTER is the optional participant,
the foreign key (Nurse_In_Charge) is placed in
this relation – it has the same domain as
Nurse_ID and the relationship with the primary
key is shown.
• The attribute Date_Assigned is also located in
CARE_CENTER and would not be allowed to be
null

Mapping a binary 1:1 relationship

Binary 1:1 relationship

30
 2/21/2012

Resulting relations

Step 4: map associative entities

• When a user can best visualise a relationship as an
associative entity (rather than an M:N relationship)
we follow similar steps to mapping an M:N
relationship
• Three relations are created, one for each of the two
participating entity types and the third for the
associative entity
• The relation formed is called the associative relation
• The next step depends on whether on the ER
diagram an identifier was assigned to the
associative entity

31
 2/21/2012

Identifier not assigned

• Here the default primary key for the

associative relation 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
• Sometimes an identifier (called a surrogate identifier
or key) is assigned to the associative entity type on
the ER diagram. There are 2 possible reasons:
a) The associative identity type has a natural
identifier that is familiar to end users
b) The default identifier (consisting of identifiers for
each of the participating entity types) may not
uniquely identify instances of the associative
identity
• The process for mapping the associative entity is
now modified

32
 2/21/2012

Identifier assigned

• As before a new associative relation is created to

represent the associative entity
• However, the primary key for this relation is the
identifier assigned on the ER 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
• The following Fig. Shows the associative entity
type SHIPMENT that links the CUSTOMER and
VENDOR entity types

Identifier assigned
• Shipment_No has been chosen as the identifier for
two reasons:
1. Shipment_No is a natural identifier for this entity
that is very familiar to end users
2. The default identifier consisting of the combination
of Customer_ID and Vendor_ID does not uniquely
identify the instances of shipment. In fact, a given
vendor will make many shipments to a given
customer
• The new associative relation is named SHIPMENT,
with primary key Shipment_No. Customer_ID and
Vendor_ID are included as foreign keys in this
relation

33
 2/21/2012

Mapping an associative entity

Associative entity

Three resulting relations

34