1 / 61

Lecture No. 2

Lecture No. 2. Project Planning / Management Database Design Entities, Relationships, Modelling. Important Dates. Wednesday 16th March : Semester 1 ‘Enrolment Statement’ issued to all students. Data Base Principles. This Lecture’s Objectives:

violet-santana
Download Presentation

Lecture No. 2

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. Lecture No. 2 Project Planning / Management Database Design Entities, Relationships, Modelling

  2. Important Dates Wednesday 16th March : Semester 1 ‘Enrolment Statement’ issued to all students

  3. Data Base Principles This Lecture’s Objectives: 1. To briefly look at some Project Planning activities 2. To further develop aspects of Database Design 3. To introduce you to Entities and Entity relationships 4. To work through some examples of E-R modelling

  4. Planning • Scheduling • Cost Tracking • Resource Management • Reporting • Software Functions • Real time interactive operation • Add / Delete items • Change logic dependencies • Adjust calendar and calendar dependencies • Change resource and availability levels and dates • Mouse driven : Multiple platforms • (Microsoft, MacIntosh) Project Planning

  5. Project Planning 1. Project Manager’s Controls - Schedule - Costs - Technical - Resources 2. Goals, Objectives, Targets, Disciplines 3. Development of Project Definition / Scope 4. Performance Assessment Criteria 5. Level of Detail in Planning 6. Feed back and Controls 7. Replanning and Rescheduling 8. Budgeting

  6. Project Planning Task / Week 1 2 3 4 5 6 7 Analysis Design Review Programming Implementation - Plan - Manuals System Testing Review Acceptance Test Review Results Handover

  7. PERT Diagram Get Mix Pour into ingredients Ingredients cake pan Place Bake Remove cake in cake cake from oven oven start Preheat oven

  8. Your Project Why did we divert into Project Planning ? Your assignment is a project, and the resources (you skills, your time, computer time and the preparation of the various deliverables need to be carefully managed. One of the team will need to take the role of Co-ordinator, and one (not necessarily the same person) will need to take the role of Quality Controller and Reviewer - i.e. to critically review what the team have done or are intending to do. One person will be the minute taker of your meetings, and there will probably be the recognition of individual skills (e.g. screen design)

  9. Data Base Design

  10. Data Base Design • Reduce data redundancy. • Provide stable data structures that can be readily changed with changing user requirements. • Allow users to make ad hoc requests for data. · Maintain complex relationships between data elements. • Support a large variety of decision needs

  11. End Product of Data Base Design • A database which will: • Accurately reflect the ‘real world’ data in all • required aspects • Be responsive to Management Information demands • Reflect Business Rules and Controls • Be capable of modification to meet changes • in Management needs • Be an asset to the Organisation/Enterprise

  12. Business Functions and Processes • Business Functions : • Broad groups of closely related activities and decisions which contribute to a product or service like cycle. (e.g. planning, materials management, production planning, quality assurance).

  13. Business Functions and Processes • Business Processes: Decision related activities which occur within a function. They are related to management of people, money, material and information. • Materials Management (Business Function)could be subdivided into: requirements planning, purchasing, goods received, material accounting, stock-keeping Business Processes should reflect related activity groupings

  14. Business Activities Business Activities : Specific operations or transactions required to carry out a process Some guidelines: An activity should produce some clearly defined (identifiable) result - a product, a decision, a plan ...... An activity has clear boundaries - a clear beginning and end. Activities do not overlap. An activity is carried out as a unit, by a single agent or a team Once initiated, an activity proceeds independently of and from other activities.

  15. Business Entities Are persons, objects or events about which Information is, or will be, recorded in the Information Data Base Many of these Entities can be identified with Business Activities (e.g. supplier , purchase order , customer) (notice the use of the singular noun - there is no particular standard)

  16. Critical Success Factors * Key factors which must be performed well to ensure the success of an organisation * Also known as Critical Performance Items CPI and Key Performance Indicators KPI e.g. production failure rate < 0.01% of total production units production cost increases <= c.p.i. increases customer service complaints < 1% of all customer transactions absenteeism < 1% of staff in any 24 hour period product quality => advertised standards (water, power) no more than 1% of trains > 3 late at destination

  17. Design Criteria -data availability - data reliability - data currency - data consistency - data flexibility - data efficiency Verify these criteria are satisfied via technical review

  18. Database Design -the process of developing database structures from user information requirements -a structured methodology Structured Methodology - a number of ordered formal processes with known inputs and expected outputs Objectives 1. derive relationships 2. evolve to meet user requirements 3. user requests are met within reasonable time limits

  19. 3 Schema Architecture The Primary Objectives of a DBMS are to provide facilities for : 1. Definition of Database Logical Structures 2. Definition of Physical Structures 3. Access to the Database 4. Definition of Storage Structures to store user data These components are known as the ‘database architecture’

  20. 3 Schema Architecture EXTERNAL SCHEMAS logical user 1 user 2 user..n Global CONCEPTUAL SCHEMA logical (as seen by database analysts, designers, programmers and the Data Base Administrator) INTERNALSCHEMA (Storage Views) physical (as seen by the Operating System and the Data Base Administrator) SCHEMA: Describes data in the data base - also sub-schema

  21. 3 Schema Architecture external external external external view A view B view C view X Cobol C Assembler External + DML + DML DML + DML Schemas dbms Conceptual Model Conceptual Schema Internal Model Internal Schema Physical Level user interface logical record interface stored operating system record interface physical record interface

  22. Progression Stages The previous overhead gave the foundation for the stages of design Stage 1: The Conceptual Design (External Schemas) - the recognition and understanding of user requirements. The purpose of this stage, which is independent of any / all physical considerations, is to construct a model of the information used in an organisation or business Data flow diagrams, data structure diagrams are the ‘tools’

  23. Progression Stages Stage 2 : Logical Database Design This is the stage where a model is constructed of the information used in an organisation The model is a specific model (E-R Diagram, UML, O-O model) but is independent of any DBMS Stage 3 : Physical Database Design This describes the base relations, file organisations, indexes, integrity constraints and security. It is tailored to a specific DBMS

  24. Database Design Functional Requirements Other matters Domains, Nulls, Derived Data, Encoded Data. Data Base Model Information Level Design Processes Final Information Level Design Physical Constraints Physical Level Design Final Database Structure

  25. Database Design 4th Generation Environment - User Perception user terminal teleprocessing report query electronic monitor writer language mail application programs data dictionary DBMS structured and non-structured data images, graphics, video,voice data base

  26. Data Modelling Data Modelling is about - Structuring and Organising Data Leads to the emergence of Records Attributes Data Types Constraints Semantics e.g. age relates to ? person ? building ? isotope ? galaxy ? Commercial Data Base Models Hierarchical Specific organisation and relationships between records held in the database Network Relational Constraints, domains, conditions of update, inserts, deletes, modifications, access

  27. Entities • The designation of a ‘thing’ about which data is to be collected • stored • processed • Typical Entities : person , item , building , stock , vehicle , job , book , library

  28. Entity Relationship Diagrams Used to model relationships 1 to 1 relation 1:1 1 to Many 1:M Many to Many M:N (real world situations) vehicle regn no 1:1 department staff Also :- Optional Mandatory occurrence 1:M supplier item M:N

  29. Entity Relationships Many to One One to One One to Many Many to Many

  30. Logical Data Modelling • 3 types of data objects: • Entities • Attributes • Relationships • Entities: Are persons, places, or things about which data is to be, or is, gathered • Attributes : Are the properties of entities Examples are Names, Tax Numbers, Age, Status • Relationships : Describe how entities relate to each other e.g. Customers BUY Products • Persons WORK ON Jobs

  31. Logical Data Modelling Entity-Type : An entity type represents the class of Objects which share some common aspect - such as Jobs, Persons, Materials, Houses Entity-Occurrence : Also called ‘entity instance’ Example : “3 bedroom, brick veneer, 10 years old” are some of the actual values of an entity instance of the Entity Type ‘House’

  32. Logical Data Modelling CARDINALITY Premise: If an entity ‘A’ relates to another entity ‘B’, then the conditions of occurrences of ‘A’ and ‘B’ need to be known. Cardinality : The specification of the number of occurrences of 1 entity type which can be related to the number of occurrences of another entity type Cardinality is expressed as ‘one’ or ‘many’

  33. Logical Data Modelling 2 entities can be related as : One to One : (1 : 1) The interpretation of this is that an occurrence of an entity ‘A’ can relate to ONE and ONLY ONE occurrence of entity ‘B’, and an occurrence of entity ‘B’ can relate to ONE and ONLY ONE occurrence of entity ‘A’ One to Many : (1: M) or (1 : N) The interpretation is that ONE occurrence of entity ‘A’ can relate to ONE or MORE occurrences of entity ‘B’, but an occurrence of entity ‘B’ can relate to ONE ONLY occurrence of entity ‘A’

  34. Logical Data Modelling Many to Many (M : N) - a common business relationship The interpretation is that ONE or MORE occurrences of entity ‘A’ can relate to ONE or MORE occurrences of entity ‘B’ and ONE or MORE occurrences of entity ‘B’ can relate to ONE or MORE occurrences of entity ‘A’

  35. Logical Data Modelling MODALITY An extension of relationship Indicates whether an occurrence MUST PARTICIPATE in a relationship Cardinality indicates the maximum number of entity occurrences which can participate in a relationship Modality indicates the minimum number of occurrences Modality = 0 if an occurrence is not needed or is optional Modality = 1 if an entity occurrence is required or mandatory

  36. Logical Data Modelling DEGREE Relates to the number of entity types associated in a relationship UNARY - The entity type is related only to itself (also called recursive) BINARY - Two entity types are related (most common case) TERNARY - A relationship involving more than two entity types

  37. Logical Data Modelling ATTRIBUTE TYPES and ATTRIBUTE VALUES An attribute type is an occurrence of a named set of values e.g. ‘size’, ‘name’, ‘mass’, ‘person_id’ An attribute value is an occurrence (or instance) of an attribute type An attribute value is a characteristic of, or a fact about an entity occurrence e.g. Attribute ‘size’ Attribute value ‘25.4mm’ Entities/Attributes are often referred to as ‘meta-data’, that is ‘data about data values’

  38. Logical Data Modelling DOMAINS A ‘Domain’ is a set of possible values which an attribute set can adopt e.g. dates, integers, person name constructions, post codes 3 Main Types of Domains: Data Type : Numeric, Text, Integer, Date ..... Ranges : Values between sets ‘Employee IDs must be between 10156 and 10987’ Acceptable Values : Specific Post Codes, Area Codes, Names of Prominent People, Building Names They indicate the only values an attribute can have. All of these features contribute to ‘Database Integrity’

  39. Logical Data Modelling shiphands sail ships Relationships Representation

  40. Logical Data Modelling Cardinality Course Units Meaning : ONE Meaning : MANY

  41. Logical Data Modelling Mandatory - Optional Relationship (Binary Relationship) Artists Pictures paint Unary Relationship Ternary Relationship customers buy cars dealers

  42. Logical Data Modelling ENTITIES RELATIONSHIPS 1 : 1 1 : N M : N membership class degree cardinality modality mandatory optional recursive binary n-ary ATTRIBUTES domains values

  43. Entity Relationships Each entity may be a ‘one’ or ‘many’ participant part vendor delivers warehouse quantity Quantity is the amount of a particular ‘part’ shipped from a particular ‘vendor’ to a particular ‘warehouse’.

  44. Logical Data Modelling • Modelling symbols - McFadden, Hoffer, Prescott Mandatory One Mandatory Many - at least One Optional One - No less than One Optional Many - May be none There is NO standard notation for E-R Modelling

  45. The Monash Gallery Carrier Exhibition Contract Collection Contact Buyer Artist History Artist Item Invoice Gallery Artist Category Payment Exhibition Category Category

  46. Data Modelling Thoughts Entities Nation Stock nation code stock code nation name firm name exchange rate stock price stock qty stock divdnd Attributes A 1:M relationship

  47. Data Modelling Thoughts Nation State City Name Statename Cityname Population Statepop Citypop Area State area City Area Questions : Is the Nation ‘Population’ necessary ? Is the State ‘Population’ necessary ?

  48. Data Modelling Thoughts Library Book Borrower libname callno borrower ID ISBN booktitle duedate Library Book Copy Borrower libname callno bookno borrowerID ISBN due date title Physical Objects

  49. A Project Management Data Model Project Management involves Planned and Actual data A Project is normally divided into a number of Activities which require Resources Planning requires an estimation of consumable resources EmployeeActivityProject empno planned hours project id Daily Work workdate actual hours

  50. A Project Management Data Model Planned Hours is an attribute of Activity Actual Hours is an attribute of Daily Work The time(hours) spent on an Activity would be the sum of actual hours in the Daily Work entity This is probably workable, if planning is done at the Activity level and employees submit daily worksheets (or details are captured by electronic means) Planning could be more detailed if Planners were able to indicate how many hours of each day each employee should spend on a project

More Related