Designing Tables for a Database System 1 Where

Designing Tables for a Database System 1

Where we were, and where we’re going • The Entity-Relationship model: Used to model the world • The Relational model: An abstract definition of tables. Includes table names, column names, and key constraints • A relational databases system: A database infrastructure which implements the relational model. Allows for complex definitions of tables, including data types and constraints. • By converting an ER diagram to the relational model, we get abstract definitions that we can reason about • By converting an ER diagram to a set of tables, we define the logical schema used to actually store the data 2

Tables • The basic element in a relational database 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. 3

postgres. QL • In this course, we will use the postgre. SQL DBMS • In order to issue commands to the DBMS we use the psql terminal • In order to connect to it, print the following command in your shell: psql –hpgserver public • This will give you access to “your” database 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 • Postgresql Documentation can be found at: http: //www. postgresql. org/docs/8. 4/interactive/index. html 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

Creating Tables in the SQL DDL = Data Definition Language Id Name Dept. Age 0345 Eyal Sales 28 0965 Yair Transport 34 7665 Ori Warehouse 31 8

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

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

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: Databases are case insensitive in Table and Column names! 11

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 See also: http: //www. postgresql. org/docs/7. 4/interactive/datatype. html 12

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. • Rule of thumb: add constraints, unless you know that they are affecting the runtime. • Different types of constraints: * Not Null * Default Values * Unique * Primary Key * Foreign Key * Check Constraints 13

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

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

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

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

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) ); What does this mean? Can this be written differently? 18

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) ); CREATE TABLE Employee( ID INTEGER NOT NULL, Fname VARCHAR(20) UNIQUE, Lname VARCHAR(20) UNIQUE, Gender CHAR(1) DEFAULT(‘F’), Salary INTEGER NOT NULL, Dept INTEGER, ); Which is a stronger constraint? 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. The primary key is used for efficient access to the table 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(ID) ); 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

Foreign Key Constraint CREATE TABLE Employee( ID INTEGER PRIMARY KEY, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) DEFAULT(‘F’), Salary INTEGER NOT NULL, Dept. Number INTEGER, FOREIGN KEY (Dept. Number) REFERENCES Department(Dept. Number) ); Dept. Number must be unique (or primary key) in Department 23

Foreign Key Constraint (Syntax 2) CREATE TABLE Employee( ID INTEGER PRIMARY KEY, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) DEFAULT(‘F’), Salary INTEGER NOT NULL, Dept. Number INTEGER, FOREIGN KEY (Dept. Number) REFERENCES Department ); NOTE: You can use this syntax only if the name of the fields in both tables are identical 24

Foreign Key Constraint (Syntax 3) CREATE TABLE Employee( ID INTEGER PRIMARY KEY, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1) DEFAULT(‘F’), Salary INTEGER NOT NULL, Dept. Number INTEGER REFERENCES Department ); 25

Foreign Key Employee 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 26

Understanding Foreign Keys • The constraint on the last table should be read as: “The field Dept in Employee is a foreign key that references the field Dept in Department” • Meaning: Every non-null value in the field Dept of Employee must appear in the field Dept of Department. 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 a database system will not allow Department 312 to be deleted if there are Employees working in (referencing to) this department. • This a general rule for constraints! A database system rejects any change to the database that causes a constraint to be violated! • 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

Foreign Keys Should we also revise the department table? CREATE TABLE Department( Dept. Number INTEGER PRIMARY KEY, Name VARCHAR(20), Manager. Id INTEGER, ); Do you see a problem in defining these tables and in inserting data now? 29

Cyclic Foreign Key Constraints ID FName Dept. Num LName Gender Name Salary Dept. Num Manager. ID 30

One Solution to Cyclic Constraints Add one of the constraints later on (after insertion): CREATE TABLE Department( Dept INTEGER PRIMARY KEY, Name VARCHAR(20), Manager. Id INTEGER); Insert data here… ALTER TABLE Department ADD(FOREIGN KEY (Manager. Id) REFERENCES Employee(ID)); Transactions can also be used to solve this problem… Discussed (much) later in the course 31

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 • Cannot refer to content of other rows! 32

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 CHECK (Gender = ‘M’ or Salary > 10000) ); 33

Deleting a Table • To delete the table Employee : DROP TABLE Employee; • Mind the order of dropping when there are foreign key constraints. Why? • Can use: DROP TABLE Employee cascade constraints; 34

Modifying Tables • Done with ALTER TABLE statement • Usually: – ADD followed by an attribute name and type or – DROP followed by an attribute name • ALTER TABLE Actor ADD phone CHAR(16); • ALTER TABLE Actor DROP bdate; 35

Inserting, deleting, and updating data in a table 36

The Employee Table Column Type Modifiers ------------ id integer not null fname character varying(20) gender character(1) deptnumber integer 37

Inserting a Row To insert a row into the Employee table: • insert into employee (id, fname, gender, deptnumber) values(122, 'Goldman', 'M', 12); • insert into employee (id, deptnumber) values(123, 13); 38

Some More Details… • The fields don’t have to be specified if values are specified for all columns and in the order defined by the table • Example: insert into employee values(33, 'David', 'F', 11); 39

Deleting Rows • General format: DELETE FROM Table WHERE Condition; Deletes all rows satisfying Condition from Table • For example, to remove the employee with id 121 from the Employee table: DELETE FROM Employee WHERE id = 121; 40

Deleting Rows (cont. ) • To remove all employees having a salary greater than 100, 000: DELETE FROM Employee WHERE Salary > 100000; • To remove all employees: DELETE FROM Employee; 41

Updating Rows • We can update rows in a table • General format: UPDATE Table SET Field 1=value 1, …, Field. N=value. N WHERE Condition • Now we can reduce salaries instead of firing employees: UPDATE Employee SET Salary = 100000 WHERE Salary > 100000; 42