2. ER Diagrams – 4marks

Question 1

Why is conceptual modelling important?

Answer 1

Without conceptual models, it is very difficult to communicate database designs to (non-technical) users - This lack of communication may result in user’s data requirements being missed or incorrect requirements being captured

Question 2

The type of conceptual model used in this course is…

Answer 2

Entity Relationship (ER) Model, by drawing ER diagrams (ERD’s).

Question 3

What is a Universes of Discourse (UoD)?

Answer 3

The UoD is the relevant portion of the real world to be modelled by the database, i.e a mini world
The database to be built will not model everything in the world but everything in the UoD

Question 4

An entity has the following properties…

Answer 4

• Real-world object distinguishable from other objects (e.g a student, car, job, subject, building etc.)
• is described by a set of attributes.
• On an ER diagram, shown as entity name (noun) in a rectangle

Question 5

An attribute can be of the types…

Answer 5

• Composite Vs. Simple Attributes
• Single-valued Vs. Multi-valued Attributes
• Derived Vs. Stored Attributes

Question 6

In general attributes are represented on an ER diagram by

Answer 6

a circle with the attribute name.

Question 7

Key attributes have the following properties…

Answer 7

• Key (or uniqueness) constraints
• Key attribute’s values are distinct for each individual entity
• Key must hold for every possible extension of the entity type
• Multiple keys are possible
• On an ER diagram, a key attribute has its name underlined inside the oval

Question 8

Value set attributes have the following properties…

Answer 8

• Specifies the set of values that may be assigned to a particular attribute of an entity (eg: Employee Age: Integers between 21 & 65 or Vehicle Registration Number: String of 3 alphabets followed by 3 integers)
• Value sets map to relational domains
• Value sets are not displayed on the ER diagram

Question 9

NULL valued attributes have the following properties and types…

Answer 9

Properties
• Applies if a particular entity does not have an applicable value for an attribute
Types of Null Values
• Not Applicable (eg: Tertiary-Degree is Not applicable for a person with no university education)
• Unknown (eg: Home-Phone is Not known even if it exists)
• Missing (eg: Person’s Height: Not known at present time)

Question 10

Composite attributes are…

Answer 10

• attributes can be divided into smaller parts which represent simple attributes with independent meaning (eg: name can be broken into First name and Last name)

Question 11

Compare Single and multivalued attributes…

Answer 11

• Single value only allows one value (eg: Gender is M or F, not both) whereas multivalued can have more than one (eg: Degree = {BSc, BInfTech}
• On an ER diagram, single have a single bordered circle, while multivalued have a double border.

Question 12

Derived attributes have the following properties…

Answer 12

• Can be derived from related attribute values: (Age  Date - B-day)
• On an ERD, represented by a broken line border circle.

Question 13

Constraints which can be defined using ER model include

Answer 13

• Cardinality Constraints
• Participation Constraints
(Together called “Structural Constraints” )

Question 14

Cardinality ratios for ER are:

Answer 14

• 1:1
• 1:N
• M:N

Question 15

Consider the conditions:
One employee can only work in one department, but one department can have many employees.
What is the cardinality of this relationship and on which side of the relationship is each side of the ratio drawn?

Answer 15

ANSWER:
It is a 1:N relationship
The 1 goes on the side of the department and the N goes on the side of the employee.

Question 16

Participation constraints have the following properties…

Answer 16

They are either:
• TOTAL Participation (Existence Dependency) (eg: Constraint: Every employee must work for a department)
• PARTIAL Participation (eg: Constraint: Not every employee is a manager)
• Represented on ER by double lines (partial only double on one side of the relationship)

Question 17

Attributes of 1:1 or 1:N relationships…

Answer 17

Can (probably should) be migrated to one of the participating entities

Question 18

Attributes of M:N…

Answer 18

• CAN NOT Be migrated to the participating entities.

* Must have identities of the participating attributes must also added (during later mapping).

Question 19

Weak entities have the following properties…

Answer 19

• do not have key attributes of their own
• identified uniquely only by considering the primary key of another “Owner” entity
• relationship type that relates a weak entity to its owner is called the “Identifying relationship”
• Represented on ERD by a double line, otherwise the same as the usual entity rectangle with name.
• Also, on ERD, the relationship relating to it’s owner is also a double line (on normal diamond for relationship).

Question 20

Extended ER (EER) includes additional attributes of:

Answer 20

Superclass and subclass

Question 21

Super class and subclasses have the following properties…

Answer 21

• Attributes of a superclass are inherited by the subclasses.
• Entities in the same class have the same attributes
• Subclass can have its own specific attributes & relationships
• Every entity in a subclass is a member of its super class(es)
• Can be overlapping (could be both) or disjoint (must be one or the other)

Question 22

A relationship on an ER is represented by:

Answer 22

• A diamond with the relationship name inside.