Designing Tables for a postgre SQL Database System

Designing Tables for a postgre. SQL Database System 1

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. • postgre. SQL: a database infrastructure which implements the relational model. • Converting ER->Relational model is important! • SQL(Structured Query Language): A language used to get information from a database. 2

psql • In order to connect to the postgre. SQL 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 3

4

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 5

Reminder The database is kept on the disk, so anything you create will be there next time you log on. sailors Reserves Main Memory DISK CPU 6

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 file. Name 7

Tables • The basic element in postgre. SQL 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. 8

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

Creating a Table The basic format of the CREATE TABLE command is: CREATE TABLE Table. Name( Column 1 Data. Type 1 Col. Constraint, … Column. N Data. Type. N Col. Constraint, Table. Constraint 1, … Table. Constraint. M ); 10

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

An Example (cont. ) If you type d Employee you get: Column Type ------------ Modifiers ------ id integer fname character varying(20) lname character varying(20) gender character(1) salary integer dept integer not null Note: postgre. SQL is case insensitive in Column names! 12

Examples of Data Types Name Aliases Description bigint 8 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 in the plane date calendar date (year, month, day) double precision float 8 double precision floating-point number integer int, int 4 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 13

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 14

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 ); 15

Default Values CREATE TABLE Employee( ID INTEGER NOT NULL, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) default(‘F’), Salary INTEGER NOT NULL, Dept INTEGER ); 16

Unique Constraint (Syntax 1) CREATE TABLE Employee( ID INTEGER UNIQUE NOT NULL, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) default(‘F’), Salary INTEGER NOT NULL, Dept INTEGER ); 17

Unique Constraint (Syntax 2) CREATE TABLE Employee( ID INTEGER NOT NULL, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) default(‘F’), Salary INTEGER NOT NULL, Dept INTEGER, UNIQUE(ID) ); 18

Unique Constraint (Another Example) CREATE TABLE Employee( ID INTEGER NOT NULL, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) default(‘F’), Salary INTEGER NOT NULL, Dept INTEGER, UNIQUE(FNAME, LNAME) ); Can this be written differently? 19

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(FNAME, LNAME) ); Primary Key implies: * NOT NULL * UNIQUE. There can only be one primary key. 20

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) ); 21

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( Dept. Number INTEGER PRIMARY KEY, Name VARCHAR(20), Manager. Id INTEGER ); Shouldn’t all department numbers in Employee appear in Department? 22

Employee Foreign Key ID FName LName Gender Sallary Dept 02334 Larry Bird M 230000 12 04556 Magic Johnson M 270000 45 Foreign Key Department Dept Name Man. ID 12 Sales 988 45 Repair 876 23

Foreign Key Constraint CREATE TABLE Employee ( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, Dept. Number INTEGER REFERENCES Department ); Note 1: Dept. Number must be unique (or primary key) in Department Note 2: You can use this syntax only if the name of the fields in both tables are identical Note 3: The referencing attribute (Dept. Number) can be null 24

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(Dept. Number) ); 25

Foreign Key Constraint CREATE TABLE Employee( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, Dept. Number INTEGER, FOREIGN KEY (Dept. Number) REFERENCES Department ); Using this syntax allows a pair or more of attributes to be a foreign key 26

Deleting referenced values What happens to Employees in department 312 when Department 312 is removed from the Department table? 27

Deleting a Referenced Value • If nothing additional is specified, then postgre. SQL 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 28

Check Conditions • A check condition is a Boolean expression: – “And”s and “Or”s of conditions of the type X > 5… • On a column: it can refer only to the column • On a table: it can refer only to multiple columns in the table 29

Check Constraints CREATE TABLE Employee( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) check(gender=‘F’ or gender=‘M’), Salary INTEGER NOT NULL, Dept. Number INTEGER ); 30

Deleting a Table • To delete the table Employee : DROP TABLE Employee; • Mind the order of dropping when there are foreign key constraints. 31

Converting ER-Diagrams to Table Definitions 32

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

Relation definition vs. Table definition • We show 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 34

Simple entity translation id name birthday Actor 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 35

Student id name address student id 36

Simple entity translation id birthday 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)); 37

Translating Entities with Relationships birthday id Actor (without constraints) Acted In Film title year name address salary 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 38

Translating relationships (without constraints) birthday id Actor Acted In Film title year name address salary type How would you define the table for Acted. In? create table Acted. In(id varchar(20) references actor, title varchar(20) references film, salary integer, primary key(id, title)); 39

Translating Recursive Relationships (without constraints) manager id Employee name worker Manages 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? 42

Translating relationships (key constraints): Option 1 id Director Directed name salary Film title year Option 1: • Same as without key constraints (3 tables), except that the relationship primary key is…? title. 43

Translating relationships (key constraints): Option 1 id Director name Directed Film salary title year create table Directed( id varchar(20) references Director, title varchar(40) references Film, salary integer, primary key (title)); 44

Translating relationships (key constraints): Option 2 id Director name Directed Film salary title year 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 45

Translating relationships (key constraints): Option 2 id Director name Directed Film title year salary create table Film( title varchar(40) primary key, year integer, salary integer, Why couldn’t we do this when there were no constraints? id varchar(20) references Director); 46

Translating relationships (participation constraints) id Director name Directed salary Film title year 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 47

Translating relationships (participation constraints) id Director name Directed Film title year salary create table Film( title varchar(40) primary key, year integer, id varchar(20), Where should we add NOT NULL? salary integer, foreign key (id) references Director); 48

Translating relationships (participation constraints) id name Actor Acted In salary Film title year • How would we translate this? 49

Translating Weak Entity Sets phone number name A regular table for Organization, and. . create table award( Organization name varchar(40), year integer, money integer, Gives o_name varchar(40) references Organization(name), Award year money name primary key(name, year, o_name), ); 50

Oname Translating Aggregation phone number Organization picture salary Gives Director ID Acted In Won Award year Broadcasted name year Film title type Won(title, year, name, Oname, Broadcasted); 51

Summary Simple Entity Tables Primary key Remarks Single table The entity key a column for each attr. Simple 3 (2 entities For the relation: Foreign keys Relationship +relationship) Both entity keys from rel. Table Key constraint 3 as before or Key of Foreign keys constrained ent. from rel. Table Foreign key from 2 (one for each constr. Entity entity) Key and 2 Regular Constrained Participation entity has a non- constr. null f. key 52

Weak Entity Tables Primary key Remarks 2: parent and Weak: its own Foreign keys weak entities and parent keys from weak ent. ISA: covers and 2: only child disjoint entities ISA: otherwise 3: parent and Parent key child entities Aggregation Foreign keys from child ent. 3: 2 aggregates For relationship: Foreign keys and relationship keys of both from relationship aggregates table 53