Entity Relationship Diagrams / Core Concepts

Covering week 2's content on core concepts, different attribute types and degrees and cardinality

Three broad stages of Database Design

Conceptual: Building model independent of DBMS. Logical: Create database in given DBMS. Physical: How this database is stored in hardware.

What is an Entity Relationship Model? (ERM)

A systematic way of describing and defining a business process. It’s modelled as entity sets that are linked by relationships that express dependencies and requirements between them.

ER modelling is what stage of DB design?

Conceptual

What do we use to notate Relationships?

Verbs

How do we name entity sets?

Use a singular noun that is: meaningful and unique

What is the naming convention for Attributes?

camelCasing!

What are characteristics of PK’s?

1) Non-null value 2) unique 3) not change/become null

What is a Candidate Key?

When entities have more than one attribute to be the PK; these sets are called ‘candidate keys’, and only one can be chosen.

What do we do when we have a set of candidate keys?

Select only one, and the rest are ‘alternate keys’.

What do we do if none of the candidate keys are suitable?

Introduce

What is a ‘Simple’ attribute?

Cannot be subdivided, examples are Gender or Marital status.

What is a ‘Composite’ attribute?

Composed of several parts, examples are address and names.

Are the Simple and Composite attributes good for operational design?

They reduce redundancy and inconsistencies, make retrieval and usage easier. —> Creating additional attributes for an entity is viable.

What is a ‘Multi-valued’ attribute?

Multiple values possible, examples are qualifications and different phone numbers.

Are 'Multi-valued’ attributes good for operational design?

They reduce redundancy and inconsistencies, easy usage and retrieval. —> Not the best to create additional attributes for an entity.

What are ‘Derived’ attributes?

Values that are calculated from other attributes, such as age and ordertotal.

Is a ‘Derived’ attribute good for operational design?

Normally not stored, this uses resources and data.