DBMS

UNIT 1: Introduction to DBMS (15 Hrs)

• Introduction to DBMS
  • Data
  • Database
  • Definition & Characteristics of DBMS
• File System Vs Database System
• Advantages & Disadvantages of DBMS
• Database Users
• Types of DBMS
• Introduction to Structured Query Language (SQL)

UNIT 2: DBMS Architecture and Data Modelling (15 Hrs)

• DBMS Architecture
  • Data models
  • Schemas and Instances
• 3-Schema Architecture of DBMS
• Data independence
• Database Languages and Interfaces
• Data Modelling
  • E-R Model: Concept
  • ER Modeling Notations
    • Entity set
    • Attributes
    • Relationships
    • Types of keys
• Understanding Data Languages

  • Data Definition Language (DDL)

  • Data Manipulation Language (DML)

UNIT 3: Relational Model and Normalisation (15 Hrs)

• Relational Data Model
• Concept and definitions of RDBMS
• Relational model Constraints
  • Entity Integrity
  • Referential Integrity
  • Key Constraints
  • Domain Constraints
• Relational Database Design
  • Introduction to Normalisation
  • Functional dependencies
  • Transitive dependencies
  • Normal Forms: 1NF, 2NF & 3NF
• Using SQL for querying and Joins
• Understanding Data Control Language (DCL)

Sample Lab Work (indicative) (1 Credits, 30 Hours)

1. Create a table to store employee information with the following columns: employee_id, name, department, salary.
2. Add a new column to the employee table to store the employee’s email address.
3. Drop the customer table.
4. Modify the order table to change the data type of the price column from INTEGER to DECIMAL(10,2).
5. Create a primary key on the employee_id column in the employee table.
6. Create a foreign key on the customer_id column in the order table that references the customer_id column in the customer table.
7. Create a unique constraint on the product_id column in the product table.
8. Insert a new record into the employee table with the following values: employee_id = 100, name = ‘John Doe’, department = ‘IT’, salary = 50000. Use different values to insert multiple employees.
9. Update the salary of the employee with the ID 100 to 60000.
10. Delete the employee with the ID 100.
11. Select all employees from the employee table who work in the ‘IT’ department.
12. Select the average salary of employees in the ‘Sales’ department.
13. Select the employee with the highest salary in the employee table.
14. Insert a new record into the order table with the following values: order_id = 100, customer_id = 1, product_id = 1, quantity = 1, price = 100.
15. Update the quantity of the order with the ID 100 to 2.
16. Delete the order with the ID 100.
17. Select all orders from the order table that were placed by the customer with the ID 1.
18. Join the employee table with the order table to select all employees who have placed an order.
19. Use a subquery to select all employees who have placed an order for a product with the price greater than 100.
20. Create a view that shows the name and salary of all employees who work in the ‘IT’ department and earn more than 50000.
21. Write a procedure to insert a new record into the order table.
22. Write a function to calculate the total price of an order.
23. Grant the SELECT privilege on the employee table to the user ‘public’.
24. Grant the INSERT privilege on the order table to the user ‘sales’.
25. Revoke the UPDATE privilege on the customer table from the user ‘marketing’.
26. Create a role called ‘manager’ with the SELECT, INSERT, UPDATE, and DELETE privileges on all tables in the database.
27. Grant the ‘manager’ role to the user ‘admin’.

Project Work

1. Create a SQL based mini project based on a real-world scenario as defined by the Teacher.

SUGGESTED READING

1. Elmasri, Navathe “Fundamentals of Database Systems”, Pearson Education.
2. Korth, Silberschatz, Sudarshan,“ Database System Concepts”, TMH.
3. SQL, PL/SQL, The programming language of Oracle, Ivan bayross, BPB.