Lecture 10: Relational Databases & Structured Query Language (SQL)

Lecture 10:Relational Databases & Structured Query Language (SQL)

Objectives “The large majority of today's business applications revolve around relational databases and the SQL programming language (Structured Query Language). Few businesses could function without these technologies…” • Relational databases • Database management systems • SQL

Part 1 • Relational databases…

DB Relational databases • Most of today's databases are relational: • database contains 1 or more tables • table contains 1 or more records • record contains 1 or more fields • fields contain the data • So why is it called "relational"? • tables are related (joined) based on common fields

Example • Here's a simple database schema for tracking sales • 3 tables, related by primary keys (CID, OID, PID) • primary keys (in boldface) are unique record identifiers • customer may place order for one product at a time…

Customers… • Here's some data for the Customers table • ignore last row, it's a MS Access mechanism for adding rows…

Products… • Here's some data for the Products table • yes, we're selling animals :-)

Orders… • Here's some data for the Orders table • how do you read this? • e.g. order #9906 states Kathie O'Dahl purchased 600 Ants • must join tables together to figure that out...

Part 2 • Database management systems…

Data request DB Engine Database management systems • A DBMS consists of 2 main pieces: • the data • the DB engine • the data is typically stored in one or more files

Common types of DBMS • Two most common types of DBMS are: • Local • Server

DB Engine Data application Local DBMS • A local DBMS is where DB engine runs as part of application • Example? • MS Access • underlying DB engine is JET ("Joint Engine Technology")

DB Engine Server DBMS • A server DBMS is where DB engine runs as a separate process • typically on a different machine (i.e. server) • Examples? • MS SQL Server, Oracle, DB2, MySQL Data network? server application

Databases & Visual Studio .NET • Most databases ship with tools for opening / manipulating DB • MS Access database: use the MS Access Office product • SQL Server database: use Query Analyzer • But you can also use Visual Studio .NET! • View menu, Server Explorer • right-click on Data Connections • Add Connection... • MS Access database: • Provider: JET 4.0 OLE DB • Connection: browse… • Connection: test... • Drill-down to Tables • Double-click on a table • "Show SQL Pane" via toolbar

Part 3 • Structured Query Language…

Structured Query Language • SQL is the standard programming language of relational DBs • SQL is written in the form of queries • action queries insert, update & delete data • select queries retrieve data from DB • SQL is a great example of a declarative programming language • your declare what you want, DB engine figures out how…

Select queries • Select queries retrieve data w/o changing DB • you specify what data • you specify which table(s) to pull from • you specify under what conditions to retrieve data (if any) • you specify how to present data (if any) • Key concept: • what you get back is another (temporary) table!

Example • Select all customers, in no particular order: Select * From Customers;

Example #2 • Select all customers with a balance, in sorted order: Select * From Customers Where Balance > 0.0 Order By LastName Asc, FirstName Asc;

Computed fields • SQL is a programming language, so it can do computation • Example: • compute # of customers, average balance, and max balance Select count(*) as COUNT, avg(Balance) as AVG, max(Balance) as MAX From Customers;

Joins • You have to "join" tables when you want to combine data • Example: • what's the name of the customer that placed order #12351? • we need to join Customers table with Orders table… Select FirstName, LastName From Customers Inner Join Orders On Customers.CID = Orders.CID Where Orders.OID = 12351;

Nested select queries • Often you need to nest queries • Example: • what products have been ordered by "Jim Bag"? • requires 2 steps: • TEMP = join Orders with Customers to get Jim's orders • then join Products with TEMP to get product names Select Distinct Name From Products Inner Join (Select PID From Orders Inner Join Customers On Orders.CID = Customers.CID Where FirstName = 'Jim' and LastName = 'Bag') as TEMP On Products.PID = TEMP.PID Order By Name Asc;

Action queries • Action queries update a database • Main actions are insert, update, and delete • as with Select queries, date / string values require delimiters • bad things happen if you forget them…

Examples • Here's an example of each type of action query… • once again, note the delimiters around text & date values Insert Into Orders(OID, CID, PID, Quantity, DateOfPurchase) Values(33411, 14, 1, 1, '01-Jun-2004'); Update Customers Set CreditLimit = 40000000000.0, Balance = 0.0 Where LastName = 'Gates' and FirstName = 'Bill'; Delete From Customers Where CID = 666;

Summary • Databases are a critical component of most business apps • SQL is the standard programming language for databases • Databases are a huge subject area in Computer Science • as well as in business applications

Lecture 10: Relational Databases & Structured Query Language (SQL)