NotationSummaryR, S:table schemassuch as. Branch (branch-name, branch-city, assets).Account(branch-name,account-number,balance)ri, r2 : table instances (i.e., sets of tuples)ri(R,): a table instance of table schema Ri; e.g., ri(Branch) is a set ofbranchesti,t,: tuplesA, B, C, Kr, α, etc., : a set of attributes; e.g, A could represent branch-name; in the slides, we use Ki, K2, for primary keys and α for foreign keyti[A]: the projection of t, on attribute AIf r;is an instance of Branch and t, is a tupe in r, thenti[branch-name]could be"Perryridge"Ilk(ri): the set of values under column K of table instance ri (note: Kcould' consist of more than one attribute/column)DepartmentofComputerScienceandEngineering,HKUSTSlide1
Department of Computer Science and Engineering, HKUST Slide 1 Notation Summary • R, S: table schemas such as • Branch (branch-name, branch-city, assets) • Account (branch-name, account-number, balance) • r1 , r2 : table instances (i.e., sets of tuples) • r1 (R1 ): a table instance of table schema R1 ; e.g., r1 (Branch) is a set of branches • t1 , t2 : tuples • A, B, C, K1 , , etc., : a set of attributes; e.g., A could represent branchname; in the slides, we use K1 , K2 , for primary keys and for foreign key • t1 [A]: the projection of t1 on attribute A • If r1 is an instance of Branch and t1 is a tupe in r1 then t1 [branch-name] could be “Perryridge” • K (r1 ): the set of values under column K of table instance r1 (note: K could consist of more than one attribute/column)
Example of Mapping ISA into TablesBig table:Employee(EmpNo, Name,type,payperhour, nohours, salary)EmpNoSmall tables:NameEmployee (EmpNo, Name)PT-Emp(EmpNo, payperhour, nohours)EmployeeFT-Emp (EmpNo, salary )Discussion:ISAProblemwithusingabigtableIsitnecessarytoadd"type"inEmployee?PT-EmpFT-EmpIs it convenient to add "type" in Employee?Given an EmpNo, howdoyouknow if he/she a PTorFT employee?salaryCan an employee be both FT and PT?nohoursHow to restrict an employee to be either FT or PT?What are the foreign keys? Do you see a problempayperhourwiththe definition inSlide 6?Slide 2
Department of Computer Science and Engineering, HKUST Slide 2 Example of Mapping ISA into Tables Employee EmpNo Name Big table: Employee(EmpNo, Name, type, payperhour, nohours, salary) PT-Emp payperhour FT-Emp salary ISA Small tables: Employee ( EmpNo, Name) PT-Emp ( EmpNo, payperhour, nohours ) FT-Emp ( EmpNo, salary ) nohours 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 EmpNo, 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?
DiscussionProblemwithusingabigtable. The table will have many columns with many null values in the tableIs it necessary to add "type" in Employee?Not essential; to find out the type of an employee you can check ifhe/sheexistsinthePT-EMPorFT-EMPtableDoes it help to add"type"in Employee?.Yes,checking bothtablesforthetypeof anemployeeisexpensiveGiven an EmpNo, how do you know if he/she a PT or FT employee?.Seeabove;andtrytowriteanSQLasanexerciseCan an employee be both FT and PT?.Yesif nothing is doneHow 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 itDepartmentofComputerScienceandEngineering,HKUSTSlide3
Department of Computer Science and Engineering, HKUST Slide 3 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 EmpNo, 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
DiscussionWhat are the foreign keys? Do you see a problem with the definitionin Slide 6?·EmpNoistheforeignkeyinPT-EMPandFT-EMPtables.Yes, because on Slide 6, the definition says a foreign key in a tablecannotbetheprimarykeyofthattableFinally, why is Employee needed? Can we get rid of it by puttingName in the PT-Emp and FT-Emp tables?.Think about it yourselvesDepartmentofComputerScienceandEngineering,HKUSTSlide4
Department of Computer Science and Engineering, HKUST Slide 4 Discussion • What are the foreign keys? Do you see a problem with the definition in Slide 6? • EmpNo 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