GUJARAT TECHNOLOGICAL UNIVERSITY (GTU)

Competency-focused Outcome-based Green Curriculum-2021 (COGC-2021) Semester -III

Course Title: Database Management System

(Course Code: 1333204)

Diploma programme in which this course is offered	Semester in which offered
Information and Communication Technology	3rd Semester

1. RATIONALE

The aim of this course is to get understanding of the basic concepts of database management system used for business, scientific and engineering application which stored centralized. The students will develop the skills to design and develop database using different database models manage & retrieve data from different perspective using Structured Query Language (SQL) in ORACLE (centralized storage) so there is no need of storing data in files and paper. This helps students to design ER-models to represent simple databases and convert them into relational tables, populate relational databases and formulate SQL queries on data. Students will improve database design through normalization. Students will understand how database systems must provide for the safety of the stored information, despite system crashes or attempts at unauthorized access.

2. COMPETENCY

The course should be taught and implemented with the aim to develop various types of skills so that students are able to acquire following competency:

Design and develop database for given applications and manage them through different Structured Query Language (SQL) operations in Oracle.

3. COURSE OUTCOMES (COs)

The practical exercises, the underpinning knowledge and the relevant soft skills associated with this competency are to be developed in the student to display the following COs:

a. Understand the fundamental concepts of database systems
b. Design database using Entity Relationship approach
c. Apply SQL commands to create, modify, and manipulate databases.
d. Apply concepts of normalization to design an optimal database.
e. Explain transaction management concepts for concurrent use of database.
f. Explain transaction management concepts for concurrent use of database.

4. TEACHING AND EXAMINATION SCHEME

Teaching Scheme (Hours)			Total Credits	Examination Scheme (Marks)				Total
L	T	P	(L+T/2+P/2)	Theory CA	Theory ESE	Practical CA	Practical ESE	Total Marks
3	0	2	4	30*	70	25	25	150

(*): Out of 30 marks under the theory CA, 10 marks are for assessment of the micro-project to facilitate integration of COs and the remaining 20 marks is the average of 2 tests to be taken during the semester for the assessing the attainment of the cognitive domain UOs required for the attainment of the COs .

Legends: L -Lecture; T - Tutorial/Teacher Guided Theory Practice; P -Practical; C - Credit, CA -Continuous Assessment; ESE -End Semester Examination.

5. SUGGESTED PRACTICAL EXERCISES

The following practical outcomes (PrOs) that are the sub-components of the COs. Some of the PrOs marked '*' are compulsory, as they are crucial for that particular CO at the 'Precision Level' of Dave's Taxonomy related to 'Psychomotor Domain' .

Sr. No.	Practical Outcomes (PrOs)	Unit No.	Approx. Hrs. Required
1	Prepare a report on current database trends, architecture and tools.	1	2
2	Draw ER-Diagram for Library Management System and convert it to relational schema	2	2
3	Implement SQL queries to perform various DDL Commands. (Create minimum 5 tables with different data types and operate upon them)	3	4
4	Implement SQL queries to perform various DML Commands. (Insert minimum 10 rows using different insert methods, edit and remove data using update and delete commands) and retrieve data using SELECT command and various SQL operators.	3	4
5	Implement SQL queries using Date functions like add-months, months-between, round, nextday, truncate etc	3	2
6	Implement SQL queries using Numeric functions like abs, ceil, power, mod, round, trunc, sqrt etc. and Character Functions like initcap, lower, upper, ltrim, rtrim, replace, substring, instr etc.	3	2
7	Implement SQL queries using Conversion Functions like to-char, to-date, to-number and Group functions like Avg, Min, Max, Sum, Count, Decode etc.	3	2
8	Write SQL query for set operators and join operations.	3	2
9	Apply the concept of integrity/data constraints while creating/altering a table	3	2
10	Write SQL queries for CREATE USER, GRANT, REVOKE and DROP USER command.	3	2
11	Practices on Normalization - using any database perform various normal forms.	4	2
12	Prepare a report on transaction management concepts for concurrent access of database by multiple users.	5	2
Total			29

Note

i. More Practical Exercises can be designed and offered by the respective course teacher to develop the industry relevant skills/outcomes to match the COs. The above table is only a suggestive list.
ii. The following are some sample 'Process' and 'Product' related skills (more may be added/deleted depending on the course) that occur in the above listed Practical Exercises of this course required which are embedded in the COs and ultimately the competency.

S. No.	Sample Performance Indicators for the PrOs	Weightage in %
1	Problem Analysis	20
2	Development of the Solution	20
3	Testing of the Solution	10
4	Record observations correctly	20
5	Interpret the result and conclude	30
Total	Total	100

6. MAJOR EQUIPMENT/ INSTRUMENTS REQUIRED

a. Hardware: Computer Systems with minimum PIV processor (or equivalent) and 1 GB RAM.
b. Software: SQL/PLSQL supporting software. (e.g. Oracle, SQL Server, MySQL)

7. AFFECTIVE DOMAIN OUTCOMES

The following sample Affective Domain Outcomes (ADOs) are embedded in many of the above-mentioned COs and PrOs. More could be added to fulfil the development of this competency.

a) Work as a leader/a team member.
b) Follow ethical practices.
c) Practice environment friendly methods and processes. (Environment related)

The ADOs are best developed through the laboratory/field-based exercises. Moreover, the level of achievement of the ADOs according to Krathwohl's 'Affective Domain Taxonomy' should gradually increase as planned below:

i. 'Valuing Level' in 1st year
ii. 'Organization Level' in 2nd year.
iii. 'Characterization Level' in 3rd year.

9. UNDERPINNING THEORY

Only the major Underpinning Theory is formulated as higher level UOs of Revised Bloom's taxonomy in order development of the COs and competency is not missed out by the students and teachers. If required, more such higher level UOs could be included by the course teacher to focus on attainment of COs and competency.

Unit	Learning Outcomes	Topics Covered
Unit - I: Introduction to Database Systems	1a. Describe basic concepts of database and its applications 1b. Differentiate between databases and file systems 1c. Explain the concepts of data abstraction and data independence 1d. Explain the different types of data models	1.1 Introduction 1.1.1 Data and Information 1.1.2 Database and Database Management System 1.1.3 Metadata 1.1.4 Data items, fields & records 1.1.5 Data Dictionary 1.2 Purpose of Database System 1.3 File oriented System versus database system 1.4 Application of DBMS 1.5 Database Administrator 1.5.1 Roles and responsibilities of DBA 1.6 Schema, Sub-Schema, Instances 1.7 Data Abstraction 1.7.1 Internal Level 1.7.2 Conceptual Level 1.7.3 External Level 1.8 Data Independence 1.9 Database Architecture 1.9.1 ER Model 1.9.2 Relational Model 1.9.3 Object oriented data model 1.9.4 Network Data Model 1.9.5 Hierarchical Data Model
Unit - II: ER Model and Relational Algebra	2a. Design ER Diagram for an application 2b. Convert ER Diagram to database	2.1 Basic concepts of E-R 2.1.1 Entity 2.2.2 Attributes 2.2.3 Relationship 2.2.3.1 Participation 2.2.3.2 Recursive relationships 2.2.3.3 Degree of relationship set 2.2 Mapping Cardinality 2.3 Key 2.3.1 Primary, Foreign, Super, Candidate 2.4 ER Diagrams 2.5 Weak Entity Sets 2.6 Enhanced ER Model 2.6.1 Subclass & Super Class 2.6.2 Generalization 2.6.3 Specialization 2.6.4 Aggregation 2.7 Converting ER Diagrams to database
Unit - III: Structured Query Language	3a. Write queries to create database, store and retrieve data from database 3b. Use SQL functions for different Operations 3c. Write SQL sub-queries to retrieve data 3d. SQL Constraints	3.1 SQL Data types 3.2 Data Definition Language Commands: create, alter, truncate, drop 3.3 Data Manipulation Language Commands: insert, select, update, delete 3.4 Privilege command: grant, revoke 3.5 SQL views 3.6 Single row function 3.7 Date functions 3.8 Numeric & Character function 3.9 Conversion function 3.10 Miscellaneous function 3.11 Group function 3.13 Operators 3.14 Arithmetic 3.15 Comparison 3.16 Logical Group by 3.17 Having and order by clause 3.18 Set operators: Union, union all, intersect, minus 3.19 Joins: simple join, equi join, non equi join, self-join, outer join 3.20 Need of Constraints 3.21 Domain Integrity constraints: Not null, Check 3.22 Entity Integrity constraints 3.22.1 Unique 3.22.2 Primary key 3.23 Referential integrity Constraints 3.23.1 Foreign key 3.23.2 Reference key
Unit - IV: Refining database design through Normalization	4a. Describe the Normalization process 4b. Design database using different levels of normalization	4.1 Importance of Normalization 4.2 Functional Dependencies 4.2.1 Partial Functional Dependency 4.2.2 Full Functional Dependency 4.2.3 Transitive Dependency 4.3 Normal Forms 4.3.1 First Normal Form 4.3.2 Second Normal Form 4.3.3 Third Normal Form
Unit - V: Transaction Management	5a. Apply transaction concepts 5b. Use Serializability	5.1 Transaction concepts, properties of transactions 5.2 Serializability of transactions 5.2.1 Conflict Serializability 5.2.2 View Serializability

Note: The UOs need to be formulated at the 'Application Level' and above of Revised Bloom's Taxonomy to accelerate the attainment of the COs and the competency.

10. SUGGESTED SPECIFICATION TABLE FOR QUESTION PAPER DESIGN

Unit No.	Unit Title	Teaching Hours	Distribution of Theory Marks	Distribution of Theory Marks	Distribution of Theory Marks	Distribution of Theory Marks
Unit No.	Unit Title	Teaching Hours	R Level	U Level	A Level	Total Marks
I	Introduction to Database Systems	06	4	4	2	10
II	ER Model and Relational Algebra	10	5	5	6	16
III	Structured Query Language	16	6	6	10	22
IV	Refining database design through Normalization	06	3	3	6	12
V	Transaction Management	04	2	4	4	10
Total		42	20	22	28	70

Legends: R=Remember, U=Understand, A=Apply and above (Revised Bloom's taxonomy) Note : This specification table provides general guidelines to assist student for their learning and to teachers to teach and question paper designers/setters to formulate test items/questions assess the attainment of the UOs. The actual distribution of marks at different taxonomy levels (of R, U and A) in the question paper may vary slightly from above table.

11. SUGGESTED STUDENT ACTIVITIES

Other than the classroom and laboratory learning, following are the suggested student-related co-curricular activities which can be undertaken to accelerate the attainment of the various outcomes in this course: Students should conduct following activities in group and prepare reports of about 5 pages for each activity, also collect/record physical evidences for their (student's) portfolio which will be useful for their placement interviews:

i. Prepare seminar presentations explaining the organization of database in various live systems like banking, insurance, online store etc.
ii. Prepare power point presentation for different SQL Statements.
iii. Prepare case study explaining the need for converting a large table to many smaller tables using 1NF, 2NF, 3NF
iv. Design database which can be used in the course on .net programming

12. SUGGESTED SPECIAL INSTRUCTIONAL STRATEGIES (if any)

These are sample strategies, which the teacher can use to accelerate the attainment of the various outcomes in this course:

a) Massive open online courses (MOOCs) may be used to teach various topics/sub topics.
b) Guide student(s) in undertaking micro-projects.
c) 'L' in section No. 4 means different types of teaching methods that are to be employed by teachers to develop the outcomes.
d) About 20% of the topics/sub-topics which are relatively simpler or descriptive in nature is to be given to the students for self-learning, but to be assessed using different assessment methods.
e) With respect to section No.11, teachers need to ensure to create opportunities and provisions for co-curricular activities.
f) Guide students on how to address issues on environ and sustainability

13. SUGGESTED MICRO-PROJECTS

Only one micro-project is planned to be undertaken by a student that needs to be assigned to him/her in the beginning of the semester. In the first four semesters, the micro-project are group-based. However, in the fifth and sixth semesters, it should be preferably be individually undertaken to build up the skill and confidence in every student to become problem solver so that s/he contributes to the projects of the industry. In special situations where groups have to be formed for micro-projects, the number of students in the group should not exceed three.

The micro-project could be industry application based, internet-based, workshop-based, laboratory-based or field-based. Each micro-project should encompass two or more COs which are in fact, an integration of PrOs, UOs and ADOs. Each student will have to maintain a dated work diary consisting of individual contributions in the project work and give a seminar presentation of it before submission. The total duration of the micro-project should not be less than 16 (sixteen) student engagement hours during the course. The student ought to submit a micro-project by the end of the semester to develop the industry oriented COs.

A suggestive list of micro-projects is given here. This has to match the competency and the COs. Similar micro-projects could be added by the concerned course teacher:

Students have to perform the following steps for any chosen project.

a) Choose any topic of your choice and enlist its requirements.
b) Draw an ER Diagram for your chosen topic and prepare tables, establish relationships between them.
c) Normalize the database.
d) Determine the different scenarios and how data will be fetched with queries.
e) Write the relational algebra queries for queries mentioned.

Sample Project Definitions: Hotel management, Event Management, Hospital Management, Health Monitoring Management System, Airline Management, Bank Management system, Transportation Management System, Library Management System etc

14. SUGGESTED LEARNING RESOURCES

S. No.	Title of Book	Author	Publication with place, year and ISBN
1	Database Systems Concepts, design and Applications 2/e	Singh, S. K.	Pearson Education, New Delhi, 2011
2	An Introduction to Database Systems	Date, C. J.	Pearson Education, New Delhi, 2006
3	Database System Concepts	Korth, Henry	McGraw Hill, Delhi, 2011
4	Introduction to Database Systems	ITL ESL.	Pearson Education, New Delhi, 2010
5	SQL/PL/SQL	Bayross, Ivan	BPB, New Delhi, 2010

15. SOFTWARE/LEARNING WEBSITES

a) DBMS: NPTEL Database Management Systems
b) SQL Plus Tutorial: Oracle SQL Plus Tutorial
c) Database Tutorials: Rose India Database Tutorials
d) SQL Basic Concepts: W3Schools SQL Tutorial
e) SQL Tutorial: Beginner SQL Tutorial

16. PO-COMPETENCY-CO MAPPING

Semester III	Database Management Systems (1333204) - POs and PSOs
Competency & Course Outcomes	PO 1: Basic & Discipline specific knowledge	PO 2: Problem Analysis	PO 3: Design/development of solutions	PO 4: Engineering Tools, Experimentation & Testing	PO 5: Engineering practices for society, sustainability & environment	PO 6: Project Management	PO 7: Life-long learning
Competency: Design database for a given application & perform various database operations.	3	3	3	2	1	2	2
Course Outcomes
CO a) Understand the fundamental concepts of database systems.	3	-	-	-	-	-	-
CO b) Design database using Entity relationship approach.	2	3	2	1	-	1	2
CO c) Implement Relational algebra in a database.	2	1	-	-	-	-	-
CO d) Apply SQL commands to create, modify, and manipulate databases	3	2	3	3	-	1	1
CO e) Apply concepts of normalization to design an optimal database.	2	3	2	-	-	2	2
CO f) Explain transaction management concepts for concurrent use of database.	2	2	-	-	-	-	-

Legend: '3' for high, '2' for medium, '1' for low or '-' for the relevant correlation of each competency, CO, with PO/PSO

17. COURSE CURRICULUM DEVELOPMENT COMMITTEE

GTU Resource Persons

S. No.	Name and Designation	Institute	Contact No.	Email
1	Dr. P. P. Kotak, Principal	S & SS Gandhi College, Surat	8200601748	kotakp2003@yahoo.com
2	Ms. Manisha Mehta, HoD, Computer Engineering	Govt. Polytechnic, Himmatnagar	-	manishamehtain@gmail.com
3	Miss Dhara H. Wagh, Lecturer, Department of Computer Engineering	Govt. Polytechnic, Gandhinagar	9427465614	dhara.wagh1791@gmail.com
4	Mihir R. Panchal, Lecturer, Department of Electronics and Communication Engineering	Govt. Polytechnic for Girls, Ahmedabad	9723340568	Panchalmihir031@gmail.com