Question

Problem 2 (40 points) Consider the business rules below, describing a database of Dormitories and Students who live in the dorms. Every student must have a student ID, a name, gender. A student can have a meal choice (e.g.the choice of eat in the dormitory café every day for example "Yes" for choice of yes, "No" for no, and Null value represents not decided yet) No two students have the same combination of name and gender. (oNLY assumed in this case) dormitory rooms have the same number No two students have the same id; A dormitory room is described by its room number and room space/area, and no two A dormitory room may has 0,1 or several students living in A student must live in exactly one dormitory room (2a) (15 points) Draw an ER diagram that encodes these business rules. Clearly annotate all key and/or participation constraints. (2b) (15 points) Write below your SQL DDL statement(/s) that implement all constraints specified in your ER model in (2a). Indicate the creating orderand deleting order as you are to remove these tables in the future. Briefly few words) explain which constraints are captured in your SQL, and in what way. Note: If a constraint cannot be implemented, state that explicitly in your explanation You will not receive full credit without explanation(s) (2c) (5 points) Write a SQL statement to add one tuple into your Dormitories table (2d) (5 points) Write a SQL statement to delete all students whose choice (of eating in the dormitory) are null values (not decided yet whether to eat in the dormitory café every day) from your Students table.

Answer 1

id name orm- Stodent Vin meo

• //creating student table

  create table student(student_Id varchar(20),name char(20),gender char(10),meal varchar(6),Dorm_id varchar(20),unique(name,gender), primary key(student_Id),foreign key (Dorm_id) references dorm,check(gender in ('Male', 'Female', 'others')),check(meal in ('yes','no','null')));

  //In student table used primary key,foreign key constraints on student_Id,Dorm_id.And check constraints on gender,meal.

  gender:checking for male,female,others

  meal:yes,no,null

  //creating dorm table

  create table dorm(Dorm_id varchar(20),dorm_space varchar(20),primary key(Dorm_id));

  ///Inserting data into dormentory table

  insert into dorm(Dorm_id,dorm_space) values ("Room 1","200feet");

  //deleting students data who didn't decided about meal

  delete from student where meal='null';