Transcript Course administration (Cont.) - csns

CS122 Using Relational Databases
and SQL
1.
Introduction
Huiping Guo
Department of Computer Science
California State University, Los Angeles
Course administration
 Lectures
 Lec: Monday
4:20 – 6:00pm, ET A210
 Lab: Wednesday 3:30 – 6:00pm, ET A210
 Instructor: Huiping Guo




Email: [email protected]
Phone: (323) 343-6673
Office: ET-A318
Office hours: M: 10:00 - 12:00 PM
W: 10:30 – 12:30
 Course webpage

www.calstatela.edu/faculty/hpguo/Teaching/Winter16/
CS122/CS122_W16.htm
1. Introduction
CS122_W16
1-2
Course administration (Cont.)
 Textbook
 “SQL Essentials” by Gary Randolph and Jeffrey
Griffin Franklin Beedle & Associates,
 Resources
 SQL Tutorial
• http://www.w3schools.com/sql/
• www.1KeyData.com
• http://www.xlinesoft.com/interactive_sql_tutorial/

MySQL Tutorial
• Lynda
• http://www.tutorialspoint.com/mysql/
• http://www.tizag.com/mysqlTutorial/
1. Introduction
CS122_W16
1-3
Course administration (Cont.)
 Grading policy






Homework (individual): (20%)
Lab(group): (15%)
In-class project(group): (10%)
Attendance (5%)
Midterm (20%): 02/10
Final exam (30%): Monday, 03/14, 4:30 – 7:00pm
 Group up!



Form groups of 2 students
Email me the name of your group partner ASAP! (one email/group)
Put CS122 group in the subject line
 Final letter grade
 A
90-100
A- 85-89
 B+
80-84
B 70-79
 C
60-69
NC < 60
1. Introduction
CS122_W16
1-4
Note:
 The grading policy is for all students in the class
 No extra homework
 No make up exams
 DO NOT take this class if you cannot attend the final
exam!
 You’ll be graded based ONLY on your
performance!

NOT on your request
1. Introduction
CS122_W16
1-5
Course administration (Cont.)
 Note:
 Assignment and lab submissions:
• Through CSNS
• Make sure your files are READABLE!
• Please submit your homework/lab before the due
time
• The submission button will be disabled after the due time

I don’t accept email submissions!
1. Introduction
CS122_W16
1-6
Course administration (Cont.)
 CSNS webpage
 http://csns.calstatela.edu/index.html




Every student enrolled in the class has an
account
both of your username and password are your CIN.
When you first sign in, you will be asked to choose a
different username and password.
If you already used the system in other classes, your
username and password are the same as before.
 Make sure you give the correct email address
1. Introduction
CS122_W16
1-7
Course administration (Cont.)
 CSNS: how to modify the uploaded file
 You cannot delete the uploaded file
 Modify the file and upload it with the same name + a version
number to CSNS
 How to know your grade
 You’ll receive an email if your email address in CSNS is
correct
 Log into CSNS to check
1. Introduction
CS122_W16
1-8
Course administration (Cont.)
 Important dates
 Thursday, Feb 18
Drop Deadline
 Academic integrity
 DON’T COPY OTHER’S WORK!
 DON’T GIVE YOUR SOLUTIONS TO ANYONE!
 If plagiarism is found, ALL parties involved will
get F
1. Introduction
CS122_W16
1-9
Other policies
 DO NOT talk in class during lectures
 After lecture, leave the classroom
 Students are not allowed to stay in the
classroom alone without the presence of the
instructor
 Please turn off your cell phone!
1. Introduction
CS122_W16
1-10
New teaching strategies
 Course Redesign With Technology
 Funded by the Chancellor’s Office
 The class will be partially flipped
 Some lectures will be recorded and the videos will be put
on the class website
 You’re SUPPOSDED to watch the video before attending
the class
 You need to work on some projects in class
 Please take this survey
 http://csns.calstatela.edu/department/cs/survey/respon
se/edit?surveyId=5275336
1. Introduction
CS122_W16
1-11
Introduction
What is a database?
 A large, integrated collection of data
 Bank
 Company
 E-commerce
 Government
 University
 ……….
1. Introduction
CS122_W16
1-13
Why use a database?
 Efficient data management access
 Concurrent access and crash recovery
 Data integrity and security
 Data administration
 Data independence
 Reduced application development time
1. Introduction
CS122_W16
1-14
Database models
 Physical model
 How data is stored on a disk
 Logical model
 How data is organized
 Three models
• Hierarchical
• Network
• Relational
1. Introduction
CS122_W16
1-15
Relational model
 Most popular relational DBMS
Oracle, Sybase, MySQL, SQL Server, Access
 In this model, a database is a collection of tables
 The tables are related to each other

TitleID
1
3
4
5
6
7
Title
Meet the Neurotics
Smell the Glove
Time Flies
Neurotic Sequel
Sonatas
Louis at the Keys
1. Introduction
UPC
2727366627
1283772282
1882344222
2828830202
3999320021
3838227111
Genre
alternative
metal
alternative
alternative
classical
jazz
CS122_W16
1-16
Relational Database Concepts:
Table
Table
(Relation)
Field
(column,
Attribute)
Table: Titles
TitleID
1
3
4
Record (tuple, row)
5
6
7
Title
Meet the Neurotics
Smell the Glove
Time Flies
Neurotic Sequel
Sonatas
Louis at the Keys
1. Introduction
UPC
2727366627
1283772282
1882344222
2828830202
3999320021
3838227111
CS122_W16
Genre
alternative
metal
alternative
alternative
classical
jazz
1-17
Relational Database Concepts
 Candidate Key
 Also called keys
 Uniquely identify a record
• No two rows have the same candidate key
 Maybe
multiple candidate keys in a table
 Primary key
 Select a candidate key as the primary key
 Only one primary key in a table
1. Introduction
CS122_W16
1-18
Relational Database Concepts
TitleID
1
3
4
5
6
7
Title
Meet the Neurotics
Smell the Glove
Time Flies
Neurotic Sequel
Sonatas
Louis at the Keys
UPC
2727366627
1283772282
1882344222
2828830202
3999320021
3838227111
Genre
alternative
metal
alternative
alternative
classical
jazz
 Candidate keys
 titleID, Title, UPC
 Primary key
 Any ONE of the candidate keys
1. Introduction
CS122_W16
1-19
Data type
 Determine what kind of data a field can
store
 Common data types
Text
 Numeric

• Byte integer, long integer, single, double, decimal
• date/time, boolean
• Special
1. Introduction
CS122_W16
1-20
Schema
 “Definition” of a database
 Names of the tables
 Attributes and attribute types in each table
 Constraints on each tables
 Dependencies between tables
 To be covered in CS422
1. Introduction
CS122_W16
1-21
Introduction to SQL
 Structured Query Language
 Open ANSI standard
 Supported by most databases
 Some variation in implementation
 A skill that is used by many people in many
environments
Programmers
 Database Administrators
 Managers

1. Introduction
CS122_W16
1-22
What Does SQL Do?
 Views information from relational database
 Single or Multiple Tables
 Tools to Calculate and Summarize
 Manipulates information in relational database
 Insert Records
 Update Records
 Delete Records
 Operates on entire recordset with single command
 Defines relational database
 Create Database, Tables, Primary and Foreign Keys
1. Introduction
CS122_W16
1-23
SQL queries
 Basic format:
SELECT filed_name1, field_name2,…
FROM
table_name1, tabel_name2…
[WHERE conditions]
 If you select ALL attributes of a table
SELECT *
FROM
table_name
[WHERE conditions]
1. Introduction
CS122_W16
1-24
SQL examples
 List all the attributes of all titles
SECLECT *
FROM Titles
 List the title and upc of all titles
SECLECT title, upc
FROM Titles
 List the title and upc of the titles in the
‘metal’ genre
SECLECT title, upc
FROM Titles
WHERE genre = ‘metal’
1. Introduction
CS122_W16
1-25
Using MySQL
Introduction to MySQL
 MySQL is a free client-server DBMS
 MySQL Sever
 cs1.calstatela.edu
 MySQL client

Graphical User Interface tool
• MySQL Workbench, MYSQL client…

Command line
• Connect to the server directly under the command
window
• We’ll use SSH Secure Shell Client
1. Introduction
CS122_W16
1-27
Use MySQL on the CS server
Log into the CS server using SSH
2. Connect to the MySQL server
3. Play with MySQL
1.
1. Introduction
CS122_W16
1-28
1. Log into the cs sever
 Use SSH to log in
 Launch SSH Secure Shell Client
 Click Quick Connect
 Type the following info
• Host name: cs1.calstatela.edu
• User name: your assigned user id
• you’ll be asked to input password
– Password is case sensitive
1. Introduction
CS122_W16
1-29
1. Log into the cs sever
 After logging in, change your password



The password is for the cs server!
You need to input the current password
The password you typed won’t show up!
1. Introduction
CS122_W16
1-30
2. Connect to the MySQL server
 $mysql –p
 You will be asked to input the password.
 The password is the original password assigned to you,
not the password you just changed to.
 The password you type DOESN’T show up
1. Introduction
CS122_W16
1-31
2. Connect to the MySQL server
 Change your password
mysql> SET PASSWORD = PASSWORD (‘ your new password');
Note:You had better set the same password for the CS server and
the MySQL server
1. Introduction
CS122_W16
1-32
Choose the database
 Each student has only one database
 The name of the database is the same as your
username
 You don’t have the privilege to create new
databases
 The database doesn’t contain any tables
1. Introduction
CS122_W16
1-33
Choose the database
 After connected to MySQL sever, you need to choose the
database

Mysql> Use database_name;
1. Introduction
CS122_W16
1-34
Transfer the script files to the
server
 You’ll use two script files
 Lyric.sql: for your labs and in-class projects
 Books.sql: for your homework
 The two files are used to create tables in your
database
 You need to transfer the two files to the cs
server before using the files to create the
tables
1. Introduction
CS122_W16
1-35
Transfer the script files to the
server (Cont.)
 How?
1. Go to the course web page
 Find the two files: Lyric.sql, Books.sql
 Right click a file, chose “Save Links As”
 Choose any folder you want folder, say c:\temp
 You need to memorize the folder name
1. Introduction
CS122_W16
1-36
Transfer the script files to the
server (Cont.)
2.
Launch SSH Secure File Transfer Client
click
1. Introduction
CS122_W16
1-37
Transfer the script files to the
server (Cont.)
3.
On the top-left window, choose the folder
where the two files are
1. Introduction
CS122_W16
1-38
Transfer the script files to the
server (Cont.)
4.
Drag files to the top-right window
1. Introduction
CS122_W16
1-39
Run script files
 Go back to the secure shell client window
 Command
 mysql>use hpguo;
 mysql> source lyric.sql;
1. Introduction
CS122_W16
1-40
Run script files
 To do assignments
 Use the same database
 Run Books.sql
 All tables in Lyric will be removed.
 You need to run lyric.sql again to do labs or in-class
projects.
1. Introduction
CS122_W16
1-41
Some MySQL commands
 Help

? or \h or help;
 Reconnect to the server
 Connect;
 Display sever info
 Status
 Quite MySQL
 \q or quit; or exit;
1. Introduction
CS122_W16
1-42
Some MySQL commands
 Show databases
 Show databases;
 use database
 Use dbname;
 Show tables

Show tables;
 Show table schema
 Desc tablename;
1. Introduction
CS122_W16
1-43
Exercise

Design the following queries:
1.
2.
3.
Find all the information of sales person
Find the name and web address of all artists
Find the names of all artists who are in
Canada
1. Introduction
CS122_W16
1-44