Entity Relationship Diagrams

1

• Entity Relationship Diagrams
• (continued ..)

2
Objectives Week 8

• At the end of this lecture you should
• Fully understand how to develop ERDs
• Be able to convert an Entity Relationship diagram
  to RDM
• Be prepared to undertake assignment two ITC114.

3
Where are we up to?

• In the last lecture we looked at
• Looking at the various models (conceptual,
  internal, external and physical)
• Understanding the various components of ERDs
• Drawing very simply ERDs this was followed up
  in the tutorial.
• Understanding more about relationships.

4
Types of relationships

• Remember that there are three main types of
  relationships
• 1. 11 relationships
• 2. 1M relationships
• 3. MN relationships

5
Relationships

• A relationship
• Is an association between two entities that is
  important to the system
• It describes how the entities in the association
  behave with respect to each other.
• Occur only between entities, not attributes
• Relationships can involve more than one entity
• A relationship is represented by a diamond.
• Relationships can involve more than two entities.

6
11 relationships

• Occurs when an entity of one of the types is
  associated with exactly one entity of the other
  type.
• Example
• Suppose you are modelling a chain of video shops
  you find out that a video shop only has one
  manager. This could be modelled as a 11
  relationship

Looks after
Manager
Shop
1
1
7
11 relationships

• Consider a lecturer who is only allocated one
  office on campus and similarly an office can only
  be used by one lecturer.
• That is
• Each lecturer must be assigned only one office
• No office may be assigned to more than one
  lecturer
• Some offices may be unassigned

has
Lecturer
Office
1
1
8
What happens when these are converted to RDM?

• 11 relationships are definitely a unique
  situation.
• They are a realisation that you need to store
  information about both entities.
• The alternative to store all the information in
  one entity but this does NOT make sense?
• So to link the TWO entities together requires the
  primary key of one to be placed in the other?
• BUT WHICH PRIMARY KEY????

9
Reconsider the Lecturer Office example

• Without knowing the specific attributes of the
  respective entities the questions would be
• Would you place the lecture_id into the office
  relation?
• OR
• Would you place the office_id into the lecturer
  relation?
• ANSWER
• You need to fully understand the requirements of
  the system to answer this question
• BUT
• Most likely the foreign key would be within the
  Lecturer relation RATHER than the Office
  relation..

10
1M relationships

• Occurs when an entity of one of the types can be
  associated with multiple entities of the another
  type.
• Example
• What is the relationship between a hotel and a
  guest?
• A hotel can have many guests, but a guest can
  only be in one hotel
• This could be modelled as a 1M relationship

Can have
Hotel
Guest
1
M
11
1M relationships another example

• Example 2
• What is the relationship between a course and a
  student?
• A course has many students, but a student can
  only be in one course
• This again can be modelled as a 1M relationship

enrols in
Student
Course
M
1
12
MN relationships

• Occurs when many entity can be associated with
  many entities of the another type.
• Example
• Consider students enrolled in a university
  course, effectively a student can do many
  subjects, and a subject can be done by many
  students

Studies
Student
Subject
M
N
13
MN relationships another example

• Example 2
• What is the relationship between a product and an
  order?
• A product can appear on many orders, and an
  order can have many products
• This again can be modelled as a MN relationship

enrols in
Order
Product
M
N
14
Types of Relationships

• There are three types
• UNARY
• BINARY
• TERNARY

15
Relationships -3 basic types

• Unary relationship - an association within a
  single entity.

requirement
SUBJECT
16
Binary relationship

• Is the most common relationship
• Exists when 2 entities are associated.

SUPPLIER
PART
M
N
17
Components of ERDs
Ternary relationship - exists when 3 entities are
associated. Relatively rare.
SuppliedProject parts
SUPPLIER
PROJECT
PART
18
Cardinality

• Cardinality
• Expresses the specific number of entity
  occurrences associated with one occurrence of a
  related entity.
• Typically is a function of organisation policy.
• Often just shown as 1, M or N

19
Another representation for many.

• Alternate representation of many

Doctor
Patients
has
Can be included on a E-R diagram as brackets
specifying the actual number of occurrences.
Student
Result
takes
(1,1)
(0,36)
1
M
20
Relationships Optional or Mandatory

• Basically we are still discussing business
  rules in particular a relationship can be
  Optional or Mandatory.
• Optional does not have to exist or occur - it is
  shown by a small round circle.
• Mandatory must exist or occur in the
  relationship shown by a filled in small round
  circle.

21
Relationships - Optionality

• Optionality
• Defines the involvement of the relationship
  between two entities.

undertakes
SUBJECT
STUDENT
M
N
(0,300)
(1,4)
22
Mandatory relationships

• A situation where one entity can not occur with
  out the other .. by default this is implied in
  your diagram however where explicitly required it
  is drawn in.

Student
Result
Obtains
1
M
23
Developing ERDs ....

• Consider the following
• A painter may paint many paintings. To be
  considered a painter in the ARTIST database, the
  painter must have painted at least one painting.
• Each painting is painted by one (and only one)
  painter.
• A painting may or may not be exhibited in a
  gallery.

24
Answer
paints
Painter
Painting
M
1
M
Is placed in
1
Gallery
25
Converting ERDs to Relational Tables

• On your ERD add attributes specifically for each
  entity
• Determine the primary keys for each entity
• Each entity will become a relational table then
  for
• 1M relationship
• the primary key on the ONE entity is added as a
  foreign key to the MANY side entity
• The relationship is then basically discarded
• MN relationship
• The relationship also becomes to a table
• The keys of this new table are the primary keys
  of the connecting entities.
• Other attributes may be assigned to the
  composite entity as required

26
Example for 1M to RDM

• Attributes of Sales Rep (SalesRep_ID,
  SalesRep_name)
• Attributes of Customer (Cust_ID, Custname,
  address)
• Converting to a RDM
• SalesRep (SalesRep_ID, SalesRep_name)
• Customer (Cust_ID, Custname, address,
  SalesRep_ID)

Sales Rep
Customer
has
1
M
27
Another example of 1M

• Attributes of Book (Book_Code, Book_name, Date)
• Attributes of Publisher (Pub_ID, Pub_name)
• Converting to a RDM
• Book (Book_Code, Book_name, Date, PuB_ID)
• Order Publisher (Pub_ID, Pub_name)

Book
Publisher
has
M
1
28
Another example of a MN to RDM

• Attributes for Student (Stud_ID, Stud_name)
• Attributes of Subject (Subj_No, Subj_Name)
• grade????
• Converting to RDM
• Student (Stud_ID, Stud_name)
• Subject (Subj_No, Subj_Name)
• Result (Stud_ID, Subj_No, grade)

Student
has
Subject
M
N
29
Composite Entity

• Alternate name Intersection entity or Bridge
  entity.
• created when the relationship is MN.
• Remember that attributes are made up of
• a. the two primary keys of the connecting
  entities
• b. any other attributes specific to this new
  entity.

30
Converting a MN to 2 x 1M relationships

• If you have a MN relationship it is possible to
  convert it to TWO 1M relationships.
• Exercise convert the following ERD to one that
  contains no MN relationships.
• Attributes for Order (Order_ID, Order_date)
• Attributes of Part (Part_No, Part_Name)
• No_ordered????
• Need to add a new entity between ORDER and PART

Order
has
Part
M
N
31
Reducing a MN relationship to 2 x 1M
relationships
Order
Quantity
has
1
M
M
The key to the NEW entity (Quantity) is a
concatenation of the keys from the two connecting
entities (Order and Part). Therefore the primary
key to Quantity is Order_No Part_No
is for
1
Part
32
Now converting the previous ERD to RDM

• Attributes for Order (Order_ID, Order_date)
• Attributes of Part (Part_No, Part_Name)
• Attributes of Quantity (No_ordered)
• Converting to RDM
• Order (Order_ID, Order_date)
• Part (Part_No, Part_name)
• Quantity (Order_ID, Part_no, No_ordered)
• FOREIGN KEY Order_ID REFERENCES TABLE Order
• FOREIGN KEY PART_No REFERENCES TABLE Part

33
Now try this for yourself ..

• Exercise Convert the following MN relationship
  to one containing 2 x 1M relationship.
• Attributes for Student (Stud_ID, Stud_name)
• Attributes of Subject (Subj_No, Subj_Name)
• grade????

Student
has
Subject
M
N
34
Exercise Determine the RDM from this
information.

• Student_Id, Student_Name, Subject_Name,
  Subject_Code, Student_Grade_in_Subject,
  Assessment_Item_Number, Assessment_Item_Mark

35
Another example for you to attempt.

• A library keeps records of loans of books to
  borrowers. Each borrower is identified by a
  borrower no., and each copy of a book by an
  accession number (the library may have more than
  one copy of any given book). The name and
  address of each borrower is held such as overdue
  loan reminders, can be sent when necessary. The
  information about books is the title, author(s),
  publisher, publication date, ISBN, purchase price
  and current cost, For analysis of usage patterns
  data about old loans is kept for five years.
• Draw the corresponding ERD

36
ERD relating to previous information
37
The RDM associated with the ERD

• PUBLISHER
• publisher_id, publisher_name, address, phone
• BORROWER
• borrower_no, name, address
• COPY
• accession_no, isbn, purchase_price
• FOREIGN KEY (isbn) references BOOK
• BOOK
• isbn, title, publisher_id, publish_date,
  current_price
• FOREIGN KEY (publisher_id) references PUBLISHER
• BOOK_AUTHOR ( WRITES)
• isbn, author_id
• FOREIGN KEY (isbn ) references BOOK
• FOREIGN KEY (author_name ) references AUTHOR
• LOAN
• accession_no, date_borrowed, date_returned,
  borrower_no
• FOREIGN KEY (accession_no ) references COPY
• FOREIGN KEY (borrower_no ) references BORROWER
• AUTHOR

38
Produce an ERD from the following information

• The Airport Authority in a small nation requires
  a database to store information about airlines,
  pilots and planes. There is only one airport but
  several airlines use it.
• For each airline the identification code, full
  name and phone number of its local office is
  required.
• Several types of plane use the airport and for
  each type the fuel capacity and maximum range is
  to be recorded.
• Each pilot works for only one airline. For each
  pilot, their name, date of birth, address, the
  types of plane they can fly and their pilots
  licence number is to be recorded.
• Further information must be recorded to enable
  the production of a report listing the following
  details for each plane that uses the airport
• Plane identification code,
• Plane type,
• Date of manufacture,
• Owning airline.