relationship

RELATIONSHIPS

RELATIONSHIPS

ERDs(ENTITY-RELATIONSHIP DIAGARAMS)

• Graphical data model
• Entities are represented by rectangles
• Unique identifier (primary key) located in rectangle at top of the entity rectangle it is a unique identifier for

Employee

EmployeeID

LastNmae FirstName HourlyRate Position

• Business rules are not usually included in the ERD graphic but are often included as text attachments

LOGICAL DATABASE DESIGN COMPONENTS

• TABLE: a -2D logical structure like a grid where each row contains attributes about a single instance of the entity type the table represents, and each column represents a particular attribute

• Entities are sometimes split into two tables
• Different entities are sometimes merged into a single table (rare)
• Entities are usually named using a plural, while table are named in the singular
• • Different DBMS and organizations have different naming standard, but assume that mixed case and spaces within names can cause conversion problems later, and that underscores are useful for separating words within a name
  
  
  ·         Column: the smallest named unit of data in database
                          
  • Columns must be given a database
  • Data type help the database store data sufficiently
  • Data type restrict attiributes value to the correct data type and provides a set of behaviors consistent with the specified data type (such as addition, subtraction, etc. for numbers)
  • Unfortunately, different vendors support differing zoos of data type.
  
  
  • Contraints: rules that restrict allowable data values
  
  
  • Primary key: one or more column that uniquely identify a particular row in a table
  
  
  • The constraint is that duplicate values are not allowed in the primary key columns(s) of a table
  • Primary keys are usually implemented as an index
  • An index speeds up searches
  
  
  • Foreign key: a field on the many-side side of a one-to-many relationships that unique identifies one row in another table (usually by using the primary key in the latter table)
  • Referential constraint:
  
  
  • Can check for parent record when inserting new child record (using the child record’s foreign key to check for a matching parent record)
  • Don’t allow modification of child record’s foreign key if the new value is not represented by an instances in the parent table
  • Can delete all matching child record when a parent record is deleted
• • Integrity constraint: used to make sure field (attributes) values that are invalid are not allowed
  
  
  • May check for arangeof values, or sprcific valid values
  • May check for NOT NULL
  
  
  • Trigges: a trigger is a program stored in the databse that runs when a specific event happens. Triggers can be used to validate data (among other tjings)
  
  
  • Surrogate key: a key used to replace what would be the natural key for an entity
  • View:refers to the way different users may see the same databse differently
  
  
  • View are stored queries (virtual table)
  • Views can hide on columns (clearer, more secure)
  • Views can hide table (cleaner, more secure)
  • Views can hide complex operation such as joins
  • Views may improve query performances