230 likes | 411 Views
Steven Emory Department of Computer Science California State University, Los Angeles. Using Relational Databases and SQL. Lecture 1: Introduction to Relational Databases and MySQL. Database Origin. Database comes from the open compound word “Data Base”
E N D
Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 1: Introduction to Relational Databases and MySQL
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
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
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)
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.
Dr. Edgar F(rank) Codd • MA Mathematics, MA Chemistry • MS and PhD in Communication Sciences • ACM Turing Award (1981)
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)
Tables • Artists = Table (Relation) • ArtistID, City, Region, ... = Columns (Attributes) • Each row is called a Record (Tuple)
Data Types • Determines storage requirements • Common attribute types • String • Numeric (float, double, real) • Date and Time • Blobs and Clobs (images and other binary data)
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
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)
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
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
QL Examples • Alpha (Codd) • SEQUEL (Chamberlin, Boyce)
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
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, ...
Other Interesting Facts • Question: Why is a database always represented by an aluminum can or some type of cylinder?
Other Interesting Facts • Answer: Because that’s what disk storage looked like back in the old days!
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.
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)
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