![Page 1: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/1.jpg)
Relational DatabasesLecture 2Chapter 3
Robb T. Koether
Hampden-Sydney College
Fri, Jan 18, 2013
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 1 / 26
![Page 2: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/2.jpg)
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 2 / 26
![Page 3: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/3.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 3 / 26
![Page 4: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/4.jpg)
Types of Databases
Originally, “databases” were simply collections of data, arrangedin whatever way the programmer saw fit.The “DBMS” was simply a program that could process the data.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 4 / 26
![Page 5: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/5.jpg)
Types of Databases
In the early 1970s, the concept of a relational database (RDB)was introduced.Its data were organized into tables of rows and columns.Queries were organized into select, insert, delete, and update.Given the standardized forms, queries could be highly optimizedby the DBMS.Today, SQL (Structured Query Language) is the standard forrelational databases.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 5 / 26
![Page 6: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/6.jpg)
Types of Databases
Catalog of Items
Item Price Stock
Sales
Cust Item Qty
Bob
Alice
123
123
4
2
456
123
6.99
3.99
1500
1000
: : : :::
A Relational Database (SQL)
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 6 / 26
![Page 7: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/7.jpg)
Types of Databases
More recently, other forms have appeared, most notablyExtensible Markup Language (XML).An XML database is structured hierarchically.The underlying theory is less mature.The supporting software is less extensive.The queries are less optimized.On the other hand, the structure is much more flexible.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 7 / 26
![Page 8: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/8.jpg)
Types of Databases
Catalog
Item2Item1
...
StockPrice
...
Sales
Cust2Cust1
...
QtyItem
...
Company
A Hierarchical Database (XML)
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 8 / 26
![Page 9: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/9.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 9 / 26
![Page 10: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/10.jpg)
Relational Databases
A relational database is a database in which the data are stored intabular form, arranged in rows and columns.Traditionally, rows are called records and columns are calledfields.In modern terminology, rows are called tuples and columns arecalled attributes.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 10 / 26
![Page 11: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/11.jpg)
Relational Databases
Consider a database of company employees.
fname lname ssn bdate sex salary deptAlice Smith 123456789 1968-05-22 F 35000.00 2Barbara Brown 135792468 1985-10-12 F 40000.00 3James Green 246813579 1974-02-15 F 100000.00 1Jennifer Wallace 321549876 1985-12-02 F 50000.00 2...
......
......
......
Employees table
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 11 / 26
![Page 12: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/12.jpg)
Relational Databases
In the example, the tuples are(Alice, Smith, 123456789, 1968-05-22, . . . )(Barbara, Brown, 135792468, 1985-10-12, . . . )etc.
The attributes arefnamelnamessnbdateetc.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 12 / 26
![Page 13: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/13.jpg)
Relational Databases
The table expresses a relation, namely“The employee fname lname has SS number ssn, was born onbdate, is sex, earns a salary of salary, and works in departmentdept.”
For example, the first row represents the statement“The employee Alice Smith has SS number 123456789, was bornon 1968-05-22, is female, earns a salary of $35000.00, and worksin department #2.”
Thus, each tuple of the database represents a true statement, aninstance of the relation.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 13 / 26
![Page 14: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/14.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 14 / 26
![Page 15: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/15.jpg)
Relational Databases
A query is a request that is sent to the DBMS.The DBMS parses the query, optimizes it, and sends it to thestorage engine, which executes the query on the database.There are four basic types of query.
SelectInsertDeleteUpdate
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 15 / 26
![Page 16: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/16.jpg)
Relational Databases
A selection query might request all employees in Dept #2.The result would be
fname lname ssn bdate sex salary deptAlice Smith 123456789 1968-05-22 F 35000.00 2Jennifer Wallace 321549876 1985-12-02 F 50000.00 2Ernest Roth 642189753 1986-06-12 M 60000.00 2John Kohler 789012345 1966-11-24 M 40000.00 2Raymond Jones 963418527 1974-08-30 M 80000.00 2
Employees table
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 16 / 26
![Page 17: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/17.jpg)
Relational Databases
An insertion query might add the tuple(Susan, Lane, 654872109, 1959-03-31, F, 70000.00, 2).
The result would be
fname lname ssn bdate sex salary deptAlice Smith 123456789 1968-05-22 F 35000.00 2Jennifer Wallace 321549876 1985-12-02 F 50000.00 2Ernest Roth 642189753 1986-06-12 M 60000.00 2John Kohler 789012345 1966-11-24 M 40000.00 2Raymond Jones 963418527 1974-08-30 M 80000.00 2Susan Lane 654872109 1959-03-31 F 70000.00 2
Employees table
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 17 / 26
![Page 18: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/18.jpg)
Relational Databases
A deletion query might delete the tuple with SSN 654872109.The result would be
fname lname ssn bdate sex salary deptAlice Smith 123456789 1968-05-22 F 35000.00 2Jennifer Wallace 321549876 1985-12-02 F 50000.00 2Ernest Roth 642189753 1986-06-12 M 60000.00 2John Kohler 789012345 1966-11-24 M 40000.00 2Raymond Jones 963418527 1974-08-30 M 80000.00 2
Employees table
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 18 / 26
![Page 19: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/19.jpg)
Relational Databases
An update query might change the department number from 2 to5.The result would be
fname lname ssn bdate sex salary deptAlice Smith 123456789 1968-05-22 F 35000.00 5Jennifer Wallace 321549876 1985-12-02 F 50000.00 5Ernest Roth 642189753 1986-06-12 M 60000.00 5John Kohler 789012345 1966-11-24 M 40000.00 5Raymond Jones 963418527 1974-08-30 M 80000.00 5
Employees table
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 19 / 26
![Page 20: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/20.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 20 / 26
![Page 21: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/21.jpg)
Schemas
Definition (Schema)A relation schema consists of the relation’s name, a list of its attributes,and their domains, i.e., the set of legitimate values of each attribute. Adatabase schema is a set of relation schemas.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 21 / 26
![Page 22: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/22.jpg)
Schemas
The schema of the Employees relation isEmployees(
fname string,lname string,ssn string,bdate date,sex string,salary float,dept integer
)
Furthermore, 1 ≤ dept ≤ 5 and sex is ‘M’ or ‘F’.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 22 / 26
![Page 23: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/23.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 23 / 26
![Page 24: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/24.jpg)
Primary Keys
Every table must have a primary key.The primary key is an attribute, or set of attributes, whose value,or values, is unique for each tuple.In a relation schema, we underline the attributes that comprise theprimary key.What would serve as the primary key in the Employees table?
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 24 / 26
![Page 25: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/25.jpg)
Outline
1 Types of Databases
2 Relational Databases
3 Queries
4 Schemas
5 Primary Keys
6 Assignment
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 25 / 26
![Page 26: Relational Databases - Lecture 2 Chapter 3people.hsc.edu/faculty-staff/robbk/Coms480/Lectures... · 2013. 1. 21. · Title: Relational Databases - Lecture 2 Chapter 3 Author: Robb](https://reader036.vdocuments.us/reader036/viewer/2022071610/61498cf7080bfa626014aeb0/html5/thumbnails/26.jpg)
Assignment
Read Chapter 3.
Robb T. Koether (Hampden-Sydney College) Relational Databases Fri, Jan 18, 2013 26 / 26