Data Modelling and Database Design for Business Analysts | Dr Monjur Ahmed | Skillshare

Data Modelling and Database Design for Business Analysts

Dr Monjur Ahmed, Experienced Educator

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
11 Lessons (1h 26m)
    • 1. Introduction

      2:37
    • 2. Course outline

      5:25
    • 3. What is Database

      5:26
    • 4. Relational database

      8:40
    • 5. Primary Key and Foreign Key

      14:21
    • 6. Meta Data

      2:35
    • 7. Entity Relationship Diagram

      13:22
    • 8. Resolving Many to Many Relationship

      7:56
    • 9. Design Database from ERD

      5:51
    • 10. Normalization and Dependency

      17:11
    • 11. Database Design Steps

      2:18

About This Class

What will You Learn?

A student will achieve two important skills required for a Business Analyst - Data Modelling and Database Design. After completing this course, a student will be able to analyse business cases, develop Entity Relationship Diagram (ERD) and design Relational Database. However, this course is NOT about database development on any specific DBMS. A database design can be implemented using any available tool or DBMS (e.g. Oracle, SQL). Data Modelling and Database Design are independent of any specific tool.

Requirements

Basic computer literacy

Overall Course Objective:

  • Gain two essential skills to become a Business Analyst
    1. Data modelling
    2. Relational Database Design

Learning Outcomes (LOs):

  1. Understand Database and Relational Database
  2. Understand Data Modelling
  3. Learn to Develop Entity Relationship Diagram (ERD)
  4. Design Database
  5. Analyze real life business/organizations to model data requirement

Course Outline:

  1. Database and Relational Database
  2. Tables (database tables and its elements)
  3. Case Study Analysis (real life scenario analysis)
  4. Entity Relationship Diagram (ERD)
  5. Anomalies (Insert, Update, Delete)
  6. Dependency (Functional, Partial and Transitive)
  7. Normalization (UNF, 1NF, 2NF and 3NF)
  8. Database Development Steps

Target Audience:

Anyone who wants to become a Business Analyst, or want to learn Data Modelling and Database Design.

Transcripts

1. Introduction: Hi. Welcome to take a modeling ended up his design calls. In this ghost, we learn how we can analyze business and are use to model data on eventually based on the data model how we can design a database. But before we it starts this course we need to satisfy why we need to take this course. So let has explored this ghost is about to get a mortally, which means capturing the data requirements for a business from the DOJ operation and to eventually be able to design. Objective is this course is not about how to develop and implement a database. However, data modelling and Jacob is design is pretty inquisitor to be able to developing, implement a database. And for any software and information systems development data modelling is crucial parts. So is getting messages, and because they are the design part of any software system on designing is a very crucial factor. Because accuracy off any system depends mostly on design. There are any mistakes or errors done at the designer Strange. It costs more if it's the real later on, compared to any errors a mystic that are found in the development stage so to have on air a priest system to have an integrated system. It is very important that we consider on we learn how to design a system in this case. In this course, we're going to see how we can just like the data responded of a system. So as the name says, this discourse is warranted with data modelling and database design, which are two very important skills for any business analysts. So to sum up in this ghost, we're going to learn design part off database for which we require Jim ordering. And Jacob modeling is all about understanding what our business does and what 100 later there would you retire if our systems developed for them. So let us start. Thank you very much. 2. Course outline: hi in this really? Well, look into the learning outcomes on a course outline for these court selects. First, distract with the learning outcomes. The first learning outcome is to understand our database and our relational data bees. We will learn how we can model data for a business. For that, we will learn how to develop entity relationship diagram based on this learning outcomes when we learn up to developing entity relationship diagram, this will help to fulfill our another learning outcome, which is to be able to design and database. To do this, we will analyze riel life business or organizations so that we can model the data requirements for the business organization and to design identities for the organization. So these are the learning outcomes for this course. Now we look into the course outline. The first, a topic that we're going to cover except summarized, Choppy will have ah number of sub topics when we dive into the course. So the first topic is well, I understand what is a database and what is a relational database. Since the tables elements off database collection of tables make a database, we will look into what is a table on what are the elements off our table. We will also look into a very important part off get. It is designed to make a database design integrated, which is animal is. We look into insect object angelic animals. We will analyze cases, studies, real life scenario, business on organizations that you find out how we can develop data modelling artifacts and eventually weaken Design, added agrees. In this course, we will also family arise ourselves with the database development of steps which are conceptual, a logical and physical design of a database. However, in this course we are not exploring. Jacob is development and implementation. So we will leave the physical design the last part out off this cup of this course. But we'll briefly learn all the steps off database design and implementation altogether. Who's against the database development? That's part of the whole process. We we need to learn entity relationship diagram that we learn a very important part off data modelling that helps us to structure the Gate of Rick Arm is for a business Thanks. Once you make that entity relationship diagram, we are able to design the database where we need to look into some important aspects, like dependency on In this course, we talk about functional partial and transitive dependency. Once they have covered this, we will look into normalization and other very important parts for to make every three efficient at database design actually mobilisation we Rickard joined, we were captured, understand dependency that way, uncover before we start normalization. In this course, when it comes to normalization, we will look into a normalised form off data and then we look into first normal form, a second normal form and technology. These are not the whole of the story when it comes to normalization. But since this is a beginner course, we will look up your shirt. No Muslim to these other course outlines for this coast. Now it would be a good idea to look into the overall course objective. In a nutshell. In this course, we're going to learn toe essentially skills like every business analyst needs, which is data modelling. You are just joining a business. I'm just training what is going on in the business. Understand your business operation in their data requirements and structuring structuring the requirements so that we can develop. We can design and develop a good database for that organization. So once we know data modelling this relieving surely help us to game Driskill off relational database design. So these are the overall cost objectives. So we have seen that learning object is for these coasts on and the course outline and also over course objectives that you can see on this like so we now are ready to start our first topic. Thank you. 3. What is Database: Hi. In this video, we'll see what is a database. So a database, as its name suggests, is a base arc Tater. In other words, addictive. It is a place where we keep data. So what is data? Anything that we're interested about? Two kids? Um, it might be a list of furnitures. It might be a list of customers. It might be a shopping list. So how do we store the gator without planning? Yes, we can just keep the data were, however you want to, but this is problematic. I'll give you an example. Let's say I have five hundreds CDs of movies. Some of them are cultural owns. Some of them are side by movies. Some of them are action movies. Now, if I keep them all together, then to find any specific moving I need to search. All the cities are giri lease. But if I categorize them as cartoon SciFi on action movies, and if I sort them alphabetically, then it will be very easy for us to find of the movie that we're looking for so we can keep data in a real life or in the computers. We need some unspecific plan planning this specific planning means having a specific in structure having a specially standards to keep our data. So in this goes, as we talk about relational database, we will be talking about tables So the specially structure where we can keep data is called a table. A database is a collection of tables, so now let's have a look into how table looks like. So here is an example of a table. A table is a collection of rows and columns, and, as you can see on this light that we have a table. And let's say that we give the name for this table as a student, which you can see here, and we have this table where we have columns. One column is a student I D on. We have two more columns. The name and address and columns are also known as domains because they keep the similar type of data from look industry and Idea column. This column has only one kind of date of which is a student i d. The same applies to the rest. Two columns in the name column You find only one kind of traitor, which is the name off the students. So that's why these columns are also known as domain. On the other hand, we have rose. As you can see, this is one. Drove one, a student's name, student I D and address. So each row contents. Let's assume that a complete information about one student. That is why the rows are also known as records. So if I look into any role, that row gives me the record off. One student, Osama. A table is made up of rows and columns where the columns are domains because they have similar type of data and rose are also considered as records because one grow gives some complete information about one example off off the domain. So if we look into the roads, the rest Rohit is giving us the complete information about one a student and and so one in this connection of probably were to say that the table we're looking at is not a very efficient one, but we are not worried much at this moment. As we just started learning later on, you will see that keeping rub the whole name in one column probably is not very efficient. We'll probably divided into first name and last name and will make two columns instead of one column. However, that will not affect our understanding on tables for the time being. So this is a table, and when we have a collection off tables, then we call them database. So do activities. Once again, is the based where we keep data. Database is the place where we store data. We looked into a table and it's elements. But this is not the only way to restore data. Data can be story in different ways, but in this ghost wear look into the database design, which is made up off tables. So we will explore how we can extra data in tables. Thank you. 4. Relational database: Hi. In this video, we'll see what is a relational database. We know that our generous is a collection of tables, but a relational database is not a whimsical or random collection of tables, but the collision of tables and their relations. When it's a relation we mean how these tables are connected, for example, left safe, objective, reserved five tables. We might ask the question. Why this wife tables are making these databases. Why not other tables that is defined by how those tables are connected on themselves, in other words, how these tables are related? So the relationship among get tables men's, how the tables are related or connected In this connection, I could probably tell you another front, nor that relation is actually the name of the tables. When you add France injected discourse, you see that the tables are actually called the relations. But for the time being, for our understanding. Lex at the relational Mr Relationship among databases, in other words, how the tables are related or connected among themselves, for which a collection of tables are called one databases. So we know that relational activism is a collection of data base. By now on that collection of Vietnamese tables somehow related among themselves. So why we need relational database? Let's have a look at an example. First, let's say that in an organization there are two different departments admissions department and accounts department, and they're keeping student records on in admission department. They keep just I. D name and address for our understanding left so that they keep only this information. But in the Accounts department, they not only keep this information, but also how much is total fresh, um, here on how much they paid and how much is Do you? So now if you look carefully if the tables are not related, this is not a relational database. Just to different departments are keeping the gig according to their own convenience. On If we Look into the raid highlighted part for the first student here and here we see that the address is different. For the same student. It is obvious that one person can have more than one address previous address. But at any given time, the contract address or meaning address is normally one, or if at least let's say, fix more than one than all the departments should have same information. But in this case, in one organisation, different departments. I'm not having the same information. So something is wrong here. Either they have in complicate information. I mean, let's say that the student has both actresses in that case, bold, aggressive should be, uh, in knowledge of what? The department, the departments. But in this case, this is definitely not the case. The reason is this is not a relational database. So they do not have any idea about the objects that are happening in other departments. For example, exit. The indignation departs in the department. They know that the student lives in Hamilton, New Zealand, but later they moved to Otago, New Zealand and the change directories in the Accounts department, but not with admissions and admissions department have no idea about it. This is a problem Later on, in this course, we'll see. What are these problems, how we solve them. But for the time being, we know that this is a problem. To overcome this problem, we design relational databases. Let's have a look into a relational database. Now then, this is a very simple example a slightly modified version off the tables that you have seen in previous light. Let us assume that this is making a relational database. However, this is not a very good database design or this is not a very sophisticated religion. Give his design as we legacy that this is not the way we do. But this presentation is for our understanding how tables are related among themselves that makes them a relational database. So if you look carefully, you'll see that in this presentation on the slide, the Accounts department now have only student I. D. Here. Notch name and addresses, and the name and address is coming on Lee at this park. So now in place, student lives that changed their address, then they will have only one place in this table to change their address. So if other department makes address, the need to come to this department to ask what is the address of the student and they will not have multiple couples, there will be a single companies or any changes it will be accurate will not have a problem that we have seen in previous life. Now let's so this is relational database where you make relationship you ever duplication off major to make sure that there are no errors. There are no mismatching information for the timing like us be known technical because this is what we're going to learn throughout the course. How we can make a relational table like the one you see the very simple one in front of our eyes. So just let's ask a question that how we can know the name of the students who paid less than 500. If you look carefully, that counts department the table like they have gifts as well. The information off the students who paid less than 500 for example, that's the distance student here. Record number one on the system here Record number floor They prayed less than 500. But we also need to know the name of the student which this Accounts department table is not able to give us. For that we need to go to the other table. If I ask this question, give me the names of the students are paid less than five hundreds. First we go to this table. OK, this is really great. Listen, 500 What is their i v 1234 So what is the name. Let us go to the other chip with 1234 Out here they sister. His name is John Smith in the same way we see the other one the phone to record in the table and we move to the other table to find out that the name is Mark Taylor. Because this student I d is telling us that this is student. Is this a student? Now we see that these two tables are related in terms off our popular retirement on also at the same time, it helps us to element if some problems that you have seen in a previous life. So however, as I said earlier that this is not an average injected his design, but that does not harm our learning on the concept of water is a relational database. So this is what we call relational database, where the tables share information in such a way that we can find all the information that we need. Yet it helps us to eliminate some problems. For example, the one we have seen in in the previous slide. Once again, this is not a solid relational databases designed buddy. By now, it helps us to understand the concept of relational database design. So now we understand what is relational idea? Give him his design now, ready to move forward to look into Jim modeling. But it is just another first that would see gradually. It doesn't say that throughout this coast. This is what we're going to learn. What is a relational database? How we can develop a relational identities. But for that we need to understand a few more concepts for you. More terminal. AGIs. That's what we let's throw. Thank you. 5. Primary Key and Foreign Key: Hi. In this video we'll look into primary key and foreign key to a very important aspect that we need to understand if we want to design and develop relational databases. So let's distract here. We're looking at a table. The cable has Collins and Rose and we have familiarized ourselves on table special. This the circles one is one row or one record in this table. No, If we ask the question how we can uniquely identify the above records is each the student I d Or is it the name or is it actress? If we want to identified, This is student. We can say that. OK, we're talking about at least this meat now. The problem is there might be more than one person who has the same name so name we improbably are not able to uniquely identify someone by just my name was people share same name and the same applies to address. So we need to have something that will uniquely identify only this a student. If we look carefully the student i d. And if we have a rule, let's say that if this 3 to 45 student i d. If it is given toe this Alice Smith. With this student, I d must not be given toe anyone else. This instrument allergy will only be for this a student. Then we can say that this student I d could be and the unique identifying for this a student. So we need something one column in our tables that will have a unique value and that value will not be repeated. So if we look carefully in this column, we see that and we need to have a rule that there will not be any repeating values. So if we look carefully, this value is unique because no other was. Regardless of how many rules we have four or 4000 or four million. This 1234 or 3 to 45 must not epi actualize. If they appear twice, then they will not be and unique identifying on the other hands. This name Alice is made or listed. This name John Smith. We cannot prevent them to appear twice because someone else might have the same name. And we cannot say that. Can you change your name? Because we need to put them in jail base and we cannot put if you have same name, it is not possible in real life. So we need to have something that isn't that will have unique records. In this case, we have the student i D. And this unique identifier for each is students is known as primary care. So in this table, the Student I D column is that primary keep. So if we have 3 to 45 mystery number who just for Alice is made, there might be some other Ellis is me someday my guru, a lot of other students in the same database in the same table with the same name, but they can be identified separately because the student I d will be different for everyone. So this is the main concept on primary key Primary key. To sum up, a primary came in, I call them, which will have a unique value. The Valley will never appear twice, and by that we will be able to uniquely identify its record as you can see on the slide. So now we know what is primary key. It is time for us to move forwards to learn about foreign king. Now let's look into this table we, uh, family with this table. In previous lecture, we have seen something like this. Now we have a question of us like that. Why? These tables are not exactly a relational database. Because if we say relational databases that the tables are connected and we have to understand relational. Beautiful is we used to sing with that kind of table where we have seen that. Okay, they are connected, but they're not exactly relational activities. And let us find out why it is not exactly relational databases for each table. For each table, there must be an unique identify where by which it record record can be I've enjoyed. Now, if we ask the question for the table that Accounts Department keeps here, how can we identify each transaction? Now? The problem is that we can't identify its transaction. Restaurant number 1234 the page three hundreds. So we know which is student paid. But if we ask that, what is the invoice number? Elixir, for example, what is the identifier off this transition? If someone else pays the same amount in less than 300 just we're looking at the amount 303 100. We will not be able to say which transition goes for which president. Also student number 1234 If they want to make multiple payment, how we're going to keep track, we're going to add probably a green jag. Another role here. Well, say 1234 Let's that they make another 500 payments. So 500 here, but it will not be able to keep it violates our rule of primary key. Everything that is Primerica that wanted to for is coming twice here again if the student pays, makes another payment. So this is not a relational database. We need to find out away by hooch. We can answer this question that Okay, how we I can I didn't identify all each transaction and we will let registered in pay multiple times because, ideally, in business, we cannot set out to all our customers that you have to pay everything in one transition. There might be provision for instruments or monthly payments. Who do you need to cook in our databases? So, having this example in front of our eyes like a smooth for electricity, how we can solve this problem and we want to solve this problem. We need foreign key. So we need to understand now what is for NK, which we'll see in next life. If we look carefully into this two tables. Now we see that's we added one more column in this table, which is transition i d. And if you look carefully every transaction, let's listen there one invoice or one transition. Each transaction has unique identification code. So if I want to find out this transition, regardless of its 300 or what it was the payment amount this transition I can uniquely identified, because if you look carefully, here is another 300. But I know that this is one transition and this is another transaction and we can you know , we can clearly distinguish between these two transitions that one is pegged by which is to drink and our guys played by just Julian's. So now we had another problem that I think this table on the left is about payment. It is not about his students, so we cannot have instrument ivy as the primary key business that does not reflect the reality, and that will confuse us another important point that we have is what if I student makes multiple payment and in previous, like we saw that it is not possible to make multiple payments, and that's why we need foreign keep so it will look carefully here. This column is the primary king vision or understanding. What is primary key? And for this table student I d is primary care? No. Here we have an important note. If one column is appearing as primary in one table in any database, the same column cannot appear as primary key elsewhere. So this is student I D. If we refer to desist, really, it cannot be a pro American, this table. So that is another rule. So we need this table's own primary key, which we have created, which is transition ivy now. Primerica unique. They cannot appear twice. The main difference between primary and foreign key is Floren Key can appear more than once primary. He cannot appear more than once they are unique in the table. But if you have something called foreign kid and it can appear more than once and what is foreign, keep This is primarily for this table. If I borrow this table, this column into some other tables. In this case, this is what we have actually borrowed from here. This is called Time Foreign Key in this table. So just I want if we have a primary key which is here in this table student ivy, If it appears in any other table, then it is called for in key. So the table student admissions department has a student i d s primary key and this primary keys appearing in Accounts Department table, which then is foreign key and a student i d in this table must be unique because its primary in this table on the student I d in any other table can appear more than once that since this is foreign key, if we look carefully 1234 Strategy is that very more than once, which means this student made multiple payments. That one payment was $300 the agreement was $400. So this is the foreign key. We see that this primary key from here in this table is appearing more than once. Now we satisfy the question that yes, we have this tape. The left left a table is about payments. So we have a suitable primary keep, which is transition i. D. This is paid by student, so we need to keep your register and paid what for that unique identity fire, which is primary key instrument table. We have important that table in this table where it became foreign key and because it's foreign keys. So one students I can appear more than once in this foreign key column. As you can see, the district number 1234 Now they paid more than once. One payment was 300. Other parent was 400 the Tech name just one hundreds. So this is watch we call a relational victories where here tables and they are related. And they are connected by means of primary key and foreign Key. So just a quick recap in this relational database, which is consisted of two tables. The Accounts Department table has primary key, which is transition i d. The ignitions department has primary care, which is a student, i d. And because the students are making payments. So the student I unique identify for any student is going to be the foreign clean in the payment accounts department table. So now if we ask the question, How many payments? Um, Sara Cooper made. We cannot discharge my name because there may be a lot of circle, but if we mean this circle, But then we know that a student number 2453 So we go to the payment table on research for this trip number, which is two full five to found one, and that's the only one. So we can say that. Okay, this system has made just one payment, which waas $500. So that is how relational Jacobi's is constructed. And the tables in a relational database can academic themselves by means of primary and foreign key. And as we keep moving with this coast, we'll see into more complex examples. Thank you. 6. Meta Data: Hi. In this video, we're going to see what is maitre data. So next major data men's data about data. What does that mean? It means what kind of dig away? Distraught in a column. So let's see an example. This is the table where we have student I. D. Name and address. Now if we look carefully in the student I G column, we have only Jews. It's on and in the name of letters in address. We have letters for this example, but we can also have members like, for example, we can have a post coach, which is no for any address. So now if we ask the question that what kind off data we're keeping in the column Mystery and I d. We understand from this example that numbers or visits in the same way we can ask you the question for other columns. And for this example, we know that this is the latest her name, column, and in address we have latest, but it could be the case and numbers. In other words, they're known as alpha numeric, so when you submitted it is very simple and straightforward. Major determines data about data in other works. When we say we have a table, we also need toe mention what kind of data were going to kill Oh, and why we need to know major later. Because, let's say, for example, if we have a column for bank balance, if we do not mention what kind of future we want to keep, then anyone can enter a BCD on a B C. D cannot be a bank balance. It must be disease, or it must be a number. So if we don't know the major data, if we don't just specify the major data, we will not be able to design robust deter bees. And that is why we need to. It's basically we need to find out what kind of data we're going to keep in each column in a table. And that is what we call meta data. Thank you 7. Entity Relationship Diagram: Hi. In this video we'll talk about the data modelling and for Jacob more than we learned that you named Entity Relationship Diagram, which is also known as E a G or er diagram. So one is an E at Bagram, as we see from its name that it is a diagram, which shows some relationship among entities. So join us and entity relationship Diagram with Chris Nicholl. Understand, work is an entity. Well, I understand what is an entity, and we develop entity relationship diagram by examining and analyzing cases. Study for this course. We initially choose communications study for a small barbershop, as you can see on the slide. But it is a small by. The shop, which sells different guys of by visit, has implies, like manager and other stuff on. Of course, it has its customers. It's a business, and the customers intially used to order by walking in just or in a traditional way, but later on the shop for the business as that of the phone ordering system, which they did by conducting a survey of the customers. So the first things that we need to understand what is an entity because we will develop an entity relationship diagram on this case and study in the following slides. Try to understand what is an entity. We see some highlighted words in this. It's like as by August managers stuff customers order. Now, if we look carefully, we see that these are the item. These are the things about which a business wants to keep information. For example, what kind of different bargains there they're on, Who is the manager, where the staffs and the customers on also about the orders, How many orders have been placed. So an entity means something normally the now that it would replying to you would be able to find in case of studies about which a business wants to keep information about. So in this case, we see that burgers manager stuffs customers order. These are possible entities on which the business wants to keep information, So we need to generalize the terms off entities. So let's see how we can determine the entities from this case is starting. So when we said we want to keep information about bogus, we are actually keeping information about boobs and manager in this stuff, we can say that their employees customers? Yes, their customer and orders order. So these are the entities about on which the information wants to keep information or interested in having in commission about. So finally, we can say that these four are entities for this cases. Study food, customers, employees and order. If you look carefully, you will see that I have not used any plural term. The name of the entities any any and quickly should be named by using single action. For example, employ. You know, it implies cutting a customer, not customers, and so on. So we found our entities for the case of study, which for entities, as you can see food, customers, employees and order no, we need to find out how they are related among themselves. Fruit employing order. Customer The slight shows how they are related food and order. Let's say, for example, here order is placed on food on a customer places, order and order is taken by employees. Now I am reading in this great customer places order. But what if someone routes that there were around order places customer who do not make any sense. So to avoid ambiguity like this, sometimes we can use arrows to show the direction to read the entity relationship diagram. So now if he's Uneme because it is not creating any confusion that customer places, order and order is taken by employees. Onda order is placed on food. So this is our initial entity relationship diagram where we have challenged the entities and how they are related. However, there's more to this story that we're going to see now, which is called Khar Geniality. So what is card in ality? Cardinal determines how the entities are connected among themselves. Let's have a look. The 1st 1 that we're going to talk is 1 to 1. Here you see an example of two entities. Let's say this is a and B 1 to 1 between Thank you a and entity be It would mean that one example of this entity can be associating on Lee with one example off. The other entity lets the entity is people and entities be his passport. In that case, it would be a 1 to 1 relationship because one people one person can have only one passport and one passport can be old can be given to only one person. On the other hand, there might be one to many relationships where, as you can see that this is denoted by a Christian nutrition of the many and where we say Entity A might be associated with many instances off Entity B. But in ticket be can be associated with only one instance off entity, for example, if let's take another example that lets it is a student and be his book, so one is Student can take many books. Let's say like one book can be taken at one time only by one person of one student, because one book cannot be top to town to give it to us. Different people. That's not how way have two books in real life. So we can say that there is a one to many relationship. On the other hand, we have many to many relationship, which is also denoted by M and relationship. So here you see that the many, many Ainge is a both thing. So example off this entity, a good basis stated with many is genesis off being and the other way around. For example, if we say that, let's say that one book it has five companies are 10 covers I mean multiple copies. So in that case, we can say that if there are students and there are books and one book has multiple copies , and we can say that one student can take different books, many books, on the other hand, one book example off one book can go to many students. In that case, it will be a manning too many relationship at the beginning. Sometimes the management relationship appears to be confusing to the learners. However, for the time being, we will not worry much if it appears a bit confusing a zoo. Later on, we'll see examples that will make it clear to us that what it is when we'll see real example with data. So at this point, just to give you an outfront, nor that we cannot have manicure many relationship. I want to discuss much on this later on this at this moment, because we're going to have for discussion on this later with illustrations and later. But why, in real life when you designed it obviously cannot have many to many relationship, However, we have seen 1 to 1 relationship, one to many relationship on manager. Many relationship this relationship degree of relationship in database known as a cartoon ality. So now Knicks have our developed entity relationship. Diagram with card in ality. If you have a pleasant looking, we'll see customer places order one customer places. Many order another hand. One order can go toe only one customer. Once again, customer places. Many order, but Order goes to only one customer. Let's look into that arrest of this one employee can may take more than one order, but one order is taken by only one employee. This can take more than one of this, but one of these can be taken by only one of this. That's how we interpret the card analogy, the last one. Fruit and odor. We see that one order can have many items of foods. On the other hand, one item of food one specific item of food can go into many orders. That is why this is a many to many relationship. Once again, one of these can have many off them, and one of these could go to many of them. It is not about one specific sample, it's about the type. So let's say if we say that cheeseburger, for example, different or just can have cheeseburger, so accept it's a money, and that is why it is a man in too many relationship. So now this is our finalized initial here diagram where we have our car geniality and we had some phrases that was describing relationship Initially, it is important to have the but, you know, when we move forward with our, uh, data modelling, we may not essentially need those description about the relationship. So if we look here carefully that we'll see some changes here, we now place circles which denotes optionality. What does often religion mean? Means there may be there may not be so optionally t here in the manager, many relationship if we look that if there how would we interpret that? Ah, food made not go to any order at all. So this food's going to order is optional. But if there is an order, it must have at least one fruit or more than one food. So if there is, there is a food item. Just knock me that it has to all the protective should go in order. There might be one expressive for that no one ever ordered, so it may never be in any order. Or on the other hand, if there is an order, there must be one kind of food. Since you were talking about a food shop. In other words, it's a barbershop. And if you look here, there is an order taken off course. It must be taken by one employee. But there is an employee. Doesn't mean that the camp has to taken an order in a business. There might be some employees who don't take order actual, but if an order is taken, then it must be taken by one of them place. That's how we denote optional. Either way, we mean that it may be associated with the other entity. Or it may not be, is that we move forwards. This is our initial entity relationship diagram. And if we can remember that, we said we cannot have managed to many relationship. We need to resolve that. Thank you. 8. Resolving Many to Many Relationship: hi in this review. Well, see how we can resolve Manager many relationship from an interview relationship diagram. This is the intricate relationship diagram that we have developed where we have many to many relationship here between fruit and order entities. And as we discussed, this is the initially I diagram on, You know, finally, your diagram. We cannot have manager many relationships, so why we cannot have managed to many relationship and how we resolve the manager. Many relationships from intricate relationship program is what we're going to see in our coming slides. So now let's a result of this magnitude many relationship to resolve this, manage many relationship. We need another entity, an extra entity that will introduce your into your relationship. Bagram. We'll know. Normally it's named as by conjugating the two entities, like, in this case, food and order. We are naming it as food order. However, it's look a rule. It can be named using any woods the water to we introduce on extra entity here, and if he was seeing this entity is connected to both the entities and if you look very carefully, we are not going to have this relationship anymore because we have resolved this for who do we have the extra entity named Food Order. And this is a real time that you will have one to these two ends, the originating interviewees and a short formula is that you sew up the type of relationship this site goes to the other side and this one comes to the other one, and that is no. We resolved many to many relationship. So find out, as you said, that we cannot have this one animal because we introduced an extra entity and reconnected using the the treat that we learned. This is what it should look like in your entity relationship diagram. And now we can see there is no more man if you many relationship in between huge and order entities. So this is our completed final entity relationship diagram with no Manitou. Many relationship as we have results earlier that this is extensively we introduced in our interview relationship diagram for the Sheikh of Duties Design. Now we're going to see why we actually cannot have managed many relationship. So let's say we have food and orders, which means that one food item can go to many or this about the hands One or they can have many food items. Now let's look here. Listen, this is order. A one is going to have to food items. Food item number one. Thank you. Injected his design in one cell. We cannot have multiple dictate should have at Imitator one item off later. So this is not allowed in the relational. Jamie's design Israeli. We cannot have like this. So what you can do? We can have the two tables, food and order and we can say which food is going toe which order now we obviously understand that this is the primary key or unique identify for order table And this is primary key for fruit table by definition, in any table, primary key can not to be duplicated. So let's of what is the case here? We want to say that Order of 001 is going to have cheeseburger and chicken burger, food number one and two. On the other hand, there was another order. Oh, for which is going to have cheeseburger Israel, which is again the same as food number one. Now the problem here is that we cannot have like this because this is the unique identify or primary care for food table, and we cannot have any duplicated value. So this is a problem here. On the other hand, if we look here, we see the same problem that we have food number one and number two in order number one and we're repeating. And this is not allowed in our relational databases. Unique identified are Primerica cannot be duplicated, which means based on this example, we cannot have managed to many relationship in our relational Jetta bees. The solution, as a way we have seen earlier, is to introduce an extra entity which becomes an extra table in between these two tables Now which we look into the food order here We only list the total food items that we have and here we lose to the orders. Now they they are connected. This is the card unity that we have shown. Now, if we look carefully, we can see that order 001 has food won number one and number two how this is possible because in this extra table, the primary key from this table and primarily from this table, they all together make the primary key for these tables. In other words, when results many to many relationship the extra table. The new table that we introduced, it has a complexity. Composing came It's the Primary T, which is made up of more than one attributes. In this case. These columns are also known as actually success we have seen earlier. So food I D and order I d. The altogether make one primary T for food or the table. So if we look into the combination, this combination appears only once in this table on this combination appears only one. So even though we have multiple instances off 02 or 01 But when you combine it with the other one, we see this is unique and that is how we resolve management relationship. And as we have seen in the previous, like that prayer in reality when it is and database we if we have manage many relationship , we will not be able to is toe data because any cell in a table this is one cell can allow only one value. Multiple values are not allowed in in the cells off any any table in a relational databases that we must remove manager many relationships from our data modelling so that we can make a design and implement the database. Thank you 9. Design Database from ERD: Hi. In this video, we're going to see how we can design database from entity relationship diagram. This is the entity relationship diagram that we have developed earlier. If we can remember, we had four entities who'd older customer and employing on because we had a manicure. Many relationship between Order and food employ entity, which eliminated by introducing an extra entity, which is food order. So the first the state in designing database from interrogation program is to find out that primary keys for the energies. So if we look carefully will see that customer I D is the primary key or unique, identifying for customer entity in the same way and play idea for employees Order Number is for order, entity and food number is for food entity. One thing is notable here is that when we have the extra energy to eliminate manage many relationship, this extra entity will take both primary case from the entities for which we eliminated the management in relationship on and the both primary keys from the entities Altogether. They become primary key for this extra entity, which is a composite key because it is made up of more than one attribute in this case, um, food number is the primary key for food. Entity on order number is primary key for order entity, but for food order, entity, food number and order number, these two altogether makes the primary key for this entity. So our first escape after developing entity relationship diagram is, as you can see on the slide, is that you'll find out that primary key or unique identify their on. And then we find other attributes for each entity. So here we have just an example that, let's say, for food, we also have food number and foot type for customer. We have customer named Jacob, but on a customer's address in the same way employees, they have their idea and on top of that other attributes and play name, date of birth and phone. So now we have our entity relationship diagram Final One, from which we first round of the primary keys for each entity, your unique identifier. And then we found other attributes. So when we start designing database, the entities become tables and attributes become columns. So if we look here at this example now we see of the all the entities with their attributes . So later on, when we develop database, each entity becomes table. In this case, we have 12345 tables, namely food, hood, order, order, customer and employee. And we can see their attributes suggest, as an example way. If we take the foot entity into consideration when we develop database, this will be one table which will have 123 columns on the columns will be food number, food, name and food type. So this is our initial. Jacob is designed, but we have a bitch more left toe. Come up with our finalized design, which we're going to see now if you look very carefully in this design, which differs a bit from the previous design is that the order table has more attributes now. So what we have done here, if we look carefully, this employee i d. We borrowed this employee idea and we included here in the same way in the customer idea we included here, by which we I understand that this unique identify where is going to be a reference here. So this is the foreign key. This is the primary key on in this order. Table order number is the primary key on customer idea is a foreign key, which is primary key for customer table on employee ideas affording king this table, which is primary key in employee table. Now we need to carefully look into the rest off the design. This part where we had manage amended relationship, we and we had an extra attribute entity, Food order. Here. We have to be a bit careful to understand what is the primary key. As we discussed idea really considered this table food number and order number this all together they make primary. Just one of them will not be considered as Primerica for this table. And when we consider food order table, this order number is part of the primary key here, which is when you consider this table is foreign key for this table. So food number and foot order number is a primary care altogether for food or the table and order number for this table works as a foreign key in the same way the same rule applies for food number. So this is how we design a database from our finalized entity relationship diagram. Thank you 10. Normalization and Dependency: hi in this video will learn normalization and dependency a very important part for Gucci jet of his design. So let's start with the understanding what his normalization. This is a process off element. Aging three Animal is namely object Animal E insertion, Animal e and adulation Animal. So what is an object animal? E It is when you try to object a table and leads to a logical inconsistency. For example, let's say if we want to object this students address in one record or in one dro on the other room and not to be objected, and we can see that there is a mismatch in address which is off course on inconsistent data for our table. So this is Abdic Animal e anche in session Animal is where we cannot inside a record. For example, if we want to insert this new student here, we cannot inside because they have just got admission. But they have not and rode into any courses. But in real life, a student may get admission without having them. Android Andronico sis. But in this case, we are not being able to do so, which is an in session animal e on in delish in animal. If we look here, this is student. If they want to withdraw from the scores Matt, we regulating the whole record on this is of course is not an ideal situation. A student soldier from a course doesn't mean that we the student, is a drink from the institution. So this is called Danish in animal e a better adjective is design eliminates this kind off animal ease And by normalization, we eliminated this kind off possibilities. So to understand, normalisation way, we need to understand dependency. First, there are three kinds of dependency in database which is functional, partial and translated. Different is, as you can see on the slide. First, let's understand what is a functional dependency If we carefully look into this table, we see there are two instruments. It same name, but they are different persons. Um, we understand that they're different persons by looking at their student I d that they are not certain not same students to different students with different the same name. So we can say that the name and date of birth off any student they are depending on a student I d. In other words, the name and a little butter. If we had any other tribute in this table will be identified by looking at the student I d . So name and data, but functionally depending on history in idea this is functional dependency. Now let us look into transitive dependency. If we look into this table carefully, we see that a student gets admission into effect, which is identified by the faculty i. G. We find the name of the faculty by looking into the faculty idea. In this case, if you look carefully, the faculty name depends on faculty i d. But the faculty i d for any student depends on the restaurant idea. So this is students by looking into their i d. We find out who trackers there, which ancient helps us to find her the name of the faculty So faculty name is not directly depending on a student i d. But on faculty idea, this is what we call transitive dependency. Faculty name is transitive Lee, depending on a student I d. So now we look into partial dependency. The difference between transitive and partial dependence is that in this case and attributes is depending on on another attribute which is part of the keys which we look here. Book name is depending on book I G. Which book I student has taken we can find it true book. I guess a book name is not depending on a student. I D book name is depending on the book idea If we know the book i g we know the name of the book but if we want to find which ago a student took we go to a book i d. So book name here is depending on part of the key because book ivy is it's part off. Like this is a composite key where we have two attributes book, idea, industry and idea as a primary key for this table, as we have underlying these two attributes. So partial dependency means when an attribute is depending on part off the key off a table . So in this case, book name is partially depending on depending on this trend, I d. So these other dependencies now we look into normalization and steps off normalization. So here we will look into four normal forms a normalised form, a first number form a second normal form and third normal form just in our front note for you adopted there are more normal falls after 1/3 normal form. But since this is a beginner coast, this will have enough for us to know how we can normalize after take note and if you know how to do Ah, normalization after tag number from the rest will not be a big issue for Astra Land. So lesson start what is an anomalous form? It will look carefully if we see this record. The highlighted one will see their repeating groups and as we have learned earlier, that in any database in any sale of a table, we cannot have more than one. That dick item should be actually. But here we're having to book names. So this is a normalised room from from here. We need to take it to first novel from or one in f. So in first number for what you do those repeating groups. We displayed them into a different tables from a student table. We have taken those attributes and we named it as the book table. So now we have two tables. This is first normal form. So once your first normal form, we're ready to go into a second normal form. But before we go to second normal from let's have a quick looking toe diagram which we call a dependency diagram. So this is dependency diagram on job. Based on our understanding from earlier discussion, we see that we have two key attributes CIA Ultimate. They're making the primary key for this table faculty idea and student i d faculty name depends on faculty. I d. And here also have another non key attributes. It is not fired. A big enrollment i g depends on the strength i d. But enrollment type depends on enrollment. So here we see the track algae name is partially depending on this joint. I g faculty name has partial dependence on the street idea. Where's enrollment type has transitive dependency on the street I d on These are the two that will eliminate as part of our other normalization. So we had our first normal form, which where we just leave it to the table into through tables, restaurant and book. Now we will converts Are these database into a second normal form Now the rule for second normal form is that it should be in first normal from what you already have here on. Then we need to remove partial dependency. And now, if we want to remove partial dependency women that faculty again faculty name that we are going to split each take off from this table and we'll make it another table. Like so if we look here that we have taken actually buttes that had partial depend dependency and we made a new table. So now we have three tables and now we can say that we in these database the these databases in second normal form. So now we have second normal form. It is time for us to convert this database in return Normal from Let us check if we're we need to convert it into third normal form. So third normal from say, is that a database that should be in second normal form the tables and rigidities, Aunt, it should not have any transitive dependency. And we know that the one we see in front of our eyes is already in second normal form. That's what we found after achieving second normal form or two. NF on. Now if we look carefully, we still have translated dependence here and Roman type is translated Lee, depending on the strength I d. So what? We'll do this to our enrollments related attributes. We will escalated into another table and then with us will remove positive dependency. So here, uh, you see that we have removed those attributes and now we have four tables. Now we have removed all the dependencies and we This is our normalized form. Now we have our normalised database, but we have only one key attributes or for the other tables we need to find Chiaki Boots. In this simple example, we have enrollment idea, faculty idea and book idea as primary king, If we didn't have, then we had to create a new attribute that would work as primary care. So now we have our all the tables no dependencies on and all of the tables have their We identify our primary. Now, this is normalised database, but not a relational activities because these tables are not connected by Minister of primary for and we have only the tables isolated and they have the primary keys. So what we need to do this primary case are identified Now we're going to have depending on the relationship. How there are these tables are related. We need toe Take primary kids from here from these tables to make them foreign key in other tables. In this case, we see that faculty idea because it's about a student faculty. I d from here becomes a foreign key student table in the same way from book I d. We have taken just weren't able to be foreign key and from enrollments table the enrollment idea, we have taken it into a student table to making a foreign Keith. Now, this is what we call a relational database, which we have no normalized and the tables are related and there are no dependency that we eliminate take by using normalization. So normalization is a simple a stack. So this is one example that you see, but in real life will work with different scenarios and the relationship may not be as straightforward as this askew Get practicing, it will get more exploiters and it is a matter of practice. So now we have our normalized relational database Here you can do further fine tuning as you have seen in previous. It was like that we had on the string name But can we actually split it into off the card accidents? Probably we can into first name and last name. So if we have an attribute, we will try to explain it into as many parts as possible for better database design for better query design that, well, we'll learn as they keep advancing and really start developing gigabits. However, that will be discussed in another course, probably indigenous development. For the time being, we can actually this leak that tape the name street name into Christie. Man. Last thing. That's what we have done here. I can give you another example here that if we have address as an attribute, it is probably better to explain it further into a street number. Street name, postcode, safety country. It helps us to find out. I think our mission in a more structured and in a better with. So now we have fine tuned our database and we have our oh final normalized relational database. Now let's look into that. The victim is with some data field in. So here we have some sample gator feel in. If you look carefully, the students one student is taking one book. Now, if we ask the question. What if one student wants to borrow more than one book from library, which is a very normal case? So we see that even after having our final edit of his design, we probably need to look into further fine tuning. So here we see that, actually, this is a man in humanity relationship that probably we did not solve earlier. It happens. So I kept it in here like this, deliberately so that if you have any mistake later on, you can. I mean, you're trying. So now we actually cannot put more than one book here. So something is missing here. What is missing? One. A student can borrow more than one book and one book obviously can have multiple copies and they can go toe different distractions. So we need to eliminate this by using your table, as we have seen, you know, our data modelling that we introduced an extra entities, entities become table. So in this case, what we're going to do, we are going to have on extra table, which probably we're going to name as book alone. So now if you look carefully based on our previous understanding that in this book alone is to eliminate management relationship between these two tables. So in this book alone, the primary duty should be the primary key of this table. Plus the primary key off this table book table, the book registrant idea. Altogether, they make primary key for this table. And if we want, you can have Father excuse. As you can see here, we have loaned date. So now if you examine this very carefully, we will see that one student can take more than one of his dream number. S 02 can't is taking more boring More than one book here on this is this course monster. All previous understanding of data modelling Where, off eliminating management relationship. And now we can say that this database is flying teamwork. And since we have first name and last name is late, not just a name now probably can ask the database the question that can you please show me all this Trojans whose first name starts with a or last name starts with the Lexus. See constantly. I have always sees here, but of course we'll have different last names. So that is how we normalized our database. we find you in our database and we almost at the end of our native is designed. We do get a modeling, then we do normalization. For that, we find dependency on you, find relationship and then way further fine tune and you're finding We might find that we just saw that maybe there might be fewer emissions in our database design, which you will need to reveal. And so So that is what we results further problems, and we gradually find consistent integrated database design. So when you have addictive is designed like this, then you can use this gentleman is designed to develop and implement on our database, using any tool that you can find in the market to develop these tables and define the keys , defined relationships and so on. However, that is outside of the scope off these, of course. So we will not discuss further about how we can develop a gentle ease from did. It is design, so we have resolve to the management relationship on. That's how we inch our database design way find. A new integrated database is designed as the final outcome off our data modelling and victories design process. Thank you 11. Database Design Steps: Hi. In this video we'll see the strips off database design. We have learned all the steps. Now we will summarise the steps that we need for our designing adjective ease. So the first thing that we do is we analyze the business case or the client's business tour in a strange what is a business? One of the rules lunge. Who are the major players in that business That helps us to understand the entities we a gym in the entities and their relationships based on our understanding on our client's business. So once we understand the entities and the relationships, it helps us to develop the initial entity relationship diagram, which, as you have seen, make content manager many relationships. And we know that we cannot have management relationships that we resolved to have the final entity relationship diagram. Once you have the final entity relationship diagram, we develop the detained entity relationship diagram where we mentioned that unique Identify our case for each entity and we also find to the attributes and you have seen that the entity relationship in an entity relationship diagram. The entities become tables and keys becomes primary keys on dso The attributes becomes other columns in the table. So once we have the details here diagram, we also do normalization of for the tables. So once we're done with the normalization, we have our final A database design which we we may further fine tune to have the final solution as our getting his design. So these other steps in a database design which initial involves data modelling to develop an entity relationship diagram on then we normalized way. Find soon to have an air of freedom is design. Thank you.