Database Application Assignment

1
Database Application Assignment

• FIS 1311 Nov. 28, 2005

2
Outline Assignment Components

• Use Cases
• Entity Relationship Diagram
• MS Database with data
• Query
• Query results/output
• XML Record
• Illustrate with bulletin board example

3
Use Cases

• Note Include final version of scenario in your
  design document.
• Each group member should create
• One diagram with at least one case (one or more
  ovals in the diagram)
• Corresponding statements of pre-conditions, flows
  and post-conditions
• These should be combined into a single coherent,
  consistent document with a common list of use
  case actors

4
Use Cases, cont

• Label each set of use case diagrams, conditions,
  etc. with the name of their creator
• Ensure each of the queries/statements is
  expressed in some way in the use cases in your
  group

5
Entity Relationship Diagram

• for high-level graphical descriptions of of
  conceptual data models --especially for
  relational database systems.
• Entitytable, attributesfields,
• multi-field attributestable with values (and
  IDs, definitions)
• Relationshipsrelationships (1-1, 1-many)

6
MS Database with Data

• Screen capture(s) and database file
• Screen capture of the tables with sample data
• Screen capture oftheir relationship
• ALT PRNT Screen
• Grab

7
SQL Query

• One per person, based on one of the
  questions/statements provided in the scenario
  description
• Will be a conditional select statement, usually
  with a join component sometimes with a sub-query
  and/or a miscellaneous clause

8
Query Results

• Display the content of the fields selected from
  the tables.
• You can do this by inputting a query into MS
  Access (graphically or using SQL input), or
  manually carrying out the query HTML or
  MSAccess Report
• Use the principles from last weeks information
  on page/site design

9
XML Record

• One per student/group member
• For query results OR table contents
• http//www.xmlblueprint.com/XML-Markup-Languages.h
  tm xmlBluePrint XML Markup Languages
• Only needs to be well-formed (check manually, not
  with validator)
• Follow the example provided in Standards and XML
  (Oct. 24), Anatomy of a Document

10
Illustration Bulletin Board

• Use Cases
• Entity Relationship Diagram
• MS Database with data
• Query
• Query results/output
• XML Record

11
BBS Scenario

• Both students and teachers must log on, and
  supply their email addresses for security
  purposes. Messages that accumulate on the
  bulletin board have titles, dates/times, and also
  have attachments.
• Search the contents of the messages for a
  particular word.
• When were the most messages posted --day or night?

12
Use Case

• Actors
• Preconditions
• Flow
• Post-conditions

13
Entity Relationship Diagram
14
Database
15
(No Transcript)
16
Query

• SELECT users.first_name, users.last_name,
  message.title, message.date_time FROM users,
  messageWHERE users.ID message.user_IDAND
  message.content IS LIKE felixORDER BY
  message.date_time

17
Query

• SELECT COUNT() WHERE messages.time_date gt 18
  OR messages.time_date lt 7
• SELECT COUNT()WHERE messages.time_date lt 18 AND
  gt 7
• If query A produces a bigger number, more
  messages were sent during the night.
• Assumes that daytime begins at 700 AM and ends
  at 600 PM
• Assumes that time_date uses a 24 hour clock

18
Query Results

• Messages containing the word felix, ordered by
  date
• Help me John Smith, 21 March 2004
• RE Help me John Smith, 22 March 2004
• What is the name of that cat Al Chen, 26 July

19
XML

• ltusergt
• ltidgt12lt/idgt
• ltusernamegtjdoelt/usernamegt
• ltpasswordgtabc123lt/passwordgt
• ltemailgtjdoe_at_utoronto.calt/emailgt
• ltfirst_namegtJanelt/first_namegt
• ltlast_namegtDoelt/last_namegt
• lt/usergt