Logical Data Modelling

Logical data modelling is the top-down design method often used in developing a database application. This approach has three stages:

•  Identify the entities that make up the system

•  Analyse the data to associate the data used by an organisation with particular entities.

•  Identify the relationships between entities

The systems analyst develops an entity-relationship model (ERM), which is shown graphically as an entity-relationship diagram (ERD).

EXAMPLE (examples of ERD's written by Phil James)

Entity diagram for college library borrowing system

• A loan is made for many books.
• A book must be part of only one loan.
• Loans are be made by many members.
• A member can make only one loan.
• A member can make many reservations (for a book).
• A reservation (for a book) can only be made by one member.
• A reservation can be for many books.
• A book may be a part of one reservation.

Cardinality and Optionality

(Explanations written by Phil James)

Note: Each of these is considered from the direction of A to B.

Cardinality

One to One

One to Many

Many to One

Many to Many

Optionality

Mandatory-to-Mandatory

Mandatory to Optional

Optional to Mandatory

Optional to Optional

Reading Entity relational diagrams

Looking at a complete ERD can sometimes make it hard to establish the relationship (association between) the entities. So it becomes necessary to break it down.

Looking at it from Entity A's perspective: A has a mandatory (must be) relationship with B; A has a one-to-many relationship with B.

Looking at it from Entity B's perspective: B has an optional (may be) relationship with A; B has a one-to-one relationship with A (no crow's foot at the other end).

Remember: The first step in designing an ER diagram is a proper understanding of the problem, which needs to be turned into an ER diagram - without this you cannot tackle the problem correctly. To develop the ER diagram you will need enterprise rules. These are rules about the enterprise (real world business) on which your database is going to be modelled. The enterprise rules for the above diagram are: that A must employ one or more B's but that each B may be employed by one A.

Activity

Entity Relationship Questions

(Questions written by Phil James)

1. What word describes the relationship that exists between two (or more) entities such as one-to-one, one-to-many, many-to-one, many-to-many?

• Optionality
• Entity
• Attribute
• Cardinality

2. What word describes the nature of the relationship i.e. whether it is optional or mandatory?

• Cardinality
• Entity occurrence
• Optionality
• Determinant

3. Look at the diagram below then answer the following questions:



4. Which one of the following does the boxes in the diagram represent?

• Attributes
• Entities
• Relationships
• Primary Key

5. Which one of the following does the lines in the diagram represent?

• Attributes
• Entities
• Relationships
• Primary Key

6. What is the nature of the relationship between D and C?

• One to One
• One to Many
• Many to One
• Many to Many

7. What is the nature of the relationship between A and D?

• One to One
• One to Many
• Many to One
• Many to Many

8. What is the nature of the relationship between B and C?

• One to One
• One to Many
• Many to One
• Many to Many

9. What is the nature of the relationship between A and B?

• One to One
• One to Many
• Many to One
• Many to Many

10. What type of relationship exists between A and B?

• Optional to Mandatory
• Mandatory to Optional
• Optional to Optional
• Mandatory to Mandatory

11. What type of relationship exists between A and D?

• Optional to Mandatory
• Mandatory to Optional
• Optional to Optional
• Mandatory to Mandatory

12. What do A, B, C and D represent in the diagram?

• Attribute descriptions
• Entity names
• Entity occurrences

13. Which of the following relationships are not acceptable in completed ER Diagrams?

• One to One
• One to Many
• Many to One
• Many to Many

14. A college has five teachers. Each teacher takes about three classes a day. What is the relationship between teachers and their classes?

• One to One
• One to Many
• Many to One
• Many to Many

15. There are many students in each class and students have several classes a day. What is the relationship between students and classes?

• One to One
• One to Many
• Many to One
• Many to Many

16. The college keeps a personal record of each student in the college. Which one of the following describes the relationship between each college personal record and its students?

• One to One
• One to Many
• Many to One
• Many to Many

17. Below is a table, which represents an Entity and its occurrences. Look at it closely then answer the following questions:

CUSTOMER

18. What is signified by the column marked Surname?

• A record
• A primary key
• An attribute
• An entity

19. What does the box containing 0125 represent on the second row of the table?

• A record
• A primary key
• An attribute occurrence
• An entity

20. What does the underline on Customer_No represent?

• A record
• A primary key
• An attribute
• An entity

21. What type of fields/attributes would we apply an index to in the table?

• Primary key
• Surname
• First Name
• Telephone No

Activity

In groups produce an entity relationship diagram on paper. You will be given one of the scenarios below:

•  An appointments database for a doctor's surgery

•  A stock control database including the sale of goods for a computer company

•  A lesson-booking database for a driving school

•  A sports club membership database

•  A college admissions database