Transcript Database Management System

Chapter 12.3+
Information Systems
Database Management
Database Management Systems
Database: A structured set of data.
Database Management System: (DBMS) A
combination of software and data, including:



Physical database: a collection of files that
contain the data.
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.
2
Database Management Systems
Specialized database languages allow the user to:
specify the structure of data;
 add, modify, and delete data;
 query the database to retrieve specific
data.

3
Database Management Systems
4
Databases
Databases are a recent development in the
management of large amounts of data.
As paper file systems were “computerized”
each application was implemented separately
with its own data set.
These systems were riddled with both corrupt
data and redundant data, none of which
could be shared.
5
Databases
The integration of separate systems into one
database resolved these issues, but
introduced new ones.
With all data shared, control of access to the
data becomes a major concern.
Parking doesn’t need to see your grades.
6
Database Management Systems
A schema is a description of the entire
database structure used by the database
software to maintain the database.
A subschema is a description of only that part
of the database that is particular to a user’s
needs.
7
Database Management Systems
A layered approach hides the complexities of
database implementation.



User sees data in terms of the application.
The application “sees” data in terms of the
database model.
The DBMS “sees” data as it is organized.
8
Database Management Systems
Advantages of the layered approach include:




Simplification of the design process.
Better control of access.
Data Independence.
Applications can be written in terms of simple,
conceptual views of the data
– the database model.
9
Database Models
A database model is a conceptual view of how
to organize and manipulate data.
The most popular one is the Relational Model.
10
The Relational Model
In a relational DBMS, the data items - and the
relationships among them - are organized into
rectangular tables.
As with spreadsheets, these tables consist of
rows and columns.



Each table is called a relation.
The rows are called tuples.
The columns are called attributes.
11
The Relational Model
Of course, different authors adopt different
terms. There is a commonly used, alternate set
of names:

Relations are also called tables.

A tuple can be referred to as a record,
and in this terminology

a record is a collection of related fields.
12
AN EXAMPLE
A Sample Movie Table
14
A Database Table
We can express the schema for this table as follows:
Movie (ID:key, Title, Genre, Rating)
15
A Partial Customer Table
16
A Partial Customer Table
We can express the schema for this table as:
Customer (ID:key, Name, Address,
CreditCardNumber)
17
Relationships
A table can represent relationships between objects.
The Rents table relates Customers to the Movies they’ve
rented, by their respective IDs.
18
Relationships
We can also express the schema for a relationship:
Rents (CustomerId, MovieId, DateRented,
DateDue)
Note the absence of a key field.
19
RELATIONAL OPERATIONS
Relational Operations
There are 3 fundamental operations that can be used to
manipulate the tables in a database:

SELECT


PROJECT


Copies rows (tuples) from a table (relation)
Copies columns (attributes) from a table (relation)
JOIN

Combines 2 tables (relations) into 1
21
Relational Operations
The result of any relational operation is a new relation.
We can express these operations with a simple syntax.
PGmovies ← SELECT from Movie
where Rating = “PG”
This operation creates a new relation (PGmovies) by
copying all the rows from the Movie table that have a
Rating of PG.
22
Relational Operations: SELECT
The PGmovies relation.
23
Relational Operations
The same syntax can be used for the other operations.
PGtrimmed ← PROJECT MovieId, Title from
PGmovies
This operation creates a new relation that copies two
attributes from the PGmovies relation.
24
Relational Operations: PROJECT
The PGtrimmed relation.
25
Relational Operations
A JOIN creates a new relation by combining 2 relations
according to some criterion.
Rentals ← JOIN Customer and Rents
where Customer. Id = Rents.CustomerId
26
Relational Operations: JOIN
The Rentals relation.
27
Relational Operations: PROJECT
The PROJECT operation can be used to remove the
attributes we don’t want…
RentalsTrimmed ← PROJECT Name,
Address, MovieId from Rentals
28
Relational Operations
The RentalsTrimmed relation.
29
Relational Operations
Now, JOINing RentalsTrimmed to PGtrimmed …
PGrenters ← JOIN RentalsTrimmed and PGtrimmed
where RentalsTrimmed.MovieId = PGtrimmed .MovieId
…creates a table of customers who have rented PG movies.
30
STRUCTURED QUERY LANGUAGE
Structured Query Language
Structured Query Language (SQL)
• sometimes pronounced “SeQueL”
• a comprehensive language for managing
relational databases.
• Implements the basic dBase operations, but
with a different set of key words.
32
Structured Query Language
SQL includes commands to:
• create new databases
• create new tables
• insert/delete records
• update records
• create views
• set permissions on tables, procedures, and views
• execute queries
Queries in SQL - PROJECT
All SQL queries are created with SELECT.
SELECT attribute-list
FROM table
WHERE attribute meets condition
Conditional Operators:
=, <>, >, <, >=, <=,
BETWEEN, LIKE, IN
34
Queries in SQL - SELECT
SELECT *
FROM table
WHERE attribute meets condition
* is a wild card symbol.
It represents “all attributes”
which means “the whole record”.
35
Queries in SQL - JOIN
SELECT *
FROM table1
INNER JOIN table2
ON condition
The JOIN is performed first, then the SELECT.
Types of JOINs:
INNER, LEFT, RIGHT, FULL
36
Queries in SQL - Examples
select Title from MOVIE where Rating = 'PG'
select Name, Address from CUSTOMER
select * from MOVIE where Genre like '%action%'
select * from MOVIE where Rating = 'R' order by Title
37
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'
38
DATABASE DESIGN
Database Design
Entity-relationship (ER) modeling

A popular technique for designing relational
databases.
ER Diagram


Chief tool used for ER modeling.
Captures the important record types, attributes,
and relationships in a graphical form.
40
Database Design

These designations show the cardinality
constraint of the relationship
41