641 Week 6 Outline

Homework Review: 

1) HW3-4 Review

2) Identify the functional dependencies in the following tables and then states whether each one is in 1^st normal form and 2^nd normal form or not. If not, fix it.

Lecture 1: Boyce-Codd Normal Form

Every determinant is a key. A table may have many functional dependencies, and BCNF requires that every the determinant in every functional dependency must be a key, i.e., can functionally determine all other columns. 

BCNF is more stringent than 3NF and 2NF. Thus, a table that is in 2NF or 3NF may still violate BCNF. 

Example: Warehouse Table

BCNF is more stringent than 3NF and 2NF. Thus, a table that is not in 2NF or 3NF will also violate BCNF. A table is not in 2NF or 3NF can be fixed using the same procedure for fixing BCNF violations. 

Example: Student Registration Table and Royalty Table

Lecture 2: Basic SQL DML Statements

Preparation: Creating sample Oracle tables using scripts from script loader on ecourse.org and Lookup tables using dictionaries (user_tables, user_constraints, user_cons_columns)

Running an Oracle Script. Running a few scripts and then use dictionary tables to find out the database tables and constraints created by the scripts

select, delete, update, insert: understand what each does and memorize the basic syntax for each command

SELECT columns (separated by commas)

FROM tables (separated by commas)

WHERE conditions (apply to individual records)

UPDATE table_name

SET col1=exp1, col2=exp2,….coln=expn

WHERE conditions (apply to individual rows)

INSERT into table_name (col-1, col-2 ….., col-n)

VALUES (val-1, val-2, …..,val-n)

DELETE FROM table_name

WHERE conditions (apply to individual rows)

Examples: Basic Select Statements

Example 1: Find all employees whose salary is more than 800

Example 2: Find all employees who were hired after 1980

Example 3: Find all employees who never received commissions.

Example 4: Find those salesmen who were hired before 1983

Example 5: Find all distinct job titles

Example 6: Find those employees whose name starts with K

Example 7: Finds all managers whose salary is more than 200

Example 8: find all analysts whose name ends with T

Additional Exercises:

The company just started a new marketing department in Chicago. The department ID is 50. Insert a new record into the database.

Create a new employee for SARA with job title CLERK and salary 2500 in Department 20

Delete salesmen who made less than 100 commissions

Change Adams’ name to ADAM

Find all analysts whose name ends with T

Homework:

Reading: 

• Chapter 19 of LIU
• SQL Tutorial

Correctness Questions: online

Hands-on Assignments (due on 10/14): 

1.  Is the following table in BCNF? If not, fix it to be so.