online exsam

3/11/2015

1

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; Ray Toal and Joh David N. Dioniso – The Javascript Programming Language; 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

Chapter 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

Electronic spreadsheets and database management systems

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

3

3/11/2015

2

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 _______________

– database _________________

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 _________ of the database contents

Database schema

A specification of the _______________ 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

________

3/11/2015

3

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

A Database Table

8

9 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

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)

9

3/11/2015

4

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

10

Figure 12.8 A database table containing customer data

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

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Relationships

11

12 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’

12

3/11/2015

5

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

CS1160 Intro to Programming

CS1150

Introduction to Computer Science

Queries in SQL

SELECT name, address FROM customer

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

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

13

14 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'

14