DATABASE MANAGEMENT SYSTEMS - Assignments

 

 

DATABASE MANAGEMENT SYSTEMS -Practical and Theory Assignments Are Given Below.

Theory Assignment

1)    Design an ER diagram that models the following objects and relationship in the world of football : teams, players, games, managers, contracts
•    Each team has a unique team name, city it plays in
•    Each person being a part of team has unique id and name
•    For player their weight, height, position and birth date are of importance.
•    Players have a contract with at most one team and receive a salary for their services and team have at least  24 and at most 99 players under contract.
•    Each team has one to three managers, managers can work for at most 4 team and receive a salary for each of their employment.
•    Players can’t be manager.
•    A game involves a home team and visiting team additionally the day of game and the score of game are of importance.
•    Teams play with each other several times in a season.
•    Moreover for each game played we would like to know which player participated in the game and how many minutes they played.

Also indicate the cardinalities for each relationship types.


2)    Construct an ER Diagram for a company database . consider that the database of company keeps tract of the following information.
•    Employees which includes empid, empname ,address, gender, date of birth, age, date of joining.
•    Department which includes deptid, deptname, deptlocation
•    Project which includes projid, projname, projlocation.
•    One employee can belong to only one dept. but can work in multiple projects.
•    One dept can have any numbers of employees and number of employees can be involved in one project.
•    Multiple projects are going on in one dept and one project can be done in number of dept.
•    Also show that each dept is managed by a manager who is one amongst the employees.

3) Solve Question papers of last two year GTU exam.

PRACTICAL 

ASSIGNMENT I

Create  following tables:

emp(eno,ename,title)
pay(title,salary)
proj(pno,pname,location,budget)
assign(eno,pno,role,duration)

Write an SQL statement for followings:
1)    Add a column name doj(date of joining) in emp table.
2)    Make eno of emp table, title of pay table, pno of project table and eno,pno of assign table  primary key.
3)    Make title in eno table and eno, pno in assign table foreign key.
4)    Set rule for salary in pay table ( salary > 10000).
5)    Set constraint for ename of emp table ie ename must not null.
6)    Set unique constraint for pname of proj table
7)    Set default value for salary which is 12000.
8)    List structure of each table after they are created
9)    Drop a column named doj from emp table.
10)    Set a constraint for budget i.e .budget must be positive value.

Assignment II

     Create the following Databases.
    
     Salesmen
     SNUM      SNAME               CITY           COMMISSION
     -------------------------------------------------------
     1001      Piyush              London              12 %
     1002      Sejal               Surat               13 %
     1004      Miti                London              11 %
     1007      Rajesh              Baroda              15 %  
     1003      Anand               New Delhi           10 %
    
     HERE,
     SNUM      : An uique number assigned to each salesman.
     SNAME     : The name of salesman should not null.
     CITY      : The location of salesmen.
     COMMISSION: The Salemen's commission on orders.
    ======================================================================
    ======================================================================
     Customers
    
     CNUM      CNAME               CITY      RATING    SNUM
     -------------------------------------------------------
     2001      Harsh               London      100     1001
     2002      Gita                Rome        200     1003
     2003      Lalit               Surat       200     1002
     2004      Govind              Bombay      300     1002
     2006      Chirag              London      100     1001
     2008      Chinmay             Surat       300     1007
     2007      Pratik              Rome        100     1004

     HERE,
     CNUM   : A unique number assigned to each customer.
     CNAME  : The name of the customer.
     CITY   : The location of the customer.
     RATING : A level of preference indicator given to this customer.
     SNUM   : The number of salesman assigned to this customer.
    =======================================================================
    =======================================================================
     Orders
     ONUM      AMOUNT         ODATE          CNUM      SNUM
     -------------------------------------------------------
     3001       18.69         10/03/97       2008      1007
     3003      767.19         10/03/97       2001      1001
     3002     1900.10         10/03/97       2007      1004
     3005     5160.45         10/03/97       2003      1002
     3006     1098.16         10/03/97       2008      1007
     3009     1713.23         10/04/97       2002      1003
     3007       75.75         10/04/97       2004      1002
     3008     4723.00         10/05/97       2006      1001
     3010     1309.95         10/06/97       2004      1002
     3011     9891.88         10/06/97       2006      1001

     HERE,
     ONUM   : A unique number assigned to each order.  
     AMOUNT : The amount of an order.
     ODATE  : The date of an order.
     CNUM   : The number of customer making the order.
     SNUM   : The number of salesman credited with the sale.
   =======================================================================
   =======================================================================

Assignment III

Insert at least 10 records in each table you have created in assignment I and II.

Assignment IV

For tables of Assignment II solve following queries in SQL

1.  Produce the order no, amount and date of all orders.

2.  Give  all  the information about all the  customers  with  salesman

    number 1001.

3.  Display the following information in the order of city, sname,  snum

    and commission.

4.  List of rating followed by the name of each customer in Surat.

5.  List of snum of all salesmen with orders in order table without  any

    duplicates.

6.  List of all orders for more than Rs. 1000.

7.  List of names and cities of all salesmen in London with  commission

    above 10%.

8.  List all customers excluding those with rating <= 100  unless  they

    are located in Rome.

9.  List all orders for more than Rs.1000 except the orders of snum<1006 nbsp="" p="">

    of 10/03/97.

10. List all orders taken on October 3rd or 4th or 6th, 1997.

11. List all customers whose names begins with a letter 'C'.

12. List all customers whose names begins with lettter 'A' to 'G'.

13. List all orders with zero or NULL amount.

14. Find out the largest orders of salesman 1002 and 1007.

15. Count all orders of October 3, 1997.

16. Calculate the total amount ordered.

17. Calculate the avarage amount ordered.

18. Count the no. of salesmen currently having orders.

19. Find the largest order taken by each salesman on each date.

20. Find the largest order taken by each salesman on 10/03/1997.

21. Count the no. of different non NULL cities in the Customer table.

22. Find out each customer's smalles order.

23. Find out the first customer in alphabetical order whose name  begins

    with 'G'.

24. Count the no. of salesmen registering orders for each day.

25. List all salesmen with their % of commission.

26. Display the no. of orders for each day in the decending order of the

    no. of orders in the following format.

    FOR dd-mon-yy, there are __ Orders.

27. Assume  each salesperson has a 12% commission. Write a query on  the

    order table that will produce the order number, salesman no and  the

    amount of commission for that order.

28. Find the highest rating in each city in the form :

    For the city (city), the highest rating is : (rating)

29. List all in descending order of rating.

30. Calculate the total of orders for each day and place the result  in

    descending order.

31. Show the name of all customers with their salesman's name.

32. List all customers and salesmen who shared a same city.

33. List all orders with the names of their customer and salesman.

34. List  all orders by the customers not located in the same  city  as

    their salesman.

35. List  all customers serviced by salespeople with  commission  above

    12%.

36. Calculate the amount of the salesman commission on each order by  a

    customer with rating above 100.

37. Find  all  pairs  of customers having  the  same  rating  with  out

    duplication.

38. Company policy is to assign each salesperson three customers,one  at

    each of the three ratings. Display all possible combination of  such

    three customers.

39. List  all  customers located in cities  where  salesman  Sejal  has

    customers.

40. Find  all pairs of customers served by a single salesman  with  the

    salesman's name and no.

41. List all salesmen who are living in the same city with out duplicate   

    rows.

42. List all pairs of orders by a given customer with customer name.

43. Produce the name and city of all the customers with the same  rating

    as Harsh.

44. Extract all orders of Miti.

45. Extract all orders of Baroda's salesmen.

46. Find all orders of the salesman who services 'Harsh'.

47. List all orders that are greater than the average of October 4,1997.

48. Find the avarage commission of salesmen in London.

49. Find all orders attributed to salesmen in 'London' using  both  the

    subquery and join methos.

50. List the commission of all salesmen serving customers in 'London'.