Course Objectives

• To understand the different issues involved in the design and implementation of a database system.
• To study the physical and logical database designs, database Modeling, relational,hierarchical, and network models
• To understand and use data manipulation language to query, update, and manage a database
• To develop an understanding of essential Properties of DBMS concepts such as database security, integrity, concurrency
• To design and build a simple database system and demonstrate competence with the fundamental tasks involved with modelling, designing, and implementing a DBMS.

Learning Outcomes

• Describe the fundamental elements of relational database management systems
• Explain the basic concepts of relational data model, entity-relationship model, relational database design, relational algebra and SQL
• Design ER-models to represent simple database application scenarios
• Convert the ER-model to relational tables, populate relational database and formulate SQL queries on data
• Improve the database design by normalization
• Familiar with basic database storage structures and access techniques: file and page organizations, indexing methods including B tree, and hashing

Course Syllabus

Module-1: DBMS Concepts [5Hrs]

Data Abstraction - Data models and data independence. Instances and Schemas. Components of a DBMS and overall structure of a DBMS- Life Cycle of a DBMS application- Database terminology.

Module-2: Data Modeling [5Hrs]

Basic concepts- Types of data models- Conceptual, physical and logical database models- E-R data model and Object-oriented data model. Components of ER Model- ER Modeling symbols. Entity and entity sets- Relations and relationship sets- E-R Diagrams- Reducing E-R Diagrams into tables.

Practice

Assume we have the following application that models soccer teams, the games they play, and the players in each team. In the design, we want to capture the following:

• We have a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs.
• Each team has many players, and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses.
• Teams play matches, in each match there is a host team and a guest team. The match takes place in the stadium of the host team.
• For each match we need to keep track of the following:
• The date on which the game is played
• The final result of the match
• The players participated in the match. For each player, how many goals he scored whether or not he took yellow card, and whether or not he took red card.
• During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place.
• Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee.
• Design an ER diagram to capture the above requirements. State any assumptions you have that affects your design (use the back of the page if needed). Make sure cardinalities and primary keys are clear.

Module-3:Relational DBMS Model [5 Hrs]

Basic concepts, Attributes and domains- Intention and extensions of a relation- concept of integrity and referential constraints- Relational Query Languages (Relational algebra and relational calculus (Tuple and domain relational calculus).

Module-4: Relational Database Design [6 Hrs]

Notion of normalized relations- Normalization using Functional Dependency- First Normal form- Second Normal Form- Third Normal form- BCNF.

Practice

Perform NF on the given table CLICK HERE FOR TABLE

Module-5: SQL [6 Hrs]

Structure of a SQL query- DDL and DML, TCL- SQL queries and sub queries- Tables, views and indexes.

Practice

To study DDL-create and DML-insert commands.

(i) Create tables according to the following definition.

CREATE TABLE DEPOSIT (ACTNO VARCHAR2(5) ,CNAME VARCHAR2(18) , BNAME VARCHAR2(18) , AMOUNT NUMBER(8,2) ,ADATE DATE); CREATE TABLE BRANCH(BNAME VARCHAR2(18),CITY VARCHAR2(18));
CREATE TABLE CUSTOMERS(CNAME VARCHAR2(19) ,CITY VARCHAR2(18)); CREATE TABLE BORROW(LOANNO VARCHAR2(5), CNAME VARCHAR2(18), BNAME VARCHAR2(18), AMOUNT NUMBER (8,2));

(ii) Insert the data as shown below. DEPOSIT CLICK HERE FOR TABLE BRANCH CLICK HERE FOR TABLE CUSTOMERS CLICK HERE FOR TABLE BORROW CLICK HERE FOR TABLE
(1) Describe deposit, branch.
(2) Describe borrow, customers.
(3) List all data from table DEPOSIT.
(4) List all data from table BORROW.
(5) List all data from table CUSTOMERS.
(6) List all data from table BRANCH.
(7) Give account no and amount of depositors.
(8) Give name of depositors having amount greater than 4000. (9) Give name of customers who opened account after date '1-12-96'.

Module-6:Aggregate functions [4 Hrs]

Set Operations, predicates and joins, Set Membership- Tuple variables- Set comparison- Database modifications using SQL.

Practice

Create the below-given table and insert the data accordingly.
Create Table Job (job_id, job_title, min_sal, max_sal) COLUMN NAME DATA TYPE job_id Varchar2(15) job_title Varchar2(30) min_salNumber(7,2) max_sal Number(7,2)
Create table Employee (emp_no, emp_name, emp_sal, emp_comm, dept_no) COLUMN NAME DATA TYPE emp_noNumber(3) emp_name Varchar2(30) emp_sal Number(8,2) emp_comm Number(6,1) dept_no Number(3)
Create table deposit(a_no,cname,bname,amount,a_date). COLUMN NAME DATA TYPE a_no Varchar2(5) cname Varchar2(15) bname Varchar2(10) amount Number(7,2) a_date Date Create table borrow(loanno,cname,bname,amount). COLUMN NAME DATA TYPE loanno Varchar2(5) cname Varchar2(15) bname Varchar2(10) amount Varchar2(7,2)
Insert following values in the table Employee. emp_nemp_nameemp_salemp_comm dept _no 101 Smith 800 20 102 Snehal 1600 300 25 103 Adama 1100 0 20 104 Aman 3000 15 105 Anita 5000 50,000 10 106 Sneha 2450 24,500 10 107 Anamika 2975 30 Insert following values in the table job. CLICK HERE FOR TABLE Insert following values in the table deposit.

Module-7: Transaction Management [8 Hrs]

Subqueries,ManupulatingData,Transaction management and Concurrency control

Practice

To apply the concept of Aggregating Data using Group functions. (1) List total deposit of customer having account date after 1-jan-96. (2) List total deposit of customers living in city Nagpur. (3) List maximum deposit of customers living in bombay. (4) Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. (5) Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE. (6) Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998 (7) Find the average salaries for each department without displaying the respective department numbers. (8) Write a query to display the total salary being paid to each job title, within each department. (9) Find the average salaries > 2000 for each department without displaying the respective department numbers. (10) Display the job and total salary for each job with a total salary amount exceeding 3000, in which excludes president and sorts the list by the total salary. (11) List the branches having sum of deposit more than 5000 and located in city bombay.

To solve queries using the concept of sub query. (1) Write a query to display the last name and hire date of any employee in the same department as

SCOTT. Exclude SCOTT (2) Give name of customers who are depositors having same branch city of mr. sunil. (3) Give deposit details and loan details of customer in same city where pramod is living. (4) Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary. (5) Give names of depositors having same living city as mr. anil and having deposit amount greater than 2000 (6) Display the last name and salary of every employee who reports to ford. (7) Display the department number, name, and job for every employee in the Accounting department.

TEXT BOOKS

1. Database Management Systems: Raghu Ramakrishnan
2. ORACLE PL/SQL Programming – Scott Urman BPB Publications.

REFERENCES

1. Database Systems Concepts – Henry F Korth, Abraham Silberschatz.
2. Database Management Systems – Alexis Leon, Mathews Leon – Leon, Vikas Publications

Session Plan

Session 1

I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Session 2

I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Case Studies

Case Studies