Conceptual Database Design Components



~ Antity : an “object” about which data is collected ;it may be a person , place , event , actual physical object , or simply a concept .
· Instance : an individual occurrence of an entity .
· External entity : an entity used to exchange data , but which is not stored in the database
~ Attribute : a unit fact about a particular entity : the fact should be atomic (indivisible)
~ Relationship : an association among entities ( see Relationships section below )
~ Business rule : a policy , procedure , or a standard that an organizations used and which dictates certain control on the data ; often implemented in databases as constraints .


Relationship


~ Maximum cardinality : the maximum number of instances one entity can be associated with
~ Minimum cardinality : the minimum number of instances one entity can be associated with
~ ransferable : a relationship is transferable if the parent may change over time
~ one-to-one
· Conditional in one direction : means that a correspondingrecord may or not be found on the optional side of the relationship.
· Conditional in both directions : means that corresponding records may or not be found on both sides of the relationship.
· Mandatory in one direction : means that a corresponding record must exist on the mandatory sied of the relationship.
~ one-to-many : indicates that a record in on etable may be related to many ( usually 0+ ) records in another table .
~ Many-to-many : this can be thought of as a one-to-many relationship that goes in both directions; many-to-many relationships are not natively supported in relational databases , but ther ways to convert them into something that can be handled

~ Intersection data : data that is associated with two related entities in a many-to-many relationship , and which only makes sense when asociated with both related entities; intersection data can be placed ( mapped ) into a saparate table to help relational database handle to many-to-many relationship .
~ Recursive : refers to relationships between instances of the same entity type .
ERDs ( Entity – Relationship Diagrams )
· 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

· RELATIONSHIPS

· ZERO OR ONE

· EXACTLY ONE

· ONE OR MORE

· ZERO OR MORE





~ Business rules are not usually included in the ERD graphic , but are often included as text attachments.
Logical Database Design Components
~ Table: a 2-D 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 tables .
Ø Different entities are sometimes merged into a single table ( rare ) .
Ø Entities are usually named using a plural , while tables are named in the singular.
Ø Different DBMSs and organizations have different naming standards , but assume that mixed case and spaces within names can couse conversion problems later , and that underscores are useful for separating words within a name
~ COLUMN : the smallest named unit of data in a database
Ø Columns must be given a data type.
Ø Data types help the database store data efficiently.
Ø Data types restrict attribute values to the correct data type and provides a set of behaviors consistents with the specified data type (such as addition , subtraction , etc , for numbers )
Ø Unfortunately , different vendors support differing zoos of data types .
~ CONSTRAINTS : rules the restrict allowable data values
· Primary key : one or more columns that uniquely identify a particular row in a table

o The constraint is that duplicate values are not allowed in the primary key column(s) of a table.
o Primary key are usually implemented as an index
o An index speeds up searches
· Foreign key : a field on the many-side side of a one-to-many relationship that uniquely identifies one row in another table ( usually by using the primary key in the latter table )
· Referential constraints :
o Can check for parent record when inserting new child record ( using the child record’s foreign key to check for a matching parent record )
o Don’t allow modification of child record’s foreign key if the new value is not represented by an instance in the parent table .
o Can delete all matching child record when a parent record is deleted .
· Intergrity constraints : used to make sure field ( attribute ) values that are invalid are not allowed.
o May check for a range of values , or specific valid values
o May check for NOT NULL
· TRIGGERS : a trigger is a program stored in the database that runs when a specific event happens . Triggers can be used to validate data ( among other things )
· Surrogate key : a key used to replace what would be a natural key for an entity
· Views : refers to the way different user may see the same database differently
o Views are stored queries ( virtual tables )
o Views can hide columns ( cleaner , more secure )
o Views can hide table ( cleaner , more secure )
o Views can hide complex operations such as joins
o Views may improve query performance