Understanding Database Building Blocks in SQL Server
Understanding Database Building Blocks in SQL Server
Every house has a kitchen, at least one bathroom and a bedroom, a front door, a plumbing system, and other things. These things can be arranged in different ways and in different numbers to produce different houses. So it is with databases. There are certain things that all databases have in common. In this post, I will introduce the building blocks that make up all databases and show how they are assembled. I will also discuss three rules that need to be followed when using the building blocks.
The material presented in this post should be of value to anyone who has any kind of interaction with databases, but it is at a very elementary level. For a more comprehensive discussion of database design, please see my article, An Intuitive Approach to Database Design.
Before we get to the building blocks, let’s take a moment to understand the term mini-world because it will help us understand the building blocks.
A database is a model of a mini-world. A mini-world can be a medical office, a retail business, a library, or many other things. When we want information about the mini-world, we turn to the database that models it.
Let’s say you go to the doctor for an appointment. When you tell the receptionist that you have an appointment, the receptionist will try to find your appointment in the model (the database). If the model has been kept current with its mini-world, it should be able to confirm that you do have an appointment. Similarly, if a business wants to know which one of their high-volume customers have not placed orders in the past 6 months, they can turn to the model of their mini-world if it has been kept current with that mini-world.
A mini-world can be an entire business, or it might be a part of a business, such as a bank branch, or the sales department.
The first thing a database designer does is to understand the mini-world, because the designer’s job is to design a model of that mini-world. And to understand the mini-world, the designer starts by identifying the building blocks that will go into assembling a database.
The Building Blocks
An entity is something that exists in the mini-world and that has characteristics that are of interest to us. For example, in a school enrollment mini-world, there are student entities, course entities, instructor entities, and others. Students have names, addresses, GPAs, and other characteristics that interest us. Courses have titles, credits, fees, and others. And instructors have names, addresses, qualifications, and others. Entities are unique. Each student is a unique entity; there’s John and Mary and so on. Each course is a unique entity and each instructor is a unique entity.
Entities don’t have to be physical things. Though you can’t touch, see, or smell an enrollment, it nevertheless exists in the school enrollment mini-world and has characteristics that are of interest to us, such as the date of the enrollment, the student who enrolled, the course the student enrolled in, and the final grade received. Sales are other examples of entities that don’t have a physical component but that have characteristics of interest to us (sales date, customer, salesperson, order total, method of shipment, terms of payment, etc.).
Entities of the same type belong to one entity-type. An entity-type is a set of entities of that type. The Student entity-type is the set of all students. The Course entity-type is the set of all courses, and the Instructor entity-type is the set of all instructors.
Entity-types are the most important concept in this post. They become tables in a database.
The attributes of an entity are the characteristics of that entity that are of interest to us. As already mentioned, students’ attributes include name, address, GPA, and others. Courses have names, credits, departments they belong to, and others. And so on with other entity-types.
Assembling the Building Blocks
How are these building blocks represented in the database? Each entity-type is represented by a table in the database. In that table, the individual rows are the unique entities and the columns are the attributes. Here is an example of a table that represents the Credit Card entity-type:
Building Block Rules
When working with building blocks, it is important to follow certain rules that not only make it easier to work with database objects but also prevent data anomalies (data anomalies are beyond the scope of this post but are discussed in detail in my article, An Intuitive Approach to Database Design).
Rule 1: Each table should represent one and only one entity-type.
The database of the school enrollment mini-world, above, needs the following tables: Student, Course, Instructor, Enrollment and others, one for each entity-type identified in that mini-world. The database of the medical office mini-world needs a Doctor table, a Patient table, an Appointment table, a Medications table, and others. One table for each entity-type.
There is an exception to this rule. Get ready, here comes the brain twister: If two entity-types share the same attributes and an entity in one of the entity-types is also an entity in the other entity-type (one entity is a member of both entity-types), then the two entity-types can be represented in one table. Here it is, again: A Professor entity-type and an Advisor entity-type can both be represented in one table, because advisors are also professors and they have the same attributes (professor/advisor name, professor/advisor qualifications, and others). And again: A Folder entity-type and a SubFolder entity type can both be represented in one table, because all SubFolder entities are also Folder entities and they share the same attributes (folder title, folder size, number of items in folder, parent folder, and others). Just one more: The Employee and Manager entity-types can both be represented in the same table, because employees and manager entities have the same attributes and manager entities are also employee entities. This exception to the rule is examined more closely in another blog: SQL Server – The Self Join Query.
By the way, notice that this rule is the reason why the proper way to name a table is in the singular. Tables represent a single entity-type.
Rule 2: All columns must be atomic.
Have you ever wondered why database tables might have columns for City and State, but only one column for the address? Maybe you’ve wondered why there isn’t a column for Address Number, another one for Address Street Type (Blvd., Avenue, etc.), another for Apartment Number, and so on. Why are these columns usually all lumped together into a single Address column? The answer to all of these questions lies in an understanding of atomicity.
When we say that a column is atomic, we mean that it cannot be further subdivided and still retain meaning. Let’s make up a column and call it MegaAddress that includes a street address together with city and state.
But because we do searches, sorts and other operations on portions of that column (search by city, sort by state, print out just the street address part, and so on), we can say that subdivisions of that column have their own meaning (StreetAddress, City, and State). So, MegaAddress is not atomic. A better table would be:
Now, what about the StreetAddress column? Should we subdivide that down further into StreetNumber, StreetName and StreetType? As long as we don’t do anything significant with subdivisions of it—if we don’t sort on street numbers, search for all roads or avenues, and so on—then the column is atomic as is. But if we do those things, then the column is not atomic and then, yes, we should subdivide it as described.
Rule 3: Columns cannot be multivalued.
Some entity-types contain multivalued attributes, or an attribute that can have more than one value. In a Course table, there can be an attribute called Prerequisites. Because a course entity can have more than one prerequisite, the Prerequisite attribute is a multivalued attribute. When we create a table for the Course entity-type, we will not be able to represent the Prerequisite attribute as a column in the table. To properly represent a multivalued attribute, we will need to create another table for it and relate it to the original table using a one-to-many relationship (relationships are discussed in detail in the courses I teach at Interface Tachnical Training SQL100: Introduction to Transact-SQL and SQL250:Transact-SQL for Developers and in my article, An Intuitive Approach to Database Design). Other examples of multivalued attributes include an employee’s dependents, a doctor’s qualifications, and so on.
Rule 3 says that, for a given entity (row in a table), there can be only one value in each column.
This post introduced the concept of the mini-world as the aspects of a business or other environment that a database models. The post also described a database table as representing a single entity-type in which the rows hold the individual entities of the same type and the columns represent the attributes of those entities. Three rules regarding tables were introduced. Rule 1 states that a table should represent a single entity-type. The exception to the rule is when two entity-types share the same attributes and entities in one entity-type are members of the other entity-type. In that case, both entity-types can be represented in the same table. Rule 2 states that all columns of a table must be atomic, meaning that a column cannot be subdivided and still retain meaning in the mini-world. Finally, Rule 3 states that columns in a table cannot be multivalued, meaning that, for a given entity (row in a table), there can be only one value per column.
SQL Server Instructor – Interface Technical Training
You May Also Like
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015