-- CSE 344 Lecture 02 -- Relational Data Model
-- Reading: 2.1, 2.2, 2.3
--
----------------------------------------------------------------------
----------
-- 1. Review: Fundamental Concepts
--
-- We want to store data in a database to get all the great features
we
-- discussed last lecture (an efficient way to ask all sorts of
questions
-- on the data, an easy way to update the data, recovery from crashes,
-- etc.)
-- We need to somehow tell the database management system (DBMS) about
-- our data. For example, we may want to create a database of movies.
-- We have information about movies, actors, and casts. We need to
-- somehow tell the DBMS about this data.
-- A DBMS allows a user to define the data stored in terms of a data
model.
-- A data model is a general, conceptual way of structuring data
-- 1.1 Data model: general, conceptual way of structuring data
--
-- Data models studied in this course:
-- relational data model -- data = relations
-- semistructured data model (XML) -- data = a tree
--
-- Other data models:
-- key-value pairs -- used by the NoSQL systems
-- graph data model -- used by RDF
-- object-oriented -- often used as a layer over relational model
--
--
-- Note: a data model describes both the data AND a query language
--
-- 1.2 Schema -- the structure of a particular database under a
certain data model
--
-- 1.3 Instance -- the actual data
--
--------------------------------------------------------------------
-- 2. The relational Data Model
-- Database instance:
-- -- "table" or "relation"
-- -- "column" or "attribute" or "field"
-- -- "row" or "tuple" or "record"
--
-- Cardinality of relation instance: nb tuples
-- Database schema:
-- -- "table name" or "relation name"
-- -- "column name" or "attribute name"
-- -- each attribute has a "type" or "domain"
--
-- Degree (or arity) of relation: nb attributes
--
-- Types or datatypes
--
-- -- Character strings: CHAR(20), VARCHAR(50), TEXT
-- -- Numbers: INT, BIGINT, SMALLINT, FLOAT
-- -- Others: MONEY, DATETIME,
--
-- -- Types are vendor specific
-- -- Types are static and strictly enforced;
-- exception: sqlite has dynamic types
-- http://www.sqlite.org/datatype3.html
--
-- Keys:
-- an attribute is called a "key" if it uniquely identifies a record
--
-- We can have a key with multiple attributes: what does this mean ?
-- It means that each unique combination of values for those
attributes
-- uniquely determines the record.
-- Candidate key: Minimal set of fields that uniquely identify
-- each tuple in a relation (candidate key = key)
-- Primary key: One candidate key can be selected as primary key
--
-- Foreign keys:
-- other tuples may use key values as "logical pointers"
--
--
--
-- Example on Whiteboard:
--
-- w/o types: Company(cname, country, no_employees, for_profit)
-- w/ types: Company(cname: varchar(30), country: char(20),
no_employees:int, for_profit:char(1))
-- specify keys by underlining
--
-- Note: if we had a semistructured data model, we would create
-- a tree. We would say: At the root, there is a company. Below it,
there are
-- departments. Each department has employees as its children, etc.
--
-- A database instance is the actual content of the tables in the
database.
--
----------------------------------------------------------------------
----------
-- 3. The Relational Data Model in SQL
--
-- We will use SQLite in class
--
--
-- 3.1 Creating tables
--
create table Company
(cname varchar(20) primary key,
country varchar(20),
no_employees int,
for_profit char(1));
insert into Company values ('GizmoWorks', 'USA', 20000,'y');
insert into Company values ('Canon', 'Japan', 50000,'y');
insert into Company values ('Hitachi', 'Japan', 30000,'y');
insert into Company values('Charity', 'Canada', 500,'n');
select * from Company;
-- Making sure SQL Lite shows us the data in a nicer format
-- These commands are specific to SQLite!
.header on
.mode column
.nullvalue NULL
--
--
-- Comment: upper/lower case; name conflicts
-- -- Company, company, COMPANY = all the same
-- -- Company(cname, country), Person(pname, country) = repeated
'country' OK
-- -- Company(cname, country), Person(pname, company) = the
attribute 'company' not ok
-- Null values: whenever we don't know the value, we can set it to
NULL
insert into Company values('MobileWorks', 'China', null, null);
select * from Company;
-- Deleting tuples from the database:
delete from Company where cname = 'Hitachi';
select * from Company;
delete from Company where for_profit = 'n';
-- what happens here??
select * from Company;
-- note: sql lite is REALLY light: it accepts many erroneous commands,
-- which other RDBMS would not accept. We will flag these as alerts.
-- Alert 1: sqlite allows a key to be null
insert into Company values(NULL, 'Somewhere', 0, 'n');
select * from Company;
-- this is dangerous, since we cannot uniquely identify the tuple
-- better delete it before we get into trouble
delete from Company where country = 'Somewhere';
select * from Company;
-- Discussion in class:
-- tables are NOT ordered. They represent sets or bags.
-- tables do NOT prescribe how they should be implemented: PHYSICAL
DATA INDEPENDENCE!
-- tables are FLAT (all attributes are base types)
-- Discussion: how would you implement a table?
-- row oriented
-- column oriented
-- vertically partitioned
-- horizontally partitioned
-- What are the pros/cons of the different physical implementations ?
-- Why is physical data independence important?
-- It is important so that we can optimize the data layout on disk for
-- performance without breaking the applications written on top of the
database!
-- 3.2 Altering a table in SQL
-- Add/Drop attribute(s)
-- let's drop the for_profit attribute:
-- Note: SQL Lite does not support dropping an attribute:
-- ALTER TABLE Company DROP for_profit; -- doesn't work
ALTER TABLE Company ADD ceo varchar(20);
select * from Company;
UPDATE Company SET ceo='Brown' WHERE cname = 'Canon';
SELECT * FROM Company;
-- A peek at the physical implementation:
-- What happens when you alter a table ? Consider row-wise and
column-wise.
-- 3.3 Multiple Tables, and Keys - Foreign Keys
-- Now alter Company to add the products that they manufacture.
-- Problem: can't add an attribute that is a LIST OF PRODUCTS. What
should we do??
--
--
-- Create a separate table Product, with a foreign key to the company:
create table Product
(pname varchar(20) primary key,
price float,
category varchar(20),
manufacturer varchar(20) references Company);
-- Alert 2: sqlite does NOT enforce foreign keys by default. To enable
-- foreign keys use the following command. The command will have no
-- effect if your version of SQLite was not compiled with foreign keys
-- enabled. Do not worry about it.
PRAGMA foreign_keys=ON;
insert into Product values('Gizmo', 19.99, 'gadget',
'GizmoWorks');
insert into Product values('PowerGizmo', 29.99, 'gadget',
'GizmoWorks');
insert into Product values('SingleTouch', 149.99, 'photography',
'Canon');
insert into Product values('MultiTouch', 199.99, 'photography',
'MobileWorks');
insert into Product values('SuperGizmo', 49.99, 'gadget',
'MobileWorks');
select * from Product;
-- If we try:
insert into Product values('MultiTouch2', 199.99, 'photography',
'H2');
-- We should get an error if foreign keys got enforced
-- Error: foreign key constraint failed
