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:
- 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.
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:
- 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; 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:
- Converting an E-R diagram of a given student-section-course database to a relational schema.
- 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:
- 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)
- 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:
- 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
- For a given set of relation schemes, create tables and perform the following Join Queries using MySQL Workbench:
- Inner Join
- Outer Join
- For a given set of relation schemes, create tables and 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
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:
- 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:
- Fundamentals of Database System – Elmasari & Navathe - Pearson Education-5th, Edition.
- Database System Concepts by Sudarshan, Korth (McGraw-Hill Education) - 6th, edition
Reference:
- An introduction to Database System – Bipin Desai, Galgotia Publications
- Database System: concept, Design & Application - S.K.Singh (Pearson Education)
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.MySQL.html
- 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 , PPT, SN-DCM1
Session 2
Data Models: VIDEO LINK , NPTEL VIDEO LINK , PPT
DBMS Architecture: VIDEO LINK
Building a DBMS, Components of DBMS Environment: VIDEO LINK
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
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 LINK , PPT
Session 10
Functionalities of MySQL Workbench, Creating a connection in MySQL Workbench: VIDEO LINK
Setup the MySQL Workbench: 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
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 LINK2 , PPT, SN-DCM14
Session 15
Schemas, Mappings: VIDEO LINK1 , VIDEO LINK2
Instances, Data Independence, Data Abstraction: VIDEO LINK1
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
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 LINK1 , VIDEO LINK2 , PPT, SN-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 LINK , PPT
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 30
Practice 9:
- Converting an E-R diagram of given hospital database to a relational schema.
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:
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 LINK1 , VIDEO LINK2 , NPTEL VIDEO LINK , PPT, SN-DCM36-37
Session 38
Practice 10 & 11:
DQL : IITB VIRTUAL LAB LINK
Describe: IITB VIRTUAL LAB LINK
- 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)
- 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 LINK , NPTEL VIDEO LINK, SN-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 :
DCL & TCL: IITB VIRTUAL LAB LINK
- 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
- For a given set of relation schemes, create tables and perform the following Join Queries using MySQL Workbench:
- Inner Join
- Outer Join
- For a given set of relation schemes, create tables and 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
- For a given set of relation tables perform the following using MySQL Workbench:
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 LINK , PPT
Importance of a good schema design, problems encountered with bad schema designs, motivation for normal forms: NPTEL VIDEO LINK , PPT
Session 45
Dependency theory - functional dependencies, 1NF: NPTEL VIDEO LINK , PPT
Decompositions and desirable properties of them: NPTEL VIDEO LINK , PPT
Lossless-join decomposition: NPTEL VIDEO LINK , PPT
Session 46
1NF, 2NF, 3NF, BCNF, with example and problem solving: EDUREKA VIDEO LINK , PPT
Multi-valued dependencies and 4NF: VIDEO LINK , NPTEL VIDEO LINK , PPT
Join dependencies and 5NF: VIDEO LINK , PPT
Session 47
Practice 15:
- 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 LINK1, VIDEO LINK2
Session 49
Concurrency control: VIDEO LINK, NPTEL VIDEO LINK , PPT
Locking based protocols for concurrency control: VIDEO LINK, NPTEL VIDEO LINK , PPT
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
Session 51, 52, 53 & 54
Project Review.
Our Main Teachers
Dr. Sasmita Kumari Nayak
Associate Professor, Department of Computer Science & Engineering
VIEW PROFILEReceived 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 PROFILEPh.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 […]
