Database Design for School Students for an Entire School

Подписаться 54 тыс.

Просмотров 20 тыс.

50% 1

📝 Get my Database Design Guides to many different sample databases: www.databasestar.com/dbdesign...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
Databases for schools where students change grades each year is a little more complicated than your average "university database design".
In this video, you'll see me design a database for a school that allows multiple levels, such as a primary school or secondary school. We will look at 16 different requirements and slowly design the database to meet each of them.
Check out the link below for an image of the entire completed design.
⏱ TIMESTAMPS:
00:00 - Intro
01:14 - Req 1: students
02:24 - Req 2: parents and carers
04:31 - Req 3: school years
05:27 - Req 4: terms
0614: - Req 5: year levels
08:01 - Req 6: classes
08:38 - Req 7: subjects
09:13 - Req 8: departments
09:55 - Req 9: teachers
10:43 - Req 10: teacher details
11:15 - Req 11: classes and terms
12:04 - Req 12: classrooms
13:09 - Req 13: class times
13:54 - Req 14: multiple periods
14:35 - Req 15: student scores
15:25 - Req 16: score grade mapping
16:59 - Further requirements
🔗 VIDEO LINKS:
Final ERD of this database: dbshostedfiles.s3.us-west-2.a...

Наука

Опубликовано:

9 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 75

@jasonkelley6185 3 месяца назад

This is my new favorite youtube channel. I'll be binging all your videos. Very concise. You aren't saying "uhm" every other word and stumbling over the ideas. Very clear. Great voice! This is how it should be done. Thanks you!

@DatabaseStar 3 месяца назад

Thanks for the kind words! I try to keep my points focused and well explained so I don’t confuse the viewer and waste their time. I’m glad you’ve noticed that 😊

@-.-smile Год назад

Amazing videos, Ben! Keep up the great work. I’ve been using your videos to upskill my Oracle SQL skills that I use at work. Many thanks!

@DatabaseStar Год назад

Great to hear! Glad you like the videos and that it is helping your SQL skills

@oracle_professor Год назад

Thank You!, I'll recommend the video to my students!

@DatabaseStar Год назад

Thanks! 😀

@gilgameshgaming4210 7 месяцев назад

Thank you for the time and effort you put into this awesome explanation❤

@DatabaseStar 7 месяцев назад

No problem, glad you liked it!

@djukic921 Год назад

This video was very helpful for my project. Thank you very much for this!

@DatabaseStar Год назад

Awesome! Glad to hear it.

@coolingvexation3226 11 месяцев назад

Designing databases is so fun, these videos are very helpful.

@DatabaseStar 11 месяцев назад

Glad you like them!

@epicgameryt4052 Год назад

Really awesome video! Keep up the great work. I've worked on multiple school management systems, and this seems like a really smart approach. I mostly work in the Netherlands, so we don't know things like terms, but great work.

@DatabaseStar Год назад

Thanks a lot! Glad you liked the video.

@TheInternetFan Год назад

best thing I did last year for my training was subscribe to this channel. great content man!

@DatabaseStar Год назад

Thanks! I’m glad you like the content and have subscribed!

@astorgadexterpelaez3502 10 месяцев назад

really helpful video thanks!

@DatabaseStar 10 месяцев назад

Glad it was helpful!

@Chromosome11 9 месяцев назад

After a while finally i find this Gem.

@DatabaseStar 9 месяцев назад

That’s great!

@medilies Год назад

I worked on a SMS before and this gold. There will be one inconvenience with OOP which us the entity named class. The system can also go to supporting school year expenses and having students guardians and teachers as users

@DatabaseStar Год назад

Thanks! Yeah it can be tricky mapping entities to database tables. Thanks for the comment on expenses and users, they are great ideas.

@anmarm.9487 Год назад

This was very helpful. Still interested in more of this database, such as fees, payment installment, attendance, quiz scores, maybe authentication for guardians, maybe tables for notes or messageboard, or even students homework, imagination is endless also few queries to pull useful real life cases. I like your tutorials very helpful, just wish they will go bit more advance.

@DatabaseStar Год назад

Great ideas! I think they could all be added to the database design. I'll keep this in mind for future designs - especially sample queries.

@paywand2959 8 дней назад

This is education.... thank you sir

@DatabaseStar 8 дней назад

You’re welcome!

@barakadewise7200 3 месяца назад

The video is awesome and very usefully thanks

@DatabaseStar 3 месяца назад

Thanks, glad you like it!

@anelembanga6061 7 дней назад

Very informative, thanks.

@DatabaseStar 7 дней назад

Glad it was helpful!

@osmanhamashool6396 Год назад

hey! This is awesome, we would appreciate if you follow up another video with the rest of the requirements.

@DatabaseStar Год назад

Thanks! Sure I can do a follow up video

@mitchell2769 Год назад

thank you for another fantastic video! In the final schema, the one thing I don’t understand is which start_period_id and end_period_id in the Class table are both linked to the PK in the Period table. Shouldn’t they be linked to the start_time and end_time fields in the Period table? (I’m new at this, haha)

@DatabaseStar Год назад

Glad you liked it! Good question. The reason is so that all the classes can have the same start and end time. The start and end times are attributes of a period, and not a class. For example, "Intro to Physics is in period 1 so it starts at 9AM", rather than "Intro to Physics is at 9AM". This also means that any class in period 1 will start at 9AM, which can help with timetables. But this is an assumption for how periods work from when I was at school. Different countries may work differently. Having a start and end time on the class table could also work.

@AhmedNabil-pq6eo Месяц назад

Great job and well done, what is the name of the application that you used to draw the ERD, appreciated it.

@DatabaseStar Месяц назад

Thanks! It’s called LucidChart.

@asdfasdfuhf Год назад

Question for 7:20: You say: "We know that students are in one level at a time, but they can be in more than one level during their time at the school. So, we'll need a joining table, as this is a many-to-many relationship". I would say this is a many-to-one relationship: One student can be at EXACTLY ONE year_level at a time. One year_level can be shared amongst MANY students. Thus, Many-to-One. Is my logic flawed?

@DatabaseStar Год назад

Good question! Yes, it could be described as a one to many relationship. However, for the data in the table, there would be a record for student 1 in year level 1, then student 1 in year level 2. Only one record would be considered the active or current record, but we would still need to store both in the table. I think the main point here is that it's storing data over many years. If it was only storing the current year, then it would be a one to many and we wouldn't need the joining table.

@soj_afk Год назад

Thank you for the question and the response! I was confused about this as well thinking about how one student would be in multiple years!. I figured someone else would have noticed and decided to check the comments for a better response! This is very helpful!!! I definitely would take more videos on database structure on your channel! Thank you for the first class lesson!!!

@kirilsivanovs1929 10 месяцев назад

Good day! I can`t understand... If we are going to store information about Students and Levels/Years as N:M, that why we are not gonna do the same trick with Teacher and Class?@@DatabaseStar

@hiluu Год назад

Thanks for your video! I have a question that how do we design if a class is held in many rooms and it has many periods on different days of week?

@DatabaseStar Год назад

Good question. If that is a possibility you would need to have a joining table I believe, to capture the possibilities or combinations of many classes and many rooms.

@kiethuynh2820 10 месяцев назад

Awesome video! but i wanna ask you about the "guardian_type_id" field, I think that this field should be set in "guardian" table instead of "student_guardian" table

@DatabaseStar 10 месяцев назад

Thanks! Yes the guardian type field could also go in the guardian table and that could work. I added it to student guardian because it allows a guardian to have one type for one student and another type for another student. But that may be rare so it could be unnecessary.

@kiethuynh2820 10 месяцев назад

@@DatabaseStar Thanks u!

@e-polk Год назад

Great video! Could you give me some pointers on how to capture Attendance using this same database design?

@DatabaseStar Год назад

Thanks! I would suggest thinking about the kind of things you need to capture for attendance, and then how it relates to each of the existing tables. For example, you may want to capture attendance for each student within each class for each time the class happens. So you may want an attendance table to link to the student, the class, and store a date, and perhaps an indicator of "attended" and "did not attend". You may also want to capture whether or not the student has already let the school know in advance, e.g. "away with a reason", to tell them apart from students who did not attend but nobody knows why.

@e-polk Год назад

@@DatabaseStar Thank you. Yes, that's exactly what I want to do. How would I set up my relationships between the three tables: Student, Class, and Attendance?

@anmarm.9487 Год назад

Please. Could you elaborate on the relationship rules, example if student get removed which data get removed or changed to null. ON DELETE and ON UPDATE. That will be great...

@DatabaseStar Год назад

Good idea. Generally you would either remove the related IDs or set them to NULL, depending on what rules you wanted to implement. Alternatively you could have a new column in the student table for "active", and if you need to delete a student you would set active=0 instead, so you kept the data. This concept is called "soft delete" and is common.

@pulkitsrivastava6132 Год назад

Why we made separate table for guardian type? The guardian type can be just stored in student_guardian table

@DatabaseStar 11 месяцев назад

It could be stored there. However, having a pre-defined list of guardian types makes it easier to select from a list, and also ensures that only these values can be used. If it's free text then it's easier to put in the wrong data.

@user-mz1sf7iy1p 4 месяца назад

Hi Do you have a template for school management system on access? Kindly share if any.

@DatabaseStar 4 месяца назад

Do you mean a template on Microsoft Access? If so, then no, as I don’t use access. But you can use something like this video to start.

@-0-__-0- Год назад

Does this cater to students of all year levels and college students? I'm expecting that the diagram would work on Elementary and Senior High School students, but may not work in college as it is quite different. But knowing the moodle is capable of doing it, maybe I'm wrong.

@DatabaseStar Год назад

You’re right, it may not work on college students. One reason is that it doesn’t include courses or majors and the ability to have different subjects within them. But the design could be expanded for that I think.

@-0-__-0- Год назад

@@DatabaseStar Thank you.

@GenjaOrigins Год назад

How can i learn to design a database based on requirements? i know sql commadns but i dont know how to desin a database teh shape etc

@DatabaseStar Год назад

I've got a video here that shows you how to design a database from an idea or requirements: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5RpUmDEsn1k.html

@GenjaOrigins Год назад

@@DatabaseStar I had an interview and i got rejected cause they asked me to change my database based on some different requirements and i dont know what to study.

@DANNYEL20122 Год назад

Please can you do this but for colleges

@DatabaseStar Год назад

Sure, I think this design should work for colleges as well. The year level could include rows for different years of college.

@Dhirajkumar-fb3to 4 месяца назад

Is it same for noSQL database mongoDB

@DatabaseStar 4 месяца назад

The concepts should be the same, but the implementation may be different. I don’t have any experience with nosql.

@codingcambodia Год назад

please make on online course database design where each lesson also contains quiz. Student needs need to complete the quiz of the lesson to be able to move to the next lesson

@DatabaseStar Год назад

Good idea! I'll create it in the future.

@codingcambodia Год назад

@@DatabaseStar thanks

@satishkumasi2326 Год назад

Hi sir, is there any way for asking questions to you on your paid courses?, how to contact you if I have questions before enrollment. Could you please provide the contact

@satishkumasi2326 Год назад

Contact form does not work. Could you help

@DatabaseStar Год назад

Hey, yeah you can contact me with any questions you have at my email: ben@databasestar.com. What issue are you having with the contact form?