Oracle and SQL (CIS150)

Professor: Priscilla Grocer

Website: www.pgrocer.net
Email: Priscilla.Grocer@bristolcc.edu.
The class is an online course this semester.

Course Description (from catalog):

This course is an introduction to the Oracle database. The student will learn to work with Oracle and the structured query language SQL as they design, manipulate and access the database. In addition, the concepts and design of relational databases will be analyzed and implemented.

Course Objectives:

The primary objective of this course is to teach the student to work with Oracle both in the development of the database and the use of SQL and PL/SQL. Students who successfully complete Oracle and SQL will be able to:


Text:
There are three texts that I have used and found helpful. These are simply recommendations, not requirements.
Oracle9i: SQL with an Introduction to PL/SQL
Lannes L Morris-Murphy
Course Technology
Oracle Database 11g SQL
Jason Price
Oracle Press
Oracle 12c: SQL 3rd edition
Joan Casteel
Cengage
You can choose to download Oracle for use on your computer or use the school's version. We are using Oracle 19c.

Material to be Covered: The order in the syllabus does not necessary reflect the order that will used in the course. Please use the weekly schedule sheet to follow the assigned topics.

  1. Introduction/concepts
    1. Relational database
    2. Normalization
    3. Entity Relationship model
    4. Object orientation
    5. Introduction to Oracle
    6. Introduction to SQL
    1. SQL statements in Oracle
      1. Basic SQL statements
        1. Selecting
        2. Arithmetic expressions
        3. Concatenating
      2. Introduction to SQL
      3. Limiting and Sorting data
      4. Functions
      5. Using multiple tables
      6. Grouping functions
      7. Simple subqueries
      8. Multiple column subqueries
    1. Additional features
      1. Output
      2. Updating - add, changes and deletions
      3. Manipulating data
      4. Creating and maintaining tables
      5. Keys
      6. Views
      7. Database objects and indexing
    1. More advanced features of Oracle and SQL
      1. Controlling access
      2. PL/SQL structure
      3. Variables
      4. Writing executable code
      5. SQL and PL/SQL
      6. Control structures
        1. If
        2. For loop
        3. While loop
      7. Composite data types
      8. Working with cursors
      9. Processing with multiple cursors
      10. Exceptions
      11. Procedures and functions
      12. Triggers


    Requirements:

    Requirements and Grading:
    The student will be expected to complete all assigned work. A final exam or project may be included as part of homework, programs, code, tests and quizzes and would count for 10%. Most assignments/program/codes and tests/quizzes will count approximately 4%/5% (some will count more and some will count less depending on complexity).
    A schedule of assignments, projects, exams, quizzes etc. will be posted on the Web site in a weekly chart at the beginning of each week. Students should check on a regular basis. During a week, additions and changes to the week of chart should be anticipated.

    Grading:

    Your committment: For each hour in class, you should plan to put in 2 to 3 hours outside of class so you need to recognize that committment and include it in your planning.
    Below there is a TENATIVE schedule of homework based on previous semesters that will give you an idea of what you need to accomplish to be successful in the class. You are also required to read the chapters in the book and do outside research as assigned.

    Week #1 Introduction Oracle assignment        Week #2 Second Oracle assignment, quiz
    Week #3 Oracle quiz, function assignment        Week #4 Oracle quiz, multiple tables and subqueries
    Week #5 subquiz        Week #6 Views and reports in Oracle
    Week #7 Oracle keys assignment        Week #8 Oracle PL/SQL Intro quiz, PL/SQL decision assignment
    Week #9 Loop assignment, quiz        Week #10 scripts and programs in PL/SQL
    Week #11 PL/SQL problems to write and execute        Week #12 Procedure, function assignment
    Week #13 Trigger assignment        Week #14 Project
    Week #15 Wrapup       

    It is very important that you let me know if you are having problems so we can work out a plan. Please take advantage of my availability and don't let yourself get behind! I truly want to see you succeed!

    Evaluation:

    Assignments, programs and exams are graded using either number grades or letter grades based on the following (A=90-100, B=80-89, C=70-79, D=60-69, F=below 60). The student's grade for the course will use the same scale and will be based on the percentages explained in the grading section. Plus and minus grades will be given.
    Many of the assignments in this class are open ended - the grading will be based on how well the project demonstrates mastery of the material. Students who do a minimum of work will be graded accordingly. Assignments are only accepted if they are credible work and meet the minimum requirements and standards for that assignment. Assignments that are not accepted can be resubmitted. Resubmission is allowed on graded assignments, with permission of the instructor. You cannot earn an A+ on an assignment that is resubmitted. The instructor will only accept, without penalty, resubmissions on credible work. Resubmissions must be done within a week to avoid additional penalties for late assignments.
    Note: To achieve an A+, on open-ended assignments, students must have done sufficient extra work in development or implementation tot make the assignment standout. In doing assignments, students must do their own work. Relying too heavily on my examples or working too closely with someone else will be penalized.
    Assignments are due the week after they are assigned. Late assignments will be penalized. The 10% of the grade that deals with responsibility and involvement will loose points from late assignments. For purposes of this class, the week will end at midnight on Thursday.

    Attendance: Attendance is based on email communication. Students must report their status once a week. This report can be combined with the submission of an assignment.

    Methodology:

    This course is taught on line with a variety of techniques. The weekly schedule will explain what should be accomplished each week and give assignments. To help learning there are extensive notes with examples, Powerpoint presentations, topic videos, lectures (audio and Smartboard) from the last time the course was offered in class (Spring 2020 and Fall 2019). The instructor is available via email. Optional on line meetings (Zoom) will be held periodically to explain difficult concepts and to answer questions. Their will be multiple times to allow the student choice if they want to attend. These will be posted on the weekly schedule.
    All students should read the assigned notes, study the presentations available and avail themselves of other resources at the web site in mastering the course material. In addition, students will be working independently on assignments and projects designed to give them additional computer skills and practical experience in analyzing and solving problems. When appropriate, exercises and problem solving techniques are used. This syllabus is not to be construed as a contract in any way, shape, manner or form. This syllabus contains a suggested course outline and will be generally followed, subject to change according to the instructor's discretion and needs. Academic flexibility is important!

    Bristol Community College Withdrawal Policy:
    Students are responsible for withdrawing officially if they stop attending any or all classes. Faculty no longer have the ability to withdraw a student from a class. A grade of "F" will be assigned to any student who stops attending a course but does not officially withdraw. Students are encouraged to meet with an advisor before making any changes to their schedule. Withdrawals effect Satisfactory Academic Progress and can place the student at risk for academic probation or dismissal. Students who use financial aid and who subsequently withdraw may be required to return some or all funds received. Withdrawals are accepted until the tenth week of classes. Students may withdraw online in accessBCC, in person at any Enrollment Center, or via their college email to enrollmentservices@bristolcc.edu. Email requests must come from the student's BCC college email address and must include the student's name, BCC student ID number, and course information (CRN, course and section number). Email from non-college accounts will not be accepted. If a student officially withdraws after the third week of classes, there will be no tuition or college fee refunds. For more information, see the College Catalog at: http://bristolcc.smartcatalogiq.com/en/2017-2018/Catalog/Academic-Information/Withdrawal-Policy-and-Procedure. Students with questions should contact Enrollment Services via any of the methods mentioned above or at 774-357-2590.

    Disability Services Disability Accommodations: Bristol Community College complies with federal legislation for individuals with disabilities (Section 504 of the Rehabilitation Act of 1973 and the Americans With Disabilities Act of 1990 and the ADAA of 2009) and offers reasonable accommodations to qualified students with disabilities. It is your responsibility to notify me and the Office of Disability Services (ODS) of your need for classroom accommodations. Accommodations are arranged through ODS, which will issue a confidential Disability Services Accommodation Plan signed by both the Learning Specialist and student. This should be accomplished, when possible, during the first two weeks of class. If you have questions about the process, please contact ODS by calling (508) 678-2811 (Fall River, ext. 2955; Attleboro and Taunton, ext. 2996; New Bedford, ext. 2955 and/or 4011). You may also contact Office of Disability Services (ODS) online at http://www.bristolcc.edu/students/disabilityservices/
    If you have a documented disability and will be requiring accommodations, please contact me and the Office of Disability Services (508) 678-2811 (Fall River, ext. 2955; Attleboro and Taunton, ext. 2996; New Bedford, ext. 2955 and/or 4011) as soon as possible to arrange for appropriate accommodations. You may also contact Office of Disability Services (ODS) online at http://www.bristolcc.edu/students/disabilityservices/
    Any student with a documented disability in need of academic adjustments or accommodations is requested to speak with me and the Office of Disability Services (508) 678-2811 (Fall River, ext. 2955; Attleboro and Taunton, ext. 2996; New Bedford, ext. 2955 and/or 4011) as soon as possible to arrange for appropriate accommodations. You may also contact the Office of Disability Services (ODS) online at http://www.bristolcc.edu/students/disabilityservices/
    I encourage any student in need of accommodations for a specific documented disability to meet with me and the Office of Disability Services (508) 678-2811 (Fall River, ext. 2955; Attleboro and Taunton, ext. 2996; New Bedford, ext. 2955 and/or 4011) at your earliest convenience to ensure timely and appropriate accommodations. You may also contact the Office of Disability Services (ODS) online at http://www.bristolcc.edu/students/disabilityservices/
    For Online Courses: If you are a student who would normally seek accommodations in a traditional, face to face classroom, please speak to me and the Office of Disability Services (ODS) as soon as possible. You may contact the Office of Disability Services to arrange for appropriate accommodations by calling (508) 678-2811 (Fall River, ext. 2955; Attleboro and Taunton, ext. 2996; New Bedford, ext.2955 and/or 4011) or by stopping by Room L109. You may also contact the Office of Disability online at http://www.bristolcc.edu/students/disabilityservices/

    Interaction Plan:
    This class is offered online. This is an asynchronous course with communication via email recorded audio and video notes, online meetings and discussions. Office hours and help sessions are offered online.. Other methods of online communications may also be used. In addition open lab help session are available and students may request in person meetings. Students must communicate with the instructor at least once a week via email.