Database Design (Tue Sep 19, lecture 9)

Homework due for today

Legend: : Participation | : Early | : PDF | : Portfolio | : Zipped

  1. PA Rails 1: Begin work on PA Rails 1. The series of PA Rails assignments (there are three) will be a rock solid review of what you’re learning from RORT. They are hard. You should not wait till the last minute!This will be due on TBD.

  2. Schema: Develop an initial schema for your product. Read Basics of good database design in Web Development. You might google around for other sources if you like. You need to think clearly about the functionality that your system will support. You also need to think carefully about relationships.

    For example, let’s say I have a Student entity and I have a Course entity. Is there exactly one student for every course (1 to 1). Are there many students for any one course (many to 1) or perhaps does one student have many courses (1 to many), or maybe even a combination - one student can have many courses, and one course can have many students - (many to many). Don’t even start worrying about designing your database until you have questions like that clear in your mind!

    Using all that background, develop initial schema for your product. Each team member work separately so you can then compare and contrast what you came up with. So this is an individual assignment! Include table names, field or attribute names, and relations or joins. Please use this exact format: Simple Schema Notation Deliverable: Initial schema for your product. As a pdf file generated any way you want. Include your name and hw number!

  3. Project teams: Even though there are no specific deliverables, you should be working on steps GIT, DOCUMENTATION, PLANSKEL, BUILDSKEL, SHEMASKEL, SCHEMASDV from Project Roadmap. Not just during lab and during your meetings, but on your own. Remember with a team of four (or 3 or 5) students, that’s a lot of horsepower, and so we have high expectations. The sooner you kick into gear the better!

Review Homework

  • Discuss some exemplar Schemas that were designed
  • About PR Meetings on Thursday
    • Nice job on presentations everyone
    • How will PR scheduling work exactly

Relational Databases and Object Oriented Software

  • What does an ORM do?
  • “Impendance misatch”
  • The “ActiveRecord” Object Oriented Pattern
    • Wraps records you are working on inside an object of the Active Record pattern
    • Not every record in the database, just what the code is working on at one time or another
  • The Rails Activerecord layer
  • What is the relationship between the underlying DB and the AR objects
  • What is database Migration. What problem does it solve?

Data Modeling Background

  • What does the term ‘Data Modeling’ mean?
  • How Relational Databases work
  • Tables, Columns, Databases, Joins
  • “3d Normal Form”
  • Different approaches
  • Key question is how you correlate the tables in a database with the attributes of a class

Data Modeling

  • Crucial step in design
  • SQL vs. NOSQL databases
  • SQL Databases
    • Schema
    • Tables
    • Fields
    • Joins
  • Relationships
    • One to One (Student <-> Transcript)
    • One to Many (Student -> Car)
    • Many to Many (Student <-> Courses)
  • Careful analysis of the product requirements


  • Team up with one other person
  • Design the data model for a music library management application like iTunes
  • 15 Minutes, ask questions


  1. What are the entities?
  2. Is something contained in something else?
  3. How many X’s are there for each Y?
  4. What kinds of facts do we need to know about each entity?
  5. What are the datatypes of those facts? Are there any ‘arrays’?
  6. Possibly identify other entities
  7. Repeat until done

Look at next class