The Complete Database Design Beginners Tutorial | Rares Ilea | Skillshare

The Complete Database Design Beginners Tutorial

Rares Ilea, Web Applications Developer | IT Consulta

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
22 Lessons (2h 6m)
    • 1. The Complete Database Design & Modeling Beginners Tutorial skillshare promotional video

      0:41
    • 2. 01 Introduction

      2:03
    • 3. 02 what is a database

      6:59
    • 4. 03 How databases work and why are they important

      10:34
    • 5. 04 Different types of databases

      2:48
    • 6. 05 What is a Relational Database

      3:57
    • 7. 06 What is Database Design

      5:21
    • 8. 07 What are the three types of Database Design

      2:17
    • 9. 08 Database Design Tools

      1:46
    • 10. 09 Setup MySQL Workbench and prepare the work environment

      8:01
    • 11. 10 Define Conceptual and Logical Design for an Online Store

      8:57
    • 12. 11 What is Database Normalization

      2:59
    • 13. 12 First Normal Form

      9:26
    • 14. 13 Second Normal Form

      3:09
    • 15. 14 Third Normal Form

      4:58
    • 16. 15 Database Relationships

      3:49
    • 17. 16 One to one relationship

      6:49
    • 18. 17 One to Many Relationship

      9:19
    • 19. 18 Many to many Relationship

      6:53
    • 20. 19 Finalize Database Design

      9:31
    • 21. 20 Generate the Physical Database

      14:06
    • 22. 21 Conclusions

      1:35

About This Class

Learn Data Modeling and Database Design from A to Z, while creating an online store's database model with MySQL.

This course will introduce you to databases and relational database design. Every theoretical notion in this course is followed by several examples to help you better understand database modeling and design techniques. You will learn, by example, while designing a real world online store's database in simple, incremental steps.

Nowadays, databases are everywhere! Every web application like: Facebook, Google, Booking or even a simple blog uses them. Knowing databases is a MUST for every web developer. If you have no technical skills and plan to join the IT industry, or if you are already a developer who wants to understand more about databases and the backend, or you are simply interested in the subject, then this course is for you. 

Sound database design reduces data redundancy to a minimum, improves data retrieval speed and ensures the database will adapt easily to future changes.

In this course you will learn relational database design with step-by-step, practical examples. This video tutorial cover the following topics: 

  • What is a database? 
  • How databases work? The power database indexes. 
  • What are some different types of database management systems (DBMS)
  • What is a relational database? What are tables, columns, primary keys, foreign keys, relationships, and much more
  • What is database design and why is database design important? 
  • What are the 3 levels of database design? Conceptual, Logical and Physical database design.  
  • Data modeling and database design tools. You will also learn how to setup MySQL Workbench on you computer and how to use it to design a database 
  • What database normalization is and how to apply the three normal forms (1NF, 2NF, 3NF)
  • What data redundancy means?
  • How to design and create database relationships: one-to-one, many-to-one and many-to-many relationships.
  • Complete the design of an online store. Create entities and attributes for:  products, items, customers, categories, brands, reviews, etc. 
  • Finally, you'll learn how to use it's forward engineering feature to create the actual database on MySQL Server.

Every technical lecture has a screenshot of the database schema.

Enroll now and I'll see you on the other side!

Transcripts

1. The Complete Database Design & Modeling Beginners Tutorial skillshare promotional video: Hi. Are you new to databases and want a master? The basics. Do you want to land a new job or just add database designed to your skill set? You're in the right place. This course includes all the information you need to start off. You'll learn what databases, how it works and why Databases air so important in modern computing. Then we'll focus our attention on the most common database type used today. They're relational databases. You'll learn how to design a real life database from an online storm. In simple, incremental steps, you'll learn how to apply normalization, build the tables, relationships, polish the design and finally generate the physical database using my SQL enroll now. 2. 01 Introduction: Hello and welcome to the complete database modelling and design beginners tutorial. My name is really Sheila, and I'll be your host for the next couple of hours. If you join these cars, that means that you already know the importance off databases and database design. I congratulate you for your decision to learn these new skills. Let me walk you through. What are you going to learn in this course? We will start off by understanding what the database is. Then we'll take a look under the hood and understand how databases work. And why are they so important in modern computing? I walk you through the different types of data basis, and then we'll focus our attention on the most commonly used database management system today. The relational databases. I'll explain what relations tables, topples, records, primary keys and many other concepts are afterwards will take a deep dive on the subject off database design. I'll explain what database design means and why is it important? And you'll learn what are the steps off a sound database design? As I'll introduce every theoretical aspect, I'll walk you through several examples while building a really wall database for an e commerce website. You will learn about database normalization and the three normal forms. Then you will apply them on our online stores database. We'll build the relationships, and as I'll cover every type, you will learn how to design Want one, many to one and a many to many relationship finally will complete the design by adding categories, navigation and the review system for our store. And then we'll actually generate the database. Using a technique called forward Engineering, we'll add some data and run a few sequel queries. That being said, I hope you will enjoy the journey and without further ado, let's get started. 3. 02 what is a database: hi and welcome to lecture number two in this lecture will answer the question. What is a database? I could have started by sharing with you the formal definition off a database from Wikipedia. But since I'm a big fan off the evolution of human civilization, I'll take you on a quick door to see how collecting data for struck the ages and how it all led to our modern database systems. Let's explore some pieces off history together. Let's go back 8000 years. The oldest examples off writing. We have our dated 6000 BC The Appeal The Nation, Mesopotamia in a city state off le Gosh in modern day Iraq, these Asian symbols are known as the cuneiform writing in the Middle East. The development of the cuneiform writing was a major boom toe The city states they needed to keep track off a complex administrative system. These first documents in written history are concerned with goods and trade records, off taxes and three bills. So, for example, on enter off such attacks, record could have bean, a sure son off a new set. Pay taxes with three parts off wheat and one ounce of gold. The Mesopotamian administration documents predate even the most patient off stories and religion. In other words, collecting data and extracting information was vital for humans since the beginnings off civilization moving forward through history. The same systems for collecting data about taxes and rates were used by the Egyptians, then by the Romans. The Roman sensor was an officer nation from who was responsible for maintaining the senses , supervising public morality and overseeing certain aspects off the government's finances. Among other duties, they had to keep track off the Roman population and create a register which contain basic information about every Roman citizen. They were supposed to register their first name, Last name, and Nick name the name off their father or off their patron if they were slaves. The ranking society, which could have bean slaves Freeman night, etcetera, they land for which they had to match a name, location and usage. Obviously, this column use improvement. Probably. The Romans haven't heard about database normalization as well, due in the following sections. Among other data, the number off slaves or cattle the cities and owned was very important on a funny note. After all this data was collected, the Citizen was then asked, You're declaring from your heart do you have a wife? And if he was married, he had to give the name of his wife and likewise the number, names and ages off his Children. Considering the estimates, by the end of the first century BC, the entire population off the Roman Empire was somewhere around 4 to 5 million people. Thus, probably this was the largest collection of data in the world at that time. Tracking information about people evolved during the Middle Ages. One of the biggest data collections at that time is that off the Catholic Church, the church register the marriages and boards since the time of the Roman Empire. Using this information, people nowadays can build their genealogical tree, moving forward in time to the early 19 hundreds. The beginning of the last century was marked by the rise off the industrial age. The first big corporations were founded, among them the Ford Motor Company. Fourth was the first company in the world that build the production line. Now imagine managing the company Without the aid of modern computers, you would have to keep some bigger registers with data about employees, cars and their spare parts about customers for party collaborators and much more. Imagine how much time would have taken you to find the car that a specific customer bought and then sent him a spare part that he needs. Luckily, half a century later, the first computers were built. In the beginning, data and even program code was store on perforated paper, then on magnetic disks. And finally, data was stored in files on heart price. You can think off them as the popular Microsoft Excel files. These was a major breakthrough. The four spare part problem from that's light could be sold by opening a few fires and do some control as searches. UN. Improvement would have Bean to write a program toe, find the spare parts and searched through this first automatically. Still, in order to face the data really fast, something better was needed. This performance improvement requirement led to the development off the first database management systems. In the next lecture will see what they brought to the table and why they gain such importance and popularity. Modern computing. Before that, let's define a database. This is the official database definition from Wikipedia. Let's break it down a database is an organized collection off data. Probably by now you've got an idea about what the data collection is and how it is organized. The Mesopotamians collected data concerning taxes and commerce. The romance build a similar data collection, but with more details. The Catholic Church collected data about marriages and births, and so on. It is a collection off scheme, us tables, queries, reports, views and other objects. What this means is that a database can store multiple schemers. For example, we could use the same database in store, a Roman sense of schema and the fourth Motors schema. The scheme I itself is a collection off tables for the Roman sense of schema. We would have a big table called Citizens. On the other hand, Ford Motor Off schema must have multiple tables on employee table, a spare parts stable and so on and so forth. The queries reports. Views are ways off processing this data in order to obtain valuable information. For example, to extract a spare part for a customers car, we could create a query tooling customers, cars and spare parts tables together in such a way that it returns the exact spare parts that the customer needs in a few milliseconds, moving forward to the next phrase. The data typically organized the model aspects off reality, etcetera, etcetera. This is pretty self explanatory, right? Okay. I hope you have a picture in Mind off what the database look like. For me, the simplest way to think about it is to imagine a collection off Excel tables, which can be queried and returned results really fast. That being said, joining the next lecture to find out how databases work. And why are they so important? See you in an instance. 4. 03 How databases work and why are they important: Hi and welcome back in this lecture, we will explore how data basis work and why are they important in modern computing? I go through some theoretical information about algorithms That might seem weird, I promise you. It is only in this lecture that I'll do this first I thought, Oh, skipping this part. But in order for you to understand the underlying mechanism off databases and answer probably one of the most common interview question how databases indexes work. I had to show you this part. Books off Hundreds off pages have been written on this subject, but I'll focus only on the core concepts so that you get the main idea. I start off the discussion with some theory about the big O notation ba trees and how these concepts are using databases to allow super fast retrieval of data. So the bigger notation is used to classify algorithms according to how they're running time or space requirements grow as the import size grows. This definition may not say much, but you understand how this works. By going through some examples, we will be focusing in measuring the time complexity. Let's start with constant time. Imagine we have these line off court that compute seeks not this, that it does not depend on the input size in any way for this line of code. We say that its complexity is big off one or constant time. Now let's see a linear example. Suppose we had the following loop that prints the numbers from zero to an this block off court runs and times. Does we say that this block has the complexity big go off an finally Let's look at quadratic time. You can see that the Prince statement will be executed, and times and times. That means the time complexity is big, go off and squared, so doesn't make sense of these Big o notation works for our purposes. These examples should suffice. But if you find this topic interesting and want to take a deep dive on the bigger notation , you can find very useful information. By doing a simple search on Google. We'll move on to explore the most common algorithm complexities in computing. For example, as we saw in the previous slide, painting an array off on elements has the complexity be go off and the best sorting algorithm has the complexity big go off and times log off. And so what does all this has to do with databases? Let's imagine we have a table off an rose We want to search for a specific role. Was the complexity off that well in the worst case scenario, meaning that the role we are searching for is the last one we would have to reiterate over each row until the end Does the complexity off the search would be big? Go off an. As you can see, this means that the search time increases linearly with the number off rose well, this is not ideal for a database. What we want is to reduce the impact that Sy says over the search time. Now, by looking at this complexity chart, wouldn't it be great if we could reduce the time off? Searching toe a logarithmic complexity, the big go off log off an that would imply that the search time is almost unaffected by the number of rows in the table. That would be a major improvement. Fortunately, we can, by using a data structure called B tree, which is at the core off most databases implementations today, let's explore how it works so What is the B tree? A bit tree. It's a self balancing three data structure that gives eight assorted and allow searches, sequential access in sections and additions in logarithmic time. It is commonly used in databases and file systems. This definition may not say much, but let's take an example to understand how it works. Let's imagine we have an array of numbers from 1 to 7 now. In this image, you can see the steps needed to create a bit tree from the initial array. The end result is the last three from the picture. We say that this BA tree is off order free because every note has a most free Children. Now let's see what it would mean to search for elements. Seven. Instead of doing seven reads from the initial array, we could find it in just three reads how first we look at the root element, which is for and that would be read Number one. We see that the node we are searching for has a higher value. So we continue on the right branch. We compare it to six. These will be read number two still higher. We need to keep searching on the right branch. Finally, we find it, and that would be the first and the last read. If you are a bit confused, don't worry, we'll explore soon. Another example. What we should keep in mind is that in the worst case, the number off reads we must perform in order to find the note equals the debt off the tree . In our example, the B tree that is free and we perform free reads in orderto get toe the elements. Second, this is a more complex bigotry. Off order am equals five, meaning that every note can have at most five Children it can store for entries. Fernald, the three in this example, has the depth off to and every note can store. For entries, that means it can index four squared equals 16 entries. To generalize the number off entries of a tree can index and equals to the number off entries Fernald raised to the power off the which is the leapt off the tree. Having these formula in mind, let's see how much data arbitrary off order five can invest in respect to its depth, as you can see at the depth off. 10. It can index more than one million entries. As we noted previously, the number off reads We must perform in order to get to the note. We are searching for equals that treat that This means that searching for a value in an indexed array off one million elements takes 10 reads a far cry from the one million plus reads that would have to be performed on a non indexed array. Now let's see how databases are using the power off be trees. Here is an example off a database table storing data about employees. We aim to such employees bidi using a logarithmic time complexity. The solution implemented by most databases, which is also their key ingredient, is to index the employee i D column into a B tree. In this particular example, we can find employees 46 in three steps. The first step is to read the root note and see on which branch to continue. Then we move on on the middle branch and find the I D 46 now to search for this idea. In a B tree, it takes just two reads. You may be thinking right. The tree depth is, too. Why did you say free steps? Well, the note in the B tree doesn't start the whole road. Data it just store its location on the hard drive, so we need additional steps that number free that reads the actual role in conclusion toe. Find data in a database. It takes us deep. Plus one reads where the is the depth off the B tree. If you think off the previous slide, searching for an employee in a table off one million employees would take us 11 reads before moving forward. I want to mention something. If the searches were run by the first name, then we would have to index the first name column. The B tree would have looked similar, but instead of numbers, it would have contained letters. Indexing users by email addresses is common practice. Since that logging time, you must search for them by email address. Now let's put all these pieces together. We said that in order to find the rowing a database we need to execute D plus one reads, the total time to search this row in an and RO database table would take D plus one multiplied by the time to access a block of data on the hard drive that is a constant and it depends on the Hardwell a keep month. It's rotations per minute or whether or not is an SSD. We just mark that with THD access time. Next, we can compute the tree depth based on the three order and the total number of entries. So the equals log off base and minus one off an results that the time to such a table off and rose. It's a logarithmic function off an and that's it. We are done enough with all the story and mathematics. I hope you understand how databases work at the corps level. You should not bother memorizing all this information, but if during an interview, someone will ask you how databases indexes work, you should tell them that be trees are used to index table columns in order to reduce the such complexity from a big off and toe, a big off log off on a side note. In our examples, we have used be trees off order five storing for entries per note. In real life, though, ba Treece store hundreds off entries per node, making their depth lower and improving search times. Now let's take a quick example. Imagine our problem with one million employees and the hard disk access time off 0.2 milliseconds. A database will return results in 2.2 milliseconds versus a classic technique off searching for that row that will take 100,000 times more time, probably by now. You already got an idea why databases are so important. Off course, it's because they managed data so efficiently and, as you see next are simple to design and to query. Today, the Internet is full off Web applications. Thanks. So the data basis, on top of which they are built, we can access useful information in seconds or less. You can think off Google that basically starts every page there is on the Internet, and it earns millions off results for a search in less than a second. You can think about Facebook that has 1.9 billion users, and you can find everyone by their name in less than a second. You can think off Amazon booking and many others before moving forward. Let's do a quick recap in this lecture. We learn about the big O notation. Then we talk about B trees and their logarithmic search complexity. Then we saw how they are used in databases to drastically improve the search time. Thanks for watching and seeing the next lecture. 5. 04 Different types of databases: Hi and welcome back in this lecture will discuss about the three most popular types off databases. Relational databases are the most common database systems used today. Some examples are Microsoft Sequel server, Oracle Database, IBM, Informix, My sequel and many others. The Relational Database Management systems feature very good performance for managing data . They allow multiple users, even thousands, what we date at the same time and are providing an advanced security for accessing the data . As we already so relational Database systems store data in columns and rows, which in turn make up tables. These stables are linked to each other using relationships. Thus the name relational. A set off tables make up a schema, a number off scheme. US. Create a database. Many databases can be created on a single server. Another type of databases are the object oriented database management systems. These systems represent information inform off objects as used in object oriented programming. The way off representing data is what makes them different from relational databases. Their promises to make the integration between the database and the application that uses it simpler. But due to the excellent support off relational databases and off the object relational mapping frameworks such as hibernate used in Java Web application. The object oriented databases never made it to the big league. They are not so commonly used. The most innovative structures for storing data today are no sequel. Databases do not get confused by their name. It comes from not only sequel meaning that in addition to structured query language, they are using other mechanisms as well. These do not follow the table row column approach off the relational databases. Instead, they build a bookshelves off elements and the low access per bookshelf. So instead off tracking individual words in books. No sequel databases narrow down the data you are looking for by pointing you to the bookshelf, then a mechanical assistant. Worst with the books toe. Identify the exact word you are looking for, to name a few examples. Mongo, DB, Cassandra Nail for Jay and many others. Because no sequel databases focus on storing data in large chunks, they are increasingly used in big data in real time Web applications. All the I D giants are using them nowadays. Before I finish this lecture and move on our focus on relational databases, let's name a few more models that are not so commonly used. This would be flat file databases here. Article network and object Relational databases. Thanks for watching and see you in the next lecture. 6. 05 What is a Relational Database: hi and welcome back in this lecture, we will take a deep dive on the subject off relational databases. This is the most widely used type of database management system, as I mentioned earlier relational databases, a set off tables that are linked together in some meaningful way. The relational database model was proposed by Edgar Code off IBM Research around 1969. Let's get accustomed with the terminology. Oro represents a single item, and it's often called topple or record. In our example, a row represents an employee. In other words, on employee record is stored in one row. A column is an attribute off furry coat, for example, first name or last name. The table itself is also called relation and is basically a set off topples or records that share the same attributes. In order for a relational database to function, a number of basic rules need to be followed. This would be it's stable, has a unique name. Each table contains multiple rows. Each row in a table is unique. Every table has a key to uniquely identify the rows. Each column in the table has a unique attribute name. These rules are implemented as part off the overall database design. A primary key is a minimal set off attributes or columns whose values are uniquely identify a record in a table. Each table can have only one primary key, even though this might consist off multiple attributes. In most databases, a key consists off a single attribute. For example, the table off employees has a column i D. That is the tables Primary key. It is common practice when your records are people to use as primary key, their Social Security number or their email address. Sometimes a key is composed off multiple attributes. This means that only the combination off these attributes uniquely identify a record. This is called a composite primary key. Consider the stable. We could use a composite key on three columns, burst time, first name and last name. It is not a perfect example, but the chances that two individuals were born at the same time and have the same name are almost zero. A foreign key is the field, whose values are the same as the primary key off another table, for example, let's imagine that our employees must give some daily reports off their work. We start that information in a table called Daily Reports. In that table, the field employee I D is the foreign key, since it is the primary key in the table employees. The table Daily reports has its own primary key called idee toe uniquely identify each report. If the same employee makes multiple daily reports, these results in duplicate values in the Employee i D column. What you see here is known as a many toe one relationship. Many daily reports belonged to one employee. There are some other important facts about the relational database. There are free types off relationships that can be applied between its tables, and it can be quieted using sequel or structured query language. These are very important, but we'll discuss more on these subjects later in the course. Okay, so this was the last lecture off the section database basics less summarized what we have discussed about in this section. First, you saw how collecting data evolves in 6000 BC and I break down the database definition. Then you. So how they work using Ba Treece and those having a logarithmic such complexity. Afterwards, we discussed about the common database types and you got familiar with the relational databases. I hope you enjoyed it so far, and you are welcome to join me in the next section, where we'll discuss about database design. Thanks for watching. 7. 06 What is Database Design: hi and welcome back in this lecture will discuss about database design what it is, what are its objectives and benefits. So database design is the process of producing a detailed data model off a database A fully attributes. The data model contains detailed attributes for each entity. The term database design can be used to describe many different parts off the design, often overall database system principally and most correctly, it can be fought off as the logical design off the database structures used to store the data in a relational model. These are the tables. There are several database design objectives. The 1st 1 is that the database supports. Both required an unplanned information retrieval, so the database must be designed to store the data necessary to support information requirements defined during the design process and any possible unplanned queries that may be posed by the users. The 2nd 1 is that the tables are constructed properly and efficiently. Each table in the database must represent a single subject and should be composed of relatively distinct columns, which keep redundant data to an absolute minimum. The food one is about data integrity, so data integrity is imposed at field table and relationship levels. These levels off integrity help guarantee that the data structures and their values will be valid and as accurate as possible at all times. The database will support business rules. 11 Toe, the organization it is designed for. Well, the data must provide accurate information that is always meaningful toe the business and the last one. The database should lend itself to future growth and development, so the database structure should be easily modifiable and expandable. As the information requirements off the business continue to change and grow, let's see why database design is important. First, the database structure is easy to modify and maintain modifications you made toe a table or a field will not affect other tables or feels in the database. The data is easy to modify changes that to make toe the value off a given field will not affect the values off other fields within the table. Furthermore, a well designed database gives duplicate feels toe on absolute minimum, so you typically modify a particular data value in one field. Only information is easy to retrieve. You should be able to create queries easily due to a well constructed tables and the relationship between them and lastly and user applications are easy to develop and build. You can spend more time on programming and addressing the data manipulation tasks at hand instead of working around the problems that will arise when you work with a poorly designed database. Now let's talk about the database design process. Firstly, I have to underline that database design is more art than science, as you have to make many decisions. Databases are usually customized to suit a particular application, no to customise. Applications are alike, and hands no do that. Our bases are like guidelines, usually in terms off. What to do and what not to do are provided in making these design decisions. But choices ultimately rest on you, the designer. Now let's review the steps off database design. First, you have to determine the purpose off the database, and this will help you prepare for the remaining steps for our purposes. Let's imagine we are building an online store database. Then you have to find an organized the information required. So you have together all the types of information to record in the database, such as product name, order, number, etcetera, then you have to divide the information into tables. Divide the information items into major entities such as products, orders, customers. Each entity then becomes a table. Then you have to turn information items into columns. You have to decide what information needs to be stored in each table. For example, on employee table might include columns such as last name and hire date. Then you will have to specify the primary keys. Choose each stables, primary key, the primary keys, a column or a set of columns, as we mentioned previously that it used to whiny clearly identify each row. For example, we might have product I D or order I D. Then you have to set up the table relationships. Look at each table and decide how the data in one table is rated. Toe the data in other tables that feels toe tables or create new tables to clarify relationships as necessary. Then you will have to apply the normalization rules to see if the tables are structured correctly and make adjustments if necessary. Lastly, you will need toe, refined the design, analyze for errors, create the tables and add a few records to see if everything works as expected. Don't worry. If these steps seem confusing, we will follow them in the next lectures while we'll build a database for an online store. Just one more theoretical aspect. And then we'll get to work. Thanks for watching and seeing the next lecture. 8. 07 What are the three types of Database Design: Hello again and welcome back. This lecture will be a short one. Before we put in practice what we've learned, I wanted to eliminate some of the confusion out there. Lots of people are asking water the three types off database design. Well, there is no such thing as three types Off designs is more like the three levels off database design, so let's see what those levels are. The free levels off database design are the conceptual data model, the logical data model and the physical later model. Here we compared these three types off data models. The 1st 2 levels of database design are also called data modelling. This step is sometimes considered as a high level and abstract design Face the aims off this face our toe describe what data is contained in the database. So the entities, such as customer and products, describe the relationships between the entities, such as a customer, can buy many products and described the constraints on the data, such as a customer first name can have maximum 45 characters. The last level database physical design defines a data model off a specific database management system that includes the internal database storage structure, file organization or in the same techniques. So it is specific to the database management system. You can see that the complexity increases from conceptual to logical toe physical. This is why we always first start with the conceptual data model. So we understand that the high level, what are the different entities in our data and how they relate to one another, Then move on to the logical data model. So we understand the details off for data without worrying about how they will be actually implemented and finally, the physical data model. So we know exactly how to implement our data model in the database off choice in a database design project. Sometimes the conceptual data model and a logical data model are considered as a single deliverable. In the next lecture will see some of the tools that are available to create a database design. Thanks for watching and suing the next lecture 9. 08 Database Design Tools: Welcome back in this lecture. I'll go really quickly and talk about the available database design tools. These tools can be either online or best of applications, and they can be free or paid. There are tons off database modelling tools online. Most of these tools are paid, but some off them have a freemium package. Freemium means that you can use a limited set off their features, or you are limited to create one or two database designs. Or you must allow public access to your database design, which is bad scenes. Other scan copied some off. The Freemium Online tools I've used are db designer dot net. Get my model that Come Lucy chart dot com And here, the plus the desktop database. Modelling tools are more powerful. The offer features off reverse and forward engineering. What that means is that supposing you have an existing database, you can apply a reverse engineering action and create the design on the other side. You can create the design, and then, with the click off a simple button, you can forward, engineer and create the database. My favorite tool is my sequel Workbench. This is a free tool, and it access an I D for my sequel server. In the next lectures, we will set up my sequel, Workbench and Design the database, often online store with its aid. Another tool that has a free version is sequel Power Architect. This tool offers support for database management systems other than my sequel, The most popular tool out there is Air Wind Data Modeler, which is paid. You can find much more similar tools on the Internet, but for the scope of this tutorial will continue with my sequel, War Bench. Thanks for watching and seemed the next lecture. 10. 09 Setup MySQL Workbench and prepare the work environment: Hello again. In this lecture, we will set up the work environment. In order to design the database, we need just my sequel, workbench. But at the end of this tutorial, we're gonna actually create a database from our design using the four other engineering feature off the workbench. As I mentioned earlier, my sequel workbench, is just an I. D. That allows us to design a database and connect to my sequel server in order to see the scheme us run some queries and so on. In order to actually run the database, we will need my sequel server, which is a database management system. So what we're gonna do next is download my sequel server and my sequel Workbench, and install them on our computer. So please go ahead and open Google. We're start by searching for my sequel workbench, and we're just gonna click on this first result. This is the official website off my sequel. We are going toe the download section. We will search for my sequel community addition, which includes both the server and the world bench. So here it is, at the bottom of the page. And let's just click on download. So I just click on that. And then here the first result, I'll click download, which will take me toe the Donald page. No. Here you can choose your operating system. There are quite a few options. I leave Microsoft Windows since that what I'm using, and I'll just go head to the download page once again, scroll down, and here I'm gonna choose the complete installer nor the web installer. Click on download. Okay, Uh, you have an option here to sign up for an Oracle account, but I skip that for now. Andi, I'm just going to click here on. That's it on gun. Oppose the video until the Donald will finish. Now let's run the installer. It takes quite a fume second stone start. Okay? It takes longer than I expected, but anyways, it started. Let's just go ahead and accept the license agreement. Andi. Okay, let's choose the developer default package because it includes both the saver and the workbench. So I will do next. Now, these tells us that in order for a component connected toe fightin to run, we need fight any stolen or computer. But we don't need it for our purposes. So we're just gonna skip this step, Okay? Yes. And the set up will install all these components, but for us, the most important at the first ones. So let's just execute this, okay? I'll post the video until the set up complete, and we'll get back to it in the product configuration face. So here we are. The set up has been completed. Now, let's configure the sever so we will choose a standalone my sequel server. Okay, we can leave these options unchanged. We have toe set a password for our route user. I just enter. Ad mean you can choose whatever password you want. Seems we run the sever on our local machine just for testing purposes. The security doesn't matter that much. This will install my sequel server as a window service. Next we don't need on X protocol. Next and just around the configuration, we'll skip this part for the router. Okay, let's check if the server runs. The connection is successful. That's great. Apply on. That's it Finished. And we're just going to start my sequel workbench after the set up. And here we are. Now we have a connection to our local my sequel server here enter the best world and now you can see the database scheme us that are installed by default on my sequel server. These are some sample tables for the world schema. We'll have a scheme are just like that for our e commerce website by the end of this tutorial. But for now, let's just close the connection toe my sequel server and focus on the design feature off my sequel Workbench. These second entry in the left panel, as you can see by its icon, is specific toe database design. So what I do next is to create a new model. As you can see, these opened Ah, new tab, and I'll just go ahead and save it as, um, e commerce D. B. And I'll add a diagram. This is the design tool off my Secret War bench, and in the next lectures will create our database model using it. As you will see, we can create a bus, create the relationships at that reviews and many other things. So thanks for watching and join me in the next lecture. When we start our day be design 11. 10 Define Conceptual and Logical Design for an Online Store: Hi and welcome back. I hope you manage toe install my secret workbench without problems and that it's working fine on your computer. In this lecture, we will finally start designing our database. For now, there is no need to figure out all the details off our database. You just have to think off the conceptual entities to model a real life e commerce website . So are you ready? Let's begin. Okay. Police think what will be the main entities off our database? Do you have some ideas? Well, most certainly will need a customer's entity. What else? Well, since the main purpose is to sell something, we will want to create the products entity as well. And we will need something pulling these two entities together and represent a sale. Let's call that entity orders. Probably your foot off having a review system or some categories to group the products. That's very good. Every successful online store has them, and we'll add them later as well. But for now, let's just think off these three main entities. What attributes would you assign them? Let's see what attributes should accustom or have. I would assign them on I D, which will also be the primary key une email so that I can contact them on what, us, the name and the others off the customer. Very important. We must know where to deliver our products. And I would assign a least off every products. I want alot customer to bookmark they favorite products and maybe send them notifications about discounts or other promotional offers for their favorite products. So the end result would be something like this. Okay, what about the product attributes? What would you do? Well, I would add on i d a name in about section the price, the brand, a brand description and the category where the product belongs. What about orders? I added the product and the customer. Since you must know who bought what the purchase date so that we know when and a warranty field that will start the numbers off years. The product will be under warranty starting from the purchase date. Now, if you have some experience with database design, you might notice that there is room for improvement. I did that on purpose so that I can show you in the next lecture how to improve these design. So for Now let's up on my sequel, War Bench, and create these entities. So here we are on my sequel Workbench on the diagram tab on the left side. As you can see, there is a toolbar with several bottoms, and for now, I just use this one to create a new entity, so I'll double click it. And as you can see, there is, ah, panel open with all the tables properties. Andi, Let's put this name here, customers and at the columns, the I D, which is an integer by the way we lose several data types. The most important for us will be integral to represent numbers voucher to represent strings on. Probably we will use float for numbers with decimal point. But for now, let's close this So the idea is off. Type. Integer is a primary key. Next, I'll at the email. The data type is correct. The name Okay, T address. We'll use a larger field for the address off 2 50 characters on the list, off favorite products. The same. Let's he was a bigger data field on. That's it. We created our customer stable. There seems to be a mistake here. A column has been added by mistake, so I'll just go ahead and remove it like this. Okay? And now I'll add the product stable. The i D. Which is the primary key. OK, no, I let the name on about section Once again. I'll use a larger field for the about section the price. I'll use afloat data type for the price aan den. I add the brand. Okay, brand description on and the last thing the category that will start the categories to which this product belongs on. That's it. We have the product stable. Andi last. Think the orders. As I said earlier, we'll have, ah, product. I'll choose data type off watcher customer. And now the product and the customer together can build up the primary key off the order stable. So I at these check here now on order, is uniquely identified by a composite primary key made up by the customer and the product that he bought. Later, we will see that we can replace these fields and used the ideas and build a one to many relationship. But for now, let's just keep it like that. And as I said, I had the warranty off type ain't on the purchase date, which is Ah, date. Andi, That's it. We are done with the first part of the design. Now let's move on and apply database normalization rules on our design. In the next lecture, you'll find what that is, and I'll demonstrate how it works on our database models. So for now, thanks for watching and seeing the next lecture. 12. 11 What is Database Normalization: hi and welcome back in this lecture will go over. What is database normalization and why is it needed? Afterwards, we'll apply what we learn on our database model database. Normalization may some confusing in theory, but don't worry. When we go through the normalization steps and apply them on our database, you'll see that in practice, it's not such a big deal. We know. For the Adu, let's begin. Normalization is a database design technique, which organizes stables in a manner that reduces redundancy and dependency off data. We never talked about redundancy, and you may I ask what it is? Let me clarify that redundant data is useless data or the same data that it is stored in more than one column or table going back to the definition database. Normalization is a multi step process that puts data in tabular form by removing the complicated data from the relation tables. It is used for mainly to purposes, eliminating useless data and ensuring data Dependencies make sense, meaning that the data is logically stored without normalization. It becomes difficult to handle and update the database without facing data loss. Inception up nation and Galicians anomalies are very frequent in databases that are not normalized. Understand these anomalies? Let's take the example of this customer table. Let's start with Adaptation on normally, to update the address off a customer who cures twice or more than twice in a table like Adam. Here we will have to update the address column in all the rows, or else that I will become inconsistent in sectional. Normally, suppose we have to register a new customer name and address, but he has not opted for any favorite products yet. Then we will have to insert now in that column leading toe on assertion. Anomaly. Lastly, let's review the deletion anomaly. If a customer has only one favorite product and he temporally drops it when we delete that role, the entire customer record will be deleted along with it. The inventor of the relational model, Edgar Coat, proposed a theory off normalization with the introduction of the first normal form. Afterwards, he continued to develop the second and the fourth normal form. Later, he joined Raimond Boys and develop the Boys Code Normal four. The theory of database normalization is still being developed today, and there are discussions even on the sixth normal four However, in most practical applications, normalization achieves his best in the first normal form. So in the next three lectures, we will apply the first free, normal forms on our database model and see how that works. Thanks for watching and let's get to work. 13. 12 First Normal Form: hello again in this lecture will discuss about the first normal form. We'll see what it's rules are, and then we'll apply them on our database design. So what is the first normal form? The definition from Wikipedia says that the first normal form is a property off a table in a relational database. A table is in the first normal form if and only if the domain off each attributes contain atomic values and the value off each attribute contain a single value from that domain. This definition my sound on biggest, but the rules are pretty simple. What it means is that every table cell should contain a single atomic value, and each record in a table needs to be unique. So let's see how that can be applied to our customer stable. Imagine we have the following customers now. The first rule says that every cell or attribute should contain atomic values. By looking at the name column, we can already see that the first normal form is not satisfied. Imagine that you want to send a newsletter and address the customer by its first name. Of course, you as a human could identify which of the two names from the name column is the first and which is the last. But it is very hard for a computer to extract and separate them. This is the main reason why the first normal form and forces atomic values. So what we have to do is to split the name column into first name and last name columns like this. Probably you already figured out that the address column is not in the first normal form, so we will apply the same mechanism for these column as well. The result is the state City Street and number columns. Now let's move to the last column here. We have to take a different approach. As you can see customer, we tidy. Number one has two different values. Separated by a comma in his Favorites. Products column you may say, OK, let's plead that column into frayed repo Dog number one and favorite product number two. Well, what happens if a customer adds another favorite product and another you will need to keep adding columns to the table? Probably already know this, but you are not allowed to do that. You are not supposed to alter the table structure. Does the design when you need to. Other record for the scenario. There are other steps. Let's see what they are to bring the model in tow the first normal form. We split the strings we used to hold our favorite products in tow atomic values. And we ensure that narrow contains more than one favorite product now. Not that the primary key the I. D column is no longer unique in the solution. We have duplicated customers so, ironically, identify Row. We need to use a combination off I. D and favorite product. This is a requirement off the first normal form, since we cannot have two or more columns with duplicate rose, so we must believe the table in two tables like this. Indeed, columns do not contain more than one favorite product in these design is that each customer favorite product entry appears on its own roe using customer ideas a key I want to. Many relationship exists between the customer and the favorite product. By the way, we will discuss about the table relationships later. In this design, a row in the parent table, customers can be associated with many favorite products. Row in the child table customer favorite products. Also, please note that the entry for customer number two for Mary doesn't appear in the customer favorite product. Mary didn't choose any Fareed product, so there is no need to insert a row with the newer value there. Okay, I hope you got the idea behind the first normal form. If not, don't worry. We'll go to a few more examples. Let's look at the products stable. Imagine. We have a Nikon seven record and on our website we want to display its name, then attack line, some photos and afterwards, a description off its detailed features. Then it's price and so on. By looking at this table, you can notice a few problems. The about column holds both the tagline and the description for the iPhone. This doesn't respect the rules of the first normal form. OK, moving on to the category column. There are a few values separated by commas. We have to change that as well as in our previous example. The about column can be splitting to tagline and description, and the category has to be moved to a new table, since we can assign the product toe on indefinite number of categories, So the end result will look like this. Andi, that's it. All you have to do is to split the columns to atomic values. And if multiple values can be assigned to a record, you have to move them to a new table. This new table, the child table, will be No. One to many relationship with a parent table, but we'll discuss more about the subject later. For now, let's move to my sequel were Bench and Update Our Design. So here we are. Let's start with the customers. I'll go here and, uh, added the customer table. First, I'll remove the name and add the first name and last name columns, and I will move them up in the design. Now I remove the address and add state, city and street. And the last thing to add is the number. The street number, which will be on in okay now for the customers. I'll have to add the customer favorite products stable, so I just create a new table, okay, and at a custom or I D and ah parole docked and the stable will have a composite primary key by the storm attributes. That's great. One more thing before moving toe the product stable. I'll have to remove the favorite Products column from the customer stable because we move that tow this new child table and that's it. Now the customers are in the first normal form, and I'll go ahead and update the product stable. So, as I said, I would remove the about attribute and add, but tagline and the description. I'll make the description field bigger on that's it. And with these new attributes, after the name attribute and ah, I'll create the products category table beforehand. Let's remove the category from the product stable. So that's it. Your table at the product i d. And the category on these two attributes will form the composite primary key off this new table on. That's it. There is no need to change anything for the order stable, so our design is in the first normal form. Thanks for watching and join me the next lecture when we'll apply the second normal form 14. 13 Second Normal Form: Hello again and welcome back in this lecture, I'll cover the second normal form. You'll see what it is and how it works. Now let's look at the official definition. It may sound confusing, but don't worry as well go through some examples. You'll figure out how this works, so the table is in the second normal form. If it is in the first normal form. And no non prime attribute is dependent on any proper subset off any candidate key off the table. A non prime attribute off the table is an attribute that is not part off the composite primary key. So put simply, a table is in the second normal form if it is in the first normal form, and every non key attributes off the table is dependent on the whole composite primary key . Let's imagine we have the order stable and we have a record saying that Mary has bought an iPhone seven, which has a warranty or three years, and the purchase date is the first of June 2017. This table has a composite primary key formed by the customer and the product, the second normal form, says that any non prime attributes meaning the attributes that do not from the primary key should depend on the whole primary key, in other words, both on the customer and on the product. Now let's look at our known prime attributes. The purchase date depends both on the customer and on the product, because Mary bought on iPhone seven on the first of June. The warranty, on the other hand, does not depend on the customer. Anyone who buys the same product gets the same warranty time off three years. The warranty is a property off the product, not off the composite primary key. Thus the stable is not in the second normal form, so the solution is to move the warranty column toe the products table where it belongs. The result, after applying the 2nd 1 more form, is the following. Now the order stable is good to go. What about the customers and the products stable? They have a single column primary key, so we cannot apply the second normal form. The rule is that tables without composite primary keys are in the second normal form by default. That being said, let's move on and update our database design. So here we are, in my sequel Workbench and we want toe. Make the orders. I respect the 2nd 11 form as we saw we have toe move the warranty attribute. So the products table. So I'll delete it from the order stable and added, Here on, That's it. Now our database is in the second normal form. Thanks for watching and see you in a few seconds. 15. 14 Third Normal Form: hi and welcome back. Now let's discuss about the for normal form. Edgar Code, the creator of the relational database model, stated that a table is in the fourth normal form if and only if both of the following conditions hold. The table is in the second normal form, and every known prime attributes off the table is non transitive lee, dependent on every key off the table. To put this in simpler terms for normal form applies that every non prime attributes off a table must be dependent on the primary key. Or we can say that there should be not the case that a non prime attributes is determined by another known prime attributes. So these transit e functional dependency should be removed from the table, and also the table should be in the second normal form to make things simpler. Let's see an example. So let's imagine our products stable. We are searching for attributes or columns, if you like more this naming that don't depend on the primary key and depend on another attribute. The primary key is the ideas for product iPhone seven. Obviously the name depends on the i D. I. From seven is the name of the product with I D one. The same applies for tagline, description, price, quantity and brand. What about brand description? While this attributes is more dependent on the brand than on the product itself, it's something specific to the brand. Not toe the product. If you look at our data, you can notice that we have the same text for brand description, both for the iPhone and for the MacBook Pro. This is exactly what we want to avoid. Data redundancy. This is what the third normal form imposes. So the rule is that we take that information out off the initial table, resulting in two new tables product, which will have the brand description removed and brands where the brand description is start. I hope it's clear how it works. Let's quickly review the other two tables. If you look at the customer stable, every column depend solely on the primary key and not another known prime attributes. John Doe is the name of the customer with i d. One John Doe at gmail dot com. It sees email address NEW YORK It's his state. New York is the city where he lives. Fifth Avenue is the street and the house number as well. So this table is in the form normal form. For the sake of the example, I'll take a quick detour and add another column toe the stable. Let's suppose we are the zip code toe the table. Obviously, the ZIP code depends on the primary key. Is John those zip code? But we introduce a transitive dependency between the street city state, which depend on the ZIP code. By the way, in case you don't know how zip codes work, every street phenomenon specific city from a specific state gets assigned on unique zip code. So for a normalisation problem, we will have to split the customers version to table. As in the previous example. Now I created the table holding the ZIP codes and the data that is dependent upon it. So is this clear? I hope I explained this in a way that's easy to understand. Now let's move on to the order Stable Things are straightforward here. There is no known prime attribute on which the purchase date could depend. Does this table is in the form normal form? Now let's update the database schema with our latest changes Here we are in my sequel Workbench, and what I'm going to do next is to create a new table called Brands and move the brand description out off the product stable. So I'll go ahead and create the table. I'll create the column called Brand, which will hold a brand name so it will be off type March are Onda will move the brand description, and I will assign a higher value off to 50 characters. This is not what we wanted. OK, and now I'll just remove the brand description column from the product stable. Andi, that's it. Now our database is in the form normal form. So thank you for watching and see you in a second by 16. 15 Database Relationships: Welcome to the database relationships section will stop the section by reminding ourselves what foreign keys are, and then we'll explore some specific concepts off database relationships. At the end of the first section, we touch the subject off Foreign keys. A relationship is established between two database tables when one table has a foreign key that references the primary key off another table. This is the basic concept behind the relational databases. Let's look at the example once again. So we have the employees stable with the column i d. Acting as a primary key. The Daily Report Stable has a column employee I D. That references the employees i d. Column. So the primary key column off the employees stable. We say that this is the foreign key for the employees. Probably you already noted that the relationship in this example is off type, many to one, because more than one daily record can be assigned to the same employees. We'll explore this type of relationship in depth in the next lectures. For now, let's move on to some other theoretical aspect. A more niche topic and probably not so well understood about database relationships is what is on identifying relationship versus unknown identifying relationship. Let's discuss about this on identifying relationship this one. The existence so for row in a child table depends on a row in a parent table. This may be confusing because it's common. Practice this day Do not make the foreign key to the parent part off the child's primary key, but the logical relationship is that the child cannot exist without a parent. For example, a person has one or more four numbers. If they had just one phone number, we could simply store it in a column in the table person. Since we want to support multiple phone numbers, we make a second table called phone numbers. You may think off the phone numbers as belonging to a person, even though they are modelled as attributes off a separate table. This is a strong clue that this is an identifying relationship. When you design an identifying relationship in my sequel, it will make by default the person I d as part off the primary key in the phone number stable. That means you will have a composite primary key. A non identifying relationship is when the primary key off the parent table must not become primary key off the child table. A good example of this is by looking at the persons and state stable. Person is a child table with respect to states, but a rowing a person stable is not identified by its state attribute. In other words, state is not part off. The primary key off the persons on on identifying relationship can be optional or mandatory . That means that the foreign key column can be optional. In practice. These principles are not so commonly used and known. Identifying relationships are used by default. Many modelling tools don't even have options for using, identifying or known identifying relationships. My sequel, Workbench, has dis options, but we'll use known identifying by default. Now let's discuss about relationships. Captain Ality. The degree off relationship, also known as Card in Ality, is the number off occurrences in one entity, which are associated toe the number off on currencies. In another, there are three degrees off relationship or card in al it ease, known as want 11 too many and many to many in the next three lectures will explore how these relationships work. Thanks for watching, and soon the next lecture 17. 16 One to one relationship: Hello again and welcome back. During the next few minutes, we'll go through some examples off the 1 to 1 relationship. The 1 to 1 relationship allows only one record on each side of the relationship. The easiest way to think about it is like in this example, country and capital city. A country has only one capital city, and the capital city belongs to just one country. In this example, the country table holds a foreign key with the 80 off the capital city. It was noting that for this type of relationship, the foreign key can be moved to the other table and obtain the same results so we can move the foreign key in the capital city table on reference the country to which it belongs. Keep in mind that this kind of relationship is not very common. This is why I wanted to give you this obvious example for our online store. There is no need to use a want one relationship, but for the sake of the example, and because it is common practice to separate the addresses in their own table, will implement a 1 to 1 relationship between a customer and it's address our initial table looks like this. What will, though, is to split it in two tables will move all the columns related to the address in one table , call addresses and will create a foreign key in the customer stable, referencing its address. The end result. We look like this great. Does this make sense? As I told you, this may not be the perfect example for a 1 to 1 relationship. What happens if John's wife creates an account? She will have the same address as Jones, so you can notice that the same address Foreign Ki 11 is use twice now. Our perfect example off a 1 to 1 relationship will become a many to one relationship. Seems to customers can have the same address while this is logically correct, and I will build this relationship as a many to one relationship in a really development environment. But for the sake of our example, let's keep it 1 to 1. As I said, this type of relationships are pretty rare, and I really wanted to give you an example so that you'll know how to model it. By the way, in a minute, we'll get to my secret what mansion create this relationship. But now the question is, how can we enforce it to stay 1 to 1 relationship? Well, we can use an unique constraint on the column address I d. That means that no two rows can have the same value. This will enforce the DB Administrator to create another address for John's wife. I repeat, this is not optimal. We don't want to start duplicate data, you might ask. OK, OK, but give me an example where this is truly necessary. Okay, let's do that. Consider our initial example with countries and their capital city. Now what if you enter a new country record and by mistake, you point a capital toe the wrong i d. As in this example, you can see that Japan has the capital i d 22 which is Washington. This can happen sometimes right now. Unlike our previous example with John's wife, this is also logically incorrect. To avoid breaking the one toe on relationship and entering incorrect data. We must enforce it with that unique constraint and you'll see how to do it in just a moment . Well, if you have this constant in place when you try to insert the Japan record, you'll receive a sequel, a roar. So besides enforcing the 1 to 1 relationship, you'll also avoid human errors. I hope I made these issues clearly enough. Now let's move to my sequel Workbench and create the relationship between the customer and it's address. So here we are in my sequel were Bench on. What I'm going to do is to create a new table call addresses. Al, just make room for it. Here I will create ah on I D column as the primary key, and now I'll move the address related columns from the customer stable to the address is stable, and my circle has these nice feature off doing a cut and paste. Okay, and that's it. The other sees stable is complete. No, remember, we discussed about identifying and known identifying relationships. In this case, this is obviously on identifying relationship because an address cannot exist without the customer. It doesn't make sense to have an address in our tables if it doesn't belong to a customer. So I could simply use on identifying relationship for this scenario. And what that will do is to add a customer I d in the child table. So in the address is stable and added to the primary key. So the addresses will have, ah, composite primary key. But in practice, I don't need toe do this because it doesn't help much. So I'll just create a simple, known identifying relationship. And as I said, I prefer doing it this way for other relationships as well. So now I'll place the relationship. And as you can see, my sequel, Workbench has these nice feature toe highlight, the primary key and the foreign key when you hoover over the relationship. Also, please noted that, um, these icons here at the end of the relationship represent a want one, and you will see soon how ah, man, it one is represented. Andi, That's it. The relationship is built now to enforced the one toe on relationship. I'll open the customer stable on for the address. I d. I have to say that it it must be unique right on. You can do it like this or also you have these here dis option on. That's it. I just created the 1 to 1 relationship. Thanks for watching and join me The next lecture 18. 17 One to Many Relationship: Hi and welcome back in this lecture, I'll show you a few examples off one to many relationships I want to. Many relationship allows a single record in one table Toby related to multiple records in another table, and this is one off the most commonly used type of relationships. So let's see an example from our database. Let's look at the relationship between products and brands, as you can see in the example, one or more products belong to a brand. That means that between products and brands, there is a many to one relationship. If you look the other way around, there is a one to many relationship between brands and products or, in other words, of brand has many products. Basically, the one thing you should not here is that one too many and many to one, is the same relationship. Nothing changes in the DB. It just depends from which side you look at it. Okay, that being said, before we move to our next example, let's update the design in my sequel Workbench. Now, before I create the relationship between products and brands, I want to discuss about the brands primary key. So even if we could use the brand name as the primary key in the brand stable because no two brands have the same name. I'll add an I. D column off type into which will be a number. Why is that? Well, in most Web applications, it is easier to work with ideas, which are numbers. And on a second foot we do a small size optimization. Imagine that you have 1000 iPhones in your store and for everyone off them, you have to specify the foreign key toe the brand as the world apple. Instead, off a number like one. Obviously, that will take much more storage. So I'll go ahead and create the I. D column off type end. Set it as the primary quim and I'll remove the brand as the primary key I and move the primary key as being the first column. And then I replaced the brand name wit name Simple as that. Okay, and now let's create a relationship. So I'll choose the one to many relationship and click on the product and then on the brands on. That's it. The relationship has been created, and, as you can see, my sequel added by default brands I d for on key in the product stable. No, what I'll do. I'll remove the brand column from here, since it has been replaced with the brands I D for Anke and we're done. Now let's move on with our examples. Imagine the product stable as it is now we are using some assign ideas for our product, considering that every product in the world has a unique serial number. Do you think we could use that in place off our I D? Of course we can, and probably everything will become better integrated with the cash register to make up a Rantisi's for mobile phones. The serial number is called I'm EAI code, and you can find it on their backs or on their manual on using it. You can verify the phones. Authenticity. Let's use that as the I D. Okay, now let's add a few more products to our table. Imagine you have in your store free iPhone seven. You could as well have 1000. Do you see some data redundancy Here? In other words, repeating data. Well, the name tag line description, price brand and warranty are repeating for every product as you can see for the iPhone seven. We should remove that data redundancy from our design. So how could we do that? You may ask. Well, imagine that every product we have is actually a physical item with an unique serial number . The name description brand etcetera are attributes that are the same for each item and represent the product in our case. IPhone seven. They could be extracted in some other table. Think for a moment. How would you do that? Ready? Let me show you how I would id break these daytime. Two tables. One is called products and store genetic data about the product and the other is called item. Here we store specific item related data. This new relationship between products and items is the one to many relationship. A product can have multiple items. Or if we look from the other side, more items belong to a product. Does this make sense? I want to show you a few more details so that you better understand the advantages. Imagine the iPhone Sevens. You want to start the production date? Where will you do that? In the items stable or in the product? Stable. Well, you're right. Not every phone was produced on the same date. So the production they'd becomes an attribute specific to a product item. Now let's see where other attributes could be placed. How about weight items or products? Products. Right? Every item should have the same weight since is the same product? How about location? Imagine your own multiple stores at different locations. Where would you store the location? You are right on the item. Okay, I hope things are clear. Now Let's move on and update the DB design. So here we are again. Andi. I think the easiest way to do it is to add the items stable at the I D column, which will be the primary keen. Andi. What? That's let's start the production date off. Dolly date. Okay, that's good. No, let's build the relationship between products and items. So we want many items to belong to. A product on. That's it. I'll just move the stables around toe. Be easier toe. Seem so now a night, um, has been linked to the products. And as you can see, the multiple side off the relationship is here on the items. That's it. Let's move on. Let's review the products toe products category relationship. Remember, we split the tables like this when we discussed about the first normal form. In this form, A product belongs to one or more categories, so they are in a one to many relationship. Now imagine we add another product and assign it to the same categories. Suddenly we emulate and I say emulate because it's not by the book. Many to many relationship between products and categories. A product belonged to multiple categories, and a category can have multiple products. Well, theoretically, if you keep the categories as simple strings, this good work. But this is not optimal. First of all, imagine that you could have 1000 smartphones. That means that in the products category table, you'll have to repeat the world smartphones 1000 times and another 1000 times the world phones lots of redundancy. Next, imagine we add a category description that we look like this. Besides having lots of redundancy, the stable is not in the second normal form. Taking into account that product idea and category formed. The composite primary key description does not depend on the product, so we'll have to split the table like in this example. What we have accomplished now is preparing the tables for a proper many to many relationship. Join me the next lecture to see how it works and to see more examples. Thanks. See you in a second. 19. 18 Many to many Relationship: hi and welcome back in this lecture will continue our journey with the last type of relationship. Many too many, a many to many relationship allows multiple records in one table Toby related to multiple records in another table. For these relationships, we need to create an extra table. Remember the products, products categories and category stables? This is a perfect example off a many to many relationship. A product belongs to multiple categories, and the category contains multiple products. As you can see in the example, the products category stable is an intermediary table that it's used to build a many to many relationship. The product i D and the category I d are both foreign keys referencing products and categories, and together they form the composite primary key off the products category stable. Now you may think I still don't get it. Why do we need this extra table? And you are right to ask? I didn't get that at first, either. So let's clarify this idea first. Imagine our tables now. How would you design that? One product belongs to many categories. You could add a foreign key to products in the category stable, But how would you design that the category has multiple products. One solution is to add multiple product i ds in the category stable. Well, the solution breaks the first normal form. Remember the columns? Atomic values? That leaves us no choice but to extract these values into a separate table. The good part is, when we do it, we basically built the product category stable. Now the databases normalized and also the many to many relationship is built. For example, Product one and product free belong to category number one, so there are many products in a category and also product. One belongs to category one toe free and four, which means that the product belongs to one or more categories. Next will design these relationship in my sequel Workbench. So here we are in my sequel Workbench, and I'll start by creating a new table for categories. So let's name it categories. Then I'll add the I D as the primary key column, Then a name on a description on dial set a higher value for the description off 250 characters. Okay, this is extra. Let's remove that. So we have the categories now. I will create a many to many relationship, and I'll use this button from the toolbar that places a new relationship off type Many too many, and you will see that my sequel is that smart. That builds the intermediary table for us. So let's just do that. The many to many from categories to products on. That's it. My sequel created this intermediary table, and name it categories has product that will be good enough for us as well. You can notice that the relationships between the tables and the intermediary table are off type identifying, and that makes sense. If a category doesn't exist, there is no way it can appear in the categories. Has products table. The same rule applies for the product, and the category has product. Also, please note that the categories has products. Table has a composite primary key, made up off categories. I D and products I. D. And this is how the identifying relationships work. Well, that's it. We have these relationship build, and the last thing to do is to remove the old products category table, which will be useless. Now Let's arrange the stables toe, see them better. Okay, so that's it. Let's move on to the customer favorite products relationship. What do we want obtain here? Well, firstly, a customer can have one or many favorite products. For example, Joan can have both the iPhone seven and Galaxy S eight. On the other hand, we want Alan Toby ableto add the iPhone seven to its favorite list. Thus, we want a product to be assigned to multiple customers. This means we need the money to many relationship. How can we build that? Obviously, by using an extra table will call it custom or favorite products. Now let's represent the data we discussed. So John, meaning customer number one, likes products with ID's one and Free and Alan likes product i d. Number one. That's it. So let's designed the relationship. So how do we do that? Once again, we select the many to many relationship and willing the customer toe the products on. That's it. My sequel created for US customer has products table. I remove this old one since we won't use it anymore to avoid any confusion, I will rename the stable to customer has favorite product. Andi. That's it. We are done now that you know how to work with every type of relationship, let's move on and finalize our design. Thanks for watching and seeing the next lecture 20. 19 Finalize Database Design: Hi and welcome back. We are getting close to finalize our database design. We'll start this lecture by designing the order stable, which is one of the most important tables in our database. Then we'll go ahead and see a few more examples that will include categories and parent categories and a review system for our e commerce database. Let's move on to the final relationship. We have toe build the orders. Orders are at the heart off our e commerce store, the whole very important data related to purchases. So what an order has to do is to make the connection between a customer and the items that he bought. A customer can have many orders, but an order can belong to only one customer. Otherwise, it wouldn't make sense. Logically, that means that the orders are in a many to one relationship with the customer. Thus, the order stable will hold a foreign key with the customer I d. We'll go ahead and also sign an I D to each orderto have a primary key. Please note that the order stable could have worked with a composite primary key formed by the customer I D. And the purchase date, but they are easier to reference if we assigned them their own I d. Next on order could contain one or more items because the customer may by more than one item, for example, he can buy two iPhones, one for him and one for his wife. So we could add on items I d column toe the order stable and store the ideas off the items . But then again, that will break the first normal form, right? We just have to store one value in one column. This leads to the creation off the orders items table. One last thing to do is to add an unique restriction toe the order items Item I D column. We don't want the same item, Toby added toe two distinct orders that would be logically incorrect. Also, in this way, we ensure we have one to many relationship between the orders and the items, and not many to many relationship. Is this clear? You probably are wondering why I didn't just added the foreign key in the items stable to reference and order. In that way, we would have obtained a one to many relationship without the need, often extra table If you prefer doing it that way, there's nothing wrong. You are completely right. As I told you in a previous slide, designing the DB is both sides and Arc, and the designer can make his own choices before creating the order items stable and keep the data lady to cells in separate tables. It's just a matter of preference. Another lesson that you could get from this example is that you could represent a one to many relationship using an extra table like you would for a many to many relationship and other unique constraint. That being said, let's go ahead and update the DB design. Here we are in my secret war bunch. Let's start designing the order relations first. I'll create, ah, one to many relationship between the orders and the customers. So let's do that great. And by the way, I remove this old column. We don't need it anymore. And instead of the product, I'll create a rename it so I d. And the signing into your value. And this would be the order's primary key. No, What I'm going to do next is toe create ah, link between the orders and the items so Let's move the brands away from there so we could create the order items stable manually and then create the one to many relationship between the orders and the order items and the same between the items and your items. But I think it would be easier if we just create the table using a many to many relationship and then rename it to order items and add the unique constraint on the items column. Andi, that's it. The design is already now. Let's improve our categories. Design. Did you notice how every online store has this navigation that allows you to switch categories and see where you are in the store? What we want is to do the same thing for our online store. How do we do that? Can you guess? Of course, we need to add a parent column in the category stable like this. Now the category stable is in a one to many relationship with itself because a parent category can contain multiple Children categories, for example, phones and tablets both have the phone and tablets parent category. This type of relationship is also called self referencing relationship. Let's go ahead and create these in our design. So here we are in the workbench. This would be uneasy job. I'll just create one to many relationship here on the category stable on That's it. My sequel added the categories i d for on key for us. Uh, I'll just rename it tow. Avoid confusion, Toe Parent. Got to go these i d and that's it. Let's move on. The last thing that we're going to do is to implement a review. Stable. What do we want to achieve? One or more customers can leave reviews one or more product. Thus the review stable access an intermediary table between products and customers, creating many to many relationship. The table will also store a rating and review description for each record. The customer i D and the product I d will create the composite primary key for the review staple, since they are uniquely identify our review. Does this make sense? Okay, let's go ahead and create the design. Here we are again in my sequel workbench, so I'll go zoom out to see better the database. Okay? No, What we want is to create the review stable, which willing the customers and the products. Of course, we could do that manually. But since it's acting as an intermediary table, you know many to many relationship. I'll just use the many to many relationship button here on these. Created this table for us with the A composite primary key having foreign key as well toe the customers and to the product. I'll rename the stable as reviews. And then I'll add the columns rating, which will be a number from 1 to 5 eventually under description, which can be off higher value. Okay, let's remove that extra column on. That's it. We are done. I move the reviews higher. Okay, so now our database design is completed, the databases normalized. All the relationships are built and we are done. Don't forget that you can download these design from the resources section off. The lecture on in the next lecture will talk about how to do forward engineering and generate the actual database. Thanks for watching. See you in a few seconds. 21. 20 Generate the Physical Database : hi and welcome back. We are getting close to the end of this tutorial, and I really hope you enjoyed it so far in this lecture. I'll show you how to generate the database from your design, using the forward engineering feature off my Secret War bench. Then I'll go ahead and add a few records. I show you how to do it either manually from the former editor or using inserts scripts, and then we'll run a few sequel queries. Then it will be up to you to add more data and play around with sequel queries and have lots of fun with the database. I'm thinking off creating a part two off this tutorial where I would cover sequel queries, insert script views, start procedures and so on. Since this is the first time I ever created a video, I'm waiting to see what the feedback is. So if you liked it, please leave a review. I would deeply appreciate that, and this will encourage me to create the second part of this tutorial and probably many others on the subject off Web development. For now, let's open my sequel workbench and continue our work here. Reata, now probably you remember when we talked about the three levels off database design, the conceptual logical and physical? Because we added the data types here. So int var char date and so on. And because my sequel workbench automatically transforms the entities in tow tables and the attributes into columns, what we have here is the physical design, because off that we can generate the database directly. So if you look here, this is the name off the database might be beats the default name that my secret workbench assigned to our baby. So I just rename that toe. I don't know. My store, for example. You can choose any name you want. Okay, so that's it. Uh huh. And now, from this design, let's go to the toolbar. Ondo, Uh, forward engineering. I'll click the forward Engineering Manu and three that will start the whole process. Now, forward engineering wizard will be opened. And the first thing my sequel more bench asks us is the location off the my sequel server so that it knows where to build the database. Of course, we're gonna choose the local instance as I did here. Andi, there are no other things to configure in this page, so I'll just go ahead and click next. Then there are a few other options that can be selected. For example, if you don't want toe create the foreign keys, of course we want to do that. So I leave these check box unchecked, create the index, ease the answer of state months. All these settings can be ignored for now what? It's, um, something to consider. This drop configuration here can be used. If you are planning toe, build a database and then come back to your design, do a few changes and build it again using forward engineering. Then you will want to check this button here, this check box so that it drops your previous database. But you should just be careful that this will also drop the data that you created. Maybe you already know it, but in this context, drop means delete. Okay, so I'm not checking anything here because it's fine for us. I would go head, then click next. Okay. On here, you can choose what entities to export or what tables. Of course, we will want all off them. So if you leave these check box checked as it is by default, you should be just fine. I'll just click next and go to the next page on Dhere. In this screen, you can see the sequel statements. Then my secret workbench automatically generated in order to create a database. So this creeps create the tables. The columns assigned the data types create the primaries and the foreign keys. But there is no need to understand everything that happens here, So let's just go ahead on that's it. So in order to test that our database was created successfully, we have to go here toe home the home page and then toe the connections to my sequel connections. Goto the local instance. So toe the my sequel server on our local host on as you can see here, here is the database, my store and with all the tables. Now let's add some products to our database and just go ahead and click here on the product stable and oh, right click, and then you can select the 1st 1000 products which will be explained here in the screen. Of course, we have no entry yet and less create one, so I'll go to the form editor and here you can add the product. So let's add the iPhone seven. Okay, I'm using the data samples from our slides, and we cannot to sign yet the brand because we have to create the brand first. But for the moment, let's create the problem product. And you can see here that my speak all generated the he said script Al copied for the moment, and you'll see why in a second and just apply. Okay, so this tells us that there is an error for creating this product because off the the fact that the brands I d don't don't have a default value, so then I'll cancel this action. I just wanted to show you that these foreign keys constraints, work and trigger errors if you don't pay attention. So let's go ahead and create first a brand. We'll do the same action. So either from the former editor here. Sorry, let's choose Apple and a description. And now we have we apply. And now if you go to the table once again, toe the agreed, you should see that the brand has been added here and now I just go back to the product and select the brand I D. One and apply apply. And now we have the iPhone seven in our database. Now I'll add the MacBook Pro in the products stable, and I'll do it in a different way. Al used the outer generated insults script that was generated by my sequel workbench when I tried to enter the iPhone seven. And as you can see here, this sequel command states that you have toe enter the values, these values in this order so that they match the columns that you enter here. So first of all, you cannot is that since we didn't added the brand last time, we have to add that column because it was not out of generated okay. And now I just change the values with the ones for a MacBook Pro five years off warranty and the brand I. D. S. One. And now I'll execute this query and then I'll go and select the products again. And you can see here that we have the MacBook Pro as well. Now let's add the galaxy s eight. And for that I'll have to add the something brand first. And now I want to show you the furred way off. Adding data is toe, just go here in this result, greed and and eat in the columns directly. So I d. Number two name Samsung on a description. And the only thing left to do is to click the apply button here. Now you can see the insert script was auto generated again, just doing apply on. That's it. Now the brand has bean properly insert in our DB. Now I'll go back to the products and and product. We tidy number free Galaxy eight and entered the brand number two. Now I'd like the apply button. Okay, finish now let's run a few queries. So what I'll do, I'll go to the query stab, and here I'll enter the easiest query possible to select all the product. So select oh, from road docked Ron. And as you can see, we have here the least off products. What if we want to choose just the products that are branded Apple? Well, then we'll have to add a wear clothes. And now, as you can see, we'll have here only the iPhone and a MacBook pro. And if we want to count how many Apple products we have, we can adhere the count function and once again run the queries. Andi, that's it. As I said, if I see that you and other people found this tutorial useful, I will be the second part where I'll go in depth into the subject off sequel queries. But for the moment, I want to recommend your very nice place to start. If you're a beginner, I'm talking about the V Free schools dot com website that has a very nice introductory tutorial toe sequel on If you want to learn more, I'm pretty sure you will enjoy what they offer here. So for now, thanks for watching and join me in the last lecture when we'll draw some conclusions about the stuff you have learned in these database designed tutorial Thanks and see you in the second. 22. 21 Conclusions: hi and welcome to the last lecture. It has been a pleasure being with you here, and I hope you are now more confident about databases and how to design them before we say good bye. Let's review some of the things you have learned today. We started our discussion with a short walk through through the history of collecting data . And then we define what the databases. Afterwards, we learn about the use of Beatrice in database management systems, which provide a logarithmic search complexity. We explore different types of data basis, and then we focused our attention on the relational databases. We talk about database design and the steps you must take in order to model a real world problem. Afterwards, we discussed about one off the fundamental principles the database normalization, and we took a deep dive through the 1st 2nd and fourth normal forms. Then we explored database relationships, and you're so several example off want 11 too many and many to many relationships. Finally, we completed the design, often e commerce database and generated the actual scheme us. Using my sequel workbench and forward engineering. I want to thank you for your time and to congratulate you toe have completed the course. And I hope you feel you have learned some valuable information and that you feel more comfortable to design a real world data base. Let me know if you like the tutorial and until next time, I wish all the best take care.