Conceptual database design component

● entity: “objet” about which data is collected; it may be a person, palce , event, actual physical object , or simply a concept

● instance : an individual occurrence of an entity

● external entity : an entity used to excharge data, but which is not stored in the database

● attribute : a unit fact about a particular entity ; the fact should be atomaic ( indivisible )

● relationship: an association among entities ( see Relation Section below )

● business rule: a policy , procedure , or standard that an organization uses and which dictates certain control on the data ; implement in database as constraints

Relations

●maximum cardinatily: the maximum number of instances one entity can be associated with

●minimum cardinality : the minimum number of instances one entity can be associated with

●transferable : a relationship is transferable if the parent may change over time

●one-to-one :

●conditional in one direction : means that a corresponding record may not be found on the optional side of the relationship

●conditional in both directions: means that corresponding records may or not be foundon both sides of the relationship

●mandatory in one directions: mean that a corresponding record must exist on the mandatory side of the relationship

●mandatory In both direction: means that corresponding records must be found on both sides of the relationship

●one-to-many : indicates that a record in one table 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 direction: many-to-many relationship are not natively support in relational database , but there are 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 associated with both related entities; intersection data can be placed ( mapped )into a separate table to help relational database handle the many-to-many relationship

●recursive: refers to relationship between instances of the same entity type

ERDs (Entity-relationship diagrams)

●graphical data model

●entities are represented by rectangle

●unique identifier (primary key) located in rectangle at top of the entity rectangle it is a unique identifier for

●relationship

-Zero or one:

-exactyl 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: 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 two 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 organization have different naming standards, 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 a database

-Column 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 consistent with the specified data type (such as addition, subtraction, etc.for numbers).

-Unfortunately , different vendors support differing zoos of data types.

●constraints: rules that restrict allowable data values

-primary key: one or more columns that uniquely identify a particular row in a table

●the constraints is that duplicate values are not allowed in the primary key column(s) of a table

●primary keys are usually implement as an index

●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 letter table)

-referential constraints:

●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 instance in the parent table

-integrity constraints : used to make sure field(attribute) values that are invalid are not allowed

●may check for a range of values, or specific valid values

●may check for NOT NULL

-triggers: A trigger is a program stored in the database that runs when a speficic event happens. Triggers can be used to validate data(among other things).

●surrogate key: a key used to replace what would be the natural key for an entity

●views: refers to the ways different users may see the same database differently

-views are stored queries( virtual tables)

-views can hide columns (cleaner , more secure)

-views can hide tables(cleaner, more secure)

-views can hide complex operations such as joins

-views may improve query performance