Database Design Component

Conceptual database design component

• Entity ‘’object’’ about which data is collect it may be a person ,place,event,actual,or simple a concept .instance an individual occurrence of an entity .external entity: an entity used to exchange data but which Is not store in the database Attribute a unit fact about particular entity;the fact should be atimic (indivisible)
• Relationship an association among entities (see relationship section below)
• Business rule: policy,procedure,or a standard that an organization uses and which dictates certain control on the data ;often implemented in database as constraints
  
  
  
  
  
  
  
  Relationships
       
• Maximum cardinality: the maximum number of instances one entity can be associated with
• Transferable a relationships is transferable if the parent may change over time
• One-to-one .conditional in one direction : means that a corresponding Record may or not be found on the optional side of the Relationship .
• conditional in both direction : means that a corresponding Record may or not be found on the optional side of the Relationship .
• mandatory in one direction : means that a corresponding Record must exist on the mandatory side of the relation .
• mandatory in both direction : means that corresponding record must be found on both side relationships One-to many : indicates that a record in oine table may be related to many (usually 0+) record in another table
• Intersection data:data that is associated with two related entities in a many-to-many relationship and which only make sense when associated with both related entities ; intersection data can be placed (mapped) into a separated table to help relational database handle the many –to
  
  ERDs (ENTITY-RELATIONSHIP DIAGRAM)
  
  
• Graphical data model
• Entities are represent by rectangle
• Unique edentifier (primary key) located in rectangle at top of the entity rectangle its is unique identifier for
  
  
  

Employee

Employee ID

Last name First name hourly rate Position

• Relationships
  
• Zero or zero one :
  
      
• Exactly one:
  
     
  
  
• One or more:
  
  
  
• Zero or more :
  
  
• Business rules are not usually  includes in the ERD graphic , but are often includes as text attachements
  
  Logical Database design components
  
  
• Tables: a 2-D logical structure like a grid where each row contains attributes about a single instance of the entity type the table represent , and each column represent a particular attributes :
  
  
  a.Entities are sometimes split into two tables
  b.different entitites are sometimes merged into a signal     table
  c.entities are usually namd using a plural , while tables are named in the singular
  d.different DBMSs and organizations have different naming standard,buts assume that mixed case and spaces within names can cause conversion problem later , and that underscores are useful for separating words within a name
  
  
• Column: the smallest named unit of data in a database
  a. Columns must be given a data type
  b. Data types helps the database store data efficiently
  c. Data type restrict attributes value of the correct data type   and provides a set of behavior consistent with the speciefied data type (such as addition,subtraction,etc for numbers)
  d. Unfortunately,different vendors support differing zoos of data type
  
  
• Contraints :rules that restrict allowable data values
  A. primary key: one or more columns that uniquely identify a particular row in a table
    I . the constraint is that duplicate values are not allowed in  the primary key column(s) of a table
   II. Primary keys are usually implemented as an index
  III. An index speeds up searches
  
  B. Foreign key; a field on the many-side side of a one-to-many relationship that uniquely identify one row in another table (usually by using the primary key in the latter table)
  
  C. Referential constraints:
    
   I. can check for parent record when inserting new child record (using the child’s record foreign key to check for a matching parent record )
  
  II. Don’t allow modification of child record’s foreign key if the new value is not represent by an instance in the parent table
  
  III. Can delete all matching child record when a parent record is deleted
  
  D. Intergrity constraints : used to make sure field( attributes) values that are invalid are not allowed
    I.may check for a range of values or specific valid value
   II.may check for NOT NULL
  
  E. Triggers : a Triggers is a program stored in a database that runs when a specific events happens. Triggers can be used to validate data(among other things)

• Surrogate key : a key used to replaced what would be the natural key for an entity
• Views: refer to the ways different user may see the same database differently
  
  a.views are stored quires(virtual table)
  b.views a hide column(cleaner,more secure)
  c.views can hide tables(cleaner,more secure)
  d.views can hide complex operation such as joins
  e.views may improve query performance