Data Models

Question 1

Explain the importance of thinking through the design of a query before writing it in SQL

Answer 1

1. You will get more accurate results
2. It will speed up your work
3. You will have less re-work

Above all, think before you do. Ask yourself, what is the problem you’re trying to solve and then come up with a road map before coding.

An ER diagram is instrumental in the “think before you do” step. It will help you plan.

SQL for Data Science Week 1

Question 2

Describe what a database is

Answer 2

A container used to store and organize data. Think of a wall of file drawers.

SQL for Data Science Week 1

Question 3

What is a table made up of?

Answer 3

A table is made up of columns and rows

SQL for Data Science Week 1

Question 4

What is a data model?

Answer 4

• A data model organizes information
• A data model tells us how tables relate to each other.
• A data model usually represents a business process and it can help you understand a business process.
• A data model should represent a real world process as closely as possible.

SQL for Data Science Week 1

Question 5

What are the two types of data models?

Answer 5

1. A data model for prediction used by data scientists
2. A data model that represents how tables are organized in a database

SQL for Data Science Week 1

Question 6

What are 4 benefits of the relational model?

Answer 6

1. It simplifies the connections between the data
2. It allows you to retrieve and update data
3. It allows you to easily write queries against it
4. It allows you to write data to it

SQL for Data Science Week 1

Question 7

Define and describe the relational model

Answer 7

1. Shows the relationships between tables
2. Makes querying data easy and intuitive

SQL for Data Science Week 1

Question 8

Define and describe the transactional model

Answer 8

Used to simply store information. It is not built with later use in mind, so it is not conducive to querying and analysis.

SQL for Data Science Week 1

Question 9

Define an entity

Answer 9

An entity is one of these:
1. Person
2. Place
3. Thing
4. Event

They are distinguishable, unique and distinct.

For example, Heather is the entity

SQL for Data Science Week 1

Question 10

Define an attribute

Answer 10

Characteristic of an entity

A characteristic about Heather is she has green eyes.

SQL for Data Science Week 1

Question 11

Define a relationship

Answer 11

A relationship describes the association among entities.

For example:
- One-to-many
- Many-to-many
- One-to-one

SQL for Data Science Week 1

Question 12

Example of a one-to-one relationship

Answer 12

Manager to store

If there is only one manager per store

SQL for Data Science Week 1

Question 13

Example of a one-to-many relationship

Answer 13

Customer to invoices

SQL for Data Science Week 1

Question 14

Explain a many-to-many relationship

Answer 14

Students to classes

SQL for Data Science Week 1

Question 15

What is a primary key?

Answer 15

A column, or set of columns, that uniquely identify every row in the table

SQL for Data Science Week 1

Question 16

What is a foreign key?

Answer 16

One or more columns that can be used to identify a single row in another table.

SQL for Data Science Week 1

Question 17

Explain how ER diagrams are used to document and illustrate relationships

Answer 17

An ER diagram is composed of entities and specifies the relationships that can exist between instances of those entity types.

They show:
1. Relationships
2. Business Process
3. A visual representation of the database structure
4. Shows the links (primary keys/foreign key) between tables

SQL for Data Science Week 1

Answer 18

SQL for Data Science Week 1

Question 19

ER Diagram Notation Types

Answer 19

1. Chen Notation
2. Crow’s Foot Notation
3. UML Class Diagram Notation

SQL for Data Science Week 1

Question 20

Crow’s Foot Notation Example

Answer 20

SQL for Data Science Week 1

Question 21

Chen Notation Example

Answer 21

SQL for Data Science Week 1

Question 22

UML Class Diagram Notation

Answer 22

SQL for Data Science Week 1

Question 23

Example of the three types of ER Diagram Notation

Answer 23

SQL for Data Science Week 1