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, 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

5. 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, 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 week’s 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.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

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

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 ‘%felix%’ORDER BY message.date_time

17. 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

18. Query Results • Messages containing the word “felix,” ordered by date: Help meJohn Smith, 21 March 2004 RE: Help meJohn Smith, 22 March 2004 What is the name of that cat Al Chen, 26 July

19. XML <user> <id>12</id> <username>jdoe</username> <password>abc123</password> <email>jdoe@utoronto.ca</email> <first_name>Jane</first_name> <last_name>Doe</last_name> </user>