1 / 19

Database Application Assignment FIS 1311: Use Cases and Entity Relationship Diagram

This assignment requires the development of a conceptual model for a database application focusing on a bulletin board system. Students will create use cases, an Entity Relationship Diagram (ERD), and SQL queries to extract data. Each group member will contribute to the design document by developing at least one use case diagram with corresponding pre-conditions, flows, and post-conditions. Additionally, a sample database with data must be constructed, and relevant queries should be executed to display results. Final designs must be well-documented and include XML records for output.

kiley
Download Presentation

Database Application Assignment FIS 1311: Use Cases and Entity Relationship Diagram

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

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

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

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

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

More Related