COIS20026 Database Development

1
COIS20026 Database Development Management

• Week 3 The Relational Model
• Prepared by Angelika Schlotzer
• Updated by Satish Balmuri
• Updated by Tony Dobele

2
Week 3 The Relational Model

• Objectives
• describe use the terms relation, tuple,
  attribute, domain
• describe the various types of keys (primary,
  candidate, alternate, composite/concatenated)
• Explain the role of foreign keys
• list 6 properties of relations
• Define the entity integrity and referential
  integrity rules.

3
Objectives (contd)

• Transform an ERD to a logically equivalent set of
  relations, including
• Regular weak entity types
• relationships of different cardinality degree
• Associative entity types
• Supertype/subtypes
• Other ER modelling constructs

Note Unless otherwise mentioned all the
references of this lecture material are from the
prescribed course text book or images from
publishers.
4
Logical Design

• Logical design is the process of transforming the
  conceptual model /design (in our case the ERD)
  into a logical model/design. (McFadden, et al.,
  2004)

5
Relational Model

• Relational data model has 3 main components
• data structure - data organised into tables with
  rows and columns
• data manipulation - operations (using SQL
  language) used to manipulate stored data
• data integrity - facilities are included to
  specify business rules to maintain integrity of
  data as they are being manipulated

6
Some Definitions

• Relation - named, two-dimensional table of data
• fixed number of columns
• variable number of rows
• single, simple value at each intersection of row
  column
• shorthand text notation for its data structure
  is
• EMPLOYEE1(EmpID, Name, DeptName, Salary)

7
Definitions (contd)

• graphic representation of a relation is shown as
• Attributes (columns) - data items that are
  contained in the relation
• represent the data items that need to be stored
  for an entity
• each attribute can come from a different domain

EMPLOYEE1
EmpID
DeptName
Salary
Name
8
Six Properties of Relations (McFadden et al.,
2002, p 190)

• each relation in a database has a unique name
• an entry at each row column intersection is
  single-valued (atomic)
• each row is unique
• Each attribute within a table has a unique name
• sequence of columns from left to right is
  insignificant
• sequence of rows from top to bottom is
  insignificant

9
More Definitions

• Row (tuple) - represents an entity instance
• 1 row (tuple) for each relation instance
  currently in the database (can be zero)
• NULL -
• an attribute in a relation may not currently have
  a value associated with it
• represents the absence of a value ie the value
  is unknown
• the primary key cannot be null

10
Relational Keys

• Key -
• any attribute or group of attributes that can
  uniquely identify 1 row (tuple) in a relation
• Candidate key -
• any attribute that could act as a key for the
  relation
• often there is more than 1 attribute that could
  serve this purpose

11
Relational Keys

• Primary Key -
• the attribute(s) selected to function as the
  unique identifier for the relation
• should never contain more than the absolute
  minimum number of attributes required to uniquely
  identify a row
• Alternate Keys -
• attributes that were candidate keys but were not
  selected to be the primary key

12
Relational Keys

• Composite (Concatenated) Key -
• when more than 1 attribute of a relation is
  chosen to function as the primary key eg
• FLIGHT(FlightID, Date, PassengerNo)

13
Relational Keys

• Foreign Key -
• attribute in a relation of the database that
  serves as the primary key of another relation
  within the database
• occurs when relationships between 2 tables
  (relations) must be represented
• DEPARTMENT (DeptName, Location, Fax)
• EMPLOYEE1 (EmpID, Name, DeptName, Salary)

14
Figure 5-3 -- Schema for four relations (Pine
Valley Furniture)
15
Integrity Constraints

• Constraints are designed to assist in maintaining
  accuracy integrity of data contained in the
  database
• Domain Constraints
• all values in a column of a relation must be
  taken from the same domain

16
Entity Integrity

• Designed to assure that every relation has a
  primary key with valid data values (guarantees
  primary key cannot be null)
• Entity integrity rule
• No primary key attribute (or component of a
  primary key attribute) may be null.

17
Referential Integrity

• The relational model defines associations between
  tables through use of a foreign key
• The referential integrity constraint maintains
  consistency among rows of 2 relations

18
Referential Integrity Rule

• If there is a foreign key in a relation, either
  each foreign key value must match a primary key
  value in the other relation or else the foreign
  key value must be null

19
Figure 5-5 Referential integrity constraints
(Pine Valley Furniture)
Referential integrity constraints are drawn via
arrows from dependent to parent table
20
Transforming EERD into Relations

• Logical design involves transforming
  entity-relationship (and Extended ERD) diagrams
  developed during conceptual design into
  relational database schemas
• process has a set of well-defined rules (steps)
  to complete the conversion

21
Step 1 Map Regular Entities

• Recall that regular entities have independent
  existence
• Each regular entity is transformed into a
  relation
• name given to the relation is usually that of the
  entity type
• each simple attribute becomes an attribute in the
  relation
• entity type identifier becomes primary key of the
  relation

22
Figure 5-8 Mapping a regular entity
(a) CUSTOMER entity type with simple attributes
(b) CUSTOMER relation
23
Step 1 Map Regular Entities (contd)

• for composite attributes, only the simple
  attributes are included in the relation
• for multi-valued attributes, 2 new relations are
  created -
• the first relation containing all of the simple
  attributes
• a second relation containing the primary key
  attribute of the first relation as an attribute
  an attribute for the multi-valued attribute
• in the second relation these 2 attributes will
  become a concatenated primary key

24
Figure 5-9 Mapping a composite attribute
(a) CUSTOMER entity type with composite attribute
(b) CUSTOMER relation with address detail
25
Figure 5-10 Mapping a multivalued attribute
1 to many relationship between original
entity and new relation
26
Dealing with Multi-Valued Attributes (another
example)
GOLFER PlayerID Membership
GOLFER
PlayerID
Name
Address
GOLF_MEMBERSHIP
This relation will have 1 row for each club in
which a player has membership
PlayerID
Membership
27
Step 2 Map Weak Entities

• Recall that a weak entity cannot exist
  independently -
• has an identifying owner
• does not have its own complete identifier
• does have a partial identifier to distinguish
  among instances

28
Step 2 Map Weak Entities (contd)

• For each weak entity
• create a new relation containing -
• all the simple attributes of the weak entity
• only the simple attributes of any composite
  attributes
• include the primary key of the owner relation as
  a foreign key attribute
• make the primary key from the owner relation
  the partial identifier of the weak entity the
  concatenated primary key for the new relation

29
NOTE the domain constraint for the foreign key
should NOT allow null value if DEPENDENT is a
weak entity
Foreign key
Figure 5-11(b) Relations resulting from weak
entity
30
Step 3 Map Binary Relationships

• Procedure for this dependent on relationship
  degree cardinalities
• Map Binary One-to-Many Relationships
• for each 1M relationship
• first create the two participating entity
  relations
• include the primary key attribute of the 1
  relation as an attribute (foreign key) in the M
  relation (many side)

31
Figure 5-12 Example of mapping a 1M relationship
(a) Relationship between customers and orders
Note the mandatory one
32
Figure 5-12(b) Mapping the relationship
Again, no null value in the foreign keythis is
because of the mandatory minimum cardinality
Foreign key
33
Map Binary Many-to-Many Relationships

• For each MN (many-to-many) relationship -
• create the relations for the entities
• create a new relation -
• add the primary keys of the participating entity
  types as foreign keys
• become the concatenated primary key of the new
  relation
• add any non-key attributes associated with the
  MN relationship

34
Figure 5-13 Example of mapping an MN
relationship
(a) ER diagram (MN)
35
Figure 5-13(b) Three resulting relations
New intersection relation
Foreign key
Foreign key
36
Map Binary One-to-One Relationships

• Special case of 1M relationships
• Create 1 relation for each of the participating
  entities
• add the primary key of the entity type that has
  mandatory participation to the relation for the
  entity type that has optional participation in
  the relationship
• add any relationship attributes to the relation
  with the foreign key

37
Figure 5-14 Mapping a binary 11 relationship
(a) Binary 11 relationship
38
Figure 5-14(b) Resulting relations
39
Step 4 Map Associative Entities

• First create a relation for each participating
  entity type and the associative entity
• include the primary keys of the two regular
  entity relations as attributes of the associative
  relation
• these keys become the concatenated primary key if
  the associative relation has no identifier
• these keys become foreign keys if the associative
  relation has an identifier

40
Figure 5-16 Mapping an associative entity
(a) Associative entity
41
Figure 5-16(b) Three resulting relations
42
Step 5 Map Unary Relationships

• Recall that a unary relationship is a
  relationship between instances of a single entity
  type
• Unary 1M Relationships
• create a relation for the entity type
• within the relation you just created add the
  primary key attribute as a foreign key attribute
  (needs to have a different name)

43
Figure 5-17 Mapping a unary 1N relationship
(a) EMPLOYEE entity with recursive relationship
(b) EMPLOYEE relation with recursive foreign key
44
Unary MN Relationships

• First create 2 relations -
• one to represent the entity type in the
  relationship
• one to represent the MN relationship
• Associative relation has primary key consisting
  of 2 attributes (with different names) that both
  take their values from primary key of the
  relation created for the entity type
• add any nonkey attributes of the relationship to
  the associative relation

45
Figure 5-18 Mapping a unary MN relationship
(a) Bill-of-materials relationships (MN)
(b) ITEM and COMPONENT relations Note that
Item_No in COMPONENT table is a foreign key and
part of primary key
46
Step 6 Map Ternary Relationships

• Ternary relationship is a relationship among 3
  entity types
• The relationship is usually transformed in the
  ERD to an associative relationship
• create the relations for the entity types
• create an associative relation for the
  associative relationship
• include the primary keys of the other 3 relations
  (generally become the concatenated primary key
  for this relation)
• add attributes belonging to associative entity
  type

Figure 5.19
47
Figure 5-19 Mapping a ternary relationship
(a) Ternary relationship with associative entity
48
Figure 5-19(b) Mapping the ternary relationship
Remember that the primary key MUST be unique
49
Step 7 Map Supertype/ Subtype Relationships

• These relationship types are not directly
  supported by relational data model
• Most common strategy employed is
• create a separate relation for the supertype and
  each subtype
• assign the attributes common to all subtypes to
  the supertype
• for each subtype add the primary key of the
  supertype attributes unique to itself
• assign one (maybe more) attributes of the
  supertype to function as subtype discriminator

Figure 5.20 5.21
50
Figure 5-20 Supertype/subtype relationships
51
Figure 5-21 Mapping Supertype/subtype
relationships to relations
52
Summary

• The objective of logical design is to transform
  the conceptual model (ERD or EERD) into a logical
  data model with well-structured relations that
  minimise redundancies inconsistencies
  (anomalies)
• Integrity constraints assist in maintaining the
  accuracy integrity of data in the resulting
  database

53
Summary (contd)

• Each entity type relationship in the
  entity-relationship diagram is mapped into
  corresponding relations
• This mapping process has a well-defined sequence
  of steps that identify how to map each entity
  relationship type into well-structured relations

54
Summary (contd)

• Supertype/subtype relationships are not directly
  supported by the relational data model but,
• can be represented by creating a relation for the
  supertype each subtype and including a subtype
  discriminator attribute in the supertype

55
Next Week

• Next week we will be looking at Normalisation
• Ensure you do text book readings as per study
  guide.