Simple Databases

1. Simple Databases

2. Setting Up SQLite in Eclipse • Use SQLite – already installed on the linux machines • Download one of the following two SQLite JDBC drivers • sqlitejdbc-v056.jar • sqlite-jdbc-3.7.2.jar • Store it wherever you like

3. At Least Two Methods to Get it Working • Both basically put the jar you just downloaded in the build path for your project. • Technique 1:Right click on your project icon in the Package Explorer. In the menu select Build Path and then Add External Archives. Use the folder explorer that appears to find the jar file you downloaded and select “open” and it will be made part of your program’s build path.

4. At Least Two Methods to Get it Working • Technique 2: • Select Runat the top of the page. • Select Run Configurations… about 5 lines down. • Select the Classpath tab in the row of tabs underneath the name of your main routine. • In the Classpath window select User Entries • Select Add External Jars… from the right column • Now navigate to the folder where you stored your sqlitejdbc jar file • Select the jar file • Hit the Open button • Then select Apply button

5. Installing SQLite3 on Linux • Linux • Download the source file from (usually the second file listed) http://www.sqlite.org/download.html • tar –xzvf the downloaded file • cd to the new folder • ./configure • make • make install

6. Installing SQLite3 on a Mac • On a recent OS you don’t have to, it is already there

7. Installing SQLite3 on Windows • Download the first two zip files from the section labeled Precompiled Binaries for Windows. • Unzip them and place the three resulting files in C:\WINDOWS\system32 (or any directory on you PATH. • Alternative: I created a new directory called SQLite in C:\Program Files (x86) and placed the three files in that location. I then extended the PATH variable to search that location

8. Adding the SQLite Manager to FireFox • You can manage an SQLite database using the command line and text-based SQLite commands, but, it is easier to the SQLite Manager extension you can get for Firefox. • First, start Firefox • Then go to https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ and hit the green “Add to Firefox” button and install the extension. • After it is installed you can click on the “SQLite Manager” under the Tools tab at the very top.

9. Using SQLite ManagerCreating/Deleting/Closing/Connecting To a Database • Creating a Database • Database Tab >> New Database • Enter name (.sqlite will be appended to end) • Navigate to desired folder and click “Select Folder” • Close a Database • Database >> Close Database • Connect to Database • Database >> Connect Database • Navigate to desired folder, select sqlite file, hit Open • Delete Database • From command line , Windows Explorer, or Finder navigate to appropriate folder and remove file containing database

10. Using SQLite ManagerCreating a Table • Adding a simple Table • Table >> Create Table • Fill in Table Name text box with the new table name • For each column (attribute) • Fill in Column Name text box with column name unique to this table • Select Data Type • Only real types are Text, Numeric, Integer, Real, None (Blob) • Select/Enter Constraint: Primary Key, Autoinc, Allow Null, Unique, Default Value • Enter attributes or columns • Give it a name • Give it a data type • Identify it as having • Enter a Column Name (e.g. Id) • Select a Data Type (e.g. Integer) • Select the appropriate check boxes • Primary Key • Autoinc • Allow Null (Not NULL) • Unique • Default: Enter Value (rarely use one of possible selections) • Hit OK Button

11. Using SQLite ManagerViewing Database • Select a Table • Select Structure tab • Shows the corresponding Create statement • Operations (also in the Structure tab) • Drop • Empty • Rename

12. Using SQLite ManagerAdding Data • Select Table on Left >> Select Browse and Search tab • Add a tuple • Hit Add Button, Fill in values (default shown), hit OK button • Notice SQL statement • Update a tuple (Edit) • Select Row, Hit Edit Button, Modify Values, hit OK • Delete a tuple • Select Row, Hit Delete Button (you better be sure)

13. Using SQLite ManagerSearching Database • Select a Table • Select Browse & Search tab • Hit Search button • Enter operations and values for all attributes in query • Hit OK Button

14. SQL Statements by Example • Will be used in JDBC • Execute SQL button • All previous operations and much more can be done with SQL Statements • SQL Manger used primarily for viewing

15. Create Table • CREATE TABLE Person ( PersonIdInteger Primary Key AutoIncrement , /* It must always be non-null. Autoincrement only allowed on Integer Primary Key */ PersonName Text, -- Notice it can be null Birthdate Text NOT NULL) • CREATE TABLE Marriage ( MarriageId Integer Primary Key, Husband Integer REFERENCES Person(PersonId), -- one way to specify foreign keys Wife Integer, FOREIGN KEY (Wife) REFERENCES Person(PersonId)) – another way to specify foreign keys • There is a Default Primary Key if none specified • As if there were the attribute: ROWID Primary Key • Turning on and off foreign key checking • PRAGMA foreign_keys = ON; • PRAGMA foreign_keys = OFF;

16. DROP, EMPTY, or RENAME Table • DROP TABLE Person; • DELETE FROM Person; • ALTER TABLE Person RENAME TO Student; -- not used much

17. Adding Tuples To A Table • INSERT INTO Person VALUES(1, “William”, “Male”, “222 West Elm”, 27); -- The order of values is important • INSERT INTO Person (Id, Name, Gender, Address, Age) VALUES (2, “William”, “Male”, “222 West Elm”, 27); • INSERT INTO Person (Age, Address, Gender, Name, Id) VALUES (27, “222 West Elm”, “Male”, “William”, 3); • INSERT INTO Person (Id, Name) VALUES (4, “Bill”); -- other values become NULL

18. Modifying a Tuple • UPDATE Person SET Name = “Bill”, Phone = “801-555-5555” WHERE Name = “William”;

19. Simple Search • SELECT * FROM GradeBook WHERE Grade = “A”; • SELECT * FROM GradeBook WHERE Grade = “A” AND Section = 1; • SELECT * FROM GradeBook WHERE MidTerm1 IS NULL;

20. Deleting a Tuple • DELETE FROM Class List WHERE Section = 2;

21. 236 Review or Project,  or Select,  or Rename •  or Project • Select Execute SQL Tab • Sample SQL statements • SELECT x,z FROM Person; • SELECT * FROM Person; • Semi colon not needed in SQL Manger but is needed in JDBC •  or Select • Select Execute SQL Tab • Sample SQL statements • SELECT * FROM T WHERE x = y; •  or Rename – renames column in resulting table • Used primarily to control Join • Select Execute SQL Tab • SELECT X AS A, Y, Z FROM T; • Multi-word names should be quoted

22. Queries“Union” • SELECT Name, Address FROM ClassList UNION SELECT Name, Address FROM FacultyList; • Doesn’t have to be Union compatible • The names from the second table are “renamed” to the names of the first table

23. Queries“Difference” • SELECT Name, Address FROM Staff EXCEPT SELECT Name, Addr FROM Students

24. Queries“Intersection” • SELECT Name, Address FROM Staff INTERSECT SELECT Name, Addr FROM Students

25. QueriesCartesian Product • SELECT * FROM Person CROSS JOIN Spouse;

26. QueriesNatural Join • SELECT * FROM Person NATURAL JOIN Spouse; • To rename before natural joins use nested queries or sub queries • CREATE TABLE Person (Id Integer Primary Key, Name TEXT); • CREATE TABLE Spouse(Id Integer Primary Key, Spouse Integer); • SELECT Spouse1, Name AS Spouse2 FROM Person NATURAL JOIN (SELECT Spouse AS Id, Name AS Spouse1 FROM Person NATURAL JOIN Spouse ); • Any place you can put a Table name in a query you can put: (SELECT …)

27. QueriesInner Join • SELECT Customers.AcctNumber, Customers. Custname FROM CustomersINNER JOINcust_invoice ONCustomers.AcctNumber = cust_invoice.AcctNumber; • SELECT  Customers.AcctNumber, Customers. Custname FROM  Customers JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber; • Join controlled by “ON” clause

28. QueriesLeft Outer Join • SELECT Customers.AcctNumber, Customers. Custname FROM CustomersLEFT OUTER JOINcust_invoice ONCustomers.AcctNumber = cust_invoice.AcctNumber; • SELECT  Customers.AcctNumber, Customers. Custname FROM  Customers LEFT JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber; • All tuples in Left table of the join that do not match tuples from the right table according to the “ON” clause are preserved by appending a so-called generated “Default” tuple from the right table.

29. QueriesRight Out Joins • There is NO Right Outer Join in SQLite • If you wanted Customers.AcctNumber, Customers. Custname FROM  Customers RIGHT OUTER JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber; • You would use Customers.AcctNumber, Customers. Custname FROM  cust_invoice LEFT OUTER JOIN Customers  ON Customers.AcctNumber = cust_invoice.AcctNumber;

30. QueriesOuter Joins • There is NO Outer Join in SQLite • If you wanted Select Customers.AcctNumber, Customers. Custname FROM  Customers OUTER JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber; • You would use Select Customers.AcctNumber, Customers. Custname FROM  cust_invoice LEFT OUTER JOIN Customers  ON Customers.AcctNumber = cust_invoice.AcctNumber UNION Customers.AcctNumber, Customers. Custname FROM  Customers  LEFT OUTER JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber;

31. Views --Giving a Query a Name • CREATE VIEW TableName AS SELECT … • CREATE VIEW ParentsChildrenAS SELECT PersonName AS ParentName, ChildName FROM Person JOIN (SELECT ParentId, PersonName AS ChildName FROM Person JOIN Children WHERE Person.PersonId = Children.ChildId ) WHERE Person.PersonId = ParentId; • You cannot edit (delete, insert, or update) a view • Also an example of nested queries

32. Using The Command Line ForCreating/Deleting/Closing/Connecting To a Database • Creating a Database • sqlite3 newDBName.sqlite • If the file newDBName.sqlite does not exist it will create a new database with that name for you • Close a Database • While in sqlite3 type .quit at the prompt • Connect to Database • Assuming there is an existing “DB.sqlite” database Sqlite3 DB.sqlite • Delete Database from the command line • In Windows type: del DB.sqlite • In Unix or a Mac type : rmDB.sqlite

33. Transactions • Treat sequence of 2 or more SQL statements as if it were a single statement. • Constraints not checked until the end of the transaction • ACID: Atomic Consistent, Isolated, and Durable

34. TransactionsAutocommit or Atomic Transactions • Autocommit or atomic transaction UPDATE authors SET au_fname = 'John‘ WHERE au_id = '172-32-1176'

35. TransactionsMulti-Statement Transactions • BEGIN TRANSACTION UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176‘; UPDATE authors SET au_fname = 'Marg‘ WHERE au_id = '213-46-8915' COMMIT TRANSACTION; • The word “COMMIT” may be replace by “END” • SavePoints • Rollbacks • IF statements IF (…) BEGIN ROLLBACK TRANSACTION [TO SAVEPOINT savepointName] END ELSE BEGIN COMMIT TRANSACTION END

36. Using JDBC • Sample.java • Sample2.java – Shows drop table and insert • Empty Table and Rename Table • Update (Modify) • Simple Select • Deleting a Tuple • Select, Project, Rename • Union, Intersection, Difference • Joins • Transactions • Views • Support Classes: