Topic 5B – Database Fundamentals

Once you have data worth keeping, the question becomes: how do you organize it so that everyone who needs it can get exactly what they need — and nothing they shouldn't have?

Learning Objectives

By the end of this topic, you should be able to:

Learning Activities

To help you meet the learning objectives, we have prepared a combination of readings, activities, and videos.

Course Readings

These reading were designed to introduce the course topics to an audience of educators. They should be considered "required" and read in order.

Supplemental Readings

Some participants find it helpful to read about a topic from a source written for a slightly more technical audience. These supplemental readings cover similar material as the course readings but may not fully align with the course learning objectives. Use them as an optional complement to your study, not a substitute for the course readings.

Lesson Videos

These videos support the readings above and may present the material with some deeper connections and worked examples.

Checking for Understanding, Questions

Why Databases

  1. A small private school manages student information using three separate spreadsheets maintained by three different staff members: one for contact information, one for enrollment and grades, and one for extracurricular activities. The school decides to merge all three into a single integrated database. Name two specific benefits the school would gain from this change.
  2. In your own words, explain the difference between data and metadata. Then give one example of metadata that a school might collect automatically, without any staff member actively creating it.

The Relational Model

  1. A relation called STAFF has the following attributes: Employee ID, Last Name, First Name, Department, Hire Date, and Salary. How many attributes does it have? What would one tuple in this relation represent?
  2. Two staff members — the principal and the payroll coordinator — both use the same STAFF relation, but the principal's subschema does not include the Salary attribute. What is the practical reason for this restriction, and what database concept implements it?

Database Design

  1. A relation called PROFESSIONAL_DEVELOPMENT stores one tuple for every training session attended by every staff member. Its attributes are: Employee ID, Staff Name, Department, Training ID, Training Title, Provider, Hours, and Completion Date. Identify at least two redundancy problems in this design.
  2. Using the relation in Question 5, describe a specific deletion anomaly that could occur. Then describe how redesigning the database into multiple relations would prevent it.
  3. A school wants to add a new professional development offering to its catalog before any staff members have registered for it. Can they do this in the single-relation design from Question 5? Why or why not? Would the multi-relation redesign solve this problem?

Checking for Understanding, Answers

You can compare your answers to the following answer key.

Show Answer Key

Why Databases

  1. Two benefits (many valid pairs): (1) Elimination of redundancy and inconsistency — with three separate spreadsheets, a student's address might be updated in the contact spreadsheet but not in the others, leading to conflicting records. A single integrated database stores each piece of data once, so updates propagate everywhere automatically. (2) Improved access control — a single database system can enforce who can see what (a teacher sees grades but not financial aid status; the nurse sees health information but not grades), whereas separate spreadsheets shared via email or shared drives are much harder to secure.
  2. Data is the substantive content being recorded — a student's name, a test score, a date of birth. Metadata is data about the data — it describes the structure, origin, or circumstances of the data without being part of the content itself. Example of automatically collected school metadata: a learning management system might record the timestamp of every time a student logs in, how long they spent on each assignment page, or the device type used — all created automatically by the system without any staff member recording it.

The Relational Model

  1. The STAFF relation has 6 attributes (Employee ID, Last Name, First Name, Department, Hire Date, Salary). One tuple would represent a single row — the complete record for one specific employee, containing values for all six attributes (e.g., 1042 | Kowalski | Maria | Math | 2018-08-15 | 58,000).
  2. The practical reason is privacy and access control: salary information is sensitive and should only be accessible to staff with a legitimate need (payroll, HR), not to supervisors who do not manage compensation decisions. The database concept that implements this is the subschema (also called a view): a restricted window into the full schema that presents only the attributes a particular user role is authorized to see. The underlying data in the full STAFF relation is unchanged; the subschema simply hides the Salary column from the principal's view.

Database Design

  1. At least two redundancy problems: (1) Staff information is repeated — every time an employee attends a training, their name and department are stored again in a new tuple. If that employee moves departments, every one of their past training records would need to be updated. (2) Training information is repeated — the Training Title, Provider, and Hours are stored once per attendance record. If the same training is attended by 20 employees, those values appear 20 times. A typo-fix or provider name change would require updating 20 rows. These two independent concepts (who employees are; what trainings exist) are conflated into a single relation.
  2. Deletion anomaly: if the only employee who attended a particular training session (say, Training ID T-042) leaves the district and their record is deleted, all information about that training — its title, provider, and hours — disappears from the database entirely. There is no way to keep the training record without keeping the attendance record. Prevention via multiple relations: splitting the design into a TRAINING relation (Training ID, Title, Provider, Hours) and an ATTENDANCE relation (Employee ID, Training ID, Completion Date) means that training records and attendance records are stored independently. Deleting an employee's attendance records does not affect the TRAINING relation at all.
  3. No, they cannot add a new training to the single-relation design before anyone has attended it. Every tuple in PROFESSIONAL_DEVELOPMENT requires both staff attributes and training attributes — a tuple with no Employee ID is not valid. This is an insertion anomaly: the design makes it impossible to record one concept (a new training offering) without also recording another (an employee who attended it). Yes, the multi-relation redesign solves this: with a separate TRAINING relation, a new training can be added as a tuple in that relation with no corresponding entry in the ATTENDANCE relation, because the two are now independent.

Extend Your Learning

The following resources go a little deeper on topics we touched on but did not fully explore in the readings. These are entirely optional — none of this material appears on the Competency Demo — but each one is a natural "next question" from something covered this week.

  • NoSQL databases: when the relational model is not the right fit
    Relational databases are powerful for structured data, but they can struggle with the scale and flexibility needed for social media, real-time applications, and unstructured content. NoSQL databases offer alternative data models (document, key-value, graph). This overview from MongoDB explains the tradeoffs between relational and NoSQL approaches.
    NoSQL Explained — MongoDB
  • Student data privacy and FERPA
    School databases hold sensitive student information. FERPA (Family Educational Rights and Privacy Act) governs who can access student education records and under what conditions. This overview from the US Department of Education explains what FERPA covers and what rights parents and students have — directly relevant to the access control concepts from the readings.
    FERPA Overview — Student Privacy Policy Office