Database Approach: Database, Approaches to Building a Database, Data Models, Database Management System (DBMS), DBMS Architecture, Building a DBMS, Components of DBMS Environment.

File Based Systems and Database Systems: File Based Approach, Database Systems, File- oriented Systems vs. Database Systems, Advantages and Disadvantages of DBMS.

Data Source: Types of Data sources, Access Documents and Information from Data Sources.

Roles in Database Environment: Database Users, Database Administrators (DBA)

Database installation procedure: Functionalities of MySQL Workbench, Creating a connection in MySQL Workbench, Setup the MySQL Workbench, Working of SQL Editor, Data Export & Import, Database table creation & insertion of values.

Practice:

• Collect data from different data sources and identify the type of data source.
• Installation of MySQL Workbench. Import and export the database into MySQL Workbench.
• Create instances of database in MySQL.

Three Level Architecture, External Level, Conceptual Level, Internal Level, Schemas, Mappings, Instances, Data Independence, Data Abstraction, E/R Model - Conceptual data modeling - motivation, entities, entity types, various types of attributes, relationships, relationship types, E/R diagram notation, examples, Database Design & Modeling using MySQL Workbench, Create EER Diagram with MySQL Workbench.

Practice:

• Analyze the problem and come with the entities in it. Identify what data has to be persisted in the databases.
• Use MySQL Workbench to construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
• Use MySQL Workbench to construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
• A university registrar’s office maintains data about the following entities:
  • (a) Courses, including number, title, credits, syllabus, and prerequisites;
  • (b) Course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom;
  • (c) Students, including student-id, name, and program;
  • (d) Instructors, including identification number, name, department, and title.
• Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Use MySQL Workbench to construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.

Relational Data Model: Concept of relations, schema-instance distinction, keys, referential integrity and foreign keys. Relational algebra operators: selection, projection, cross product, various types of joins, division, example queries, tuple relation calculus, domain relational calculus, converting the database specification in E/R notation to the relational schema.

Practice: 1. Converting an E-R diagram of a given student-section-course database to a relational schema. 2. Converting an E-R diagram of a given hospital database to a relational schema.

Database installation procedure: Database table creation & insertion of values.

Database Languages: SQL - DDL, DML, TCL, DCL

SQL: Data definition in SQL, table, key and foreign key definitions, update behaviors. Querying in SQL - basic select-from-where block and its semantics, nested queries, notion of aggregation, aggregation functions group by and having clauses.

Data Definition Language: Creating a Database, Table Operations (Create, Alter, Drop, Truncate, Comment and Rename), Creating and Removing an Index.

Practice:

1. Perform the following operations using MySQL Workbench:

• Creating a Database
• Viewing all databases
• Viewing all Tables in a Database
• Creating Tables (With and Without Constraints)
• Inserting/Updating/Deleting Records in a Table
• Saving (Commit) and Undoing (Rollback)

2. Perform the following Altering a Table operations using MySQL Workbench:

• Rename the table
• Add a new column
• Rename the column name
• Change the data type of the column name
• Delete table

Data Manipulation Language: Using different DML commands (Insert, Delete, Update, and Select), Sorting Results (Order By), Aggregate Functions, Join, Grouping Results (Group By) Data Control Language & Transaction Control Language: Using different DCL commands (Grant, Revoke) & using different TCL commands (Commit, Rollback and Savepoint).

Practice:

For a given set of relation schemes, create tables and perform the following using MySQL Workbench:

• Simple Queries with Aggregate functions
• Queries with Aggregate functions (GROUP BY and HAVING clause)
• Queries involving:
  • Date Functions
  • String Functions
  • Math Functions

Perform the following Join Queries using MySQL Workbench:

• Inner Join
• Outer Join

Perform the following Subqueries using MySQL Workbench:

• With IN clause
• With EXISTS clause

For a given set of relation tables perform the following using MySQL Workbench:

• Creating Views (with and without CHECK OPTION)
• Selecting from a View
• Dropping Views

Normalization: Dependencies and Normal forms - Importance of a good schema design, problems encountered with bad schema designs, motivation for normal forms, dependency theory - functional dependencies, Armstrong's axioms for FD's, closure of a set of FD's, minimal covers, Types of Normalization: 1NF, 2NF, 3NF and BCNF, decompositions and desirable properties of them, algorithms for 3NF and BCNF normalization, multi-valued dependencies and 4NF, join dependencies and definition of 5NF.

Practice:

1. To check whether the given database table is normalized or not

• If yes, find out the status of normalization and reasoning
• First Normal Form (1NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)

Transaction processing and Error recovery: Concepts of transaction processing, ACID properties, concurrency control, locking based protocols for concurrency control, error recovery and logging, undo, redo, undo-redo logging and recovery methods.

Project:

• Mini project (Application Development using MySQL Workbench)
  • Leave Management System
  • Blood Donation Management System
  • CUTM Cafeteria System
  • Student Subject Registration System
  • CUTM Stock Monitoring System
  • (The above projects, including but not limited to, assign to the students)