database design using entity relationship models l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design Using Entity-Relationship Models PowerPoint Presentation
Download Presentation
Database Design Using Entity-Relationship Models

Loading in 2 Seconds...

play fullscreen
1 / 21

Database Design Using Entity-Relationship Models - PowerPoint PPT Presentation


  • 265 Views
  • Uploaded on

Database Design Using Entity-Relationship Models. Mapping E-R models into relations Four common data structures. Mapping E-R Models to Tables. We may not be familiar with all notations There may be E-R constructs that cannot be translated directly Multi-valued attributes

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Database Design Using Entity-Relationship Models' - placido


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
database design using entity relationship models

Database Design Using Entity-Relationship Models

Mapping E-R models into relations

Four common data structures

mapping e r models to tables
Mapping E-R Models to Tables
  • We may not be familiar with all notations
  • There may be E-R constructs that cannot be translated directly
    • Multi-valued attributes
    • Ternary relationships
  • We may need to change the data model
    • Change a relationship into an entity
    • Change an entity into an attribute
    • Collapse two entities
    • Make a strong entity weak
basic conversion rules
Basic ConversionRules
  • A database conforming to an E-R diagram can be represented by a collection of tables.
  • Converting an E-R diagram to a table format is the basis for deriving a relational database design from an E-R diagram.
    • Primary keys allow entities and relationships to be expressed uniformly as tables.
    • For each entity and many-to-many relationship there is a unique table, named after it.
    • Each table has columns (generally corresponding to attributes) with unique names.
representing entities as tables
Representing Entities as Tables
  • A strong entity set reduces to a table with the same attributes

CUSTOMER (CustNumber, CustName, Address, City,

State, Zip, ContactName, PhoneNumber)

representing entities
Representing Entities
  • We then may need to normalize the table if it is not in DK/NF
  • De-normalize?

CUSTOMER (CustNumber, CustName, Address, City, State, Zip, ContactName, PhoneNumber)

CUSTOMER (CustomerNumber, Address, Zip, ContactName)

ZIP-TABLE (Zip, City, State)

CONTACT (ContactName, PhoneNumber)

Interrelation (integrity) constraints:

CUSTOMER[Zip] Í ZIP-TABLE[Zip]

CUSTOMER[ContactName] Í CONTACT[ContactName]

representing weak entities
Representing Weak Entities
  • Weak entity sets must be documented by referential integrity constraints
  • An ID-dependent weak entity set becomes a table that includes a column for the primary key of the identifying strong entity
  • The table corresponding to the weak relationship is redundant
representing has a relationships

Foreign key!

Representing HAS-A Relationships
  • 1:1 - Place key of one relation into other
    • Perhaps they should be combined!
  • 1:N - Place key of parent into child
representing has a relationships8

Intersection

relation

Error!

Representing HAS-A Relationships
  • M:N - Build a table with columns for primary keys of two participating entity sets
    • May add descriptive attributes of relationship set
recursive relationships
Recursive Relationships
  • Same as non-recursive relationships
  • One participant instead of two
  • E.g., 1:1
    • Remember two alternatives
    • Foreign key can have NULL values (when?)
recursive relationships10
Recursive Relationships
  • What about 1:N and M:N recursive?
    • Give an example of each, not from book :)
    • How many tables do you end up with, in each case?
    • How is this different from the non-recursive case?
    • What is the domain of the foreign key?
higher order relationships
Higher-order Relationships
  • Treat as combination of binary relationships
  • Binary constraints must be enforced with business rules
  • Types of constraints:
    • MUST (ORDER:CUSTOMER:SALESPERSON)
      • ORDER(OrdNo, CustNo, SalespersonNo, ...)
      • CUSTOMER(CustNo, SalespersonNo, ...)
      • SALESPERSON(SalespersonNo, ...)
    • MUST NOT (PRESCRIPTION:DRUG:PATIENT)
    • MUST COVER (AUTO:REPAIR:TASK)
representing is a relationships
Representing IS-A Relationships
  • Specialization method
    • Form a table for each sub-type entity
    • No table for generalized (super-type) entity
    • Common attributes are repeated
representing is a relationships13

Usually

same key

Need this?

Representing IS-A Relationships
  • Generalization method
    • Form a table for the super-type entity
    • Form a table for each sub-type entity (include primary key of generalized entity set, 1:1)
    • Common attributes are inherited
trees hierarchies
Trees (hierarchies)
  • Nodes are entities
  • Only 1:N relationships (branches)
  • Each child has a unique parent
    • Root: unique node without parent
    • Siblings: children sharing parent
  • Obvious relational representation

Root

simple networks
Simple Networks
  • Only 1:N relationships
  • But a child can have more than one parent (of different types)
  • Obvious relational representation
complex networks
Complex Networks
  • A child can have multiple parents of the same type
  • At least one N:M relationship
  • Need intersection relations(s)
bills of materials
Bills of Materials
  • A special case of networks
  • M:N recursive relationships
  • Foreign keys in intersection relation have same domain
another example overhead
Another Example: Overhead
  • EMPLOYEE: SSN, Fname, Minit, Lname, BirthDate, Address, JobTypeSECRETARY: SSN, TypingSpeedTECHNICIAN: SSN, TGradeENGINEER: SSN, EngType
  • EMPLOYEE: SSN, Fname, Minit, Lname, BirthDate, Address, JobType, TypingSpeed, Tgrade, EngType
another example overhead20
Another Example: Overhead
  • CAR: VehicleID, LicensePlateNo, Price MaxSpeed, NoOfPassengersTRUCK: VehicleID, LicensePlateNo, Price, NoOfAxles, Tonnage
  • PART: PartNo, Description, MFlag, DrawingNo, ManufactureDate, BatchNo, PFlag, SupplierName, ListPrice
another example overhead21
Another Example: Overhead
  • PERSON: SSN, Fname, Minit, Lname, BDate, No, Street, AptNo, City, State, ZipEMPLOYEE: SSN, Salary, Rank, Office, PhoneSTUDENT: SSN, Class, GflagGRAD_DEGREES: SSN, Year, Degree, CollegeINSTRUCTOR_RESEARCHER: SSN