http flic krpar 4 n Ln Databases and
http: //flic. kr/p/ar 4 n. Ln Databases and Model Classes
4 SWEBOK KAs covered so far 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. Software Requirements Software Design Today’s Software Construction Software Testing Software Maintenance Software Configuration Management Software Engineering Process Software Engineering Models and Methods Software Quality Software Engineering Professional Practice Software Engineering Economics Computing Foundations Mathematical Foundations Engineering Foundations topics
Database Management System (DBMS): Controls the creation, maintenance, and use of a DB http: //flic. kr/p/ar 4 n. Ln Database (DB): Organized collection of data
Recall Rails MVC Rails uses a DBMS!
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
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
Relational Model Concepts http: //en. wikipedia. org/wiki/File: Relational_model_concepts. png
Example Tables Author. ID First. Name Last. Name Year. Born 1 Ayn Rand 1905 2 Peter Benchley 1940 Publishers Author. ISBN Publisher. ID Publisher. Name ISBN Author. ID 1 Bobbs Merrill 0452273331 1 2 Random House 0452011876 1 1400064562 2 Titles ISBN Title Edition. Number Year. Published Description Publisher. ID 0452273331 The Fountainhead 1 1943 … 1 0452011876 Atlas Shrugged 1 1957 … 2 1400064562 Jaws 1 1973 … 2
Primary versus Foreign Keys • Primary key: Uniquely identifies each record in table Authors Author. ID First. Name Last. Name Year. Born 1 Ayn Rand 1905 … … • Foreign key: Field in table A such that the field is a primary key in one other table B Author. ISBN Author. ID 0452273331 1 … …
Example Foreign Keys Author. ID First. Name Last. Name Year. Born 1 Ayn Rand 1905 2 Peter Benchley 1940 Publishers Author. ISBN Publisher. ID Publisher. Name ISBN Author. ID 1 Bobbs Merrill 0452273331 1 2 Random House 0452011876 1 1400064562 2 Titles ISBN Title Edition. Number Year. Published Description Publisher. ID 0452273331 The Fountainhead 1 1943 … 1 0452011876 Atlas Shrugged 1 1957 … 2 1400064562 Jaws 1 1973 … 2
CRUD-to-SQL Mapping CRUD Operation Create Read (Retrieve) Update (Modify) Delete (Destroy) SQL Statement INSERT SELECT UPDATE DELETE For complete documentation, see: http: //dev. mysql. com/doc/refman/5. 5/en/sql-syntax-data-manipulation. html
Example SELECT Queries • SELECT * FROM Authors • SELECT Author. ID, Last. Name FROM Authors • SELECT * FROM Authors WHERE Year. Born > 1910 • SELECT * FROM Authors WHERE Last. Name LIKE ‘r%’ • SELECT * FROM Authors WHERE Last. Name LIKE ‘_e%’ • SELECT * FROM Authors WHERE Last. Name REGEXP ‘[a-r]*’ ORDER BY Last. Name ASC For complete documentation, see http: //dev. mysql. com/doc/refman/5. 5/en/select. html http: //dev. mysql. com/doc/refman/5. 5/en/pattern-matching. html
Use JOIN to merge data from multiple tables • SELECT First. Name, Last. Name, ISBN FROM Authors INNER JOIN Author. ISBN ON Authors. Author. ID = Author. ISBN. Author. ID ORDER BY Last. Name, First. Name • SELECT Titles. Title, Authors. Last. Name, Publishers. Publisher. Name FROM (Publishers INNER JOIN Titles ON Publishers. Publisher. ID = Titles. Publisher. ID) INNER JOIN (Authors INNER JOIN Author. ISBN ON Authors. Author. ID = Author. ISBN. Author. ID) ON Titles. ISBN = Author. ISBN For complete documentation, see http: //dev. mysql. com/doc/refman/5. 5/en/join. html
Recall Rails MVC How to reconcile these diffs? You write OO Ruby But DB knows relational/SQL
Class with no associations ? ? ?
Class with no associations
One-to-many association ? ? ?
One-to-many association
Many-to-many association ? ? ?
Many-to-many association
Generalization ? ? ?
Generalization
Ruby provides an Object-Relational Mapping (ORM) system Let’s see how it works… Go to: http: //web. stanford. edu/~ouster/cgibin/cs 142 fall 10/lecture. php? topic=active. Record
- Slides: 23