Databases and Model Classes

1. http://flic.kr/p/ar4nLn Databases and Model Classes

2. 4 SWEBOK KAs covered so far Today’s topics • Software Requirements • Software Design • Software Construction • Software Testing • Software Maintenance • Software Configuration Management • Software Engineering Management • Software Engineering Process • Software Engineering Models and Methods • Software Quality • Software Engineering Professional Practice • Software Engineering Economics • Computing Foundations • Mathematical Foundations • Engineering Foundations

3. Database (DB): Organized collection of data Database Management System (DBMS): Controls the creation, maintenance, and use of a DB http://flic.kr/p/ar4nLn

4. RecallRails MVC Rails uses a DBMS!

5. Why use a DBMS? • Data independence: Applications need not be concerned with how data is stored or accessed • Provides a lot of functionality that would be silly to implement yourself: • Sharing (network) • Customizable security • Integrity

6. Two key aspects of a DBMS • Database model: How DB is structured and used • Examples: Relational, Object-Oriented, Hierarchical • Query language: Types of questions you can ask • Examples: SQL, XQuery Relational + SQL is most common and use by Rails

7. Relational Model Concepts http://en.wikipedia.org/wiki/File:Relational_model_concepts.png

8. Example Tables Authors AuthorISBN Publishers Titles

9. Primary versus Foreign Keys • Primary key: Uniquely identifies each record in table • Foreign key: Field in table A such that the field is a primary key in one other table B Authors AuthorISBN

10. Example Foreign Keys Authors AuthorISBN Publishers Titles

11. CRUD-to-SQL Mapping For complete documentation, see: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-manipulation.html

12. Example SELECT Queries • SELECT * FROM Authors • SELECT AuthorID, LastName FROM Authors • SELECT * FROM Authors WHERE YearBorn > 1910 • SELECT * FROM Authors WHERE LastName LIKE ‘r%’ • SELECT * FROM Authors WHERE LastName LIKE ‘_e%’ • SELECT * FROM Authors WHERE LastName REGEXP ‘[a-r]*’ • SELECT * FROM Authors WHERE LastName REGEXP ‘[a-r]*’ORDER BY LastName ASC For complete documentation, seehttp://dev.mysql.com/doc/refman/5.5/en/select.html http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

13. Use JOIN to merge data from multiple tables • SELECTFirstName, LastName, ISBNFROM Authors INNER JOIN AuthorISBN ON Authors.AuthorID = AuthorISBN.AuthorIDORDER BY LastName, FirstName • SELECT Titles.Title, Authors.LastName, Publishers.PublisherNameFROM (Publishers INNER JOIN Titles ON Publishers.PublisherID = Titles.PublisherID)INNER JOIN (Authors INNER JOIN AuthorISBN ON Authors.AuthorID = AuthorISBN.AuthorID)ON Titles.ISBN = AuthorISBN.ISBN For complete documentation, seehttp://dev.mysql.com/doc/refman/5.5/en/join.html

14. RecallRails MVC You writeOO Ruby But DB knows relational/SQL How to reconcile these diffs?

15. Class with no associations ???

16. Class with no associations

17. One-to-many association ???

18. One-to-many association

19. Many-to-many association ???

20. Many-to-many association

21. Generalization ???

22. Generalization

23. Ruby provides anObject-Relational Mapping (ORM)systemLet’s see how it works…Go to: http://web.stanford.edu/~ouster/cgi-bin/cs142-fall10/lecture.php?topic=activeRecord