Relational Database Design: Data Modeling for Beginners Part 1 of 2

Summary

In this two-part video series, Tim Jones from Accelerate Computer Training introduces beginners to relational database design and data modeling. The focus is on planning the structure of databases, emphasizing the importance of understanding entities, attributes, and relationships to avoid data inconsistencies and redundancy. Key concepts like primary and foreign keys are discussed, and the necessity of a blueprint for database construction is highlighted. The video aims to equip non-technical users with the skills to model real-world scenarios into effective data structures, setting the stage for part two where an Entity-Relationship Diagram will be created.

Takeaways

• 📚 The video series focuses on teaching relational database design and data modeling for beginners, emphasizing the structure rather than the interface.
• 👨‍🏫 Tim Jones from Accelerate Computer Training is the instructor, aiming to help non-technical users understand relational database architecture.
• 🔍 The agenda includes defining the need for relational databases, learning to plan a database, and understanding key concepts like entities, attributes, and relationships.
• 📊 The importance of using an Entity-Relationship Diagram (ERD) is highlighted as a blueprint for planning a database's structure.
• 🔑 Key fields like primary and foreign keys are crucial for linking tables and ensuring data integrity in relational databases.
• 🏗️ The video stresses the importance of planning before building a database, comparing it to a construction crew needing a blueprint.
• 📈 The benefits of using multiple tables in a relational database are explained, such as avoiding inconsistent data entry and eliminating redundant storage.
• 🎓 The process of data modeling is introduced as the method for translating real-world scenarios into structured data within a database.
• 🔍 The video provides a practical example of identifying entities and attributes using the process description of a classical music compositions database.
• 📝 The script guides viewers through the process of creating a process description and identifying potential entities and attributes within it.

Q & A

What is the main focus of the video series 'Relational Database Design, Data Modeling for Beginners'?

The main focus of the video series is to teach the skills and strategies used to plan the structure of new relational database systems, emphasizing on the structure of what the database will contain rather than its interface or appearance.

Who is the target audience for the video series?

The target audience for the video series is non-technical users who need to track related sets of information and wish to improve their understanding of relational database architecture.

What are the key skills that will be taught in the video series?

The video series will teach skills such as identifying entities and attributes in real-world scenarios, identifying relationships between entities and determining their cardinality, and drawing an Entity-Relationship Diagram (ERD).

Why is it important to use multiple tables in a relational database?

Multiple tables in a relational database are important to avoid inconsistent data entry, eliminate redundant data storage, and ensure data consistency by storing each value only once and referencing it multiple times.

What is the role of primary and foreign keys in relational databases?

Primary keys play a role in automatically assigning a unique identifier to each record in a table, while foreign keys are used to reference the primary key of another table, which helps in relating records of data to each other.

Why is planning a relational database before building it crucial?

Planning a relational database before building it is crucial because it provides a blueprint to follow, ensuring that the database is structured correctly to avoid issues with data inconsistency and redundancy, much like a construction crew needs a blueprint to build a structure.

What is the difference between an entity and an attribute in the context of database design?

An entity is a thing or idea about which data is stored, while an attribute is a characteristic or descriptor of an entity. An entity has multiple attributes that describe it, whereas an attribute is a single value.

What is the purpose of creating a separate table for Composer, Era, and Style in the example of the Classical Music Compositions database?

Creating separate tables for Composer, Era, and Style ensures that each value is entered and stored only once, which guarantees consistency and avoids redundancy. It also allows for easier updates and references in the main Composition table.

How does the video series define the term 'relational database'?

A relational database is defined as a collection of related data about entities, organized into separate tables, where each table is made of columns and rows, with each column being a field and each row being a record.

What is the significance of the term 'Data Modeling' as used in the video series?

Data Modeling, as used in the video series, refers to the process of turning real-world scenarios into sound data structures, which involves identifying entities, attributes, and their relationships to create a blueprint for a database.

What is the importance of a process description in the database planning phase?

A process description is important in the database planning phase as it provides a clear goal and a finish line for the database development, ensuring that all stakeholders agree on what the database needs to address and store.

Outlines

00:00

📚 Introduction to Relational Database Design

Tim Jones introduces a two-part video series on relational database design for beginners. The focus is on the structure of databases rather than their interface. The target audience is non-technical users who need to manage related sets of information. The session aims to define the need for relational databases and teach skills like identifying entities and attributes, determining relationships and cardinality, and drawing an Entity-Relationship Diagram (ERD). The importance of primary and foreign keys is discussed, and the necessity of planning before building a database is emphasized, comparing it to a construction project needing a blueprint.

05:00

🔑 The Power of Multi-Table Structure in Relational Databases

The video explains the concept of a relational database, which organizes related data into separate tables to avoid inconsistent and redundant data entry. It uses the example of a 'Classical Music Compositions' table to illustrate the problem of inconsistent data and the solution of using multiple tables for entities like Composer, Era, and Style. Each entity is stored once with a unique identifier (primary key), and these IDs are referenced in other tables to ensure data consistency. The video also addresses the potential concern of users seeing only numbers instead of actual names, assuring that a well-designed database would present user-friendly interfaces while maintaining the underlying structure.

10:01

🎓 Data Modeling: From Real-World Scenarios to Data Structures

The paragraph delves into the process of data modeling, which involves converting real-world scenarios into structured data models. It discusses the two phases of database development: the virtual (planning) phase and the physical (building) phase. During the planning phase, items to be tracked are called entities, and their characteristics are attributes. The paragraph emphasizes the importance of starting with a process description, which outlines the purpose of the database. It then guides viewers through identifying entities and attributes from a process description, using the example of a classical music collection database. The video teaches how to differentiate between entities and attributes, and how to optimize data structure by normalizing data and using ID numbers to link related records.

15:02

👶 Entity Identification in Educational Database Design

This section continues the data modeling process with a new process description focused on an educational setting. It involves identifying potential entities such as Student, Class, Teacher, and Incidents. The paragraph discusses the decision-making process for naming entities and attributes, emphasizing the importance of choosing understandable and consistent terminology. It also covers the strategy of creating separate tables for entities that have many related records to avoid redundancy and ensure data consistency. The video provides a practical approach to entity-relationship mapping, showing how to link entities like Student to Class, Teacher, and Incident through unique identifiers.

20:03

🏫 Streamlining Data with Entity-Relationship Diagrams

The final paragraph summarizes the process of identifying entities and their relationships in the context of an educational database. It touches on the entities of Classroom and Grade, explaining the rationale for treating them as separate entities to maintain data integrity and facilitate updates. The paragraph reinforces the principle of 'One fact, one field' in database design and the benefits of creating a comprehensive Entity-Relationship Diagram (ERD). It sets the stage for Part 2 of the series, where the ERD will be constructed, providing a clear blueprint for the database's structure.

Keywords

💡Relational Database

A relational database is a type of database that organizes data into tables with rows and columns, where each row represents a record and each column represents a field. The video emphasizes the importance of relational databases for organizing related sets of information efficiently. It is used as the central theme of the video, with the speaker teaching how to plan the structure of new relational database systems.

💡Data Modeling

Data modeling is the process of creating a blueprint for a database by defining its structure, including the tables, fields, and relationships between data. In the video, data modeling is presented as a crucial skill for planning a relational database, involving the identification of entities and attributes, and the creation of an Entity-Relationship Diagram (ERD).

💡Entity

An entity in the context of the video refers to a thing or concept about which data is stored, such as 'Composition' or 'Composer'. Entities are represented as tables in a relational database, and each table contains records (rows) that represent instances of that entity. The video discusses how to identify entities in a real-world scenario and how they are used to structure the database.

💡Attribute

An attribute is a characteristic or descriptor of an entity. In the video, attributes are discussed as the individual pieces of data that describe an entity, such as the 'Title' or 'Year' of a 'Composition'. Attributes are represented as columns in a database table and are used to define the fields within each table.

💡Primary Key

A primary key is a unique identifier for each record in a database table. The video explains that primary keys, such as 'ID', are used to automatically assign a unique number to each record, ensuring that each entry is distinct and can be accurately referenced.

💡Foreign Key

A foreign key is a field in a database table that refers to the primary key of another table, creating a link between the two tables. The video discusses how foreign keys are used to establish relationships between entities, such as using a 'Composer ID' in the 'Composition' table to reference the 'Composer' entity.

💡Cardinality

Cardinality refers to the type of relationship between entities in a database, indicating the number of records in one table that can be associated with a record in another table. The video mentions cardinality in the context of identifying relationships between entities and designing the database structure accordingly.

💡Entity-Relationship Diagram (ERD)

An Entity-Relationship Diagram is a visual representation of the structure of a database, showing the entities, their attributes, and the relationships between them. The video highlights the importance of ERDs in planning and illustrating the proposed structure of a database before building it.

💡Normalization

Normalization is the process of organizing data in a database to minimize redundancy and dependency. The video explains how normalization is achieved by splitting data into separate tables and using primary and foreign keys to link them, ensuring data consistency and efficiency.

💡Inconsistent Data Entry

Inconsistent data entry refers to the issue of different users entering the same information in various ways, leading to discrepancies in the database. The video uses examples, such as different spellings of a composer's name, to illustrate the problems caused by inconsistent data entry and how a relational database structure can help avoid this issue.

💡Process Description

A process description is a written statement that outlines the purpose and requirements of a proposed database. In the video, the speaker emphasizes the importance of creating a process description as a starting point for planning a database, involving stakeholders and ensuring a clear understanding of the database's goals.

Highlights

Introduction to Relational Database Design for Beginners

Focus on database structure rather than interface

Target audience is non-technical users needing to track related information sets

Agenda includes defining relational databases, planning databases, and drawing ERDs

Importance of key fields like primary and foreign keys in relational databases

Comparison of database creation to construction without a blueprint

Definition of a relational database as a collection of related data about entities

Explanation of tables, fields, records, and entities in a database

Advantages of using multiple tables to avoid inconsistent and redundant data

Example of inconsistent data entry in a single-table database

Strategy to store data once and reference it many times using multiple tables

Use of primary key fields to assign unique identifiers to records

Elimination of long text entries and storage through normalization

Discussion on the importance of unique names in databases

Introduction to Data Modeling as the process of turning real-world scenarios into data structures

Differentiation between virtual phase entities and physical phase tables

Process of creating a process description for database planning

Technique of identifying entities and attributes from a process description

Example of entity identification with 'Classical Music Compositions'

Explanation of how to differentiate between entities and attributes

Importance of starting database planning with a clear process description

Practical exercise of identifying entities and attributes in a new scenario

Discussion on the decision-making process for entity naming

Detailing the attributes of the 'Student' entity and its relationship with 'Class'

Strategy for handling 'Incident' data through separate tables for better control

Advantages of creating a separate 'Classroom' entity for data consistency

Normalization strategy for the 'Grade' attribute to avoid redundant data

Conclusion and预告 of Part 2 focusing on building the Entity-Relationship Diagram (ERD)

Transcripts

Hello, and welcome to Relational Database Design, Data Modeling for Beginners, part

1 of 2.

My name is Tim Jones from Accelerate Computer Training in Long Beach, California.

In this two-part video series, we are going to teach you the skills and strategies used

to plan the structure of new relational database systems.

The emphasis here is on structure, what the database will contain, not interface, or what

it will look like.

Our target audience for this session is the non-technical user who needs to track related

sets of information but wishes to improve their understanding of relational database

architecture.

On the agenda for these two sessions we will Define and defend the need for relational

databases Learn the skills needed to plan a database,

including how to: Identify entities and attributes in a real-world

scenario Identify relationships between entities and

determine their cardinality (or type of relationship) , and

Draw an Entity-Relationship Diagram, or ERD, that clearly illustrates the plan for a proposed

database.

We will also discuss the role that key fields, both primary and foreign keys, play in relational

databases.

We will not build any databases today, because how you create tables, fields, and relationships

will depend on the database development platform you use.

Whether you build in AirTable, FileMaker, MySQL, or another platform, you need to construct

a plan before you start to build.

Can you imagine a construction crew showing up on the jobsite without a blueprint to follow?

How would they know where to dig, where to cut, how much to pour?

As the saying goes, “If you fail to plan, you plan to fail” so we are going to learn

how to plan a relational database.

But before we start that, there are some terms and definitions we all need to understand,

starting with relational database.

A relational database, called simply a base in AirTable, and a solution or app in FileMaker,

is a collection of related data about entities, organized into separate tables.

What is a table?

A table is the structure in which data describing an entity is stored.

It is is made of columns and rows, like a spreadsheet.

Each column is a field, and each row is a record.

An entity is a thing or idea about which we want to store data.

As an example, a table storing data about the entity classical music compositions might

be structured like this.

Note that in a relational database system, we create one table per entity.

“Why not just store everything in one big list?”

you might ask.

“Why use multiple tables?”

I am so glad you asked!

We use multiple tables To avoid inconsistent data entry, and to eliminate redundant entry

and storage.

Let’s look at our Classical Music Compositions table again, this time with only records of

some of Bach's compositions.

Notice the inconsistent data entry.

In the Composer field, we have three different renditions of his name, which, to a database,

looks like three different Composers.

In the Era field, we have two different entries for the Classical era.

There were obviously no restrictions on what users were allowed to enter in these fields,

and as a result, we get inconsistency, which will cause trouble when we try to search or

report on this data.

Not only are we asking our users to type the same data values over and over, which eventually

leads to inconsistency, but we are also asking the database to store those values repeatedly,

which makes the database big and slow.

If only there was a way to store an entry once and reference it many times.

Well, there is!

Using multiple tables in a relational database.

Splitting entities out into separate tables for Composer, Era, and Style, lets us enter

and store each value only once, which guarantees consistency.

How many times would we enter Mozart's name in a system structured like this?

Only once.

Likewise, each era and each style is entered and stored only once in its own table.

We use a primary key field, called ID here, to automatically assign a unique identifier

like 1, 2, or 3, to each record when it is created.

Then in the Composition table, we'll enter a Composer's ID number instead of their name,

like a one for Mozart, a two for Bach, and so on.

Likewise, we enter an Era ID number instead of typing the Era name, and a Style ID instead

of typing the Style name like Fugue or Concerto.

It eliminates typing and storing long text entries, which always lead to inconsistent

data, and it also resolves the dilemma of two composers, for example, having the same

name.

Unlikely, you say?

Did you know that there were two famous classical composers named Johann Sebastian Bach?

Take it from a guy named Tim Jones, names are not guaranteed to be unique.

Now, I know you are probably thinking that this will never work for you because you could

not bear to see numbers in place of the actual composer's name, era, style, or whatever.

Don't worry!

A well-designed database would never make users view the data like this.

You would see the composer's name, referenced from the Composer table where it lives.

Its the ID numbers that are the key to relating records of data to each other which lets us

view that data anywhere we want.

If I have convinced you that the multi-table structure of a relational database system

is reasonable and prudent, let’s learn how to the plan them.

The skills I am about to teach you are generally referred to as Data Modeling, which is the

process of turning real-world scenarios into sound data structures.

When we talk about database development, there are often two sets of terms we use during

the different phases of development: virtual phase and physical phase.

During the virtual, or planning stage, we call each thing we are going to track an entity,

but when we start to build the database, we will create a table for each entity.

Similarly, while in the virtual or planning stage, we call each characteristic of an entity

an attribute, knowing that each attribute will become a field in the actual table.

And lastly, we might talk of an instance of an entity being the same as a record of data

in a table.

Having defined our terms, we are ready to begin planning.

You should always start with a process description.

This is a written statement describing the proposed database.

It could be as simple as “We want to store information about the classical compositions

in our collection, including title, composer, era, year, and style or genre.”

Try to involve as many of your stakeholders as you can when drafting the process description

and get agreement from them on what needs the database must address.

This gives you a goal, a finish line to cross, even if only for Phase One of the database.

Once the process description is ready, we can identify entities and attributes.

I call this going on a hunt for nouns, because the nouns in the process description are usually

entities, attributes, or both.

You will recall from earlier that an entity is a thing or idea about which we want to

store data.

An attribute is a characteristic or descriptor of each entity, or of an entity.

For each noun, here’s how to tell the difference: an entity has multiple attributes that describe

it, while an attribute is only one value.

Let's practice.

Here again is our process description, or real-world scenario.

Let’s list the nouns, the relevant nouns, as we read it.

We want to store information (okay, information is a noun, but not relevant to the database)

about the classical compositions in our collection, including title, composer, era, year, and

style or genre.

Okay, we got them all.

Now let’s figure out whether each one is an entity (a thing or idea about which we

want to store data) or an attribute (a characteristic or descriptor of an entity).

To determine the difference, remember an entity has multiple attributes that describe it,

while an attribute is only one value.

Some nouns may be both an entity and an attribute of another entity; keep that in mind.

First up is composition.

When you think about a specific classical music composition, like Beethoven's Fifth

Symphony, for example, are there multiple attributes that come to mind that describe

it, or just one value?

There are multiple data values that describe a composition: its title, its composer, the

year it was written, etc.

Therefore, composition is an entity, and will deserve its own table in the database.

We create a box for each entity; these boxes will ultimately become our Entity-Relationship

Diagram, our blueprint for the database.

Earlier, we learned the value of automatically assigning a unique identifier to each instance

of an entity, so we will add ID as the first attribute of each entity.

OK, one down.

Let's move on to Collection.

This word appeared in the process description, so we added it to our list of nouns, or potential

entities, but the term Collection could refer either to a specific person's music collection

or the entire body of work that we call classical music and it would not affect the structure

of our database, so we can scratch that one off the list.

Next is Title.

When you think about the title of a composition, are there multiple attributes that come to

mind, or just one value?

A title is series of words, yes, but it makes up one single value, so Title is an attribute.

Which entity does it describe?

A composition, of course, so we add Title as an attribute to the Composition entity

and mark it as managed on the list.

Next up, Composer.

Does composer bring to mind multiple attributes that describe it?

Certainly!

A composer has a first name, a last name, a birthplace, and many other interesting characteristics.

That means Composer is an entity, so we make a box for it and list its attributes.

But Composer, while being an entity itself, is also an attribute of Composition.

We track that by adding Composer ID under Composition, and now we can mark it off the

list.

If you are wondering, "How are my users going to know the unique ID number of the composer

who wrote Piano Sonata No. 2 in B flat minor?"

don't worry.

Every database platform provides a means of entering an ID number by choosing from a drop-down

menu or pick list that shows the name or title of the thing.

Next, we have Era.

Music historians have divided classical music into three eras: Baroque, Classical, and Romantic.

They actually use these eras to describe composers, not compositions.

For example, Bach lived during the Baroque era; thus, every one of his compositions was

a Baroque composition because he was a Baroque-era composer.

So era is an attribute of Composer, not Composition.

By the guidelines we have used so far, Era would seem like an attribute.

For any given composer, we would need to store only one value to describe their era.

However, there is something else to consider.

We do not want to force, or even allow users to enter the same values over and over in

the same field across many records.

It leads to inconsistent data entry and makes the database big and slow.

We need to optimize, or normalize, this attribute, by giving it its own table , where we will

create a record for the three eras, each with its own ID number and Title.

And then we will define an Era ID attribute in the Composer entity.

We have dealt with Era.

Now I know this is detailed work, but stay with me; you are going to have to do this

on your own soon.

Only two more.

Year Written; is it an entity or an attribute?

Are there multiple attributes that describe the idea of the year when a composition was

written?

No, it is a simple attribute of a composition that users will type freeform into a Year

field.

Lastly, Style.

It is obviously an attribute of Composition where we will enter whether a piece of music

was an opera, a concerto, a symphony, etc.

But there are only so many distinct entries we would ever make in this field, so to avoid

inconsistent entries and with the goal of normalizing our data, we should create a separate

Style table with at least these two attributes: ID and Title.

In the Composition entity, we need a Style ID attribute where we will implement a drop-down

menu fed by the records in the Style table to help users enter the ID number that identifies

the style of each composition.

There, we have accounted for all the nouns in the process description.

and now its your turn.

Here is a new process description.

Read it and write down every potential entity you see.

I recommend phrasing entity names in the singular rather than the plural form, as in person

rather than people.

Okay, go hunt for those nouns; you have one minute, or for as long as you want to pause

the video.

Now let's go through it together.

The first relevant noun is Child, or Student.

The next one is Class.

Now did anyone write down Year as a potential entity?

I would agree with that, but for the last sentence, which says we do not need to track

year-over-year history of each student, but rather only a snapshot of where each student

is right now.

After that we come to Teacher.

And then behavioral incidents.

If you think we should just store incident data in an open-ended notes field, we'll get

more control by storing it in a separate table.

Next, to notify the parents, we'll need to store parent or family contact details.

Next, there are several classrooms, so that may be an entity.

And then Pre-K and Kindergarten, which are grades.

The last two sentences do not introduce any nouns that are not already on the list, so

now we move on to inspecting our candidate entities more closely to determine which are

entities, which are attributes, and which may be neither.

The first potential entity is child or student.

It is definitely an entity because there are many attributes that describe it.

But should we call it child or student?

Well, there is often more than one word that could be used for the same noun, so this is

the time to decide what you want to call each entity.

Eliminate all synonyms and choose entity names your whole team will understand and adopt.

Let's go with student.

Each student will need an ID, but what other attributes describe a student?

First name, last name, date of birth, good.

How are we going to keep track of which class a student is in?

Well, Class is next on our list to consider.

Is class an entity?

Yes, a class has several attributes that describe it: an ID, a Title like Miss Johnson's Pre-K

Class.

And although that title makes clear both the teacher and the grade of that class, it would

still be wise to store both the ID# of the teacher who teaches the class, and the ID#

of the Grade of each class.

I know we haven't even dealt with Teacher or Grade as candidate entities yet, but we

will, and we probably know already that they are entities in and of themselves, as well

as being attributes of the Class entity.

The ID# of the room where a class is held would also be an attribute of each class.

And now that we have Class as an entity, we can add Class ID as an attribute of Student.

Anything else about Student?

A student has a parent or family, which is on our list, so we will add a box for Family,

with some attributes, and then add Family ID to the Student entity.

And now we have dealt with Student, so we'll cross it off as managed.

We have also managed Class and Parent/Family, so we can move on to teacher.

Are there multiple attributes about a teacher?

Yes, a teacher has a First name, a Last name, a Date of hire, a Phone number, and perhaps

more.

And we have already noted that Teacher ID is one of the attributes of a Class.

Next up is incidents of good or bad behavior.

I included this not to bring up any painful memories of your own childhood misdeeds, but

because I thought it would give us another vantage point from which to consider an important

strategy in data modeling, that of asking yourself "How many of those could relate to

one of these?"

In this case, "How many Incidents could any one Student have?"

If it is many, then you should almost always make a table (or entity) for the many thing.

There is a principle in database design: One fact, one field.

That is, if you have a data value to store, store it in its own field.

Don't store two facts in one field, or three facts in one field.

One fact, one field.

So to keep track of these incidents, we are not going to make a Notes field in the Student

table and just keep a running text log for each student.

We are going to create an Incident table, giving each incident a unique identifier value,

along with the ID# of the student and the Date and Description of the incident.

Let's move on to Classroom.

You may think it over-complicates the system to create a table for something so simple

as classroom, but I promise you, if we put in the extra effort to design the structure

properly, it will save time and avoid frustration for the life of this database.

I look at creating Classroom, or just Room as an entity as another victory in the fight

against inconsistent and redundant data entry.

Store it once, reference it many times, right?

There is another benefit, too: if we ever rename a room in our school, we will only

have to edit its name in one record of the database, and because we referred to that

room in the related tables by its ID number, the name change will not break any relationships,

and the new name will appear in place of the old name in all screens and reports.

We'll want each room to have a descriptive Title, like Room A or Room 11, and the capacity,

or number of students a room can accommodate, would be appropriate here.

That takes care of Classroom, and we are almost done.

Grade, like Room, is an attribute of Class, but since we may have many classes of the

same grade, we do not want to store the grade name over and over, so we will put it in its

own table with fields for ID and Title.

Now that we have addressed all the nouns we found in the process description, we are well

on our way to a complete Entity – Relationship Diagram, or ERD, which we will build in Part

2 of this two-part series.

See you there!