Google
Database Systems
Relational Database
• Most common form of database • Uses tables consisting of rows (tuples) and columns (attributes) • Most tables contain a primary key that uniquely identifies each row in the table (No duplicates allowed) • Rows in one table may point to rows in another table through the use of a foreign key – it points to a primary key in another table. A table may have multiple foreign keys. • Tables are related to one another by their keys
Example
• Two tables are shown below. Each has a primary key. In addition, the CourseInstance table has a foreign key which links its rows back to the Courses table
Primary Key
Foreign Key
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
1
SQL
Structured Query Language
• Standard language used to communicate with a DBMS • Language is divided into two parts o Data Definition Language (DDL) -- used to define tables in DB o Data Manipulation Language (DML) – use to manipulate data in tables
Common SQL Commands (that are part of DDL)
• • • • CREATE DATABASE CREATE TABLE DROP TABLE ALTER TABLE Create a new database Create a new table in a database Remove a table from a database Change the specifications of a table
Common SQL Commands (that are part of DML)
• • • • SELECT INSERT DELETE UPDATE Query the database Add new row(s) to the database Remove a specified row Modify a specified row
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
2
SQL – Creating and Removing Tables
Create a new table
• CREATE TABLE table-name (attribute-list) • Each attribute has a data type – char is a fixed string, varchar is a variant string with a maximum length specified • The primary key is also specified • Example: CREATE TABLE Courses ( CourseId integer, College char(20), CourseNumber integer, CourseName varchar(50), NumCredits integer, primary key CourseId )
Deleting a table
• DROP table-name • Examples: DROP Courses
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
3
Relational Database
• Most common form of database • Uses tables consisting of rows (tuples) and columns (attributes) • Most tables contain a primary key that uniquely identifies each row in the table (No duplicates allowed) • Rows in one table may point to rows in another table through the use of a foreign key – it points to a primary key in another table. A table may have multiple foreign keys. • Tables are related to one another by their keys
Example
• Two tables are shown below. Each has a primary key. In addition, the CourseInstance table has a foreign key which links its rows back to the Courses table
Primary Key
Foreign Key
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
1
SQL
Structured Query Language
• Standard language used to communicate with a DBMS • Language is divided into two parts o Data Definition Language (DDL) -- used to define tables in DB o Data Manipulation Language (DML) – use to manipulate data in tables
Common SQL Commands (that are part of DDL)
• • • • CREATE DATABASE CREATE TABLE DROP TABLE ALTER TABLE Create a new database Create a new table in a database Remove a table from a database Change the specifications of a table
Common SQL Commands (that are part of DML)
• • • • SELECT INSERT DELETE UPDATE Query the database Add new row(s) to the database Remove a specified row Modify a specified row
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
2
SQL – Creating and Removing Tables
Create a new table
• CREATE TABLE table-name (attribute-list) • Each attribute has a data type – char is a fixed string, varchar is a variant string with a maximum length specified • The primary key is also specified • Example: CREATE TABLE Courses ( CourseId integer, College char(20), CourseNumber integer, CourseName varchar(50), NumCredits integer, primary key CourseId )
Deleting a table
• DROP table-name • Examples: DROP Courses
JDBC
Northwestern Polytechnic University
Dr. Nels Vander Zanden
3