ch22 - Chapter Twenty-Two: Relational Databases Chapter...

Info iconThis preview shows pages 1–17. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter Twenty-Two: Relational Databases
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter Goals To understand how relational databases store information To learn how to query a database with the Structured Query Language (SQL) To connect to databases with Java Database Connectivity (JDBC) To write database programs that insert, update, and query data in a relational database
Background image of page 2
Organizing Database Information Relational database Stores information in tables Each table column has a name and data type
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Product Table in a Relational Database
Background image of page 4
Product Table Each row corresponds to a product Column headers correspond to attributes of the product All items in the same column are the same data type
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SQL SQL stands for Structured Query Language SQL is a command language for interacting with databases Most relational databases follow the SQL standard SQL is not case sensitive " create table" = "CREATE TABLE " We will use uppercase letters for SQL keywords and mixed case for table and column names
Background image of page 6
Some SQL Types and Their Corresponding Java Types SQL Data Type Java Data Type INTEGER or INT Int REAL Float DOUBLE Double DECIMAL (m, n) Fixed-point decimal numbers with m total digits and n digits after the decimal point; similar to BigDecimal . BOOLEAN Boolean CHARACTER (n) or CHAR (n) Fixed-length string of length n; similar to Str i ng.
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SQL Command to Create a Table CREATE TABLE Product ( Product_Code CHAR(11), Description CHAR(40), Price DECIMAL(10, 2) )
Background image of page 8
SQL Command to Add Data to a Database Use the INSERT INTO command to insert rows into the table Issue one command for each row, such as INSERT INTO Product VALUES ('257-535', 'Hair dryer', 29.95)
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SQL SQL uses single quotes ( ' ), not double quotes, to delimit strings What if you have a string that contains a single quote? Write the single quote twice: Sam''s Small Appliances ' To remove an existing table use the DROP TABLE command DROP TABLE Test
Background image of page 10
A Customer Table Consider a Java class Customer : public class Customer { . . . private String name; private String address; private String city; private String state; private String zip; } It is simple to come up with a database table structure that allows you to store customers
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
A Customer Table
Background image of page 12
An Invoice Table For other objects, it is not so easy to come up with an equivalent database table structure Consider an invoice; each invoice object contains a reference to a customer object: public class Invoice { . . . private int invoiceNumber; private Customer theCustomer; . . . } Continued
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
An Invoice Table (cont.) You might consider simply entering all the customer data into the invoice tables It is wasteful It is dangerous; on a change, you can forget to update all occurrences In a Java program, neither of these problems occur Multiple Invoice objects can contain references to a single Customer
Background image of page 14
Poor Design for an Invoice Table with Replicated Customer Data
Background image of page 15

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Linking Tables Replicating the same data in multiple rows has two problems
Background image of page 16
Image of page 17
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 102

ch22 - Chapter Twenty-Two: Relational Databases Chapter...

This preview shows document pages 1 - 17. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online