RELATIONAL DATA BASE MANAGEMENT SYSTEM (RDBMS) 6TH SEM DIPLOMA COMMERCIAL PRACTICE
GOVERNMENT OF KARNATAKA
DEPARTMENT OF TECHNICAL EDUCATION
BOARD OF TECHNICAL EXAMINATIONS
DIPLOMA IN COMMERCIAL PRACTICE
VI SEMESTER
9CP66P - RELATIONAL DATA BASE
MANAGEMENT SYSTEM (RDBMS)
Contact Hours per week = 6 Contact Hours per Semester = 96
(PART – A)
UNIT – I: AN OVERVIEW OF DBMS:
Introduction – Data – Information – Database – Goals of DBMS – Characteristics of DBMS – Types of DBMS – Advantages and Disadvantages of DBMS.
UNIT – II: RELATIONAL DATABASE MANAGEMENT SYSTEM:
Introduction to RDBMS –Terminology – Relational Data Structure – Data Integrity – Design Constraints – Primary Key – Foreign Key and its Constraints.
UNIT – III: ER MODELLING:
Introduction to Entity Relationship Modeling – ER Model – Entities – Attributes – Types of Attributes – ERD Conventions – Relationships – Degree – Connectivity – Cardinality – Dependency – Participation.
UNIT – IV: STRUCTURED QUERY LANGUAGE:
Introduction to SQL – Characteristics of SQL – Data types – Types of SQL Commands – Data Definition Language (DDL) – Data Manipulation Language (DML) – Data Control Language (DCL) - SQL Operators (Arithmetic Operator – Comparison Operator – Logical Operators and Set Operators).
UNIT – V: WEB DATABASE:
Introduction to Internet – E-Com – Web Browser – Accessing Database on the Web Browser – Tools required for Web Database – Internet Information Server (IIS) – Object Linking Embedding Data Base (OLEDB) provider – ActiveX Database (ADO) Connection.
(PART – B)
Write the Steps for the Following
- Draw E R Diagram and Relational Schema
- Create the Following Tables for a Book Club Database.
| CATALOG |
| ||||
| Book id | Title | Author id | Publisher id | Year of Publish | Price |
| AUTHOR |
| ||
| Author id | Name | City | Country |
| PUBLISHER |
| ||
| Publisher id | Name | City | Country |
| ORDER DETAILS |
| |
| Order No. | Book id | Quantity |
Identify the Primary and Foreign Key in Each Table
- Insert the suitable values into each tables (at least 10 Records each)
- Query the Tables and Store them in Separate Qname.
a) Get name, City, Country of all the Publishers
b) Get the Title, Year and Price of all the Books Published in the Year 2008
c) Get the Title, Year and Price of all the Books Published after 2010 and whose price is greater than 500.
d) Sort the above query in Ascending Order.
e) Get the Names of all Authors who have more than two books in the Catalog.
f) Get the Name and Total Price of each Publisher in the Catalog.
- Query Multiple Tables (Relationship)
a) Get the Title, Author Name, Publisher Name, Year and Price of all the Books Published after 2008.
b) Delete the Author Record whose Country is other than ‘India’.
c) Increase the Price of all the Books by 20 % discount on published after 2010.
d) Change the Name of the City from Bengaluru to Mysooru.
e) Get the Details of all Publishers whose Name starts with ‘S’ from Publisher Table.
f) Get the Total Cost of the Books in the Order-details Table.
g) Get the List of Book, Price which is in the Order Details.
- Create Report for the above Queries and different tables. Try Sorting and Grouping the Data with different styles.
- Prepare a Mini Project on any database if needed.
SOFTWARE REQUIREMENT:
MS ACCESS – DATABASE - QUERY TOOL – MYSQL OR SQL DEVELOPER.
INSTRUCTIONS:
1. Write the Steps for Executing Queries in Part B.
2. In Part A Explain the mentioned topics and make a brief note in the Record.
3. In Part A, only Theoretical aspects are covered, whereas in Part B, have to conduct practically.
4. The given exercise is an overview of Database created and manipulated. This exercise can be tried for other queries, databases also with suitable attributes.
GENERAL OBJECTIVES
1. To Familiarize the Students with various Concepts of Database and its Uses.
2. To Enable the Students to acquire Skills about Database Model.
3. To Know the Concept of E.R Diagram and ER Modeling.
4. To Understand and Comprehend different SQL Statements or Database Language.
SPECIFIC OBJECTIVES
1.1. To Get aware of Data, Information and Database.
1.2. To Explain the Goals of Database Management System
1.3. To Explain the Characteristics of DBMS.
1.4. To List out various Types of DBMS
1.5. To Explain the Advantages and Disadvantages of DBMS.
2.1. To Explain the Meaning of RDBMS and its Terminology
2.2. To know about Relational Data Structure
2.3. To know about Data Integrity and Design Constraints.
2.4. To know how to use the Primary Key and Foreign Key.
2.5. To know the Constraints of using Primary key and Foreign Key.
3.1. To Explain the Meaning of ER Modeling
3.2. To Describe the Working of an ER Model
3.3. To Explain how handles Entities and Attributes
3.4. To List out the various types of Attributes.
3.5. To Explain the Meaning of ERD Conventions
3.6. To Explain the Meaning of Relationships, Degree, Connectivity, Cardinality, Dependency
and Participation.
4.1. To Explain the Meaning of SQL
4.2. To List out the various Characteristics of SQL.
4.3. To discuss about Data Types and Types of SQL Commands like DDL, DML & DCL.
4.4. To discuss the SQL Operators like Arithmetic, Comparison, Logical and Set Operators.
5.1. To Explain the Meaning of Internet and E-Commerce.
5.2. To know how to use the Web Browser and Accessing Database on the Web Browser.
5.3. To know what are the tools required for Web Database.
5.4. To know how to use IIS, OLEDB and ADO Connections.
--------------------------------------------------------------------------------------------------------------------
BOOKS AND WEB SITES FOR References:
1. Database Management System by Alexis Leon & Mathews Leon
2. Problem – Solving Cases using MS Access by Brandy Mons
3. DBMS a Practical Approach by E R Rajiv Chopra – S Chand Publications.
4. Com Primer – Wrox.
5. Database Management System by Hannur
SCHEME OF EVALUATION
1. Part - A - Writing the E R Diagrams + Steps 20 Marks
2. Part – B - Querying the Tables Create + Insert at least (4) 40 Marks
2. Viva 20 Marks
3. Record 05 Marks
4. Printouts 05 Marks
TOTAL 100 Marks
Comments