Entity-Relationship Diagrams and the Relational Model

Only available on StudyMode
  • Download(s) : 204
  • Published : May 26, 2013
Open Document
Text Preview
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 represented pictorially (ER diagrams). • Can map an ER diagram into a relational schema.

ER Model Basics

ssn

name

lot

Employees

• Entity: Real-world object, distinguishable from other objects. An entity is described using a set of attributes. • Entity Set: A collection of similar entities. E.g., all employees. – All entities in an entity set have the same set of attributes. (Until we consider hierarchies, anyway!) – Each entity set has a key (underlined). – Each attribute has a domain.

ER Model Basics (Contd.)
name ssn Employees lot Works_In since did dname budget Departments

ER Model Basics (Cont.)
name ssn lot

dname did budget Departments

since

Employees

supervisor
Works_In

subordinate

• Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department. – relationships can have their own attributes.

Reports_To

• Relationship Set: Collection of similar relationships.
– An n-ary relationship set R relates n entity sets E1 ... En ; each relationship in R involves entities e1 ∈ E1, ..., en ∈ En

• Same entity set can participate in different relationship sets, or in different “roles” in the same set.

name ssn lot

since did

dname budget

…to be clear…
• Recall that each relationship has exactly one element of each Entity Set – “1-M” is a constraint on the Relationship Set, not each relationship

Key Constraints
An employee can work in many departments; a dept can have many employees.

Employees

Manages

Departments

Works_In since

• Think of 1-M-M ternary relationship

In contrast, each dept has at most one manager, according to the key constraint Many-to- 1-to Many on Manages. Many

1-to-1

Participation Constraints
• Does every employee work in a department? • If so, this is a participation constraint – the participation of Employees in Works_In is said to be total (vs. partial) – What if every department has an employee working in it?

Weak Entities
A...
tracking img