pre-mid - 20110417 1. 2. 3. 4. 5. (1) (2) SQL SQL SQL ERD...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 20110417 1. 2. 3. 4. 5. (1) (2) SQL SQL SQL ERD Normalization Theory 1NF (First Normal Form)2NF (Second Normal Form)3NF (Third Normal Form)BCNF (BoyceCodd Normal Form)Atomic pp. 510~513 Relational Algebra Relational Operation Union Set Difference Cartesian Product Projection Selection pp. 55~59 pp. 52 Null pp. 411 Table Row Column pp. 43 BTree B+Tree pp. 318~326 IndexMultilevel Index pp. 310~317 (3) (4) (5) (6) (7) (8) Heap Hash pp. 38~39 (9) EntityRelationship Model EntityRelationship Diagram Entity Attribute Relationship pp. 213 (10) Conceptual Data Model Physical Data Model Implementation Data Model Data Independence Internal Schema /View Conceptual Schema External Schema pp. 23~25 (11) Database Management System pp. 12~111 20110417 (bit)<=(byte)<=(char)<= (string)<=(field)<=(record)<=(file)<=(database) <=(data mart)<=(data warehouse) Oracle, DB2, Sybase, Infomix, MySQL, PostgreSQL, Microsoft SQL Server, ... (Sharing) (relational model) Ted Codd 1970 -Entity-Relationship Model Peter Chen 1976 4 (Ans. Hierarchical data modelNetwork data modelER modelRelational data model) BTree 2 3 1 2 123 BTree SOL: 2011 10417 100 0 70 123 50,60 8 80,90 120 125 Rel lation 1NF? 2NF? 3NF? 2 ? BCNF F ( , , , ) SOL L: 2NF 3NF (, , , ) ( , ) BC CNF C CUSTOME ERS SQL a) SELECT * FROM CUSTOMER WHERE phone IS NULL; RS E ; (a ANS: A (b b) SELECT COUNT(* AS OV *) VERSEAS F FROM CUS STOMERS WHERE re W egion != 'TW'; ANS: A 2011 10417 Referentia Integrity al Nu value ull Integrity Constraint y t (1) At tomic (2) (3) Ty ype Width (4) (5) (6) Relatio algebra onal a Union Set dif fference Carte esian product Pr rojection Sel lection Natural Jo oin : DEPART TMENT : No Name Manag e ger Man nager DepN EMPLOYEE: EmpN No Name Salary DepNo De epNo SQL (a) SELECT E E.NAME AS , D.NAME AS , M.NAME AS D S PLOYEE E I INNER JOIN DEPARTME N MENT D ON E.DEPNO = D.DEPNO FROM EMP INNER JO OIN EMPLOY YEE M ON D.MANAGER = M.EMPNO D 2011 10417 WHERE E. .EMPNO <> M.EMPNO ORDER BY E.NAME Y ANS S: (b) SELECT * FROM EMP PLOYEE WHERE SA ALARY >= ( SELECT MAX(SALARY FROM EM M RY) MPLOYEE ) ANS S: (c) SELECT D D.NAME AS , AVG(SALARY AS A Y) PLOYEE E I INNER JOIN DEPARTME N MENT D ON E.DEPNO = D.DEPNO FROM EMP GROUP BY D.NAME Y ORDER BY 2 DESC Y ANS S: ...
View Full Document

Ask a homework question - tutors are online