n Database Modeling representation of the …

1 1DB212 Peter Lo 20041 Database Modeling (Part 1)DB212 Peter Lo 20042 Entity-Relationship ModelnA conceptual data model, which is a representation of the structure of a Database that is independent of the software that will be used to implement the databaseDB212 Peter Lo 20043 Entity-Relationship ModelNameEmployee nameAddressEMPLOYEEDB212 Peter Lo 20044 EntitynAn Entity can be a person, place, object, event or concept in the user environment about which the organization wishes to maintain :uPerson:EMPLOYEE, STUDENT, PATIENTuPlace:STATE, REGION, COUNTRYuObject:MACHINE, BUILDINGuEvent:SALE. REGISTRATIONuConcept: ACCOUNT, COURSEE ntity2DB212 Peter Lo 20045 Entity Type and Entity is a difference between Entity Typeand Entity Type is a collection of entities that share commonproperties or characteristicsuEntity Instance is a single occurrenceof an entity typeDB212 Peter Lo 20046 ExamplesnEntity type:EMPLOYEEnAttributes:uEMPONOuNAMEuAD DRESSuYEARHIREDnInstances:100101 Roy LimMary WongHong KongKowloon19891999DB212 Peter Lo 20047 AttributenAttribute is a property or characteristic of an entity that is of interest to the entity and relationships may have :uSTUDENT:STUDENT NO.

2 ,NAME, ADDRESSuEMPLOYEE:EMPLOYEE NO., NAME, SKILLA ttributeDB212 Peter Lo 20048 Candidate KeynCandidate key is an attribute (or combination of attributes) that uniquely identifies each instance of an entity typeCandidate Key3DB212 Peter Lo 20049 Primary KeynPrimary Key is a candidate key that has been selected as the identifier for an entity KeyDB212 Peter Lo 200410 MultivaluedAttributenAttribute that can have more than one value for each entity exampleuAn EMPLOYEE may possess a number of skills. uSo SKILL is a Peter Lo 200411 RelationshipnRelationships are what that holds together the various Peter Lo 200412 Degree of RelationshipnThe degree of a relationship is the number of entity types that participate in that relationshipuUnary relationship (recursive relationship)uBinary relationshipuTernary relationship4DB212 Peter Lo 200413 Unary RelationshipnUnary Relationship also called Recursive RelationshipnIt is a relationship between the instances of one entity typeDB212 Peter Lo 200414 Unary Relationship (Recursive Relationship)PERSON11 One-to-oneIsmarriedtoDB212 Peter Lo 200415 Unary Relationship (Recursive Relationship)DB212 Peter Lo 200416 Unary Relationship (Recursive Relationship)ITEMNMMany-to-manyHas components5DB212 Peter Lo 200417 Binary RelationshipnIt is a relationship between instances of two entity Peter Lo 200418 Ternary RelationshipnIt is a simultaneous relationship among instances of three entity types.

3 NTernary relationship is a simultaneous relationship among instances of three entity typesDB212 Peter Lo 200419 Ternary RelationshipVENDORS hipsWAREHOUSEWAREHOUSEDB212 Peter Lo 200420 Existence DependencynAn instance of one entity cannot exist without the existence of an instance of some other Peter Lo 200421 Weak EntitynA Weak Entity is an entity type that has an existence relationship in which the primary key of the parent entity is used as part of the primary key of the dependent entities usually do not have a natural identifier because the primary key of the parent entity is often used as part of the primary key of the dependent child integrity is enforced as weak entity cannot exist unless the parent entity Peter Lo 200422 Example of Weak Peter Lo 200423 GeneralizationnGeneralization is the concept that some things (entities) are subtypes of other, more general express generalization relationships, object are arranged into a hierarchy.

4 UExample: Accountant, Programmer Analyst are subtypes of the more general type called Peter Lo 200424 CategorizationnCategorization is when an entity comes in various : There are different subtypes of employee which are hourly employee, salaried employee and part-time Peter Lo 200425 Supertypesand SubtypesnSupertypesuA generic entity type that is subdivided into subset of a supertypethat shares common attributes or relationships distinct from other Peter Lo 200426 ISA RelationshipnThe relationship between each subtype and supertypeis called an ISA subtypes are mutually exclusive and that one ( required for each instance of the supertype). Employee no. Name Date Hired Address EMPLOYEE ISA ISA SALARIED EMPLOYEE ISA HOURLY EMPLOYEEH ourly rate Employee no. CONSULTANT Stock option Employee no. Contact no. Employee no. Annual salary Daily rate DB212 Peter Lo 200427 Logical Database DesignnThere is a process of transforming the conceptual data model into a logical Database model.

5 NThere are four types of logical Database models in use today: uObject-orienteduHierarchicaluNetwork Peter Lo 200428 Object-Oriented Data ModelnMost future Database management systems will be based on objects, or will incorporate object-oriented functionality. nThis enable users to create generic, all purpose components that can be reused in multiple Peter Lo 200429 ObjectnObjects are abstraction of the real world entities that exhibit states and behaviors. nThe state of objects are expressed as values of the Attributesof the object. nThe behavior of an object is expressed by a set of Methodsthat operate on its Peter Lo 200430 AttributenThese are the properties of objects that are of interest to the describe the characteristics of an can have a specific attribute called a state. nThe state of an object describes the object's current status. DB212 Peter Lo 200431 MethodsnMethods define the behavior of an object. nCan only process data within the object class in which they are defined (Encapsulation).

6 NCan receive requests from methods in another object class. nA number of method categories:uOccur Methods instance add, instance change, instance Methods perform calculations on the data values encapsulated in the same object Methods produce signals when predetermined limits are exceeded in a Peter Lo 200432 EncapsulationnEncapsulation is the property that the attributes and the methods of an object are hidden from the outside world and do not have to be known to access its data values or use its Peter Lo 200433 Object Classes and InstancenA logical grouping of objects that have the same attributes and behavior. nAn object instance is one occurrence of an object class. nWhen we use the term object by itself, we are referring to an object class. VEHICLEN umberYearModelAddVehicleObject nameAttributesMethodsDB212 Peter Lo 200434 Identifying and Describing ObjectsnTop-down ApproachnBottom-up ApproachDB212 Peter Lo 200435 Top-down ApproachnThis begins with a high level description of the environment and proceeds from the general to the object-oriented data model, methods are activated by sending message from a sending object to a receiving example studying written material and talking with users are activities to locate nouns in written material.

7 NThis provides Information about Potential Peter Lo 200436 Bottom-up ApproachnBottom-up approach begins with system detail, example reports, video forms and other detail documents and analyst identifies the Candidate Objects and their Peter Lo 200437 Top-down vs. Bottom-up ApproachnIn reality, the top-down and bottom-up approaches should be used to identify and describe candidate Peter Lo 200438 InheritancenInheritance is an important principle of the object-oriented mode. nInheritance means that all properties of an object class become the properties of its subclasses. DB212 Peter Lo 200439 InheritancenInheritance is the property that, when entity types or object classes are arranged in a hierarchy, each entity type of object class assumes the attributes and methods of its properties of an object class become the properties of its attributes that are unique to a subtype are associated with that Peter Lo 200440 Example of InheritancenThe attributes of EMPLOYEE apply to all three subclasses.

8 NThe method CalculateAgeapplies to all salaryStock optionCalculateStockBenefitCONSULTANTC ontact hiredAllocateToContractHOURLYH ourly rateCalculateMthlyWageEMPLOYEEE mployee hiredBirthdateCalculateAge11DB212 Peter Lo 200441 Advantage of OO ApproachnReusabilityuObjects can be defined for a variety of functions and then reused in numerous data typesuAn object-oriented Database can store and manage complex data such as documents, graphics, images, voice message and video databasesuObject-oriented databases can support distribution of data across a network more easily than other data Peter Lo 200442 AppendixEntity-Relationship DiagramsDB212 Peter Lo 200443 Entity-Relationship DiagramsnAn Entity-Relationship Diagram (ERD) is a graphical model of the information system that depicts the relationships among system entities. DB212 Peter Lo 200444 Three Main Types of Relationships nOne-to-One relationship (1:1) nOne-to-Many relationship (1:M) nMany-to-Many relationship (M:N) 12DB212 Peter Lo 200445 One-to-one relationship (1:1)nIt exists when exactly one of the second entity occurs for each instance of the first entity.

9 DB212 Peter Lo 200446 One-to-Many Relationship (1:M)nIt exists when one occurrence of the first entity can be related to many occurrences of the second entity, but each occurrence of the second entity can be associated with only one occurrence of the first entity. DB212 Peter Lo 200447 Many-to-Many Relationship (M:N)nIt exists when one instance of the first entity can be related to many instances of the second entity, and one instance of the second entity can be related to many instances of the first entity. DB212 Peter Lo 200448 CardinalitynCardinality describes how instances of one entity relate to instances of another entity. 13DB212 Peter Lo 200449 Cardinality SymbolsDB212 Peter Lo 200450DB212 Peter Lo 200451 Creating an Entity Relationship Diagram (ERD) the all significant events, transactions, or activities that occur between two or more the nature of the the Entity Relationship Diagram.