Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Web Based Services, Lecture Notes - Computer Science - 5, Study notes of Computers and Information technologies

University of SussexComputers and Information technologies

Transaction processing and the ACID criteria. Relational Model. The Table, Kinds of Relationship, Nomalisation, MySQL, Java Data Base Connector

Typology: Study notes

2010/2011

Uploaded on 09/09/2011

asdlol2
asdlol2 🇬🇧

4.4

(8)

232 documents

1 / 19

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
Web Based Commerce 514H3
Dr Kingsley Sage
Room 2R308, Chichester II
[email protected]
© University of Sussex 2009
Lecture 3
Transaction processing and the ACID criteria
The Relational Model
The Table
Kinds of Relationship
Normalisation
MySQL
Java Data Base Connector (JDBC)
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13

Partial preview of the text

Download Web Based Services, Lecture Notes - Computer Science - 5 and more Study notes Computers and Information technologies in PDF only on Docsity!

Web Based Commerce 514H

Dr Kingsley Sage

Room 2R308, Chichester II

[email protected]

© University of Sussex 2009

Lecture 3

 Transaction processing and the ACID criteria

 The Relational Model

 The Table

 Kinds of Relationship

 Normalisation

 MySQL

 Java Data Base Connector (JDBC)

Transaction processing

 At its simplest, a transaction can be defined as a “unit of work” in a database  Design of a transaction processing system affected by:

  • whether processing takes place over a single connection to an embedded database or over multiple connections to the same database
  • How many different users may be making transactions at the same time
  • Whether there are multiple threads of execution within the application server or at the application logic layer

The ACID criteria

 Four criteria to ensure safe data sharing:

  • Atomicity : A transaction is complete if and only if all of its operations were performed successfully
  • Consistency : A transactions must transition data from one consistent state to the next. E.g. if a database has records with two related parts, a transaction must not violate that structure
  • Isolation : Any changes made to the data are invisible to other concurrent transactions until the transaction commits. Isolation requires that several concurrent transactions must produce the same results as those same transactions executed serially
  • Durability : committed updates are permanent. Failures after a commit cause no loss of data. This also implies that committed data can be recovered after a system failure

The Relation

 Each row represents an n-tuple of R  The ordering of rows in immaterial  All rows are distinct  The ordering of the columns is significant - it corresponds to the ordering S1, S2,…,S n of the domains on which R is defined  The significance of each column is partially conveyed by labelling it with the name of the corresponding domain “The term relation is used here in its accepted mathematical sense. Given sets S1, S2,…,Sn, R is a relation on these n sets if it is a set of n-tuples each of which has Its first element from S1, its second element from S2 and so on. We shall refer to Sj as the jth domain of R. As defined above, R is said to have degree n.” E. F. Codd 1970 S1 … Sn

R

The Table

Table: Column

 Represents an attribute of the entity type  Has a domain that specifies the data type of the value  Should be a single value  Is uniquely named

  • Must be uniquely named within the table, useful to make it uniquely named within the database  The number of columns determines the table’s degree

Table: Row

 Represents a single entity or instance of the entity type  Ordering of the rows is undefined  Uniquely identified by its primary key

  • The primary key may be a composite key  No 2 rows may be identical  The number of rows determines the table’s cardinality

Table: Relationships

 Cardinality (or degree) - the number of instances involved in a relationship

  • 1:1 - one to one
  • 1:M - one to many
  • M:M - many to many

Relationships: one to one

 The primary key of one table is also the foreign key referencing another table  Could conflate to one table?? 1235 Database systems 1234 SQL 1233 Java bk_isbn PK bk_title 1235 3. 1234 19. 1233 14. pr_isbn PK pr_price books price 1: books book^ prices

Relationships: one to many

 The foreign key of the books child table is the primary key of the publishers parent table Database systems SQL Java bk_title 1235 p 1234 p 1233 p bk_isbn PK bk_pub_id p003 Wiley p002 O’Reilly p001 Peachpit pub_id PK oub_name books publishers publisher book 1:M

Relationships: many to many

Junction table : Has a composite primary key, made up of foreign keys a002 Beloff a002 Davies a001 Fehily au_id PK au_name book author M:M 1235 a 1234 a 1234 a ab_isbn CK ab_auid CK 1235 Database systems 1234 SQL 1234 JDO bk_isbn PK bk_title books books_authors authors

Normalisation: First Normal Form

 A relation is in its first normal form if and only if every non-key attribute is functionally dependent on the primary key Put it another way …  A table is in its first normal form if it has columns that contains only atomic values and it has no repeating groups (I.e. the primary key values are unique)

Normalisation: First Normal Form

Normalisation: Second Normal Form

 A relation is in second normal form if and only if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key Put it another way …  “Can I determine a non-key column value if I know only part of the (composite) primary key value?”

  • Yes - table is NOT 2NF
  • No - table is 2NF

Normalisation: Second Normal Form

The Normalisation Oath

 No repeating

  • Uphold set semantics  The field depends on the key
  • Functional dependence - 1NF  The whole key
  • Full functional dependence - 2NF  And nothing but the key
  • Transitive dependence - 3NF  So help me Codd

MySQL

 Free version of the popular Structured Query Language (SQL) database product  Has several parts:

  • MySQL server: resides as a server process on your PC and does the database work
  • MySQL admin tool: small client program to administer the MySQL server
  • MySQL client: client that allows you to communicate with MySQL server – you can type commands at the prompt and appropriate database actions happen at the server  MySQL deals in databases and tables  See the course notes for a MySQL primer

Using the MySQL client

 Key MySQL commands

  • CREATE: to make tables
  • INSERT: to put values into tables
  • SELECT: to make queries  MySQL replies with an integer reflecting the number of table rows affected  MySQL has a variety of data types that can be mapped to Java and other language data types (mappings are described in the course notes)

Creating tables

CREATE Table Employees

EmpID INT,

EmpFN VARCHAR(20) NOT NULL,

EmpMN VARCHAR(20),

EmpLN VARCHAR(20) NOT NULL

Making queries (2)

SELECT EmpFN, EmpLN FROM Employees; +----------+----------+ | EmpFN | EmpLN | +----------+----------+ | John | Smith | | Robert | Schroader| | Mary | Michaels | | John | Laguci | | Rita | Carter | | George | Brooks | +----------+----------+ 6 rows in set (0.00 secs)

Making queries (3)

SELECT * FROM Employees

WHERE EmpID > 3;

+----------+----------+----------+----------+ | EmpID | EmpFN | EmpMN | EmpLN | +----------+----------+----------+----------+ | 4 | John | NULL | Laguci | | 5 | Rita | C. | Carter | | 6 | George | NULL | Brooks | +----------+----------+----------+----------+ 3 rows in set (0.02 secs)

JDBC

 Java Data Base Connector (JDBC) is a call-level Application Programming Interface that allows Java to communicate with SQL databases  You also need the Connector/J driver  JDBC allows Java to execute commands and queries directly  Java code example included in the course notes  Lab sheet 2 (week 3) deals with MySQL and JDBC

Using JDBC

 Follow a simple set of steps:

  • Declare and initialise a Java String variable that will hold the SELECT statement
  • Declare and initialise a Statement variable that creates a Statement object
  • Declare and initialise a ResultSet variable that uses the Statement object to execute the query and to create a ResultSet object that contains the query results
  • Use the ResultSet variable to process the results in your Java application  Q: How is persistence managed?

Next time …

 Overview of web technologies (such as PHP, JSP, J2EE) …