QUESTION 1

1. In this table:

   cust_id	fname	lname	birth_date	email
   1	James	Hadley	1972-04-22	james@example.com, james@nowhere.com
   2	Susan	Tingley	1968-08-15	susan@example.com
   3	Frank	Tucker	1958-02-06	NULL
   4	John	Hayward	1966-2-22	susan@example.com

   Which cust_id(s) is/are the main reason the table does NOT meet First Normal Form (1NF)?

   a		3
   b		1 and 3
   c		1
   d		2 and 4

5 points

QUESTION 2

1. Consider this table which violates 3NF

   customer-branch

   cust_id	fname	lname	branch	branch_zip_code
   1	James	Hadley	Chicago	60007
   2	Susan	Tingley	Naperville	60540
   3	Frank	Tucker	Elmhurst	60106
   4	John	Hayward	Chicago	60007

    Which statement best illustrates the insertion anomaly that can arise due to the 3NF violation?

   	a	INSERT INTO customers (cust_id, fname, lname, branch, branch_zip_code) VALUES (5, 'Louis', 'Blake", 'Chicago NW', 60007);
   	b	INSERT INTO customers (cust_id, fname, lname, branch, branch_zip_code) VALUES (5, 'John', 'Hayward', 'Chicago', 60007);
   	c	INSERT INTO customers (cust_id, fname, lname, branch, branch_zip_code) VALUES (5, 'Louis', 'Blake', 'Naperville', 60500);
   	d	INSERT INTO customers (cust_id, fname, lname, branch, branch_zip_code) VALUES (4, 'Louis', 'Blake', 'Wheaton', 60187);

5 points

QUESTION 3

1. Consider this table which tracks employees (by id and name) and the number of hours they worked on each project (project number and name). The primary key is {Eid,Pnumber} (Employee ID+Project Number)

   Employee_Project +------+-------+---------+---------+----------+ | Eid | Pnumber | Hours | Ename | Pname |

   +---------+------+-------+---------+----------+ | 1 | 100 | 15 | Hadley | Google | | 2 | 102 | 30 | Tingley | Facebook | | 3 | 27 | 5 | Tucker | Amazon | | 4 | 100 | 20 | Hayward | Google |

   | 2 | 100 | 30 | Tingley | Google |

   +------+---------+-------+---------+----------+ Which columns are responsible for the table failing to meet 2NF? (Select all that apply)

   a		Hours
   b		Eid (Employee ID)
   c		Pname (Project Name)
   d		Pnumber (Project Number)
   e		Ename (Employee Name)

5 points

QUESTION 4

1. Consider this table which satisfies Fourth Normal Form (4NF)

   student_id	course	student_hobby
   1	Math	Running
   1	Science	Running
   1	Math	Chess
   1	Science	Chess
   2	Math	Baseball
   2	Math	Chess
   2	Math	Movies

   If student 2 starts taking Biology, how many rows will need to be added to the table?

   a		2
   b		4
   c		3
   d		1

5 points

QUESTION 5

1. Using the "bank" database that we've been using in the class, which query will produce the most rows of output?

   A) SELECT open_branch_id FROM account;

   B) SELECT DISTINCT open_branch_id FROM account;

   C) SELECT DISTINCT open_branch_id, cust_id, open_date, close_date FROM account;

   D) SELECT open_branch_id FROM account LIMIT 20;

   a		C
   b		A
   c		D
   d		B

5 points

QUESTION 6

1. Using the "bank" database that we've been using in the class, which query will produce the fewest rows of output?

   A) SELECT open_branch_id FROM account;

   B) SELECT DISTINCT open_branch_id FROM account;

   C) SELECT DISTINCT open_branch_id, cust_id, open_date, close_date FROM account;

   D) SELECT open_branch_id FROM account LIMIT 2;

   a		A
   b		B
   c		C
   d		D

5 points

QUESTION 7

1. If you join a table to itself, what kind of join are you using?

   a		Selective Join
   b		Cross Join
   c		Self Join
   d		You can't join a table with itself

5 points

QUESTION 8

1. Which SQL keyword is used to retrieve only unique values?

   a		UNIQUE
   b		DIFFERENT
   c		DISTINCTIVE
   d		DISTINCT

5 points

QUESTION 9

1. Which of the following SQL statements against the bank database is correct?

   a		SELECT * FROM account WHERE open_date BETWEEN( '2003-07-30' , '2004-06-30');
   b		SELECT open_branch_id FROM account WHERE open_date '2003-07-30' BETWEEN '2004-06-30';
   c		SELECT FROM account WHERE open_date BETWEEN '2003-07-30' AND '2004-06-30';
   d		SELECT * FROM account WHERE open_date BETWEEN '2003-07-30' AND '2004-06-30';

5 points

QUESTION 10

1. Using our bank database, which are legal MySQL queries? (Do not generate an error)

   A) SELECT emp_id*lname FROM employee;

   B) SELECT emp_id*superior_emp_id from employee;

   a		Neither A nor B
   b		Both A and B
   c		B only, because both columns are numeric types
   d		A only because emp_id is a numeric type

   PLEASE EXPLAIN THANKS!