数据库辅导材料

大题！1．What are main functions of a DBA？2．Explain the distinctions among the terms superkey, candidate key and primary key?3．Given a database schema:loan-info-schema=(branch-name,customer-name,loan-number,amount),functional dependencies on the schema are:loan-number amount, branch-nameIs the schema in BCNF? Why? If it isn’t, give a lossless-join decomposition into BCNF of the schema.4．Using the log, the system can handle any failure that does not result in the loss of information in nonvolatile storage. What are recovery procedures which the recovery schema use?Consider the academic database contains three relations as the following 3 tables, and then give an expression in SQL for each of the following queries.①Tip: Describe primary keys, foreign keys and check constrains if necessary.②Find the student numbers and names of the male students, each of whom is older than 22-year-old.③Find the student numbers of the students, each of whom has at least chosen two courses.④Find the names and ages of the male students, each of whom is older than all the female students.⑤List the course name and average grade for all the courses which are taught byteacher LIU.A university registrar’s office maintains data about the following entities:(a)students, including student-id, name, program;.(b)instructors, including id, name, department and title;(c)courses, including course number, title, credits, syllabus and prerequisites;(d)course offerings, including course number, year, semester, section number,instructor(s), timings, and classroom.Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.1.Construct an E-R diagram for the university registrar’s office. A class meets only at one particular place and time; y ou needn’t model a class meeting at different places at different times; also needn’t guarantee that the database does not have two classes meeting at the same place and time.2.Design a relational database, which is constructed by relation schemas, corresponding to the preceding E-R diagram with marked primary keys.填空1.To design a trigger mechanism, we must specify the ①conditionsunder which the trigger is to be executed; specify the ②actions to be taken when the trigger executes.2.In physical level, the database is stored as a collection of files. Each file is asequence of records, each of which is a sequence of fields.3.In database system, indexing mechanisms are used to speed up access todesired data.4.In distributed database system, data is spread over multiple machines(also referred to as sites or nodes).①parser and translator ,5.②execution plan大题答案1．Schema definition, storage structure and access-method definition, schema and physical-organization modification, granting of authorization for data access, routine maintenance.2．A superkey is a set of one or more attributes that, taken collectively, to identify uniquely an entity in the entity set. Candidate keys are minimal superkeys which no proper subset is a superkey. Primary key is a candidate key that is chosen as the principal means of identifying entities within an entity set.3．The schema is not in BCNF. Because loan-number isn’t a superkey for loan-info-schema. And the functional dependency loan-number-> amount,branch-name is nontrivial.lossless-join decomposition:loan-schema=(loan-number,branch-name,amount)borrower-schema=(customer-name,loan-number)4．Undo(Ti) restores the value of all data items updated by transaction Ti to the old values.Redo(Ti) sets the value of all data items updated by transaction Ti to the new values.①CREATE TABLE SC( Sno int,Cno int,Grade int,PRIMARY KEY (Sno, Cno),FOREIGN KEY (Sno) REFERENCES S,FOREIGN KEY (Cno) REFERENCES C,CHECK (Grade >= 0))②SELECT Sno, SNAMEFROM SWHERE AGE>22 AND SEX=‘M’;③SELECT SnoFROM SC AS X, SC AS YWHERE X.Sno=Y.Sno AND o !=o④SELECT SNAME, AGEFROM SWHERE SEX=‘M’ AND AGE> ALL (SELECT AGEFROM SWHERE SEX=‘F’)⑤SELECT CNAME, A VG(GRADE)FROM C, SCWHERE C.C#=SC.C# AND TEACHER=‘LIU’GROUP BY C.C#1、E-R diagram for the university registrar’s office：2、The relational database corresponding to the preceding E-R diagram：student( sid, name, program)course_offering(courseno, secno, year, semester, time, room)instructor(iid, name, department, title)courses(courseno, title, credits, syllabus)enrolls(sid, courseno, grade)teaches( iid, courseno)requires(couseno, prerequisite, maincourse)。