SQL LAB @ HOME 1 ~ NIIT POST

Sql lab @ home 1

1.    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:

2.   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:  

3.    Consider the following DoctorDetails table.

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

ANS: 

Primary Key :DoctorID

Candidate Key :DoctorID,ShiftID

Alternate Key : DoctorDutyID

doctorID and shiftID are individually unique in every row.

Therefore the coloumns, doctorID and ShiftID ,are candidate keys for the primary key.

The Shift id coloumn may contain duplicate values as two doctors may be on the same shift.

DoctorID  should be chosen as the primary key and shiftid as the alternate key.

4.  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:

Entities: Department and Employee

Attributes of department : DepID,DepName and DepId

Attributes of employee : EmpID, EmpName ,and DepID

Type of Relationship : Many to many

5.   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:

Entities:Patient and Doctor

Attributes for Patient : patient name , date admitted , patient id

Attributes for doctor : Doc_ID , Specialization , Doc_Name

Relationship-Many to many

6.    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:.

7.    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.

Entities: Customer , Account

Attributes for Customer : CUSTOMER NAME , SSN NUMBER , CUSTOMER ADDRESS

Attributes for Account : ACCOUNT NUMBER , BALANCE

Relationship : Many to One

  

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:

NOTE:ALL THE ABOVE DIAGRAMS SHOULD BE DONE IN MICROSOFT WORD DOCUMENT