Overview Weak entity sets and keys Design principles CS 235: - PDF document

Overview � Weak entity sets and keys � Design principles CS 235: � Examples Introduction to Databases Svetlozar Nestorov Lecture Notes #3 CS 235: Intro to DB; S. Nestorov 1–2 Example: Email Addresses Weak Entity Sets � Sometimes an E.S. E ’s key comes not � Email address = user name + host name, e.g., (completely) from its own attributes, but from evtimov @ cs.uchicago.edu . the keys of one or more E.S.’s to which E is � Email address corresponds to a user name on a linked by a supporting many-one relationship. particular host. � Called a weak E.S. � Once on a host, you only need user name, e.g., � Represented by putting double rectangle evtimov around E and a double diamond around each � Key for an email = the user name at the host supporting relationship. (which is unique for that host only) + the IP � Many-one-ness of supporting relationship address of the host (which is unique globally). (includes 1-1) essential. • With many-many, we wouldn't know which entity provided the key value. CS 235: Intro to DB; S. Nestorov 1–3 CS 235: Intro to DB; S. Nestorov 1–4 Email Addresses Example: Chain of Weakness name � Consider IP addresses consisting of a name primary domain (e.g., edu ), subdomain (e.g., uchicago ), and host (e.g., cs ). Users @ Hosts � Design issue: Under what circumstances name name name could we simply make user-name and host- Sub- name be attributes of email, and dispense Primary Hosts Domains Domains with the weak E.S.? In1 In2 CS 235: Intro to DB; S. Nestorov 1–5 CS 235: Intro to DB; S. Nestorov 1–6 1

Chain of Keys All Connecting Entity Sets Are Weak � Key for primary domain = its name. � Key for sub-domain = its name + name of BBP primary domain. � Key for host = its name + key of sub-domain = its name + name of sub-domain + name of The- The- The- primary domain. Beer Price Bar Beers Bars Prices manf name name addr price CS 235: Intro to DB; S. Nestorov 1–7 CS 235: Intro to DB; S. Nestorov 1–8 Constraints All Connecting Entity Sets Are Weak � In this special case, where bar and beer � Part of the schema determine a price, we can omit price � Keys from the key, and remove the double � Single value constraints diamond from ThePrice. � Referential integrity constraints � Better: price is an attribute of BBP. � Domain constraints � General constraints CS 235: Intro to DB; S. Nestorov 1–9 CS 235: Intro to DB; S. Nestorov 1–10 Single Value Constraints Referential Integrity � Each attribute has a single atomic value � Exactly one value • No set attributes! • Compare with at most one for single-value constraints. � Many-one, one-one relationships CS 235: Intro to DB; S. Nestorov 1–11 CS 235: Intro to DB; S. Nestorov 1–12 2

Design Principles Design Principles � Setting: client has (possibly vague) idea � Faithfulness to requirements. of what he/she wants. You must design a • Remember the design schema should database that represents these thoughts enforce as many constraints as possible. and only these thoughts. Don't rely on future data to follow � Avoid redundancy. assumptions. • Wastes space and encourages • Example: If registrar wants to associate inconsistency. only one instructor with a course, don't • Intuition: something is redundant if it could allow sets of instructors and count on be hidden from view, and you could still departments to enter only one instructor figure out what it is from the other data. per course. � Avoid intermediate concepts. CS 235: Intro to DB; S. Nestorov 1–13 CS 235: Intro to DB; S. Nestorov 1–14 Good and Bad Design Good and Bad Design Good? name name addr Bad? Beers ManfBy Manfs name name Beers ManfBy Manfs Bad? manf name � Question: Why is it OK to have Beers with just its key as attribute? Why not manf make set of beers an attribute of Beers addr manufacturers? CS 235: Intro to DB; S. Nestorov 1–15 CS 235: Intro to DB; S. Nestorov 1–16 Exercise Problem 2 Exercise Problem 3 � E/R diagrams � Multiway relationships CS 235: Intro to DB; S. Nestorov 1–17 CS 235: Intro to DB; S. Nestorov 1–18 3