Database Technology Teaching Material For Learn

Database Technology

1. CSC 453 Database Technologies
Lecture 1
Eric J. Schwabe
College of CDM, DePaul University
Spring 2014

2. Tonight:
Course Administration
Introduction to Databases
The Relational Model
Introduction to SQL

SQLDeveloper

SQL DDL

SQL Queries
2

3. Course Administration
Course web site: http://d2l.depaul.edu/
Office hours: Tuesdays and Thursdays,
3:30pm-5:00pm
Email: [email protected]

Please begin subject line with “CSC 453”

Expect reply within one business day
Prerequisite: CSC 403 (CSC 383 or CSC 393
will do, but not CSC 451…)
3

4. Course Administration
Grading: 40% homework, 30% midterm
exam (5/8), 30% final exam (6/12)
No late HW’s accepted, but I will drop the
lowest HW score
Please shut off electronic devices during class
Read DePaul’s Academic Integrity Policy
4

5. Required: Fundamentals of Database
Systems (sixth edition), Elmasri and Navathe

Be sure to purchase ISBN 0136086209
Additional references (library.depaul.edu):

Oracle Database 10g SQL (Price)

Oracle Database 10g PL/SQL Programming
(Urman/Hardman/McLaughlin)
Other readings will be posted as needed…
Course Text
5

6. What is a database?
Data is information that can be recorded and
has a known meaning
A database is an organized collection of
logically related data

Persistent: is stored on a stable medium

Shared: has multiple uses and interested users

Interrelated: forms a bigger picture
6

7. Database Sizes
Small, personal databases: megabytes (10
6
)

Typically a single workstation, single user
Enterprise databases: gigabytes to terabytes
(10
9
to 10
12
)

Typically dedicated servers, many users
Data warehouses: petabytes (10
15
)

Multiple integrated data sources, many users
7

8. DBMS
A database management system (DBMS) is a
collection of software components that lets
you

create (define, construct)

maintain (update, modify, enforce constraints in)

control access to (secure, allow concurrency in)
a database
8

9. DBMS
DBMS Examples: Oracle, IBM DB2, MS
Access/SQL Server, MySQL
We can work with a DBMS directly or
through an application that supplies a
particular interface (which could be very
general or quite restrictive)
The database and DBMS together make up a
database system
9

10. Why would you use a
database?
Early data processing systems used files of
data in text form
Problem: program-data dependence led to

limited data sharing

duplication of data

excessive time for development and maintenance
10

11. Benefits of Database Systems
A database system uses a single repository of
data accessed by multiple users

Contains information on the structure of the data

Supports different views of the data

Allows sharing of and concurrent access to data
The costs are higher overhead for the design,
implementation, and maintenance of the data
What are the benefits?
11

12. Benefits of Database Systems
Program-data independence
Controlled data redundancy
Controlled access to data
Support for multiple user interfaces
More efficient query processing
Faster application development
12

13. What does a database look
like?
Models have evolved…
File Processing Systems
Hierarchical Model
Network Model
Relational Model
Later Models
13

14. File Processing Systems
Data stored in simple text files, each one
possibly having a different fixed
organization of the data
High level of program-data dependence
Does not facilitate the sharing of data
Difficult to optimize queries/transactions
14

15. Hierarchical Model
Hierarchical Model: Data arranged in
“parent-child” (“one-to-many”) relationships
e.g.: A company has many departments; a
department has many employees; an employee
has many dependents; et cetera
Structure can be too restrictive: e.g., a student
takes many courses, but each course has many
students!
15

16. Network Model
Network Model: Can represent more general
relationships (“many-to-many”) among types
of data
Both models share some weaknesses:

Applications have to navigate relationships
explicitly

The DBMS can not rearrange stored records to
optimize queries
16

17. Relational Model
First model to separate the logical structure of
the database from its physical structure
Data are divided into two-dimensional tables
called relations
Relationships between tables are given by shared
keys
Rules exist for dividing data among tables
A standardized query language (SQL) exists
17

18. Later Models
Object and Object-Relational: Add support for
structured data types (often used in
multimedia and geographic information
systems)
Multidimensional: Allows data to be
categorized by many different attributes (often
used in data warehousing)
Not as common as the Relational Model
18

19. Three-Schema Architecture
Internal schema: How data is stored in memory

Logical schema: abstract organization of data

Physical schema: arrangement of data on disk
Conceptual schema: Abstract description of the
different types of data

Entities, relationships, constraints
External schema: A user’s view of the data

Could be different interfaces for different users
19

20. Schema vs. Snapshot
The schemas describe the structure of the
database, but do not tell us what is in it

Creating the schemas is defining the database
The particular data stored in the database at
some point in time make up a snapshot (or
database state)

Putting in the data is populating the database
20

21. How is a database made?
Requirements Analysis

Document business rules, determine entities,
relationships, constraints needed
Conceptual Design

Generate conceptual schema
Logical Design √

Generate logical schema
21

22. How is a database made?
Physical Design √

Generate physical schema, optimize query
efficiency
Implementation √

Use DBMS to define and populate database
(Analysis and Design of Applications that use the
database is done in parallel, and is used to inform
Physical Design)
22

23. What are we going to do?
The Relational Model
Structured Query Language (SQL)
Database Design and Normalization
Database Programming
Storage and Indexes
Other Database Models
23

24. The Relational Model
First introduced by E. F. Codd in 1970
A database is made up of two-dimensional
tables called relations, each representing
some type of entity recorded in the database
Each relation has a name, describing what
entity it represents
24

25. The Relational Model
Each row of a relation is a tuple (or record),
representing one instance of that entity
Each column of a relation is an attribute for which
each tuple has a value assigned

Each attribute has a domain from which its values are taken

A domain consists of atomic values – no multi-valued or
divisible attributes are allowed

Missing/unknown values are indicated by NULL
25

26. Typical Domains
Numerical values

integers, fixed-point values, floating-point values
Character strings

fixed-length or variable-length
Dates and times
We can also enforce constraints on values

ranges, upper/lower bounds, allowed/forbidden
values, no NULLs, et cetera
26

27. Properties of a Relation
1. Each relation has a unique name (in database)
2. Each attribute has a unique name (in relation)
3. Each entry of a relation contains a single
value from its attribute’s domain
4. The order of the records does not matter
5. The order of the attributes does not matter
6. No two records in a relation are identical
27

28. Relation Schema
A relation schema of consists of the name of a
relation followed by a list of its attributes

e.g., STUDENT(ID, FirstName, LastName)
Each attribute must have its domain specified

e.g., ID is a seven-digit number, FirstName and
LastName are both strings of length at most 20
Can also be represented graphically as a row
of rectangles, one for each attribute
28

29. Relation
A relation (or relation state) for a given
schema is a set of tuples, where each tuple
contains for each attribute either an element
from its domain or the value NULL

e.g., { (1234567, ‘Eric’, ‘Schwabe’),
(9999999, ‘Amber’, ‘Settle’) }
Usually represented as a table, where rows are
tuples and columns are attributes
29

30. NULL
For some attributes, NULL can be used in
place of a value from the domain
NULL can have several meanings:

The value is unknown

A value exists, but is not currently available

The attribute does not apply to that tuple
Preferable to avoid them if possible…
30

31. Primary Keys
A set of attributes in the relation may be
defined to be the primary key – it must be a
minimal set of attributes that uniquely
identifies each tuple in the relation
NULL values are not allowed in the primary
key of a relation (“entity integrity”)
The primary key is underlined in the schema
How do we construct one?
31

32. Constructing a Primary Key
Consider those sets of attributes where their
values are sufficient to determine the values
of all other attributes (these are superkeys)
If no attribute can be removed from a
superkey while still preserving this property,
then it is a candidate key
Some candidate key is chosen to be the
primary key (if only one, may just call it key)
32

33. Foreign Keys
We link a pair of relations using a shared key
that is the primary key in one of them
In the referencing relation, this key is called a
foreign key (schema includes arrow to
primary key)
The foreign key associates each tuple in the
referencing relation with exactly one tuple in
the referenced relation
33

34. Referential Integrity

In each tuple, every foreign key must have a value
that is the value of the primary key in some tuple
in the referenced relation
This restricts the changes that can be made:

Can only insert or update a tuple if the value of every
foreign key in the tuple appears among the values of the
primary key that it references

Can only delete or update a tuple if the value of its
primary key does not appear among the values of any of
the foreign keys that reference it
34

35. Constraints
These will be maintained by the DBMS:

Domain constraints: All values of each attribute
must come from the specified domain

Entity integrity: No attribute in a primary key
can contain a NULL value

Referential integrity: Every foreign key value
must match the value of the primary key in some
tuple of the table that it references
35

36. Relational Database Schema
Collection of relation schemas, each including:

Name of relation

Attributes listed in parentheses (or in rectangles)

Primary key is underlined (solid)

Arrows from foreign keys to linked primary keys
36

37. SQL
Structured Query Language (SQL) is the
industry standard for relational databases
All major DBMSs support some version of
SQL
Statements can be issued interactively,
batched in script files, or embedded in a
program in general-purpose programming
language (e.g., Java, C++)
37

38. Classes of SQL Commands
Data Definition Language (DDL)

Create schemas, tables, constraints, views
Data Manipulation Language (DML)

Modify and update tables, retrieve information
Data Control Language (DCL)

Grant and revoke access to parts of database
Most users will only have access to the DML
– we will use both the DDL and the DML
38

39. Using SQLDeveloper
First, you must set up a new connection

cdmoracledb.cti.depaul.edu (140.192.30.211)

use your CSTCIS username, with initial
password cdm####### (use 7-digit Student ID)

To change password, ALTER USER username
IDENTIFIED BY newpassword ; (…Execute)
Double-click to open an existing connection
Be sure to disconnect when you are done!
39

40. Running SQL Commands
Single SQL command

Type on one line in center window, Execute (Ctrl-Enter)
Script (Sequence of SQL commands stored in a file)

Type @X:\Path\File.sql in center window, Run Script (F5)
Any output will appear in bottom window under
Query Result or Script Output
Click on Save icon to save output window contents
to a text file (Click Clear icon first)
40

41. Viewing Tables
Left window shows current Tables, click on
+ to expand list
Right-click on Tables and choose Refresh to
see changes (can also Commit changes to disk)
Double-click on a table to open it in the center
window

COLUMNS tab shows schema

DATA tab shows contents
41

42. Working with SQL Scripts
Create file scriptname.sql in Notepad

Be sure to use .sql extension!
To add comments:

-- for a single-line comment

/* ... */ for a multi-line comment
Output full contents of table with
SELECT * FROM TABLE_NAME ;
42

43. Creating a Table
CREATE TABLE TABLE_NAME
( Attribute1 DOMAIN_1 ,
Attribute2 DOMAIN_2 ,
...
AttributeN DOMAIN_N );
You can list as many attributes and domains
as you want, separated by commas
43

44. SQL Domains
Numerical domains:

NUMBER: A general floating-point number
(also REAL, FLOAT)

NUMBER(*, 0): A general integer
(also INTEGER, INT)

NUMBER(n): An n-digit integer

NUMBER(x, y): A fixed-precision number with
x total digits, and y digits to the right of the
decimal point (also DECIMAL, NUMERIC)
44

45. SQL Domains
String domains:

CHAR(n): A fixed-length string of n characters

VARCHAR(m) or VARCHAR2(m): A variable-
length string of up to m characters
Dates:

DATE: A date in ‘dd-mon-yy’ format (dd = day,
mon = month, yy = year)

(There are often variations in the date format in
different SQL implementations…)
45

46. Removing Tables
To remove a table:
DROP TABLE TABLE_NAME ;
(Should do this at the beginning of every
script before creating tables to avoid
conflicts with existing tables…)
Remove referencing tables before referenced
tables, or use CASCADE CONSTRAINTS
46

47. Populating a Table
To insert a record into a table:
INSERT INTO TABLE_NAME
VALUES ( value1, value2, value3, ... );
Values of attributes must be given in the same
order as in the schema
Will generate an error if any constraints are
violated (domain constraints, entity integrity,
referential integrity, user-defined constraints)
47

48. Populating a Table (continued)
To insert a record that specifies only some of
the attributes:
INSERT INTO TABLE_NAME(Attr1,Attr2,...)
VALUES ( value1, value2, ... );
Missing attributes will be filled in with NULL
unless default values have been specified
48

49. Defaults and Constraints
After attribute and domain, before comma:

Add default value for the attribute with
DEFAULT value

Disallow NULL values with NOT NULL

Force values to be unique with UNIQUE

Impose other constraints with CHECK (condition)
e.g., CHECK (low <= Attribute AND Attribute <= high)
Applied whenever a tuple is added/modified
49

50. Creating Primary/Foreign Keys
Set up primary and foreign keys using
CONSTRAINT clauses within CREATE, after all
attributes have been specified

CONSTRAINT PKName
PRIMARY KEY ( Att1, Att2, ... )

CONSTRAINT FKName
FOREIGN KEY ( Att1, Att2, … )
REFERENCES TABLE_NAME ( Att1, Att2, ... )
Separate all clauses with commas
50

51. UPDATE
UPDATE TABLE_NAME
SET Attribute = value
WHERE condition;
Sets Attribute to value in exactly those tuples
that satisfy condition
51

52. DELETE
DELETE FROM TABLE_NAME
WHERE condition;
Removes from the table exactly those tuples
that satisfy condition
52

53. Displaying Table Contents
SELECT * FROM TABLE_NAME ;
This will display the entire contents of the
table TABLE_NAME (all rows and columns)
This is an example of a very simple query…
53

54. SQL Queries
General form of a query:
SELECT list of attributes to report
FROM list of tables
[WHERE tuple condition]
[GROUP BY list of grouping attributes]
[HAVING group condition]
[ORDER BY list of ordering attributes] ;
Result is an ordered set of ordered tuples
54

55. SELECT list of attributes
FROM list of tables
SELECT gives which attributes to include

give a single attribute, or a list (rename with AS)

* for all attributes

DISTINCT will only report distinct tuples
FROM gives the table(s) to get tuples from

for now, just a single table
SELECT FROM
55

56. WHERE
WHERE condition
Each tuple is tested against the condition, and
only those that satisfy it are returned by the query
Condition expression can contain:

comparisons

expressions with wildcards (for strings)

boolean operations
56

57. Comparisons
Put numerical or string value on each side, each
comparison returns true or false
=is equal to
!= or <>is not equal to
>is greater than
>= is greater than or equal to
<is less than
<= is less than or equal to
57

58. Wildcards
Using LIKE, we can compare character strings to
strings that include wildcard characters that
match anything:
_ matches any single character
% matches any consecutive set of characters
For example:

‘b_d’ will match ‘bad’, ‘bed’, but not ‘band’

‘bat%’ will match ‘bat’, ‘bath’, ‘battery’…
58

59. Boolean Operators
Simple conditions can be combined into more
complicated conditions

X AND Y is satisfied by a tuple if and only if
both X and Y are satisfied by it

X OR Y is satisfied by a tuple if and only if at
least one of X and Y is satisfied by it

NOT X is satisfied by a tuple if and only if X is
not satisfied by it
59

60. Three-Valued Logic
Any comparison involving NULL values will
yield a result of UNKNOWN

Don’t use = to check for NULLs, as all NULLs
are distinct; use IS NULL instead

There are extended definitions of AND, OR, and
NOT that include UNKNOWN
An end result of UNKNOWN does not satisfy
a WHERE (only TRUE does!)
60

61. ORDER BY
ORDER BY list of ordering attributes
Tuples returned by the query are sorted by
the first attribute in the list
Ties are broken by the second attribute, then
the third, et cetera
Tuples are sorted in ascending order unless
we put DESC after an attribute
61

62. Next Time:
More SQL

Finish basic queries

Joins

Views

Nested queries
(Assignment 1 will be posted after class, due 4/11…)
62