The Relational Data ModelOutlineSlide 3TerminologyDomainsSchemasInstancesExampleUpdatesSchemas and InstancesTwo Mathematical Definitions of RelationsPowerPoint PresentationTwo Definitions of RelationsFrom ODL to Relational SchemaAdding Non atomic AttributesSet AttributesModeling Collection TypesModeling RelationshipsOption #1HintBetter SolutionAdditional IssuesFrom E/R Diagrams to Relational SchemaEntity Sets to RelationsRelationships to RelationsMany-one RelationshipsHandling Weak Entity SetsModeling Subclass StructureOption #1: the ODL ApproachOption #2: the E/R ApproachOption #3: The Null Value ApproachThe Relational Data ModelMonday, October 2, 2000Outline•The relational model (3.1)•ODL to relational model (3.2)•E/R to relational model (3.3)•Subclasses to relational model (3.4)The Relational Data ModelDatabase Model(ODL, E/R)Relational SchemaPhysicalstorageODL definitionsDiagrams (E/R)Tables: column names: attributes rows: tuplesComplexfile organizationand index structures.TerminologyName Price Category Manufacturergizmo $19.99 gadgets GizmoWorksPower gizmo $29.99 gadgets GizmoWorksSingleTouch $149.99 photography CanonMultiTouch $203.99 household HitachituplesAttribute namesTable nameProducts:Domains•each attribute has a type•must be atomic type (why ? see later)•called domain •examples:–Integer–String–Real–…Schemas•Relational Schema:–Relation name plus attribute names–E.g. Product(Name, Price, Category, Manufacturer)–In practice we add the domain for each attribute•Database Schema–Set of relational schemas–E.g. Product(Name, Price, Category, Manufacturer) Vendor(Name, Address, Phone)Instances•An instance of a relational schema R(A1,…,Ak), is a relation with k attributes with values of corresponding domains•An instance of a database schema R1(…), R2(…), …, Rn(…), consists of n relations, each an instance of the corresponding relational schema.ExampleName Price Category Manufacturergizmo $19.99 gadgets GizmoWorksPower gizmo $29.99 gadgets GizmoWorksSingleTouch $149.99 photography CanonMultiTouch $203.99 household HitachiRelational schema:Product(Name, Price, Category, Manufacturer)Instance:UpdatesThe database maintains a current database state.Updates to the data: 1) add a tuple 2) delete a tuple 3) modify an attribute in a tupleUpdates to the data happen very frequently.Updates to the schema: relatively rare. Rather painful. Why?Schemas and Instances•Analogy with programming languages:–Schema = type–Instance = value•Important distinction:–Database Schema = stable over long periods of time–Database Instance = changes constantly, as data is inserted/updated/deletedTwo Mathematical Definitions of Relations•Relation as cartesian product–tuple = element of string x int x string x string–E.g. t = (gizmo, 19, gadgets, GizmoWorks)–Product is subset of string x int x string x string–Order in the tuple is important !•(gizmo, 19, gadgets, GizmoWorks)•(gizmo, 19 , GizmoWorks, gadgets)–No attributes•Relation as a set of functions–Fix the set of attributes •A={name , price, category, manufacturer}–A tuple is a function t:A Domains–Relation = set of tuples–E.g. –Order in a tuple is not important–Attribute names are important{name gizmo, price 19, category gadgets, manufacturer gizmoWorks}Two Definitions of Relations•We will switch back and forth between these two:–Relational schemas with attribute names–Positional tuples, without attribute namesFrom ODL to Relational SchemaStart simple: a class definition has only single valued attributesInterface product{ float price; string name; Enum {telephony, gadgets, books} category}Class becomes a relation, and every attribute becomes a relation attribute:Name Price CategoryGizmo $19.99 gadgetsProductAdding Non atomic AttributesName Currency Amount CategoryGizmo US$ 19.99 gadgetsPower Gizmo US$ 29.99 gadgetsPrice is a record: {string currency, float amount}ProductSet AttributesName SSN PhoneNumberFred 123-321-99 (201) 555-1234Fred 123-321-99 (206) 572-4312Joe 909-438-44 (908) 464-0028Joe 909-438-44 (212) 555-4000One option: have a tuple for every value in the set:Disadvantages?Interface person{ string name; integer SSN; set <integer> PhoneNumber;}Modeling Collection TypesHow can we model bags? Lists? Fixed length arrays?The problem becomes even more significant if a class has several attributes that are set types? Question: how bad is the redundancy for n set type attributes, each with possibly up to m values?Questions:Modeling RelationshipsInterface Product { attribute string name; attribute float price; relationship <Company> madeBy; }Interface Company { attribute string name; attribute float stock-price; attribute string address; } How do we incorporate the relationship madeBy into the schema?Option #1 Name Price made-by-name made-by-stock-price made-by-addressGizmo $19.99 gizmoWorks 0.0001$ MontezumaWhat’s wrong?HintInterface Product { attribute string name; attribute float price; relationship Company madeBy; }Interface Company { attribute string name; attribute float stock-price; attribute string address; relationship set <Product> makes; }Better Solution Name Price made-by-nameGizmo $19.99 gizmoWorksProduct relation: (assume: name is a key for company)Company relation: Name Stock Price AddressgizmoWorks $0.00001 MontezumaAdditional Issues1. What if there is no key?2. What if the relationship is multi-valued?3. How do
View Full Document