数据库原理及应用2

• 1、(20 points)Now given a middle school database with two relations,one is Student with the following attributes:StudentNo,StudentName, StudentAddress, SudentBirthday, StudentSSN,ClassId, the other is Class relation with ClassId,ClassName. Accoring to the common situation of a middle school:
  (3,5 each 4 points, others each 3 points)
  (1)Please write two reasonable candidate keys, one primary key for student relation.
  (2)Then explain the relationship and diiference beween candidate key and primary key according to (1).
  (3) Write the primary key and a foreign key of student relation, then exlain the meaning of them respectively.What is the datatype rule for the foreign key and corresponding primary key when you deifine it.
  (4)Add constraint “not null” for attribute SudentBirthday by “alter table” command;
  (5)Add constraint with values beween 1 and 15 for attribute ClassId in Class realtion .
  (6)Give a suitable datatype respectively for StudentName and SudentBirthday.

• Refrence answers:
  (1)For Student, candidate keys: StudentNo , StudentSSN;
  primary key: any one of StudentNo, StudentSSN;
  foreign key: ClassId;
  (2)There are more candidate keys in one relation and one of them can be assiged as primary key;
  (3)primary key is used for identifying the sole row in one table ,in example (1),each student has a sole StudentNo or StudentSSN; and the foreign key is used for connecting to the primary key of other relation and referencing its value. For instance in (1),ClassId value refeneces the ClassId values of Class relation.
  (4)Alter table Student add constraints SudentBirthday not null;
  (5)Alter table Class add constraints check ClassId beween 1 and 15;
  (6)StudentName:varchar(20); SudentBirthday:datetime;

• 2、(32points, each 4 points)Consider the relational database for a company with 3 relations as follows:
  employee(empno,birthdate, firstname,lastname,gender,hiredate, deptno)
  empno:number of an employee; birthdate:birthday of an employee; hiredate:the date when the employee entered the company; deptno:the department number an employee belongs to; gender:man or woman;
  salaries(empno,salary, fromdate,todate)，empno:the employee number; salary：salary level between the period from fromdate to todate;
  fromdate,todate: the period(start time and end time) when an employee was with the salary level；
  department(deptno,deptname,location),deptno:the department number; deptname: the department name of a department; location:where the department located on.
  The primary keys are underlined by solid lines. Please give the SQL statements for the following problems:
  （1）Create table employee with suitable data type for each attribute of it.You are required to define empno as the primary key of it and deptno as the foreign key referencing to department (deptno). (4 points)

  create table employee(empno int, birthdate datetime, firstname
  varchar(20), lastname varchar(20), gender varchar(10),hiredate
  datetime , deptno int, primary key(empno),forign key(deptno)
  refrences department(deptno) );

  （2）Increase 300 for the salary of all employees who entered company during the period between “2016-01-01” and “2016-12-31” .(4 points)

  update salary=salary+300 where fromdate>=’ 2016-01-01’ and
  fromdate <=’’ “2016-12-31” ;

  （3）Find the firstname,lastname and corresponding department name of all employees whose birthdate before “1996-01-01” and firstname beginning with latter “P” . (4 points)

  select firstname,lastname, deptname from employee, department
  where department. deptno= employee. deptno and birthdate<“1996-01-01”
  and firstname like ‘P%’;

  （4）Find each employee’s number,his(her) average salary for all periods of him or her.(4points)

  select empno,avg(salary) from salaries group by empno;

  （5）Find the employees number for man and woman respectively.(4 points)

  Select sex,count(*) from employee group by sex;

  （6）Find these employees’ last names who work for department “office”.(4 points)

   select lastname from employee, department where employee.deptno=department.deptno and department.deptname= “office”;
  

  (7)Create a new user “quser” (password is also “quser”) by root(password for root is “1234”);(4 points)

  mysql –u root –p1234
  create user “quser”@”%” identified by “quser”;

  (8) Login by user root(password is “1234”)，grant the privileges selecting and updating on relation salaries to an beingless user “quser”. Then Login by user quser,select all the tuples of salaries. (4 points)

   mysql  –u root –p1234
   grant insert,update on salaries to scott
   mysql  –u scott – p scott
   select * from salaries
  

• 3、(18 points)Assume that the following functional dependency set F={AB，BCD} for a relation schema R=(A,B,C,D,E).
  (1)Please compute A+ ,AE+ .(4 points)
  A+=ABCD，AE+=ABCDE
  (2)What is the candidate key for it? Please give the reason. (3 points)
  AE. Because the closure of AB is U,the whole attribute set.
  (3)Is there a FD that a non-prime attribute part functionally depends on the primary key? Please write it if any. (2 points)
  Yes. AB
  (4)Is there a FD that a non-prime attribute transive functionally depends on the primary key? Please write it if any. (2 points)
  Yes. AB, BC.
  (5)What Normal Form is the relation? Please give the reason for the conclusion.(3 points)
  1NF. Becase there is a FD that a non-prime attribute part functionally depends on the primary keyAE,
  A->B
  (6)Please decompose the relation into some relations with at least 3NF. (4 points)
  R1(BCD),R2(AE),R3(AB)

• 4、(30 points)Now a reserach-club manamgement database system will be developed in a university according to the situation in real mini-world as follows: There are many students in a major and each student belongs to only one major. A major has any number of clubs and each club is managed exactly by one major. For each club, more than 4 but less than 15 students participate in and each student can participate in more than one club or no club. Students participate in the club to count the hours. Each club is supervised by a teacher. There are 3 kinds of no-overlapping clubs accoding to club research field: information field, Mathematics field and culture field. Each club must be a kind of them. Each entity has the attibutes as follows:
  major: number, name,location, tel (of major);
  stduent: number, name, sex, birthday, interest(of student);
  research club: number, name, location,requirment(of club);
  supervisor: number,name,major (of teacher);
  Please complete the following design:
  (1)Design EER diagram and indicate entities , attributes, relationships between them and participation constrains of them. In the EER model, there must include one weak entity and one specialization according to the description above (10 points)
  (2)List the strong (nonweak) entity types in the EER diagram.(3 points)
  (3)List the weak entity in your EER model and give the partial key and primary key of it.(3 points)
  (4)Explain the specialization constraints according to club type you designed above(Disjoint or Overlapping, Full or partial).(4 points)
  (5)Map to relational schemaes based on EER above, for each relation, define the relation name, attribute names,datatype, primary key, and foreign key( if any). (10 points)

• Referenceanswer
  (1)ER diagram
  
  (2)strong entities: major, stduent, club;
  (3)weak entity: supervisor, partial key :tnum; primary key(cnum,tnum)
  (4)disjoint: one club belongs to only one type club;
  full: each club must belong to one of the three kinds of clubs: information field, Mathematics field and culture field.
  (5)Relations:
  major(mnum,mname ,tel,loc),pramary key:mnum;
  student(snum,sname,sex, birthday, interest,mnum), primary key:snum;
  club(cnum,cname,loc,req,type,mnum,tnum),primary key:cnum; forign key:mnum,tnum
  attend(snum,cnum,hours), primary key:(snum,cnum); forign key:snum, cnum
  supervisor(tnum,tname,tmajor) , primary keytnum; forign key:tmajor