Database Lesson #5 of 8 - Database Design

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.

[@2002egncn]

The best series of database fundamentals I have found. It is 2022 and still hold high!

[@mekabay]

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

[@mgray999]

This needs more up votes

[@chrisbck2490]

Thanks for taking the time to index this.

[@mario17-t34]

Absolutely the best contents, presentation, speed, manners, slides etc... Perfectionne !!!! Thanks so much Dr for you generosity

[@obliviondew9268]

This is the simplest database lecture i've ever had many many thanks Dr.Daniel Soper !

[@biokode]

Best explanations of database concepts I've seen so far. Great stuff!

[@tomasloritegarcia5143]

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.

[@KathrynBrock1]

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.

[@Smr0891]

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

[@nitingadgil1186]

Best tutorial ever on Database basics.

[@zainnm86]

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

[@aaronnarva1586]

This series is wonderful. Very clear and very strategically ordered.

[@sokheachap7887]

by far one of the best videos to help with understanding relational databases....I'm so glad I found your videos!

[@sansybah236]

This is one of best explanation of database concepts. Thx

[@marrr7611]

Great videos! Like your tone of voice and flow...

[@mahendra8472]

Many many thanks.., I did my engineering, but nobody explained the concepts like you... you are just great :)

[@kingsleyakhimien5132]

Thanks Dr. Soper...you excellently explained each concepts with practical examples. Great video!

[@jimc1499]

Thank you for these videos Dr. Soper

[@NekkruEvul]

These videos were very helpful when studying for my Database exam. Excellent stuff. Thank you sir!

[@somachicasincity4232]

Thank you for posting this. Definitely clarified for me the entity relation model and implementation processes.

[@radhwanalbedany8595]

2018 and im following your videos as a best practicing i found

[@ihos4m]

This is an amazing lesson, specially the examples are very helpful. Thank you.

[@MrRapperb]

thank for these videos, they are really useful in learning, and I love the fact that you are a Big Bang Theory fan too :)

[@jimb6554]

I just realized that as well so I searched the comment section for anybody referencing that BBT reference.

[@giacomocorica7172]

Thanks a lot Dr. Soper.

[@ahmedalmakhamreh1244]

Great series, Thank you so much.

[@pinkpearl1967]

This is SO helpful. Thank you!

[@iloveyourmum00]

This is so clear! Thank you su much!

[@salomon0991]

You are brilliant ,,,that all I can see to you . you have helped very much . thanks..thanks

[@satalize3866]

Thank you in advance. I download this and your other lectures on database

[@BhupinderSingh]

Thanks a lot for this awesome video.

[@rakeshsangal202]

I wish you were professor of my MIS class.

[@Regalert]

This guy is perfect (with all due respect)

[@hossamdaw1976]

Thank you, explained very well..

[@hebaalayass2454]

Great explanation !!! thank u

[@jeremylupo203]

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.

[@biggpappa2966]

Great great video thanks 😊 simplifies things in a good way. Know even me I can understand think I couldn't u destiny before 👍

[@nandhi19]

Thank you!

[@pwrtricks]

Very Good!!!i In 2020 adding a lot of knowledge!

[@jimb6554]

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.

[@ExploreNollywood]

Thanks, i learnt a lot

[@cursedtrousers8735]

I want to have a one and only one relationship with you Dr. Soper!

[@radocisar3420]

😊

[@mahmoudabdelbary6859]

many thanks

[@TonyTigerTonyTiger]

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.

[@ramilberces2743]

Thank you Sir

[@Karmidzhanov92]

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!!!

[@nguyentrungkien7116]

When translating hierarchical relationship, if we create a new relation for each of entity type what kind of constraints can be satisfied?

[@maddyflys327]

gracies:-)

[@gtang31]

why do we need to use cross joins when working with the recursive tables?

[@shahdel-badrawy]

great

[@harisubramanian6756]

Nice

[@Ikhideifidon]

In the Rating Associative entity, what if we want a customer to only have 5times rating privileges?

[@nickwoodward819]

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!

[@soundbeans]

@ 34:49 isn't there now repetition in the intersection table? or is this unavoidable?

[@MrAzeemulhaq]

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.

[@BlackOpsQuickscopePS]

Increase the speed to 1.25 ... You can thank me later.

[@uraaxeecaqligaaga4890]

Sir If I ask u a question do you mean person one the person sponsoring and person 2 the person sponsored by? Thank u

[@saurabh7337]

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

[@TonyTigerTonyTiger]

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.

[@nizindia]

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.

[@nickwoodward819]

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

[@davidhodson6506]

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

[@pramujisinggihriyanto6901]

Pls allow me to download your best explanation on database.

[@uraaxeecaqligaaga4890]

What does the star represent for in SQL part? Thank you sir

[@julfamily9088]

It represents 'all' for instance when you say SELECT all or everything form a specific table

[@pardeep657]

the way patient and bed example is explained bit confusing..

[@SpongeBlaster]

This guy must love The Big Bang Theory

[@mazesings]

It's season 10 and Sheldon Cooper still can't drive. Bazinga!

[@Mintos93]

Sheldon need to get to the dentist...

[@Motassem.K]

but he doesn't drive