1 / 18

Review SQL Exercise:

Review SQL Exercise: . Original Table: StudentTable. B. B. A. B. B. A. UPDATE StudentTable SET LetterScore = ’A’ WHERE Score >= ‘90’. UPDATE StudentTable SET LetterScore = ’B’ WHERE Score >= ‘80’. The second command undermines the result of the first command.

pahana
Download Presentation

Review SQL Exercise:

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. Review SQL Exercise: Original Table: StudentTable B B A B B A UPDATE StudentTable SET LetterScore = ’A’ WHERE Score >= ‘90’. UPDATE StudentTable SET LetterScore = ’B’ WHERE Score >= ‘80’. The second command undermines the result of the first command

  2. Database Development Process E-R Modeling: High level blueprint defining how data are stored structurally in the system What to look for in the interview? Define users’ need through interview How many entities? How the entities are related? (relationships)

  3. Class 2: E-R Modeling and Database Lab • E-R Data Model • The most popular technique for creating a data model. • E: entity (things) • R: relationship (among the entities) • + business rules restricting the flow of the data • Data Model: • Read Problem Solving Guide: (p89a) Immanuel Kant, Data Modeler • Attempts to model the “real world” • Simply a model of what the data modeler perceives

  4. 5 minutes free write Social Implications of DB: • Relate any of Kant’s ideas to what you learned from reading “For Some, Online Persona Undermines Resume” • PRINT your name on Free Write and hand it in

  5. More exercise • Book 23 a) Create a E-R Diagram to relate all the information in the form

  6. Translating E-R diagram into Tables • How to translate the three basic types of relationships into data tables and establish relationships? • One to Many (1:N) • Many to Many (N:M) • One to One (1:1)

  7. Translating E-R diagram into Tables (1:N) Key Step 1: Create one table for each entity Key

  8. Translating E-R diagram into Tables (1:N) • Step 2: Add Foreign Key Column to the “many entity” to represent relationship

  9. Step 3: edit relationship • Go to Tools  Relationships • Add two entities • Drag the “advisor name” of advisor table to the “advisor name” of student table. • Close relationships

  10. Many-to-Many Relationship • Now the rule changed • One advisor can supervise many students • One student can have many adviser

  11. Foreign Key doesn’t work for N:M relationship

  12. Solution

  13. Exercise: • Revise the relationship of previous exercise in Access and input the above records

  14. 1:1 Relationship • How to convert into tables?

  15. Exercise • Based on your E-R diagram in 23(a), construct tables and relationships in Access. Add at least three records for each table.

  16. Importance of User Review • Users are the final judges of what data the database should contain and how records relate to one another • Easiest time to change database structure is during data modeling stage • Once database has been constructed, loaded with data, and application forms, reports, queries, and application programs created, changing a relationship means weeks of work!

  17. How should you review as a user? (Cont) When database developed for your use, you must carefully review data model • If you don’t understand any aspect of it, ask for clarification until you do • Data model must accurately reflect your view of business • Do not proceed unless you are 100% comfortable with the model

  18. Key Terms and Concepts

More Related