Designing%20Tables%20for%20a%20postgreSQL%20Database%20System

1
Designing Tables for a postgreSQL Database System
2
From theory to practice

• The Entity-Relationship model a convenient way
  of representing the world.
• The Relational model a model for organizing data
  using tables.
• postgreSQL a database infrastructure which
  implements the relational model.
• Converting ER-gtRelational model is important!
• SQL(Structured Query Language) A language used
  to get information from a database.

3
psql

• In order to connect to the postgreSQL database,
  we use the psql terminal
• In order to connect to it, print the following
  command in your shell
• psql hdbserver public
• This will give you access to your database

4
(No Transcript)
5
A few useful commands

• \q exit psql
• \h command help about command
• \d name describe table/index/ called name
• \dt list tables
• \di list indexes
• \dv list views
• \df list functions

6
Reminder
The database is kept on the disk, so anything you
create will be there next time you log on.






sailors
Reserves
DISK
Main Memory
CPU
7
Running Commands from an .sql File

• Instead of typing commands into the psql
  terminal, you can load commands from a file (no
  special format is required).
• The file name should end with .sql
• Invoke by
• \i fileName

8
Tables

• The basic element in postgreSQL is a table.
• A table has columns (attributes), and rows
  (tuples).
• Every column has a Name and Type (of the data it
  stores), and some columns have constraints.
• Some tables may have additional constraints.

9
Creating Tables in SQL
Id Name Dept. Age
0345 Eyal Sales 28
0965 Yair Transport 34
7665 Ori Warehouse 31
10
Creating a Table

• The basic format of the CREATE TABLE
• command is
• CREATE TABLE TableName(
• Column1 DataType1 ColConstraint,
• ColumnN DataTypeN ColConstraint,
• TableConstraint1,
• TableConstraintM
• )

11
Example
CREATE TABLE Employee( ID INTEGER NOT
NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER )
12
An Example (cont.)

• If you type \d Employee you get
• Column Type Modifiers
• ----------- ------------ ------------
• id integer not null
• fname character varying(20)
• lname character varying(20)
• gender character(1)
• salary integer not null
• dept integer

Note postgreSQL is case insensitive in Column
names!
13
Examples of Data Types
Name Aliases Description
bigint int8 signed eight-byte integer
boolean bool logical Boolean (true/false)
box   rectangular box in the plane
bytea   binary data ("byte array")
character varying (n) varchar (n) variable-length character string
character (n) char (n) fixed-length character string
circle   circle in the plane
date   calendar date (year, month, day)
double precision float8 double precision floating-point number
integer int, int4 signed four-byte integer
line   infinite line in the plane
point   geometric point in the plane
text   variable-length character string
timestamp (p)   date and time
14
Constraints in Create Table

• Adding constraints to a table enables the
  database system to enforce data integrity.
• However, adding constraints also makes inserting
  data slower.
• Different types of constraints
• Not Null Default Values
• Unique Primary Key
• Foreign Key Check Condition

15
Not Null Constraint
CREATE TABLE Employee( ID INTEGER NOT
NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER )
16
Default Values
CREATE TABLE Employee( ID INTEGER NOT
NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1) default(F), Sala
ry INTEGER NOT NULL, Dept INTEGER )
17
Unique Constraint (Syntax 1)
CREATE TABLE Employee( ID INTEGER
UNIQUE NOT NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1) default(F), Sala
ry INTEGER NOT NULL, Dept INTEGER )
18
Unique Constraint (Syntax 2)
CREATE TABLE Employee( ID INTEGER NOT
NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1) default(F), Sala
ry INTEGER NOT NULL, Dept
INTEGER, UNIQUE(ID) )
19
Unique Constraint (Another Example)
CREATE TABLE Employee( ID INTEGER NOT
NULL, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1) default(F), Sala
ry INTEGER NOT NULL, Dept
INTEGER, UNIQUE(FNAME,LNAME) )
Can this be written differently?
20
Primary Key Constraint
CREATE TABLE Employee( ID INTEGER
PRIMARY KEY, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER, UNIQUE(FNA
ME,LNAME) )
Primary Key implies NOT NULL UNIQUE. There
can only be one primary key.
21
Primary Key Constraint (Syntax 2)
CREATE TABLE Employee( ID
INTEGER, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER, PRIMARY
KEY(FNAME,LNAME) )
22
CREATE TABLE Employee( ID INTEGER
primary key, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER )
CREATE TABLE Department( DeptNumber INTEGER
PRIMARY KEY, Name VARCHAR(20), Manager
Id INTEGER )
23
Foreign Key
Employee
ID FName LName Gender Sallary Dept
02334 Larry Bird M 230000 12
04556 Magic Johnson M 270000 45

Foreign Key
Dept Name ManID
12 Sales 988
45 Repair 876
Department
24
Foreign Key Constraint
CREATE TABLE Employee ( ID INTEGER
primary key, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, DeptNumber INTEGER
REFERENCES Department )
Note 1 DeptNumber must be unique (or primary
key) in Department Note 2You can use this syntax
only if the name of the fields in both tables are
identical Note 3 The referencing attribute
(DeptNumber) can be null
25
Foreign Key Constraint (different names of
attributes)
CREATE TABLE Employee( ID INTEGER
primary key, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, Dept INTEGER REFERENCES
Department(DeptNumber) )
26
Foreign Key Constraint
CREATE TABLE Employee( ID INTEGER
primary key, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1), Salary
INTEGER NOT NULL, DeptNumber
INTEGER, FOREIGN KEY (DeptNumber)
REFERENCES Department )
Using this syntax allows a pair or more of
attributes to be a foreign key
27
Deleting referenced values
28
Deleting a Referenced Value

• If nothing additional is specified, then
  postgreSQL will not allow Department 312 to be
  deleted if there are Employees working in
  (referencing to) this department.
• You can also specify this explicitly
• FOREIGN KEY (Dept) REFERENCES
• Department ON DELETE RESTRICT
• Alternatively, if the constraint is written as
• FOREIGN KEY (Dept) REFERENCES
• Department ON DELETE CASCADE
• then Employees working in 312 will be deleted
  automatically from the Employee table, when 312
  is deleted from Departments

29
Check Conditions

• A check condition is a Boolean expression
• Ands and Ors of conditions of the type X gt 5
• On a column it can refer only to the column
• On a table it can refer only to multiple columns
  in the table

30
Check Constraints
CREATE TABLE Employee( ID INTEGER
primary key, Fname VARCHAR(20), Lname
VARCHAR(20), Gender CHAR(1) check(genderF
or genderM), Salary INTEGER NOT
NULL, DeptNumber INTEGER )
31
Deleting a Table

• To delete the table Employee
• DROP TABLE Employee
• Mind the order of dropping when there are foreign
  key constraints.

32
Converting ER-Diagrams to Table Definitions
33
General Principals

• When converting ER diagrams to Relations, we
  should try to
• Reduce duplicate information
• Constrain as tightly as possible
• Note
• Some scenarios can be represented in different
  ways.
• Sometimes we will not be able to fully represent
  constraints, or will be forced to represent
  information more than once.

34
Relation definition vs. Table definition

• We show how to translate ER-Diagrams to table
  definitions
• Sometimes, people translate ER-Diagrams to
  relation definitions, which are more abstract
  than table definitions.
• e.g., Employee(ID, Fname, Lname, Gender, Salary,
  Dept)
• table definitions contain, in addition,
  constraints and datatypes

35
Simple entity translation
birthday
id
Actor
name
address

• General Rule
• Create a table with the name of the Entity.
• There is a column for each attribute
• The key in the diagram is the primary key of the
  table

36
Student
id name address





name
address
student
id
37
Simple entity translation
birthday
id
Actor
name
address
Relation Actor (id, name, birthday, address)

• create table Actor(id varchar(20) primary key,
• name varchar(40),
• birthday date,
• address varchar(100))

38
Translating Entities with Relationships (without
constraints)
title
birthday
id
Film
Actor
year
Acted In
name
salary
address
type

• Create tables for the entities as before
• Create a table with the name of the relationship
• Relationship table attributes its own attributes
  (salary) all keys of the relating entities
  (title, id).
• Q What is the primary key of the table?
• A A composite of both entity keys
• Q What foreign keys are needed?
• A From the relationship table to the entities

39
Translating relationships (without constraints)
title
birthday
id
Film
Actor
year
Acted In
name
salary
type
address

• How would you define the table for ActedIn?

create table ActedIn(id varchar(20) references
actor, title
varchar(20) references film,
salary integer,
primary key(id,title))
40

• How would we represent this diagram in tables?

?????
?? ????
??
??' ????
????
??????
????
??' ????
?????
??' ?????
41
Answer
?????
??????
????
????
??' ???? ?? ????





??' ?????





??' ???? ??





??' ????? ??' ???? ??' ???? ?????





42
Translating Recursive Relationships (without
constraints)
manager
id
Employee
Manages
worker
name
address
Relation Manages (Wid, Mid) What would be the
table definition? create table Manages( Eid
varchar(20) references Employee(id), Mid
varchar(20) references Employee(id), Primary
key(Eid, Mid))
How would you prevent someone from being his own
manager?
43
Translating relationships(key constraints)
Option 1
id
Director
Film
Directed
title
name
year
salary

• Option 1
• Same as without key constraints (3 tables),
  except that the relationship primary key is?
• title.

44
Translating relationships(key constraints)
Option 1
id
Director
Film
Directed
title
name
year
salary

• create table Directed(
• id varchar(20) references Director,
• title varchar(40) references Film,
• salary integer,
• primary key (title))

45
Translating relationships(key constraints)
Option 2
id
Director
Film
Directed
title
name
year
salary

• Option 2
• Do not create a table for the relationship
• Add information columns that would have been in
  the relationship's table to the table of the
  entity with the key constraint

46
Translating relationships(key constraints)
Option 2
id
Director
Film
Directed
title
name
year
salary

• create table Film(
• title varchar(40) primary key,
• year integer,
• salary integer,
• id varchar(20) references Director)

Why couldnt we do this when there were no
constraints?
47
Translating relationships(participation
constraints)
id
Director
Film
Directed
title
name
year
salary

• General Rule
• If both participation and key constraint exist,
  use Option 2 from before (only 2 tables), AND
• Add the not null constraint on the referncing
  attribute to ensure that there will always be
  values for the key of the other entity

48
Translating relationships(participation
constraints)
id
Director
Film
Directed
title
name
year
salary

• create table Film(
• title varchar(40) primary key,
• year integer,
• id varchar(20),
• salary integer,
• foreign key (id) references Director)

Where should we add NOT NULL?
49
Translating relationships(participation
constraints)
id
Actor
Film
Acted In
title
name
year
salary

• How would we translate this?

50
Translating Weak Entity Sets
A regular table for Organization, and..
phone number
name

• create table award(
• name varchar(40),
• year integer,
• money integer,
• o_name varchar(40) references Organization(name)
  ,
• primary key(name, year, o_name),
• )

Organization
Gives
money
Award
name
year
51
Translating Aggregation
phone number
Oname
Organization
picture
Director
ID
Gives
salary
Acted In
Won
year
Film
Award
Broad- casted
title
name
type
year

• Won(title, year, name, Oname, Broadcasted)

52
Summary
Tables Primary key Remarks
Simple Entity Single table The entity key a column for each attr.
Simple Relationship 3 (2 entities relationship) For the relation Both entity keys Foreign keys from rel. Table
Key constraint 3 as before or 2 (one for each entity) Key of constrained ent. Foreign keys from rel. Table Foreign key from constr. Entity
Key and Participation constr. 2 Regular Constrained entity has a non-null f. key
53
Tables Primary key Remarks
Weak Entity 2 parent and weak entities Weak its own and parent keys Foreign keys from weak ent.
ISA covers and disjoint 2 only child entities Parent key
ISA otherwise 3 parent and child entities Parent key Foreign keys from child ent.
Aggregation 3 2 aggregates and relationship For relationship keys of both aggregates Foreign keys from relationship table