06 JSP Databases Multiple Tables Mark Dixon 1

06 – JSP Databases: Multiple Tables Mark Dixon 1

Admin: Test • • Next week (Mon 5 th) 11: 00 -13: 00 Individual 40% of module mark Open book: ü Web-site: mdixon. soc. plymouth. ac. uk ü Printed slides ü Robbins J (2006) HTML & XHTML Pocket Reference (3 rd edition). O'Reilly. ISBN: 978 -0 -596 -52727 -3 ü Bergsten H (2001) Java. Server Pages Pocket Reference. O'Reilly. ISBN: 978 -0 -596 -00231 -2 ü Gennick J (2006) SQL Pocket Guide (2 nd edition). O'Reilly. ISBN: 978 -0 -596 -52688 -7 ü Google Mark Dixon 2

Example: Distance SPECIFICATION • User Requirements – need to convert a given distance in miles to kilometres (1 mile = 1. 6 km) • Software Requirements Mark Dixon – Functional: – User should be able to enter the distance (in miles) – the distance (in kilometres) should be calculated and displayed – Non-functional must use Java. Script should be quick and easy to use 3

Example: Land Area (what) SPECIFICATION • User Requirements – need to know the land area in acres, given the length and width in metres (1 m = 1. 09 yds) • Software Requirements Mark Dixon – Functional: – User should be able to enter the length and the width (in metres) – the length and width (in yds), and the area (in acres) should be calculated and displayed – Non-functional must use JSP server-side code (not Java. Script) should be quick and easy to use 4

Example: Land Area (how) • Acre – unit of measurement of land area – long strip of land: can be ploughed in a day • 1 Acre = 220 yds x 22 yds (= 4840 yds 2) 22 yds 220 yds • 1 furlong (furrow long) = 220 yds Mark Dixon 5

Example: Countries SPECIFICATION • User Requirements – Manage population data • Software Requirements Mark Dixon – Functional: – Country List page: should list all countries in the database (each item should be a link to the Country page). – Country page: should list all details for specific country. – Non-functional must use JSP server-side code (not Java. Script) should be quick and easy to use 6

Questions: Databases • How many records are in the following table? • How many fields does the following table have? Mark Dixon 6 4 7

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 8

Questions: HTML in VB • Are these correct (assume variables and fields exist)? f = f + rs. Fields("Description"). value h = h + rs. Fields(" Name"). value a = "<p>" + a "</p>" html = html + <img src=face. gif /> h = "<table>" + h + "</table>" Mark Dixon 9

Session Aims & Objectives • Aims – To add dealing with multiple tables and writing data to your understanding of 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 – create a web page that allows the user to write (store) data in database Mark Dixon 10

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 11

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 12

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 13

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 14

Relations (tables) Track Title 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 Artist D Name Count ry 1 UK Black Sabbath 15

Relations (tables) Track Title 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 16

Relations (tables) Track Title Paranoid Falling in Love Pink Artist ID 1 2 Love in an Elevator 2 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 17

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

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

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

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

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

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

Question: Prescriptions • Identify duplication and separate: Prescription Mark Dixon Date Surname Forenames Drug Name 6 Jan 04 Jones Alison Co-codamol 11 Jan 04 Smith Bob Tegretol 18 Jan 04 Hope John Co-codamol 5 Feb 04 Johnson Sally Co-codamol 8 Feb 04 Smith Bob Tegretol 10 Feb 04 Smith Bob Sorbitol 24

Question: Solution Patient. ID Surname Forenames Prescription 1 Jones Alison 2 Smith Bob 3 Hope John 4 Johnson Sally Date Patient. ID Drug. ID 6 Jan 04 1 1 11 Jan 04 2 2 18 Jan 04 3 1 5 Feb 04 4 1 8 Feb 04 2 2 Drug. ID Drug Name 10 Feb 04 2 3 1 Co-codamol 2 Tegretol 3 Sorbitol Mark Dixon Drug 25

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 26

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 27

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 28

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 29

SQL: Joining tables Two tables SELECT * FROM Person, Hobby; Cartesian set (all record combinations): Mark Dixon 30

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

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 32

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 33

Questions: Databases • How many primary keys? • How many foreign keys? Mark Dixon 3 2 34

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 person's details 35

Example: People. List. jsp v 1 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname") + " "; } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon 36

Example: People. List. jsp v 2 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; now links String id; while(r. next()){ id = Integer. to. String(r. get. Int("Person. ID")); html += "<a href='Person 2. jsp? id=" + id + "'>"; html += r. get. String("Surname") + "</a> "; } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon 37

Example: Person. jsp v 2 <%@page import="java. sql. *"%> reads querystring <%@page content. Type="text/html" page. Encoding="UTF-8"%> <% (from previous page) String id = request. get. Parameter("id"); Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); displays data for Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); record only selected Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person WHERE Person. ID = " + id + "; "); String surname = ""; if(r. next()){ surname = r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title>Person</title></head> <body> Surname: <input name="txt. Surname" type="text" value="<%=surname%>" /> </body> </html> Mark Dixon 38

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) example from the lecture working. Mark Dixon 39