HOME PAGE

CS430 DBMS Design, Spring'05

• We use zeus.vwc.edu as Oracle DBMS server
• Syllabus
• Class Policy
• Spring Break: 3/21 - 3/25
• Tests
  • Test 1: 
• Assignments ( Please type the questions and your answers) 
  • Homework 1: 
• SQL*Plus User's Guide (in campus)
• E-R Model Link (link1)
• Topics covered:
  • 1/24(M): Entity, relationship
  • 1/26(W): Data redundancy, types of DBMS
  • 1/28(F): Table, primary key, foreign key, integrity
  • 1/31(M): Relational Algebra: Union, Intersection, etc.
  • 2/02(W): Join, division and applications
  • 2/04 (F): Relational Calculus
  • 2/07(M): DB design: ER models - Chen, Crow's Foot
  • 2/09(W): Attributes; Dependency 
  • 2/11(F): Anomalies; Normalizations - 1NF. 2NF, 3NF
  • 2/14(M): 3NF; Examples; ERDs
  • 2/16(W): Oracle 9i Client/Server DBMS
  • 2/18(F): SQL (;) and SQL*Plus: ed, spool, show/set, get/start (@)
  • 2/21(M): Define editor; SQL; comparison of SQL and SQL*Plus
  • 2/23(W): SQL: Naming rules; data types; VarChar2(N)
  • 2/25(F): Char(N), Number, Number(p), Number(p,s), date types;
  • 2/28(M): Date; Integrity/value constraints; defined at column level
  • 3/02(W): Defined at table level
  • 3/04(F): Test I
  • 3/07(M): Test I Answer Key 
  • 3/09(W): Use SQL scripts file. Constraints: NOT NULL, UNIQUE, CHECK
  • 3/11(F): Script file: Create tables; use Oracle 9i on Zeus. 
  • 3/14(M): Insert tuples; TO_DATE function; insert NULL values
  • 3/16(W): Display constraints' information; run script file to: drop, create table, insert data, query. 
  • 3/18(F): vi editor in UNIX; user_tablespaces and user_users tables; add column
  • 3/13(W):Modify column type and constraints; drop a column; rename a column
  • 4/01(F): Truncate table; substitute variables; SET DEFINE OFF; update records
  • 4/04(M):Update/delete records; retrieve data with order; set liesize; DISTINCT; column alias
  • 4/06(W): COLUMN command; column concatenation; WHERE clause; ORDER BY clause
  • 4/08(F): SQL functions for character, number, and date; DUAL table; NVL function
  • 4/11(M): to_number(col), to_char(col, format) -- for retrieval, to_date(col, fmt) -- for insertion; group functions: max, min, sum, avg, count 
  • 4/13(W): Group by; Sub-query; Review.
  • 4/15(F): Test II 
  • 4/18(M): Having clause for group condition; substitute variable and define command. Multiple tables: Eqi-join
  • 4/20(W): Eqi-Join with group function; Non-Eqijoin
  • 4/22(F): Outer-join: (+) locates at the deficient table/col side -- NULL value allowed
  • 4/25(M): Set operations: UNION (ALL), INTERSECT, MINUS; Sub-query - nested query: single-row sub-query  
  • 4/27(W): Use sub-query to: create a table with records; insert records; update a record; delete a record   
  • 4/29(F): Multiple-row sub-query: IN, >ALL (>max), <ALL (<min), >ANY (>min), <ANY (<max), =ANY (same as IN)
  • 5/02(M): Top-N query: ROWNUM, inline view; create/drop a view, list views
  • 5/04(W): Control access -- DBA tools: create user/passwd/role; PL/SQL: naming, block structure, variable declarations and assignment, example
  • Final: 8 - 10:30am, Mon., 5/9/05