Week 12

1
Week 12

• Prizes, PHP and XML
• Data Schemas and Applications

2
Agenda

• Coursework feedback
• Normalisation exercise from a few weeks ago
• Judging and prizes for the best baby Flickr
• PHP, XML and Web services introduction to the
  tutorial.

3
Coursework Feedback

• General feedback

4
Normalisation Exercise

• Normalise this data on outstanding video rentals
• Describe your result as an ER model
  (relationships not foreign keys)

Source www.gc.maricopa.edu/business/sylvester/cis
164/norm-rev.htm
5
Normalisation

• Video data
• Artist is the same for a given video title
• So is Length
• So these depend only on Video Title
• So normalise this data into one table Video with
  a primary key of VideoTitle
• Borrower data
• Borrower(name) and Card Nbr are related
• So Normalise to a Borrower table with a primary
  key of CardNbr
• What remains is
• DateDue, DaysOverdue, VideoTitle, CopyNbr and
  CardNbr
• These represent a Rental
• Primary key is VideoTitle, CopyNbr, and Date (why
  not CardNbr?)

6
Normalised Tables

• Video(VideoTitle, Artist, Length)
• Borrower (CardNbr, Name)
• Rental (VideoTitle, CopyNbr, DateDue,
  DaysOverdue, CardNbr)

7
ER model
This indicates Foreign Key becomes part of the
PrimaryKey
CopyNbr not unique in the shop but only for a
VideoTitle
8
Observations

• We can improve on this model in two ways
• When a video is returned, the rental record will
  be deleted. But this will also delete knowledge
  of the existence of a video copy, so we need to
  introduce a Copy table
• The DaysOverdue is actually a function of todays
  date and the due date- today seems to be 8/32/00
  (i.e.9/1/00) so this data is redundant since it
  can be calculated
• In UML you would be able to keep this attribute
  but mark it as derived and define the rule for
  calculating it.
• This model is closer to the model you might
  create working top-down

9
(No Transcript)
10
CREATE TABLE Video( Title VARCHAR(20) NOT
NULL, Artist VARCHAR(20), Length INTEGER, CONST
RAINT pk_Video PRIMARY KEY (Title) ) CREATE
TABLE Borrower( CardNbr INTEGER NOT
NULL, BorrowerName VARCHAR(8), CONSTRAINT pk_Bor
rower PRIMARY KEY (CardNbr) ) CREATE TABLE
Rental( DueDate DATE NOT NULL, CopyNbr INTEGER
NOT NULL, Title VARCHAR(20) NOT
NULL, CONSTRAINT pk_Rental PRIMARY KEY
(DueDate,CopyNbr,Title) ) CREATE TABLE
Copy( CopyNbr INTEGER NOT NULL, Title VARCHAR(20
) NOT NULL, CONSTRAINT pk_Copy PRIMARY KEY
(CopyNbr,Title) ) -----------------------------
--------------------------------- -- Create LINK
tables -- CREATE TABLE Borrower_Rental( CardNbr
INTEGER NOT NULL, DueDate DATE NOT
NULL, CopyNbr INTEGER NOT NULL, Title VARCHAR(20
) NOT NULL, PRIMARY KEY (DueDate,CopyNbr,Title),
FOREIGN KEY(CardNbr) REFERENCES
Borrower(CardNbr) ON DELETE RESTRICT ON UPDATE
RESTRICT, FOREIGN KEY(DueDate,CopyNbr,Title)
REFERENCES Rental(DueDate,CopyNbr,Title) ON
DELETE RESTRICT ON UPDATE RESTRICT )
11
QSEE code generation

• Note the additional link table. This is because
  Borrower is optional (the second entry in the
  sample data). QSEE is very strict about
  forbidding nulls as foreign keys, so it creates
  an addition link table.
• Nulls in foreign key fields is a big debating
  point theorists on the one side , pragmatists
  on the other. In my view QSEE should allow this
  as a optional setting.
• The only way to avoid this additional table is to
  remove the optionality, generate the DDL and then
  edit the foreign key column to remove the Not
  Null constraint from the foreign key column.

12
Questions for the customer

• Is VideoTitle really unique? Can two videos have
  the same title? Is there a standard identifier
  like ISBN which would be better? How could this
  be linked to IMDb e.g. Out of Africa IS there an
  equivalent to ISBN for videos? Why not?
• Numbering copies of a title is a bit harder than
  numbering every copy with a serial number which
  is unique in the store. Why? Would this be a
  better approach? Should every video ever
  produced have its own unique number?

13
Flickr Award Ceremony

• Groups and their projects
• Prizes

14
PHP, XML and Web services

• PHP 5.0 provides a simple way to access (and
  update) an XML document - SimpleXML.
• Web services provide data in XML format
• Amazon, Flickr, EBay
• The Weather Channel
• PHP can send requests to the web service and
  reformat the data returned.
• Bristol weather

15
Tutorial

• Install PHP code on your own workspace
• Get it working
• Make some improvements
• Learn about
• XML
• XML access in PHP
• The power of web services