Q1 Consider the following information about a university database: · Professors have an SSN, a name, an age, a rank, and a research secialty! · Pro"ects have a ro"ect number, a sonsor name, a starting date, an ending date, and a budget! · #raduate students have SSN, a name, an age, and a degree rogram $e!g!, %!S! &r Ph!'!(! · )ach )ach ro" ro"ec ectt is manag managed ed by one one rof rofes esso sorr $kno $known wn as the the ro" ro"ect ect*s *s rin rinci cial al investigator(! · )ach ro"ect is worked on by one or more rofessors $known as the ro"ect*s coinvestigators(! · Professors can manage and+or work on multile ro"ects! · )ach ro"ect is worked on by one or more graduate students $known as the ro"ect*s research assistants(! · hen graduate students work on a ro"ect, a rofessor must suervise their work on the ro"ect! #raduate studentscan work on multile ro"ects, in which case they will have a $otentially different( suervisor for each one! · 'eartments have a deartment number, a deartment name, and a main office! · 'eartments have a rofessor $known as the chairman( who runs the deartment! · Professors work in one or more deartments, and for each deartment that they work in, a time ercentage is associated with their "ob! · #raduate students have one ma"or deartment in which they are working on their degree! Create an )-+))- diagram that fulfils the above re.uirements and motivate your suggestions! suggestions! /he e0ercise will be assessed both according according to correct )-+)))-+))- synta0 synta0 and whether you have made a good design! Solution: dditional comments+assumtions: · 2t has been assumed that sets of students and rofessors are dis"oint! · 3orks 3orks on4 is a ternary relation! /his allows for relating a articular student to a articular suervisor in the conte0t of a articular ro"ect! · 2t has been assumed that one rofessor can be a head of at most one deartment!
· )very )very deartm deartment ent has its chairman, chairman, and at least least one rofesso rofessorr $the $the chairma chairman( n( emloyed, thus two total relationshis $double lines(! · )ach ro"ect has a rincial investigator, one or more co5investigators and students working on it, therefore total articiation relationshis! · )very student has a ma"or deartment she+he works in, but it*s not clear whether the students work in every deartment, thus in the 3orks at4 relationshi a double line from 3Student4 side and a single one from the deartment side!
Q6 'esign a database to kee track of information information for an art museum! ssume ssume that the following re.uirements were collected! /he museum has has a collection collection of -/7 -/7&89)C &89)C/S /S ! )ach art ob"ect has a uni.ue • 2'No 2'No,, and and rtist rtist,, if know known, n, a ear $whe $when n create created, d, if know known( n( a /itle itle and and a 'escrition! /he art ob"ects are categori;ed in several ways, as discussed below! below! -/7&89) -/7&89)C/S C/S are categori;ed categori;ed based on tyes! tyes! /here are three three main tyes, • Painting, Painting, Sculture Sculture and Statue, Statue, lus an <&ther* category category for those that don*t fit into one of the categories above!
•
•
• •
•
•
•
•
P2N/2N# has a Paint/ye $oil, watercolor, etc( a material on which it is Crawn&n $aer, canvas, wood( and Style $modern, abstract etc( SC=>P/=-) SC=>P/=-) or a S/ S//=) has a %aterial %aterial from which it was created $wood, $wood, stone, etc( ?eight, eight eight and Style! St yle! n art ob"ect in the &/?)- category has a /ye$rint, /ye$rint, hoto, etc( and Style! -/7&89)C/S are also categori;ed as P)-%N)N/7C&>>)C/2&N, which are owned by the museum $'atec.uired, whether it is &n'islay or Stored and Cost( or 8&--&)', which has information on the Collection $where it was borrowed from(, 'ate8orrowed, and 'ate-eturned! -/7&89) -/7&89)C/S C/S also have information information describing describing their country5cu country5culture lture using information on country+culture of &rigin $2talian, )gytial, merican, 2ndian etc( and Period$-enaissance, %odern, ncient( ncient( /he museum kees track of -/2S/S*s information, if known: Name, 'ate8orn, 'ate'ied, 'ate'ied, Country&f&rig Country&f&rigin, in, Period, Period, %ainStyle and 'escrition! 'escrition! /he name is assumed uni.ue! 'ifferent )@?282/2&NS occur, each having a Name, Start'ate and )nd'ate! )@?282/2&NS are related to all the art ob"ects that were on dislay during the e0hibition! 2nformation is ket on other C&>>)C/2&NS with which the museum interacts, including Name $uni.ue(, /ye $museum, ersonnel etc(, 'escrition, ddress, Phone and ContactPerson!
'raw an ))- schema for this alication! -ecord any assymtions you make! Solution:
A