数据库大题的答案Word文件下载.docx

1、Each project is 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 differen

2、t） supervisor for 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 wi

3、th their job.Graduate 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 a

4、bout the university.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 rela

5、tions and capture 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）,of

6、fice char（10）,primary 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,pc_ time integer,primary key （dno, prof_ssn）,foreign key （prof_s

7、sn） 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 flo

8、at,primary key （pid） ）6. create table graduates （ grad ssn char（10）,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 attribute in the graduates table.7.

9、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）,foreign key （pid） references

10、 projects ）9. create table work in （ pid integer,foreign key （prof ssn） references professors ,foreign key （pid） references projects ）observe that we cannot enforce the participation constraint for projects in thework in table without check constraints or assertions in sql.10. create table supervise

11、s （ prof 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 graduates（grad ssn）,foreign key （pid） references projects（pid） ）Note that we do not need an explicit（明确的） table for the Work Proj relation

12、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: string, level: string, age: integer）Class（name: string, meets at: string, room: string, d:Enrolled（snum: integer, cname: string）Faculty（d:

13、 integer, fname: string, deptid: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.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 enrolled in