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 courseDepartment – 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 universityType – 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 courseEnd – The ending time of the courseTeacher – 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:
- 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 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:
StudentTeacherDepartmentSchoolCourseSection
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.
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.
