Review SQL Exercise:

1
Review SQL Exercise
Original Table StudentTable
B
A
B
B
A
B
UPDATE StudentTable SET LetterScore A WHERE
Score gt 90.
UPDATE StudentTable SET LetterScore B WHERE
Score gt 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 Kants 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 (1N)
• Many to Many (NM)
• One to One (11)

7
Translating E-R diagram into Tables (1N)
Key

Step 1 Create one table for each entity

Key
8
Translating E-R diagram into Tables (1N)

• 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 doesnt work for NM relationship
12
Solution
13
Exercise

• Revise the relationship of previous exercise in
  Access and input the above records

14
11 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 dont 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