INFO 365 – Database Administration I 

 

Instructor

Dr. Xiaohua Tony  Hu

Office

3401Market Street, suite 300 , room 325    

Class Time:                                  Tuesday 18:30-21:20pm

Location:                                      RUSH 006

Office Hour:                                 Tuesday 17:20-18:20pm

Phone

215-8950551 

Email Id

Xh29@drexel.edu

Home page

http://www.cci.drexel.edu/faculty/thu/

Course page

http://www.cis.drexel.edu/faculty/thu/Teaching/INFO365/info365.htm

 

1. Course Description

The purpose of this course is to teach advanced issues in SQL database development, not database administration (that's covered in ISYS366).  This includes advanced ERD techniques, database management system internals and advanced elements of the SQL language, as well as stored procedures and triggers, specifically as demonstrated in the Oracle implementation.

 

2. Course Exit Competencies

Upon completion of the course, the student should be able to:

1)      Create stored procedures, stored functions and triggers in an RDBMS.

2)      Describe the internals of an RDBMS.

3)      Identify database requirements and model them in an ERD.

 

3. Prerequisites

 INFO 210 Database Management Systems, CS 133 or CS 172

 

 

4. Required Textbook:

Title: Oracle Database 10g PL/SQL Programming

Authors: Scott Urman, Ron Hardman, Michael McLaughlin

Or

         Oracle Database 11g PL/SQL Programming

Authors: Michael McLaughlin

 

 

Recommended Textbook:

Title: Oracle Database 11g The Complete Reference

Authors: Kevin Loney

Publisher: McGraw-Hill (Osborne)

ISBN: 0071598758 / 9780071598750

 

Title: Oracle PL/SQL Language Pocket Reference (3rd Edition)

Author: Steven Feuerstein, Bill Pribyl, Chip Dawes

Publisher: O’Reilly & Associates

ISBN: 0-59600-680-2

 

5. Oracle ID

Request an Oracle ID at https://linux.ischool.drexel.edu/signup/index.pl

 

 

6. Course Project

Students will be required to do a course project. You could choose to continue working on your INFO 210 project to save time in doing the ERD etc. For this course, the project will consist of creating triggers, procedures and functions for your Oracle Database. Or you can begin a brand new project.

 

You are required to create four working triggers, procedures or functions (mix-n-match) that demonstrates your competency of the course matter.

 

Project Proposals are due by week 3 and the project itself is due on or before the final exam week (week 11)

 

7. Exams, Assignments and Grading

There will be two exams as per the schedule noted. And there will be no makeup exams. No Exceptions. There will be two PL/SQL programming assignments.

 

Break-Up

Scale

 

Exams (mid: 15; Final 30)

45%

A  category

A+: 97 – 100% , A: 93-96%, A-: 90-92%

Final Project

35%

B category

B+: 87 – 89%, B: 83-86%, B-: 80-82%

C category

C+: 77 – 79% , C: 73-76%, C-: 70-72%

Assignments

  20%

D category

D+: 67 – 69%, D: 63-66%, D-: 60-62%

 

 

F category

< 60%

 

 

8. Plagiarism Policy

Plagiarism is the representation of another’s words, ideas or work as your own. The standards for academic honesty are described in Student Handbook and on Drexel website. All students are responsible for reading and understanding these rules. Do not show/pass either soft copy or hard copy of assignments. Once caught, both parties will suffer.

For this course, all assignments are individual efforts and only the project is a joint effort venture.

All submissions should be original work, any exchange, reuse or direct submission of other’s work (documents, code etc) as your own or as your team’s is Plagiarism.

For this course, students found guilty of plagiarism can expect one or more or all of -

(a)  Incident being reported to the University Judicial Office, where a permanent record is maintained, and

(b)  Fail the course.

For group project’s all the team members are equally responsible to observe this policy, failure to do will lead to the whole team receiving an ‘F’, if such an instance is detected or suspected.

 

9. Noting the one-week Add/Drop period with withdrawal beginning in week 2 .

Related policy links are:

 

10. Weekly Schedule

Week

Tentative Weekly Topics & Assignments

Milestones

Reference

1

Course Overview, 210 Recap, Review of ERD & SQL

 

INFO 210 textbook

2

Introduction to PL/SQL

Project Proposal Format.

 

3

PL/SQL Basics

Proposal due

4

Records and Tables, Cursors

 

5

SQL in PL/SQL, Built-in SQL Functions

6

Built in SQL Functions, Procedures & Functions

Mid-term Exam

7

Triggers,

Handout: Project Report Template.

 

8

Packages and Triggers, Error Handling

 

9

Functional Dependencies

 

10

Normalization and De-Normalization, Final Exam Review

11

Finals Week

Project Due

 

 


Project Report Structure

----------------------------------------------COVER PAGE ---------------------------------

Project Name

Your Name (with Univ Id)

Table of Contents (with page numbers)

Statement of Purpose (or Executive Summary)

System Analysis

(Discussion of the current system, manual or automated)

Project Requirements

What are you the requirements that you intend to address in this active database

Project Scope

In Scope details

Out of Scope details

Database Design

ERD diagram

Brief description of Entities & their relations

Brief description of an ‘Data Integrity’ issues i.e. The Business Rules implemented in the Database

Features of the active database

Brief description of each Procedure/Function

Functionality

Business Rules they implement

Tables they are based on

How to use it (syntax, parameter list etc)

Brief description of triggers

Functionality

Business Rules they implement

When are they used

Cautionary notes, if any

Work Breakdown

Table that shows who’s the author of the SP/SF/Trigger/SQL scripts

Appendix A

SQL code (Procedures, Functions, Triggers etc)

-------------------------------------------------------------------------------------------------------------------------------

Project Deliverables – A Checklist

1)      Project Report in above format

2)      A zipped file  with the project code:

a.      Get Familiar with the ‘project pieces’

1.      A README.TXT which talks the user through the Setup, Testing and Cleanup Phase.

b.      Set up Phase

2.      One file with CREATE TABLE statements, called CREATE.SQL.

3.      One file with INSERT statements, called INSERT.SQL.

4.      One file with all the CREATE PROCEDURE/FUNCTION/TRIGGER statements, called Create_Procs.SQL.

c.       Testing Phase

5.      One or more SQL script files called TESTnn.SQL which help do the testing of your project. (Use SQL*Plus for this)

6.      It should include User Prompts and Pauses indicative of how the project is progressing. (brief comprehensive messages score more than long meandering narratives)

7.      All output should be ‘SPOOLed’ to an output file, which can be perused offline to determine what happened step-by-step in the project.

8.      If you can provide a script file that can do all of the above (and still not confuse the tester/user), then call the file as 'AUTORUN.SQL'. If not then make the instructions in the 'README.TXT' very explicit.

d.      Clean-Up Phase

9.      Only one DELETE.SQL script files that will DELETE all the database objects (tables, SP, SF, Triggers, or View) created above.  (Remember View’s are not dropped with the DROP TABLE… CASCADE….. command)

 

Tips to Success:

1)      Spelling, grammar, etc. counts.

2)      Well written ReadME.Txt, which does not confuse the user,

3)      Project paper must be paginated.

4)      Not following this structure COMPLETELY will result in the loss of one grade.

5)      Softcopy of this structure is available on the CRCSERV5 folder.

6)      Triggers named appropriately (_BIUR, _AIDUR, _ADS, _BIDS, etc),

7)      TESTnn.SQL file works as detailed in ReadMe.txt,

8)      Brief comprehensive output messages from the Tester files will score more than long meandering narratives,

9)      Testing Script works as detailed in either the ReadMe.txt or in the TestInstructions.txt file,

10)  Cleanup phase leaves the user session truly ‘cleaned’ out for next group’s testing.