《大数据库大题的问题详解》由会员分享，可在线阅读，更多相关《大数据库大题的问题详解（62页珍藏版）》请在金锄头文库上搜索。
1、word第一套试卷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,
2、 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 work
3、ed 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 for ea
4、ch 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.Graduat
5、e 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 university.
6、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 capture as
7、 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),primar
8、y 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_ssn
9、) 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 float
10、,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 attribute in
11、 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),fore
12、ign 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 participation co
13、nstraint 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 gradua
14、tes(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: strin
15、g, 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 that
16、 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 a
