Overview of Data Models

We now begin learning about how to organize and store data in a database.
In order to do this we need to learn about data models.
A data model is a notation for describing data or information.
It must describe three facets of the data:
1. The Structure of the Data: This concerns how data is organized into structures with properties. The description of the structures is similar to class or struct definitions used in C++/Java. The actual data structure used to implement these structure is called the physical data model. We will also be interested in higher level abstractions of these data structures known as the conceptual model.
2. Operations on the Data: This is usually a fixed set of primitive commands. These operations might be allowed queries to retrieve information from the data, and allowed modifications which can change the data stored. How these operations are carried out is determined by the DBMS, not the application programmer.
3. Constraints on the Data: Database data models usually have a way to describe limitations on what the data can be. For example, a day of the week value might be required to be an integer between 1 and 7.

Important Data Models

The two most important data models in use for database systems are:

The relational model, including object-relational extensions.
The semistructured-data model, including XML and related standards.

The Relational Model in Brief

The relational model is based on tables such as the following table for Movies:

title year length genre

Gone with the Wind 1939 231 Drama

Star Wars 1977 124 Sci-Fi

Wayne's World 1992 95 Comedy
The structure portion of the relational model might appear to resemble an array of structs in C where the the column headers are the field names, and each of the rows represents the values of one struct in the array.
This is only one of many possible ways that this data could be physically implemented as data structures.
The operations normally associated with the relational model form the "relational algebra". These operations are table oriented. For example, return all rows where the genre field is comedy.
Constraints in the relational model are given by indicating the type of a column. For example, that the length must be an integer.

title	year	length	genre
Gone with the Wind	1939	231	Drama
Star Wars	1977	124	Sci-Fi
Wayne's World	1992	95	Comedy

The Semistructured Model in Brief

Semistructured data resembles trees or graphs rather than tables or arrays.
Two common sources of semi-structured data are XML documents and JSON data.
XML is a language for specifying tagged based languages like HTML.

Using XML, we could come up with a language for movies and then create documents in it like:

<Movies> 
   <Movie title="Gone With the Wind">
   <Year>1939</Year> 
   <Length>231</Length>
   <Genre>drama</Genre>
   </Movie> 
   <Movie title="Star Wars">
   <Year>1977</Year> 
   <Length>124</Length>
   <Genre>sciFi</Genre>
   </Movie>
   <Movie title="Wayne's World">
   <Year>1992</Year>
   <Length>95</Length>
   <Genre>comedy</Genre>
   </Movie>
</Movies>

JSON stands for Javascript Object Notation. It is basically the format Javascript uses to serialize objects. For example,
```
{
  "MyFavoriteString" : "The quick brown fox jumped over the lazy dog",
  "MyFavoriteArray" : [1, 2, 3]
}
```

Three Data Facets for Semi-Structured data

The structure of an semi-structured data model is determined by how elements such as tags or {}, [] can be nested.
An XML Schema or DTD can be used to define this for a particular XML language and the Javascript spec determines this for JSON.
Operations on XML data are often carried out using DOM commands, and queries can be handled using XQuery. For example, if we wanted a query to return the contents of all year tags in the above document, we might use the query /movies/movie/year . This query specifies paths to retrieve from the document.
JSON operations are essentially anything Javascript can do to a Javascript object.
XML constraints can be specified using an XML Schema or a DTD, and can say what kind of values a tag can have for its content.
Constraints in JSON are determined by the Javascript language.

Other Data Models

There are many other models that are, or have been, associated with DBMSs.
In the 80's and 90's, one trend was to extend the relational model with object oriented features.
Two major changes to relations this causes are:
1. Row values can have structure rather than just being elementary types like integers or strings.
2. Relations can have associated methods (sometimes called triggers). These can do things like, if a row is inserted in table X then modify table Y and Z in such and such way.
Besides Object-oriented models, we've previously mentioned that a tree-oriented model called the hierarchical model and a graph-oriented model called the network model were often used in early DBMS systems.
IBM's Information Management System (IMS) developed starting in 1966 for Apollo Space program used the Hierarchical Model.
General Electrics's Integrated Data Store (IDS) (1964) used the network model.

Comparing Approaches

Semistructured models have more flexibility than relations.
Nevertheless, the relational model is still preferred in most common DBMSs.
Some reasons for this are: efficiency of data access, efficiency of data modification, and ease of use.
The relational model achieves these by:
1. Providing a simple, limited approach to structuring data that is reasonably versatile, allowing anything to be modeled.
2. Providing a limited yet useful collection of operations on data.
Using the relational model we can implement a language like SQL that enables programmers to express their information wishes at a very high level.
A few lines of SQL can do the same thing as thousands of lines of C, and further, since the base operations are so restricted, it can be highly optimized.

Basics of the Relational Model

Data in the relational model is represented in a two dimensional table called a relation. For example,

title year length genre

Gone with the Wind 1939 231 Drama

Star Wars 1977 124 Sci-Fi

Wayne's World 1992 95 Comedy
As we said earlier, each row in the above represents a movie, and each column, represents a property of a movies.
To continue fleshing out this model, we need to fix some terminology regarding relations...

title	year	length	genre
Gone with the Wind	1939	231	Drama
Star Wars	1977	124	Sci-Fi
Wayne's World	1992	95	Comedy

Relational Model Terminology

The names of columns of a relation are called attributes. For example, title, year, length, and genre in the Movie relation.
The name of a relation and the set of attributes for a relation is called the schema for that relation. For example,
```
Movies(title, year, length, genre)
```
The attributes in a relation are a set, not a list, so order doesn't matter. The physical model is allowed to store the data in whatever order is most efficient.
The order we give to the attributes in a schema when we first talk about a relation is called the standard order.
In the relational model, a database consists of one of more relations.
The set of schemas for a relational database is called a relational database schema or just a database schema.
Rows of a relation are called tuples. A tuple has one component for each attribute of the relation. For example, one tuple out of the Movies table is:
```
(Gone With the Wind, 1939, 231, drama)
```
The SQL command used to insert a tuple into a relation is INSERT. For example,
```
INSERT INTO MOVIES VALUES
   ("Gone With the Wind", 1939, 231, "drama");
```

Relational Model Terminology continued

Each component of a tuple must be atomic, that is, it must be of some elementary type such as an integer or string without any substructure.
Associated with each attribute of a relation is a domain that specifies the elementary type for that tuple component.

We can add domain info to our relation schemas by using : as we do below:

Movies(title:string, year:integer, length:integer, genre:string)

The SQL command used to define a relation is called CREATE TABLE. For example,

CREATE TABLE Movies(
    title VARCHAR(50),
    year INT,
    length INT,
    genre VARCHAR(10)
);

Relations should be viewed as a set of tuples not a list of tuples. So two relations with the same tuples but stored in a different order physically should be viewed as equivalent.
There are many possible sets of tuples that could be stored in a given relational schema. Any such set is called a relational instance. Different relational instances might arise for example if the data in database changes because of a user action.
The set of tuples stored in a relation "now" is called the current instance.
A set of attributes which, if fixed, fixes the rest of the attributes of a tuple is a called a super-key. A minimal super-key is called a key. For example, title, year together might be a key for the Movies table. It is contained in the super-key title, year, length.

In-Class Exercise

Come up with a relation to describe pages in a wiki system. Annotate the attributes with appropriate domains. Write down some example tuples. Give an example key.

Post your solution to the Sep 4 In-Class Exercise Thread.

Example Relational Database Schema for a Movies Database

Movies(
   title:string,
   year:integer, 
   length:integer,
   genre:string,
   studioName:string, 
   producerC#:integer
)
MovieStar(
   name:string,
   address:string,
   gender:char,
   birthdate:date
)
StarsIn(
   movieTitle:string, 
   movieYear:integer, 
   starName:string
   )
MovieExec(
   name:string, 
   address:string, 
   cert#:integer, 
   netWorth:integer
)
Studio(
   name:string,
   address:string, 
   presC#:integer 
)

Data Models

Outline

Introduction