CS 1222 Using Relational Databases and SQL 1

CS 1222 Using Relational Databases and SQL 1. Introduction Daniel Firpo Slides prepared by Randy Moss Department of Computer Science California State University, Los Angeles

Introduction

What is a database? r A large, integrated collection of data m Bank m Company m E-commerce m Government m University m ………. 1. Introduction CS 1222 -Fall 2017 1 -3

Why use a database? r Efficient data management access r Concurrent access and crash recovery r Data administration r Data integrity and security r Data independence r Reduced application development time 1. Introduction CS 1222 -Fall 2017 1 -4

Database models r Physical model m How data is stored on a disk r Logical model m How data is organized m Examples • No. SQL • Key-value pair • Relational 1. Introduction CS 1222 -Fall 2017 1 -5

Relational model r Most popular relational DBMS m Oracle, Postgres, My. SQL, SQL Server, Access, and more r How do Relational DBs store data? m Tables! 1. Introduction CS 1222 -Fall 2017 1 -6

Relational Database Concepts: Table (Relation) Table: Titles Field (column, Attribute) Record (tuple, row) 1. Introduction CS 1222 -Fall 2017 1 -7

Relational Database Concepts r Candidate Key m Uniquely identify a record • No two rows have the same candidate key m Maybe multiple candidate keys in a table r Primary key m Select a candidate key as the primary key m Only one primary key in a table 1. Introduction CS 1222 -Fall 2017 1 -8

Relational Database Concepts r Candidate keys m title. ID, Title, UPC r Primary key m Any ONE of the candidate keys 1. Introduction CS 1222 -Fall 2017 1 -9

Data type r Determine what kind of data a field can store r Common data types m Text m Numeric • Byte integer, long integer, single, double, decimal • date/time, boolean • Special 1. Introduction CS 1222 -Fall 2017 1 -10

Schema r “Definition” of a database m Names of the tables m Attributes and attribute types in each table m Constraints on each tables m Dependencies between tables 1. Introduction CS 1222 -Fall 2017 1 -11

Introduction to SQL r Structured Query Language r Open ANSI standard m Supported by most databases m Some variation in implementation r A skill that is used by many people in many environments m Programmers m Database Administrators m Managers 1. Introduction CS 1222 -Fall 2017 1 -12

What Does SQL Do? r Views information from relational database m m Single or Multiple Tables Tools to Calculate and Summarize r Manipulates information in relational database m m Insert Records Update Records Delete Records Operates on entire recordset with single command r Defines relational database m Create Database, Tables, Primary and Foreign Keys 1. Introduction CS 1222 -Fall 2017 1 -13

SQL queries r Basic format: SELECT filed_name 1, field_name 2, … FROM table_name 1, tabel_name 2… [WHERE conditions] r If you select ALL attributes of a table SELECT * FROM table_name [WHERE conditions] 1. Introduction CS 1222 -Fall 2017 1 -14

SQL examples r List all the attributes of all titles SELECT * FROM Titles r List the title and upc of all titles SELECT title, upc FROM Titles r List the title and upc of the titles in the ‘metal’ genre SELECT title, upc FROM Titles WHERE genre = ‘metal’ 1. Introduction CS 1222 -Fall 2017 1 -15

Using My. SQL

2. Connect to the My. SQL server r $mysql –p m You will be asked to input the password. m The password is the original password assigned to you, not the password you just changed to. r Change your password mysql> set password=PASSWORD(“new password”); 1. Introduction CS 1222 -Fall 2017 1 -17

Run script files r You’ll use four script files m Lyric. sql: mostly for your labs m Books. sql: mostly for your homework m Orders. sql and Sailors. sql: mostly for exams m These four files are used to create tables in your database 1. Introduction CS 1222 -Fall 2017 1 -18

Some My. SQL commands r Help m ? or h or help; r Reconnect to the server m Connect; r Display sever info m Status r Quit My. SQL m q or quit; or exit; 1. Introduction CS 1222 -Fall 2017 1 -19

Some My. SQL commands r Show databases m Show databases; r use database m Use dbname; r Show tables m Show tables; r Show table schema m Desc tablename 1. Introduction CS 1222 -Fall 2017 1 -20