Transcript 21 ASP.Net DB 2 - Mark Dixon`s web site

21 – Databases: Multiple Tables
and Writing Data
Mark Dixon
1
Questions: Databases
• How many records are in the following table?
• How many fields does the following table have?
Country
Name
Population Land Mass
UK
60776238
241590
Spain
40448191
499542
Germany
82400996
349223
Egypt
80335036
995450
Kenya
36913721
569250
China
1321851888 9326410
Mark Dixon
6
4
Continent
Europe
Europe
Europe
Africa
Africa
Asia
2
Questions: SQL
• Write an SQL statement to display the name and
land mass of all countries in Africa.
Country
Name
Population Land Mass
UK
60776238
241590
Spain
40448191
499542
Germany
82400996
349223
Egypt
80335036
995450
Kenya
36913721
569250
China
1321851888 9326410
Continent
Europe
Europe
Europe
Africa
Africa
Asia
SELECT Name, Land Mass
FROM Country WHERE Continent = 'Africa';
Mark Dixon
3
Questions: HTML in VB
• Are these correct (assume variables and
fields exist)?
f = f + r("Description")

h = h + r("<br />Name")

a = "<p>" + a "</p>" 
html = html + <img src=face.gif /> 
h = "<table>" + h + "</table>" 
Mark Dixon
4
Advice
• Don’t
– put anything on desktop
– Especially database
Mark Dixon
5
Session Aims & Objectives
• Aims
– To deal with multiple tables
– To write data to databases
• Objectives,
by end of this week’s sessions, you should be able to:
– identify a suitable primary key for a table
– identify duplicated data in a single table
– split that table to reduce data redundancy,
using a suitable foreign key
– generate SQL statements to (temporarily)
join tables, and use these in your code
– use SQL to write data into database
Mark Dixon
6
Data Duplication
• Look for repeating data:
Track
Mark Dixon
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
7
Problem: Data Duplication
• takes up lots of space
• can become inconsistent (misspellings)
• difficult to change (need to change each
instance)
• difficult to search (misspellings)
Mark Dixon
8
Solution: Normalisation
• Part of database design
• Process of breaking data down (splitting)
• Codd
– 7 stages of normalisation
• Mathematical
• Difficult to apply stages
• Most professionals do it instinctively
Mark Dixon
9
Relations (tables)
Track
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
10
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Paranoid
1
1
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
Country
Black Sabbath UK
11
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Paranoid
1
1
Black Sabbath UK
Falling in Love
Aerosmith
2
US
2
Aerosmith
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
Country
US
12
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
Love in an Elevator
Aerosmith
Smooth Criminal
Alien Ant Farm US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
US
13
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
Love in an Elevator
2
Smooth Criminal
Alien Ant Farm US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
14
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
Smooth Criminal
3
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
15
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
4
Disturbed
Smooth Criminal
3
Meaning of Life
4
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
US
16
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
4
Disturbed
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
Disturbed
US
Down with the Sickness Disturbed
US
Mark Dixon
US
17
Relations (tables)
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
4
Disturbed
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
4
Down with the Sickness Disturbed
Mark Dixon
US
US
18
Relations (tables)
Foreign
Key
Track
Track Title
Artist ID
Paranoid
Primary
Key
Artist
ID Artist Name
Country
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
4
Disturbed
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
4
US
Down with the Sickness 4
Mark Dixon
19
Question: Keys
Country
Name Population Land Mass ContID
UK
60776238
241590
1
Spain
40448191
499542
1
Germany 82400996
349223
1
Egypt
80335036
995450
2
Kenya
36913721
569250
2
China 1321851888 9326410
3
Continent
ID
Name
1
Europe
2
Africa
3
Asia
• Name a Primary Key
ID in the Continent table
• Name a Foreign Key
ContID in the Country table
Mark Dixon
20
People Database (with Hobbies)
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
21
Entity-relationship diagrams
• Each table in db
– stores details of entity
• shown as rectangular box
Person
Hobby
•Relationships between tables
–represent relationships between entities
•shown as line between entities (boxes)
Mark Dixon
22
Relationship Types
• One-to-one
A
B
• One-to-many
A
B
• Many-to-one
A
B
• Many-to-many
– (can't be implemented in relational database)
A
Mark Dixon
B
23
Question: Which relationship type?
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
Person
Hobby
24
SQL: Joining tables
Two tables
SELECT * FROM Person, Hobby;
Cartesian set
(all record
combinations):
Mark Dixon
ID
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
Su rn am e
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Fo ren am es
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
Ph o n e
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
em ail
Ho b b yID
m ark . d ix o n @ p lym o u th . ac. u k
7
jo h n . sm ith @ jo h n . sm ith . ac. u k
7
sally. jo n es@ sally. jo n es. co m
7
fred . b lo g g s@ aaaaaa. co m
7
g en n y@ b b b b . cccc. co m
7
m ark . d ix o n @ p lym o u th . ac. u k
8
jo h n . sm ith @ jo h n . sm ith . ac. u k
8
sally. jo n es@ sally. jo n es. co m
8
fred . b lo g g s@ aaaaaa. co m
8
g en n y@ b b b b . cccc. co m
8
m ark . d ix o n @ p lym o u th . ac. u k
9
jo h n . sm ith @ jo h n . sm ith . ac. u k
9
sally. jo n es@ sally. jo n es. co m
9
fred . b lo g g s@ aaaaaa. co m
9
g en n y@ b b b b . cccc. co m
9
m ark . d ix o n @ p lym o u th . ac. u k
10
jo h n . sm ith @ jo h n . sm ith . ac. u k
10
sally. jo n es@ sally. jo n es. co m
10
fred . b lo g g s@ aaaaaa. co m
10
g en n y@ b b b b . cccc. co m
10
m ark . d ix o n @ p lym o u th . ac. u k
11
jo h n . sm ith @ jo h n . sm ith . ac. u k
11
sally. jo n es@ sally. jo n es. co m
11
fred . b lo g g s@ aaaaaa. co m
11
g en n y@ b b b b . cccc. co m
11
m ark . d ix o n @ p lym o u th . ac. u k
12
jo h n . sm ith @ jo h n . sm ith . ac. u k
12
sally. jo n es@ sally. jo n es. co m
12
fred . b lo g g s@ aaaaaa. co m
12
g en n y@ b b b b . cccc. co m
12
Descrip tio n
Perso n ID
Arch ery
1
Arch ery
1
Arch ery
1
Arch ery
1
Arch ery
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
M u sic
1
M u sic
1
M u sic
1
M u sic
1
M u sic
1
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
25
SQL: Joining tables
Two tables
SELECT *
FROM Person, Hobby
WHERE Person.ID = Hobby.PersonID;
Matching records
ID
Surname
Forenames
Phone
email
1
Dixon
Mark
01752 232556
[email protected]
1
Archery
1
1
Dixon
Mark
01752 232556
[email protected]
2
Herpetology
1
1
Dixon
Mark
01752 232556
[email protected]
3
Music
1
1
Dixon
Mark
01752 232556
[email protected]
6
Hitting people with swords
1
2
Smith
John
01752 111111
[email protected]
4
Football
2
2
Smith
John
01752 111111
[email protected]
5
Rugby
2
Mark Dixon
HobbyID
Description
PersonID
26
SQL: Joining tables
SELECT ID, Surname
FROM Person, Hobby
WHERE Person.ID = Hobby.PersonID;
ID
Surname
1 Dixon
1 Dixon
1 Dixon
1 Dixon
2 Smith
2 Smith
Mark Dixon
27
Question: SQL Joining Tables
• Write an SQL query to join the following:
Track
Artist
Track Title
Artist ID
ID Artist Name
Country
Paranoid
1
1
Black Sabbath
UK
Falling in Love
2
2
Aerosmith
US
Pink
2
3
Alien Ant Farm US
Love in an Elevator
2
4
Disturbed
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
4
US
Down with the Sickness 4
Mark Dixon
28
SQL: More
• Loads more:
– group by
– aggregate functions: average, count
– inner joins
– outer joins (left and right)
• Have a look at:
– http://www.w3schools.com/sql/sql_join.asp
Mark Dixon
29
Example: Person v1 (Specification)
• User requirement:
– Display people's details from database online
– need 2 pages:
smith
jones
dixon
list of people
Mark Dixon
jones
sally
person's details
30
Example: PeopleList.aspx v1
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
Sub Page_Load()
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim cn As New OleDbConnection(cs)
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
Dim s As String
cmd = New OleDbCommand("SELECT * FROM Person;", cn)
cn.Open()
r = cmd.ExecuteReader()
s = ""
Do While r.Read()
s = s & r("Surname") & "<br />"
Loop
cn.Close
parData.InnerHtml = s
End Sub
</script>
<html>
<head><title></title></head>
<body>
<p id="parData" runat="server"></p>
</body>
</html>
Mark Dixon
31
Example: PeopleList.aspx v2
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
Sub Page_Load()
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim cn As New OleDbConnection(cs)
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
Dim s As String
cmd = New OleDbCommand("SELECT * FROM Person;", cn)
cn.Open()
r = cmd.ExecuteReader()
s = ""
Do While r.Read()
s = s & "<a href='Person.aspx?id=" & r("ID") & "'>"
s = s & r("Surname") & "</a><br />"
Loop
cn.Close
parData.InnerHtml = s
End Sub
now links
</script>
<html>
<head><title></title></head>
<body>
<p id="parData" runat="server"></p>
</body>
</html>
Mark Dixon
32
Example: Person.aspx v2
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
Sub Page_Load()
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim sql As String
Dim cn As New OleDbConnection(cs)
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
Dim s As String
sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id")
cmd = New OleDbCommand(sql, cn)
cn.Open()
r = cmd.ExecuteReader()
s = ""
If r.Read() Then
txtSurname.Value = r("Surname")
End If
cn.Close()
End Sub
reads querystring
(from previous page)
displays data for
selected record only
</script>
<html>
<head><title></title></head>
<body>
<a href="PeopleList2.aspx">Back to People List</a><br />
<form runat="server">
Surname: <input id="txtSurname" runat="server" /><br />
<input id="btnSave" type="submit" value="Save" runat="server" />
</form>
</body>
</html>
Mark Dixon
33
Example: Person v2 (Specification)
• User requirement:
Display person’s details from database online
– Change surname and save to database
Mark Dixon
34
Changing Data
• SQL
– INSERT: inserts a new record
INSERT INTO Person (Surname, Age)
VALUES ('Smith', 21);
– UPDATE: makes changes to specified record
UPDATE Person
Set Surname = 'Smith', Age = 21
WHERE id = 14;
– DELETE: deletes specified record
DELETE FROM Person WHERE id = 14
Mark Dixon
35
WARNING!!
• All changes permanent (no undo)
• WHERE clause is CRITICAL
DELETE FROM Person;
Will delete ALL records in table
Mark Dixon
36
Example: Person.aspx v3 (error)
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim cn As New OleDbConnection(cs)
Save button
executes SQL UPDATE
Sub Page_Load()
Dim sql As String
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id")
cmd = New OleDbCommand(sql, cn)
cn.Open()
r = cmd.ExecuteReader()
If r.Read() Then
txtSurname.Value = r("Surname")
End If
cn.Close()
End Sub
Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick
Dim cmd As OleDbCommand
Dim sql As String
sql = "UPDATE [Person] " + _
" SET [Surname] = '" + txtSurname.Value + "'" + _
" WHERE id = " & Request.QueryString("id") & ";"
cmd = New OleDbCommand(sql, cn)
cn.Open()
cmd.ExecuteNonQuery()
cn.Close
End Sub
</script>
Mark
Dixon
PROBLEM: Page_Load
re-reads old surname first
37
Example: Person.aspx v3b
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim cn As New OleDbConnection(cs)
Save button
executes SQL UPDATE
Sub Page_LoadComplete(s As Object, e As EventArgs)
Dim sql As String
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id")
cmd = New OleDbCommand(sql, cn)
cn.Open()
r = cmd.ExecuteReader()
If r.Read() Then
txtSurname.Value = r("Surname")
End If
cn.Close()
End Sub
Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick
Dim cmd As OleDbCommand
Dim sql As String
sql = "UPDATE [Person] " + _
" SET [Surname] = '" + txtSurname.Value + "'" + _
" WHERE id = " & Request.QueryString("id") & ";"
cmd = New OleDbCommand(sql, cn)
cn.Open()
cmd.ExecuteNonQuery()
cn.Close
End Sub
</script>
Mark
Dixon
Fix: Use Page_LoadComplete
38
Example: Person.aspx v3c
Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + Server.MapPath("People.accdb") + ";"
Dim cn As New OleDbConnection(cs)
Dim sql As String
Sub Page_Load()
cn.Open()
End Sub
Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick
Dim cmd As OleDbCommand
sql = "UPDATE [Person] " + _
" SET [Surname] = '" + txtSurname.Value + "'" + _
" WHERE id = " & Request.QueryString("id") & ";"
cmd = New OleDbCommand(sql, cn)
cmd.ExecuteNonQuery()
End Sub
• Page_Load: first
• Click events
• Page_LoadComplete:
last
Sub Page_LoadComplete(s As Object, e As EventArgs)
Dim cmd As OleDbCommand
Dim r As OleDbDataReader
sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id")
cmd = New OleDbCommand(sql, cn)
r = cmd.ExecuteReader()
If r.Read() Then
txtSurname.Value = r("Surname")
End If
cn.Close()
End Sub
Mark Dixon
39
Tutorial Exercise: Person
• Task 1: Get the Person (v1) example from the lecture
working.
• Task 2: Modify your code, so that forename is displayed as
well as surname (use a table).
• Task 3: Get the Person (v2 and v3) example from the
lecture working.
• Task 3: Modify your code, so that a line of text is displayed
confirming that data has been saved.
• Task 4: Modify your code, so that an add button is
included, which allows a new record to be added.
• Task 5: Modify your code, so that a delete button is
included, which allows the current record to be deleted.
Mark Dixon
40
Tutorial Exercise: Music
• Task 1: Create the Music database (from the lecture) with the Track
and Artist tables.
• Task 2: Create a web page to display a list of Artists.
• Task 4: Change that web page, so that each artist name is a link to
another page, which displays all the tracks by that artist.
Hint: Use query strings to pass the artist ID between pages.
Mark Dixon
41
How To: Database Permissions
• Generally
– Read: works by default
– Write: requires permissions
• Asp.Net pages run as user:
– Visual Studio
• Logged in user (few problems)
– IIS
• ASP.Net Account
• NETWORKSERVICE (Server 2003)
• IIS APPPOOL\DefaultAppPool (Windows 7)
Mark Dixon
42
How To: Database Permissions 1
• In order for ASP to
write to a database
– Need to give write
access account for
database file
(People.accdb)
• Right-click on file in
File Explorer
• Click Properties
• Click Security tab
• Click Edit button
Mark Dixon
43
How To: Database Permissions 2
• Click Add button
Mark Dixon
44
How To: Database Permissions 3
• Click Advanced
button
Mark Dixon
45
How To: Database Permissions 4
•
Click
Find button
Click
user
Click
OK button
Mark Dixon
46
How To: Database Permissions 5
• Select Internet
Guest Account
• Ensure write
access is on
Mark Dixon
47