ER2XML ER to XML Translator

1. ER2XMLER to XML Translator Min Song, Jinho Kang Project Advisor : Murali Mani

2. Outline • Introduction • Database design process • Benefit from using XML as logical data model • Limitation of existing conceptual models • ERex (ER extended for XML) • XGrammar • ER2XML • Translation examples • Conclusion and future development

3. DB design process • Phase 1 : Conceptual design phase • Phase 2 : Logical design phase • Phase 3 : Physical translation phase • Our focus Automatic translation from conceptual model to logical model

4. XML : Promising logical data model • Benefit 1 : Necessity Lingua franca for information exchange over the web • Benefit 2 : Capability Well suited for present day application with features like 1) Union types Example : Entity Person can have City and State information OR ZIP information, but not both. 2) Ordered relationship

5. Limitation of existing conceptual models • Existing conceptual models 1. ER (Entity Relationship) Model 2. UML (Unified Modeling Language) 3. ORM (Object Roll Modeling) • All of these models do not have modeling capabilities to represent union types. • Disables main feature provided by XML.

6. Extensions to the ER Model • Given features of ER 1. ER models : Representative of real world database application. 2. Nomalization – No redundancy • Extended features to ER 1. Structural specification - Categories 2. Constraint specification - Coverage constraints (Total & Exclusive) - Order constraints

7. Relationship type similar to ISA relationship type PersonCity and PersonZip are category entities of an entity Person Each category entity can have its own key attribute Categories in EReX

8. Coverage Constraints • Coverage constraints are specified on entity types or roles. • Total coverage constraint : PersonCity U PersonZip = Person Interpretation : All instances of PersonCity and all instances of Personzip sums up to all instances of Person • Exclusive coverage constraint : PersonCity ∩ PersonZip = Person Interpretation : There is no instance in Person that has city, state and zip all together.

9. XGrammar Define • A schema in XGrammar • Denoted by a 6-tuple G = (N,E,A,S,P, ∑) • N is a finite set of non-terminal symbols • E is a finite set of element names • A is a finite set of attribute names • S is set of start symbols • P is set of production rules of the form X → x (RE) • ∑ is the set of constraints.

11. ER2XML • Developed in Java which provides platform independent environment. • Provides easy way to generate correct schema from complex EReX diagrams. • Fully adapts all features of XGrammar. • Displays intermediate translation steps and results to back trace the problem

12. ER2XML - Features

13. ER2XML – Coverage Constraints • User can create total or exclusive coverage constraints by combining multiple entities or roles. • Verification of the constraints will be shown on the command screen.

14. ER2XML – Translation • Brief translation procedure 1. Initialization of symbols - N, E, A, S, P and ∑ 2. Translation of relationship types 3. Removal of unnecessary symbols 4. Capture exclusive coverage constraints 5. Capture total coverage constraints 6. Distinguish ROOTS among symbols • Some coverage constraints might not be captured in XGrammar.

15. ER2XML Schema generated by ER2XML is consistent with example schema.

16. ER2XML – Complex EReX

17. ER2XML – Complex EReX • N = {Root, Person, Bank, Checking, Savings, Loan company, Card company, Account_info, Credit_history, Banks with, Apply_for, Rated, Request} • E = {root, person, bank, checking, savings, loan company, card company, credit_history, account_info, banks with, apply_for, rated, request} • A = {ssn, city, state, zip, pho-number, routing_number, BID, CH-acc, ch-bal, SV-acc, sv-bal, loan-number, LID, CID, C-acc, balance, limit, BRef, decision, LRef, points, CRef, LRef} • S = {Root} • P = {Root -> root(Person*, Bank*, Loan company*) • Person -> person(@ssn,Banks with[*], Apply_for[+], Rated[*], ((@street,@city,@state,@zip) + @pho-number),(Checking[+] + Savings[+])) • Bank -> bank(@routing_number,@BID,Request[+]) • Checking -> checking(@CH-acc,@ch-bal) • Savings -> savings(@SV-acc,@sv-bal) • Loan company -> loan company(@loan-number,@LID,Card company[1], (Credit_history[+] + Account_info[+])) • Card company -> card company(@CID) • Account_info -> account_info(@C-acc,@balance) • Credit_history -> credit_history(@limit) • Banks with -> banks with(@BRef) • Apply_for -> apply_for(@decision,@LRef) • Rated -> rated(@points,@CRef) • Request -> request(@LRef) • } • IDREF constarints: • {BRef::IDREF~(Bank) • LRef::IDREF~(loan company) • CRef::IDREF~(card company) • LRef::IDREF~(loan company) • } • Key Constraints: • {key(Account_info)=(@C-acc) • key(Credit_history)=(@limit) • key(Person)=(@ssn) • key(Checking)=(@CH-acc) • key(Loan company)=(@loan-number) • key(Bank)=(@routing_number) • key(Banks with)=(parent::person/@ssn,BRef) • key(Apply_for)=(parent::person/@ssn,LRef) • key(Rated)=(parent::person/@ssn,CRef) • key(Request)=(parent::bank/@routing_number,LRef) • }

18. Any EReX Diagram can be translated into XML schema with its maximum expressiveness using ER2XML. Some coverage constraints might not be captured by XGrammar and ER2XML. It is guaranteed that resulting schema has no redundancy as long as EReX has no redundancy. More simplified and enhanced GUI needs to be developed. “Generate XML” will be a big plus to current ER2XML. More insightful comments and reasoning for each translation step will make ER2XML a great tool to learn EReX. Conclusion Future Works

19. References • EReX: A Conceptual Model for XML, M. Mani • Constraint Specification for XML: A Closer Look (Late Breaking News), M. Mani • Data Modeling using XML Schemas, M. Mani.