CS 4660 DATABASE ARCHITECTURE: SYLLABUS (Summer 2006) Instructor: Ted Billard Email : ted.billard@csueastbay.edu Phone : 885-3437 Home Page : www.mcs.csueastbay.edu/~billard Class Time: TR 8-9:50am Office Hrs: Tues 12-12:30pm, Weds 9-11:30am (Sc N216) Summary: The course is divided into three parts. In the first part, we study how to design good relational databases using normal forms. Several case studies are examined in lectures and workshops. In the second part, we study how to make queries to extract useful information from relational databases. Queries are expressed in SQL and relational algebra. The generation of the data definition using SQL is also examined. The third part is database programming using SQL for data definition and query. Each student will have access to their own MySQL database. The term project is a university database and is considered throughout the course along with regular homework assignments. Prerequisite: CS 3240 Data Structures. Notes: Instructor Lecture Notes (ILN) 4660/6660. Optional Text : Database Management Systems, Ramakrishnan, McGraw-Hill, 3rd ed. (0-07-246563-8) Schedule: Date Topic Chapter Exercises Due 1 6/20 Intro, Entity-Relationships (E-R) ILN,14.2 2 6/22 Functional Dependencies (FD) ILN,15.2 3 6/27 Normal Forms (3NF) ILN,15.5 4 6/29 Case Studies ILN 5 7/06 PRACTICE QUIZ: FD 6 7/11 Relational Algebra, SQL ILN,8.2,9.1,9.3 Ex.I: Design (practice) 7 7/13 Relational Algebra, SQL 8 7/18 PRACTICE QUIZ: Design 9 7/20 TEST 1: Design 10 7/25 Workshop: Project and Query Ex.II: Project Design 11 7/27 Negation ILN,8.2 12 8/01 Data Definition Language ILN,2.1 Ex.III: Query (practice) 13 8/03 PRACTICE QUIZ: Query 14 8/08 Advanced Query ILN,8.2 15 8/10 TEST 2: Query 16 8/15 Advanced Query ILN,8.2 17 8/17 Advanced Query ILN,8.2 Ex.IV: Advanced Query (practice) 18 8/22 Aggregates,Integrity,Views ILN,10.2 19 8/24 QUIZ 1: Advanced Query ILN,10.2 8/31 FINAL EXAM: Advanced Query Ex.V: Term Project CS 4660 DATABASE ARCHITECTURE: EXERCISES (Summer 2006) EXERCISE I: EXAMPLE DESIGN Design a database for McPrinters, Inc., which prints books for publishers. McPrinters has different branches. Each publisher asks McPrinters to do various print jobs. A job requires items like paper and ink. The items for a particular job are purchased with one or more purchase orders. Each purchase order has a list of unique items along with quantity. A purchase order is used for only one job. In the following, determine the appropriate keys and other attributes. 1. Make an E/R diagram 2. Make an FD List 3. Make an FD Diagram 4. Make a relation schema including primary and foreign keys EXERCISE II: PROJECT DESIGN - UNIVERSITY DATABASE For the term project, do each of the following: 0. Give examples of reports, documents, catalogs, files, etc. with data 1,2,3,4. Repeat steps above. EXERCISE III: INTRODUCTION TO SQL, ALGEBRA Review the Supplier queries in lecture notes and practice SQL [sorry, mysql does not have NOT IN]: 6.6.2,6.6.6,6.18,6.22,6.24,6.34,6.40 unix% mysql -pany -u any -h gold suppDB Do each of the following: 1. Get supplier status of OCR and Display projects (SQL, Alg). 2. Get S#, P#, J# where at least two are co-located but it must be J6 (SQL). 3. Get J# for projects supplied only by S2 (6.41) (SQL, Alg). Review the Animal schema and practice SQL: unix% mysql -pany -u any -h gold animalDB Do the following: 4. Which rooms have underweight (<55) males (SQL, Alg)? EXERCISE IV: ADVANCED QUERY Review 6.3, 6.4, 6.37, 6.43 Do each of the following: 1. Get P# supplied to all projects in LONDON (6.42) a. Give two different algebra solutions: divide and minus b. Give one SQL solution: NOT IN 2. Get J# for projects supplied with at least all parts available from S2 (6.44). a. Give two different algebra solutions: divide and minus b. Give one SQL solution: NOT IN 3. Get S#, P# such that S# does NOT supply P# (6.47). a. Give one SQL solution: NOT IN 4. Find the minimum WEIGHT of animals. a. Give one SQL solution using aggregates and mysql. b. Give another SQL solution without aggregates. c. Give an algebra solution without aggregates. EXERCISE V: PROJECT DDL/DML [No Teams] For the term project, do each of the following: 1. SQL Data Definition Language (DDL) to CREATE TABLEs for University design. 2. SQL Data Manipulation Language (DML) to INSERT example data into tables. 3. SQL DML to generate reports, documents, catalogs, files, etc. unix% recreate unix% mysql -u -p -h gold Collaboration/Copying: Unless otherwise stated, do your own work in this class. Violations will be prosecuted to the full extent of the University rules.