Computer Science
Grade 10
20 min
Database Design
Database Design
Tutorial Preview
1
Introduction & Learning Objectives
Learning Objectives
Define what a database is and explain the purpose of database design.
Identify and differentiate between entities, attributes, and relationships in a given scenario.
Explain the concept of a primary key and its importance for data integrity.
Create a simple Entity-Relationship Diagram (ERD) to model a database structure.
Normalize a simple, unorganized table into First Normal Form (1NF).
Distinguish between a flat-file database and a relational database.
Ever wonder how Spotify instantly brings up all your saved playlists and favorite songs? 🤔 It's not magic, it's powerful database design!
In this lesson, you'll learn the fundamental principles of organizing data. We'll explore how to structure information logically to build efficien...
2
Key Concepts & Vocabulary
TermDefinitionExample
EntityA person, place, thing, or concept about which data is stored. It's like a noun in a sentence and becomes a table in a database.In a school database, 'Student', 'Teacher', and 'Course' are all entities.
AttributeA property or characteristic of an entity. These become the columns in a database table.For the 'Student' entity, attributes would be `student_id`, `first_name`, `last_name`, and `grade_level`.
RelationshipThe way two or more entities are connected or associated with each other.A 'Teacher' *teaches* a 'Course'. A 'Student' *enrolls in* a 'Course'.
Primary KeyAn attribute (or set of attributes) that uniquely identifies each record (row) in a table. It cannot be empty (NU...
3
Core Syntax & Patterns
Entity Integrity Rule
Every table must have a primary key, and the column(s) for that primary key cannot be NULL (empty).
This rule ensures that every single record in a table is unique and can be reliably identified. You apply this rule when designing any table to guarantee that you can find and manage each row without confusion.
Referential Integrity Rule
A foreign key must either match a primary key value in the table it links to, or it must be NULL.
This rule prevents 'orphan' records. For example, you can't have an order for a `customer_id` that doesn't exist in the 'Customers' table. It maintains the consistency of relationships between tables.
First Normal Form (1NF)
A table is in 1NF if all its columns contain atomic (indivisible)...
4 more steps in this tutorial
Sign up free to access the complete tutorial with worked examples and practice.
Sign Up Free to ContinueSample Practice Questions
Challenging
A company stores employee phone numbers in a single `employee_phones` field like '555-1234 (Home), 555-5678 (Work)'. The tutorial suggests creating a separate `PhoneNumbers` table. What is the primary advantage of this normalized design when a user wants to search for all employees with a 'Work' phone number?
A.The normalized design uses less disk space.
B.Searching becomes much simpler and more reliable because 'Work' is a value in a dedicated 'type' column, not text embedded in a larger string.
C.The original design is impossible to search.
D.The normalized design allows for more than two phone numbers per employee.
Challenging
In the Student Clubs example, the normalized design has `Students(student_id, student_name)` and `Club_Memberships(membership_id, student_id, club_name)`. If you wanted to add information about each club (e.g., the faculty advisor's name), what is the most efficient database design change?
A.Add an `advisor_name` column to the `Club_Memberships` table.
B.Add an `advisor_name` column to the `Students` table.
C.Create a new `Clubs` table with `club_name` and `advisor_name`, and link `Club_Memberships` to it.
D.Create a new `Advisors` table and add `advisor_id` to the `Students` table.
Challenging
You are given a 'kitchen sink' table for a school: `(student_id, student_name, course_id, course_name, teacher_name, grade)`. Which set of tables represents the best normalization of this structure to avoid redundancy?
A.`Students(student_id, student_name)` and `Courses(course_id, course_name, teacher_name, grade)`
B.`Students(student_id, student_name, grade)` and `Courses(course_id, course_name, teacher_name)`
C.`Students(student_id, student_name)`, `Courses(course_id, course_name, teacher_name)`, and `Enrollments(student_id, course_id, grade)`
D.`Student_Courses(student_id, course_id)` and `Teacher_Grades(teacher_name, grade)`
Want to practice and check your answers?
Sign up to access all questions with instant feedback, explanations, and progress tracking.
Start Practicing Free