Notation Summary R S table schemas such as

Notation Summary • R, S: table schemas such as • Branch (branch-name, branch-city, assets) • Account (branch-name, account-number, balance) • • r 1, , r 2 : table instances (i. e. , sets of tuples) r 1(R 1): a table instance of table schema R 1; e. g. , r 1(Branch) is a set of branches t 1, , t 2 : tuples A, B, C, K 1, , etc. , : a set of attributes; e. g. , A could represent branchname; in the slides, we use K 1, K 2, for primary keys and foreign key t 1[A]: the projection of t 1 on attribute A If r 1 is an instance of Branch and t 1 is a tupe in r 1 then t 1[branch-name] could be “Perryridge” K(r 1): the set of values under column K of table instance r 1 (note: K could consist of more than one attribute/column) Department of Computer Science and Engineering, HKUST Slide 1

Example of Mapping ISA into Tables Big table: Employee(Emp. No, Name, type, payperhour, nohours, salary) Emp. No Small tables: Name Employee ( Emp. No, Name) PT-Emp ( Emp. No, payperhour, nohours ) Employee FT-Emp ( Emp. No, salary ) ISA PT-Emp FT-Emp salary nohours payperhour Discussion: • Problem with using a big table • Is it necessary to add “type” in Employee? • Is it convenient to add “type” in Employee? • Given an Emp. No, how do you know if he/she a PT or FT employee? • Can an employee be both FT and PT? • How to restrict an employee to be either FT or PT? • What are the foreign keys? Do you see a problem with the definition in Slide 6? Department of Computer Science and Engineering, HKUST Slide 2

Discussion • Problem with using a big table • The table will have many columns with many null values in the table • Is it necessary to add “type” in Employee? • Not essential; to find out the type of an employee you can check if he/she exists in the PT-EMP or FT-EMP table • Does it help to add “type” in Employee? • Yes, checking both tables for the type of an employee is expensive • Given an Emp. No, how do you know if he/she a PT or FT employee? • See above; and try to write an SQL as an exercise • Can an employee be both FT and PT? • Yes if nothing is done • How to restrict an employee to be either FT or PT? • A constraint can be specified to ensure FT and PT tables are disjoint; there are more than one way to do it Department of Computer Science and Engineering, HKUST Slide 3

Discussion • What are the foreign keys? Do you see a problem with the definition in Slide 6? • Emp. No is the foreign key in PT-EMP and FT-EMP tables • Yes, because on Slide 6, the definition says a foreign key in a table cannot be the primary key of that table • Finally, why is Employee needed? Can we get rid of it by putting Name in the PT-Emp and FT-Emp tables? • Think about it yourselves Department of Computer Science and Engineering, HKUST Slide 4