Database Design

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:

• Data
• Database
• Relational Database
• DBMS
• RDBMS
• NULL
• Anomalies
• Integrity
• Entity integrity
• Referential integrity
• Domain integrity
• Entity
• Attribute
• Relation
• Tuple
• Table
• Rows
• Columns
• File
• Record
• Field
• Value
• Entry
• Database Design
• Schema
• Normalization
• Naming Conventions
• Keys

Here we will cover more terms that you should know when working with databases. These terms include:

• SQL
• DDL
• DML
• SQL Keywords
• Front end
• Back end
• Client
• Server
• Client Side
• Server Side
• Server Side Scripting Language
• View
• Join

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:

• one-to-one
• one-to-many
• many-to-many.

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

• RESTRICT
• CASCADE
• SET NULL.

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:

• superkey
• candidate key
• primary key
• foreign key
• foreign key

The categories of keys we learned are:

• surrogate key
• natural key
• simple key
• compound key
• composite key

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!