21 Databases Multiple Tables and Writing Data Mark

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? Mark Dixon 6 4 2

Questions: SQL • Write an SQL statement to display the name and land mass of all countries in Africa. 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(" 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: Mark Dixon – 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 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 Title Artist Name Count ry Black Aerosmith Sabbath Aerosmith UK 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 Paranoid Falling in Love Pink Mark Dixon US 10

Relations (tables) Track Title Artist Paranoid Artist ID Falling in Love 1 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 I D Artist Name Count ry 1 Black Sabbath UK 11

Relations (tables) Track Title Artist Paranoid Artist ID Falling in Love 1 Aerosmith 2 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 I Artist D Name Count ry 1 Black Sabbath UK 2 Aerosmith US 12

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath 2 Aerosmith US 2 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 13

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 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 Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Disturbed US Alien Ant Farm The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Mark Dixon 15

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Mark Dixon 16

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 4 Disturbed US Voices 4 Disturbed US Down with the Sickness Disturbed US Mark Dixon 17

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 4 Disturbed US Voices 4 Down with the Sickness 4 Disturbed Mark Dixon US 18

Relations (tables) Foreign Key Track Title Artist ID Paranoid 1 Falling in Love 2 Pink Primary Key Artist I D Artist Name Count ry 1 Black Sabbath UK 2 2 Aerosmith US Love in an Elevator 2 3 Alien Ant Farm US 4 Disturbed US Smooth Criminal 3 Meaning of Life 4 The Game 4 Voices 4 Down with the Sickness 4 Mark Dixon 19

Question: Keys Country Name Population Land Mass Cont. ID 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 Cont. ID 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 mark. dixon@plymouth. ac. uk john. smith@john. smith. ac. uk sally. jones@sally. jones. com fred. bloggs@aaaaaa. com genny@bbbb. cccc. com Hobby. ID 1 2 3 4 5 6 Mark Dixon Description Archery Herpetology Music Football Rugby Hitting people with swords Person. ID 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 mark. dixon@plymouth. ac. uk john. smith@john. smith. ac. uk sally. jones@sally. jones. com fred. bloggs@aaaaaa. com genny@bbbb. cccc. com Hobby. ID 1 2 3 4 5 6 Mark Dixon Description Archery Herpetology Music Football Rugby Hitting people with swords Person. ID 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 25

SQL: Joining tables Two tables SELECT * FROM Person, Hobby WHERE Person. ID = Hobby. Person. ID; Matching records 1 Surnam e Dixon Forenam es Mark 1 Dixon Mark 2 Smith John ID Mark Dixon Phone email 01752 232556 01752 111111 mark. dixon@plymouth. a c. uk john. smith@john. smith. a c. uk Hobby. I D 1 Description Archery Person. I D 1 2 Herpetology 1 3 Music 1 6 1 4 Hitting people with swords Football 5 Rugby 2 2 26

SQL: Joining tables SELECT ID, Surname FROM Person, Hobby WHERE Person. ID = Hobby. Person. ID; Surnam e 1 Dixon ID 1 Dixon 2 Smith Mark Dixon 27

Question: SQL Joining Tables • Write an SQL query to join the following: Track Mark Dixon Artist Track Title Artist ID I D Artist Name Count ry Paranoid 1 1 UK Falling in Love 2 Black Sabbath Pink 2 2 Aerosmith US Love in an Elevator 2 3 Alien Ant Farm US 4 Disturbed US Smooth Criminal 3 Meaning of Life 4 The Game 4 Voices 4 Down with the Sickness 4 28

SQL: More • Loads more: – group by – aggregate functions: average, count – inner joins – outer joins (left and right) • Have a look at: – http: //www. w 3 schools. com/sql_join. asp Mark Dixon 29

Example: Person v 1 (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: People. List. aspx v 1 <%@ Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. DB" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim s As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() s = "" Do While r. Read() s = s & r("Surname") & " " Loop cn. Close par. Data. Inner. Html = s End Sub </script> <html> <head><title></head> <body> <p id="par. Data" runat="server"></p> </body> </html> Mark Dixon 31

Example: People. List. aspx v 2 <%@ Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. DB" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim s As String cmd = New Ole. Db. Command("SELECT * FROM Person; ", cn) cn. Open() r = cmd. Execute. Reader() s = "" Do While r. Read() s = s & "<a href='Person. aspx? id=" & r("ID") & "'>" s = s & r("Surname") & "</a> " Loop cn. Close par. Data. Inner. Html = s End Sub now links </script> <html> <head><title></head> <body> <p id="par. Data" runat="server"></p> </body> </html> Mark Dixon 32

Example: Person. aspx v 2 <%@ Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. DB" %> <script runat="server"> reads querystring (from previous page) Sub Page_Load() Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim sql As String Dim cn As New Ole. Db. Connection(cs) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader Dim s As String sql = "SELECT * FROM Person WHERE id=" & Request. Query. String("id") cmd = New Ole. Db. Command(sql, cn) cn. Open() r = cmd. Execute. Reader() s = "" If r. Read() Then txt. Surname. Value = r("Surname") End If cn. Close() End Sub displays data for selected record only </script> <html> <head><title></head> <body> <a href="People. List 2. aspx">Back to People List</a> <form runat="server"> Surname: <input id="txt. Surname" runat="server" /> <input id="btn. Save" type="submit" value="Save" runat="server" /> </form> </body> </html> Mark Dixon 33

Example: Person v 2 (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 v 3 (error) <%@ Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. DB" %> <script runat="server"> Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Save button executes SQL UPDATE Sub Page_Load() Dim sql As String Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader sql = "SELECT * FROM Person WHERE id=" & Request. Query. String("id") cmd = New Ole. Db. Command(sql, cn) cn. Open() r = cmd. Execute. Reader() If r. Read() Then txt. Surname. Value = r("Surname") End If cn. Close() End Sub btn. Save_Click(s As Object, e As Event. Args) Handles btn. Save. Server. Click Dim cmd As Ole. Db. Command Dim sql As String sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txt. Surname. Value + "'" + _ " WHERE id = " & Request. Query. String("id") & "; " cmd = New Ole. Db. Command(sql, cn) cn. Open() cmd. Execute. Non. Query() cn. Close End Sub </script> Mark Dixon PROBLEM: Page_Load re-reads old surname first 37

Example: Person. aspx v 3 b <%@ Page Language="VB" %> <%@ Import Namespace="System. Data. Ole. DB" %> <script runat="server"> Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Save button executes SQL UPDATE Sub Page_Load. Complete(s As Object, e As Event. Args) Dim sql As String Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader sql = "SELECT * FROM Person WHERE id=" & Request. Query. String("id") cmd = New Ole. Db. Command(sql, cn) cn. Open() r = cmd. Execute. Reader() If r. Read() Then txt. Surname. Value = r("Surname") End If cn. Close() End Sub btn. Save_Click(s As Object, e As Event. Args) Handles btn. Save. Server. Click Dim cmd As Ole. Db. Command Dim sql As String sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txt. Surname. Value + "'" + _ " WHERE id = " & Request. Query. String("id") & "; " cmd = New Ole. Db. Command(sql, cn) cn. Open() cmd. Execute. Non. Query() cn. Close End Sub </script> Mark Dixon Fix: Use Page_Load. Complete 38

Example: Person. aspx v 3 c Dim cs As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; " + _ "Data Source=" + Server. Map. Path("People. accdb") + "; " Dim cn As New Ole. Db. Connection(cs) Dim sql As String Sub Page_Load() cn. Open() End Sub • Page_Load: first • Click events • Page_Load. Complete: last Sub btn. Save_Click(s As Object, e As Event. Args) Handles btn. Save. Server. Click Dim cmd As Ole. Db. Command sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txt. Surname. Value + "'" + _ " WHERE id = " & Request. Query. String("id") & "; " cmd = New Ole. Db. Command(sql, cn) cmd. Execute. Non. Query() End Sub Page_Load. Complete(s As Object, e As Event. Args) Dim cmd As Ole. Db. Command Dim r As Ole. Db. Data. Reader sql = "SELECT * FROM Person WHERE id=" & Request. Query. String("id") cmd = New Ole. Db. Command(sql, cn) r = cmd. Execute. Reader() If r. Read() Then txt. Surname. Value = r("Surname") End If cn. Close() End Sub Mark Dixon 39

Tutorial Exercise: Person • Task 1: Get the Person (v 1) 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 (v 2 and v 3) 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 APPPOOLDefault. App. Pool (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