Click here to Skip to main content
13,832,353 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 30 Dec 2017
Licenced MIT

Data Modeling Principles in Action

, 30 Dec 2017
Rate this:
Please Sign up or sign in to vote.
How to do some basic data modeling

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 figured 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 your answer in the comments so we can all learn from one another.

Data Modeling SQL Puzzle Question

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

Data Modeling - Sample Table

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. Courses are taught by a teacher who is employed by a department.
  • 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 – The type of course being taken, such as lectures or labs.
  • Credits – The number of credit hours awarded once class is taken and passed.
  • Semester – There are three main semesters in the Year. Fall, Winter, and Summer.
  • Days of Week – Which days of the week the course is held. Classes can be 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 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. They allow you to focus on the entities and their relationships without getting bogged down into details, such as naming fields and assigning datatypes.

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.

Data Modeling Relationships

The relationship describes the way two entities are connected. Relationships can be named 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:

Data Modeling - Simple Conceptual Data Model

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. Below are some examples of how cardinality can be represented:

Data Modeling - Cardinality

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 can 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 toy may or may not be owned by a child.

Now let’s solve the puzzle!

What are the Main Entities You Can Identify Within the Sample Data and Table?

These are the entities I thought of:

  • 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. You may have come up with some others, such as Schedule, which isn’t shown in the list.

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.

Data Modeling - Conceptual Data Model Answer

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 interrelated.


This article, along with any associated source code and files, is licensed under The MIT License


About the Author

Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==>

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190114.1 | Last Updated 31 Dec 2017
Article Copyright 2017 by essentialSQL
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid