4. Database Design and the E-R Model

Question 1

What’s the point of the design process?

Answer 1

• Need to meet the aims of the enterprise
• Specification of user requirements
• Interaction with domain experts required

Question 2

What are the 3 main phases of the design process?

Answer 2

• Conceptual design phase (ER diagram)
• Logical design phase (map ER to Relational / OO / …)
• Physical design phase (file organisation, indexing, …)

Question 3

How can a database be modelled relative to entities?

Answer 3

A database can be modelled as a collection of entities, with relationships among entities

Question 4

What is an entity?

Answer 4

An object that exists and is distinguishable from other objects
e.g specific person

Question 5

What is an attribute?

Answer 5

Entities have descriptive properties called attributes

e.g people have names and addresses

Question 6

What is an entity set?

Answer 6

A set of entities of the same type that share the same set of attributes
e.g set of all persons

Question 7

What are the 5 attribute types?

Answer 7

• Simple, which compromise of a single component part
e.g salary
• Composite, which compromise multiple component parts
e.g address is made up of house name, street etc
• Single-valued, can only have one value per entity
e.g date of birth
• Multi-valued, may have more than one value per entity
e.g phone number for home, work etc
• Derived, attributes are computed from other attributes
e.d age is calculated from date of birth and current date

Question 8

What is a relationship?

Answer 8

An association that exists between entities, and like entities, relationships are uniquely identifiable
e.g a specific person works at a specific company

Question 9

What is a relationship set?

Answer 9

A set of relationships between one or more entity sets

e.g set of all pairs of persons working for companies

Question 10

What is a binary relationship set?

Answer 10

Involves two entity sets, most relationship sets in a database system are binary

Question 11

What is a N-ary relationship set?

Answer 11

An example of this is students work on research projects under the guidance of an instructor

Question 12

What are multiplicity constraints?

Answer 12

Number of times an entity can or must take part in a particular type of relationship

Question 13

What is participation?

Answer 13

The minimum number of times an entity must take part in a relationship set
• Total participation means an entity must take part in the relationship set
• Partial participation means an entity may or may not take part in the relationship set

Question 14

What is cardinality?

Answer 14

The maximum number of times an entity can take part in a relationship set

Question 15

What are the 4 types of mappings for cardinality constraints?

Answer 15

• One to one
• One to many
• Many to one
• Many to many

Question 16

What is a candidate key?

Answer 16

A candidate key of an entity set is a minimal set of attributes that uniquely identifies each entity
e.g Consider module with attributes code, title, and credits. Candidate keys are code and title

Question 17

What is a primary key?

Answer 17

A primary key of an entity set is the candidate key that is chosen to uniquely identify each entity
e.g Primary key for module is code

Question 18

What is a weak entity set?

Answer 18

An entity set with no candidate keys, so it depends on the existence of an identifying entity set(s)
• Weak entity set must have total participation in relationship with identifying entity set(s)
• Must be one-to-many relationship from each identifying set to the weak entity set

Question 19

What is a discriminator?

Answer 19

The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.

Question 20

How would you form the primary key of a weak entity set?

Answer 20

The primary key of a weak entity set is formed by the primary key(s) of the identifying entity set(s), plus the weak entity set’s discriminator.