Dbms Using Sql and Oracle

BSS

DETAILED SYLLABUS

ORACLE

1.OVERVIEW OF DATABASE MANAGEMENT

Data Information and knowledge, Increasing use of Data as a Corporate Resource, Data Processing versus Data Management, File-Oriented Approach versus Database Oriented Approach to Data Management (Disadvantages of File Oriented Approach), Data Independence, Database Administration Roles, DBMS Architecture, Different Kinds of DBMS Users, Importance of Data Dictionary, Contents of Data Dictionary, Types of Database Languages, Data Models.

2.TRADITIONAL DATA MODEL

ANSI/SPARC 3-level Architecture and the Place of Logical Data Models in this Architecture, A brief overview of the three Traditional Models namely Hierarchical Model, Network Model and Relational Model, Data Definition and Data Manipulation Constructs in each of the three Models with Examples, A comparison of the Three Models.

3.RELATIONAL MODELS

Definition of Relation, Properties of Relational Model (Codd’s 12 Rules or Equivalent), Concepts of Keys: Candidate Key, Primary Key, Alternative Key, Foreign, Fundamental Integrity Rules, Entity Integrity, Referential Integrity, Relational Algebra, Select, Project, Cross Product, Different Types of Joins (Theta Join, Equi-Join, Natural Join, Other Joins), Set Operations, Tuple Relational Calculus, Domain Relation Calculus, Simple and Complex Queries Using Relational Algebra, Stand-Alone and Embedded Query Languages.

4.STRUCTURED QUERY LANGUAGES

SQL Constructs (SELECT...... FROM...... WHERE...... GROUPBY ...... HAVING.... ORDERBY...... ), INSERT, DELETE, UPDATE, VIEW Definition Tables, Temporary Queries, Correlated nested Queries, SQL Standards (SQL ’86, SQL’89, SQL ‘920), Transaction Constraints : Not null, Unique, Check, Primary Key, Foreign Key, References

5.EMBEDDED SQL AND APPLICATION PROGRAMMING INTERFACES

Limitation of SQL in Handling Complex Applications, Programmatic Access to Relational Database, Static Embedded SQL Handling of Nulls, Cursors, Use of SQL/SQLSTATE and Dynamic Embedded SQL, SQLDA, Application Programming Interfaces (API’s), Types of API Calls, Native API’s, Introduction to ODBC (Open Database Connectivity)

6.DATABASE DESIGN

ANSI/SPARC 3-level Architecture, Entity-Relationship Model as a tool for Conceptual Design-Entities Attributes and Relationships, ER Diagram, Converting an E-R Model into Relational Scheme, Normalization Concepts in Logical (Relational) Model, Updates Anomalies, Issues in Physical Security.

7.DESIGN AND DEVELOPMENT OF DATABASE APPLICATION ON COMMERCIAL RDBMS PLATFORMS

Database Design, Applications Design, SQL/Embedded SQL, Trouble shooting Performance and Documentation

8.ORACLE SQL

Introduction to RDBMS, SQL Procedural Vs Non Procedural Languages, DDL, DML, DCL., Data Types in SQL, Create, Alter, Drop Tables, Instert, Update, Delete Tables, Constraints, Column and Table Level Constraints and NULL value Concepts -Entity and Referential integrities, Range, Searching, Patterns, Matching, Built-in Functions,UNION, INTERSECT AND MUNUS CLAUSES,Indexes,Views, Sequences, Report Creation, JOIN Concepts, Permission Granting and Revoking.

9.ORACLE PL/ SQL

Introduction to PL/SQL, Portability, Execution, Environment, Data types, PL/SQL in SQL PLUS, Executing a PL/SQL Block, Syntax, of PL/SQL, Structure of a PL/SQL Block, Comments, Identifiers, Commit, Rollback, Savepoints,Cursor, Locks: Shared Locks, Exclusive Locks - Implicit Locking, Explicit Locking ,Exception Handling.