Database Lesson #5 of 8 - Database Design
HTML-код
- Опубликовано: 25 июл 2024
- Dr. Soper gives a lecture on database design. Topics include transforming ER data models into physical database designs, denormalization, implementing one-to-one, one-to-many, and many-to-many binary and recursive relationships, and writing SQL statements for creating joins for both binary and recursive relationships.
The best series of database fundamentals I have found. It is 2022 and still hold high!
Notes by M. E. Kabay
00:00 Objectives
01:39 From Relations to Entities to Tables
03:26 Transitioning from a Data Model to a Database
06:32 Normalization Review: Modification Anomalies
11:06 Normalization Review: Solving Modification Anomalies
12:19 Normalization Review: Definitions
14:09 Normalization Review: Normal Forms
15:29 Normalization Review: Normalized Relations
16:29 Normalization Example
18:23 Normalized Set of Tables
19:26 Denormalization
20:25 Denormalized Set of Tables
22:53 Denormalization Benefits & Costs
25:43 Representing Relationships: 1:1 Relationships
30:15 Representing Relationships: SQL for 1:1 Relationships
31:02 Representing Relationships: 1:N Relationships
32:41 Representing Relationships: SQL for 1:N Relationships
33:08 Representing Relationships: N:M Relationships
35:50 Representing Relationships: SQL for N:M Relationships
36:45 Representing Relationships: Association Relationships
37:46 Surrogate Keys and Associative Entities
41:34 Representing Relationships: Recursive Relationships
44:05 Representing Relationships: 1:1 Recursive Relationship Examples
46:14 Representing Relationships: 1:N Recursive Relationship Examples
47:55 Representing Relationships: N:M Recursive Relationship Example
This needs more up votes
Thanks for taking the time to index this.
Absolutely the best contents, presentation, speed, manners, slides etc... Perfectionne !!!!
Thanks so much Dr for you generosity
Best explanations of database concepts I've seen so far. Great stuff!
This is the simplest database lecture i've ever had
many many thanks Dr.Daniel Soper !
Thank you!!!! I'm in an online systems class, and this was the help I needed to bridge the gap between in-person and online learning. Very grateful for your doing these lectures.
Thanks a lot Dr. Soper. I am an Engineer Computer and your video tutorial helps me maintain and refresh some already rusted concepts. My sincere gratitude and admiration for your work.
This series is wonderful. Very clear and very strategically ordered.
Best tutorial ever on Database basics.
Fantastically clear and simple explanation of a complex subject. I was struggling with this at work and then I came across ur lectures; they have really helped me out
Great videos! Like your tone of voice and flow...
This is one of best explanation of database concepts. Thx
by far one of the best videos to help with understanding relational databases....I'm so glad I found your videos!
Thanks Dr. Soper...you excellently explained each concepts with practical examples. Great video!
Just an FYI, if you listen with the playback speed on 1.5, it is actually a lot easier to listen to if you have a hard time keeping attention to slow speakers like I do lol. Great info though! Thanks
These videos were very helpful when studying for my Database exam. Excellent stuff. Thank you sir!
Thank you for posting this. Definitely clarified for me the entity relation model and implementation processes.
Many many thanks.., I did my engineering, but nobody explained the concepts like you... you are just great :)
Thank you for these videos Dr. Soper
2018 and im following your videos as a
best practicing i found
This is an amazing lesson, specially the examples are very helpful.
Thank you.
Thanks a lot Dr. Soper.
thank for these videos, they are really useful in learning, and I love the fact that you are a Big Bang Theory fan too :)
I just realized that as well so I searched the comment section for anybody referencing that BBT reference.
Great series, Thank you so much.
This is SO helpful. Thank you!
This is so clear! Thank you su much!
You are brilliant ,,,that all I can see to you . you have helped very much . thanks..thanks
Thanks a lot for this awesome video.
I wish you were professor of my MIS class.
Thank you in advance.
I download this and your other lectures on database
Great great video thanks 😊 simplifies things in a good way. Know even me I can understand think I couldn't u destiny before 👍
This guy is perfect (with all due respect)
Thank you, explained very well..
Great explanation !!! thank u
Thank you!
Thanks, i learnt a lot
Hi Dr. Soper, great videos! I wanted to make a quick suggestion on your 1:N Recursive Relationship example. The way you explain it almost makes it sound the same as the 1:1 relationship in terms of implementation. However, it's important to note here that the direction of the relationship is important in 1:N recursive relationships. You use "referredBy" (essentially the parent) which of course is appropriate. But you couldn't use "referred" (essentially the children). I'd think it's important for students to understand that the column in a 1:N relationship should reflect the parentid.
Very Good!!!i
In 2020 adding a lot of knowledge!
At 30:05 I was confused as to why he keep saying "we put the foreign key on the optional side of the relationship" but then he puts the foreign key into the Patient Table which is the mandatory side of the relationship!
The way I resolve that contradiction is by saying that what he meant by "we put the foreign key on the optional side of the relationship" means that we create a foreign key that "refers" to the optional table but then we "place it" into the mandatory table.
How do we actually create this ‘look up tables’ in N:M relationships. I’m interested in the actual implementation, how do you join those two columns in a separate relation. Thanks and great content!!!
37:53 I don't think it is as big of problem as indicated, to make a surrogate key the primary key of an association entity. The problem mentioned arises only if the two foreign keys are only foreign keys, with no uniqueness enforced. But, at least in T-SQL, you could place a unique constraint on them, making the combination of their values a candidate key.
many thanks
Thank you Sir
I want to have a one and only one relationship with you Dr. Soper!
😊
When translating hierarchical relationship, if we create a new relation for each of entity type what kind of constraints can be satisfied?
gracies:-)
Great Video. I have a doubt, at 12:15 minutes(Data Modelling & ER) , should we not also add customerid(in vehicle table) in the new normalized layout
If the CarId is for a single physical car, then I think your idea of having CustomerId in the Car table is a good idea ... under the assumption that a car can be sold to only a single Customer (what if a car is sold to a husband and wife, both of which are previous Customers????).
But I don't think that is what the data means here. I think the CarId is not for a single physical car, but for a type of car. That is, I believe there could be 10 cars that are 2010 Nissan Centras, all 10 of which would have CarId 103. And if that is the case, it would not work to have a single CustomerId in the Car table.
why do we need to use cross joins when working with the recursive tables?
In the Rating Associative entity, what if we want a customer to only have 5times rating privileges?
Dr. Daniel, appreciate your efforts, really good but I have a question here:
at 32.16, how come the player table can have zero cardinality as a team cannot have zero players so there should not be a circle on the player table in the relationship but a line showing atleast 1 player on the team...Pls confirm.
I mean you could equally argue '1' isn't a team. You could however have created a team (logos, admin, support staff) but you haven't recruited players yet. It's unlikely but possible I'd guess
How do you read the relationship from the STUDENT_CLASS intersetion table to the STUDENT or CLASS table?
I can do it the other way around: A student can appear zero or many times in the student class table, but I can't reverse the logic
Thanks!
@ 34:49 isn't there now repetition in the intersection table? or is this unavoidable?
great
Nice
What does the star represent for in SQL part? Thank you sir
It represents 'all' for instance when you say SELECT all or everything form a specific table
Pls allow me to download your best explanation on database.
the way patient and bed example is explained bit confusing..
It's season 10 and Sheldon Cooper still can't drive. Bazinga!
Increase the speed to 1.25 ... You can thank me later.
is it possible to get the slides for your course i am really struggling with my database managment class i am on my last attempt for my exams
Hello Doctor, I didn't get very last example. Do you mean we have create a new table TREATMENT-INTERSECTION? But this is recursive and only one table or entity is required for it? Please clarify. Thanks.
Sir If I ask u a question do you mean person one the person sponsoring and person 2 the person sponsored by? Thank u
Sheldon need to get to the dentist...
but he doesn't drive
This guy must love The Big Bang Theory