1 / 18

Relational Databases

Relational Databases. Stephen Fulwider COT 4810 4 March 2008. DBMS. “software designed to assist in maintaining and utilizing large collections of data.” (Ramakrishnan) VLDB (>1TB Storage) Google Yahoo! NSA/FBI/CIA. Alternate: File Systems.

imani-warner
Download Presentation

Relational Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Databases Stephen Fulwider COT 4810 4 March 2008

  2. DBMS • “software designed to assist in maintaining and utilizing large collections of data.” (Ramakrishnan) • VLDB (>1TB Storage) • Google • Yahoo! • NSA/FBI/CIA

  3. Alternate: File Systems • Obvious drawback – custom code required per app • Special code needed to answer each question • OS-Dependent • If data write fails, what happens to the file/data?

  4. When not to use? • Small amount of data • Overhead not worth it • Only a few operations needed • Custom solution can yield better performance • Need to change data in unsupported way • e.g. modify pictures, or large amounts of text

  5. Advantages of DBMS • Data Independence / Abstraction • Efficient Data Access • Data Integrity • Security • Data Administration • Concurrent Access

  6. The Relational Model • Relation – a set of records. • Schema – description of data. • Schema specifies the field names (or attributes) • Students(sid: string, name: string, login: string, age: int, gpa: real)

  7. Levels of Abstractions (Schemas) • External • User-level interactions “DDL” • Conceptual • Logical • Stored data in terms of DBMS • Physical • How Data is stored • Indexing

  8. Data Independence • Logical • Changes in relations • Physical • Changes in file structure, indices, etc.

  9. DBMS Components • ACID Properties • Atomicity – all or none • Consistency – data cannot be inconsistent • Isolation – concurrent transactions • Durability – changes will not be lost • Together these form the backbone of what makes a DBMS a DBMS

  10. Integrity constraints • Ensure the data is good • It’s the C of the ACID standard • Make sure numbers are in range • Make sure strings are well formatted • Make sure dates are in proper order • GPA = 42.8? Or -3.1? • E-mail = “ smith@cs” – wasted space and could cause issues with some mail programs

  11. Queries • DBMS Specific FRIENDLY vs. • Relational calculus PROOFY vs. • Relational algebra MIX

  12. SQL • Structured Query Language • Developed by IBM in the 70s • Standardized by ANSI in 1986

  13. Queries • Selection • SQL • SELECT Attribute • FROM Table • RA: • Simple! Let’s get a little more complex

  14. Queries (continued) • SQL • SELECT Attribute • FROM Table • WHERE boolean expression • RA • What about multiple tables?

  15. Queries (continued) • SQL • SELECT Attribute • FROM Table1, Table2 • WHERE boolean expression relating attributes from two tables • RA

  16. Example • Find all student names who have GPA lower than 2.5 • List all (name,age) tuples of students who are 18 or older • Give login of everyone whose birthday is in the next 2 weeks

  17. Homework Questions • Given the following Schema: Students(sid: string, name: string, login: string, age: int, gpa: real) • Write a SQL Statement to list all (name,age,gpa) tuples of students who are younger than their GPA*10. • Write the equivalent relational algebra expression.

  18. References • Dewdney, A.K. The (New) Turing Omnibus. New York: Henry Holt and Company, 1993. • Ramakrishnan, Gehrke. Database Management Systems. Third Edition. McGraw Hill. Ithaca, New York. 2003.

More Related