online exsam

1 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Module 9 – Databases

Instructor: Kera Watkins 479 Joshi Research Center 937-775-5138 [email protected]

CS1150 Intro to Computer Science Sources: Jones & Bartlett Learning; Nell Dale and John Lewis – Computer Science Illuminated; Karen Myers – Wright State University; Vance Saunders – Wright State University

2 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Goals

l Describe the elements of a database management system

l Describe the organization of a relational database

l Establish relationships among elements in a database

l Write basic SQL statements

2

3 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Managing Information

Information system

Software that helps the user organize and analyze data

Database management systems

Software tools that allow the user to organize, manage, and analyze data in various ways

3

4 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Management Systems (DBMS)

Database

A structured set of data

Database management system (DBMS)

A combination of software and data, made up of – a physical database

– a database engine

– database schema

Physical database

A collection of files that contain the data

4

5 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Management Systems (DBMS)

Database engine

Software that supports access to and modification of the database contents

Database schema

A specification of the logical structure of the data stored in the database

Database query

A request to retrieve data from a database

5

6 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Management Systems

6

________

7 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

The Relational Model

Relational DBMS

A DBMS in which the data items and the relationships among them are organized into tables

Tables

A collection of records

Records (object, entity)

A collection of related fields that make up a single database entry

Fields (attributes)

A single value in a database record

7

8 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Table

102 Back to the Future

comedy adventure

PG

• Record • Database Object • Entity

9 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Table: Movie

9

10 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

A Database Table Key

Key

One or more fields of a database record that uniquely identifies it among all other records in the table

We can express the schema for this part of the database as follows:

Movie (MovieId:key, Title, Genre, Rating)

In general:

TableName (colHdr_1, colHdr_2, colHdr_3, …, colHdr_i:key, …, colHdr_m)

11 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

A Database Table: Customer Table

12 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Relationships

Customer Table

Movie Rental Table

13 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Database Table: Movie

14 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Structured Query Language

Structured Query Language (SQL)

A comprehensive relational database language for data manipulation and queries

SELECT attribute-list FROM table-list WHERE condition

name of field name of table value restriction

SELECT title FROM movie WHERE rating = 'PG'

Result is a table containing all PG movies in table “movie”

15 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Table Challenge Queries in SQL 1

l SELECT name, address FROM customer

– English: Provide the fields from the name and address columns for each record from the customer table.

Result Table

Draw the Result Table

16 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Table Challenge: Queries in SQL 2

l SELECT * FROM movie WHERE genre LIKE '%action%‘

– English: Provide each record from the movie table where the genre column contains “action”. Circle the Records for the Result Table

17 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Table Challenge: Queries in SQL 3

l SELECT * FROM movie WHERE rating = ‘PG-13' ORDER BY title

– English: Provide each record from the movie table where the rating is “PG-13”. List them in alphabetical order based on the title.

Number the Rows for the Result Table

18 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Table Extra Challenge – 10 pts quiz: What is the SQL Statement?

MovieId Title Genre Rating

5022 Elizabeth drama period R

104 Field of Dreams fantasy drama PG

7442 Platoon action drama war R

Result Table

19 Wright State University, Department of Computer Science Dr. Kera Z. Watkins, Computer Science & Engineering

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Modifying Database Content

INSERT INTO customer VALUES (9876, 'John Smith', '602 Greenbriar Court', '2938 3212 3402 0299')

UPDATE movie SET genre = 'thriller drama' WHERE title = 'Unbreakable‘

DELETE FROM movie WHERE rating = 'R'