Data Modeling

1. Data Modeling

2. Data Modeling • A database can “model” a “world” which is seen as: • a collection of entities, • relationships among entities. • An entity(-instance) is an individual “object” that exists and is distinguishable from other individuals. Example: specific person, company, event, plant • Entities have attributesExample: people have names and addresses • An entity set (also entity type) is a set of entities of the same type that share the same properties. Example: set of all persons, companies, trees, holidays

3. Entity Sets customer and loan customer-id customer- customer- customer- loan- amount name street city number

4. Attributes • An entity is represented by a set of attributes, • i.e. descriptive properties possessed by all members of an entity set. Example: customer = (customer-id, customer-name, customer-street, customer-city) loan = (loan-number, amount) • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite attributes. • Single-valued and multi-valued attributes • E.g. multivalued attribute: phone-numbers • Derived attributes • Can be computed from other attributes • E.g. age, given date of birth

5. Composite Attributes

6. Relationship Sets • A relationship (-instance) is an association among several entitiesExample:HayesdepositorA-102customer entity relationship [set] account entity • A relationship set is a mathematical relation among n 2 entities, each taken from entity sets {(e1, e2, … en) | e1  E1, e2  E2, …, en  En}where (e1, e2, …, en) is a relationship • Example: (Hayes, A-102)  depositor

7. Relationship Set borrower

8. Relationship Sets (Cont.) • An attribute can also be property of a relationship set. • For instance, the depositor relationship set between entity sets customer and account may have the attribute access-date

9. Degree of a Relationship Set • Refers to number of entity sets that participate in a relationship set. • Relationship sets that involve two entity sets are binary (or degree two). Generally, most relationship sets in an E-R schema are binary. • Relationship sets may involve more than two entity sets. • E.g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, job and branch • Relationships between more than two entity sets are relatively rare. Most relationships are binary.

10. Mapping Cardinalities • Express the number of entities to which another entity can be associated via a relationship set. • Most useful for binary relationship sets. • For a binary relationship set, the mapping cardinality must be one of the following types: • One-to-one • One-to-many • Many-to-one • Many-to-many

11. One-to-one One-to-many Note: Some elements in A and B may not be mapped to any elements in the other set

12. Many-to-one Many-to-many Note: Some elements in A and B may not be mapped to any elements in the other set

13. Cardinalities affect ER Design • Can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer • I.e., the relationship from account to customer is many-to-one, or equivalently, customer to account is one-to-many

14. E-R Diagrams • Rectangles represent entity sets. • Diamonds represent relationship sets. • Lines link attributes to entity sets and entity sets to relationship sets. • Ellipses represent attributes • Double ellipses represent multivalued attributes. • Dashed ellipses denote derived attributes. • Underline indicates primary key attributes

15. E-R Diagram With Composite, Multivalued, and Derived Attributes primary key composite multi-valued derived

16. Relationship Sets with Attributes

17. Roles • Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. • Role labels are optional, and are used to clarify semantics of the relationship

18. Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • E.g.: One-to-one relationship: • A customer is associated with at most one loan via the relationship borrower • A loan is associated with at most one customer via borrower

19. One-To-Many Relationship • In the one-to-many relationship • a loan is associated with at most one customer via borrower, • a customer is associated with several (>= 0) loans via borrower

20. Many-To-Many Relationship • A customer is associated with several (possibly 0) loans via borrower • A loan is associated with several (possibly 0) customers via borrower

21. Participation • Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g. participation of loan in borrower is total • every loan must have a customer associated to it via borrower • Partial participation: some entities may not participate in any relationship in the set • E.g. participation of customer in borrower is partial

22. Alternative Notation for Cardinality Limits • Cardinality limits can also express participation constraints

23. Specialization & Generalization • Similar concept to inheritance • Designated by the ISA relationship • If A ISA B then every A entity is also considered to be a B entity • Depicted by a triangle labeled with ISA • Can be done top-down (specialization) or bottom-up (generalization) • Specialization and Generalization are simple inverses the the design process

25. ER Design • ER design is subjective. There are often many ways to model a given scenario! • Analyzing alternatives can be tricky, especially for a large enterprise. • Ensuring good database design: • resulting relational schema should be analyzed and refined further (normalization).

26. A Sample ER-Diagram

27. Heartland Properties • Heartland Properties, Inc. (HPI) is a property management company that manages rental properties on behalf of owners.   The company offers a comprehensive collection of services to these owners, including advertising the property, interviewing potential renters, negotiating leases, inspecting and maintaining the property, and collecting rent from the renters.  The following is a brief description of the kinds of data Heartland Properties is concerned with. • Offices • HPI has offices in several cities throughout the Heartland.   Each office is identified by an Office Number.   Each office has an address (Street, City, State, Zipcode), and telephone and fax numbers.  Each office also has a year in which it was opened.

28. Heartland Properties • Employees • Employees of HPI are assigned to work at branch offices.  Each employee has an employee identifier, first and last names, a social-security number, as well as a home address and telephone number.  For each employee, HPI keeps track of when that employee was hired, the employee's gender, and job title.  For each employee, HPI keeps track of a single next-of-kin who may be notified in the event of an emergency.   • Employees are divided into three categories:  Managers, Associates, and Administration Assistants.  Managers are salaries employees and a given a monthly allowance for maintaining a company car.   Associates and administrative assistances are both hourly employees, and HPI tracks their hourly rate.   • Each office has one senior manager, and several regular managers.  Each manager manages up to 10 associates and administrative assistants.

29. Heartland Properties • Owners • Owners own the properties HPI manages.  Owners may be either business or private owners.  In either case, an owner is given a unique owner number, and HPI records the owner's name, address, and telephone number.  For owners that are businesses, HPI also must record the name of a contact person. • Properties • Each property has a unique property number, as well as an address.  Moreover, the property is managed by a particularly HPI office.  Each property has a type (house, apartment), a particularly number of rooms, and a particular number of baths. • Renters • In order for the business to work effectively, HPI must be able to identify quickly the properties that are of potential interest to a client.  Consequently, when a client approaches HPI, the company records the client's maximum monthly rent, the type of property (apartment, house), and desired number of rooms. The basic client data include a unique client number, the client's name, address, and phone number.   HPI also records the employee who initially interviews the renter.

30. Heartland Properties • The Rental Process • When a client, a potential renter, approaches HPI, a process begins which continues until HPI's association with that renter has ended.  Following the preliminary interview, the following stages may be followed: • Viewing properties. A client may wish to see one or more properties before deciding to lease.  HPI must keep track of which property was viewed, the date it was viewed, the HPI employee who accompanied the client, and any comments the client made about the property. • Drawing up of a lease.   If a client agrees to lease a particularly property, HPI draws up a lease.  The lease has a unique lease identifier, and includes details of the lease, including the property to be rented, the monthly rental, the rental deposit, the start and end dates of the lease, and the manager who signs off on the lease. • Collection of rent.  When a client rents a property, he or she is responsible for monthly payments.  Each payment is recorded by noting the day on which payment was received, the amount of the payment, the property for which payment was made, the renter making the payment, and the number on the check used to make the payment. • Property Management • One of the services HPI provides to owners is the regular inspection and maintenance of the properties.  Each property is inspected no less frequently than every six months.  Whenever a lease ends, the property is also inspected.  For each inspection, HPI keeps track of the date, property, and HPI employee making the inspection.  The employee's comments following the inspection are also recorded.

31. Heartland Properties • Management Reports • Rental listings • Each day, each HPI office prints out a list of all of the properties currently available for rent.  An example of such a report is found below. • Property Inspection Report • Periodically, management wishes to see a record of the inspections performed on a particular property.  One such listing appears as follows:

32. Heartland Properties ER-Diagram

33. ER Design to DB Schema • Entities become tables with the given attributes • Relationships become foreign keys • Many to One and One to Many • Foreign key to the One goes in the Many table • Many to Many • Create a relationship table with foreign keys to both sides of the relationship • Also add in any attributes of the relationship

34. ER Design to DB Schema • 3 tables • customer - id, name, street, city • account - number, balance • depositor • primarykey, customer_id, account_id, access-date

35. ER Design to DB Schema • Composite Attributes • typically flattened out to use the specific info • Multi-valued Attributes • build a separate schema • new schema contains the information and the primary key for the entry they belong to.

36. Heartland Properties Schema

37. Program Data Modeling • ER Design works great when you are designing the database right from the start. • However, sometimes it may be easier to map Object - Oriented program data • Map Objects and Object hierarchy to DB • Many ways to do it. What are the choices?

38. Account Owner owner_ id_ : String name_ : String balance_ : double taxId_ : String 1 * InterestBearingAccount CheckingAccount rate_ : double checkFee_ double termDays_ : int minimumBalance_ : double Object/RDBMS • How do we map the following Class Model to an RDBMS One to Many Relationship between Owner and Account InterestBearingAccount and CheckingAccount inherit from Account

39. Account Owner owner_ id_ : String name_ : String balance_ : double taxId_ : String 1 * InterestBearingAccount CheckingAccount rate_ : double checkFee_ double termDays_ : int minimumBalance_ : double Horizontal Partitioning • Each concrete class is mapped to a table

40. Account Owner owner_ id_ : String name_ : String balance_ : double taxId_ : String 1 * InterestBearingAccount CheckingAccount rate_ : double checkFee_ double termDays_ : int minimumBalance_ : double Vertical Partitioning • Each class is mapped to a table

41. Account Owner owner_ id_ : String name_ : String balance_ : double taxId_ : String 1 * InterestBearingAccount CheckingAccount rate_ : double checkFee_ double termDays_ : int minimumBalance_ : double Unification • Each sub-class is mapped to the same table

42. Horizontal Partitioning entire object within one table only one table required to activate object no unnecessary fields in the table must search over multiple tables for common properties Vertical Partitioning object spread across different tables must join several tables to activate object Vertical Partitioning (cont.) no unnecessary fields in each table only need to search over parent tables for common properties Unification entire object within one table only one table required to activate object unnecessary fields in the table all sub-types will be located in a search of the common table RDBMS Mapping