1 / 65

Data Modeling

Learn about data modeling which involves entities, attributes, relationships, and cardinalities in databases. Understand how entities and sets interact, and how cardinalities affect database design.

rsutton
Download Presentation

Data Modeling

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

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

  25. 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 generally goes in the Many • Many to Many • Create a relationship table with foreign keys to both sides of the relationship • Also add in any attributes of the relationship

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

  27. Ruby on Rails & Databases

  28. Ruby on Rails & Databases • Active Record • Active Record in Rails • CRUD & Other Stuff • Mapping Cardinalities • Migrations • Demo

  29. Active Record • Object Relational Mapping (ORM) tool supplied with Rails • Maps • Tables to classes • Rows to objects • Columns to object attributes • determined at run time

  30. Active Record Basics • Create a subclass of ActiveRecord::Base • class Employee < ActiveRecord::Base end • Rails assumes that • the name of the table is the plural form of the class name • if the name contains multiple camel-case words, the table name has underscores between the words We don’t declare the attributes

  31. Active Record in Rails • Active Record is used for Model • script/generate model person • Will create app/models/person.rb • class Person < ActiveRecord::Base end • Maps to ‘people’ table in database • can be changed • class Person < ActiveRecord::Base set_table_name “blah” end • Columns automatically map to class variables of the same name

  32. CRUD & Other Stuff • Create • Read • Update • Delete • Other ActiveRecord Functions

  33. Create • Create row by creating object an_order = Order.new an_order.name = “Dave Thomas” an_order.address = “122 Main” an_order.phone = 2125551212 an_order.save an_order = Order.new( :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 ) an_order.save Order.new do |o| o.name = “Dave Thomas” o.address = “122 Main” o.phone = 2125551212 o.save end Note: We didn’t need to set a primary key. Rails assumes “id” is primary key and set autoincrement

  34. Create • Can also use create method • Creates a new object and saves it • Takes a hash or an array of hashes an_order = Order.create( [ { :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 }, { :name => “Another Name”, :address => “blah”, :phone => 1234567890 } ] ) an_order = Order.create( :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 )

  35. Read • We need to specify which rows we want • Rails will return objects containing the data from those rows in the database • Use the find method with one or more primary keys • an_order = Order.find(27) • product_list = Order.find(params[“product_list”]) • find() will throw a RecordNotFound exception if any of the requested primary keys cannot be found

  36. Read • find() also has other options • can pass :all or :first along with other parameters • :conditions => “name = ‘Dave’” • corresponds to WHERE clause • :order => “name” • corresponds to ORDER BY clause • :limit => pagesize • corresponds to LIMIT • :offset => pagenum * pagesize • use in connection with :limit to step through query results • an_order = Order.find(:first, :conditions => “name = ‘Dave Thomas’”) • orders = Order.find(:all, :conditions => “name = ‘Dave’”, :order => “pay_type, shipped_at DESC”, :limit => 10)

  37. Read • Allowing for externally generated parameters • pname = params[:name] orders = Order.find(:all, :conditions => [“name = ?”, pname]) • orders = Order.find(:all, :conditions => [“name = :name”, {:name => pname}]) • Can also write your own SQL • orders = Orders.find_by_sql(“select * from orders”) • single parameter - SQL string • May also be an array where first element is SQL with place holders. The rest is a list of values or hash • Nice for hard queries or performance

  38. Update • Simple • find the row or rows using find • update necessary fields • save • Also works with an array for multiple update • orders = Order.find(:all, :conditions => “name like ‘Dave%’”) orders[0].name = “Fred” etc. • May also use update() or update_all() • order = Order.update(123, :name => “F”, :address => “blah”) • finds, updates, saves, and returns object • result = Order.update_all(“set clause”, “where clause”) • returns number of rows updated order = Order.find(123) order.name = “Fred” order.save

  39. Delete • delete & delete_all • Order.delete(123) • Order.delete([1,2,3,4]) • Order.delete_all([“price > ?”, maxprice])

  40. Other ActiveRecord Stuff • Magic column names • id • primary key • created_at, created_on, updated_at, updated_on • automatically updated with timestamps • xxx_id • foreign key • Find by value of a particular column • Dynamically associates a find_by and find_all_by method with each column • order = Order.find_by_name(“Dave Thomas”) • order = Order.find_by_address(“123 Main”) • orders = Order.find_all_by_email(params[“email”])

  41. Mapping Relationship Cardinalities

  42. Relationships between Tables • Relationships are established using foreign keys • Foreign key columns should be • named using the singular form of the table name with _id appended • example: a foreign key for the table products should be product_id • This expresses relationship, but not the cardinality of the relationship

  43. Specifying Relationships • Relationships are specified by adding declarations to models • has_one, has_many, belongs_to, has_and_belongs_to_many • Rule of thumb • Foreign key always has the belongs_to declaration

  44. One-to-onenote: the model for the table that contains the foreign key *always* has the belongs_to declaration

  45. One-to-many

  46. Many-to-manyMany-to-many associations are symmetrical—both of the joined tables declare their association with each other using has_and_belongs_to_many.

  47. Relationship methods • Relationship declarations also introduce methods to the associated objects. • dynamically created • named using the table that it refers to • Help navigate between the linked objects

  48. belongs_to methods • product(force_reload=false) Return the associated product (or nil if no associated product exists) The result is cached, and the database will not be queried again when this association is subsequently used unless true is passed as a parameter. • product=obj Associate this line item with the given product, setting the product_id column in this line item to the product’s primary key. If the product has not been saved, it will be when the line item is saved, and the keys will be linked at that time. • build_product(attributes={}) Construct a new product object, initialized using the given attributes. This line item will be linked to it. The product will not yet have been saved. • create_product(attributes={}) Build a new product object, link this line item to it, and save the product. class LineItem < ActiveRecord::Base belongs_to :productend

  49. Example class Product < ActiveRecord::Base has_many :line_items end class LineItem < ActiveRecord::Base belongs_to :product end item = LineItem.find(2) # item.product is the associated Product object puts "Current product is #{item.product.id}" puts item.product.title item.product = Product.new(:title => "Rails for Java Developers" , :description => "..." , :image_url => "http://....jpg" , :price => 34.95, :available_at => Time.now) item.save! # save or raise exception puts "New product is #{item.product.id}" puts item.product.title Current product is 1 Programming Ruby New product is 2 Rails for Java Developers ActiveRecord takes care of the details It created a new product and linked the LineItem to it via the foreign key

More Related