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