Scribd
Upload
9 views

Lecture02 Data Models

Uploaded by

Ifeoluwa Lapite
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views

Lecture02 Data Models

Uploaded by

Ifeoluwa Lapite
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

-- 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

You might also like