70-442: Database Design Basics

Database Design Phases

Conceptual:  “sketch” of the database from initial requirements gathering and customer information. Business process, scope, and, most important and business rules. Capture this information in a conceptual data model consisting of a set of “high-level” entities and the interactions between them.

Logical: a refinement of the work done in the conceptual phase, transforming this into a full-fledged relational database design that will be the foundation for the implementation design. During this stage, you fully define the required set of entities, the relationships between them, the attributes of each entity, and the domains of these attributes (i.e., the sort of data the attribute holds and the range of valid values).

Implementation: In this phase, you adapt the logical model for implementation in the host
relational database management system (RDBMS; in our case, SQL Server).

Physical: This phase is also more or less the performance tuning/optimization phase of the project – indexes, disk layouts, and so on, come into play here (and not before this).

Relational Data Structures

Database and Schema: You use schemas to group objects in the database with common themes or even common owners.

Domains: The domain of a column is the set of valid values that the column is intended to store.

Keys : Every table should have at least one candidate key—an attribute (or combination of attributes) that can uniquely and unambiguously identify each instance of an entity. After the Primary Key (PK) is chosen, each remaining candidate key would be referred to as an Alternate Key (AK).
Natural Keys are things like NI numbers – danger here is that you do not control the format of these. Smart Keys are keys made up of various bits of information about the entity, nice because can provide users with additional info about entity, but do not guarantee uniqueness. Surrogate Keys or Artificial Keys are made up, e.g. AutoIds or GUIDs.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: