Data modeling - why do it?

Why?

Why invest time in creating a conceptual model of your data before putting it into a database?

facilitates the discussion on the shape of your future database with your team (PI?)
forms a basis for the design of the database
helps you think through potential pitfalls in the design of your future databas
is relatively fast to make and very fast/cheap to change

Goals

Capture all important information
No redundant information
Don’t model information that is derivable from other modeled information
Information is in a predictable, logical place

A proper Entity Relationship model leads to a set of logically coherent tables.

Entities, attributes, and relationships… Oh my!

An entity is something of interest - a “thing” or event. An entity has instances that are of interest to you, maybe observations?

An attribute is a single valued property detail of an entity. A specific piece of information that describes, quantifies, qualifies, classifies, or specifies an entity. An attribute has a value (data type). Values can change over time.

A relationship expresses how entities are mutually related and always exist between two entities (or one entity twice).

Entity relationship diagrams

An Entity Relationship Model is a list of all entities, attributes and relationships that are of importance for your purpose. The model also provides background information such as entity descriptions, data types and constraints.

An Entity Relationship Diagram (ERD) is a picture, a representation of the model or of a part of the model.

Representation

Entities are represented by rectangles with a title. Attributes are represented within the corresponding entity as a list below the title with primary keys noted. Relationships are represented by a line, connecting the entities. The shape of the end of the relationship line represents the degree of the relationship.

Your turn

Art galleries keep information about artists, their names (which are unique), birthplaces, age,and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that personâs unique name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like.

Your turn (cont’d)

Draw an ER Diagram for this scenario. (http://draw.io might help, or on paper is fine.)

What are the entities in this scenario?
What attributes do they have?
What are the relationships between entities?