CS 111 Nov 8 Databases Database Management Systems

CS 111 – Nov. 8 • Databases • Database Management Systems (DBMS) • Structured Query Language (SQL) • Commitment – Please review sections 9. 1 – 9. 2.

Database • A file containing 1+ tables • Table = 2 -d arrangement of data into rows and columns – Rows correspond to “records” – info about 1 customer, 1 student, 1 animal, 1 house, whatever – Columns correspond to “fields” – individual attributes about each record. For example, name, address, phone number, ID number, $ amount

DBMS • • “Data Base Management System” Software that allows us to manipulate a database file. Most often, we want to query the database. Examples: – – – Microsoft Access Open Office Base php. My. Admin Oracle Datatel • SQL = Structured Query Language – All DBMS support SQL – We use SQL to communicate with the database.

SQL • “Structured Query Language” • DBMS accepts commands written in this language, so we can manipulate the database file. • DBMS may actually have point-&-click shortcut features to save time on tedious tasks, such as entering all the data, or creating tables from scratch. • Most common SQL command is the “select” statement, which asks the DBMS to return some of the data in the database. Examples: – Show me everybody’s address – How many employees make over $100, 000 ?

How to begin • Create the database file • Create first table: specify its format – For each field (column), it needs a name, data type and maximum length. – Common data types are: – Int/number – Date – Varchar (“variable-length character string”). Here you must specify a maximum length, such as 20 characters. – Sometimes, you may want to indicate whether a field is required, must have unique values, etc. • Enter data into the table. • Make queries about the table.

Example An Employee table: First Last Location Title Salary Peter Jacobs Brussels Broker 55000 Denise Lambert Brussels Accountant 42500 Robert Nijs Brussels Broker 66700 Ruth Molloy Chicago Manager 68650 Declan Murphy Chicago Accountant 84125 Susan Patterson Chicago Economist 51000 Rachel Brady Cincinnati Broker 43300 David Cunningham Cincinnati Accountant 48000 John Whelan Cincinnati Broker 60500 Yvonne Butler San Diego Broker 48500 Veronica Keating San Diego Broker 72000 Mary Walsh Dublin Accountant 46850

The select statement • Very commonly used in SQL. • Some possible formats: select columns from table; select * from table; select columns from table where condition; • Examples: select First, Last from Employee; select Last, Location, Salary from Employee; select Last, Salary from Employee where Salary >= 70000; select * from Employee where Location = “Dublin”; select * from Employee where Last like “M%”;

Aggregate functions • In SQL, we can ask questions that involve arithmetic, such as finding the max, min, avg of numerical values. select max(Salary) from Employee; select min(Salary), max(Salary) from Employee; select avg(Salary) from Employee where Location = “Chicago”; select avg(Salary) from Employee where Title = “Broker”;