RDBMS Lab @ Home 1

1. Shopping Spree is a leading departmental store in Shanghai. The store has a number of regular customers who purchase bulk items. The store also conducts regular feedback sessions to analyze customer satisfaction levels. Chen, the Customer Analyst of Shopping Spree, has to make the ER diagram to represent the preceding situation, and then to map the ER diagram to the corresponding tables. Help Chen to do the same.

Ans

2. Design an ER diagram for the following situation:
An organization has two types of employees, salaried and wage earning. Both the types of employees have some common properties, such as employee code, employee name, and employee address. However, the salaried employees have other additional properties: basic, allowance, and House Rent Allowance (HRA). The wage earning employees have distinct properties that are daily wage and overtime.

Ans

3.  Consider the following Doctor-details table.

 In the preceding table, you have to identify primary key, candidate key, and alternate key and give the reason.

Ans 

4.  You have been hired by a regional hospital to create a database that will be used to store the information of the patients. Implementing the database will make the data easily accessible. The following ER diagram represents the relationship between the patient and the doctor. 

In the preceding diagram, you have to identify the entities, their attributes, and the type of relationship between the entities.

Ans  

5. Tom is working in an organization as a database administrator. Based on the hierarchy, the organization has multiple departments and each department has multiple employees. The following ER diagram represents the relationship between the departments and the employees.

 In the preceding diagram, you have to identify entities, their attributes, and type of relationship between the entities.

Ans 

6. Lee Wong is the newly appointed database administrator at Standard bank. The management of this bank wants to computerize the process of banking. According to the bank's policy, one customer can have many accounts but one account cannot be shared by many customers. The following ER diagram represents the relationship between a customer and the accounts owned by a particular customer. 

In the preceding diagram, you have to identify entities, their attributes, and type of relationship between the entities.

Ans. 

7.  Consider the following scenario: 

An author can write multiple books and a book can be written by more than one author. Identify the relationship between the author entity and the book entity and represent it with an ER Diagram.

Ans  

8.  Consider the following scenario: 

In a college, there are multiple departments. Each department has various faculty members. Only one of these faculty members holds the position of the Head of the Department. Identify the relationship between the faculty and the department and represent it with an ER Diagram.

Ans