Database design Ⅰ : Consider the following conditions
i. The STUDENT may be taught by one and only one teacher . The TEACHER may be instructor of one or more STUDENT .
ii. The TEACHER may be responsible for one and only one CLASS . The CLASS may be the responsibility of one and only one TEACHER .
iii. The CLASS may be made of one of one or more STUDENT . The STUDENT must be a member of one and only one CLASS .
iv. The CLASS must have one and only are ROOM . The ROOM may belong to one or more CLASS .
Notes : Assume entity CLASS has the following attributes : CID and CNAME , entity ROOM has the following attributes : RID and LOCATION , entity STUDENT has the following attributes : SID , LASTNAME , and FIRSTNAME , entity TEACHER has following attributes : TID ,TEACHERNAME , and TITLE .
a) [8 points] Construct an E-R diagram showing these relationships .
b) [4 points] Construct appropriate relation schemas for the above E-R diagrams .
c) [4 points] Create an index std_index on the student relation with SID as the search_key .
