ch4_part2

ch4_part2 - RecapofCreateTable& Insert n n n n n

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Insert n Drop tables first (in reverse order of create) n Create tables with only primary key constraints n constraints (the foreign key column type must be  exactly the same as the referred primary key) n Insert data values, watch out for string, date, and  timestamp data types, foreign key values (must be  the same as referred primary key values) n Commit
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 SQL n Relational Model n Keys, foreign keys n SQL n Data Definition Language (how to create a table)  n Select…from…where n Joins n Aggregate functions n Insert, update, delete n String and time operation  
Background image of page 2
3 Join n Necessary whenever the  from  clause lists more than one  table n Typically these tables are linked by foreign keys n Must add equality condition between the referenced primary  key and foreign key n There may be join conditions between other columns  (depending on the question to answer)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 Example Return the name of the department  where jeff is working select dname from dept, emp where ename = 'jeff' and dept.did =  emp.did;
Background image of page 4
5 Exercise Return the names of the employees working at IT department
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Common Errors Without join condition: Return the name of the department where jeff is  working Select dname From dept, emp Where ename='jeff'; Results?
Background image of page 6
7 Common Errors Return the name of the department where jeff is working Select dname From dept, emp Where ename='jeff' where did = did Correct? From dept, emp Where ename='jeff' and emp.did = dept.did;
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8 Missing Join Conditions If there are 2 tables in the from clause, how many join conditions are needed? How about 3 tables? How about 4 tables? How about 10 tables? How to check whether there is enough join conditions?
Background image of page 8
9 Solution Represent each table in the from clause as a node Draw an edge between two nodes when there is a join condition between attributes in these two tables Stop if there are no isolated nodes According to graph theory, the minimal number of edges to leave no isolated nodes is number of nodes minus one You may add more edges (conditions), but they are not necessary unless there are additional join conditions
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
10 Missing Tables EID PID ENAME PNAME EID PID EMP PROJ EMP_PROJ
Background image of page 10
11 Missing Tables n Return the name of projects 'jeff' is  working on Select pname From proj, emp Where ename = 'jeff'; Correct?
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
12 Missing Tables n Solution: the same way as checking  missing join conditions n Identify the tables that contain the columns  in the select clause, add them to from  clause n Add join conditions to link these tables n If some tables are still not linked, some  table is missing
Background image of page 12
Self Join Return the name of employees working at  the same department as jeff select E2.ename
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 59

ch4_part2 - RecapofCreateTable& Insert n n n n n

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online