数据库大题的答案.docx

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

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

3、worked on by one or more graduate students (known as the projects research assistants).When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor fo

4、r each one.Departments have a department number, a department name, and a main office.Departments have a professor (known as the chairman) who runs the department.Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job.Gra

5、duate students have 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 information about the univers

6、ity.Use only the basic ER model here; that is, entities, relationships, and attributes. Be sure to indicate any key and participation constraints.9. Consider the university database from Exercise 8 and the ER diagram you designed. Write SQL statements to create the corresponding relations and captur

7、e as many of the constraints as possible. If you cannot capture some constraints, explain why.Answer：1. create table professors ( prof_ssn char(10),name char(64),age integer,rank integer,speciality char(64),primary key (prof ssn) )2. create table depts ( dno integer,dname char(64),office char(10),pr

8、imary key (dno) )3. create table runs ( dno integer,prof_ssn char(10),primary key ( dno, prof ssn),foreign key (prof ssn) references professors,foreign key (dno) references depts )4. create table work dept ( dno integer,prof_ssn char(10),pc_ time integer,primary key (dno, prof_ssn),foreign key (prof

9、_ssn) references professors ,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 table project ( pid integer,sponsor char(32),start date char(20),end date char(20),budget f

10、loat,primary key (pid) )6. create table graduates ( grad ssn char(10),age integer,name char(64),deg prog char(32),major 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 attribut

11、e in the graduates table.7. create table advisor ( senior ssn char(10),grad ssn char(10),primary key (senior ssn, grad ssn),foreign key (senior ssn) references graduates ,foreign key (grad ssn) references graduates )8. create table manages ( pid integer,prof ssn char(10),primary key (pid, prof ssn),

12、foreign key (prof ssn) references professors,foreign key (pid) references projects )9. create table work in ( pid integer,prof ssn char(10),primary key (pid, prof ssn),foreign key (prof ssn) references professors ,foreign key (pid) references projects )observe that we cannot enforce the participatio

13、n constraint for projects in thework in table without check constraints or assertions in sql.10. create table supervises ( 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) references gr

14、aduates(grad ssn),foreign key (pid) references projects(pid) )Note that we do not need an explicit(明确的) table for the Work Proj relation since everytime a Graduate works on a Project, he or she must have a Supervisor.10. Consider the following relations:Student(snum: integer, sname: string, major: s

15、tring, level: string, age: integer)Class(name: string, meets at: string, room: string, d: integer)Enrolled(snum: integer,ame: 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

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

17、lled in a course taught by I. Teach.3. Find the names 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 s

18、ome class is taught.The answers are given below:1. select distinct s.snamefrom student s, class c, enrolled e, faculty fwhere s.snum = e.snum and ame = c.name and c.d = f.d andf.fname = i.teach and s.level = jr2. select max(s.age)from student swhere (s.major = history)or s.snum in (select e.snum fro

19、m class c, enrolled e, faculty fwhere ame = c.name and c.d = f.dand f.fname = i.teach )3. select c.namefrom class cwhere c.room = r128or c.name in (select amefrom enrolled egroup by amehaving count (*) =5)4. select distinct s.snamefrom student swhere s.snum in (select e1.snumfrom enrolled e1, enroll

20、ed e2, class c1, class c2where e1.snum = e2.snum and ame ameand ame = c1.nameand ame = c2.name and c1.meets at = c2.meets at)5. select distinct f.fnamefrom faculty fwhere not exists ( select *from class c )except(selectc1.roomfrom class c1where c1.d = f.d )=第二套试卷 6 A company database needs to store

21、information about employees (identied by ssn,with salary and phone as attributes), departments (identied by dno, with dname and 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 b

22、e identied uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.Draw an ER diagram that captures this information.7 Consider the scenario from Exercise

23、6, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.answer:the following sql statements create the corresponding relations.create

24、 table employees ( ssn char(10),sal 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) refer

25、ences departments)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),name char(10),age integer,primary key (ssn, name),foreign key (ssn) references employees,on delete

26、 cascade )8.Consider 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,ame: string)Faculty(d: integer, fname: string, deptid: integer)The meaning of these rela

27、tions is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.Write 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

28、 courses that they teach is less than 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 classes only in room R128, print the fa

29、culty members 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 ewher

30、e c.name = ame and c.d = f.d)2. select s.level, avg(s.age)from student sgroup by s.level3. select s.level, avg(s.age)from student swhere s.level jrgroup by s.level4. select f.fname, count(*) as coursecountfrom faculty f, class cwhere f.d = c.dgroup by f.d, f.fnamehaving every ( c.room = r128 )5. sel

31、ect 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, where R1 contains N1 tuples, R2contains N2 tuples, and N2 N1 0, give the minimum and maximum possible sizes (intuples) for the resulti