GTUT DIPLOMA Relational Database Management System GTU 21 Course ( III - CE - 4330702)  (Paperback, MRS. ANURADHA A. PUNTAMBEKAR)

Unit Unit Outcomes (UOs) (4 to 6 UOs at Application and above level) Topics and Sub-topics Unit - I Introduction to Database System and SQL commands 1a. Differentiate the terms : Data, Information, Records, Fields, Metadata, Data warehouse, Data dictionary. 1.1 Concepts and Definitions : Database and database systems and database environment. 1.2 Data, Information, Data Item or Fields, Records, Files, Metadata, Data dictionary and it’s components, 1.3 Schemas, Sub-schemas, and Instances. 1b. DBMS Data types, Creating Tables (DDL), Managing Tables (DML) with SQL, 1.4 Data types 1.5 Database Language commands : Data Definition Language (DDL) : CREATE, ALTER, TRUNCATE, DROP. 1.6 Database Language : Data Manipulation Language (DML) : INSERT, SELECT, UPDATE, DELETE. 1c. Describe & practice Transaction Control Data Control Language. 1.7 Transactional Control : Commit, Save point, Rollback 1.8 DCL Commands : Grant and Revoke. (Chapter - 1) Unit - II SQL In built functions and Joins 2a. Execute various SQL operators and Functions. 2.1 Operators Arithmetic, Comparison, Logical SQL functions- Single row function. i. Single row function. ii. Date functions (add-months, months-between, round, truncate). iii. Numeric Functions (abs, power, mod, round, trunc, sqrt). iv. Character Fucntions (initcap, lower, upper, ltrim, rtrim, replace, substring, instr). v. Conversion Functions (to-char, to-date, to-number). 2b. Perform queries on ‘Group by’, ‘Having’ and ‘Order by’ clause. 2.2 Groupby, Having and Order by clause. 2c. Implement ‘Joins’ 2.3 Joins : Simple, Equi-join, Non-equi, Self-Joins, Outer-joins. 2d. Perform different types of sub queries. 2.4 Subqueries - Multiple, Correlated. 2.5 Implementation of Queries using SQL Set operators : Union, union all, Intersect, Minus. (Chapter - 2) Unit - III Database Integrity Constraints & Objects 3a. Describe with examples Domain Integrity and Entity Integrity constraint. 3.1 Domain Integrity constraints : Not null, Check. 3.2 Entity Integrity constraints : Unique, Primary key. 3b. Describe with examples Referential Integrity constraint. 3.3 Referential Integrity constraints : Foreign key, referenced key, on delete cascade. 3c. Database Objects 3.4 Views - Create, Alter, Drop views 3.5 Synonym : Create, Drop synonym 3.6 Sequences : Create, alter, Drop sequences 3.7 Index : Unique and composite - Create, Drop. (Chapter - 3) Unit - IV PL/ SQL and Triggers 4a. Describe the fundamentals of the PL/SQL programming language. 4.1 Basics of PL / SQL 4.1.1 Data types 4.2 Advantages of PL/SQL over SQL. 4b. Use different Control Structures 4.3 Control Structures: Conditional, Iterative, Sequential 4c. Write and execute PL/SQL programs in SQL*Plus. 4d. Describe & Implement Concepts of exception handling. 4.4 Exceptions : Predefined Exceptions, User defined exceptions. 4e. Implement cursor, procedure and function in Package. 4.5 Cursors : Static (Implicit & Explicit), Dynamic. 4.6 Procedures & Functions. 4f. Describe the various types of triggers. 4.7 Fundamentals of Database Triggers. 4g. Write, code, test and debug various types of triggers. 4.8 Creating Triggers. 4.9 Types of Triggers : Before, after for each row, for each statement. (Chapter - 4) Unit - V Normalization 5a. Describe different Normal Forms. 5.1 Basics of Normalization. 5b. Solve problems of normalization. 5.2 Normal Forms 5.2.1 First Normal Form(1NF) 5.2.2 Second Normal Form(2NF) 5.2.3 Third Normal Form(3NF) 5c. Describe advantages and disadvantages of Normalization. 5.3 Advantages and disadvantages of Normalization. (Chapter - 5)