13,832,353 members
Technical Blog
alternative version

Stats

4K views
4 bookmarked
Posted 30 Dec 2017
Licenced MIT

Data Modeling Principles in Action

, 30 Dec 2017
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:

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`? `Department`s belong to `school`s. There are several `school`s 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 `semester`s 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 `class`es.
• `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.)

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:

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:

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 `course`s. 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 interrelated.

Share

 Easy Computer Academy, LLC 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.

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.

 Pro