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 enrolls in one or more
courses for the
Course Number – The
course number consists of a
department code and
Courses are taught by a
teacher who is employed by a
Course Name – Full name of the
Department – Which
department within the college conducts the
Departments belong to
schools. There are several
schools within the university, such as
School – Which
school within the university
Type – The
course being taken, such as
Credits – The number of credit hours awarded once class is taken and passed.
Semester – There are three main
semesters in the Year.
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
End – The ending time of the
Teacher – The
teacher conducting the class. A
teacher can teach one or more
Status – The
teacher’s employment status. Are they a
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:
- What are the main entities you can identify within the sample data and table?
- 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. 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
Data Modeling Relationships
The relationship describes the way two entities are connected. Relationships can be named as nouns or verbs. Consider two entities:
Wife. A good example of a relationship to connect these is
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:
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
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 interrelated.