09:00:09 Hello, today the goal is to introduce in a hopefully intuitive manner The heart of everything to do with database.
09:00:23 I enabled the captions because I think there's someone in the class who's gonna want captions.
09:00:29 I hope that doesn't distract those of you who Don't need them.
09:00:39 Now. In order to. Come up with the idea. I'm going to.
09:00:50 Be drawing some diagrams.
09:00:53 I find paint fairly easy. Who pull up. But.
09:01:03 There are a lot of software. Programs that are better. And
09:01:16 I'm going to use Word.
09:01:21 Who illustrate the idea. Now.
09:01:26 In a database.
09:01:31 We're database. Is color about collecting.
09:01:46 And organizing information that should be no. Big surprise. I'm going to introduce some terms. And then I'm going to.
09:02:00 Do an example. About registration, which Hopefully, will be familiar to all of you.
09:02:11 Entity.
09:02:22 Is a thing about which you gather information. Now, not too long from now.
09:02:32 I'll be able to catch a couple of you. But, I'm not trying to catch you.
09:02:38 It's not a game I got you. But an entity. Is the thing about which you're collecting information.
09:02:45 Now if you're a car dealership. The D.
09:02:51 A card dealership, reflecting information about the cars. If you're a university, you're not collecting and information about the university you're collecting and information about students, etc.
09:03:07 And.
09:03:11 It's a relationship.
09:03:15 Is
09:03:21 A connection between.
09:03:26 And we'll be introducing the idea of an entity relationship diagram today.
09:03:34 Start off with What you're collecting information about and what other things it relates to. Now.
09:03:56 When we're
09:04:01 Collected information. My example. Is going to be registration.
09:04:10 Now, if you're doing registration, What's the first thing that occurs to you? That we might collect information about.
09:04:20 Well, there's several correct answers.
09:04:25 But a student.
09:04:36 Is one of the possible answers.
09:04:42 Oops.
09:04:52 Is one of the things.
09:04:55 We might collect information about. Now the custom in the entity relationship diagram tradition. Is to use a rectang.
09:05:07 To indicate an entity. Now I'm not going to put it in the diagram.
09:05:17 Okay.
09:05:23 But there are things. That we collect for information about the student.
09:05:35 I hope no one's surprised.
09:05:39 We want to know the name of our students.
09:05:48 Name, address, phone number, etc.
09:05:56 Now, for each of these, you should only have one address. It's a little more complicated if you try to have.
09:06:05 More than one address or more than one phone number. Big college. Hey, to track campus address, permanent home address.
09:06:18 That's 2 different addresses. Campus phone number, cell phone, permanent home phone number might be 3 different phone numbers.
09:06:29 Okay, but each piece of information
09:06:33 You're unique. That is, you don't have more than one local phone number, more than one.
09:06:40 Permanent home phone number.
09:06:50 A class they registered for is not about the student. Who their advisors is not about the student.
09:07:04 Hopefully you will develop an intuition. About that before law.
09:07:11 Let me. Change the paragraph in.
09:07:32 Okay. Now.
09:07:38 In the idea of. The registration.
09:07:48 There's
09:07:51 Else that we might. Collect information about.
09:08:17 Oh.
09:08:26 A class, but what exactly is a class?
09:08:30 Is it the meeting?
09:08:34 We have. 3 times a week or twice a week.
09:08:42 In the eyes of the registration system. A class.
09:08:49 Is what's described in the catalog.
09:09:04 And.
09:09:10 Oops.
09:09:16 The discipline. Like CIS. Is one of the things.
09:09:30 The course number.
09:09:33 Is another of these things?
09:09:41 And.
09:09:46 The title. There's a third of these things. Now I'm going to.
09:09:54 Okay. Section is a different concept.
09:10:07 A section is. An offering of the class like you might have a fall section or spring section or section one and section 2.
09:10:18 So the information about the class is the information that's true of all sections.
09:10:26 Which does not include meeting room meeting times. Anything like that. Now, in order to be able to effectively use an address.
09:10:40 You effectively use information. You've got to be able to. Quickly identify things.
09:10:56 And for a student.
09:11:00 A phone number is not always a good idea for identify them. One thing because phone numbers change for another thing because some students have multiple phone numbers.
09:11:11 An address, not appropriate addresses change all the time. But in fact, even name is not a appropriate for identifying students.
09:11:22 This may shock you, but students change names. I've had students get. Marriage students get divorced students change their aim for.
09:11:35 Other reasons.
09:11:39 When you're trying to pick an identifier. For an entity you should pick something that doesn't change.
09:11:50 Which is why
09:11:53 Lot of places use an ID number. Or an ID code, it doesn't have to be numeric.
09:12:02 Hironically enough, one of the big Issues. In the computer world. Is the social security number.
09:12:13 Why? Because the Social Security number is a number that that pretty much guaranteed to be unique. For every person who has one.
09:12:28 And it doesn't normally change.
09:12:32 In fact, in a real sense, if your social security number changes.
09:12:37 It's almost like an identity change.
09:12:44 Now, so for a student.
09:12:53 We'll say the ID number.
09:12:58 Is the identifier. And I will indicate that by underlined.
09:13:06 Glass is a little harder.
09:13:12 Obviously, if you say discipline, that doesn't identify a specific class. And if you say course number, that doesn't identify a specific class because other disciplines might use the number.
09:13:25 And there's really no guarantee. The title is unique either.
09:13:35 And some places will issue an ID number for a class and that is perfectly fine.
09:13:42 But one thing that You know, uniquely. Identifies A course.
09:13:55 Is the combination. Discipline code and the course number.
09:14:03 There's Only going to be one. IS.
09:14:09 30 10
09:14:11 There are other CIA courses, there might be 130 10 courses.
09:14:19 But by the nature of it. Within a discipline. Each course number is unique. So.
09:14:29 Disable number together. Identify the class.
09:14:35 Introduce a new concept.
09:14:41 The key is the attribute. Or attributes.
09:14:58 Within an entity. In the case of student in my example, the ID number uniquely identifies a student.
09:15:07 You have a student idea. I have a faculty ID.
09:15:10 In the case of a class. The discipline of course number together.
09:15:17 Unique to identify the class. And So, other words. That you will hear sometimes.
09:15:46 Hey, you will sometimes hear database people talk about tuples. Which intuitively is the elements in a row information about a single entity.
09:15:56 The ID number name, address and phone number of a student. Is a tuple. A student.
09:16:03 The discipline code the course number in the title is a tuple of a class. And so on.
09:16:12 So that gives us. 2 entities to play with. I mean that word entity, it sounds so weird.
09:16:20 Sounds like a Star Trek episode. Captain, the energy is about to attack the ship. No, it's not not that weird kind of entity.
09:16:29 It's just a thing about which you collect information Now.
09:16:38 But.
09:16:43 This may shock you. Students do not register. For Clathes.
09:16:53 Oh, what a surprise.
09:16:58 You are not registered for CIS 30 10.
09:17:04 You are registered for.
09:17:08 One of actually. For sections of that is offered in Spring's semester.
09:17:23 Hey. It's weird. Partly it's because we're combining across campuses.
09:17:36 So each campus. Has 2 sections. But.
09:17:49 What is a section?
09:18:03 Should not seem getting so much, obviously, but.
09:18:12 A section.
09:18:31 Well, a section certainly has a relationship to class and relationship that is the technical word.
09:18:41 But it's. Not just a class.
09:19:03 But it is.
09:19:08 About the discipline in the course number
09:19:13 But also.
09:19:21 About the semester. That is which term it is. You know, what academic year.
09:19:34 And there's other information that is important about the section.
09:19:44 The time or time it meets.
09:19:48 The place or place it meets.
09:19:55 Whether or not to pass fail.
09:19:59 There's other possible information.
09:20:03 Now, what is it?
09:20:08 That determines
09:20:16 A unique section. And remember. Even for her the small group of us. There's 4 sections of courses and at least 2 sections of labs.
09:20:30 For this course.
09:20:37 What is it that uniquely defines a section?
09:20:42 Well, even if I said course number and discipline and semester that still wouldn't uniquely identify a section.
09:20:53 So. One possibility.
09:21:01 Not the only possibility.
09:21:07 Say. The discipline in the course number and what semester it is and a section code. That uniquely determines the section.
09:21:25 And once that's uniquely determined. That determines time in place.
09:21:45 Determines also known as
09:21:52 Functional determinacy which will. Hear about later.
09:22:04 Given the key
09:22:16 Well, I think I had it right first. But anyway.
09:22:35 If you're given a certain key. Then an attribute is fixed. Then it would be correct to say that the key determines the attribute.
09:23:05 And it is. The case in the database world. The key always determines the non key attributes.
09:23:13 That is If you know which section you have. You therefore know what time it is by looking in the table.
09:23:20 If you know what section you have, you therefore know what place it is by looking in the table. For the class if you know the discipline in the course number you therefore know what title it is.
09:23:32 If you know students ID number you therefore know their name, address, phone number, etc.
09:23:41 Now. Let's go back to my cute little diagram.
09:23:50 Hey, section.
09:23:55 With the class.
09:24:10 And I'll just say.
09:24:14 Is a section is a section of Hey class. Now the reason is that diamond shape
09:24:35 An entity.
09:24:43 Is normally drawn with a rectangle.
09:24:51 And a relationship is normally done done with a diamond shape. That's just so that you can tell.
09:24:58 Now, there's another important thing. Chips.
09:25:13 A relationship table.
09:25:39 Has information about the entities that are a part of it. So, if I say, is is my relationship, a section is a course.
09:25:49 That tells me
09:25:54 That
09:26:03 The is relationship.
09:26:07 Information about the class and information about the section.
09:26:13 Well.
09:26:18 Since the information about the class.
09:26:23 Included in the information about the section.
09:26:35 The is table, ironically enough.
09:26:41 Has exactly the same key as the section table.
09:27:01 Now.
09:27:07 Think we can all agree.
09:27:20 One class can have many sections.
09:27:25 Including the class you're in ironically not.
09:27:29 But it is not true. That one. Cannot.
09:27:40 One, and cannot have. Many classes.
09:27:50 That is. 30 10
09:27:56 Can't be a class other than
09:27:59 The database.
09:28:05 This is our section. Whichever section we have, it can't be a different class.
09:28:12 Now, if we have a relationship between students. You know, like, look, at later, you know, one student.
09:28:25 Can have. Many sections.
09:28:36 And One section.
09:28:42 Can have many students.
09:28:46 Both of those are fairly intuitive.
09:28:51 Okay.
09:28:56 If you have a one to many relationship.
09:29:02 Then you can put. The ID. The key. Of the one.
09:29:13 In the table. Of the many.
09:29:30 So you might not need. Hey, relationship table. If you did have a relationship table.
09:29:41 It would have. Those attributes.
09:29:46 Number to say. Not needed. As a Separate table.
09:30:04 Okay, now.
09:30:14 Yeah, and you most diagrams you end up drawing a line, you know, and not having the diamond.
09:30:20 Take the whole.
09:30:25 Sorry.
09:30:33 Oh.
09:30:49 Hmm.
09:31:04 Okay, that's supposed to say enroll.
09:31:09 Okay. Students enroll in a section. Remember. Students don't enroll in a class because the classes This hypothetical contract.
09:31:21 Which includes all sections.
09:31:37 Yeah. In many too many relationship one student could have many sections one section can then have many students so
09:31:50 The relationship.
09:31:53 Has to have information. About
09:31:59 The entities that are a part of it.
09:32:09 A student, the identifying key is the game.
09:32:15 And the section. Yeah, identifying key is the.
09:32:25 Discipline course number.
09:32:30 Section code and semester identification.
09:32:40 I'm quiet. No. So a student in rolls in a section. A section is an offering of a class.
09:32:52 Now you notice there's no direct connection between student and class. So as we will learn later. If you want to find out.
09:33:01 The title of a class, you actually have to Start with the student is taking you have to start with the student
09:33:11 Look at the enroll table to see what sections that are offered in. Look at the section table to do term.
09:33:20 What class it is, look at the class table to get the title. Pull out the title. It's not as bad as it sounds when they put it that way.
09:33:29 Okay.
09:33:34 No.
09:33:38 There's another thing about which we have information.
09:33:46 And you're looking at one of them right now.
09:33:50 The professor or the teacher.
09:33:56 So.
09:34:16 Oops. Oh.
09:34:22 That's okay, that'll work. Now. An instructor.
09:34:37 Also has information and like a student.
09:34:43 Do I have an ID number for right now I'll just call it FID.
09:34:53 Now a faculty member will have. Information. Like name, address and phone number and stuff like that.
09:35:04 Okay.
09:35:10 But the
09:35:14 F the IM number is what determines. Who I am. I do not exist. In the system.
09:35:24 Without a faculty ID number.
09:35:27 Do it. Truthfully, you have to be
09:35:33 Battle D ID. Your email alias, you know, the 3 initials plus the The numbers.
09:35:43 Is not your actual student ID. But it is unique so it could be
09:36:08 The key and later on.
09:36:13 There's an idea called the foreign key. A foreign key doesn't come from another country.
09:36:28 The foreign key is a key for a different table.
09:36:33 And in fact, the number, which is the need for the student table, when it's in the enroll table.
09:36:46 That's called a foreign key. The discipline course number code and semester when it's in the enroll table is the foreign key.
09:36:57 For the section. Hopefully this will.
09:37:04 Make more sense later. So.
09:37:12 Now, right now
09:37:16 I don't have any relationship between instructor and anything else.
09:37:26 One of the important questions.
09:37:31 Do I want you to reflect on for a second? Should instructor be related to class? Or should instructor be related to section?
09:37:45 And that's at this point that might not be obvious to you.
09:37:52 But if you ask yourself the question
09:37:56 Can you have? More than one instructor teaching.
09:38:02 Hey, given class, yes, of course you can. Freshman English. All kinds of people get to teach that.
09:38:09 Can you have more than one instructor teaching a section? Well.
09:38:17 As one person who's the senior instructor. But by the way, I will say.
09:38:25 You remember I talked about
09:38:28 One to many and many to many.
09:38:37 There's also my idea called few to many.
09:38:58 If you there are some circumstances where you say, okay, well, we'll let 2 people be.
09:39:04 Teachers.
09:39:08 Sometimes that will work out.
09:39:12 But it's the safest thing, of course.
09:39:23 Unless you're really pretty sure about it.
09:39:29 The safest thing is to
09:39:32 Start out thinking every relationship is probably a many too many relationship.
09:39:38 Now. So I'm going to arbitrarily say. An instructor has a relationship with a section.
09:39:58 And I'm just gonna.
09:40:03 Use T. For for teaching. Now, just in when most people draw these diagrams.
09:40:19 Oops.
09:40:22 You draw lines, they're more expanded than this.
09:40:29 But an instructor. Teachers. A section. Now, so I'll come up here.
09:40:40 And I'll say. T Would you short for teaching. It's a relationship table.
09:40:51 And so.
09:40:55 But where did I put it? Yeah, has the information about the entities that are a part of it.
09:41:01 So. It's a relationship between instructor. And section.
09:41:16 So you have to have the faculty ID number. And you have to have whatever the information. Oops.
09:41:25 That should have been underlined too.
09:41:36 Oh, as the.
09:41:39 Yes. Okay, so.
09:41:44 The information about the section.
09:41:58 So now in my cute little diagram. Every student is enrolled in sections. Sections are taught by instructor.
09:42:10 A section is part of a class. And there's more things we could add to it. For example, an instructor.
09:42:20 Is within a discipline. And the classes within a discipline.
09:42:27 Huh. But I'm trying to give you, you know, just the. General idea here on our little warm up example.
09:42:42 So. In our little warm up example.
09:42:57 We have certain entities. A student, a class. A section. Oh, and an instructor.
09:43:19 And then we have relationships.
09:43:26 A section is. An offering of a course. A student is enrolled in a section. An instructor teaches a section.
09:43:47 That here is our vocabulary terms.
09:43:52 Key and alternate key of. Key is the important key for today.
09:43:59 Okay, and then there's the relationships.
09:44:09 Yeah, will, again, if you want to find out. What? Students I have. We've got to start with Figure out what sections I'm teaching from the sections I'm teaching figure out what students are enrolled from the students that are enrolled get their names.
09:44:31 It's a multi-step process.
09:44:42 I want to give you some
09:44:46 Couple of other concepts.
09:45:16 Now in the
09:45:20 Database world. You will have a lot of people say each piece of So they will, you know.
09:45:28 Stop. Full stop. Well, okay, a student ID appears more than once
09:45:36 Except for the keys, the identifiers.
09:45:46 And.
09:46:12 Normally.
09:46:18 The key is the identifiers do not change or only used to identify. Your student ID number is not supposed to change my faculty ID.
09:46:27 I am rumber is not supposed to change. In fact, I don't think they change the merger.
09:46:33 Okay, they're used for identification purposes. It does not quite the same thing as data. And I'll give you.
09:46:49 An example.
09:46:55 Once upon a time.
09:47:06 A student of mine changed her name.
09:47:12 She had to go. This is a very long time. So every office.
09:47:23 In the college.
09:47:27 To report.
09:47:30 The name change.
09:47:34 Now!
09:47:39 Hey, student name.
09:47:43 Is only stored. In one spot.
09:47:51 So changing. Hey name is
09:47:58 Relatively. Easy. And it's more complex than that because in our We know in our database, I think we have.
09:48:11 Current legal name. And preferred name. You know, but. That's really just a wrinkle.
09:48:20 So. If the information might change.
09:48:27 You probably shouldn't use it as a key.
09:48:40 I'm not going to go ahead and draw the But I think you would have discipline and instructor is in a discipline or discipline is in a class that kind of thing.
09:48:56 That is the organization. Now.
09:49:03 I'm going to. This next part will cover in more depth later.
09:49:09 But I wanna at least introduce it so you've heard of it.
09:49:17 Called normalization.
09:49:32 Oh.
09:49:35 Rules are to make it easier to use a database. You notice how We don't, oh, well,
09:49:48 I could also, an advisor relationship here, like just for. Go on.
09:50:00 Output. Pay for advice.
09:50:15 So a student.
09:50:18 So a student. Could be advised by professor. And a student can be enrolled in a section which is taught by the professor.
09:50:28 Different relationships. I know, so back to where I was.
09:50:35 Now,
09:50:56 Okay, now there's.
09:51:02 Only one piece of information can be, that is. You only have One name. One phone number.
09:51:16 One address, particularly only one ID.
09:51:25 Now as programmers You put. 2 values in the same variable.
09:51:45 Yeah, when I say no repeating groups.
09:51:50 Let's say, for example.
09:51:55 That in section. I wanted to put
09:52:02 Title.
09:52:05 I will just temporarily put it in.
09:52:12 Okay.
09:52:16 If I put that in disband course number and title. Would be repeated.
09:52:29 And will. Talk about how to formally identify it.
09:52:35 But. Would that means is
09:52:41 You shouldn't put the title in this section.
09:52:47 Because you can get it by the relationship with the class.
09:52:52 Also, if you put the title in for the section. How many times did the title appear in the database?
09:53:02 The title would appear. Once for each section.
09:53:08 They different than only once.
09:53:13 So. Those are. Conceptually the same rule, but
09:53:23 We're right now only one piece of information can
09:53:28 Be in each field. And
09:53:34 Each.
09:53:57 Each non identifying each non key. Attribute only appears once in the database. Name only appears once.
09:54:09 Title only appears once.
09:54:15 That's the first set of rules.
09:54:23 And it's
09:54:26 Seems like a set of rules. But they're really closely related.
09:54:57 The second rule, you don't have to worry about this too much for today, but you will want to get it later.
09:55:03 No attribute is determined by part of the key. So for example, in my section example up there.
09:55:10 Yeah, the title. Is determined by. Discipline and course number together.
09:55:20 If I put the title here, it's still true that title is determined by discipline and course number together.
09:55:26 But that's a subset of the key for the section table.
09:55:32 So you can't do that.
09:55:35 That tells you the title doesn't belong in that table.
09:56:06 No attribute is dependent
09:56:10 On something.
09:56:15 Oops. Oh, that's right. No, actually, it's dependent on. Something that on the key.
09:56:29 Watch.
09:57:01 No, attribute is transitively dependent on the key.
09:57:07 So. Let's say For some reason somebody wanted to put A student's name.
09:57:22 In there. Well, that would violate the part of the key. But it would also violate because determined by part of the key which is determined by the key.
09:57:39 In this example I don't see a real obvious one but If you
09:57:50 Try to merge too many of these things together
09:57:57 Something about the section.
09:58:02 Who depends on something about the course. You would not wanna put in something that is
09:58:10 Determined by the course. But we'll worry about that. I've got specific examples for that later.
09:58:17 Okay, so. Now. Right now.
09:58:27 Hi, I want you to be. Working on being able to figure out entities figure out relationships, figure out the keys.
09:58:36 And draw the diet.
09:58:39 That's it. That's enough for right now. Then we will. Learn how to put those into databases.
09:58:51 And then we'll formalize this idea. Of normalization.
09:59:01 Please if you have any questions Bringing them up in class Ask me. I want you to get understood.
09:59:13 Right away. I will.
09:59:17 Good this. Document and the diagram. I'll embed the diagram in it. And I'll put it next to the recording.
09:59:31 And I look forward to seeing you next time.