1 Week 7 October 10 Database Planning and

1 Week 7 October 10 • Database Planning and Design R. Ching, Ph. D. • MIS • California State University, Sacramento

Embedded SQL and Host Variable Host variable R. Ching, Ph. D. • MIS • California State University, Sacramento 2

3 Value assigned to the host variable R. Ching, Ph. D. • MIS • California State University, Sacramento

Output 4 All sales revenue for store number 101 R. Ching, Ph. D. • MIS • California State University, Sacramento

Host Variable, Lower Function, Wildcards, Calculation 5 Calculation LOWER function, concatenation and wildcards Host variable R. Ching, Ph. D. • MIS • California State University, Sacramento

Dynamic SQL in Oracle Graphics The value of table_name is assigned during the execution of the program Ampersand (&) specifies a lexical reference select title, sum(sales_revenue) from &table_name where record_label_code = : label group by title Colon (: ) indicates a host variable R. Ching, Ph. D. • MIS • California State University, Sacramento 6

Dynamic SQL Lexical Reference Variables Query select &column_names from &table_names where &condition Text assigned at runtime product_code, sales_revenue sales_99 manufacturer_code = ‘SON’ Equivalent to. . . select product_code, sales_revenue from sales_99 where manufacturer_code = ‘SON’ R. Ching, Ph. D. • MIS • California State University, Sacramento 7

8 Data Resource R. Ching, Ph. D. • MIS • California State University, Sacramento

Frequency of Use Very frequent Infrequent Required Accuracy Low 9 High Future Aggregate Quite old Currency Highly current R. Ching, Ph. D. • MIS • California State University, Sacramento Time Horizon Data are organizational resources and must be properly managed. Historical Scope Well defined Operational Control Level of Aggregation Source Management Control Internal Strategic Planning Detailed Wide Information Requirements External Robert Anthony's Taxonomy of Managerial Information Requirements

Database Planning 10 Database Planning Operational maintenance Systems Definition Testing Data conversion and loading Management Activities Requirements collection/analysis Database design Implementation DBMS selection Prototyping (Application) Application design R. Ching, Ph. D. • MIS • California State University, Sacramento

Database Planning Formulating an IS strategy: • Identify enterprise plans and goals with a subsequent determination of IS needs • Evaluate current IS to determine and understand existing strength and weakness • Appraise IT opportunities that might yield a competitive advantage Enterprise Data Model R. Ching, Ph. D. • MIS • California State University, Sacramento 11

System Definition • Identify boundaries (scope) of the new system – Current users and application areas • User views • Ensures no major users forgotten from database requirements • Defines data and transactions – Future users and applications R. Ching, Ph. D. • MIS • California State University, Sacramento 12

Requirements Collection • Information about each major user view – Description of the data used or generated – Details of how data is to be used or generated – Any additional requirements • Approaches to managing requirements for multiple user views – Centralized – gather all requirements to form a global data model – View integration – gather requirements of users to form local data models and combine local data models to form a global data model – Combination centralized/view integration R. Ching, Ph. D. • MIS • California State University, Sacramento 13

Database Design • Conceptual database design - process of constructing a model of the information used in an enterprise, independent of all physical considerations • Logical database design - process of constructing a model of the information used in an enterprise based on a specific data model, but independent of a particular DBMS and other physical considerations • Physical database design - process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data and any associated integrity constraints and security measures R. Ching, Ph. D. • MIS • California State University, Sacramento 14

DBMS Selection • Define terms of reference of study – State objectives and scope of study, and tasks • Shortlist 2 to 3 products – Depends on organization • Evaluate products – Create categories (groups) for evaluation based on features – Scorecard approach • Recommend selection and produce reports R. Ching, Ph. D. • MIS • California State University, Sacramento 15

Application Design • Design of the user interface and the application programs that use and process the data • Transaction Design – Transaction is a event applied to the database – Purpose is to define and document high-level characteristics of the transactions • Data to be used • Functional characteristics • Output • Importance to users • Expected rate of usage R. Ching, Ph. D. • MIS • California State University, Sacramento 16

Application Design • Transaction Design – Three types of transactions • Retrieval • Update • Mixed • User Interface Design Guidelines – User layout of form or report – Guidelines: • Meaningful titles • Comprehensible instructions • Logical group and sequencing of fields R. Ching, Ph. D. • MIS • California State University, Sacramento 17

Application Design • User Interface Design Guidelines – Guidelines: • Visually appealing layout • Familiar field labels • Consistent terminology and abbreviations • Consistent use of color • Visible space and boundaries for dataentry fields • Convenient cursor movement • Error correction for individual characters and entire fields • Error messages for unacceptable values R. Ching, Ph. D. • MIS • California State University, Sacramento 18 Follow form or report layout Edit checks

Application Design • User Interface Design Guidelines – Guidelines: • Optional fields marked clearly • Explanatory messages for fields • Completion signal R. Ching, Ph. D. • MIS • California State University, Sacramento 19

Prototyping • Working model less all required features and functionality • Types – Requirements (proof of concept) - Used to determine requirements and afterwards is discarded – Evolutionary – Used to determine requirements and evolves to become the actual application R. Ching, Ph. D. • MIS • California State University, Sacramento 20

Implementation and Data Conversion and Loading Implementation • Physical realization f the database and application designs • Define database entities (i. e. , users, tables, columns, views, etc. ) • Implement security and integrity controls Data conversion and loading • Transfer existing data into new database • Converting existing applications to run on the new database R. Ching, Ph. D. • MIS • California State University, Sacramento 21

Testing • Run database system with the intent of finding errors – Methodology and test cases – Usability criteria • Learnability • Performance • Robustness (error tolerance) • Recoverability • Adaptability R. Ching, Ph. D. • MIS • California State University, Sacramento 22

Operational Maintenance • Monitoring performance • Maintaining and upgrading R. Ching, Ph. D. • MIS • California State University, Sacramento 23

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 24

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 25

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 26

Logical vs. Physical Design 27 • 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 28

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 29 For relational model only For relational database only

30 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 31

ER Modeling 32 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

ER Modeling 33 Relationship type Products Stock number (PK) 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 Entity type 34 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 35 • 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 36 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 37

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 38

2. Attributes 39 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 40 • 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 41 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 42 • 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 43 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 Method to Follow Composite attribute 44 Customers Cust_account 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 45 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 46

Data Modeling 47 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 48

Degree of a Relationship 49 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 50

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 51

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

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

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 54

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

Participation Constraints 56 • 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 57 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 CDRecord_label_code is related to a Zero (circle) Minimum minimum and maximum Maximum (inside) Many of one music category (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Entity type 58 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 (inside) Maximum maximum of many CDs Many (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Entity type 59 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 60 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 61 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 62 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

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