CSE 344: Intro to Data Management

True or False: The same data can be represented in different ways.

True The same data can be represented in different ways.

[Section] SQLite: Basic SQL Statements What does UPDATE do?

UPDATE: updates data in table [Example] UPDATE tableName SET .... WHERE [condition];

[Section] What are some more SQL things to know for reference?

- WHERE clause - filter records - AND, OR operator - filter records based on more than one condition - LIKE operator - used in a WHERE clause to searchfor a specified pattern in a column - AS - give an alias name to a table or a column - Relational operators: =, >, >=, <, <=

[Section] SQLite: Special Commands What does .help, .header on/off, .mode [mode type], .show do?

.help - list other . commands .header on/off - show/hide column headers in query results .mode [mode type] - change how to separate the columns in each row/tuple (for better formatting) [Mode type examples]: csv, tabs, line .show - lists all display options

[Section] SQLite: Basic SQL Statements What does CREATE TABLE do?

CREATE TABLE: creates a new table [Example] CREATE TABLE tableName (columnName int, ... );

In terms of the characteristics of The Relational Model, describe Set semantics.

In terms of the characteristics of The Relational Model, Originally it was defined with Set semantics: - No duplicate tuples (modern systems allow duplicates- not in most DBMS implementations) - Order doesn't matter

In terms of the characteristics of The Relational Model, describe attributes.

In terms of the characteristics of The Relational Model, attributes are typed and static. [Examples] - INTEGER - FLOAT - VARCHAR(n) - DATETIME

In terms of the characteristics of The Relational Model, describe tables.

In terms of the characteristics of The Relational Model, tables are flat.

What are the 3 parts of any data model?

The 3 parts of any data model are: - Instance (red): The actual data - Schema (green): A description of what data is being stored - Query Language (blue): How to retrieve and manipulate data

How do we describe information?

We describe information using a data model which is a mathematical formalism to describe data. It is how we can talk about data conceptually without having to think about implementation.

[READ] Week 1: SQL Joins - demo_joins.sql

Week 1: SQL Joins

[Section] What is SQLite?

[Section] SQLite is a C library that implements a relational database management system (RDBMS) - sqlite3 is a standalone program that can run programs and manage a SQLite database

What is a database? Give examples of databases.

A database is collection of files storing related data. [Section Definition] a database is a collection of organized files containing related data persisting over a long period of time [Examples] - Accounts database - Payroll database - UW student database - Amazon's products database - Airline reservation database

[Section] SQLite: Basic SQL Statements What does ALTER do?

ALTER: modify an existing table's attributes/characteristics [Example] ALTER TABLE tableName ADD COLUMN columnName columnDatatype; [Example] ALTER TABLE tableName DROP COLUMN columnName; (Note: SQLite does not support dropping an attribute for versions prior to 3.35.5) [Example] ALTER TABLE oldName RENAME TO newName;

[Section] What are some SQLite: Special Operators?

DATE operator: lets you work with dates and times; declare as varchar (see hw1 documentation) [Example] SELECT * FROM tableName WHERE dateColumn ='YYYY-MM-DD'; SELECT * FROM tableName WHERE dateColumn < DATE('now', '-1 month'); Other operators: LIKE, LENGTH(string), SUBSTR(string, start index, end index), etc.

What is DBMS? Give examples of DBMSs.

DBMS stands for database management system, it is a big program written by someone else that allows us to manage efficiently a large database and allows it to persist over long periods of time. [Section Definition] a DBMS is a program that allows for efficient management of large databases [Examples] - Oracle, IBM DB2, Microsoft SQL Server, Vertica, Teradata - Open source: MySQL (Sun/Oracle), PostgreSQL, CouchDB - Open source library: SQLite

[Section] SQLite: Basic SQL Statements What does DELETE do?

DELETE: deletes data in table [Example] DELETE FROM tableName WHERE [condition];

How is data ACTUALLY stored? Do we care?

Don't know. Don't care. Physical Data Independence.

[Section] SQLite: Basic SQL Statements What does INSERT INTO do?

INSERT INTO: inserts new data into table [Example] INSERT INTO tableName VALUES (value1, ...);

[Section] What if we want to add more data? Consider the following, We have a table regarding Companies. What if we want to also add the products they manufacture How can we add this information? Hint: Tables have to be FLAT in SQL

If we want to add more data, we would create another table!

[Section] SQLite: Basic SQL Statements What does SELECT do?

SELECT: gets existing data from table [Example] SELECT columnName FROM tableName;

What is SQL?

SQL (standing for Structured Query Language) is the standard language for relational database management systems. [Section Definition] SQL is a declarative query language designed for managing data held in a relational database management system (RDBMS) that can - data insert - delete - query - schema creation

[Section] What are SQL Foreign Keys?

SQL Foreign Keys are a column (or a collection of columns) in one table that refers to the Primary Key of another table. It is - Used to establish a link between two tables - Requirement: When declared, foreign key values must also be in the primary key values of the linked table

What are some key points about SQL?

Some key points about SQL include that it is - A domain-specific language, where (a) SQL only works on relational databases (b) Not for general purpose programming (Java, C/C++,...) - Pertains to the Logical level of interaction with data

What is the most common answer to the question, "How do we describe information?"

The most common answer to the question, "How do we describe information?" is by using: The Relational Model

Give examples of the Data Model Zoo.

There are lots of models out there! - Relational - Semi-structured - Key-value pairs - Graph - Object-oriented