Table of Contents:

   Relational database model

There are three typical implementation models of databases: hierarchical, network, and relational. Each is based on the notion of data stored as a set of records (imagine a set of file cards, for example). Hierarchical (e.g., IMS) and network (e.g., IDMS) models are based on traversing data links to process a database; they are typically used for large mainframe systems and are not considered further here. [Slide15]

We focus on relational database management systems (RDBMSs). They have become popular, perhaps largely due to their simple data model:[Slide16]

• Data is presented as a collection of relations
• Each relation is depicted as a table
• Columns are attributes
• Rows ("tuples") represent entities
• Every table has a set of attributes that taken together as a "key" (technically, a "superkey") uniquely identifies each entity  [Slide17]

For example, a hospital might have an Employee table with a row for each employee. What attributes might be interesting? This, of course, depends on the application and use the data will be put to, and is determined at database design time. In our example, we might have the last name, the first name, the birth year and the sex.

The notion of view can be useful. Imagine that a hospital maintains a database of its employees -- there might be a lot of attributes like age, salary, emergency contacts, appraisal, etc. There may be needs to look at the database for different applications serving different users. The hospital may need to make available demographic data, for example, to a governmental agency. Only some of the attributes need be supplied - and others ought not to so as to protect privacy. Different views can be provided into the same data; in a RDBMS, a view can be seen as yet another table.[Slide18]

ER Data Modeling

Just a few words about design. How do you go about designing a database? It is useful to build a high level conceptual data model where we depict the entities that we are dealing with, their various attributes, and their relationships. An entity is some object with a real or conceptual existence in the world -- "tofu", "Advanced Java Class", "Folger Museum", "Elaine", "company", for example. An attribute is a property of an entity -- "address", "size", "mother", "age", for example [Slide19]. As mentioned above, a relational column is an attribute. A relationship defines roles in which entities work together -- "Bill – has - HIV.” RDBMSs represent relationships as tables. A side note for those already familiar with normalizing databases - ER design has been shown to give relations in third normal form. Also, ER diagrams can be mapped not just to RDBMS, but also to the network and hierarchical models. [Slide20]

It is relatively straightforward to represent a database design in graphical ER Diagrams, where rectangles represent entity types, diamonds relationship types, and ovals attributes. Underlined attribute names represent keys.

   SQL: Data definition and data manipulation language

SQL is both a Data Definition Language (DDL) and a Data Manipulation Language (DML). As a DDL, it allows a database administrator or database designer to define tables, create views, etc. As a DML, it allows an end user to retrieve information from tables. It came from an IBM Research project entitled "SEQUEL" where the intent was to create a structured English-like query language to interface to the early System R database system. Along with QUEL, SQL was the first high level declarative database language.

In this section, we will just give a few examples of SQL syntax to help suggest some familiarity with the style. For further reference, any number of books can be consulted. Also, SQL is widely used, and a quick search on the web came up with an excellent syntax reference.

Creating and Updating a Database

In this example that follows, we create a table and insert two records. Note that attributes are positional and are specified in the same order in Create Table, unless a specific ordered attribute list is specified in the Insert Into statement (non-specified values are null).

Create Table Patient

  (Name   char(20)  not null,

   Age   char(3)  not null,

   Address  varchar(20),

   Clinical_status   char(50)

  );

Insert Into Patient

  Values ("Patrick_O’Carroll", "32", "NA", "neutropenia");

Insert Into Patient (AP, PCV, Leucocytes)

  Values ("110", "20", "45,000");

Update Doses_of_Dopamine

  Set Doses_of_Dopamine = Initial * 1.2

  Where Evaluation > .02;

As you can see, SQL statements look a bit like English. The Delete statement (with a Where clause to specify conditions) removes selected tuples from a table.

What Is A Query?

A query is the question you ask, such as "Who are the students that Bryan Brown advises?", not the results (i.e., Jerry Turner). You may have several queries created as they may ask different specific questions. Once you have created a query, you can run it as often as needed because the results will change each time the table data is changed. [Slide21]

Querying from a Database

The Select (no relation to the relational algebra operation) statement is probably the most widely used SQL statement, and it is used to retrieve data from a database. It has many options, and we will again just give a few examples.[Slide22],[Slide23]

The most basic Select statement on, say, a table called Doctor, is

Select * 

  From Doctor;

This just returns all tuples in the Doctor table. We can be more selective and ask for, say, just the attributes Specialist, Years_of_Practice , and Age:

Select Specialist, Years_of_Practice, Age

  From Doctor;

This essentially applies the select and project relational operators to the table.

We can also apply conditions to be more selective. Maybe we want to look at our inventory of surgeons’ doctors with at least 50 years-old and see which ones (identified by their years_of_practice) have 50 years-old, as well as their hospital location location:

Select Years_of_Practice, Age, Hospital

  From Doctor

  Where Specialist = "Surgeon" and Age >= 50;

We can even retrieve from multiple tables. For each surgeon, let's look at its age, hospital, Last_name, and date_of_contract. We assume we have a table Department which has Specialty as key and Date_of_Birth and Name as some attributes. To illustrate a point, let's assume that both tables have Name as an attribute; the value in the inventory on-hand Bike table is a vendor-supplied name, while the value of Name in the Manufacturer table is the name of the manufacturer.

Select Age, Hospital, Last_Name, Date_of_Contract

  From Doctor, Department

  Where Specialty = "Surgeon" And 

        Doctor.Age = Department.Specialty;

Let's look at the SQL for the join example we illustrated above. It is fairly straightforward:

Select * 

  From Doctor Table2, Specialty Table

  Where Hospital = "Asia";

If we want to look at the distinct salaries we are paying to people in department A32, we can use the Distinct keyword:

Select Distinct Salary

  From Doctor

  Where Hospital = "Asia";

It's hard to believe all the theory that we very lightly touched on above lies beneath such straightforward declarative syntax.[Slide24]