Whichever database application you use does not matter! This course will cover design concepts for all relational database management systems. This course is designed for those who want to download this course or watch it ad free. This is a course for relational database design. Here are some examples of the most popular databases:
We start off this course by taking our time to first look at what a database is and how it differs from a spreadsheet, website, filing cabinet, and so forth. We introduce SQL and talk about data definition language and data manipulation language. After about 4 solid videos of explaining databases and SQL, we dive into learning about entities and attributes. We apply all of these concepts to structure tables and relationships. We learn to design all of the relationships and I explain everything in depth.
Most of the videos are on my chalkboard, explaining concepts. This is because it helps to fully understand database design before you dive into designing your own database. Once we go over all of the concepts of database design we will be designing databases from start to finish.
No programming skills are required. This course does not program a database. This course will only be teaching how to best design your database. You may be wondering why such a large course is required for only designing a database. Well, designing a database is an extremely important part of database management. The best design will get us the best results and make programming our database really easy.
After this course you should be able to tackle almost any database design project with confidence and skill. Keep in mind though that learning database design takes a lot of time and effort. Be sure to take notes and stop after each video to reflect on what you've learned. Keeping notes will make this video course a whole lot easier!
Complementary database design content can be found here - http://calebcurry.com/series/database-design-blog-series/. This series is going to be fun! We will be talking about a whole lot of cool stuff. The first part of this series will be concepts explained on the chalkboard and then the second part of this series will be going through examples on the computer. If at any point you have questions or comments, just leave me a comment!
A database is used to store large amounts of data. It differs from a spreadsheet in that it can be manipulated and managed in so many ways. A database will give us security, control, flexibility, and backups for our data. This is the first instructional video in the database design course.
In this video we talked about a relational database. A relational database is a database that sorts all information in relations. A relation is physically represented by a table. A table will have rows and columns organizing all of our information.
This video will talk about the relational database management system (RDBMS). The RDBMS is what allows us to do awesome things with our database. The RDBMS adds features as well as security. With a RDBMS we do not only have a database to store information but we can run queries and do things with our data. We use our RDBMS with SQL, a database programming language. We will be discussing SQL in the next video!
This video will be an introduction to structured query language (SQL). SQL is a language used to talk to any relational database management system. Although I didn't mention this much in the video, SQL is just a standard. Each database system, whether it be MySQL, SQL Server, Oracle, etc..., has its own implementation of SQL that may break from the standards slighty. But if you know general SQL, you can easily program in all RDBMS!
This video will be explaining what naming conventions are in general and then proceed to explain what naming conventions we will be using. It is important to realize that many relational database management systems have different naming conventions. Because this course is supposed to be able to be used for any RDBMS, my naming convention might not be the best for you. I use the convention that I use for MySQL programming, but I know many other RDBMS will be used for this course.
Database Design is the process of structuring your database in a way that will reduce errors, incorrect data, and redundant data. This will give us an optimized, fast, safe, reliable database. Database design largely implements relationships and normal forms. We will be discussing this in future videos.
Integrity is important because it is what keeps our database functional and relaible. This video will discuss three areas of integrity that you should be thinking of when you design your database.
Donate!: http://bit.ly/DonateCTVM2. This video will go over some of the most common database terms. Learn these because you don't want to have to look anything up during this series!
This video covers these terms:
Here we will cover more terms that you should know when working with databases. These terms include:
This video will talk about Atomic values. Atomic values store only one individual thing. As long as the value can be considered as an individual value then it is considered atomic. For example, a phone number is considered atomic even though it consists of a bunch of numbers. Atomic values allow for best database design and are part of the first normal form (we will discuss first normal form in future videos).
This video will be an introduction to relationships. The three kinds of relationships are:
We will learn that many-to-many relationships cannot be stored within a database practically. The rest of this course section will be teaching you how to design each relationship. I will also be giving you the solution to storing many to many relationships (stay tuned).
This video will go over the basics of one-to-one relationships. One-to-one relationships are very easy to understand. Watch this video and this course in its entirety to fully understand designing relationships.
One to Many relationships are when one row from tableA can have a relationship with multiple rows of tableB but each row of tableB can only have a relationship with one row from tableA.
Many-to-Many relationships cannot be stored in a database practically. The only way we can think of many-to-many relationships is logically. This video will be explaining the logical design for a many-to-many relationships.
This video will go over designing one-to-one relationships. This video will be useful for you when you're actually designing you database. One-to-One relationships (over multiple tables) are not as common because it is easier to store as a column within the parent table. When it comes to one-to-one relationships over two tables you must decide which is the parent and which is the child (child has the foreign key).
One-to-Many relationships will have a parent table and a child table. The child table will have a foreign key referencing the primary key in the parent table.
Child tables reference a primary key using a foreign key. This helps us design our relationships properly. Later in this series we will learn how the child table inherits values from the parent table and (in some instances) cannot exist without the parent (FK constraints).
The secret to designing many-to-many relationships is to use an intermediary table (junction table) to break a many-to-many relationship into two one-to-many relationships.
This video will sum up everything we learned from the last videos over relationships!
Keys are a big thing in database design and programming. Keys keep everything unique and are used to make connections between tables. Watch this video to begin our study of keys!
Primary key will automatically create an index used for database optimization!
A look up table is a table with a list of all possible values for a column in a different table. These values are then accessed through a foreign key. This is often used for one to many relationships where the many has only so many possibilities.
A superkey is any number of columns that forces every row to be unique. A candidate key is taking the superkey and removing unnecessary columns to get the least number of columns possible for row uniqueness.
A primary key are the candidate key that you select to be used as the main key for that table. All other candidate keys can be assigned as an alternate key. The Primary must be specified upon table creation. The alternate keys may be an official type of key in your RDBMS or it may be defined as simply a unique index.
Surrogate keys are auto incremented numbers that have no real world meaning. Auto incremented means that each row will have the next highest number. Natural keys are keys created from the columns already present within your table.
This video will be talking about the pros and cons of using surrogate or natural keys. Keep in mind that the one that you should use can vary depending upon the purpose of your database. Which one should you use?
Every row within a foreign key column references a primary key value of another table. This is used to make connections between tables within our database.
Foreign key columns with NOT NULL as a column characteristic will prevent any rows that do not have a parent primary key. This will force a relationship for every column.
Foreign key constraints are used for referential integrity. Referential integrity protects the connections between tables. The three possibilities talked about in this video are
Simple keys are keys with only one column. Composite keys are keys with multiple columns where at least one column is not a key in itself. Compound keys are keys where all columns are themselves keys. Compound keys are most commonly seen in intermediary tables (junction tables).
This video is a summary of all of the keys that we have learned. We will be discussing the key types and the key categories.
The types of keys we learned are:
The categories of keys we learned are:
Entity relationship modeling is the process of designing your entire database structure. This video will be going over the basics of EER models.
Cardinality is the maximum number of connections a row of one table can have with row(s) of another table. This is basically just a fancy word to describe the two options: 1, or many. Designing the cardinality shows us whether the relationship is a one-to-one relationship or a one-to-many relationship. In the event of a logical many-to-many relationship, the cardinality would be best designed as two separate one-to-many relationships with the intermediary table in the middle (see my video over many-to-many relationships).
Modality is the least amount of relationships one row of a table can have with a row of another table. The only two possibilities are 0 or 1. 1 is the equivalent as marking the foreign key column as NOT NULL.
This will introduce you to the topic of Database Normalization. Over the next few videos we will be covering 1NF, 2NF and 3Nf in more detail!
First normal form is the first step in database normalization. It has to do with making every column and value atomic.
Second normal form has to do with removing partial dependencies. A partial dependency is when a column depends on only part of the primary key. This only makes sense if you have a primary key that consists of multiple columns (composite/compound key).
3rd normal form deals with removing transitive dependencies, A transitive dependency is when a column depends upon a column that depends upon the primary key.
Data types are often classified into Dates, Numeric, and string.
Strings are characters (letters and numbers). Even numbers alone can be considered a string. These numbers will be used differently than numeric numbers because they are of string type. It is a common practice to illustrate a string by putting quotes around it such as "Hey!" Most databases classify strings as char, varchar, or text. These can sometimes be broken up further to bigtext, smalltext, etc...
Numeric data types are numbers. The data types usually include int (for integer), decimal, and float/double. Ints work with only whole numbers. Decimal, float, and double all work with numbers with data after the decimal point. Decimal is usually more accurate for calculations. Another thing to think about is whether the number is signed or unsigned. An unsigned number can only store positive numbers. This is good for numbers that are only positive, such as a surrogate primary key. Signed numbers can be negative but keep in mind that the max value is cut in half because you must include negative numbers now.
Dates are split up into datetime, date, time, and timestamp. Datetime is a combination of date and time. time stamp is a data type the stores the exact moment of time and is usually updated automatically when a row is entered or updated.
Joining is the process of taking data from multiple tables and putting it into one generated view. This video will be pretty slow and easy to follow because I want you to fully grasp the idea of joins.
Inner joins are used to take columns of 1 table and join them with columns of another table. Like you take 3 columns of the first table, 3 of the second table, and end up with a result of a generated table containing the selected columns from both of the tables.
Often these are combined by the primary key. So the primary key of the pk column would only be on the table once, if at all. You can use the primary key to join the table but it doesn't necessarily mean that you have to include it in the result set.
Inner joins across 3 or more tables will only return results that meet all join requirements. This can be a bit confusing so take some time to make some test data and try it out.
The best way to think about this is to imagine the first two tables being joined first. Once you have this new joined table take it and join it with the third table by the second.
Inner Joins across multiple tables can be complicated at first, but when you take the time to think about it, they are very easy to understand!
Outer Joins will take all rows from either the left, right, or both tables. This is good for when you want to return all of the rows for only one side of the join, but not the other. For example, you could return every single user, but only return some of the rows of an associated table.
Right outer joins are similar to left outer joins. They basically do the same thing. Left is right and right is left and the same effect can be rendered by merely flipping the tables (just like Jesus did). Right outer joins are in no way deprecated they are just not all too common. For consistency's sake it is a common practice to use left outer joins instead of right outer joins.
This video will help you understand the different results you can get when you have foreign keys that are labeled as NOT NULL. Notice how sometimes the type of join you use will return the same result and therefore may not matter which you use.
Outer Joins in situations of multiple tables make things complicated. But with good understanding and a little practice the path will be clear.
This video went over the example of a left and right outer join, but you can use any type of join to do the job. Once you figure out how each of the joins work, you can mix and match to get the result you want.
An alias is another name for a column or table. This is useful for when you want to change the way a a query looks, make it easier to type out, or change the way our view is presented.
This video will dive into the topic of self joins. Self joins are when you join a table with itself. This can be useful for replacing values within the table or having more than one of the same column for some reason. Self Joins can definitely be complicated!
My name is Caleb Curry and I have a passion for video and education. I have been creating videos for many years. I first started creating classes 5 years ago. I created some here and there over the years but it has been my primary focus for nearly a year now.
June 4th, 2012. I decided to take my skill set to the next level and learn how to professionally program databases as well as websites. After just graduating my Junior year of high school, I did not have a typical summer vacation. Rather, I studied daily and prepared for this new goal of mine. By July 23rd, I uploaded my first class in this new mindset of determination. Since then, I have created over 500 educational classes over database Design, programming, web development, and more. I have also done classes over Audio Production, social networking, Music theory, and a wide variety of interesting topics. On top if this, I continued to create my comedy videos as I have my entire life. I believe I have come a long way in such a short period of time and I would now like to offer payed classes for viewers to download or watch ad free.