slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Assignment #3 due Wed., Nov. 14, 2012 1. (30) Exercise 7.17 on Page 235 PowerPoint Presentation
Download Presentation
Assignment #3 due Wed., Nov. 14, 2012 1. (30) Exercise 7.17 on Page 235

Loading in 2 Seconds...

play fullscreen
1 / 5

Assignment #3 due Wed., Nov. 14, 2012 1. (30) Exercise 7.17 on Page 235 - PowerPoint PPT Presentation


  • 192 Views
  • Uploaded on

Assignment #3 due Wed., Nov. 14, 2012 1. (30) Exercise 7.17 on Page 235 Show the result of the following queries as they would apply to the database of Figure 7.6 (using the figures available on my home page). Retrieve the name and address of all employees who

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Assignment #3 due Wed., Nov. 14, 2012 1. (30) Exercise 7.17 on Page 235' - walter


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Assignment #3

  • due Wed., Nov. 14, 2012
  • 1. (30) Exercise 7.17 on Page 235
  • Show the result of the following queries as they would apply to the database of Figure 7.6 (using the figures available on my home page).
  • Retrieve the name and address of all employees who
  • work for the ‘research’ department:

RESEARCH_DEPT  DNAME = ‘Research’(DEPARTMENT)

DNUMBER = DNO EMPLYEE)

RESEARCH_EMPS  (RESEARCH_EPT

RESULT  FNAME, LNAME, ADDRESS(RESEARCH_EMPS)

Yangjun Chen ACS-3902

slide2

For every project located in ‘Stafford’, list the project number, the controlling

  • department number, and the department manager’s last name, address, and the
  • birthdate.

STAFFORD_PROJS  PLOCATION = ‘Stafford’(PROJECT)

DNUM=DNUMBER DEPARTMENT)

CONTR_DEPT  (STAFFORD_PROJS

MGRSSN=SSN EMPLOYEE)

PROJ_DEPT_MGR CONTR_DEPT

RESULT  PNUMBER,DNUM,LNAME,ADDRESS,BDATE(PROJ_DEPT_MGR)

Yangjun Chen ACS-3902

slide3

(C) Retrieve the names of employees who have no dependents.

ALL_EMPS  SSN(EMPLOYEE)

EMPS_WITH_DEPS  ESSN(DEPENDENT)

EMPS_WITHOUT_DEPS  (ALL_EMPS – EMPS_WITH_DEPS)

RESULT  LNAME, FNAME(EMPS_WITHOUT_DEPS * EMPLOYEE)

Yangjun Chen ACS-3902

slide4

2. (30) Exercise 7.18 on Page 235

  • Specify the following queries on the database schema shown in Figure 7.5, using the relational operators defined in the relational algebra. Also show the result of each query as it would apply to the database of Figure 7.6. (Using the figures available on my home page).
  • a. Retrieve the name of all employees in department 5 who work more than 10 hours per week on the ‘Product X’ project.
  • b. List the names of all employees who have a dependent with the same first name as themselves.
  • c. Find the names of all employees who are directly supervised by ‘Franklin Wong’.
  • d. For each project, list the project name and the total hours per week (by all employees) spent on that project.
  • e. Retrieve the names of all employees who work on every project.

Yangjun Chen ACS-3902

slide5

(in third edition)

  • 3. (20) Exercise 7.22 on Page 237
  • Consider the relation
  • CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode,
  • Room#, TimePeriod, Weekdays, CreditHours).
  • This represents classes taught in a university, with unique Univ_Section#. Identify what you think should be various candidate keys, and write in your own words the constraints under which each candidate key would be valid.
  • 4. (20) Exercise 7.27 on Page 239
  • Consider the two tables T1 and T2 shown in Figure 7.21 (available on my home page). Show the results of the following operations:
  • a. T1 T1.p=T2.AT2 b. T1 T1.Q=T2.BT2
  • c. T1 T1.P=T2.AT2 d. T1 T1.Q=T2.BT2
  • e. T1  T2 f. T1 (T1.P=T2.A and T1.R=T2.C)T2

Yangjun Chen ACS-3902