Duplicate Entries of Parts by Itself
Added on - 16 Sep 2019
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 duplicateentries 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' (usesname >= '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 theone(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 manageand/orwork onmultiple projectsEach project is worked on byone or more profsN-NN-NManagesProfs canmanageand/or work onmultiple projectsEach project is managed byone prof.N-1N-1Work_deptWorkcan be doneby Prof in one ormore deptsNone: single dept can havemultiple professorsN-NN-NRunsNone: assumingthere are profs whoare non-chairEvery dept will have a prof,who will run the dept.N-1N-1MajorNone: departmenthas grad students isassumedGrad students have one majordepartmentN-1N-1Work_projEvery project can beworked by one orAny student can work on oneor many projectsN-NN-N
more studentsAdvisor‘grad’ role: Everysudent (grad) willhave another seniorgrad student whowill advise himNot all grad students areseniorN-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-NManagesN-1Manages: PK should be (pid)Project, newcolumn managerFK(manager)referencesprofessorWork_deptN-NRunsN-1runs: PK is (dno)MajorN-1(no relationship tablespecified)Graduatesalready hascolumn majorFK (major)refrencesdeptsWork_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 rowsfor single pid, but in signle column PK, only one row will be allowed to one pid. So by single columnprimary key (i) is disallowed.