Web App Development
Week 2
Data Modelling

Key Terminology

Relation: formal term for a table in a DB.

Tuple: formal term for a row DB. 
Technically each row is an “n-tuple”, the use of the “n-“ is usually skipped.

Cardinality: determined how many tuples are in a relation.

Attribute: formal term for a column in a tuple.

Degree of relation: determined by the number of attributes in a relation.

Heading & Body: the tuples are the body of a relation, the heading the field names.

Domain: the domain of an attribute is the kind or type of information it represents.
	  Note: this is not the same as data-type.

Entity: anything you are storing information about. E.g. Customers, Products,
Employee’s, etc.

Participants: the entities involved in a relationship

Degree of relationship: the number of participants in a relationship

Candidate Key: when considering the relation, attributes that could be used to uniquely identify any given tuple. A simple key is comprised of one attribute, a composite key multiple attributes.

Primary Key: a table may have multiple candidates. The one chosen as they key to identify the tuples is the primary key. This distinction becomes more useful when creating the actual tables. 

The Relational Data Model

The relational data model was first proposed by E.F. Codd in 1970 and defines a set of rules on how data can be represented (structure), protected (integrity), and manipulated.

Relation systems share these characteristics:
-	All data is conceptually represented in a series of rows and columns, called a relation
-	Values within the relation are scalar. That is there is only one value for any row/column.
-	An operation is performed on an entire relation, and returns an entire relation. This is called closure.

Data Model

The data model is a conceptual description of the problem space – the information you’re storing and working with. 
 
At a high level you can break down the problem into entities, categories of information you need to store. An entity could include things like Product Sales, Customer Info, Inventory, etc.

Once you’ve established your entities you’ll need to consider what facts you need to store about those entities. This is how you determine your attributes. The attributes are the facts you need to store in relation to the entity.

Next it’s considering the domain of the attribute. The domain consists of all the possible values of an attribute. E.g., Age could be an attribute. The age would be stored as an integer data type. But the domain of age would be all the possible values someone could be. You can define that as either an actual list, or as a rule.

 The domain of age could then be considered to be an integer in the range of 0 to 140. 
Note: a mechanism to enforce this rule would be considered part of the data integrity, not description.

Domains that are type compatible are any two domains that can be meaningfully related to each other. E.g. ProjectLeadID and EmployeeID could be used to establish which projects are lead by a particular employee. Linking EmployeeID with ProjectCost would not make sense.

Your data model should also establish relationships. Relationships are associations between entities. Saying “Employee’s get paid every two weeks” implies a relationship between Employee and Payroll. 

Most relationships are binary, between two relations. This is not a limit. Having a list of SallaryEmployees and ContractEmployees implies a relation with an EmployeeID, which could have a relation with ProjectLead. You could then ask “What are the names of contract employee’s who are project leads.

Also see:
http://en.wikipedia.org/wiki/Entity-relationship_model

Goals in Defining DB Structure

When working with the relational database model your aim is:
-	Ensure Flexibility
-	Eliminate Redundancy

On the surface redundancy may appear to just be a waste of resource. In a DB redundancy can make life far more complicated and in some cases cause a loss of data integrity.

Above is a table that’s storing redundant data. Why store the enrollment date under each course I’m listed for? 

Worse, the more times a user is forced to enter data the more times they’re likely to make an error. Look carefully at rows 2, 4. This type of error is an example of an update error.

Flexibility should be considered both in terms of can the DB easily tell us the information we are looking for? Can it be added on to?

Consider the example above… how easily can you extract a last name?

Normalization

To achieve these goals you structure your data through a process called normalization.
The normalization process is comprised of increasingly stringent rules called the normal forms. 

The First Normal Form
A relation is in the 1NF if all the underlying domains are scalar.

The Second Normal Form
A relation is in 2NF if and only if it is in 1NF and every non-key attribute is irreducibly dependent on the primary key.

The Third Normal Form
A relation is in the 3NF if it is in the 1NF, 2NF, and all non-key attributes are mutually independent (one cannot be derived by the combination of two others).

There are additional normal forms, however they are mostly related to dealing with unusual situations.

Data Types

Computers classify the type of information being stored. This is the information’s data type.

When writing a computer program you’ll likely have to decide what data type to make each variable. A small list of common examples may include:
-	Strings
-	Arrays / Lists
-	Integers
-	Booleans

Keep in mind that some languages are more granular in the classification than others. For example, Flash does not distinguish between integers (whole numbers) and floating points (fractional numbers with decimals) when using the Number data type. C#, on the other hand, has many classifications for the type of number including float, decimal, double, etc.

When creating database fields you must also indicate what data type that column will be used for. DB’s tend to use very stringent classifications for storing data. This selection can affect how much space data takes up and how efficiently a DB can perform.

Some common, but by no means complete, list of data types used by SQL Server 2005:

Integer Types:
-	BIGINT: uses 8 bytes of storage. Can contain numbers ranging from –2^63 to 2^63-1
-	INT: uses 4 bytes, can store number from –2^31 to 2^31-1
-	(skipping several)
-	TINYINT: uses 1 byte, stores a range from 0-255

For more data types please refer to Chapter 3 of your SQL Tex and check out:

http://www.databasejournal.com/features/mssql/article.phpr/2212141

Querying Across Multiple Tables

Next week we’ll look at the SQL involved in handling multiple tables in greater detail. For now here’s the basic Join.

SELECT columns
FROM table1 JOIN table2
ON table1.column1 = table2.column1

SELECT Title, FirstName, LastName, AddressLine1, City
FROM Person.Contact JOIN Person.Address
ON Person.Contact.ContactID = Person.Address.AddressID

Using Table aliases (note the “pc” and “pa” as a short form in the ON):

SELECT Title, FirstName, LastName, AddressLine1, City
FROM Person.Contact pc JOIN Person.Address pa
ON pc.ContactID = pa.AddressID