Dias nummer 1

1
Web based record library lthtml
Hovedsiden Filnavn x.htm Vis alle album Søg
album / sang / kunstner søgeord Tilføj
album Slet album Ret album Vis en random record
lt?php Vis data udfra record_id Brugeren kan
rette i data submit
lt?php Filnavn Insert / add Indsætter data til
variablerne albumtitel, sange,
lt?php Filnavn header.inc WBRlib
lt?php Filnavn footer.inc Footmenu xx yy zz
albumtitel udgivelsesaar
albumtitel udgivelsesaar
lt?php Filnavn Update Opdaterer data til
variablerne albumtitel, sange,
Form x Filnavn Søg______ Tilføj record
lt?php Filnavn Delete / delete Sletter record
på baggrund af record_id
lthtml Form til ny record (insert) Filnavn
x.php Indtast data til variablerne Tabel
record record_id (key) (auto_increment) albumtit
el, kunstner, udgivelsesaar, submit
Tabel songs sond_id (key) (auto_increment) s
ongtitel, record_id
lt?php Filnavn Select / show alle album Vælger
record på baggrund af
lt?php Filnavn Select / show sange på et
album Vælger record på baggrund af
lt?php Filnavn mine_functioner.php Ereg
2
E-R diagram (entiteter og relationer)
Person
Song
Record
Artist eller band navn

• en record indeholder flere album
• et album indeholder flere sange
• et band har flere personer (men det
  implementeres ikke i min løsning)
• Relationsmuligheder 1N, NM

3
Transaktioner INSERT UPDATE DELETE SELECT
4
Constructing a database supported web sitesyet
again When we construct a database supported web
site we use the following steps Step 1
Constructing a data model Which information must
be stored and how should it be represented? This
is the hard part! Step 2 Developing data
transactions How do we insert data into the
database? How do we extract data from the
database? Step 3 Constructing web-forms for
implementing data transactions The user
interface is HTML code (forms) Step 4
Constructing PHP-files for implementing data
transactions SQL (Structured Query Language) is
used for the actual data transactions This is
the easy part! Note The more time you spend on
Step 3, the easier Step 4 becomes!
5
Noter n n n n n n
6

• Hovedsiden (hvordan ser den ud på skærmen?)

Web based record library ------------------------
--------------------------------------------------
-------------- Dagens record 1967 The
Beatles Sgt. Peppers Lonely Hearts Club
Band --------------------------------------------
-------------------------------------------- Søg
Album / Sang / Kunstner ______________
(lav form med select) --------------------------
--------------------------------------------------
------------ Forsiden Tilføj record Slet
record Ret record
1967 The Beatles Sgt. Peppers Lonely Hearts Club
Band Side 1 Sgt. Peppers Lonely Hearts Club
Band With A Little Help From My Friends Lucy In
The Sky With Diamonds Getting Better Fixing A
Hole She's Leaving Home Being For The Benefit Of
Mr. Kite Side 2 Within You, Without You When
I'm Sixty-Four Lovely Rita Good Morning, Good
Morningltbrgt Sgt. Peppers Lonely Hearts Club Band
(Reprise) A Day In The Life Kilde
www.beatlesinfo.dk
) lttdgt ltselect name"soegning"gt ltoption
selectedgtalbumlt/optiongt ltoptiongtsanglt/optiongt ltopt
iongtkunstnerlt/optiongt lt/selectgt lt/tdgt
7

• Tilføj album (hvordan ser den ud på skærmen?)

Web based record library ------------------------
--------------------------------------------------
-------------- 1967 The Beatles Sgt. Peppers
Lonely Hearts Club Band -------------------------
--------------------------------------------------
------------- Søg Album / Sang / Kunstner
_______________________________ -----------------
--------------------------------------------------
--------------------- Tilføj record Slet
record Ret record
1967 The Beatles Sgt. Peppers Lonely Hearts Club
Band Side 1 Sgt. Peppers Lonely Hearts Club
Band With A Little Help From My Friends Lucy In
The Sky With Diamonds Getting Better Fixing A
Hole She's Leaving Home Being For The Benefit Of
Mr. Kite Side 2 Within You, Without You When
I'm Sixty-Four Lovely Rita Good Morning, Good
Morningltbrgt Sgt. Peppers Lonely Hearts Club Band
(Reprise) A Day In The Life Kilde
www.beatlesinfo.dk
)
8

• Udkast til A
• Exercise A (25 percent)The data model
• As a first step of constructing a web based
  record library, you must construct a data model
  for the system, and a collection of CREATE TABLE
  SQL commands.
• Each record in the database must as a minimum be
  associated with a record title, an artist name, a
  publishing year and a collection of song titles.
• You must create at least two tables one table
  can thus contain record title, artist name,
  publication year and a number to identify a
  record (e.g., record_ID) the other table can
  then associate song titles with records. You are
  recommended to use numbers to identify songs
  (e.g., song_ID), and you must use MySQL's
  auto_increment feature to insert new records and
  songs (cf. the lecture slides). You must consider
  the following
• Which fields should each table contain? The
  preceding paragraph indicates which fields you
  need.
• Which type each field should be e.g.,
  varchar(...) or int?
• Which fields should be primary keys in each
  table?
• Are there any fields you want to require the user
  to enter, i.e., must be not null?
• When you have constructed your data model, you
  must create the tables in your database at
  mysql.itu.dk.
• Save your data model in the file
  Hpublic_html/w2/ps10/dbmodel.sql on ITU's web
  server.

9

• Udkast til B (transaktions)
• Exercise B (15 percent)Data transactions
• In this exercise you must write down a list of
  data transaction for the web based record
  library. Here are two examples of data
  transactions
• Inserting a new record in the library (without
  associated songs) INSERT INTO record (title,
  artist, year) VALUES ('Abbey Road', 'Beatles',
  '1969')
• Associating a new song with an existitng record
• INSERT INTO sang (record_ID, song_ID, title)
  VALUES (38, 'Here comes the sun') 38 is the
  record_ID for Beatles' Abbey Road
• As above, you must write down examples of SQL
  commands for the following data transactions
• deleting a song from a record
• DELETE xxx FROM xxxx WHERE xxx
• deleting a record from the library (you can
  assume there are no songs associated with the
  record)
• displaying songs from a record with a given
  record_ID (SELECT) as a minimum, you must
  display record name, artist and song titles

10

• B fortsat
• INSERT INTO record (title, artist, year) VALUES
  ('Abbey Road', 'Beatles', '1969')
• Associating a new song with an existitng record
• INSERT INTO sang (record_ID, song_ID, title)
  VALUES (38, 'Here comes the sun') 38 is the
  record_ID for Beatles' Abbey Road
• deleting a song from a record
• DELETE xxx FROM xxxx WHERE xxx
• deleting a record from the library (you can
  assume there are no songs associated with the
  record)
• DELETE xxx FROM xxxx WHERE xxx
• displaying songs from a record with a given
  record_ID (SELECT) as a minimum, you must
  display record name, artist and song titles
• SELECT xxx FROM xxx WHERE
• displaying all records published in a given year
  of a given artist (SELECT) as a minimum, you
  must display record name, artist and song titles
• SELECT xxx FROM xxx WHERE

11

• Udkast til C
• Exercise C (15 percent)The Web Structure
• In this exercise you must construct the web
  structure for the record library. The figure
  shows how the system could be composed. The
  boxes in the diagram represent the states of the
  web based record library for which HTML code is
  displayed in the user's browser. The edges
  represent links to a new state. Every edge is
  labelled with the name of the PHP script that is
  invoked. Some edges represent transactions in
  which the database is created.
• The entry page for the library, shown as a bow at
  the top of the diagram, can be implemented as an
  HTML file. The remaining boxes (states) in the
  diagram must be implemented as PHP scripts, as
  these boxes represent dynamic pages (i.e., what
  the user sees in the browser depends on the
  contents of the database and possibly form
  variables). You can also choose to let the "Show
  all records" page be the entry page.
• It is your job to complete the preceding figure.
  On each of the remaining edges you must write the
  name of the PHP script that is invoked. It is
  also a good idea to add names of form variables
  that each PHP file assumes are set.
• To determine which form variables to transfer,
  you are recommended to transfer only the
  absolutely necessary onesi.e., you should keep
  the number of form variables as small as
  possible. For instance, if you are to display
  songs of a record, record_ID is sufficient,
  because all information about songs and record
  can be retrieved from the database, based just on
  this ID.
• You must hand in a description, either as a text
  file Hpublic_html/w2/ps10/structure.txt or as a
  diagram Hpublic_html/w2/ps10/structure.gif on
  ITU's web server. If you hand in a diagram, each
  script must be named and indicate which form
  variables the script expects.

12

• Udkast til D
• Exercise D (45 percent)Web scripts
• In this exercise you must construct PHP scripts
  for the dynamic pages and for transactions for
  deleting and inserting records and songs into the
  database.
• Begin by constructing the dynamic pages for the
  boxes in the diagram. Add links in these pages to
  the PHP files for the database transactions.
• For database transactions, you can use calls to
  the PHP function header
• header("Locationurl")
• This call returns a message to the browser,
  instructing it to display the page with the given
  url. Here is some code for implementing the
  database transaction for insertion of a song into
  a given record in the database
• include("mydb.php") // Include utilities // Test
  record_ID and song title! if ( ! ereg('0-9',
  record_ID) ) error "Panic, record_ID is not
  present." if ( ! ereg('a-zA-ZæøåÆØÅ\.
  \-', song_title ) ) error "Panic, song
  title is not present." mydb_connect() //
  Connect to database mysql_query ("INSERT INTO
  song (record_ID, songtitle) VALUES (record_ID,
  'song_title')") // instead of returning a page
  to the user with thank you' // information, we
  ask the browser to show the--now updated-- //
  song page notice that the dynamic page
  pk_sange_vis.php' takes // a form variable
  record_ID' as argument. header("Location
  pk_vis_sange.php?record_IDrecord_ID") The code
  uses the utility file mydb.php from the lecture
  slides. This utility file defines the functions
  error and mydb_connect that are used above. You
  are also welcome to use the formvars.php library
  from the lecture slides.
• Note that the user is not required to enter a
  password before deleting and inserting data in
  the record library.