Data Modeling Principles in Action

In this puzzle, we’re going to learn how to do some basic data modeling.  Many of you have expressed an interest in learning more about data modeling and database design.  I figure we could start with a simple case study and let it evolve from there.

Solving puzzles is a great way to learn SQL.  Nothing beats practicing what you’ve learned.  Once you have figured out the puzzle, post you answer in the comments so we all can learn from one another.

Data Modeling SQL Puzzle Question

You’ve been asked to analyze the following information. Once that is complete, let’s model the data and design a database to house student classes and schedules.  Here is a sample table this semester’s class enrollments:

Also, here is brief description of each data element.

• Student Name – Full name of student. A student enrolls in one or more courses for the semester.
• Course Number – The course number consists of a department code and number. Teachers teach courses.
• Teachers belong to departments.
• Course Name – Full name of the course
• Department – Which department within the college conducts the course? Departments belong to schools.  There are several schools within the university, such as Business, LS&A, and Engineering.
• School – Which school within the university
• Type – Courses are classified as either lectures or labs.
• Credits – A student is awarded specific credit hours for passing a course.
• Semester – There are Three main semesters in the Year. Fall, Winter, and Summer.
• Days of Week – Courses are held on one or more days of the week.
• Start – The starting time of the course
• End – The ending time of the course
• Teacher – The teacher conducting the class. A teacher can teach one or more classes.
• Status – The teacher’s employment status. Are they a Professor, Teaching Assistant, Associate Professor, etc.

Note: We’ll make this the topic of several puzzles, so don’t worry about designing everything today!  In fact, let’s just answer the following questions:

1. What are the main entities you can identify within the sample data and table?
2. Using these entities create a data simple conceptual model

Hint:  A conceptual model shows the entities and relationships between them.  No need to list every attribute. (See http://www.1keydata.com/datawarehousing/data-modeling-levels.html for example)

Data Modeling Answer

The goal of this puzzle is to build a conceptual model.  You’ll find that conceptual models are a great way to start designing your database.   

Without getting bogged down into details, such as naming fields and assigning datatypes, conceptual models allow you to focus on entities and their relationships.

Conceptual modeling promotes a top-down approach to design.

Data Modeling Entities

When you build a conceptual model, your main goal is to identify the main entities (roles) and the relationships between them.  If you’re having trouble understanding entities, think of them as “an entity is a single person, place, or thing about which data can be stored”

Entity names are nouns, examples include Student, Account, Vehicle, and Phone Number.

Read More: Database Normalization – in Easy to Understand English >>

Data Modeling Relationships

Relationships describe the say entities are connected. You can name them as nouns or verbs.  Consider two entities: Husband and Wife.  A good example of a relationship to connect these is Marriage.

The diagram to show this simple conceptual model is:

When creating conceptual models, you can show the cardinality.  That is how many of one object are related to another.  In the case of Marriage, it is one to one, but one to many or zero or one to many are also acceptable.  Represent cardinality in one of these ways:

In a One to One relationship one, and only one, entity is related to another.  In our example, for there to be a marriage, there must be one husband, and one wife; no more and no less.

In a One to Many relationship, one, and only one, entity is related to one or more entities.  Use the * to represent many.  A one to many relationship is useful when there can be several items related, such as a child have one or more parents.

In a Zero or One to Many relationship, zero or one entity is related to one or more entities.  Use 0..1 to represent Zero or One.  In our example a child may or may not own a toy.

Now let’s solve the puzzle!

Solving the Puzzle

What are the main entities you can identify within the sample data and table?

Here are some examples:

• Student
• Teacher
• Department
• School
• Course
• Section

I came up with this list by looking at the data and thinking about the types of objects they would represent.  I think all of them are obvious, as they are the nouns in the column names. The are more.

Think of “schedule.”

As you see below, I identified the schedule as a relationship between a student and class section.  I don’t’ think there is a right or wrong answer.  Sooner or later, we’ll need to represent the schedule as a database table.  If the concept is captured in the data model, either as an entity, or relationship, I think we’re covered.

Using these entities create a data simple conceptual model

To create the conceptual model, I placed the entities on diagram, and entities then imagined how they could be related to one another.

For instance, I knew that a course was offered in one or more sections.  Also, a department offers catalog of courses.  Given this, they became the basis for relationships between the entities.

I’m forming these relationships based on my experiences with going to college.  But, if I wasn’t familiar with how college classes were set up and scheduled, the relationships may not seem so obvious.

In this case, as an analyst you would have to conduct interviews.  The interview would help you understand if you missed any entities, and help you see how the entities are interrelate.