SYLLABUS
LAB COVER SHEET
HAND-OUTS
SQLSUPPLEMENT
DBPRIMER
LABS
WEBSITES
GRADES
CURRENT WEEK
<return to HOME PAGE

FALL 2005

PREREQUISITE: CSC1142 (intro to programming) or equivalent (see instructor for approval).
TEXT: Database Systems/ Rob & Coronel, 6^th edition
SOFTWARE: MS Acesss 97/2000; SQL Server 2000
GRADING:

                - exams (4):........................................60%
                - project:............................................20%
                - labs:.................................................20%

WEEK 1 ()

LECTURE:   Database Overview
     PAGES: Chapter 1 & 2, pp 6 – 64 (largely overview pages that can be read quickly – most of this material
                                                               will be revisited later in greater depth)
    HIGHLIGHTS: Database System compared to traditional file systems

LAB : Start LAB 1

WEEK 2 ()

LECTURE: SQL (“select” statement)
PAGES: section 6.3.3 pp246-7; sections 6.4.1 – 6.4.4 pp251 – 264; sections 6.6.1 – 6.6.4 pp 272 – 284
HIGHLIGHTS: between; like; in; exist; order by; distinct; aggregate functions; group by/having

LAB: : Labs 1 due; Start lab 2

WEEK 3 ()

LECTURE:   SQL (“select” statement)
     PAGES: sections 6.8 – 6.8.3 pp 286 – 291; sections 7.1 – 7.4.4 pp 320 – 348
    HIGHLIGHTS: joining; set operators; subqueries; any/all; correlated subqueries; date/time functions.
                                SQL SERVER: Housekeeping; Stored Procedure

LAB: Lab 2 due; Start LAB 3

WEEK 4 ()

LECTURE:   POSTPONED
     PAGES:
    HIGHLIGHTS:
LAB

WEEK 5 (9/27/05)

LECTURE:  Action Queries; Creating VIEWS, Stored Procedures; Review parts of week 3
     PAGES: section 6.3.1, pp 243 – 245; section 6.3.4, pp 247 – 248; sections 6.3.6 – 6.3.7, p 249; section 6.7, pp 284 - 285
    HIGHLIGHTS: insert; update; delete; VIEWS
LAB: LAB 3   due; start lab 4

WEEK6 (10/4/05)

LECTURE:   SQL Review; CURSORS
     PAGES:
    HIGHLIGHTS: .
LAB: lab 4 due; start lab 5

WEEK7 (10/11/05)

LECTURE:   QUIZ 1 (SQL)
     PAGES: pp 95 - 107
    HIGHLIGHTS: . intro to E-R modeling; cardinalities; indexes
LAB: lab 5due; start lab 6

WEEK 8 (10/18/05)
LECTURE:   DESIGN: ER modeling
     PAGES: chapter 4 pp 124 - 169
    HIGHLIGHTS: .
LAB: lab 6 due; start lab 7

WEEK 9 (10/25/05)
  LECTURE:
     PAGES
    HIGHLIGHTS: .
LAB: lab 7 due; start lab 8

WEEK 10 (11/1/05)
  LECTURE:   :   QUIZ 2 (SQL/same format as quiz 1); lecture at 7:15pm – ternary relationships; more illustrations.

     PAGES:
    HIGHLIGHTS: .
LAB: lab 8 due; start lab 9

WEEK 11 (11/8/05)
  LECTURE:   NORMALIZATION
     PAGES: Read the Normal Form/Normalizations section in the DB PRIMER
    HIGHLIGHTS: .
LAB: lab 9 due; begin project

WEEK 12 (11/15/05)
LECTURE:   quiz 3 due
     PAGES: : main text, ch 5, pp 184 - 201
    HIGHLIGHTS: .
LAB:

WEEK 13 (11/22/05)

LECTURE:   CLASSES NOT IN SESSION
     PAGES:
    HIGHLIGHTS:
LAB:

WEEK 14 (11/29/05)

LECTURE:   Quiz 4 Review; XML; Web Application
     PAGES: Read in DB Primer : section XML VS RELATIONAL & section WEB APPLICATION/ASP.NET
    HIGHLIGHTS:
LAB:

WEEK 15 (12/6/05)

LECTURE: QUIZ 4 : Data Modeling (ER diagrams); Normalization
PAGES:
HIGHLIGHTS:
LAB:

WEEK 16 (12/13/05)

LECTURE
PAGES:
HIGHLIGHTS:
LAB:

WEEK 17 (12/20/05)

LECTURE LAST DAY
PAGES:
HIGHLIGHTS:
LAB: project due

<return to top

HANDOUTS

HANDOUT #1: Student Database

STUDENT TABLE (FILE)

SID	NAME	MAJOR	GRADE	AGE
100	JONES	HIST	GR	21
150	PARKS	ACCTG	SO	19
200	BAKER	MATH	GR	50
250	GLASS	HIST	SN	50
300	BAKER	ACCTG	SN	41
350	RUSSELL	MATH	JR	20
400	RYE	ACCTG	FR	18
450	JONES	HIST	SN	24

ENROLL TABLE

CLASS	POS	SID
BD445	1	100
BA200	1	150
BD445	2	200
CS250	1	200
CS150	1	300
BA200	2	400
BF410	1	400
CS250	2	400
BA200	3	450

CLASSES TABLE

CLASS	CTIME	ROOM
BA200	M-F9	SC110
BD445	MWF3	SC213
BF410	MWF8	SC213
CS150	MWF3	EA304
CS250	MWF12	EB210

HANDOUT #2 ()

HANDOUT #3

HANDOUT #4 Premeier Database

PREMIER DATABASE

WORKER
WORKER_ID	WORKER_NAME	HRLY_RATE	SKILL_TYPE	SUPV_ID
1235	M. FARADAY	12.5	ELECTRIC	1311
1311	C. COULOMB	15.5	ELECTRIC	1311
1412	C. NEMO	13.75	PLUMBING	1520
1520	H. RICKOVER	11.75	PLUMBING	1520
2920	R. GARRETT	10	ROOFING	2920
3001	J. BARRISTER	8.2	FRAMING	3231
3231	P. MASON	17.4	FRAMING	3231

ASSIGNMENT
WORKER_ID	BLDG_ID	START_DATE	NUM_DAYS
1412	111	12/1/1997	4
3001	111	10/8/1997	14
3231	111	10/10/1997	8
1412	210	11/15/1997	12
2920	210	11/10/1997	15
3001	210	10/27/1997	14
1235	312	10/10/1998	5
1412	312	10/1/1997	10
1520	312	10/30/1997	17
3231	312	10/24/1997	20
1311	435	10/8/1997	12
1412	435	10/15/1997	15
2920	435	10/28/1997	10
1311	460	10/23/1997	24
1412	460	12/8/1997	18
2920	460	10/5/1997	18
1235	515	10/17/1998	22
1412	515	11/5/1997	8
1520	515	10/9/1997	14

BUILDING
BLDG_ID	BLDG_ADDRESS	TYPE	QLTY_LEVEL	STATUS
111	1213 ASPEN	OFFICE	4	1
210	1011 BIRCH	OFFICE	3	1
312	123 ELM	OFFICE	2	2
435	456 MAPLE	RETAIL	1	1
460	1415 BEECH	WAREHOUSE	3	3
515	789 OAK	RESIDENCE	3	1

HANDOUT #5 : Sales Database

PRODUCT TABLE

PRODUCT_ID	DESCRIPTION	UNIT_PRICE
1	RAKE	15
2	HOE	17
3	SHOVEL	12
4	CLIPPERS	16

SALES TABLE

TRANSACT_ID	SLSPERSON_ID	PRODUCT_ID	QTY_SOLD	DATE	DISCOUNT
1	3	2	10	1/2/97	0
2	1	3	20	1/5/97	0
3	1	2	5	1/7/97	0
4	2	1	8	1/7/97	0
5	3	2	10	1/8/97	0
6	1	3	34	1/8/97	0
7	2	1	56	1/8/97	0
8	3	1	26	1/9/97	0
9	1	3	78	1/9/97	0
10	2	2	14	1/12/97	0
11	3	3	43	1/12/97	0
12	1	1	9	1/12/97	0
13	1	4	19	1/12/97	0

SPERSON TABLE

SLSPERSON_ID	NAME
1	ABLE
2	BAKER
3	CRANE
4	DOE

HANDOUT #6: WAREHOUSE Database

PARTS TABLE

PTNUMB	PTDESC
P2	TRANSISTOR
P3	CAPACITOR
P4	INDUCTOR
P7	INDUCTOR
P9	CAPACITOR

SUPPLIER TABLE

SNUMB	SNAME	LOCATION
S3	TRONOTON	CAL
S4	ROBOTEK	TEXAS
S6	MIKREL	ONTARIO
S7	RAYTEX	TEXAS

PURCHASE TABLE

EMPNUMB	SNUMB	ORDERNUM
E3	S7	OR67
E1	S4	OR73
E7	S3	OR76
E6	S6	OR77
E3	S4	OR79
E1	S6	OR80

ORDERS TABLE

PTNUMB	ORDERNUM	LINE	QTY
P4	OR67	1	250
P7	OR67	2	200
P9	OR73	1	160
P2	OR73	2	100
P4	OR73	3	200
P9	OR76	1	260
P2	OR77	1	140
P7	OR77	2	170
P3	OR79	1	150
P9	OR79	2	200
P2	OR80	1	160

WHOUSE TABLE

WHNUMB	CITY	FLOORS
WH1	DALLAS	3
WH2	NEW YORK	5
WH3	CHICAGO	2
WH4	NEW YORK	3

EMPLOYEE TABLE

WHNUMB	EMPNUMB	SALARY	YR
WH2	E1	22000	1986
WH1	E3	21000	1987
WH2	E4	25000	1984
WH3	E6	23000	1986
WH1	E7	25000	1985
WH4	E8	23000	1984
WH1	E9	24000	1987
WH4	E10	26000	1983

HANDOUT #7 : "EXISTS" TEMPLATE

The following template can be used for the query type: LIST THE A's THAT HAVE C'd EVERY B.
A,B, and C are aliases referring to tables.

SELECT A._______
FROM _______ A
WHERE NOT EXISTS
(SELECT *
    FROM ______ B
    WHERE NOT EXISTS
       (SELECT *
         FROM C
         WHERE A._____ = C.______ AND
                        B._____ = C.______));

EXAMPLE: List the STUDENTs that have ENROLLed in every CLASS.
REWORDED: List the STUDENTs that have no CLASS not ENROLLed.
A=STUDENT; B=CLASS; C=ENROLL

SELECT A.SID
FROM STUDENT A
WHERE NOT EXISTS
(SELECT *
    FROM CLASSES B
    WHERE NOT EXISTS
       (SELECT *
         FROM ENROLL C
         WHERE A.SID = C.SID AND
                        B.CLASS = C.CLASS));

EXAMPLE: List the CLASSES that have ENROLLed every STUDENT.
REWORDED: List the CLASSES that have no STUDENT not ENROLLed.
A=CLASSES; B=STUDENT; C=ENROLL

SELECT A.CLASS
FROM CLASSES A
WHERE NOT EXISTS
(SELECT *
    FROM STUDENT B
    WHERE NOT EXISTS
       (SELECT *
         FROM ENROLL C
         WHERE A.CLASS = C.CLASS AND
                        B.SID = C.SID));

HANDOUT #8:

HANDOUT #9:

HANDOUT #10: C++ version of correlated subquery.

// corrsubq.cpp
// ILLUSTRATES CORRELATED SUBQUERY IN C++
//QUERY: List names of students with ages greater than the average
//                age of their major.
1. #include<iostream.h
2. #include<fstream.h
3. void main()
4. {
5.   ifstream la,lb;
6.   int ctr,tot,avg;
7.   struct rectype
8.   {
9.       char name;
10.     char major;
11.     int age;
12. };
13.   rectype reca,recb;
14.   la.open("corrsub.txt");
15.   while (lareca.namereca.majorreca.age)
16.   {
17.     lb.open("corrsub-2.txt");
18.     ctr=0;
19.     tot=0;
20.     while (lbrecb.namerecb.majorrecb.age)
21.     {
22.       if (reca.major==recb.major)
23.       {
24.         avg=tot+recb.age;
25.         ctr++;
26.       }//endif
27.     }//endwhile (lb...)
28.     lb.close();
29.     tot=tot/ctr;
30.     if (reca.age > avg)
31.     cout<<reca.name<<" "<<reca.major<<" "<<reca.age<<" avg="<<avg<<endl;
32. // endif
33.   }//endwhile (la....)
34. }//end main

(input file)

A M 23
B M 19
C M 32
D H 35
E H 42
F H 25
G A 18
H A 22
I A 27

(output)
C M 32 avg= 24
D H 35 avg= 34
E H 42 avg= 34
I A 27 avg= 22

HANDOUT #11: CLASS PROJECT

VIDEO RENTAL

General Description:

You have been hired as a database consultant to the local video store that rents tapes to customers. The store maintains the usual information about every customer (name, address, phone number, and so on). It also has detailed information about every movie, such as its duration, rating, rental price, number of days it can be rented, and purchase price. One customer can rent several tapes and the same tape will (over time) be rented to many customers.

The owner of the store needs a detailed record of every rental that identifies the movie, the customer, the date the rental was made, the due date and the date of return. The owner also needs various customer reports and sales reports to be designed by the database consultant.

NOTE: You will stock many tapes of the same movie.

Approach

Phase I (Develop Database)

1. Construct conceptual data model showing relationships between entities, and entity attributes.
2. Transform conceptual data model into the relational logical data model:
Construct relational schema. For example: customer(cust_id,address,phone......).
3. If necessary, normalize the tables into Boyce Codd normal form (BCNF).
4. Construct the database using SQL Server.

Phase II (Develop Select Queries Using SQL)
Be sure to number you queries as indicated below
(Required Queries )
1. List overdue rentals
2. List rentals due today
3. List number of times various tapes have been USED.
4. List the most popular movies from most to least.
5. List the number of available (not currently rented) tapes by title.
6. List the percentage of rentals that are returned late.
(Add a Minimum of Six More Select Queries)
7. Correlated subquery
8. having >= all
9. a view
10. group by/having
11. involving dates:
Example: select * from tapes where purch_date > #10/11/00#
select * from rentals where (date() – date_due) < 0
12 employing a nested select statement in a select clause.

Phase III (Develop ACTION Queries Using SQL)
1. Use the insert command to insert new records into various tables.
2. Use the update command to modify various tables.
3. Use the delete command to remove records from various tables.

Documentation

Submit the following documentation in the order below:

***************************************

LABS

LAB #1

1. Create STUDENT DATABASE (see handout #1) using MS ACCESS.
2. Using the QBE method, answer the following queries:
    Query 1: List the names and ages of math majors .
    Query 2: Display the average age of math majors.
    Query 3: List the names of students enrolled in class BD445.
    Query 4: List the names of the students and their class times - order by student name.

DIRECTIONS:
Display the completed lab to the instructor.

LAB #2

Use the student database.
Write SQL statements to answer the following QUERIES:
1. List the names of students who are between the ages of 18 and 24 (not including 18 & 24). List them in
    ascending order by age.
   Use the between operator.
2. List the names of the students who have the letter e in their name.
3. List the name and age of students who are either math or acctg majors.
Do it 2 different ways:
     A. Use the “or” operator.
     B. Use the in operator.
4. List the different majors – no duplicates.
5. What is the maximum age of accounting majors?
6. List the major and the number of students in each major.
7. List the majors with more than 2 students.

DIRECTIONS: (the same for subsequent labs)
1.Use a cover sheet.
2. Print the SQL statements and the output for each query.

LAB #3

Create the Premier Database (see handout).
Write SQL statements to answer the following QUERIES:
1. List the worker names and the bldg id’s to which they are assigned.
2. List the id of workers that are assigned to either bldg111 or bldg 312.
Use a SUBQUERY for query 2
3. List the assignments whose num_days is greater than the average num_days of all assignments.
4. List the assignments whose num_days is greater than the average num_days of the assigned building.
Use a correlated subquery for query 4
5. List the worker name and the type of building for each worker’s assignments.
6. List the id of workers who do NOT have a single assignment in building 435.
7. List all the assignments on the 12^th month.
8. List the building id that has the most number of assignments - use the ALL operator.

LAB #4
Use PREMIER Database.
Write SQL statements to answer the following queries:
1.

       A.) Use the following “select statement” to create a “worker2” table
       SELECT * INTO WORKER2 FROM WORKER
A.) Use the following “select statement” to create a “assignment2” table
       SELECT * INTO ASSIGNMENT2 FROM ASSIGNMENT

*************************************************************
USE WORKER2 AND ASSIGNMENT2 TABLES FOR THE ACTION QUERIES BELOW
2. Increase all the hourly rates by 10% - use the update command. Show the resulting table.
3. Add the following assignment using the insert command: (Show the resulting table.)
     worker_id: 3231
    bldg_id: 515
    start_date: 12/1/1997
    num_days: 9
4. Delete all the “electric” workers – use the delete command. Show the resulting table.
5. Use the insert command to add the “electric” workers from table worker to table worker2. Show the resulting table.

FINISH USING WORKER2 & ASSIGNMENT2 TABLES
******************************************************************
6. List the workers whose hourly rate exceeds their supervisor’s hourly rate. Include the following info in your output:
      worker id, worker hrly rate, supervisor hrly rate.
7. List the worker_id and skill_type of those workers whose hrly rate exceeds the average hrly rate of their skill_type.
8. List the skill_type that has the highest average hrly rate.

LAB #5

Create the Sales Database (see handout #5).
Use SQL to answer the following queries:
Create Stored Procedures (SQL Server) for each of the following

            1. List the product id and total dollar amount per product.
            2. List the product id, description and total qty sold per product.
            3. List the product id and the total units sold for those products with at least 25 units sold.
            4. List the id of the salesperson having the largest single transaction of qty sold.
            5. List the product id and the salesperson id for the salesperson having the single highest transaction (qty sold) for each product. Order by product id.

LAB #6

Create the WAREHOUSE Database (see handout #6).
Use SQL to answer the following queries:

            1. List the part nos that have been ordered by NEW YORK warehouses.
            2. List the employee no. and salary of each employee who has issued orders to ONTARIO companies.
            3. List the the supplier name of all suppliers who have NO orders for capacitors.
            4. How many CAPACITORs are on order by the DALLAS warehouse?
            5. List the order number of those orders that DO NOT contain orders for capacitors.

LAB #7

Use the WAREHOUSE DATABASE
Add the following record to the ORDERS table:
P2 OR79 3 120
Use SQL to answer the following queries:

            1. List the ORDERNUM and AVG QTY for each order whose average qty is more than 200.
            2. List the EMPNUMB & SALARY of the NEW YORK employee whose salary exceeds that of
                    any other New York employee.
           3. List the ORDERNUM with the largest AVG QTY.
            4. List the ORDERNUM & QTY which represents the largest qty in EACH ORDER.
            5. List the ORDERNUM of each order where every qty (in a given order) is above 199.

LAB #8
Use NATIONAL BANK database (available in shared folder).

Write SQL statements to answer the following queries:

1. Each customer can have many loans – list each loan amount for each customer.
2. List the custid, loanid, and largest loan amount per customer.
3 List the cust id and avg loan amount of the customer with the HIGHEST AVERAGE LOAN AMOUNT.
4. List the loan id, loan amount and the percentage the loan amount is of the total loan amount.

LAB 9

Use NATIONAL BANK database (available in shared folder).

Write SQL statements to answer the following queries:

1. List custid, last name, total loan amount and total payments per customer
2. List custid, last name, loanid, total loan amount and total payments per loan within customer.
3. List custid, TOTAL loan amount (per customer) and percent of total loans.

LAB 10

WEBSITES

<return to top

WEEK 10 (11/1/05) LECTURE: : QUIZ 2 (SQL/same format as quiz 1); lecture at 7:15pm – ternary relationships; more illustrations. PAGES: HIGHLIGHTS: . LAB: lab 8 due; start lab 9

WEEK 11 (11/8/05) LECTURE: NORMALIZATION PAGES: Read the Normal Form/Normalizations section in the DB PRIMER HIGHLIGHTS: . LAB: lab 9 due; begin project

WEEK 13 (11/22/05)

LECTURE: CLASSES NOT IN SESSION PAGES: HIGHLIGHTS: LAB:

LECTURE: Quiz 4 Review; XML; Web Application PAGES: Read in DB Primer : section XML VS RELATIONAL & section WEB APPLICATION/ASP.NET HIGHLIGHTS: LAB:

HANDOUT #4 Premeier Database

PREMIER DATABASE

WEEK 10 (11/1/05)
LECTURE: : QUIZ 2 (SQL/same format as quiz 1); lecture at 7:15pm – ternary relationships; more illustrations.

PAGES:
HIGHLIGHTS: .
LAB: lab 8 due; start lab 9

WEEK 11 (11/8/05)
LECTURE: NORMALIZATION
PAGES: Read the Normal Form/Normalizations section in the DB PRIMER
HIGHLIGHTS: .
LAB: lab 9 due; begin project

LECTURE: CLASSES NOT IN SESSION
PAGES:
HIGHLIGHTS:
LAB:

LECTURE: Quiz 4 Review; XML; Web Application
PAGES: Read in DB Primer : section XML VS RELATIONAL & section WEB APPLICATION/ASP.NET
HIGHLIGHTS:
LAB: