Download Database Management Systems Course Syllabus and Model Questions and more Study Guides, Projects, Research Database Management Systems (DBMS) in PDF only on Docsity!
1
Government of Karnataka Department of Technical Education Bengaluru
Course Title: Database Management Systems
Scheme (L:T:P) : 4:0:0 Total Contact Hours: 52 Course Code: 15CS33T Type of Course: Lectures, Self Study & Student Activity Credit : Core/ Elective: Core CIE- 25 Marks SEE- 100 Marks
Prerequisites Knowledge of programming language.
Course Objectives
- Understand database concepts, applications, data models, schemas and instances.
- Implement the relational database design and data modelling using entity-relationship (ER) model.
- Demonstrate the use of constraints and relational algebra operations.
- Use of SQL in querying the database
- Demonstrate Normalization process.
- Learn the new emerging Technologies and Applications in database.
Course Outcome
On successful completion of the course, the students will be able to attain below Course Outcome (CO):
Course outcome CL Linked PO Teaching Hours
CO1 Identify the characteristics of database and describe the architecture and languages of Database system.
R,
U
CO2 Identify^ the^ elements^ used^ in^ Entity- Relationship diagram and sketch a simple diagram.
U,
A
CO3 (^) Summarize relational model concept and illustrate the relational constraints.
U,
A 1,2,3,4,5,6,7,8,9,10^08
CO4 (^) Describe Structured Query Language (SQL) and apply to query a database. A^
CO5 (^) Recognize and interpret normalization for relational databases.
U,
A
CO6 (^) Visualize the features of NOSql and identify the database applications. A^
Total sessions 52
Legends: R = Remember U= Understand; A= Apply and above levels (Bloom’s revised taxonomy)
2
Course-PO Attainment Matrix
Course Programme Outcomes 1 2 3 4 5 6 7 8 9 10 Computer Organisation 3 3 3 3 3 3 3 3 3 3 Level 3- Highly Addressed, Level 2-Moderately Addressed, Level 1-Low Addressed. Method is to relate the level of PO with the number of hours devoted to the COs which address the given PO. If >40% of classroom sessions addressing a particular PO, it is considered that PO is addressed at Level 3 If 25 to 40% of classroom sessions addressing a particular PO, it is considered that PO is addressed at Level 2 If 5 to 25% of classroom sessions addressing a particular PO, it is considered that PO is addressed at Level 1 If < 5% of classroom sessions addressing a particular PO, it is considered that PO is considered not-addressed.
Course Content and Blue Print of Marks for SEE
Unit No Unit Name
Hour Questions to be set for SEE
Marks Weightage
Marks Weightage (%) R U A A I Identify the characteristics of database and describe the architecture and languages of Database system.
II Identify the elements used in Entity-Relationship diagram and sketch a simple diagram.
III Summarize relational model concept and illustrate the relational constraints.
IV Describe Structured Query Language (SQL) and apply to query a database.
V Recognize and interpret normalization for relational databases.
VI Visualize the features of NOSql and identify the database applications.
Total 52 21 76 48 145 100
UNIT I : Databases and Data Base Users 08 Hrs
Introduction, An Example, Characteristics of the database approach, Actors on the scene, Workers behind the scene, Advantages of using the DBMS Approach, A Brief History of Database Applications, When Not to use a DBMS Database System Concepts and Architecture - Data Models, Schemas, and Instances, Three-Schema Architecture and Data Independence, Database Languages and Interfaces, The Database System Environment, Centralized and Client /Server Architectures for DBMSs, Classification of database Management System.
UNIT II: Data Modelling Using the Entity-Relationship(ER) Model 08 Hrs
4
- Each group should do any one of the following type activity or any other similar activity related to the course and before conduction, get it approved from concerned course co- ordinator and programme co-ordinator
- Each group should conduct different activity and no repeating should occur.
1 Identify and do a case study on relationship types of degree higher than two in ER data modelling 2 Design E-R diagrams for the following databases with all constraints specifications. a) Hospital Management b) Hotel Management c) Student Database 3 Conduct a survey on various applications of database and submit a report of 3 to 4 pages. 4 Conduct a case study on any one Online Transaction Processing System (OLTP) and submit a report of 3 to 4 pages. Ex: Online Banking , Online Reservation (Bus, Train, Airlines)
Course Delivery
The course will be delivered through Lectures and Power point presentations/ Video
Course Assessment and Evaluation Scheme
Method What To who m
When/Where (Frequency in the course)
Max Marks
Evidence collected
Course outcomes
Direct Assessment
CIE IA
Students
Three IA tests (Average of three tests will be computed)
20 Blue books
1 to 6
Student activities
05 Report 1 to 6
Total (^25)
SEE End Exam
End of the course
Answer scripts at BTE
1 to 6
IndirectAssessment
Student Feedback on course
Students
Middle of the course Feedback forms 1,2,3 Delivery of course End of Course Survey
End of the course Questionnaires
1 to 6 Effectiveness of Delivery of instructions & Assessment Methods Note: I.A. test shall be conducted for 20 marks. Average marks of three tests shall be rounded off
to the next higher digit.
Questions for CIE and SEE will be designed to evaluate the various educational
components (Bloom’s taxonomy) such as:
Sl. No Bloom’s Category % 1 Remembrance 15 2 Understanding 50 3 Application 35
5
Note to IA verifier : The following documents to be verified by CIE verifier at the end of semester
- Blue books (20 marks)
- Student suggested activities report for 5 marks
- Student feedback on course regarding Effectiveness of Delivery of instructions & Assessment Methods.
FORMAT OF I A TEST QUESTION PAPER (CIE)
Test/Date and Time Semester/year Course/Course Code Max Marks
Ex: I test/6 th^ weak of sem 10-11 Am
I/II SEM
20 Year: Name of Course coordinator : Units:__ CO’s:____
Question no Question^ MARKS^ CL^ CO^ PO 1 2 3 4 Note: Internal choice may be given in each CO at the same cognitive level (CL).
MODEL QUESTION PAPER (CIE)
Test/Date and Time
Semester/year Course/Course Code Max Marks
Ex: I test/6 th^ week III SEM^ Data Base Management Systems^^20
7
Collection of data
Does not collect any information relating to the topic
Collects very limited information; some relate to the topic
Collects some basic information; refer to the topic
Collects relevant information; concerned to the topic
Collects a great deal of information; all refer to the topic
Fulfill team’s roles & duties
Does not perform any duties assigned to the team role
Performs very little duties
Performs nearly all duties
Performs all duties
Performs all duties of assigned team roles with presentation
Shares work equally
Always relies on others to do the work
Rarely does the assigned work; often needs reminding
Usually does the assigned work; rarely needs reminding
Does the assigned job without having to be reminded.
Always does the assigned work without having to be reminded and on given time frame
Listen to other Team mates
Is always talking; never allows anyone else to speak
Usually does most of the talking; rarely allows others to speak
Listens, but sometimes talk too much
Listens and contributes to the relevant topic
Listens and contributes precisely to the relevant topic and exhibit leadership qualities
TOTAL 13/4=3.25=
_All student activities should be done in a group of 4-5 students with a team leader._*
MODEL QUESTION PAPER Code: 15CS33T Diploma in Computer science & Engineering III- Semester Course Title: Data Base Management Systems
Time: 3 Hours Max Marks : 100
8
PART-A
Answer any SIX questions. Each carries 5 marks. 5X6=30 Marks
- What is database? List the important characteristics of database approach. 2. Explain with examples Entity Integrity Constraints and referential Integrity Constraint. 3. Explain the characteristics of Relations.
- With examples write a note on CREATE and DROP command.
- Differentiate Having and Where clause with example. 6. Explain the Boyce-Codd normal form with example. 7. Explain the generation of spurious tuples and how to overcome the same.
- Discuss the advantages of NoSql versus RDBMS.
- What is scaling. Explain the types of scaling.
PART-B
Answer any SEVEN full questions each carries 10 marks. 10X7=70 Marks
- Discuss the advantages of DBMS Approach.
- Define the following. a) Data Model b) Database Schema/Intension c) Database State/Extension d) Data Sublanguage. e) DML.
- With a neat diagram explain the different phases of database design.
- Identify the entities, relationships and develop an E-R diagram for a company database.
- Explain with example the different Constraints that are violated during UPDATE and
DELETE operations
- Consider the following schema & write the SQL queries. SAILORS (sid, sname, rating, age) BOATS (bid, bname, color) RESERVES (sid, bid, day) a. Retrieve names of sailors who reserved green boat. b. Retrieve the colors of boats reserved by ‘ramesh’. c. Retrieve the names of sailors who have reserved a red or a green boat. d. Retrieve the sid of sailors with age over 20 who have not reserved a red boat. e. Retrieve the names of sailors who have reserved all boats called ‘Interlake’.
- List and explain the different data types used in SQL.
- Discuss the informal design guidelines for relational schema.
- Draw a state diagram and discuss the typical states during transaction execution.
- Explain the need for concurrency control.
MODEL QUESTION BANK
Diploma in Computer Science & Engineering III Semester Course Title: Data Base Management Systems
CO Question CL Marks
10
a) Domain b) Attribute c) Tuple d) Relation schema (R) e) Relation (r) (^) 10 Explain the insert, delete and update operation with examples. A Explain with example the different constraints that are violated during UPDATE and DELETE operations
U
IV
Write a note on different data types supported by SQL. R
Write the general syntax of specifying Check constraint and default constraint while creating a table.
R
Differentiate Having and Where clause with example. A Write a note on aggregate functions in SQL. U Discuss the strategies for View Implementation. (^) U With examples write a note on CREATE and DROP command. A Define view. How to create it? Give example. U,A Explain UPDATE and ALTER command with example. (^) U,A Write a note on basic queries in SQL. U Explain the different schema change statements in SQL with example.
A
List and explain the different data types used in SQL. (^) U
Explain different constraints available in SQL with example. U List and explain the different aggregate functions available in SQL with example.
A
Explain the following with examples: a) Insert b) Delete c) Distinct d) Order by e) Where
A
Explain the following Operators with examples: a) ANY b) ALL c) IN d) LIKE e) BETWEEN
A
Consider the following schema & write the SQL queries. SAILORS (sid, sname, rating, age) BOATS (bid, bname, color) RESERVES (sid, bid, day) a) Retrieve names of sailors who reserved green boat. b) Retrieve the colors of boats reserved by ‘ramesh’. c) Retrieve the names of sailors who have reserved a red or a green boat. d) Retrieve the sid of sailors with age over 20 who have not reserved a red boat. e) Retrieve the names of sailors who have reserved all boats called ‘Interlake’.
And Similar databases to be considered with different set of queries.
A
V
Explain the generation of spurious tuples and how to overcome the same.
U
Differentiate between i) prime attribute and non prime attribute ii) Full functional dependencies and partial dependencies with examples.
U
Explain the functional dependencies with examples. U Explain second normal form with an example. (^) U Explain third normal form with an example. U
11
Explain the Boyce-Codd normal form with example. U Explain the different UPDATE anomalies of tables. U Discuss the informal design guidelines for relational schema. (^) U 10 What is normalization? Explain the different types of normal forms with examples.
U
VI
Explain the following terms: transaction, concurrency control. U
Explain the desirable properties of a transaction. U Write a note on System Log. (^) U Write a note on commit point of a transaction. U Discuss the advantages of NoSql versus RDBMS. (^) U Explain CAP theorem. U Explain the advantages of distributed computing. U What is scaling. Explain the types of scaling. (^) U Differentiate between ACID and BASE. U Bring out the advantages and disadvantages of Nosql. U Draw a state diagram and discuss the typical states during transaction execution.
A
Discuss the different types of failures that occur during transaction 10 execution.
A
Explain the need for concurrency control. A Explain the categories of NoSql. U Explain Column store and Row store with suitable examples. (^) A
