Database Design Modeling with ERD Administration Guide

1. Week 8October 19 Database Design Modeling with ERD

2. Administration • Data Administrator (DA) – management of the data resources, including the database planning, development, and maintenance of standards, policies and procedures, and conceptual and logical database design • Database Administrator (DBA) – management of the physical realization of a database system, including physical database design and implementation, setting security and integrity controls, monitoring system performance, and reorganizing the database (when necessary)

3. Database Design • Data modeling • Understanding the meaning of data • Identify the user’s perspective of data • Identify the data themselves • Identify the applications supported by the data • Communication information requirements • Diagram with ERD (entity-relationship diagram) Satisfying the information needs of the organization

4. Optimal Logical Design Criteria • Structural validity - reflects the enterprise • Simplicity - ease of understanding • Expressability - distinguishability of data • Nonredundancy - exclusion of extraneous information • Shareability - nonexclusive data • Extensibility - support future information requirements • Integrity - consistency with organization’s information use and management • Diagrammatic representation - ability to graphically model data

5. Logical vs. Physical Design • Logical • Defines the whats (e.g., what information needs to be present) • Physical • Defines the hows (e.g., how data will be stored) What How Sequence

6. Fact-Finding Techniques • Examining documents • Interviewing • Observing the enterprise in operation • Research • Questionnaires

7. Design Tools Relational database design • Entity relationship diagram (ERD) • Relations, relationships, constraints • Data normalization • Method for establishing relations For relational model only For relational database only

8. Data Modeling: Entity Relationship Modeling

9. Entity Relationship (ER) Model(applies to relational data model) • High-level conceptual model • Describes the structure of the database, and the associated retrieval and update transactions on the database • Composed of • Entity types • Relationship types • Attributes

10. ER Modeling Relationship type Products stock number product description retail price stock on hand stock on order Manufacturers manufacturer code manufacturer name  Have 0..* 1..1 Attributes Entity type

11. ER ModelingAlternatively Relationship type Products Stock number Product description Retail price Stock on hand Stock on order Manufacturers Manufacturer code Manufacturer name Attributes Entity type

12. ERD Notation Primary key Entity type Relationship type Relationship name Music_categories CDs Classify music_category_code {PK} music_category_title Attributes stock_number {PK} CD_title artist music_category_code record_label_code 1..1 0..* Multiplicity (constraint) Degree of the Relationship: Binary

13. ERD NotationAlternatively Primary key (underscored) Entity type Relationship type Relationship name Music_categories CDs Music_category_code Music_category_title Attributes Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) Minimum (inside) Cardinality Maximum (outside) Many (crows feet)

14. 1. Entity Types • Strong Entity Type • Not existence-dependent on another entity type • Weak Entity Type • Existence-dependent on another entity type (i.e., child, dependent, subordinate) Entity type Entity Entity Uniquely identifiable Entity

15. Entity Types Strong entity? Music_categories CDs Music_category_code Music_category_title Classify Stock_number CD_title Artist Music_category_code Record_label_code

16. Definition of a Weak Entity Type “An entity type that borrows all or part of its primary key. Identifying relationships indicate the entity types that supply components of the borrowed primary key.” Mannino, 1999 Entity type 1 Key attributes... Method to Follow Have Weak entity type Entity type 2 Key Key attributes... Composite key

17. Diagramming Weak Entity Types An account cannot exist without an customer. Customers attributes... Strong entity type (parent, owner, dominant) Minimum must be one Customer_Accounts attributes... Weak entity entity (child, dependent, subordinate) *A customer can have more than one account Designates a weak entity type

18. 2. Attributes Property of an entity or relationship type • Attribute domain • Set of values that may be assigned to a single-valued attribute Customers Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num Customer_Accounts Cust_account Current_balance Credit_limit Active_date Expire_date

19. Attributes of Attributes • Simple (atomic attributes) - composed of a single component • Composite - composed of multiple components • Single valued - one value for an entity • Multi-valued - one or more values for an entity • Derived - value derived from a related attribute or set of attributes Student_ID FName MName LName Single-valued Multi-valued Student_ID Semester Course_ID More than one semester, more than one course_id

20. Attribute Domain Customers Composite Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num • On an ER model, should customer name be shown as a composite or simple attribute? • What is the attribute domain of Cus_name? Cust_first_name Cust_last_name John William Anita Homer Brown Tell Breake Simpson

21. Derived Attributes • Derived - value derived from a related attribute or set of attributes Student_ID Semester Course_ID Units Grade Grade_point Student_ID Semester Course_ID Units Grade Grade_point Student_ID Semester Course_ID Units Grade Grade_point Units x Grade = Grade point

22. Attributes as Keys Uniquely identifies an entity Candidate key • Keys cannot change their values (good for the life of the entity) • An efficient means for identifying an entity Primary key • Alternate key - candidate that can also be used to access an entity • Composite key - composed of multiple attributes (components)

23. Diagrammatic Representation Customers Cust_account {PK} Cust_name First_name Middle_name Last_name Cust_address Street_number Zip_code (fk) Cust_phone Soc_sec_num Key Composite attribute Composite attribute Foreign key

24. 3. Relationship Types • A set of associations between two (or more) participating entity types • Each is given a name that describes the function Customers Customer_account Own Customers_accounts Customer_account

25. Entity Relationship Diagram • Degree of a relationship - number of entities participating in a relationship (binary, ternary, quaternary, etc.) Customers Customer_account Strong Relationship Own • “Dog-ear” lines indicate a relationship between a weak and strong entity Customers_accounts Customer_account Weak

26. Data Modeling Music_categories Music_category_code Music_category_title Strong Entity (parent) All children (CDs) must have a parent (music categories or record labels) Relationship Classify Strong Entity (parent) CDs Stock_number CD_title Artist Music_category_code (fk) Record_label_code (fk) Record_labels Record_label_code Record_label Produce Weak Entity (child) Method to Follow

27. Degree of a Relationship Customers A customer purchases products and places them on his/her account Products Buy Relationship of degree three or ternary Cust_Accounts

28. Degree of a Relationship An employee is managed by only one manager (an employee is related to a maximum and minimum of one manager) Manages Employees Employee_number Employee_name Classification Project_ID Self-referencing relationship A manager manages one to many employees (a manager is related to a minimum of one and a maximum of many employees)

29. Structural Constraints • Cardinality • Determines the number of possible relationships for each participating entity • 1:1 - one to one • 1:M - one to many • M:N - many to many • Participation • Determines whether the existence of an entity depends upon its being related to another entity through the relationship Defined by business rules

30. Cardinality • 1:1 (one to one) • Each entity in X is associated with at most one entity in Y and conversely each entity in Y is associated with at most one entity in X • 1:M (one to many) • Each entity in X can be associated with many entities in Y but each entity in Y is associated with at most one entity in X. • M:N (many to many) • Each entity in X can be associated with many entities in Y and each entity in Y can be associated with many entities in X.

31. Cardinality 1:1 Relationships Strong entity type Weak entity type Customers Accounts Customer_ID Customer_name Customer_address Zip_code Account_number Customer_ID Account_type Current_balance Own Mandatory participation A customer owns a minimum and maximum of one account An account is owned by a minimum and maximum of one customer Note. This would be avoided in the logical design, but could be implemented in the physical.

32. Cardinality 1:M Relationships Strong entity type Weak entity type Customers Accounts Customer_ID Customer_name Customer_address Zip_code Account_number Customer_ID Account_type Current_balance Own Mandatory participation A customer owns a minimum one and maximum of many accounts An account is own by a minimum and maximum of one customer Note. This would be avoided in the logical design, but could be implemented in the physical.

33. Cardinality • M:N relationship if a customer can own more than one account (e.g., revolving, long-term), and one account can have more than one owner (e.g., joint account).

34. Cardinality M:N Relationships Strong entity type Weak entity type Customers Accounts Customer_ID Customer_name Customer_address Zip_code Account_number Customer_ID Account_type Current_balance Own Mandatory participation A customer owns a minimum of one and a maximum of many accounts An account is owned by a minimum of one and a maximum of many customers Note. This would be avoided in the logical design, but could be implemented in the physical.

35. Participation Constraints • Determines whether the existence of an entity depends on it being related to another entity through the relationship • Total (mandatory) - If the existence of one requires another • Partial (optional) - If the existence of one does not require the other Existence Dependency: An entity that cannot exist unless another related entity exists. A mandatory relationship produces an existence dependency. Mannino, 1999

36. ERD Notation Primary key (underscored) Entity type Relationship type Relationship name Music_categories CDs Music_category_code Music_category_title Attributes Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) A CD is related to a minimum and maximum of one music category Minimum (inside) Cardinality Maximum (outside) Many (crows feet)

37. ERD Notation Primary key (underscored) Entity type Relationship type Relationship name Music_categories CDs Music_category_code Music_category_title Attributes Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) A music category is related to a minimum of zero and maximum of many CDs Minimum (inside) Cardinality Maximum (outside) Many (crows feet)

38. ERD Notation Minimum cardinality of one (a music category has to have at least one CD) Entity type Music_categories CDs Music_category_code Music_category_title Classify Stock_number CD_title Artist Music_category_code Record_label_code Weak entity type (all four corners)

39. ERD Notation Music_categories CDs Music_category_code Music_category_title Classify Stock_number CD_title Artist Music_category_code Record_label_code A record label is related to a minimum of zero and maximum of many CDs Produce Record_labels Record_label_code Record_label

40. ERD Notation Music_categories CDs Music_category_code Music_category_title Classify Stock_number CD_title Artist Music_category_code Record_label_code Produce A CD is related to a minimum and maximum of one record label Record_labels Record_label_code Record_label

41. ERD Notation Music_categories CDs Music_category_code Music_category_title Classify Stock_number CD_title Artist Music_category_code Record_label_code Quantity_produced Produce Attribute of a relationship Record_labels Record_label_code Record_label