complex entities and relationships case examples are from the book: lars fank databaser teori og...

Complex entities and relationships

Case examples are from the book:

Lars FankDatabaser

Teori og PraksisISBN 87-571-2397-7

Short repetition: Many-to-many, One-to-many,

trees, and network relationships:

Repetition: One-to-many relationships

CustomersCustomers

Orders Orders

Customer-order relation

Bachman diagram ER-diagram

Relational model of the one-to-many relationship:

Customer ID

Order table

Customer table

Order ID

AdressName

Primary key

Forign keyPrimary key

Etc.

DateCustomer ID

1

M

Repetition: Many-to-many relations.

OrdersOrders

Products Products

Order lines

Bachman diagram ER-diagram

Relational model of the many-to-many relationship:

Order ID

Order lines

Order table

Order ID

DateCustomer ID

Primary key

Forign key

P r i m a r y ……..k e y

Price

QuantityProduct ID

ER-diagram

Orders

Products

Order lines

Product table

Forign key

Product ID

Primary key

ProductName

M

M

Different illustrations of tree structure relationships:

Tree nodes

Relation-ships

Bachman diagram ER-diagram

Relational model of the tree structure relationship:

Node IDTree nodes table Parent node ID

Primary key

Tree nodes

M1

Forign key

Different illustrations of network structure relationships:

Network nodes

Relation-ships

Bachman diagram ER-diagram

Relational model of the network relationship:

Node ID

Branches table

Network nodes table

First Node ID

Node data

Primary key

Forign key

P r i m a r y ……..k e y

Relationship dataSecond Node ID

Network nodes

Forign key

MM

ER-diagram

Network nodes

Relationship branches

First node

Second node

Relationship degree

Type of relationship Relationship degreeOne-to-many relationship Binary

Many-to-many relationship Binary

One-to-one relationship Binary

Tree structure relationship Unary

Network relationship Unary

Order relationship Unary

Relationship of degree N N-ary

Different illustrations of simple relationships of order 3.

Customers

AccountsAccounts

Account managers

Relationshipof order

3

Bachman diagram ER-diagram

Relational model of order 3 relationships:

Customer IDRelationship of order 3

Customer table Customer ID

Account IDManager ID

Primary key

Forign key

P r i m a r y ……..k e y

….

Account IDCustomerName

Customers

Account managers

Relationship of order 3

Account manager table

Forign key

Manager ID

Primary key

ManagerName

Forign key

Accounts Account ID ….Interrest

Primary key

M

M

M

Different illustrations of simple relationships of degree 3.

ER and Bachman illustrations of the relationship of order three.

An ER relationship need not be implemented as a table or a forign key

How would you convert the Bachman diagram to an ER-diagram with normalized tables?

Entities and relationships must not be connected directly in ER-diagrams.

Therefore, ER-diagrams need a pseudoentity if two relationships need to be connected

ER relationships may be viewed as Bachman entities depending on the application area.

End of session

Thank you !!!Thank you !!!