Using Relational Databases and SQL

1. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 1: Introduction to Relational Databases and MySQL

2. Database Origin • Database comes from the open compound word “Data Base” • Data Base was a military term (circa 1964) • Data Base implies data that is both persistent and shared (think Air Force Base) • The military funded much of the research into the early electronic computers of the 40’s and 50’s because of World War II and the Korean War

3. What is a Database? • A structured collection of persistent data that is typically meant to be shared • Used to model organizational activities • Structure is defined through a database model • No database model, no database! • Examples: • Airlines, Banks, Stores and Universities

4. Database Models • The Two Levels of a Database Model • Physical Level (how data is stored)‏ • The things we don’t care about • Logical Level (how data is generalized)‏ • The things we do care about • The Basic Models • Hierarchical Model‏ (IBM’s IMS) • Network Model (CODASYL)‏ • Relational Model (ALPHA, SEQUEL)‏

5. Physical/Logical Separation • Previous to the relational model, one had to write a program that traversed pointers at the physical level to extract data from a database (think like programming in assembly language) • By abstracting the physical level and writing a program at the logical level instead (think like programming in Java), extracting data from a database became easier • First proposed by Dr. Edgar Frank Codd.

6. Dr. Edgar F(rank) Codd • MA Mathematics, MA Chemistry • MS and PhD in Communication Sciences • ACM Turing Award (1981)

7. Relational Model • Proposed by Edgar F. Codd (circa 1969)‏ • Database is a collection of tables (relations) • Relational comes from ‘Relational Algebra/Calculus’ and not from ‘Relationships’ • Relational model is very mathematical • Dominant database model • Thanks to Larry Ellison, founder of Oracle, who was first to aggressively market a commercial relational database product (Ellison is currently the 4th richest person in the world)

8. Tables‏ • Artists = Table (Relation)‏ • ArtistID, City, Region, ... = Columns (Attributes)‏ • Each row is called a Record (Tuple)‏

9. Data Types‏ • Determines storage requirements • Common attribute types • String • Numeric (float, double, real)‏ • Date and Time • Blobs and Clobs (images and other binary data)‏

10. Database Schemas • The definition of the database, where you define • The name of the database • Tables • Column names, types, and constraints • Relationships between tables • Stored functions and procedures • Schemas are typically represented by a schema diagram

11. Database Schemas

12. Database Management Systems (DBMS)‏ • Software (programs) to manage databases • Creation/Deletion • Maintenance (inserting, updating‏, and deleting records) • Security (encryption, user access control)‏ • Common DBMSs • MySQL, PostreSQL (open source)‏ • Oracle, DB2, MS SQL Server (commercial)‏

13. Database Management Systems (DBMS) • You can have multiple databases, each with a single schema • A separate database for each application • Toystore (First database) • Bookstore (Second database) • Furniture Store (Third database) • Etc. • You can also have a single database, with multiple schemas

14. DBMS Diagram

15. Query Languages • Query Language = A language used to extract data from a database • Domain-Specific Language • Only for databases • History • Codd, Alpha (1971)‏ • Chamberlin and Boyce, SEQUEL (1974)‏ • Eventually renamed to SQL

16. QL Examples • Alpha (Codd) • SEQUEL (Chamberlin, Boyce)‏

17. SQL • Stands for Structured Query Language • A non-procedural, domain-specific language (not like C or C++)‏ • An open ANSI standard • Supported by most major DBMS • Some variations in implementations • Used by programmers, managers, and database administrators

18. SQL Functions • View information from relational databases • Single and multiple table selections • Calculation and analysis • Manipulate information in relational databases • Insert and delete records • Update records • Create relational databases • Create databases, tables, constraints, ...

19. Other Interesting Facts • Question: Why is a database always represented by an aluminum can or some type of cylinder?

20. Other Interesting Facts • Answer: Because that’s what disk storage looked like back in the old days!

21. Other Interesting Facts • Since IBM already had a database product called IMS, which was currently being used in the Apollo space program (1969 – 1972), Dr. Codd was asked by IBM not to disparage IMS nor promote his model over IMS since it might hurt sales of IMS. • Many people to this day believe that hindered IBM and Dr. Codd from making all the money that Larry Ellison of Oracle has made.

22. Downloading MySQL • Go to CS122 Wiki page and under the Required Software section, click on the links to • MySQL (client and server program) • MySQL GUI Tools (alternative client program) • MySQL Workbench (schema utility) • Download the versions for Windows, without the installer • Extract all three to a flash drive (preferably)

23. Important Notes!!! • You can put MySQL directly onto these computers, but be aware that other students may delete your stuff (by accident or otherwise) • Do not leave homework, labs, or programs on our computers as other students may find and mishandle your stuff • Always back up your work onto a personal flash drive before you leave • Don’t forget your flash drives