Entity-Relationship Diagrams and the Relational Model
Review Entity-Relationship Diagrams and the Relational Model
CS 186, Fall 2007, Lecture 2 R & G, Chaps. 2&3
A relationship, I think, is like a shark, you know? It has to constantly move forward or it dies. And I think what we got on our hands is a dead shark.
Woody Allen (from Annie Hall, 1979)
• Why use a DBMS? OS provides RAM and disk
Review
• Why use a DBMS? OS provides RAM and disk
– Concurrency – Recovery – Abstraction, Data Independence – Query Languages – Efficiency (for most tasks) – Security – Data Integrity
Data Models
• DBMS models real world • Data Model is link between user’s view of the world and bits stored in computer • Many models exist • We think in terms of..
– Relational Model (clean and common) – Entity-Relationship model (design) – XML Model (exchange)
Student (sid: string, name: string, login: string, age: integer, gpa:real)
10101 11101
Why Study the Relational Model?
• Most widely used model. • “Legacy systems” in older models
– e.g., IBM’s IMS
Steps in Database Design
• Requirements Analysis – user needs; what must database do? • Conceptual Design – high level description (often done w/ER model) – Rails encourages you to work here • Logical Design – translate ER into DBMS data model – Rails requires you to work here too • Schema Refinement – consistency, normalization • Physical Design - indexes, disk layout • Security Design - who accesses what, and how
• Object-oriented concepts merged in
– “Object-Relational” – two variants
• Object model known to the DBMS • Object-Relational Mapping (ORM) outside the DBMS
– A la Rails
• XML features in most relational systems
– Can export XML interfaces – Can provide XML storage/retrieval
Conceptual Design
• What are the entities and relationships in the enterprise? • What information about these entities and relationships should we store in the database? • What integrity constraints or business rules hold? • A database `schema’ in the ER Model can be
CS 186, Fall 2007, Lecture 2 R & G, Chaps. 2&3
A relationship, I think, is like a shark, you know? It has to constantly move forward or it dies. And I think what we got on our hands is a dead shark.
Woody Allen (from Annie Hall, 1979)
• Why use a DBMS? OS provides RAM and disk
Review
• Why use a DBMS? OS provides RAM and disk
– Concurrency – Recovery – Abstraction, Data Independence – Query Languages – Efficiency (for most tasks) – Security – Data Integrity
Data Models
• DBMS models real world • Data Model is link between user’s view of the world and bits stored in computer • Many models exist • We think in terms of..
– Relational Model (clean and common) – Entity-Relationship model (design) – XML Model (exchange)
Student (sid: string, name: string, login: string, age: integer, gpa:real)
10101 11101
Why Study the Relational Model?
• Most widely used model. • “Legacy systems” in older models
– e.g., IBM’s IMS
Steps in Database Design
• Requirements Analysis – user needs; what must database do? • Conceptual Design – high level description (often done w/ER model) – Rails encourages you to work here • Logical Design – translate ER into DBMS data model – Rails requires you to work here too • Schema Refinement – consistency, normalization • Physical Design - indexes, disk layout • Security Design - who accesses what, and how
• Object-oriented concepts merged in
– “Object-Relational” – two variants
• Object model known to the DBMS • Object-Relational Mapping (ORM) outside the DBMS
– A la Rails
• XML features in most relational systems
– Can export XML interfaces – Can provide XML storage/retrieval
Conceptual Design
• What are the entities and relationships in the enterprise? • What information about these entities and relationships should we store in the database? • What integrity constraints or business rules hold? • A database `schema’ in the ER Model can be