Database Design (Tue Sep 26, lecture 10)

Homework due for today

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

Alert: The series of PA Rails assignments (there are three) are pretty hard. You should not wait till the last minute!
  1. PA Rails 1: Begin work on PA Rails 1. You can look at this: Homework - Forms in Rails for some ideas. This will be due in about 10 days.
  1. 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. Use the required format!! Include your name and hw number!

  2. 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
  • How hard is it to follow the syntax required?

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