1、第一套試卷8. Consider the following information about a university database:Professors have an SSN, a name, an age, a rank, and a research specialty.Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.Graduate students have an SSN, a name, an age, and

2、 a degree program (e.g., M.S. o Ph.D.).Each project is managed by one professor (known as the project prsindpal investigator).Each project is worked on by one or more professors (known as the project S co-i nvestigators).Professors can manage and/or work on multiple projects.Each project is worked o

3、n by one or more graduate students (known as the project s research assistants).When graduate students work on a project, a professor must supervise their work onthe project. Graduate students can work on multiple projects, in which case they willhave a (potentially different) supervisor for each on

4、e.Departments have a department number, a department name, and a m宙(oeDepartments have a professor (known as the chairman) who runs the department.Professors work in one or more departments, and for each department that they workin, a time percentage is associated with their job.Graduate students ha

5、ve one major department in which they are working on their degree Each graduate student has another, more senior graduate student (known as a student advisor) who advises him or her on what courses to take.Design and draw an ER diagram that captures the in formation about the universit yUse only the

6、 basic ER model here; that is, entities, relationships, and attributes. Besure to indicate any key and participation constraints9. Consider the university database from Exercise 8 and the ER diagram you designed. Write SQL statements to create the correspond!ng relations and capture as many of the c

7、onstraints as possible .If you cannot capture some constraints, explain why.Answer :1. create tableprofessors( prof_ssn char(10),name char(64),age integer,rank integer, specialitychar(64),primary key (prof ssn)2. create tabledepts( dno integer,dname char(64),offi ce char(10),primary key (dno)3. crea

8、te tableruns ( dno integer,prof_ssn char(10),primary key ( dno, prof ssn),foreign key (prof ssn) references professors,foreign key (dno) references depts )4. create tablework dept ( dno integer,prof_ssn char(10)tpc_ time integer,primary key (dno, protssn),foreign key (profssn) references professors

9、,foreign key (dno) references depts )observe that we would need check constraints or assertions in sql to enforce the rule that professors work in at least one department.5. create tableproject ( pid integer,sponsor char(32),start date char(20),end date char(20)fbudget float,primary key (pid)6. crea

10、te tablegraduates( grad ssn char(10),age integer,name char(64),deg prog char(32)fmajor integer,primary key (grad ssn),foreign key (major) references depts )note that the major table is not necessary since each graduate has only one majorand so this can be an attribute in the graduates table7. create

11、 table advisor ( senior ssn char(10),grad ssn char(10),primary key (senior ssn, grad ssn),foreign key (seniossn) references graduates ,foreign key (grad ssn)efeences graduates )8. create tablemanages( pid integer,prof ssn char(10),primary key (pid, prof ssn),foreign key (prof ssn) references profess

12、ors,foreign key (pid) references projects )9. create tablework in ( pid integer,prof ssn char(10),primary key (pid, prof ssn),foreign key (prof ssn) references professors fforeign key (pid) references projects )observe that we cannot enforce the participation constraint for projects in thework in ta

13、ble without check constraints or assertions in sql.10. create tablesupervises( prof ssn char(10),grad ssn char(10),pid integer,primary key (prof ssn, grad ssn, pid),foreign key (prof ssn) references professors(prof ssn),foreign key (grad ssn)efeences graduates(grad ssn),foreign key (pid) references

14、projects(pid)Note that we do not need an explicit(明確白勺)table for the Work Proj relation sinceeverytime a Graduate works on a Project, he or she must have a Supervisor.10. Consider the following relations:Student(snum: integer, sname: string, major: string, level: string, age: integer)Class(name: str

15、i g, meets at: string, room: string, ?d: integer)Enrolled(snum: integer, cname: string)Faculty(?d: integer, fname: string, deptid: integer)The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.Wri

16、te the following queries in SQL No duplicates should be printed in any of the answers Find the names of all Juniors (level = JR) who are enrolled in a class taught by I.Teach2. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.3. Find the nam

17、es of all classes that either meet in room R128 or have ?ve or more students enrolled.4. Find the names of all students who are enrolled in two classes that meet at the same time.5. Find the names of faculty members who teach in every room in which some class is taught.The answers are given below:1.

18、 select distinct s.snamefrom student s, class c, enrolled e, faculty fwhere s.snum = e.snum and ame = and c.?d = f.?d andf.fname = "teach ' and sevel =4jr '2. select max(s.age)from student swhere (s.major =' history Jor s.snum in (select e.snumfrom class c, enrolled e, facult

19、y fwhere ame = and c.?d = f.?dand f.fname = 'i.teach ')3. select from class cwhere c.room =7128 1or in (select amefrom enrolled e group byamehaving count (*) >=5)4. select distinct s.snamefrom student swhere s.snum in (select e1snumfrom enrolled e1, enrolled e2, class

20、c1, class c2where e1.snum = e2.snum and ame <> ame and ame = and ame = and c1meets at = c2.meets at)5 select distinct f.fnamefrom faculty fwhere not exists ( select *from class c )except(selectci .roomfrom class c1where c1.?d = f.?d )第二套試卷6 A company database needs to store info

21、rmation about employees (identi?ed byssn,with salary and phone as attributes), departments (identi?ed by dno, with dnameand budget as attributes), and children of employees (with name and age as attributes).Employees work in departments; each department is managed by an employee; a child must be ide

22、nti?ed uniquely by name when the parent (who is an employee; assumethat only one parent works for the company) is known. We are not interested ininformation about a child once the parent leaves the company.Draw an ER diagram that captures this information.7 Consider the scenario from Exercise 6, whe

23、re you designed an ER diagram for a company database.Write SQL statementsto create the corresponding relations and capture as many of the constrai nts as possible .If you cannot capture some constraints,explain why.answenthe following sql statements create the corresponding relations.create table em

24、ployees ( ssn char(10)fsal integer,phone char(13),primary key (ssn)create table departments ( dno integer,budget integer,dname char(20),primary key (dno)create table works in ( ssn char(10),dno integer,primary key (ssn, dno),foreign key (ssn) references employees,foreign key (dno) references departm

25、ents)create table manages ( ssn char(10),dno integer, primary key(dno),foreign key (ssn) references employees,foreign key (dno) references departments)create table dependents (ssn char(10)fname char(10),age integer,primary key (ssn, name),foreign key (ssn) references employees,on delete cascade )8.C

26、onsider the following relations:Student(snum: integer, sname: string, major: string, level: string, age: integer)Class(name: string, meets at: string, room: string, ?d: integer)Enrolled(snum: integer, cname: string)Faculty(?d: integer, fname: string, deptid: integer)The meaning of these relations is

27、 straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the classWrite the following queries in SQL No duplicates should be printed in any of the answers 1) . Find the names of faculty members for whom the combined enrollment of the courses

28、that they teach is lestshan ?ve.2) . For each level, print the level and the average age of students for that level.3) . For all levels except JR, print the level and the average age of students for that level.4) . For each faculty member that has taught classesonly in room R128, print the faculty m

29、ember Js name and the total number of classes she or he has taught.5) . Find the names of students enrolled in the maximum number of classes.6) . Find the names of students not enrolled in any class.1. select distinct f.fnamefrom faculty fwhere 5 > (select count (e.snum)from class c, enrolled ewh

30、ere = ame and c.?d = f.?d)2. select sevel, avg(s.age)from student sgroup by sevel3. select sevel, avg(s.age)from student swhere sevel <> jrgroup by sevel4. select f.fname, count(*) as coursecount from faculty f, class cwhere f.?d = c.?d group by f.?d,f.fn ame6 ，having every ( c.room =r1

31、28)5 select distinct s.snamefrom student swhere s.snum in (select e.snumfrom enrolled egroup by amehaving count (*) >= all (select count (*)from enrolled e2group by ame )6. select distinct s.snamefrom student swhere s.snum not in (select e.snumfrom enrolled e )第三套試卷2. Given two relations R1and R2

32、, where R1 contains N1 tuples, R2contains N2 tuples, and N2 > N1 > 0, give the minimum and maximum possible sizes (intuples) for the resulting relation produced by each of the following relational algebra expressions .In each case, state any assumptions about the schemas for R1and R2 needed to

33、 make the expression meaningful:(1) R1 U R2, (2) RlO R2, (3) R1-R2=R1-RlA R2, (4) R1>R2, (5) Oa=5(R1), (6)幾仆U),ExpressionAssumptionMinMaxR and R2 arc union-compatiblcN2N1 +N2RirR27?1 and R2 arc union-compat ible0N1R - R2R1 and 7?2 arc union-compat ible0N1R1 x R2N1 * N2N1 * N2R1 has an attribute n

34、amed a0N1卍(Rl)R has attribute a. Nl>01N1RI/R2The set of attributes of R2 is a subset of the set of attributes of R100R2/R1The set of attributes of /?! is a subset of the set of attributes of R20IN2 / NilFigure 4.1 Answer to Exercise 4.2.3. Notown Records has decided to store information about mus

35、icians whoperform on its albums (as well as other company data) in a database The company has wisely chosen to hire you as a database designer (at your usual consulting fee of $2500/day).Each musician that records at Notown has an SSN, a name, an address, and a phone number. Poorly paid musicians of

36、ten share the same address, and no address has more than one phoneEach instrument used in songs recorded at Notown has a urej identi?cation number, a name (e.g., guitar, synthesizer, ?ute) and a musical key (e.g.f E-?at).Each album recorded on the Notown label has a unique identi?cation number, a ti

37、tle, a copyright date, a format (e.g., CD or MC)t and an album idenit?er.Each song recorded at Notown has a title and an author.Each musician may play several instruments, and a given instrument may be played by several musiciansEach album has a number of songs on it, but no song may appear on more

38、thanone albumEach song is performed by one or more musicians, and a musician may perform a number of songsEach album has exactly one musician who acts as its producer. A musician may produce several albums, of courseDesign a conceptual schema for Notown and draw an ER diagram for your schemaThe prec

39、eding in formation describes the situation that the Notown database must model. Be sure to indicate all key and cardinality constraints and any assumptions you make Identify any constraints you are unable to capture in the ER diagram and brie?y explain why you could not express them4. Consider the N

40、otown database from Exercise 3. You have decidedto recommend that Notown use a relational database system to store company data Show the SQL statements for creating relations corresponding to the entity sets andrelationship sets in your design. Identify any constraints in the ER diagram that you are

41、 unable to capture in the SQL statements and brie?y explain why you could not express them.1. create table musicians ( ssn char(10),name char(30),primary key (ssn)2. create table instruments ( instrid char(10),dname char(30),key char(5),primary key (instrid)3. create table plays ( ssn ch8(10),instri

42、d integer,primary key (ssn, instrid),foreign key (ssn) references musicians,foreign key (instrid) references instruments )4. create table songs appears ( songid integer,author char(30),title char(30), albumidenti?er integer not null,primary key (songid),foreign key (albumidenti?er) references album

43、producer)5. create table telephone home ( phone char(11),address char(30),primary key (phone),foreign key (address) references place,6. create table lives ( ssn char(10),phone char(11), addresschar(30),primary key (ssn, address),foreign key (phone, address) references telephone home,foreign key (ssn

44、) references musicians )7. create table place ( address char(30)8. create table perform ( songid integer,ssn char(10),primary key (ssn, songid),foreign key (songid) references songs,foreign key (ssn) references musicians )9. create table album producer ( albumidenti?er integer,ssn char(10),copyright

45、date date,speed integer,title char(30),primary key (albumidenti?er),foreign key (ssn) references musicians )5 Consider the following schema:Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)The Catal

46、og relation lists the prices charged for parts by Suppliers. Write the followingqueries in SQL:1. Findthe pnames of parts for which there is some supplier.2. Findthe snames of suppliers who supply every part3. Findthe snames of suppliers who supply every red part4. Findthe pnames of parts supplied b

47、y Acme Widget Suppliersandnooneelse.5 Findthe sids of suppliers who charge more for some part than theaveragecost ofthat part (averaged over all the suppliers who supply that part).6. Foeach part, ?nd the sname of the supplier who charges the most for that part.1. select distinct p.pnamefrom parts p

48、, catalog cwhere p.pid = c.pid2. select s.snamefrom suppliers swhere not exists ( select p.pidfrom parts p )except(select c.pidfrom catalog c where c.sid =s.sid )3. select s.snamefrom suppliers swhere not exists ( select p.pidfrom parts pwhere p.color = 4 red9 ) except(select c.pidfrom catalog c, pa

49、rts pwhere c.sid = s.sid andc.pid = p.pid and p.color = bed')4. select p.pnamefrom parts p, catalog c, suppliers swhere p.pid = c.pid and c.sid = s.sidand s.sname =4acme widget suppliers5and not exists ( select *from catalog c1, suppliers s1where p.pid = c1pid and c1sid = s4sid ands1.sname <&

50、gt; "acme widget suppliers')5 select distinct c.sidfrom catalog cwhere c.cost > ( select avg (c1 .cost)from catalog c1where cl.pid = c.pid )6. select s.snamefrom parts p, suppliers s, catalog c where c.pid =p.pid and c.sid = s.sidand c.cost = (select max (cl.cost) from catalog c1where cl

51、.pid = p.pid)3 Let the following relation schemas be given: R =(A,B,C)s =(D,E,F)Let relations r(R)and s(S) be given. Give the meaning of each one expressiona. !"! A(r)b. Ob二 17 (r)c. rd. fl a,f (Oc=D(r «)4. Computer Sciences Department frequent ?iers have been complaining to Dane County Ai

52、rport offi cials about the poor organization at the airport As a result, the offi cials decided that all information related to the airport should be organized using a DBMS, and you have been hired to design the database. Your ?rst task is to organize the information about all the airplanes statione

53、d and maintained at the airport. The relevant information is as follows:Every airplane has a registration number, and each airplane is of a speci?c model.The airport accommodates a number of airplane modesl, and each model is identi?ed by a model number (e.g., DC-10) and has a capacity and a weight.

54、A number of technicians work at the airport You need to store the name, SSN, address, phone number, and salary of each technicianEach technician is an expert on one or more plane model(s)f and his or her expertise may overlap with that of other technicians This information about technicians must als

55、o be recorded Tra? c controllers must have an annual medical examination. For each tra? c controller, you must store the date of the most recent examAll airport employees (including technicians) belong to a union. You must store the union membership number of each employee You can assume that each e

56、mployee is uniquely identi?ed by a social security number.The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible scoreThe FAA requires the airport to

57、keep track of each time a given airplane is tested by a given technician using a given test. For each testing eve nt, the information needed is the date, the number of hours the technician spent doing the test, and the score the airplane received on the test.1. Draw an ER diagram for the airport database.Be sure to indicate the various attributes of each entity and relationshi