X hits on this document

48 views

0 shares

3 / 11

:

# Sample Problems

1. Design an ER-diagram for the following Video Store Database

Customers of the video store are assigned a unique customer number when they make their first rental. In addition to the customer number, other information such as name and address is also collected. Each video cassette that the store owns   is identified by a unique code. Thus, if the store owns several copies of the same video, each copy has a unique identification code. Other information about a video includes the date of purchase and the number of times the video was rented. When a customer selects a video to rent, the store needs to record this transaction, including the date and time the video was rented. It is not unusual for a customer to rent several videos when they visit the store. The store assigns a   unique identifier to each movie title. For example, the Jame Bond movie "Goldfinger" is assigned the identifier ADV234.

The store may have several cassettes for this movie title. Other information on movies includes a title and the year made.

Each movie title is associated with a list of actors and one or more directors. The store has a unique internal code they  use to identify each actor. In addition, the store has a different set of internal codes it uses to identify each director. In addition to the actor and director identification codes, other biographic information on actors and directors is stored.

Using this information, the store can easily find out all the movies a specific actor has appeared in.

2. Assume you have the database

Employee (ssn, dno)

Dept_Location (dnum, dlocation)

Project (dnum, pnumber, pname, plocation, Product, Reorganization)

Works_On (essn, pno, hours)

Project (pnumber) and Works_On (pno)

Dependent(essn, dependent_name, bdate, sex, relationship,Spouse)

Simple Queries

Q1: select all employee SSNs.

Q2: Retrieve the salary of every employee.

Q3: Retrieve the names of all employees who do not have supervisors.

Q4: Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’.

Q5: Retrieve the SSNs and names of all employees who work on project number 1, 2, or 3.

Q6: Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, alphabetically by last name, first name.

Q7: Retrieve the name and address of all employees who work for the ‘Research’ department.

Q8: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.

Q9: Retrieve the name of each employee who works on all the projects controlled by department number 5.

Q10: Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

Q11: Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.

Q12: Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department.

Q13: Retrieve the total number of employees in the company.

Q14: Retrieve the total number of employees in the ‘Research’ department.

Q15: For each department, retrieve the department name, the number of employees in the department, and their average salary.

 Document views 48 Page views 48 Page last viewed Sat Jan 21 01:00:22 UTC 2017 Pages 11 Paragraphs 265 Words 2948