(4) Retrival each project code and its total amount according to contact relation. (5) Retrival sample name, tensilevalue and corresponding contact name which belongs to the contact with code "2020101". (6) Delete all the sample rows which belongs to the contact with code "2020102". 3. (16 points)Assume that the following functional dependency set F=[A->BC. C->D, B->E} for a relation schema R=(A,B,C,D,E). (1) Please compute A BC (4 points) (2)What is the candidate key for it? Please give the reason. (4 points) (3)What Normal Form is the relation? Please give the reason for the conclusion (4 points) (4) Please decompose the relation into some relations with at least 3NF. (4 points) 4. (34 points )Now a sport activity 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. There are many sport activities are arranged in this university and each one will be assigned only one sport teacher as the coach, one coach only guides and depends on one sport activity. For each activity, more than 6 but less than 20 students participate in and each student can participate in most 2 activities or no activity. Students participate in each activity to count the hours. There are more kinds of no-overlapping activities according to sport type: ball game, athletics, and swimming and others. Each activity must be a kind of them. Each entity has the attibutes as follows: major: majorcode, majorname,majorlocation, tel (of major): stduent: studentcode, studentname, sex, birthday sport activity: sportcode, sportname, sporttype: teacher teachercode teachemame teacher level: Please complete the following design, you can give simple name for each attribute name and relation anme. (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 (12 points) (2) List the strong (nonweak) entity types in the EER diagram (4 points) (3) List the weak entity in your EER model and give the partial key and primary key of it.(4 points) (1) Explain the specialization constraints according to sport activity 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 and corresponding datatype, primary key, and foreign key( if any). (10 points) 1. (20 points, each 4 points)Now given a hospital database with two relations, one is patient with the following attributes: PatientNo, PatientName, PatienttAddress, PatientBirthday, Idcard, Ward, DoctorNo, the other is doctor relation with DoctorNo, DoctorName, Doctor Title Doctor Sex. Accoring to the common situation in a hospital: (1)Please give two reasonable candidate keys, one primary key for relation patient. (2) Find a foreign key in relation patient and the corresponding primary key for it in relation doctor. (3)Add constraint "not null" for attribute DoctorSex by "alter table command: (4)According to the design method above, which relationship between doctor and patient, 1:1,1:N or M:N? (5)Give suitable datatype respectively for attribute DoctorName and PatientBirthday. 2. (30points, each 5 points)Consider the detection relational database for a detection enterprise with 3 relations as follows: project(projectcode.createdate, projectname) projectcode:code of a project; createdate: when the project was created in system; projectname: project name; projectcode is the primary key of this relation contact(contactcode.contactname amount, startdate, enddate projectcode), contactcode: contact code; contactname:contact name; amount the total amount for this project; startdate: the beginning date of this contact, enddate: the enddate of this contact: projectcode the project code which this contact belongs to is a foreign key sample samplecode,samplename, tensilevalue, testtime contactcode), samplecode: the sample code: samplename: the sample name; tensilevalue:the tensile value for this sample; testtime: test time for this sample; contactcode: the contact code which this sample belongs to is a foreign key. A project has possibly several contacts and a contact belongs to one sole project. One contact may have several samples and a sample belongs to sole contact The primary keys are underlined by solid lines. Please give the SQL statements for the following problems: (1) Create table contact with suitable data type for each attribute. You are required to define contactcode as the primary key and projectcode as the foreign key which references to project (projectcode) (2) Retrival the contactname which containing the letterv. (3) Retrival the proiectcode and proiectname which is created before '2016-01-01