Databases & SQL

1. Databases & SQL By: Julia TartakovskyCS 340, Fall 2004 Acknowledgement (for help with the J# code connecting to an Oracle DB): Yosef Lifshits

2. Database Management System (DBMS) • A DBMS is: • A collection of interrelated data (a database), and • A set of programs to access the data • DBMS provides an environment that is both convenient and efficient to use. • Database Applications: • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades • Sales: customers, products, purchases • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions

3. Purpose of Database System • In the early days, database applications were built on top of file systems • We are managing data in this way even today • E.g. using Windows Explorer to find your files • Drawbacks of using file systems to store data: • Data redundancy and inconsistency • Multiple file formats, duplication of information in different files • Difficulty in accessing data • Need to write a new program to carry out each new task • Data isolation — multiple files and formats • Integrity problems • Integrity constraints (e.g. account balance > 0) become part of program code • Hard to add new constraints or change existing ones

4. Purpose of Database Systems (Cont.) • Drawbacks of using file systems (cont.) • Atomicity of updates • Failures may leave database in an inconsistent state with partial updates carried out • E.g. transfer of funds from one account to another should either complete or not happen at all • Concurrent access by multiple users • Concurrent accessed needed for performance • Uncontrolled concurrent accesses can lead to inconsistencies • E.g. two people reading a balance and updating it at the same time • Security problems • Database systems offer solutions to all the above problems

5. Application Architectures • Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database • Three-tier architecture: E.g. web-based applications, and applications built using “middleware”

6. Levels of Abstraction • Physical level describes how a record (e.g., customer) is stored. • Logical level: describes data stored in database, and the relationships among the data. type customer = recordname : string;street : string;city : integer;end; • View level: application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes.

7. View of Data An architecture for a database system

8. Schemas and Instances • Analogous to types and variables in programming languages • Schema – the logical structure of the database • e.g., the database consists of information about a set of customers and accounts and the relationship between them • Analogous to type information of a variable in a program • Physical schema: database design at the physical level • Logical schema: database design at the logical level • Instance – the actual content of the database at a particular point in time • Analogous to the value of a variable • Physical Data Independence – the ability to modify the physical schema without changing the logical schema • Applications depend on the logical schema • In general, the interfaces between various levels and components should be well defined so that changes in some parts do not seriously influence others.

9. Data Models • A collection of conceptual tools for describing • data • data relationships • data semantics • data constraints • Entity-Relationship model • Relational model • Other models: • object-oriented model • Object-relational model • semi-structured data models, XML

10. Entity-Relationship Model Example of schema in the entity-relationship model:

11. Entity Relationship Model (Cont.) • E-R model of real world • Entities (objects) • E.g. customers, accounts • Relationships between entities • E.g. Account A-101 is held by customer Johnson • Relationship set depositor associates customers with accounts • Widely used for database design • Database design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing

12. Relational Model • A collection of tables • Example of tabular data in the relational model Attributes customer- street customer- city account- number customer- name Customer-id Johnson Smith Johnson Jones Smith 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 Alma North Alma Main North A-101 A-215 A-201 A-217 A-201 Palo Alto Rye Palo Alto Harrison Rye

13. A Sample Relational Database

14. What is SQL? • SQL (pronounced "ess-que-el") stands for Structured Query Language. • SQL is used to communicate with a database. • SQL statements are used to perform tasks such as: • update data on a database (an action query) • retrieve data from a database (a select query)

15. What is SQL? (cont.) • Relational database management systems that use SQL: • Oracle • Sybase • Microsoft SQL Server • Access • Ingres • Standard SQL commands: • "Select“ • "Insert" • "Update“ • "Delete“ • "Create“ • "Drop"

16. SQL • SQL: widely used non-procedural language • E.g. find the name of the customer with customer-id 192-83-7465selectcustomer.customer-namefromcustomerwherecustomer.customer-id = ‘192-83-7465’ • E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465selectaccount.balancefromdepositor, accountwheredepositor.customer-id = ‘192-83-7465’ anddepositor.account-number = account.account-number

17. SELECT For more examples and an interactive environment, go to: http://sqlcourse.com/select.html

18. SELECT with “distinct” • Eliminate identical records.

19. Aggregate with distinct For more examples and an interactive environment, go to: http://sqlcourse2.com/agg_functions.html

20. Group-by • Grouping tuples with identical attributes. For more examples and an interactive environment, go to: http://sqlcourse2.com/groupby.html

21. Summarized information by groups

22. Join • The result table removes meaningless tuples from the cartesian product.

23. Join with group-by and having • QUESTION:What is the meaning of this query?

24. CREATE Table • CREATE TABLE table-name (attr1 attr-type PRIMARYKEY, attr2 attr-type,…,attrN attr-type); • Adds a new table with the specified attributes (and types) to the database. • For more examples and an interactive environment, go to:http://sqlcourse.com/create.html

25. Access Data Types • Numeric (1, 2, 4, 8 bytes, fixed or float) • Text (255 max) • Memo (64000 max) • Date/Time (8 bytes) • Currency (8 bytes, 15 digits + 4 digits decimal) • Autonumber (4 bytes) • Yes/No (1 bit) • OLE (limited only by disk space) • Hyperlinks (up to 64000 chars)

26. Access Numeric types • Byte • Stores numbers from 0 to 255 (no fractions). 1 byte • Integer • Stores numbers from –32,768 to 32,767 (no fractions) 2 bytes • Long Integer(Default) • Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). 4 bytes • Single • Stores numbers from -3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values. 4 bytes • Double • Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values. 15 8 bytes • Replication ID • Globally unique identifier (GUID) N/A 16 bytes

27. Oracle Data Types • CHAR (size) -- max 2000 • VARCHAR2(size) -- up to 4000 • DATE • DECIMAL, FLOAT, INTEGER, INTEGER(s), SMALLINT, NUMBER, NUMBER(size,d) • All numbers internally in same format… • LONG, LONG RAW, LONG VARCHAR • up to 2 Gb -- only one per table • BLOB, CLOB, NCLOB -- up to 4 Gb • BFILE -- file pointer to binary OS file

28. Creating a new table from existing tables • Syntax: • SELECT [DISTINCT] attr1, attr2,…, attr3 INTO newtablename FROM rel1 r1, rel2 r2,… rel3 r3 WHERE condition1 {AND | OR} condition2 ORDER BY attr1 [DESC], attr3 [DESC]

29. ALTER Table • ALTER TABLE table-name ADD COLUMN attr1 attr-type; • … DROP COLUMN attr1; • Adds a new column to an existing database table.

30. INSERT • INSERT INTO table-name (attr1, attr4, attr5,…, attrK) VALUES (“val1”, val4, val5,…, “valK”); • Adds a new row(s) to a table. • INSERT INTO table-name (attr1, attr4, attr5,…, attrK) VALUES SELECT ... • For more examples and an interactive environment, go to:http://sqlcourse.com/insert.html

31. DELETE • DELETE FROM table-name WHERE <where clause>; • Removes rows from a table. • For more examples and an interactive environment, go to:http://sqlcourse.com/delete.html

32. UPDATE • UPDATE tablename SET attr1=newval, attr2 = newval2 WHERE <where clause>; • changes values in existing rows in a table (those that match the WHERE clause). • For more examples and an interactive environment, go to:http://sqlcourse.com/update.html

33. DROP Table • DROP TABLE tablename; • Removes a table from the database. • For more examples and an interactive environment, go to: http://sqlcourse.com/drop.html

34. J# Source Code Connecting to an Oracle DB

35. Screenshot of J# Program Output

36. Questions? • For a neat SQL tutorial and an interactive environment, go to: http://sqlcourse2.com/intro2.html