1. There are many teachers in a certain department and each teacher belongs to only one department. A department has any number of scientific research projects and each project is managed exactly by one department. For each project, more than 2 but less than 20 teachers participate in and each teacher can participate in more than one project or no project. Teachers participate in the project to count the workload. Department are with the following attributes: number, name, address,tel number. Teachers have the attributes as follows: number, name, sex, age, title of an teacher. Research projects are with the following attributes: project number, project name, cost,location. Please complete the following design:

(a) Design ER diagram and indicate entities , attributes, relationship between them and participation constrains of it.

(b) Design relational schema based on ER diagram, for each relation, define the relation name, attribute names,datatype, primary key, and foreign key( if any).

(c) Explain two of relations you design belongs to 3NF according to some hypothesis you make in the real world.

2. A Bank’s database includes BANK, BANK_BRANCH, ACCOUNT, LOAN and CUSTMER entities, and the ER model is shown as the following diagram. According to the EER model and actual requirements:

(a) List the strong (nonweak) entity types in the ER diagram.

(b) List the weak entity and give the partial key and primary key of it.

(c) What is the meaning of the constraints M and double line of the relationship L_C in the loan entity side?

(d) How do you handle ACCTS and LOANS relationships when mapping to relational schema?

(e)How do you handle A_C relationship when they are mapped to relational schemas?

3. A car dealer database includes VEHICLE, CAR, TRUCK, SUV, CUSTMER and SALESPERSON entities, and the EER model is shown as the following diagram. According to the EER model and actual requirements:

(a) List the Superclass entity type(s) in the ER diagram.

(b) List the Subclass entity type(s).

(c) What is the meaning of the constraints d and double line connected to VEHICLE entity?

(d) How do you handle VEHICLE, CAR, TRUCK, SUV when mapping to relational schema?(5)What type is the relationship SALE?Please list the attributes when mapping it to relation and give suitable explaination.

Transcribed Image Text:

BRANCHES BANK BRANCH BANK Code Name Addr Branch no Addr ACCTS LOANS Acct_no Balance Loan no Amount ACCOUNT Type Type LOAN A_C Sen Name An ER diagram for a BANK Phone CUSTOMER Add database schema CAR Engine_size Vin Price VEHICLE TRUCK Tonnage Model Date SUV No seats SALE SALESPERS ON CUSTOMER Address Cty Street Figure EER diagram for Sid Name San Name State a car dealer BRANCHES BANK BRANCH BANK Code Name Addr Branch no Addr ACCTS LOANS Acct_no Balance Loan no Amount ACCOUNT Type Type LOAN A_C Sen Name An ER diagram for a BANK Phone CUSTOMER Add database schema CAR Engine_size Vin Price VEHICLE TRUCK Tonnage Model Date SUV No seats SALE SALESPERS ON CUSTOMER Address Cty Street Figure EER diagram for Sid Name San Name State a car dealer

Answer rating: 100% (QA)

Question 1 a ER Diagram b Relational Schema Diagram c 3NF means there must not exist any transient dependency and all attributes should depend directl... View the full answer