Prof. Yitz Rosenthal (click here for my schedule)
(All contents of this website are copyright © 2009)best viewed with
Microsoft Internet Explorer 7.0 or later
Microsoft Internet Explorer 7.0 or later
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.
Homeworks must be submitted via the Angel system BEFORE the next class. I will either review the homework or provide a solution the next class. For most homeworks I will not be strict about your getting all homework problems correct. I want to see that you have attaempted the solution and have posted an answer that shows me that you tried. For these homeworks you will get full credit if you post a reasonable attempt at an answer. You will get no credit if your answer is copied or shows that you didn't even try. There may be some homeworks that I will grade on a point system. I will let you know which ones those will be when they are assigned.
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.
| Date | Topics |
|---|---|
| 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 |
| 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; |
| 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 |
| 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 |
| Week 5 |
Subqueries: converting between subqueries and joins, correlated subqueries, IN, ALL, ANY, EXISTS Set operations: UNION, INTERSECT, EXCEPT |
| Week 6 |
Physical design and Indexes MIDTERM |
| Week 7 |
Views: creating a view, reasons for using views, querying a view, updating data through a view Intro to stored procedures |
| Week 8 |
More stored procedures Triggers |
| Week 9 |
Advanced stored procedures Transactions |
| Week 10 |
Database Security |
| Week 11 |
Data warehouse technology and management. |
| Week 12 |
Client Server Processing and Distributed Databases |
| Week 13 |
Writing application code to access databases |
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.