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
