Welcome to INF1343! Database Modeling and Database Design Yuri - - PowerPoint PPT Presentation

▶
welcome to inf1343
SMART_READER_LITE
LIVE PREVIEW

Welcome to INF1343! Database Modeling and Database Design Yuri - - PowerPoint PPT Presentation

Jan 05, 2023 479 likes •1.2k views

Welcome to INF1343! Database Modeling and Database Design Yuri Takhteyev University of Toronto January 3, 2011 This presentation is licensed under Creative Commons Attribution License, v. 3.0. To view a copy of this license, visit

slide-1
SLIDE 1

Welcome to INF1343!

Database Modeling and Database Design

This presentation is licensed under Creative Commons Attribution License, v. 3.0. To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0/. This presentation incorporates images from the Crystal Clear icon collection by Everaldo Coelho, available under LGPL from http://everaldo.com/crystal/.

Yuri Takhteyev

University of Toronto

January 3, 2011

slide-2
SLIDE 2

What is a “Database”?

“an organized collection of data” (digital, managed with software) “DBMS”

slide-3
SLIDE 3

Bob information Alice

slide-4
SLIDE 4

Bob information Alice

slide-5
SLIDE 5

Alice Bob an information system

slide-6
SLIDE 6

Alice Bob storing information + doing things with it

slide-7
SLIDE 7

Alice Bob application software database

slide-8
SLIDE 8

Alice Bob application software database “persistent storage”

slide-9
SLIDE 9

Alice Bob database “persistent storage”

slide-10
SLIDE 10

What is Data?

Data Information Knowledge

slide-11
SLIDE 11

What is Data?

Bytes Database Bits Records

slide-12
SLIDE 12

Create Read Update Delete

require a way

  • f finding the

record

slide-13
SLIDE 13

Database Elements

“logical” representation “physical” representation

slide-14
SLIDE 14

Databases Models

slide-15
SLIDE 15

Key-Value

Jedi Master, unknown species smuggler, human Yoda Han Solo

slide-16
SLIDE 16

Hierarchical

characters human wookiee unknown species Padmé Amidala Padmé Amidala Chewbacca Yoda Han Solo Obiwan Kenobi

slide-17
SLIDE 17

Network

human wookiee Chewbacca Yoda Han Solo Obiwan Kenobi Jedi Master

slide-18
SLIDE 18

Relational

A notion of a “relation”

not to be confused with a “relationship”

slide-19
SLIDE 19

A Relation

(Yoda, Jedi Master)

slide-20
SLIDE 20

A Relation

(Yoda, Jedi Master, unknown species)

slide-21
SLIDE 21

A Relation

(Yoda, Jedi Master, unknown species) (San Solo, smuggler, Human) (Padmé Amidala, queen, Human) (Jabba, crime lord, Hutt) (Jar Jar Binks, senator, Gungan)

slide-22
SLIDE 22

Another Relation

(Human, humanoid, 1.7 m) (Gungan, humanoid, 1.89 m) (Hutt, gastropod, 3.5 m) (Ewok, furry biped, 0.9 m)

slide-23
SLIDE 23

And Another

(humanoid, 2 legs) (gastropod, 0 legs)

slide-24
SLIDE 24

Tabular Form

Human humanoid 1.7 Hutt gastropod 3.5 Jabba Hutt Obiwan Kenobi Human gastropod humanoid 2 persona species_type species

slide-25
SLIDE 25

Tabular Form

Human humanoid 1.7 Hutt gastropod 3.5 Jabba Hutt Obiwan Kenobi Human gastropod humanoid 2 persona species_type species

slide-26
SLIDE 26

Relational Data Modeling

Finding a proper relational representation for data

slide-27
SLIDE 27

RDBMS

MySQL, PostgreSQL, Oracle, Sybase, IBM DB2, Informix, MS SQL Server, MS Access*

slide-28
SLIDE 28

Accessing a Database

slide-29
SLIDE 29

Built-in GUI

slide-30
SLIDE 30

GUI Client for a Remote Database

internet database client database server

slide-31
SLIDE 31

A 3-Tier System

web browser application server

slide-32
SLIDE 32

A Query Language

commands status, results

slide-33
SLIDE 33

A Query Language

commands

slide-34
SLIDE 34

A Query Language

commands telnet/SSH client

slide-35
SLIDE 35

Structured Query Language

slide-36
SLIDE 36

An SQL Statement

select name, occupation from persona where species=”Wookiee”;

slide-37
SLIDE 37

An SQL Statement

select name, occupation from persona where species=”Wookiee”;

  • SQL keywords are not case-sensitive (de facto)
  • text strings nearly always are
  • names or tables and fields usually are
slide-38
SLIDE 38

An SQL Statement

so: select = SELECT* = seLecT** from = FROM* = From**

* some people prefer this ** ugly, don't do this

but: persona != PERSONA != Persona ”Wookiee” != ”wookiee”

slide-39
SLIDE 39

Types of Statements

Data Manipulation

select, insert, update, delete

Data Definition

create, alter, drop

Data Control

grant, revoke

Transaction Control

commit, rollback

slide-40
SLIDE 40

This Course

http://bit.ly/inf1343

a shortcut for http://takhteyev.org/courses/11W/inf1343/

slide-41
SLIDE 41

Contact Information

Office hours:

  • Mon, 2-3pm, iSouth rm. 328

Email:

  • use UToronto mail
  • put “inf1343” in the subject line
  • expect 2 day turn-around
slide-42
SLIDE 42

“SQL” “RDD”

slide-43
SLIDE 43

Grading

project exam assignment 1 assignment 2

slide-44
SLIDE 44

Due Dates

in-class final exam final project report assignment 2 assignment 1

January February March Apr

3 10 17 24 31 7 14 21 28 7 14 21 28 4 preliminary project design

slide-45
SLIDE 45

Lecture Schedule

(See the syllabus)

slide-46
SLIDE 46

Questions?

slide-47
SLIDE 47

A Query Language

SQL telnet/SSH client

slide-48
SLIDE 48

Unix via SSH

  • 1. Using a local bash* terminal
  • 2. Using remote bash via SSH**
  • 3. Running mysql remotely via SSH
  • 4. Moving files back and forth

* Bash = “Bourne again shell”

(a somewhat updated version of the 1971 Thompson shell)

** SSH = “Secure shell”

(a secure version of the 1969 telnet)

slide-49
SLIDE 49

Local v Remote

Local: Your laptop / desktop Remote: Another computer you are using (via your “local” machine)

Hint: Check the name in the prompt, e.g.: yuri@chai:~$

slide-50
SLIDE 50

A Terminal App / Bash

OSX: “Terminal” (pre-installed) Linux: “gnome-terminal” (pre-installed) Windows: “git-bash” from Git http://code.google.com/p/msysgit/

(you can use PuTTY if you prefer)

slide-51
SLIDE 51

SSH

ssh kenobio7@yoda.ischool.utoronto.ca

  • your username is your UtorID
  • your password is your UtorID too
  • you will need to change your password

You will need to re-enter your original password before entering the new one. That is, the sequence is:

  • riginal, original again, new, new again.
slide-52
SLIDE 52

More Unix Commands

ls – list files in a directory cd – change directory mkdir – create (make) a directory rm – delete (“remove”) a file or directory cp – copy a file or directory less – view a text file nano – edit a text file mysql – start mysql client

some of those commands are available both in your local and remote bash, some just on the server

slide-53
SLIDE 53

Anatomy of the Unix Command

cp -r /play/yoda /tmp/yoda2

the command arguments

  • ptions (may have their own arguments)
slide-54
SLIDE 54

Some Examples

cd /play go to directory “/play” Hint: press [Tab] after typing “/pl” ls list the files in the current directory cd yoda go to directory “yoda” Hint: press [Tab] after typing “y” ls Hint: use [ ] for earlier commands ↑

slide-55
SLIDE 55

Some Examples

less force.txt Hint: press [Tab] after typing “f” Hint: press “q” to exit less cd .. go to up one level ls cd locked go to directory “sandbox” Hint: you don't have the permissions

slide-56
SLIDE 56

Some Examples

cd sandbox mkdir obiwan create a directory “obiwan” (use your own name) ls we should see everyone's directory cd obiwan go to your directory

slide-57
SLIDE 57

Some Examples

ls /play/yoda/ What was that file called again? less /play/yoda/force.txt Let's look at it again. cp /play/yoda/force.txt . copy “force.txt” to the local directory nano force.txt edit force.txt Hint: ^ means [Control]

slide-58
SLIDE 58

Options

ls -sh list files with file sizes cp -r /play/yoda . copy “recursively” less -N force.txt . show the file with line numbers

slide-59
SLIDE 59

Getting Help

man ls user manual for the ls command

slide-60
SLIDE 60

Directories

/home/kenobio7 user's “home” directory ~ alias for user's home directory e.g. “ls ~” . current directory .. parent of the current directory

slide-61
SLIDE 61

Redirection

command > file.txt write the output to file command < file.txt feed the content of file to the command command1 | comman2 send the output of command1 to command2 (We'll see examples in a second.)

slide-62
SLIDE 62

MySQL

mysql connect to mysql mysql -u username -p connect to mysql as a kenobio7, with a password

slide-63
SLIDE 63

MySQL Prompt

mysql> do not confuse with the bash prompt! Hint: type “exit” or ^C to exit. What do we enter at the mysql prompt?

slide-64
SLIDE 64

A Bit of SQL

use starwars; select name, occupation from persona where species=”Wookiee”;

slide-65
SLIDE 65

A Bit of SQL

mysql> use starwars; Database changed mysql> select name, occupation from persona where species="Wookiee"; +-----------+------------+ | name | occupation | +-----------+------------+ | Chewbacca | co-pilot | +-----------+------------+ 1 row in set (0.00 sec)

slide-66
SLIDE 66

SQL From a File

cd ~ cp /play/yoda/humans.sql . mysql < humans.sql

run mysql client feeding it the contents of “non-humans.sql”

mysql < humans.sql > h.txt

save the output into “h.txt” Exercise: create a file “ewoks.sql” that would give us a list of Ewoks.

slide-67
SLIDE 67

Using SCP

scp = secure copy (or ssh + cp) copy files over an ssh connection

Hint: You will usually be running this in your local bash session (i.e. on your laptop/desktop). Hint: Windows users can use WinSCP instead.

slide-68
SLIDE 68

Remove to Local

scp user@host:/remote/file /local/dir

e.g.:

scp kenobio7@yoda.ischool.utoronto.ca:~/humans.txt .

username host (server) remote file local directory

slide-69
SLIDE 69

Local to Remote

scp /local/file user@host:/remote/dir

e.g.:

scp ewoks.sql kenobio7@yoda.ischool.utoronto.ca:~/

slide-70
SLIDE 70

Editing Files Locally

Windows: Notepad++ Mac: TextWrangler Linux: gedit (or emacs, vi) Key feature: syntax highlighting

slide-71
SLIDE 71

Home Exercises

  • 1. Connect to the server.
  • 2. Connect to mysql database “starwars.”
  • 3. Find the droids (the species is “droid”).
  • 4. Write an SQL file for finding the droids.
  • 5. Scp the file to the server.
  • 6. Find the droids, saving the results to a

file (“droids.txt”).

  • 7. Scp droids.txt back to your laptop/desktop.