Relational Databases

1
Relational Databases
2
Why Databases

• As programs get larger, data becomes more
  important
• Data is entities and relationships
• Real-world processes involve complex data
  interaction

3
Structure of a Database

• Record one discreet entity
• Field one piece of data
• Table series of like records

4
Record

• Represents some entity
• Consists of many details
• All details refer to same record

5
Field

• Specific data elements
• Has a name, length, type, value
• Sometimes length is assumed
• Each record has same field definitions, but
  different values

6
Table

• A series of records following the same definition
• All records should describe same kind of thing
• Each record is a different instance of that thing

7
Database

• A series of tables each defining different
  entities
• The relationships between those entities
• Ancillary files, indices, views

8
SQL

• Structured Query Language
• Uniform system for defining, querying data
• Used by many programming languages and db tools

9
Creating a table

• Create statement
• Include all field names and types

10
Creating a phone list table

• create table phonelist(
• id INTEGER PRIMARY KEY,
• firstname VARCHAR(15),
• lastname VARCHAR(15),
• email VARCHAR(20),
• phone VARCHAR(15)
• )

11
Details

• PRIMARY KEY is special inded
• VARCHAR is string
• Each VARCHAR must have a length
• Separate values with commas

12
Varchar

• variable length string
• String length is still legislated
• Trailing spaces automatically truncated

13
Inserting Data

• INSERT INTO phonelist VALUES(
• 0, 'Andy', 'Harris',
• 'aharris_at_cs.iupui.edu',
• '123-4567'
• )

14
Adding data to the phone list

• INSERT statement
• Requires table name
• List of values
• Text values in single quotes
• Separated by commas

15
Returning Data

• SELECT FROM Hero

16
Getting individual fields

• SELECT
• name, power
• FROM hero

17
Getting individual records

• SELECT name
• FROM hero
• WHERE transportation Binary Cow

18
Notes on WHERE clause

• Uses condition
• Single equals sign
• Text values in single quotes
• Multiple conditions OK

19
Variations on SELECT

• ORDER BY
• LIKE
• VIEW
• COUNT()
• MAX()

20
Relational Data Structures

• System for modeling more complex data
• Multiple entities
• Complex Relationships

21
Problems with BadSpy

• What is operation Dancing Elephant about?
• Which agents are in lower volta?
• Who knows sabatoge?
• If Rahab is deleted, what happens to Operation
  Raging Dandelion?

22
Goals of Normalization

• Eliminate Listed Fields
• Eliminate Redundancy
• Ensure Functional Dependency

23
Viewing the Entities
24
Relationship types

• One to one
• One to many
• Many to many

25
Isolating the Agent information

• Provide primary key
• Eliminate fields not directly related to agent
• Provide Foreign Key Reference to Operation

26
Creating Agent

• CREATE TABLE agent (
• agentID INTEGER PRIMARY KEY,
• name varchar (50),
• operationID INTEGER
• )

27
Isolating the Operation

• All data related to the operation
• Nothing not related to the operation

28
Creating the Operation

• CREATE TABLE operation (
• operationID INTEGER PRIMARY KEY,
• name varchar (50),
• description varchar (50),
• location varchar (50)
• )

29
Modeling One to Many

• Agent-operation is many to one
• Place reference to many in one table
• Agent table has reference to primary key of
  operation table
• Foreign key

30
Joining agent and operation

• SELECT
• agent.name as 'agent',
• operation.name as 'operation'
• FROM agent, operation
• WHERE agent.operationID operation.operationID

31
Building a View

• CREATE VIEW agentOpView AS
• SELECT
• agent.name as 'agent',
• operation.name as 'operation'
• FROM agent, operation
• WHERE agent.operationID operation.operationID

32
Using the View

• SELECT
• FROM agentOpView

33
(No Transcript)
34
Viewing the Entities
35
More Complete ER
36
Data Normalization

• A technique for improving data design
• Several normal forms
• Each improves on the last

37
First Normal Form

• Eliminate listed fields
• Make a new table to handle listed data
• Will probably require link table

38
Second normal form

• Eliminate Repeated Data
• If anything is typed in more than one place, take
  it out
• Make a new table
• Make a foreign key reference to that table

39
Third Normal Form

• Ensure Functional Dependency
• Look over all tables
• Ensure each field relates to the record
• Make other tables as necessary