Coursepack scheme

Download 299.95 Kb.

Page	2/7
Date	18.01.2024
Size	299.95 Kb.
	#63232

1 2 3 4 5 6 7

DBMS Course pack 28-10-23
Java Unit 2 Notes

COURSE ASSESSMENT
COURSE CONTENT THEORY

PO12	PSO1
PSO2
E2UC302B.1	2	1	2	2	1
E2UC302B.2	2	1	2	2	1
E2UC302B.3	2	1	2	2	1
E2UC302B.4	2	2	2	2	1

Note: 1-Low, 2-Medium, 3-High

COURSE ASSESSMENT

The course assessment patterns are the assessment tools used both in formative and summative examinations.

S.No.	Assessment Tools	CIE								Total CIE marks		SEE
		QUIZ1 /AAT	MTE	QUIZ2/AAT	CAT	LAB	LAB EXAM	Course-based Project
1	Blended	0	50	0	0	25	25	0	100		100

* Assignment, Quiz, Class test

COURSE CONTENT THEORY

Introduction

Introduction: An overview of the database management system, database system Vs file system, Database system concept and architecture, data model schema and instances, data independence and database language and interfaces, data definitions language, DML, and Overall Database Structure.

Relational Data Model and Language

Relational data model concepts, integrity constraints, entity integrity, referential integrity, Key constraints, Domain constraints, relational algebra, relational calculus, tuple, and domain calculus. Introduction to SQL: Characteristics of SQL, the advantage of SQL. SQL data type and literals. Types of SQL commands. SQL operators and their procedures. Tables, views, and indexes. Queries and subqueries. Aggregate functions. Insert, update, and delete operations, Joins, Unions, Intersection, Minus, Cursors, Triggers, and Procedures in SQL/PL SQL

Database Design & Normalization

Functional dependencies, normal forms, first, second, and third normal forms, BCNF, inclusion dependence, lossless join decompositions, normalization using FD, MVD, and JDs, and alternative approaches to database design.

Transaction Processing Concept

Transaction system, testing of serializability, serializability of schedules, conflict & view serializable schedule, recoverability, Recovery from transaction failures, log-based recovery, checkpoints, deadlock handling. Distributed Database: distributed data storage, concurrency control, directory system.

Concurrency Control Techniques

Concurrency control, Locking Techniques for concurrency control, Time stamping protocols for concurrency control, validation-based protocol, multiple granularities, multi-version schemes, Recovery with concurrent transactions, MySQL databases, NoSQL databases, Recent trends in domain.

PRACTICAL

Content
Draw an E-R diagram and convert entities and relationships to a relation table for a given scenario.
(Two assignments shall be carried out i.e., consider two different scenarios (e.g., bank, college)
.
Perform the following on MySQL:
a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating simple tables
c. Inserting/Updating/Deleting Records in Table
d. Saving (Commit and Undoing (rollback)
Perform the following:
a. Altering a Table, Dropping/Truncating/Renaming Tables.
b. Adding a column, Changing column data type, size
c. Dropping a column
d. Creating Tables (With and Without Constraints (Key/Domain)
e. Creating Tables (With Referential Integrity Constraints)
For a given set of relation schemes, create tables and perform the following Queries:
a. Simple Queries with Aggregate Functions
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions
For a given set of relation schemes, create tables and perform the following Queries:

Inner Join
Outer Join Subqueries- With IN clause, With EXISTS clause

For a given set of related tables perform the following: -
a. Creating Views
b. Dropping views
c. Selecting from a view
Write a Pl/SQL program using a FOR loop to insert ten rows into a database table.

Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a cursor to select the five highest-paid

employees from the table.

For a given set of related tables perform the following:

a. Begin Transactions
b. End Transaction
For a given set of related tables perform the following:
a. Create roles
b. Assign Privileges
c. Revoke Privileges

Demonstrate the working of different types of Triggers.

Using the Xampp server package, create usernames/passwords & connect it to MySQL community edition.

Download 299.95 Kb.

Share with your friends:

1 2 3 4 5 6 7