经典案例 > 数据库代写 >

Homework Assignment 2

CS425 - Database Organization


Instructions

  • Try to answer all the questions using what you have learned in class

  • When writing a query, write the query in a way that it would work over all possible database instances and not just for the given example instance!

  • After creating the relations, you can make up some data and insert them into the relations

  • Please submit the homework (as a single sql file your_name.sql) electronically using blackboard

  • In the your_name.sql file, make sure you have marked the answer for each question using comments

  • All the sql scripts should be executable in Oracle 11g (e.g., on fourier) or recent versions Consider the following database schema and example instance:


    Employee

    cid

    cname

    city

    state

    A101

    Chase

    New York

    NY

    A102

    Citi

    New York

    NY

    A103

    TD Bank

    Boston

    MA

    A104

    Moto

    Chicago

    IL

    A105

    Disney

    Orlando

    FL

    . . .

    . . .

    . . .

    . . .

    Customer


    eid

    ename

    dob

    salary

    0011

    Chris

    01/03/1986

    100,000

    0012

    Edward

    07/24/1973

    120,000

    0013

    Anna

    08/12/1992

    80,000

    0014

    Kim

    08/26/1986

    95,000

    0015

    Carol

    04/15/1988

    97,000

    . . .

    . . .

    . . .

    . . .


    Product


    pid

    pname

    unit_price

    C17811

    Desk

    500

    C17812

    Chair

    200

    C23453

    PC

    1,200

    C34451

    Mac

    1,300

    . . .

    . . .

    . . .


    Sales

    eid

    cid

    pid

    quantity

    0011

    A101

    C17811

    700

    0011

    A102

    C17812

    200

    0012

    A102

    C23453

    100

    0013

    A101

    C34451

    50

    . . .

    . . .

    . . .

    . . .

    Manages


    eid1

    eid2

    0011

    0013

    0013

    0017

    0012

    0013

    . . .

    . . .


    Hints:


  • Underlined attribute(s) form the primary key of a relation.

  • Relation Sales stores the sales information: the salesman (employee ID) who is in charge of the ordered product, the product ID, the customer ID, and the quantities of ordered products.

  • The attributes eid, cid and pid of relation Sales are the foreign keys to relations Employee (salesman),

    Customer and Product, respectively.

  • Relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).

  • All the IDs’ are strings.


Part 2.1 SQL DDL (Total: 20 Points) Question 2.1.1 (15 Points)

Write the SQL statements for all the five relations: Employee, Customer, Product, Sales, and Manages. Note:

  1. An appropriate data type should be used for each attribute, and (2) All the foreign keys should be created.

    Question 2.1.2 (5 Points)

    Write an SQL statement that adds a constraint to the Employee relation to make sure that the salary attribute cannot be NULL, and the value of this attribute has to be between 40,000 and 500,000. Furthermore, the default value for this attribute should be 80,000.

    Part 2.2 SQL Queries (Total: 56 Points) Question 2.2.1 (7 Points)

    Write an SQL query that returns the products (pid and pname) which are sold by the direct manager(s) of employee ‘0013’ and the quantity for any customer (not aggregated) is greater than 300. Hint: relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).

    Question 2.2.2 (7 Points)

    Write an SQL query that returns the IDs of all the products, with the total ordered quantity in ‘Chicago’ greater than the total ordered quantity in ‘Boston’.

    Question 2.2.3 (7 Points)

    Write an SQL query that returns the name and unit price of all the products (pname and unit_price) which are never ordered by ‘Disney’.

    Question 2.2.4 (7 Points)

    Write an SQL query that returns all the employees and their average sales amount (of all the customers) for each of the products (query result: eid, pid and avg_sales).

    Question 2.2.5 (7 Points)

    Write an SQL query that returns all the employees and the total number of employees directly managed by each employee (if no one is directly managed, then return 0). Hint: relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).

    Question 2.2.6 (7 Points)

    Write an SQL query that returns the number of products where the average ordered quantity (of all the employees and customers) is lower than 1000.

    Question 2.2.7 (7 Points)

    Write an SQL query that returns the product names and the name of the customer(s) who ordered the highest quantity (from all the employees). Query result: pname and cname.

    Question 2.2.8 (7 Points)

    Write an SQL query which returns all the employees’ IDs and their managed employees’ IDs. Hint: the result should include not only directly managed employees but also indirectly managed employees.

    Part 2.3 SQL Updates (Total: 24 Points) Question 2.3.1 (5 Points)

    Delete all the customers without placing any order.


    Question 2.3.2 (4 Points)

    A new product Telephone is added to the warehouse (available for sale). The unit price is $100. Add the information to the Product relation. Assume that pid is automatically maintained by the system.

    Question 2.3.3 (8 Points)

    Update the unit_price in the Product relation according to this rule:


    • if it is negative, set it to 0

    • if it is larger than 10,000, then set it to 2,500

    • if it is NULL, set it to 1,000

    • if none of the above applies do not change the unit_price


Note that we expect you to write a single statement that implements this.

Question 2.3.4 (7 Points)

Update the salaries of employees as his/her current salary + 0.02 · (his/her total sales amount).

51作业君 51作业君

扫码添加客服微信

点击跳转淘宝店