Design ( Logical, Physical ) Analysis Construction Adjust data model to a database technology (e.g., relational). Business & information needs. Pros & cons. Create data model. Design storage & input/output. Specifications for storage & access methods (indexes), and forms & reports System require- ments. Business case. Class diagram (classes & relationships) and write business rules. List or diagram of normalized tables (schema). Customer( CID, Name,..) SalesPerson(EID, Name..) Order(OrderID, EID, CID, OrderDate..) DBS Development TASK RESULT
Class diagram A static model of classes and their relationships Use case diagram Shows actors (users) and system functions. Sequence diagram Interactions among objects with a timeline indicated. Activity diagrams The steps within a function (or procedure, use case). Implementation diagrams The structure of the code or of the run-time modules. Unified Modeling Language (UML) Trendy notation for analyzing systems and often used with the object orientation (OO) approach. Contains specific sorts of diagrams: • The class diagram is most important for DB systems. • Similar to the old Entity-Relationship diagram (E-R diagram) but more compact.
Class Diagram – Concepts Entity: The aspect of reality recorded in the system. Class: Description of an entity, which includes its attributes (properties) and behavior (methods, procedures). Object: One instance of a class with specific data; a record in a database. Attribute:A data property of a class; an aspects of an entity. Behavior: Procedures (methods, processes, functions) performed by the class. Association: A connection between classes or between entities.
Examples from Pet Store Application Entity: Customer Class: Customer, includes attributes and behavior (below) Object: 12257, Joe Jones... (a specific customer) Attributes: CustID, Name... Behavior: AddCustomer, DeleteCustomer... (CRUD) Association: Class Customer is related to class Order reflecting relationship between entities Customer and Order.
Customer Associations • Associations (Relationships) reflect business specified in business rules* • Sometimes common-sense rules(Figure A) (A) Order • Sometimes rules unique to an organization (FigureB) places (B) Customer Product Search Profile belongs to NOTE: Assumed way of reading is left-right and top-down. If the relationship is drawn that way, the arrowhead is not necessary. Arrowheads needed when these rules are not followed (Figure B).
manages worker Employee manager Degree of Association Association Degree refers to the number of classes involved in an association: 1) Class with itself – unary (below); rare 2) Between 2 classes – binary (previous slide); most frequent and most important!) 3) Between 3 or more classes – N-ary; rare
Department Employee 1..* 1..1 works in Example: many-to-one (M:1) relationship Department Employee * 1 works in Multiplicity (Cardinality*) of Associations • Multiplicity refers to the number of objects participating in a relationship. Example: An employee must work in only one department, and one (each) department has one or more employees. • Just maximum multiplicity is often used, and associations are classified on it as well.
Types of Associations based on Multiplicity • One-to-many (1:M) - examples: Customer—Order, Client—Account • Many-to-many (M:N) - examples: Order—Item, Student—Course • One-to-one (1:1) - example: Customer—BillingAddress, Citizen-- SocaiInsuranceUser (IncomeTaxPayer…)
Procedure for Determining Multiplicity (Form A question) • Ask the multiplicity question: One (each) object on the left side of the association is associated with how many objects on the right side? Minimally, maximally? Department Employee works in Department 1 .. 1 Employee works in 2. Write the numbers/asterisks on the right side. And…
3. Ask the same question from the other side of the relationship: One (each) object on the right side of the association is associated with how many objects on the left side? Minimally, maximally? Department Employee 1..1 <employs Department 1..1 Employee 1..* <employs 4. Write the numbers/asterisks on the left side. Note: You need to memorize the multiplicity question! Or
Department Employee 1..* 1..1 works in Procedure for Determining Multiplicity (Form B question) 1. How many objects on the left side are associated with one (each) object on the right side? 2. Write the numbers/asterisks of the left side. 3. How many objects on the right side are associated with one (each) object on the left side? 4. Write the numbers/asterisks on the right side. Note: We usually need to ask just about maximum multiplicity.
Multiplicity options: 0 (zero) 1 (one) * (many); also labeled as M or N specific number, if known (3, 10); e.g., in part-whole relationship Customer 1..1 places 1..* Order 0..* Order_Item contains 1..* Item . Notation for Multiplicity NOTE: Some imprecision in Fig. 2.8 p. 49 in 5th ed. (p. 46 4th ed.): “Order” is called “Sale” in sentence two.
1) One customer can place one or more orders, and each order is placed by one and only one customer. 2) Each order can contain one or more items, and an item can be listed on no (0) order or on many orders. * Customer 1..1 places 1..* Order 0..* contains 1..* Item . Business Rules for Cardinality - Read/Write Business Rules: Order_Item • If min. multiplicity >= 1, association is mandatory; • If min. multiplicity = 0, association is optional.
Degree of association => 3. Components are assembled into Products by Employees. Simultaneous relationships. Try to read multiplicity. Support to inventory scheduling, quality control, performance measurement. But, the data model is not complete as shown! * Employee * * Component * * * Product N-ary Association assembled by assembles assembled in NOTE: Some imprecision in Fig. 2.9 on p. 49 in 5th ed. (p. 46 in 4th ed.). An association between Employee and Component missing; incorrect way of stating M:M multiplicity.
N-ary Association with Associative Class • The key in Assembly combines keys from source associated classes. Its own attributes are Date and Time. Employee EmpID Name • N-ary associative class Assembly introduced. assembles assembled by Assembly Date Time Component CompID Type Name Product ProdID Name assembled in Table Assembly:
Focus on Keys: Generic vs. Unique • Product: specific model of car (ProdID is a generic key signifying a group of things, many instances) • Component: car door (CompID is a generic key) • Employee: assembly worker (EmpID is a unique key signifying a specific person, one and only one instance) • The design can use unique keys (e.g., serial numbers) for products and components.