COMP 430 Intro. to Database Systems - Rice University

COMP 430 Intro. to Database Systems Entity-Relationship Diagram Basics Slides use ideas from Chris R. Goal: Agree on DB structure to implement. What entities to model How entities related What constraints exist in domain How to achieve good design (Logical) Entity-Relationship Diagrams name name category price Product Makes Company

The Entity-Relationship model toward a unified view of data Peter Chen, 1976 Precise enough for technical decisions Abstracted enough to omit some implementation details Abstracted enough for non-technical people Primary key minimal set of attributes that uniquely identifies entity Attributes of entity sets name name category price Product Entity sets classes/types of objects Entities individual objects Makes

Company Relationship between entity sets name category name price Makes Company Product stockprice Buys Employs Person address name ssn

Mathematical definitions Sets A, B: A={1,2,3}, B={a,b,c,d} A 1 B a 2 b 3 c d Mathematical definitions Sets A, B: A={1,2,3}, B={a,b,c,d} Cross-product A B: The set of all (a,b) pairs A B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c), (2,d), (3,a), (3,b), (3,c), (3,d)} A 1

B a 2 b 3 c d Mathematical definitions Sets A, B: A={1,2,3}, B={a,b,c,d} Cross-product A B: The set of all (a,b) pairs A B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c), (2,d), (3,a), (3,b), (3,c), (3,d)} Relationship R: A subset of A B R = {(1,a), (2,c), (2,d), (3,b)} A 1 B

a 2 b 3 c d name name category price Product Makes Company Product and Company are sets of entities. Entities uniquely identified by primary keys. Makes is a relationship a subset of Product Company.Company.

Tuples uniquely identified by pairs of Product & Companys keys. Company Product name price category name Product Company.p Company. Company.Company Company.c p.name p.price c.category c.name Gizmo 19.99 Electronics GizmoWorks

Gizmo 19.99 Electronics GizmoWorks Powergizmo 39.99 Electronics GadgetCorp Powergizmo 39.99 Electronics GizmoWorks Gadget 19.99 Toys Gadget 19.99 Toys GizmoWorks Gizmo

19.99 Electronics GadgetCorp Powergizmo 39.99 Electronics GadgetCorp Gadget 19.99 Toys GadgetCorp name name category price Product Makes Company Company

Product name price category name Product Company.p Company. Company.Company Company.c p.name p.price c.category c.name Gizmo 19.99 Electronics GizmoWorks Gizmo 19.99 Electronics GizmoWorks

Powergizmo 39.99 Electronics GadgetCorp Powergizmo 39.99 Electronics GizmoWorks Gadget 19.99 Toys Gadget 19.99 Toys GizmoWorks Gizmo 19.99 Electronics GadgetCorp Powergizmo

39.99 Electronics GadgetCorp Gadget 19.99 Toys GadgetCorp Makes name name category price Product Makes Company p.name p.price c.category

c.name Gizmo 19.99 Electronics GizmoWorks Powergizmo 39.99 Electronics GizmoWorks Gadget 19.99 Toys GadgetCorp Likes ssn name Person name

WorksFor address Company stockprice OwnsStock Can have multiple relationships between same entity sets. Each is a subset of Person Company. Company.Company with primary key (ssn, name). ssn Person name Friend address Can have relationship between single entity set. A subset of Person Company. Company.Person with primary key (ssn, ssn). Relationships can have attributes since name name

category price Product Makes Company Relationship attributes are implicitly unique per (p.name, c.name). Relationship vs. entity date name p_id category price Product Purchased Person Relationship attributes are implicitly unique per (name, p_id). What if we dont want uniqueness?

Relationship vs. entity date purch_id quantity p_id name category price Purchase ProductOf Product BuyerOf Person Representing as an entity allows multiple Purchases for each Product-Person combination. Activity Draw ER diagram Authors have IDs and names. They write books. Books have ISBNs and

titles. The library keeps track of how many copies it has of the book. Each book is written by authors and published by a publisher. We want to know every time it is checked out by a borrower. Borrowers have a library card number, name, and address. They can check out a book on a particular date. Publishers have an ID, name and address. They publish books.

Recently Viewed Presentations

  • Swaziland experience CommART - IAS 2017

    Swaziland experience CommART - IAS 2017

    HIV and NCDs: Overlapping Epidemics. ... (CVDRF) screening and management into a busy urban HIV clinic in Swaziland . In Phase 1, ART patients > 40 years were screened for hypertension, diabetes, high cholesterol and tobacco use ... Swaziland experience...
  • I GIVE THANKS (Brian Thiessen)

    I GIVE THANKS (Brian Thiessen)

    I GIVE THANKS (Brian Thiessen) Refrein: I will arise and give thanks to You Lord my God Ik zal opstaan en U, mijn God, danken And Your name I will bless With my whole heart En Uw naam zal ik...
  • Basic Econometrics in Transportation

    Basic Econometrics in Transportation

    A lucid paragraph contains a topic sentence and clearly related supporting sentences. basic guidelines for paragraph design: 1- Cover only one main point or idea in each paragraph. 2- Each sentence should establish or support the topic of the paragraph....
  • Human Factors: Threat & Error Management

    Human Factors: Threat & Error Management

    [email protected] or 707-968-5109 if you have any questions or need additional information. Our open-enrollment training schedule for Human Factors and Force Encounters courses is located on our website. We are also available for in-house sessions for your department or agency....
  • Fina 224 (3) Islamic Art and Architecture

    Fina 224 (3) Islamic Art and Architecture

    RELG 202 MYTHS, LEGENDS AND SCRIPTURES OF THE NEAR EAST Dr Niall Christie Office Hours: Monday and Wednesday, 10:00-11:00 a.m., or by appointment Copy of outline on the web at:
  • Descubrimientos Del Manto De La Virgen De Guadalupe

    Descubrimientos Del Manto De La Virgen De Guadalupe

    The image of the Virgin was imprinted on the Tilma ( a kind of poncho made of cactus fibre ) and was hidden from Juan Diego as he collected - in the middle of winter, Castillian Roses (the secret sign...
  • SIER RACL UB  Protect Americas Environment. For our

    SIER RACL UB Protect Americas Environment. For our

    HOW THIS FACT SHEET WAS DEVELOPED. The Sierra Club's Toxics Committee collaborated with scientists with particular expertise in this area to create the CEC Fact Sheet and this Powerpoint for sharing information about the health hazards posed by Contaminants of...
  • Surgery of gastrointestinal tract - TOP Recommended Websites

    Surgery of gastrointestinal tract - TOP Recommended Websites

    Garamond Arial Wingdings Tahoma Prúd 1_Prúd Diseases of Large Bowel Snímka 2 Snímka 3 Diverticulosis of the Colon I. Diverticulosis of the Colon II. Diverticulosis of the Colon III. Diverticulosis of the Colon IV. Diverticulosis of the Colon V. Snímka...