INF3560: Database Design - Syllabus

Contact Information

Name	Prof. Yitzchak Rosenthal
Office	Belfer Hall room 1313-D
Telephone	(212)960-5400 x5438 (best way to reach me is via email)
Office Hours	Wilf Campus - M/W 4:15-5PM or after class; Beren Campus - M/W 1:10-1:45 or after class
Email

Course Objectives

Relational databases pervade the corporate landscape. They store the data which is lifeblood of the modern corporation. The smooth operation of corporate databases is often a critical component of corporate success. Database administrators (DBAs) have consistently been among the highest paid information technology workers in the USA. The purpose of this course is to introduce students to knowledge and skills required to perform as a corporate DBA. We will cover both theory and practical skills. There are many DataBase Management System (DBMS) products on the market today. Each one is based on the same underlying theory but may have some practical differences. We will study the general theory and will use the "Microsoft SQL Server" DBMS for practical applications.

Books

Visual Quickstart Guide: SQL, second edition
by Chris Fehily
Peachpit Press
ISBN: 0-321-33417-5
Database Design, Application Development and Administration, 2nd edition
by Michael V. Mannino
McGraw Hill
ISBN: 0-07-288067-8

Grading

Homework: 20%
Midterm: 40%
Final: 40%

Attendance

You are permitted up to 3 absences. If you have more than 3 absenses then you MUST have a very good excuse for ALL of your absenses or else your grade will suffer.

Topics

Date	Topics	Readings
Week 1	Forms of data storage other than a Relational DataBase Management System (RDBMS): flat files, structured files (e.g. XML, Excel), hierarchical & network database management systems Intro. to Relational DBMS's and the relational model: tables, columns, rows, fields, candidate keys, primary key, foreign keys, composite keys, joining data from mulitple tables, difference between a DBMS and a DataBase(DB), popular commercial DBMSs Intro to Database Design: "crows foot" Entity Relationship Diagrams (ERDs) & other ERD forms, minimum & maximum cardinality constraints, functional dependencies (FDs) & normal forms (1st, 2nd, 3rd, BCNF, 4th, 5th normal forms), denormalization, designing an ERD from a set of business rules	Mannino: Chaps 1, 2, 5, 7 Fehily: Chaps 1, 2
Week 2	DBMS architecture: 3 tiered design - clients/appliation servers/DBMS Intro to Microsoft SQL Server: Enterprise Manager, "MSSQLSERVER" Windows service and other Windows services, "SQL Server Service Manager" applet, SQL Server client server architecture, windows authentication vs. SQL Server authentication, SQL Server registration, SQL Server groups, creating a DataBase, data files & log files, multiple physical disk drives, separation of data & log files, using the Graphical User Interface (GUI) to create tables, fields & keys: SQL Server data types [character types (char/varchar/text/nchar/nvarchar/ntext), exact numeric types, approximate numeric types, datetime types], NULL values, automatically generating SQL scripts for existing database objects, executing SQL scripts from the GUI, creating the sample DB from a script Intro to Structured Query Language (SQL): compiled into applications & used by DBAs, declaritive (non-procedural) language, Data Manipulation Language (DML) statements vs. Data Definition Language (DDL) statments, SQL standards, DBMS specific extensions, quoted & non-quoted identifiers, comments, CREATE TABLE command: named constraints & unnmaed constraints, NULL, NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, Literal values for text/numeric/datetime values, operators (> < >= <= >< (used with CHECK constraint), ( ), IN, BETWEEN, AND, OR, NOT); DROP TABLE command; ALTER TABLE command;	Mannino: Chap 3 Fehily: Chaps 3, 11
Week 3	More SQL: INSERT command, UPDATE command, DELETE command. Intro to SQL SELECT command. SELECT, FROM, WHERE, ORDER BY, AS, DISTINCT, AND/OR/NOT in WHERE, LIKE, IS NULL, IS NOT NULL SQL operators and functions: concatenation, SUBSTRING(), UPPER(), LOWER(), TRIM(), CAST(), CASE, COALESCE(), NULLIF(), and others	Mannino: Chap 3 Fehily: Chaps 10, 4, 5
Week 4	Summarzing and grouping data: GROUP BY, aggregate functions, using DISTINCT with aggregate functions, COUNT(*) vs. COUNT(expression), HAVING clause. Joining tables: cartesian product (AKA cross product, AKA cross join), WHERE syntax, SQL-92 JOIN syntax, inner joins, LEFT OUTER, RIGHT OUTER, FULL OUTER	Mannino: 3, 9 Fehily: Chaps 6, 7
Week 5	Subqueries: converting between subqueries and joins, correlated subqueries, IN, ALL, ANY, EXISTS Set operations: UNION, INTERSECT, EXCEPT	Mannino: 9 Fehily: 8, 9
Week 6	Physical design and Indexes MIDTERM	Mannino: 8 Fehily: 12
Week 7	Views: creating a view, reasons for using views, querying a view, updating data through a view Intro to stored procedures	Handouts Mannino: 10, 11 Fehily: 13
Week 8	More stored procedures Triggers	Handouts Mannino: 11 Fehily: N/A
Week 9	Advanced stored procedures Transactions	Handouts Mannino: 15 Fehily: 14
Week 10	Database Security	Handouts Mannino: 14 Fehily: N/A
Week 11	Data warehouse technology and management.	Mannino: 16 Fehily: N/A
Week 12	Client Server Processing and Distributed Databases	Mannino: 17 Fehily: N/A
Week 13	Writing application code to access databases	Handouts Mannino: N/A Fehily: N/A

Forms of data storage other than a Relational DataBase Management System (RDBMS): flat files, structured files (e.g. XML, Excel), hierarchical & network database management systems

Intro. to Relational DBMS's and the relational model: tables, columns, rows, fields, candidate keys, primary key, foreign keys, composite keys, joining data from mulitple tables, difference between a DBMS and a DataBase(DB), popular commercial DBMSs

Intro to Database Design: "crows foot" Entity Relationship Diagrams (ERDs) & other ERD forms, minimum & maximum cardinality constraints, functional dependencies (FDs) & normal forms (1st, 2nd, 3rd, BCNF, 4th, 5th normal forms), denormalization, designing an ERD from a set of business rules

Mannino: Chaps 1, 2, 5, 7

Fehily: Chaps 1, 2

Week 2

DBMS architecture: 3 tiered design - clients/appliation servers/DBMS

Intro to Microsoft SQL Server: Enterprise Manager, "MSSQLSERVER" Windows service and other Windows services, "SQL Server Service Manager" applet, SQL Server client server architecture, windows authentication vs. SQL Server authentication, SQL Server registration, SQL Server groups, creating a DataBase, data files & log files, multiple physical disk drives, separation of data & log files, using the Graphical User Interface (GUI) to create tables, fields & keys: SQL Server data types [character types (char/varchar/text/nchar/nvarchar/ntext), exact numeric types, approximate numeric types, datetime types], NULL values, automatically generating SQL scripts for existing database objects, executing SQL scripts from the GUI, creating the sample DB from a script

Intro to Structured Query Language (SQL): compiled into applications & used by DBAs, declaritive (non-procedural) language, Data Manipulation Language (DML) statements vs. Data Definition Language (DDL) statments, SQL standards, DBMS specific extensions, quoted & non-quoted identifiers, comments, CREATE TABLE command: named constraints & unnmaed constraints, NULL, NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, Literal values for text/numeric/datetime values, operators (> < >= <= >< (used with CHECK constraint), ( ), IN, BETWEEN, AND, OR, NOT); DROP TABLE command; ALTER TABLE command;

Mannino: Chap 3

Fehily: Chaps 3, 11

Week 3

More SQL: INSERT command, UPDATE command, DELETE command.

Intro to SQL SELECT command. SELECT, FROM, WHERE, ORDER BY, AS, DISTINCT, AND/OR/NOT in WHERE, LIKE, IS NULL, IS NOT NULL

SQL operators and functions: concatenation, SUBSTRING(), UPPER(), LOWER(), TRIM(), CAST(), CASE, COALESCE(), NULLIF(), and others

Mannino: Chap 3

Fehily: Chaps 10, 4, 5

Week 4

Summarzing and grouping data: GROUP BY, aggregate functions, using DISTINCT with aggregate functions, COUNT(*) vs. COUNT(expression), HAVING clause.

Joining tables: cartesian product (AKA cross product, AKA cross join), WHERE syntax, SQL-92 JOIN syntax, inner joins, LEFT OUTER, RIGHT OUTER, FULL OUTER

Mannino: 3, 9

Fehily: Chaps 6, 7

Week 5

Subqueries: converting between subqueries and joins, correlated subqueries, IN, ALL, ANY, EXISTS

Set operations: UNION, INTERSECT, EXCEPT

Mannino: 9

Fehily: 8, 9

Week 6

Physical design and Indexes

MIDTERM

Mannino: 8

Fehily: 12

Week 7

Views: creating a view, reasons for using views, querying a view, updating data through a view

Intro to stored procedures

Handouts

Mannino: 10, 11

Fehily: 13

Week 8

More stored procedures

Triggers

Handouts

Mannino: 11

Fehily: N/A

Week 9

Advanced stored procedures

Transactions

Handouts

Mannino: 15

Fehily: 14

Week 10

Database Security

Handouts

Mannino: 14

Fehily: N/A

Week 11

Data warehouse technology and management.

Mannino: 16

Fehily: N/A

Week 12

Client Server Processing and Distributed Databases

Mannino: 17

Fehily: N/A

Week 13

Writing application code to access databases

Handouts

Mannino: N/A

Fehily: N/A

Computer Resources

http://yrosenthal.com

This website, http://yrosenthal.com , will be the primary source for disseminating course related information. This includes the course syllabus, homework assignments and other course related materials.

YU Email (yums.yu.edu - YUMS stand for YU Mail System)

YU provides an email account (AKA ymail account) for every student and faculty member. I will be communicating with you via your yums accounts. You are ABSOLUTELY REQUIRED to check your YU email at least once a day. If you don't want to check your yums accounts regularly then you can forward ymail to any other email account (e.g. to your aol, yahoo or gmail accounts). See instructions on http://yums.yu.edu.

email address

Angel Online Course Management System:

Angel is a web-based course management system in use in many of YU's courses. You WILL need to login to the Angel system in order to submit your HW assignments. However, for the most part, course related information for my courses is posted on http://yrosenthal.com rather than on Angel.

Accessing the Angel website: https://angel.ac.yu.edu/yu/ (You can also get to it by following the link on http://yrosenthal.com or the link in the student section of http://yu.edu)
Angel userid: Your Angel userid is the same as your ymail userid without the @yu.edu (see the ymail section above). For example, if your ymail userid is jdoe@yu.edu then your Angel userid would be jdoe
Password: If you have never logged in before then your password should be the first letter of your last name followed by the last 4 digits in your social security number. If you have used Angel in the past then your password is whatever it was the last time you used Angel. Note: the password is NOT necessarily the same as your ymail password.
Help with Angel: If you have any problems with angel or can't login send an email to angelsupport@yu.edu

YU Network Accounts

Every student and faculty member in YU receives a userid and password that allows them to access the YU network.

Userid: Your YU Network userid is the same as your ymail userid without the @yu.edu (see the "YU Email" section above). For example, if your ymail userid is jdoe@yu.edu then your YU Network userid would be jdoe
Password: If you have never logged in before to the YU network then your password should be the first letter of your last name followed by the last 4 digits in your social security number. If you have used you YU Network account in the past then your password is whatever it was the last time you used Angel. Note: the password is NOT necessarily the same as your ymail password.
Where are the computers?: You can access the YU Network on the Wilf campus from computers in the Gottesman Library and from computers in rooms B1103, B1105 and B1111. On the Beren campus you can use the computers in the public labs or computer equipped classrooms in 245Lex or in 215Lex.
Where can I store my personal files? (MyDocuments, Z: drive, Desktop): Personal files that you want to store on the YU network can be stored in the MyDocuments folder. This folder is synonymous with the root folder on the Z: drive (ie. Z:\). The Z: drive for every user will contain their own files. You cannot access the Z: drive of any other user. You can also store files and information on your computer's "Desktop". However this is NOT recommended (keep reading). You are limited to approximately 25MB of storage space on the Z: drive and a much smaller amount on the Desktop. This IS NOT A LOT OF SPACE. You will need to occasionally "Clean your profile" to remove unneeded files by following the instructions in the "Cleaning your profile" link found on the desktop. If you run out of space, you will not be able to log off your YU Network Account until you free up some space.
Help with YU network accounts: Direct any problems you have with the YU network accounts to the MIS office on the 13th floor of Belfer Hall (room 1315). You can also call the YU MIS helpdesk at (212)960-5294 or email them at helpdesk@yu.edu.
USB drives / Thumb drives / Jump drives
: It is highly recommended that you purchase a "USB" drive (AKA thumb drive or jump drive) to save your files on. You can plug the USB drive into the flat USB port on the computer when you start working. When it is plugged in, the USB drive will appear as a new drive letter under windows. You can remove the USB drive and take it with you when you are done.

Other YU computing services (e.g. accessing the web from your dorm room)

Support for the YU computer systems is handled by two departments (1) the YU MIS department and (2) the YU Academic Computing, Networking and Support Services department. Information for both departments is available at: http://yu.edu/mis/. An all purpose help desk is available (http://www.yu.edu/mis/asp/help_desk.asp). Contact the Help Desk at: (212)960-5294 or e-mail at helpdesk@yu.edu

Database software

We will be using Microsoft SQL Server for in-class work and homework assignments. SQL Server is NOT installed on any of the publicly accessable computers in the library or on the 11th floor. I will have copies of SQL Server next week for you that you can install on your personal machines. Microsoft Access IS available on the public machines. You can do some of the work for the course with Access, but not all of the work. Other DBMSs that you might want to install for yourselves (not required) are MySQL and Oracle Personal Edition. These are both available as free downloads.