1 / 63

Week 7 October 10

Week 7 October 10. Database Planning and Design. Embedded SQL and Host Variable. Host variable. Value assigned to the host variable. Output. All sales revenue for store number 101. Host Variable, Lower Function, Wildcards, Calculation. Calculation.

solada
Download Presentation

Week 7 October 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 7October 10 Database Planning and Design

  2. Embedded SQL and Host Variable Host variable

  3. Value assigned to the host variable

  4. Output All sales revenue for store number 101

  5. Host Variable, Lower Function, Wildcards, Calculation Calculation LOWER function, concatenation and wildcards Host variable

  6. 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

  7. Dynamic SQLLexical Reference Variables Text assigned at runtime Query product_code, sales_revenue sales_99 manufacturer_code = ‘SON’ select &column_names from &table_names where &condition Equivalent to... select product_code, sales_revenue from sales_99 where manufacturer_code = ‘SON’

  8. Data Resource

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

  10. Database Planning Database Planning Operational maintenance Systems Definition Testing Requirements collection/analysis Data conversion and loading Management Activities Database design Implementation DBMS selection Prototyping (Application) Application design

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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 data-entry fields • Convenient cursor movement • Error correction for individual characters and entire fields • Error messages for unacceptable values Follow form or report layout Edit checks

  19. Application Design • User Interface Design Guidelines • Guidelines: • Optional fields marked clearly • Explanatory messages for fields • Completion signal

  20. 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

  21. Implementation andData 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

  22. Testing Testing • Run database system with the intent of finding errors • Methodology and test cases • Usability criteria • Learnability • Performance • Robustness (error tolerance) • Recoverability • Adaptability

  23. Operational Maintenance • Monitoring performance • Maintaining and upgrading

  24. 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)

  25. 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

  26. 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

  27. 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

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

  29. 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

  30. Data Modeling: Entity Relationship Modeling

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

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

  33. ER Modeling Relationship type Products Stock number (PK) Product description Retail price Stock on hand Stock on order Manufacturers Manufacturer code Manufacturer name Attributes Entity type

  34. 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) Minimum (inside) Cardinality Maximum (outside) Many (crows feet)

  35. 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

  36. 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

  37. 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

  38. 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

  39. 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

  40. 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

  41. 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

  42. 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

  43. 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)

  44. Diagrammatic Representation Customers Cust_account Cust_name First_name Middle_name Last_name Cust_address Street_number Zip_code (fk) Cust_phone Soc_sec_num Method to Follow Key Composite attribute Composite attribute Foreign key

  45. 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

  46. 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

  47. 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

  48. 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

  49. 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)

  50. 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

More Related