1 Week 8 October 19 Database Design

1 Week 8 October 19 • Database Design • Modeling with ERD R. Ching, Ph. D. • MIS • California State University, Sacramento

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) R. Ching, Ph. D. • MIS • California State University, Sacramento 2

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 3

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 4

Logical vs. Physical Design 5 • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento

Fact-Finding Techniques • • • Examining documents Interviewing Observing the enterprise in operation Research Questionnaires R. Ching, Ph. D. • MIS • California State University, Sacramento 6

Design Tools Relational database design • Entity relationship diagram (ERD) – Relations, relationships, constraints • Data normalization – Method for establishing relations R. Ching, Ph. D. • MIS • California State University, Sacramento 7 For relational model only For relational database only

8 Data Modeling: Entity Relationship Modeling R. Ching, Ph. D. • MIS • California State University, Sacramento

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 9

ER Modeling 10 Relationship type Products stock number product description retail price stock on hand stock on order Have 0. . * Attributes Entity type R. Ching, Ph. D. • MIS • California State University, Sacramento Manufacturers manufacturer code 1. . 1 manufacturer name

ER Modeling Alternatively 11 Relationship type Products Stock number Product description Retail price Stock on hand Stock on order Manufacturers Manufacturer code Manufacturer name Attributes Entity type R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation 12 Primary key Relationship type Relationship name Entity type Music_categories Attributes CDs Classify music_category_code {PK} stock_number {PK} music_category_title 1. . 1 0. . * CD_title artist music_category_code record_label_code Multiplicity (constraint) Degree of the Relationship: Binary R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternatively Entity type 13 Primary key (underscored) Relationship type Relationship name Music_categories Attributes Music_category_code Music_category_title Cardinality CDs Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) Minimum Maximum (inside) Many (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

1. Entity Types 14 • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento Uniquely identifiable

Entity Types 15 Strong entity? Music_categories Music_category_code Music_category_title CDs Classify R. Ching, Ph. D. • MIS • California State University, Sacramento Stock_number CD_title Artist Music_category_code Record_label_code

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. ” Entity type 1 Mannino, 1999 Key attributes. . . Have Entity type 2 Key Composite key Key attributes. . . R. Ching, Ph. D. • MIS • California State University, Sacramento Method to Follow Weak entity type 16

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

2. Attributes 18 Property of an entity or relationship type Customers Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num Customer_Accounts Cust_account Current_balance Credit_limit Active_date Expire_date • Attribute domain – Set of values that may be assigned to a single-valued attribute R. Ching, Ph. D. • MIS • California State University, Sacramento

Attributes of Attributes 19 • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento

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

Derived Attributes 21 • Derived - value derived from a related attribute or set of attributes Student_ID Semester Course_ID Units Grade_point Student_ID Semester Course_ID Units Grade_point Units x Grade = Grade point R. Ching, Ph. D. • MIS • California State University, Sacramento

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

$Diagrammatic Representation 23 Customers Composite attribute Cust_account {PK} Cust_name First_name Middle_name Last_name Cust_address Street_number$

Diagrammatic Representation 23 Customers Composite attribute Cust_account {PK} Cust_name First_name Middle_name Last_name Cust_address Street_number Zip_code (fk) Cust_phone Soc_sec_num R. Ching, Ph. D. • MIS • California State University, Sacramento Key Foreign key

3. Relationship Types • A set of associations between two (or more) participating entity types • Each is given a name that describes the function 24 Customers Customer_account Own Customers_accounts Customer_account R. Ching, Ph. D. • MIS • California State University, Sacramento

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

Data Modeling 26 Music_categories Strong Entity (parent) Music_category_code Music_category_title Relationship Classify All children (CDs) must have a parent (music categories or record labels) Strong Entity (parent) CDs Stock_number CD_title Artist Music_category_code (fk) Record_labels Produce Weak Entity (child) R. Ching, Ph. D. • MIS • California State University, Sacramento Record_label_code Record_label Method to Follow

Degree of a Relationship Customers Buy A customer purchases products and places them on his/her account Products Relationship of degree three or ternary Cust_Accounts R. Ching, Ph. D. • MIS • California State University, Sacramento 27

Degree of a Relationship 28 An employee is managed by only one manager (an employee is related to a maximum and minimum of one manager) Manages Employees Self-referencing relationship Employee_number Employee_name Classification Project_ID R. Ching, Ph. D. • MIS • California State University, Sacramento A manager manages one to many employees (a manager is related to a minimum of one and a maximum of many employees)

Structural Constraints • Cardinality – Determines the number of possible relationships for each participating entity • 1: 1 - one to one • 1: M - one to many Defined by • M: N - many to many business rules • Participation – Determines whether the existence of an entity depends upon its being related to another entity through the relationship R. Ching, Ph. D. • MIS • California State University, Sacramento 29

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. R. Ching, Ph. D. • MIS • California State University, Sacramento 30

Cardinality 31 1: 1 Relationships Strong entity type Weak entity type Customers Customer_ID Customer_name Customer_address Zip_code Accounts Own Mandatory participation A customer owns a minimum and maximum of one account Account_number Customer_ID Account_type Current_balance 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 Ching, Ph. D. • MIS • California State University, Sacramento physical. R.

Cardinality 32 1: M Relationships Strong entity type Weak entity type Customers Customer_ID Customer_name Customer_address Zip_code A customer owns a minimum one and maximum of many accounts Accounts Own Mandatory participation Account_number Customer_ID Account_type Current_balance 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 Ching, Ph. D. • MIS • California State University, Sacramento physical. R.

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). R. Ching, Ph. D. • MIS • California State University, Sacramento 33

Cardinality 34 M: N Relationships Strong entity type Weak entity type Customers Customer_ID Customer_name Customer_address Zip_code A customer owns a minimum of one and a maximum of many accounts Accounts Own Mandatory participation Account_number Customer_ID Account_type Current_balance 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 Ching, Ph. D. • MIS • California State University, Sacramento physical. R.

Participation Constraints 35 • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento

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

ERD Notation Entity type 37 Primary key (underscored) Relationship type Relationship name Music_categories Attributes Music_category_code Music_category_title CDs 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 Minimum Cardinality Maximum (inside) Many maximum of many CDs (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Entity type 38 Minimum cardinality of one (a music category has to have at least one CD) Music_categories CDs Music_category_code Music_category_title Classify Weak entity type (all four corners) R. Ching, Ph. D. • MIS • California State University, Sacramento Stock_number CD_title Artist Music_category_code Record_label_code

ERD Notation 39 Music_categories Music_category_code Music_category_title CDs Classify A record label is related to a minimum of zero and maximum of many CDs Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation 40 Music_categories Music_category_code Music_category_title CDs Classify A CD is related to a minimum and maximum of one record label Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation 41 Music_categories Music_category_code Music_category_title CDs Classify Quantity_produced Attribute of a relationship Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

42 R. Ching, Ph. D. • MIS • California State University, Sacramento