Transcript Relational Database Concepts

Relational Database Concepts
8-Jul-15
db.ppt
relational database example
•contain tables
•tables contain records (rows)
•records are broken into
columns (fields)
8-Jul-15
PK_ID
Quote
FK_Sources
1
I don’t like that man; I must get to know
him better.
4
2
I wish I had an answer to that because
I'm tired of answering that question.
1
3
Right is right, even if everyone is against
it, and wrong is wrong, even if everyone
is for it.
3
4
People are just as happy as they make
up their minds to be.
4
db.ppt
first design for the MyQuotes database
Quotes table:
PK_ID
8-Jul-15
QuoteBody
1
I don’t like that man; I
must get to know him
better.
2
I wish I had an answer to
that because I'm tired of
answering that question.
QuoteSource
Abraham
Lincoln
Yogi Berra
3
Right is right, even if
everyone is against it, and
wrong is wrong, even if
everyone is for it.
William Penn
4
People are just as happy as
they make up their minds
to be.
Abe Lincoln
db.ppt
•Are records
1 and 4 from
the same
source?
a better design for the MyQuotes database
Quotes1 table:
PK_ID
1
Quote
FK_Sources
I don’t like that man; I
must get to know him
better.
4
2
I wish I had an answer to
that because I'm tired of
answering that question.
1
3
Right is right, even if
everyone is against it, and
wrong is wrong, even if
everyone is for it.
3
4
People are just as happy as
they make up their minds
to be.
4
Sources1 table:
PK_SourceID
Source
1
Yogi Berra
2
Mark
Twain
3
William
Penn
4
Abraham
Lincoln
SELECT Quotes1.QuoteBody, Quotes1.FK_SourceID, Sources1.PK_SourceID, Sources1.SourceBody
FROM Quotes1, Sources1
WHERE Quotes1.FK_SourceID=Sources1.PK_SourceID
8-Jul-15
db.ppt
“inner join” SQL statement
SELECT Quotes1.QuoteBody, Quotes1.FK_SourceID, Sources1.PK_SourceID, Sources1.SourceBody
FROM Quotes1, Sources1
WHERE Quotes1.FK_SourceID=Sources1.PK_SourceID
theAnswer = myResultSet.getString(1) +
" " + myResultSet.getString(4);
“join” because it gets data from two different tables
“inner” because each table only returns matching records
8-Jul-15
db.ppt
tables represent “relationships”
• one to one relationship (1 table)
• a table of Persons may directly contain the social security
number
• one to many relationship (2 tables)
• a table of Sources
• a table of Quotes
• each Quotes record points to one Sources record
• many to many relationship (3 tables)
• a table of Persons
• a table of Skills
• a table of Person-Skills
• for each skill that a person has, there is a record here
• for each person that has a skill, there is a record here
8-Jul-15
db.ppt
Relational Database Concepts
8-Jul-15
•
•
•
•
Relationships
Contraints
Triggers
Stored procedures
•
Tables
•
Views
•
Indexes
•
Keys
•
•
•
•
Rows (Records)
Columns (Fields)
Clustered indexes
Non-clustered indexes
•
Primary keys
•
Foreign keys
•
Composite keys
•
Locking
•
Isolation Levels (for transactions)
•
•
•
optimistic
pessimistic
gets complicated!
db.ppt
the end of this PowerPoint file
Hooray!
8-Jul-15
db.ppt