Advanced DBMS outline

Advanced Database Management System

Department of Computer Science and I.T.

Spring 2018 Morning

 

 

 

 PROGRAM (S) TO BE EVALUATED: BSCS-IV

A. Course Description

Course Code	CSC405
Course Title	Advanced Database Management Systems
Credit Hours	03
Prerequisites by Course(s) and Topics	Conceptual Database Designing, Computer Programming
Assessment Instruments with Weights (Homework, quizzes, midterm, final, programming assignments, lab work, etc.)	Quizzes + Assignments + Project = 20% Mid Term Paper = 30% Final Term Paper = 50%
Course coordinator
URL (if any)
Current Catalog Description
Textbook (or Laboratory Manual for Laboratory Courses)	Learning MySQL Book by Seyed M. M. Tahaghoghi
Reference Material	MySQL Cookbook, by Paul DuBois. MYSQL in a Nutshell, by Russell Dyer.  Additional course notes and reading material will be given.
Course Goals	The course aims to introduce advanced database concepts, different data models, data storage and retrieval techniques and database design techniques

 

 

 

 

Topics Covered in the Course, with Number of Lecturer on Each Topic (assume 15-week instruction and one-hour lecture.
Week No.	Class Topic
1	Review of Basic Concepts (Database Development, Database Administration, Application Program Development, Schema Objects, SQL, MySQL)
2	transaction and query, different  Data types ENUM  SET, Text and Varchar, Aggregate functions
3	Create Table, Alter Table, Drop Table, Applying and Removing Constraints (Primary Key, Unique, Not Null, Foreign Key, Check), Default Values for Columns
4	Subqueries, Type of subqueries, Single row operator, multi row operator, Problem with subqueries, Null Values in Subqueries.
5	Creating Simple Views, Read Only Option, With Check Option, Complex Views, Creating and Dropping Indexes, Exploring the Data Dictionary Views "USER_VIEWS" and "USER_IND_COLUMNS"
6	, Viewing List of Tables in the Schema, Describing a Table, Exploring the Data Dictionary Views USER_TAB_COLUMNS, USER_CONS_COLUMNS, USER_CONSTRAINTS
7	(input, output, if-else, while, for), Creating Functions and Exceptions
8	Creating and Calling Procedures
9	Creating Triggers, Row-level vs Statement-level Triggers, Before vs After Triggers
10	Creating and using Cursors
11	MySQL Scheduled Event, creating event dropping event.
12	Creating a new Database, Running, Sys and System Users, Create a new user, CONNECT, RESOURCE and DBA Roles, Data Dictionary Views (DBA_USERS, DBA_ROLES),
13	Granting and revoking privileges, System vs Object Privileges, Data dictionary views DBA_SYS_PRIVS and DBA_TAB_PRIVS
14	Database Tuning and Performance, Database Backup and Recovery, Importing/Exporting Tables and Data
15	Spooling your SQL work, Executing SQL command stored in a file, Configuring the MySQL,
16	MySql Instance Management, Disk Space Management, MySql Database and Web Security, Introduction to the Advanced Concepts (Data Warehousing, Data Mining, Distributed Databases)