Syllabus, Spring 2004 CIS 141 4.0 Units Section 4971 M-W 4:00 PM – 5:30 PM Instructor Lab/Office Hours Monday and Wednesday, 3:00-4:00PM, 6:30-7:30 PM Lecture: Room 4502 A Lab: Room 4502 B Instructor: Terry Brown Best way to contact instructor: terryb2000@charter.net Overview This course is designed to enhance the skill set of: People whose primary jobs is the collection, manipulation, and dissemination of electronic information to action takers and to other tasked based individuals. Those with rudimentary database skills who desire to master advanced database manipulation skills. Those wishing to pass the Microsoft 70-229 exam for an MCP or towards the MCDBA. The course is geared towards the practical: sound database planning and construction, solving real-life database issues, cleaning data, and presenting data to the user via web browsers are some of the issues covered, alonmg with a strong emphasis on query construction, and use of the SQL Server 2000 Enterprise Manager and its associated tools to manage the database. Prerequisites/Advisories This course requires a minimum base competency that includes functional knowledge of Access 2000, and fundamentals of database management, and ability to create simple SELECT statements. Completion of CIS 121, or CAOA 64 is helpful. See instructor if you are unsure. Required Materials A Guide to SQL Sixth Edition, Philip J. Pratt, Thomson Course Technology, ISBN 0-619-15957-X. If you intend to do the labs at home, you will also need Microsoft SQL Server 2000 Enterprise Edition SP-3 regular or free 120 day version. You will also need Microsoft NT 4.0, 2000, or 2003 Server edition operating system regular edition or free 120 day trial software with all patches kept up to date throughout the course, and appropriate hardware to run them on. Although the textbook is aimed toward Access and Oracle 9x SQL, the course is aimed toward SQL server and Access XP. You are discouraged from using Access 97 or earlier for technical reasons. NOTE: ORACLE WILL NOT BE COVERED IN THIS COURSE. The book is available at the Cuesta College Bookstore, Barnes & Noble and Amazon, and other booksellers. The above link(s) will take you to a site that is not part of Cuesta College, and Cuesta College has no control over the content or availability of the site(s) blah blah blah. All comments regarding the content of personal homepages should be directed to the page authors. Outcomes After successfully completing this class, the student will be able to, among other things: Describe a series of steps to determine the desired outcomes of a database model. Explain why using normalization is a desired quality of a database and how it improves accuracy. Optimize database construction by using normalization. Cite specific reasons not to normalize a database. Enumerate the desired qualities of database object names. Construct a relational database that contains primary and foreign keys that are logically sound and reflect the actual data and processes being modeled. Describe the flow of information from a table to a finished report or form, and what processing takes place at each object. Understand the three kinds of SQL, DCL, DML, and DDL Employ the use of select queries to join data from more than one table, filter the results, aggregate results, and use parameters to filter results. Construct subqueries. Construct make table, union, delete, top x, distinct, and update queries. Use basic keywords in queries: WHERE, DISTINCT, BETWEEN, DISTINCTROW, HAVING, IN, LIKE, LIKE DUDE, ORDER, GROUP BY, et al. Describe the SQL Server physical & logical architecture Demonstrate proficiency in using Enterprise manager in: - Attaching to a SQL Server - Creating, modifying and dropping databases - Construct and execute queries in Query manager - Use roles in database security - Construct, debug, and execute DTS packages - Use bulk insert tools Find answers to previously unasked questions in Books Online. Understand the use of and construction of pre-made and user defined triggers. Understand the use of and construction of pre-made and user defined stored procedures to manage SQL server, and perform repeated tasks. Demonstrate building and connecting to an ODBC compliant database. Construct web forms containing advanced and Active X controls. Develop a report that sorts, aggregates, and groups information in a usable, accurate manner. Insert parameters from underlying queries into reports. Use the grouping and sorting feature to both aggregate data, and hide irrelevant detail in executive reports. Construct simple VBA procedures. Attendance This is an eighteen week class with much to learn at each session. Since there are limited lab facilities and some who may desire to add, you will be expected to be in attendance for the first three weeks. Failure to attend lecture and lab during this time may result in being dropped from the course. Your computer MUST be left in serviceable condition, operating normally when you leave for the day so the next person using will have a working system. Although not otherwise required, you are encouraged to attend each class. Every effort will be made to make your attendance a useful learning experience. Bear in mind that the lab work is builds on work from prior weeks – often a lab will require software built in prior labs! Grading Class Projects 150 points Homework. Think of it as open book tests! (The lowest will be dropped) 350 3 Mid Term Tests @ 100 points each 300 Last Test 200 Letter Grade Grade Requirement Mastery Level A 900-1000 pts Send me your resume. You should apply for work at UPS. B 800-899 Good Understanding of SQL. C 700-799 Demonstrable if incomplete knowledge of SQL; proficient user level. D 600-699 So what went wrong? F 0-599 You should apply for work at Fed Ex. Academic Honesty Please review the Fall 2003 Class Schedule section on academic honesty.  Cheating and stealing are not tolerated and will be dealt with severely. Possible consequences for cheating include failure of the test or assignment, failing the course, or expulsion. Theft will always result in failing the course and referral to law enforcement for prosecution. Make-up / Late Work Allowed only for very unusual circumstances. If allowed, assigned homework, loses 10% of its value for each class day missed. No credit if over seven calendar days. Missed in-class lab quizzes cannot be made up. Drops The student is responsible for dropping the class. The forms are available at Admissions. Homework/Lab Deliverables There will be a number of homework assignments and lab deliverables. They need to be e-mailed to me at terryb2000@charter.net. The subject line must read "HW141xx" where xx is the number of the assignment. Please, not "home work xx" or some other variation. This is not because I have a controlling personality or am inherently mean, but my mail client is; it sorts it into the proper folder for me. Thank you for your thoughtfulness. Also, you will lose 10 points if it doesn't. BaseHead Links MS Knowledgebase Virtually every Microsoft product problem Microsoft has ever addressed has a reference here. SSWUG Requires registration, but worth it.  mssqlserver.com Turns up a lot in Google. sqlsecurity.com Clearinghouse for security issues in db         Return to Cuesta Home PageContact Information Page Last Modified January 12, 2003 Copyright (c) 2004 Terry Brown. All Rights Reserved.