Transcript Database Application Assignment

Database Application
Assignment
FIS 1311; Nov. 28, 2005
Outline: Assignment
Components
•
•
•
•
•
•
•
Use Cases
Entity Relationship Diagram
MS Database with data
Query
Query results/output
XML Record
Illustrate with “bulletin board” example
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
Use Cases, con’t
• 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
Entity Relationship Diagram
• “for high-level graphical descriptions of
of conceptual data models --especially
for relational database systems.”
• Entity=table, attributes=fields,
• multi-field attributes=table with
values (and ID’s, definitions)
• Relationships=relationships (1-1, 1many)
MS Database with Data
• Screen capture(s) and database file
• Screen capture of the tables with
sample data
• Screen capture of
their relationship
• “ALT + PRNT Screen”
• “Grab”
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
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 week’s
information on page/site design
XML Record
• One per student/group member
• For query results OR table contents
• http://www.xmlblueprint.com/XML-MarkupLanguages.htm 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
Illustration: Bulletin Board
•
•
•
•
•
•
Use Cases
Entity Relationship Diagram
MS Database with data
Query
Query results/output
XML Record
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?
Use Case
•
•
•
•
Actors
Preconditions
Flow
Postconditions
Entity Relationship Diagram
Database
id
type
def’n
1
normal
If nec
2
urgent
asap
3
question clarif
Query
• SELECT users.first_name,
users.last_name, message.title,
message.date_time
FROM users, message
WHERE users.ID = message.user_ID
AND message.content IS LIKE
‘%felix%’
ORDER BY message.date_time
Query
• SELECT COUNT(*)
WHERE messages.time_date >= 18 OR
messages.time_date <= 7
• SELECT COUNT(*)
WHERE messages.time_date < 18 AND > 7
– If query A produces a bigger number, more
messages were sent during the night.
– Assumes that “daytime” begins at 7:00 AM and
ends at 6:00 PM
– Assumes that time_date uses a 24 hour clock
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
XML
<user>
<id>12</id>
<username>jdoe</username>
<password>abc123</password>
<email>[email protected]</email>
<first_name>Jane</first_name>
<last_name>Doe</last_name>
</user>