Database Creation and Maintenance

Teacher

Dr. Sasmita Kumari Nayak

Category

Core Courses

Course Attendees

Still no participant

Course Reviews

Still no reviews

Course Name : Database Creation and Maintenance

Code(Credit) : CUTM1033(2-1-1)

Course Objectives

  • Foundation knowledge in database concepts, technology and practice to groom students into well-informed database application developers.
  • Make the students understand the principles behind relational database management systems, including the database environment, the relational model, relational languages, develop simple SQL queries using MySQL Workbench.
  • Strong practice in SQL programming through a variety of database problems.

Course Outcomes

COs

Course Outcomes

Mapping COs with POs (High-3, Medium-2, Low-1)

CO1
Acquire the knowledge on different database implementation for various applications
PO1 (3), PO12(1)
CO3
Solving different problems such as to convert the entity-relationship diagrams into relational tables, normalization and SQL queries
PO2(3)
CO4
Design database models and the entity-relationship diagrams of different databases using MySQL Workbench
PO3 (3)

Course Syllabus

Module I: Database System Concepts and Data Models (9 Hrs)

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:

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

Module II: Database System Architecture and ER Diagram (9 Hrs)

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:

  1. Analyze the problem and come with the entities in it. Identify what Data has to be persisted in the databases.
  2. 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.
  3. 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.
  4. 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; and (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.

Module III: Relational Data Model and Relational algebra (9 Hrs)

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.

Module IV: Structured Query Language-DDL (7 Hrs)

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

Module V: Structured Query Language-DML, DCL, TCL (7 Hrs)

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:

  1. 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
  2. For a given set of relation schemes, create tables and perform the following Join Queries using MySQL Workbench:
    • Inner Join
    • Outer Join
  3. For a given set of relation schemes, create tables and perform the following Subqueries using MySQL Workbench:
    • With IN clause
    • With EXISTS clause
  4. 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

Module VI: Normalization (8 Hrs)

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
    • Third Normal Form
    • BCNF

Module VII: Transaction Management and Concurrency Control (5 Hrs)

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)

Text Book:

  1. Fundamentals of Database System – Elmasari & Navathe - Pearson Education-5th, Edition.
  2. Database System Concepts by Sudarshan, Korth (McGraw-Hill Education) - 6th, edition

 Reference:

  1. An introduction to Database System – Bipin Desai, Galgotia Publications
  2. Database System: concept, Design & Application - S.K.Singh (Pearson Education)
  3. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.MySQL.html
  4. https://www.edureka.co/blog/mysql-workbench-tutorial#SQL%20Development%20Editor

Session Plan

Session 1 (Module I)

Database, Approaches to Building a Database, Database Management System (DBMS): VIDEO LINK1 VIDEO LINK2 , NPTEL VIDEO LINK PPTSN-DCM1 

Session 2

Data Models: VIDEO LINK , NPTEL VIDEO LINKPPT

DBMS Architecture: VIDEO LINK 

Building a DBMS, Components of DBMS Environment: VIDEO LINK

PPT

SN-DCM2

Session 3

Practice 1: 

  • Installation of MySQL Workbench. Import and export the database into MySQL Workbench.

Session 4 & 5

File Based Approach, Database Systems, File- oriented Systems vs. Database Systems: VIDEO LINK

Advantages and Disadvantages of DBMS: VIDEO LINK

PPT

SN-DCM4-5

Session 6, 7 & 8

Project :

Project will be chosen by students in groups of size 2 or 4.

Discuss the objectives and assign the following projects:

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)

Session 9

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

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

SN-DCM9

Session 10

Functionalities of MySQL Workbench, Creating a connection in MySQL Workbench: VIDEO LINK

Setup the MySQL Workbench: VIDEO LINK

EDUREKA VIDEO LINK

Session 11

Practice 2:

  • Collect data from different data sources and identify the type of data source.

Session 12

Working of SQL Editor, Data Export & Import: VIDEO LINK

Database table creation & insertion of values: VIDEO LINK

EDUREKA VIDEO LINK

Session 13

Practice 3:

  • Create instances of database in MySQL using MySQL Workbench.

Session 14 (Module II)

Database System Architecture: Three Level Architecture, External Level, Conceptual Level, Internal Level: VIDEO LINK1 ,  VIDEO LINK2PPTSN-DCM14

Session 15

Schemas, Mappings: VIDEO LINK1VIDEO LINK2

Instances, Data Independence, Data Abstraction: VIDEO LINK1

NPTEL VIDEO LINK

SN-DCM15

Session 16 & 17

E/R Model - Conceptual data modeling - motivation, various types of attributes, E/R diagram notation, examples: VIDEO LINK1 , NPTEL VIDEO LINK 

Entities, Entity types, relationships, relationship types: VIDEO LINK1 , NPTEL VIDEO LINK

PPT

SN-DCM16-17

Session 18

Practice 4:

  • Analyze the problem and come with the entities in it. Identify what Data has to be persisted in the databases.

Session 19

Database Design & Modeling using MySQL Workbench, Create EER Diagram with MySQL WorkBench: VIDEO LINK

Session 20

Practice 5 & 6:

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

Session 21 & 22

Project :

  • Find the entity and attributes for the assigned project.

Session 23 (Module III)

Relational Data Model: Concept of relations, schema-instance distinction, Keys, Referential integrity and Foreign keys: VIDEO LINK1VIDEO LINK2PPTSN-DCM23

Session 24

Practice 7:

  • 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; and (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.

Session 25 & 26

Different types of Keys: VIDEO LINK

Relational algebra operators: selection, projection, cross product: NPTEL VIDEO LINK PPT

Join operation, types of joins, division: NPTEL VIDEO LINKPPT

SN-DCM25-26

Session 27

Converting the database specification in E/R notation to the relational schema: NPTEL VIDEO LINK

Session 28

Practice 8:

  • Converting an E-R diagram of given student-section-course database to a relational schema.

Session 29

Example queries, Tuple Relation Calculus: NPTEL VIDEO LINKPPT

Session 30

Practice 9:

  • Converting an E-R diagram of given hospital database to a relational schema.

Session 31

Domain Relational Calculus: NPTEL VIDEO LINKPPT

Session 32

Project:

  • Design the EER diagram for the assigned project.

Session 33 & 34 (Module IV)

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:

COURSERA VIDEO LINK , EDUREKA VIDEO LINKPPTSN-DCM33-34

Session 35

Project:

  • Convert ER diagram to Relational schema diagram for the assigned project.

Session 36 & 37

Data Definition Language: Creating a Database, Table Operations (Create, Alter, Drop), Table Operations (Truncate, Comment and Rename), Creating and Removing an Index: VIDEO LINK1VIDEO LINK2 , NPTEL VIDEO LINKPPTSN-DCM36-37

Session 38

Practice 10 & 11:

DQL : IITB VIRTUAL LAB LINK

Describe: IITB VIRTUAL LAB LINK

DML: IITB VIRTUAL LAB LINK

  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

Session 39 (Module V)

Data Manipulation Language: Using different DML commands (Insert, Delete, Update, and Select): VIDEO LINKNPTEL VIDEO LINKSN-DCM39

Session 40 & 41

Sorting Results (Order By), Aggregate Functions, Grouping Results (Group By): VIDEO LINK , NPTEL VIDEO LINK

Join: EDUREKA VIDEO LINK , NPTEL VIDEO LINK

Session 42

Practice 12, 13 & 14 :

DML: IITB VIRTUAL LAB LINK

DCL & TCL: IITB VIRTUAL LAB LINK

  1. For a given set of relation schemes, create tables and perform the following operations 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
  2. For a given set of relation schemes, create tables and perform the following Join Queries using MySQL Workbench:
  3. Inner Join
  4. Outer Join
    • For a given set of relation schemes, create tables and perform the following Subqueries using MySQL Workbench:
  5. With IN clause
  6. 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

Session 43

Data Control Language & Transaction Control Language: Using different DCL commands (Grant, Revoke): VIDEO LINK

TCL commands (Commit, Rollback and Savepoint): VIDEO LINK

Session 44 (Module VI)

Normalization: Dependencies and Normal forms - Types of normal forms: NPTEL VIDEO LINKPPT

Importance of a good schema design, problems encountered with bad schema designs, motivation for normal forms: NPTEL VIDEO LINKPPT

SN-DCM44

Session 45

Dependency theory - functional dependencies, 1NF: NPTEL VIDEO LINKPPT

Decompositions and desirable properties of them: NPTEL VIDEO LINKPPT

Lossless-join decomposition: NPTEL VIDEO LINKPPT

SN-DCM45

Session 46

1NF, 2NF, 3NF, BCNF, with example and problem solving: EDUREKA VIDEO LINKPPT

Multi-valued dependencies and 4NF: VIDEO LINK , NPTEL VIDEO LINKPPT

Join dependencies and 5NF: VIDEO LINKPPT

SN-DCM46

Session 47

Practice 15:

  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
    • Third Normal Form
    • BCNF

Session 48 (Module VII)

Transaction processing and Error recovery: Concepts of transaction processing: VIDEO LINK

ACID properties: VIDEO LINK1VIDEO LINK2

NPTEL VIDEO LINKPPT

SN-DCM48

Session 49

Concurrency control: VIDEO LINK, NPTEL VIDEO LINKPPT

Locking based protocols for concurrency control: VIDEO LINK, NPTEL VIDEO LINKPPT

SN-DCM49

Session 50

Error recovery and logging, undo, redo: VIDEO LINK, NPTEL VIDEO LINK PPT

Undo-redo logging and recovery methods: VIDEO LINK1, VIDEO LINK2, NPTEL VIDEO LINK PPT

SN-DCM50

Session 51, 52, 53 & 54

Project Review.

Our Main Teachers

Dr. Sasmita Kumari Nayak

Associate Professor, Department of Computer Science & Engineering
VIEW PROFILE

Received M.Tech in Information Technology from BPUT University in the year of 2010 and completed Ph.D. in Computer Science and Engineering at Sambalpur University in 2022. Currently working as an Associate Professor at Centurion University of Technology and Management since 2009.

Dr. Sangram Keshari Swain

Dean, Examinations and Dean, Students' Welfare and Professor, Department of Computer Science and Engineering
VIEW PROFILE

Ph.D. in Computer Science and Engineering having 15 years of teaching experience, having a demonstrated history of working in the higher education industry. Skilled in Social Services, Teaching, Research, Data Analysis, and Higher Education. A strong education, professional having multidimensional approaches like Engineering, Technology, Management, Social Service and Law. Social Responsibility activity has been my […]

Abhijit Pal

Assistant Professor
VIEW PROFILE