DBMS


  dbms_assgn1.pdf (Size: 51.51 KB / Downloads: 3)
A database is being constructed for storing sales information system. A
product can be described with a unique product number, product name, selling
price, manufacturer name. The product can sale to a particular client and each client
have it own unique client number, client name, client addresses, city, pin code, state
and total balance to be required to paid. Each client order to buy product from the
salesman. In the order, it has unique sales order number, sales order date, client
number, salesman number (unique), billed whole payment by the party or not and its
delivery date. The salesman have the name, addresses, city, pin code, state, salary of
the sales man, delivery date, total quantity ordered, product rate.



Write the SQL queries for the following –

(a) Find all the students’ name, city, course allotted from the CSE department.
(b) List the total number of Faculty in the CSE department.
© List the available courses from the CSE department.


Bank Database :

A bank database keeps record of the details of customers, accounts, loans and
transactions such as deposits or withdraws. Customer record should include
customer id, customer name, address, age, contact number, email id etc.,
accounts details involves account number, account type(fixed account, savings
account, monthly account etc), date of creation of the account. Transaction detail
keeps information about amount deposited or withdrawn to/from a particular
account and the date of transaction. The database should also store record of
loans which include loan amount, loan date and the account number to which the
loan is granted.


Library information system :

Database should store information about books, journals, megazines etc.
Searching for books can be done by author, title, subject. Similarly journals can
be searched by subject area, publisher etc. It should also be possible to see which
book is issued to which student and belonging department.


Hospital information system:

Patients - indoor/outdoor, medicines/lab tests(including results) prescribed to
patients, information if a patient if referred to other expert/hospital. Doctors -
specialization, patients attended etc. Different wards/beds and patients alloted to
them etc.