CMSC424 Database Design Lecture 3 CMSC424 Spring 2005 Database Design Steps Entity relationship Model Typically used for conceptual database design Three Levels of Modeling info Conceptual DB design Conceptual Data Model Logical DB design Logical Data Model Relational Model Typically used for logical database design Physical DB design Physical Data Model CMSC424 Spring 2005 2 Review Entity Relationship Model Basics E a1 R 1 E b1 an c1 bm ck E1 Entity set R Relationship set a 2 Attribute primary key if underlined CMSC424 Spring 2005 Relationship Cardinalities One to One One to Many customer has account customer has account One customer can be associated with many accounts CMSC424 Spring 2005 Relationship Cardinalities One to One One to Many Many to One customer has account customer has account customer has account Many customers can be associated with one account CMSC424 Spring 2005 Relationship Cardinalities One to One One to Many Many to One customer has account customer has account customer has account customer has account Many to Many CMSC424 Spring 2005 Relationship Set Keys Is cust id account number a candidate key Depends access date number cust id customer has account If one to many relationship as shown account number is a candidate key A given customer can have many accounts but at most one account holder per account allowed So account number sufficient to uniquely identify a relationship CMSC424 Spring 2005 Thoughts Nothing about actual data How is it stored No talk about the query languages How do we access the data Semantic vs Syntactic Data Models Remember E R Model is used for conceptual modeling Many conceptual models have the same properties They are much more about representing the knowledge than about database storage querying CMSC424 Spring 2005 Thoughts Basic design principles Faithful Must make sense Satisfies the application requirements Models the requisite domain knowledge If not modeled lost afterwards Avoid redundancy Potential for inconsistencies Go for simplicity Typically an iterative process that goes back and forth CMSC424 Spring 2005 Relational Data Model Introduced by Ted Codd late 60 s early 70 s Before Network Data Model Cobol as DDL DML Very contentious Database Wars Charlie Bachman vs Mike Stonebraker Relational data model contributes 1 2 3 4 Separation of logical physical data models data independence Declarative query languages Formal semantics Query optimization key to commercial success 1st prototypes Ingres CA Postgres Illustra Informix IBM System R Oracle DB2 CMSC424 Spring 2005 Key Abstraction Relation Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Terms Tables aka Relations Why called Relations CMSC424 Spring 2005 Why Called Relations Mathematical relations Given sets R 1 2 3 S 3 4 R S 1 3 1 4 2 3 2 4 3 3 3 4 A relation on R S is any subset of R S e g 1 4 3 4 Database relations Given attribute domains Branches Downtown Brighton Accounts A 101 A 201 A 217 Balances R Account Branches Accounts Balances Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 CMSC424 Spring 2005 Relations Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Considered equivalent to Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Relational database semantics defined in terms of mathematical relations CMSC424 Spring 2005 Relations Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Considered equivalent to Terms Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Tables aka Relations Rows aka tuples Columns aka attributes Schema e g Acct Schema bname acct no balance CMSC424 Spring 2005 Definitions 1 Relation Schema or Schema A list of attributes and their domains We will require the domains to be atomic Programming language equivalent A variable e g x E g account account number branch name balance Relation Instance A particular instantiation of a relation with actual values Will change with time bname acct no balance Programming language equivalent Value of a variable Downtown Brighton Brighton A 101 A 201 A 217 CMSC424 Spring 2005 500 900 500 Rest of the Class Converting from an E R diagram to a relational schema Remember We still use E R models for conceptual modeling of the database Relational Algebra Data retrieval language CMSC424 Spring 2005 E R Diagrams Relations Convert entity sets into a relational schema with the same set of attributes Customer cname ccity bname bcity Customer Schema cname ccity cstreet cstreet assets Branch Schema bname bcity assets Branch CMSC424 Spring 2005 E R Diagrams Relations Convert relationship sets also into a relational schema Remember A relationship is completely described by primary keys of associate entities and its own attributes acct no balance Account Schema acct no balance Account access date Depositor Schema cname acct no access date Depositor Customer cname ccity Customer Schema cname ccity cstreet cstreet Well Not quite We can do better It depends on the relationship cardinality CMSC424 Spring 2005 E R Diagrams Relations Say One to Many Relationship from Customer to Account Many accounts per customer acct no balance Account access date Account Schema acct no balance cname access date Depositor Customer cname ccity Customer Schema cname ccity cstreet cstreet Exactly same information fewer tables CMSC424 Spring 2005 E R Diagrams Relations E R Entity Sets a1 E Relational Schema 1 E a1 an an CMSC424 Spring 2005 E R Diagrams Relations E R Entity Sets a1 E Relational Schema 1 E a1 an an Relationship Sets E a1 R 1 E b1 an c1 R a1 b1 c1 cn 2 bm a1 E1 s key b1 E2 s key ck c1 ck attributes of R Not the whole story for Relationship Sets CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 R 2 b1 an c1 n m E bm Ec1k a1 an E2 b1 bm R a1 b1 c1 cn CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 2 b1 an c1 n m E bm c R E1k a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm n 1 CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 n m n 1 R 1 n R 2 b1 an c1 R E bm Ec1k a1 an E2 b1 bm R a1 b1 c1 cn E1 a1 an b1 c1 cn E2 b1 bm E1 a1 an E2 b1 bm a1 c1 cn CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 n 1 1 n 2 b1 an c1 n m E bm ck R E1 a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm R E1 a1 an E2 b1 bm a1 c1 cn R
View Full Document
Unlocking...