Design an ER diagram for an ABC IT Training Institute that will meet t he  training  needs for
individuals and employees of corporate offices. Clearly indicate the entities, relationships,
cardinality and the key constraints. The description of the environment is as follows:
The Institute offers  5 advanced courses of 3 months duration each. The Institute has 20  faculty
and can handle upto 40  trainees  per  batch.  The training will be conducted batch wise. They can
accommodate maximum 5 batches per day (2 batches in the pre-lunch session and 3 batches in
the post-lunch session). The student can register upto 2 courses simultaneously. Training
consists of theory and practicals. Theory and practicals are scheduled on alternate days. Each
batch is assigned a faculty member who takes theory sessions as  well as practical sessions.
Sunday is holiday for everyone. A test will be conducted per course every week to continuously
evaluate the performance of the student. The question paper will be set by the faculty concerned
whoever is teaching the batch. The result/grade will be declared at the end of the third month
after conducting course-end exam. 

Write and run the following SQL queries on the tables:

a) Find all the product_id’s and names whose manufacturer is LEO company.

ans. select pid, name from product where pid in(select pid from manufactures where mid in(select mid from manufacturer where name=’leo’) );

b) Find all the Supplier details who supplies police_car toy.

Ans. select name from supplier where sid in(select sid from supplies where pid=(select pid from product where nae=’police car’));

c) Write a SQL statement to insert a new product with pid=-1, name='my product', and min_age=3 into       the Product table.

ans. insert into product values(1,’my product’,3);

d) List the ids and names of all products whose inventory is below 10.

ans. select pid , name from product where pid in(select pid from inventory where stock<30);

e) List the ids and names of all suppliers for products manufactured by "TRIKA". The id and name of each supplier should appear only once.

ans. select distinct(sid),distinct (name) from from supplier where sid in(select sid from supplies where pid in(select pid from manufactures where mid in(select mid from manufacturer where name=’trika’)));

f) List the ids, names, and number in stock of all products in inventory. Order the list by decreasing number in stock and decreasing product ids.

Ans. selct pid,name,stock from product ,inventory where product.pid=inventory.pid order by stock desc;

g) List the ids and names of all products for whom there is only one supplier.

Ans. Select pid , name from product where pid in(select pid from supplies where sid in(select distinct (sid)from supplier);

h) Find the ids and names of the products with the lowest inventory. Do NOT assume these are always products with an inventory of zero.

Ans. Select pid, name from product and supplier where product.pid=supplier.pid having min(stock);

i) List the id and name of each supplier along with the total number of products it supplies.

Ans. Select id ,name, count(pid) from supplier ,product;

j) Find the id and name of the manufacturer who produces toys on average for the youngest children.

Ans. Select mid,name,  from manufacturer where mid=(select mid from manufacturer where pid =(select pid from product having avg(min_age)));

Answer the following simple queries in SQL.

                        a) Find name of supplier for city = “MUMBAI”.

                        Ans. select * from supplier where city=”MUMBAI”;

                        b) Find suppliers whose name start with “AD”

                        Ans. select * from suppliers where sname like AD%;

                        c) Find all suppliers whose status is 10, 20 or 30.

                        Ans. select * from suppliers where status in(10,20,30);

                        d) Find total number of city of all suppliers.

                        Ans. select count(city) from suppliers;

                        e) Find s# of supplier who supplies ‘BLUE’ part.

                        Ans.  select s# from sp where p# in (select p# from parts where color =”BLUE”);

                        f) Count number of supplier who supplies ‘BLUE’ part.

                        Ans. select count(*) from sp where p# in (select p# from parts where color =”blue”);

                        g) Sort the supplier table by sname.

                        Ans. select * from suppliers order by sname desc;

                        h) Delete records in supplier table whose status is 40.

                        Ans delete from suppliers where status =40;

i) Find name of parts whose color is ‘red’

Ans. select pname from parts where color=’red’;

j) Find parts name whose weight less than 10 kg.

Ans. select pname from  where weight<10;

k) Find all parts whose weight from 10 to 20 kg.

Ans. select pname from parts where weight between 10 and 20

l) Find average weight of all parts.

ans. select avg(weight) from suppliers;

m) Find S# of supplier who supply part ‘p2’

Ans.  select s# from sp where p#=(select p# from parts where pname=”p2”);

n) Find name of supplier who supply maximum parts.

Ans. select name from supplier where s#=(select s# from sp having max(quantity));

o) Sort the parts table by pname.

Ans. select * from parts order by pname desc;

Ans.

1.    student

Column	Data type	Description
Id	Int	Auto increment
Student_name	Varchar(25)	student name, unique
Password	Varchar(25)	Password
Address	Varchar(25)	Address
City	Varchar(25)	City
State	Varchar(25)	State
Phone	Varchar(25)	Phone number
Email	Varchar(25)	Email

1.    Faculty

Column	Data type	Description
F_Id	Int	Auto increment
Full_name	Varchar(25)	Name
Subject	Varchar(25)	subject
Address	Varchar(25)	Address
Phone	Varchar(25)	Phone number
Email	Varchar(25)	Email

2.    courses

Column	Datatype	Description
course_id	Int	Auto increment
Course_name	Varchar(25)	User name, unique
Duration	Varchar(25)	Duration
Fee	Int	Course fee

3.    Evaluation

Column	Datatype	Description
F_id	Int	Auto increment
Eval_teacher	Varchar(25)	Teacher name evaluating
Marks_project	Int	Project marks
Marks_viva	Int	Viva marks
Marks_theory	Int	Theory marks

Ans. DataBase Management System

A Database management system is a computerized record-keeping system. It is a repository or a container for collection of computerized data files. The overall purpose of DBMS is to allow he users to define, store, retrieve and update the information contained in the database on demand. Information can be anything that is of significance to an individual or organization.

All the possible applications of a Database Management System in any IGNOU'S Regional Center :-

(i)        Database is used to store students details in a place

We know Database is used for storing Data and Information.In IGNOU'S Regional Center all data and Information  related to student , staff and employees are stored from where we can access data and Information , update and also Delete the Data and Information.

(ii)        Easy Query processing and management

            From DBMS we can easily generate Query and we can easily manage it. And we can easily our database.

(iii)        Easy to understand and user friendly

The language we use in DBMS is easily understandable. Query Statements like SELECT, DELETE & UPDATE is very easy for users.Because we use a  simple english words or Statements.

(iv)           Store Students marks evaluation details

We easily get our Evaluatiom marks details that is stored in DBMS of Regional Center applications that is easily available for Enrolled Students.

(v)        Store Employee details.

If we want to Know the details about the any of the  Employee then we can easily get from the DBMS that is already stored in DBMS .To know which Employee is responsible for the which work that is assigned by Administrator. 

(vi)      Store final year project Submission.

In IGNOU programmed application the students have to create Project on any topics and the project is Submitted by Students in Regional Center.If student want to see the details of submitted project , they get from there.

(vii)     Easy to implement Security and integrity of data

DBMS has four methods to maintain data integrity: normalizing data, defining business rules, providing referential integrity and data validation. Data normalization is a process where unnecessary dependencies and repeating groups of data are eliminated. Business rules for data access control data handling and sharing between applications. Referential integrity is used to protect data from corruption. Data validation uses multiple processes to validate data in the application.

(viii)    Fast access of information

In DBMS, all data of an organization is integrated into a single database file. The data is recorded in only one place in the database and it is not duplicated.Thorough which we can access of Information fast

(ix)      Data sharing in other department of IGNOU.

In DBMS, data can be shared by authorized users of the organization. The database administrator manages the data and gives rights to users to access the data. Many users can be authorized to

access the same piece of information simultaneously. The remote users can also share same data. Similarly, the data of same database can be shared between different application programs.

(x)       Better data accuracy

By controlling the data redundancy, the data consistency is obtained. If a data item appears only once, any update to its value has to be performed only once and the updated value is immediately available to all users. If the DBMS has controlled redundancy, the database system enforces consistency and accurate.