Designing Tables for an Oracle Database System Database

Designing Tables for an Oracle Database System Database Course, Fall 2005 1

From theory to practice • The Entity-Relationship model: a convenient way of representing the world. • The Relational model: a model of organizing data using tables. • Oracle: 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 an oracle database. 2

Technicalities • Add the following to your. cshrc file: if (-r ~db/oraenv) source ~db/oraenv • You will be able to use Oracle after you log out and log in again. • You can run Oracle from computers pita, inferno, etc. 3

Sqlplus • Oracle is a commercial, hardware-based, heavy(!), program. • To connect to it, we use the Sqlplus utility. • The Sqlplus program accepts commands in standard SQL and sends them to the Oracle database. • The interface creates a “local” database for each user. 4

Connecting to the Oracle Database • At the command line prompt, write: Sqlplus (not sqlplus !) • This will give you access to the “personal” database of ‘user’ (In other words, to access “your” database, you have to run it from your own CS account). • You will be presented with a Sqlplus console, in which you type your commands. • To disconnect (and return to your regular shell), type: quit • Remember: (almost) Every command must end with a semicolon (; ) 5

6

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 7

Running Commands from an. sql File • Instead of typing commands into the SQLPLUS terminal, you can load commands from a file (no special format is required). • The file name should end with “. sql” • Invoke by: 1. Use the command @file from SQLPLUS to load the file. sql Or: 2. Invoke the SQLPLUS command with the extra parameter @file to load the file at connection: Sqlplus @file 8

Spooling the Output • Output can be placed in a file: spool <file_name> • Spooling can be turned off with: spool off 9

Tables • The basic element in Oracle 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. 10

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

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

Example CREATE TABLE Employee( ID NUMBER NOT NULL, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1), Salary NUMBER(5) NOT NULL, Dept NUMBER ); 13

An Example (cont. ) Oracle is case insensitive in Column names! If you type describe Employee you get: Name Null? Type ------------ID NOT NULL NUMBER FNAME VARCHAR 2(20) LNAME VARCHAR 2(20) GENDER CHAR(1) SALARY DEPT NOT NULL NUMBER(5) NUMBER Notice that “describe” describes the structure and not the contents of the table. 14

Examples of Data Types CHAR(n) String of fixed length n (n <= 2000) VARCHAR 2(n) Variable length string of size <= n (n <= 4000) CLOB Character large object (<= 4 GB) BLOB Binary large object (<= 4 GB) DATE Valid dates (up to seconds) TIMESTAMP Valid timestamps (up to milliseconds) NUMBER Up to 40 digits NUMBER(n) Integer of size n NUMBER(n, m) Number of size n with m digits after decimal place Others XML, Abstract types, etc. 15

• What happens if we insert: – 'abc' into varchar(5)? “abc “ “abc“ – 'abc' into char(2)? Wrong! – 'abc' into varchar(2)? Wrong! – 105. 32 into number(3, 2)? Wrong! – 105. 32 into number(5, 2)? 105. 32 105. 3 105 – 'abc' into char(5)? – 105. 32 into number(4, 1)? – 105. 32 into number(3)? – 105. 32 into number? – 105. 1 into number(7, 5) ? 105. 32 Wrong! • Why not always use number and not number(n, m)? • Why not always use varchar 2(4000)? • Where is the boolean datatype? 16

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 17

Not Null Constraint CREATE TABLE SSN Fname Lname Gender Salary Dept ); Employee( NUMBER NOT NULL, VARCHAR 2(20), CHAR(1), NUMBER(5) NOT NULL, NUMBER 18

Default Values CREATE TABLE SSN Fname Lname Gender Salary Dept ); Employee( NUMBER NOT NULL, VARCHAR 2(20), CHAR(1) DEFAULT(‘F’), NUMBER(5) NOT NULL, NUMBER 19

Unique Constraint (Syntax 1) CREATE TABLE SSN Fname Lname Gender Salary Dept ); Employee( NUMBER UNIQUE NOT NULL, VARCHAR 2(20), CHAR(1) DEFAULT(‘F’), NUMBER(5) NOT NULL, NUMBER 20

Unique Constraint (Syntax 2) CREATE TABLE Employee( SSN NUMBER NOT NULL, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1) DEFAULT(‘F’), Salary NUMBER(5) NOT NULL, Dept NUMBER, UNIQUE(SSN) ); 21

Unique Constraint (Another Example) CREATE TABLE Employee( SSN NUMBER NOT NULL, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1) DEFAULT(‘F’), Salary NUMBER(5) NOT NULL, Dept NUMBER, UNIQUE(Fname, Lname) ); Can this be written differently? 22

Primary Key Constraint CREATE TABLE Employee( SSN NUMBER PRIMARY KEY, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1) DEFAULT(‘F’), Salary NUMBER(5) NOT NULL, Dept NUMBER, UNIQUE(Fname, Lname) ); Primary Key implies: * NOT NULL * UNIQUE. There can only be one primary key. 23

Primary Key Constraint (Syntax 2) CREATE TABLE Employee( SSN NUMBER, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1) DEFAULT(‘F’), Salary NUMBER(5) NOT NULL, Dept. Number NUMBER, UNIQUE(Fname, Lname), PRIMARY KEY(ssn) ); 24

Another Table CREATE TABLE Department( Dept. Number NUMBER PRIMARY KEY, Name VARCHAR 2(20), Manager. Id NUMBER ); Shouldn’t all department numbers in Employee appear in Department? 25

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

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

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 28

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

Deleting a Referenced Value • If nothing additional is specified, then Oracle will not allow Department 312 to be deleted if there are Employees working in (referencing to) this department. • 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 30

Foreign Keys We should revise the Department table: CREATE TABLE Department( Dept. Number NUMBER PRIMARY KEY, Name VARCHAR 2(20), Manager. Id NUMBER, FOREIGN KEY (Manager. Id) REFERENCES Employee(SSN) ); Do you see a problem in defining these tables and in inserting data now? 31

Foreign Key ID FName LName Gender Sallary Dept. Num Foreign Key Dept. Num Name Man. ID 32

Solution to Cyclic Constraints Add one of the constraints later on (after insertion): CREATE TABLE Department( Dept NUMBER PRIMARY KEY, Name VARCHAR 2(20), Manager. Id NUMBER); Insert data here… ALTER TABLE Department ADD(FOREIGN KEY (Manager. Id) REFERENCES Employee(SSN)); 33

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 34

Check Constraints CREATE TABLE Employee( SSN NUMBER PRIMARY KEY, Fname VARCHAR 2(20), Lname VARCHAR 2(20), Gender CHAR(1) DEFAULT(‘F’) CHECK(Gender = ‘F’ or Gender = ‘M’) , Salary NUMBER(5) NOT NULL, CHECK (Gender = ‘M’ or Salary > 10000) ); 35

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

Converting ER-Diagrams to Table Definitions 37

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

Relations vs. Tables • We show to translate ER-Diagrams to table definitions • Sometimes, people translate ER-Diagrams to relation definition, which is more abstract than table definitions. – e. g. , Employee(SSN, Fname, Lname, Gender, Salary, Dept); – table definitions contain, in addition, constraints and datatypes 39

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 40

Simple entity translation id birthday Actor name address Relation: Actor (id, name, birthday, address) create table Actor(id varchar 2(20) primary key, name varchar 2(40), birthday date, address varchar 2(100)); 41

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 42

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

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 2(20), Mid varchar 2(20), Foreign key Eid references Employee(id), Foreign key Mid references Employee(id), Primary key(Eid, Mid)); If we want to make sure an employee is not his own manager we can express it with Check 44

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

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

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 47

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

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 49

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

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

Translating Weak Entity Sets phone number name create table award( name varchar 2(40), Organization year integer, money number(6, 2), o_name varchar 2(40), Gives primary key(name, year, o_name), Award year foreign key (o_name) references Organization(name) money name on delete cascade ) 52

Translating ISA: Option 1 address id Movie Person name ISA picture Actor Director create table Movie. Person(. . . ) create table Actor(id varchar 2(20), picture bfile, primary key(id), foreign key (id) references Movie. Person)) create table Director(. . . ) 53

Translating ISA: Option 2 address id Movie Person name ISA picture Actor Director No table for Movie. Person! create table Actor(id varchar 2(20), address varchar 2(100), name varchar 2(20), picture blob, primary key(id)); create table Director(. . . ) 54

Which Option To Choose? • What would you choose if: – Actor and Director DO NOT COVER Movie. Person? – Actor OVERLAPS Director? 55

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

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 57

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 no overlap 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 58