Duplicate Entries of Parts by Itself
5 Pages1012 Words223 Views
Added on 2019-09-16
Duplicate Entries of Parts by Itself
Added on 2019-09-16
ShareRelated Documents
Problem-1a. Find parts (pids, pnames and colors) that are supplied by all suppliers.In this part we see divide operator will be good to work with a it will remove all the duplicate entries of parts by itself. The query becomes-((sid,pidCatalog)/(sidSuppliers)) Partsb. Find parts (pids, pnames and colors) that are supplied by all suppliers with names starting with 'A' (use sname >= 'A' and sname < 'B' for RA, LIKE for SQL).(sid,pidCatalog)/(sidname >= ‘A’ ^ name<’B’Suppliers) Partsc. Find employees (eid and salary) certified for all aircraft of cruising range under 2000.Eid,salary(((eid,aidCertified)/(aidcruisingrange<2000Aircraft)) Employees)d. Among employees (eid and salary) certified for all aircraft of cruising range under 2000, find the one(s) with maximal salary (SQL only)Problem-33.a – RelationshipDesign Point for left-to-right or top-to-bottom DirectionDesign Point for right-to-leftor bottom-to-top DirectionConclusion fromdesign points: N-1 or N-N?From E-Rdiagram: N-1 or N-N?Work_inProfs can manage and/or work onmultiple projectsEach project is worked on by one or more profsN-NN-NManages Profs can manageand/or work on multiple projectsEach project is managed by one prof.N-1N-1Work_deptWork can be done by Prof in one or more deptsNone: single dept can have multiple professorsN-NN-NRunsNone: assuming there are profs who are non-chairEvery dept will have a prof, who will run the dept.N-1N-1MajorNone: department has grad students is assumedGrad students have one majordepartmentN-1N-1Work_projEvery project can be worked by one or Any student can work on one or many projectsN-NN-N
more studentsAdvisor‘grad’ role: Every sudent (grad) will have another senior grad student who will advise himNot all grad students are seniorN-1N-13.b – RelationshipN-1 or N-NIf N-1, name of relationshiptable and corrected PK for it(one column only: give columnname)If N-1, Table toadd column andFK to, to avoidrelationshiptable, and whatcolumn to addIf N-1, FKconstraintadd to thattableWork_inN-NManages N-1Manages: PK should be (pid)Project, new column managerFK (manager) references professorWork_deptN-NRunsN-1runs: PK is (dno)MajorN-1(no relationship table specified)Graduates already has column majorFK (major) refrences deptsWork_projN-NAdvisorN-1Advisor: PK is (grad_ssn)3.c – The two column PK, which is made up of two different column in the table, allows multiple rows for single pid, but in signle column PK, only one row will be allowed to one pid. So by single column primary key (i) is disallowed.
End of preview
Want to access all the pages? Upload your documents or become a member.