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
Department (dnumber,ssn,,dname, mgrssn, mgrstartdate. Headquarters, Administration)
Employee (ssn, dno)
Dept_Location (dnum, dlocation)
Project (dnum, pnumber, pname, plocation, Product, Reorganization)
Employee (ssn, fname, minit, lname, bdate, address, sex, salary, superssn, dno)
Works_On (essn, pno, hours)
Project (pnumber) and Works_On (pno)
Dependent(essn, dependent_name, bdate, sex, relationship,Spouse)
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.