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. | |
|---|---|---|---|---|
| 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 |