School of Mathematics &
Computer Science
|
Course#: |
|
|
Class Time: |
Thursday |
|
Instructor: |
Professor Pepper Office: POST 103 1rst Floor (Behind the SBarro's cafeteria) Telephone: 516-747-2362, (please leave a message with your phone number) Office Hours: Tuesday and Thursday on certain Tuesdays (2/1, 2/8,
2/22, 3/1, 3/29, 4/5), office hours will be held on Tuesday from
E-mail: pepper@adelphi.edu http://www.adelphi.edu/~pe16132 |
Vieira, Robert. Beginning Microsoft SQL Server 2008
Programming
Recommended and will try to have in the library:
Fowler, Martin. UML Distilled: A Brief Guide to the Standard Object Modeling Language. Boston, MA: Addison-Wesley Professional, 2003. 0321193687
Silberschatz. Database System Concepts, Sixth Edition, McGraw-Hill
Publishing Company, 2010, ISBN: 0-07-352332-1
Plus we will be using tutorials at: http://www.quackit.com/sql_server/ or http://www.functionx.com/sqlserver/index.htm or http://www.w3schools.com/sql/default.asp or https://www.webucator.com/tutorial/learn-sql/simple-selects.cfm
http://sqlzoo.net/ or http://sqlcourse2.com/
Download SQL Server 2008 Express at : http://go.microsoft.com/?linkid=9394725
This course covers in-depth analysis of the relational database model, objectives of database processing, database design, and storage structures and access methods. Database design and normalization, normal forms. Programming assignments will be done using Access or Oracle or MS SQL. It also includes UML Diagramming.
Grading System
The course grading scale is: A 93-100, A- 90-92, B+ 87-89, B 83-86, B- 80-82, C+ 77-79, C 73-76, C- 70-72, D 60-69, and F is below 59. It is not anticipated that grades will be curved.
Grading will be based on timely delivery of high quality deliverables. A minimum for passing this course: You need the ability to write complex select queries that join multiple tables, filter results and aggregate. You also need to be able to craft insert, drop, update and delete queries. You also need familiarity with connecting to an SQL database via a programming language. Basic understanding of normal forms and table relationships is also required. Beyond this minimum, I am hoping you have a firm grasp of the select statement including the use of sub-queries, views, complex aggregation and stored procedures, as well as the ability to code complex queries in either Java, Visual C++ or the MS Report Designer.
Disabilities
If you have a physical, medical or learning disability and
require accommodations, please contact the Office of Disability Support
Services (
Schedule
|
Week |
Meeting Date |
Pepper presents |
Deliverable before
the next class |
Text |
|
1 |
1/27 |
Determine goals, What is a database and what are its functions? Intro to MS Sql Server Management Studio Intro to the select statement |
Have SQL server management studio downloaded on your pc; some select queries uploaded through moodle (see moodle assignment)
|
Chap 1-3 (through select
statement) |
|
2 |
2/3 |
-Intro to northwind and pubs and adventureworks -Aggregate one table with selections insert, update, delete |
A set of queries to show you can Aggregate one table with selections insert, update, delete. – to be assigned Write a short description of the 3 sample databases we
will use so I can be sure you understand the business they model |
rest of chapter 3 |
|
3 |
2/10 |
joins & union Explain normal forms |
Give me a set of SQL queries on the chosen database that show you know basic select / sort / join / filter. – to be assigned
optional: Either do the following: http://www.quackit.com/sql_server/ sql tutorial up to SQL Outer Join (alternate sites you find are ok) OR just do the following tutorial which has the
interactive tester built in: http://sqlcourse2.com/
|
Chap 4 |
|
4 |
2/17 |
-create and alter tables -foreign and primary keys
|
Execute create tables, insert and update – to be assigned optional: http://www.quackit.com/sql_server/ sql tutorial finishing up to end. |
Chap 5 & 6 |
|
5 |
2/24 |
sub-queries; exists; functions; casting and converting; |
Execute complex queries – to be assigned |
Chap 7 |
|
6 |
3/3 |
Normalization |
-Fill in cardinality of given northwind ERD - Write examples
for each of the 4 forms by choosing an example of its implementation |
Chap 8 |
|
7 |
3/10 (No 3/16 |
UML: Use Use ERD and class diagrams Understand table relationships
|
Fill out |
UML Distilled
|
|
8 |
3/24 |
Indexes & Views |
-Create High Level spec for our final project. Include ERD. - view and index proof |
Chap 9&10 |
|
9 |
3/31 |
Scripts and Stored procedures |
-Create High Level spec for our final project. Include ERD. -Execute complex queries and create procedures http://www.quackit.com/sql_server/ SQL Server 2008 tutorial views and stored procedures topic |
Chap 11 & 12 |
|
10 |
4/7 |
Connect to DB using Java or Visual C++; Code queries |
-Tech spec for final project. -Connect and run a simple update command. Ask the user for the old and new value. |
Appendix B |
|
11 |
4/14 |
Use procedures with programming language |
Begin coding final project. |
|
|
12 |
4/21 |
Security & indexing |
Work on final project http://www.quackit.com/sql_server/
SQL Server 2008 tutorial Logins, roles and schemas topics |
Chap 14 |
|
13 |
4/28 |
|
Work on final project |
|
|
14 |
5/5 |
|
Work on final project |
|
|
15 |
5/12 |
|
Final project due |
|
Weeks 10-15 may instead be an exploration of MS report modeling instead.