SQL LAB at 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.

-----------------------------------------------------------------------------------------------------------------------------------

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.

------------------------------------------------------------------------------------------------------------------------------

3.database

Primary Key: DoctorID
Candidate Key:Doctor,ShiftID
Alternate Key: DoctoeDutyID

In the oreceding table, DoctorID and ShiftID are individually unique in every row.
Therefore, the columns, DoctorID and ShiftID, are candidate keys for the primary key.
However, the ShiftID column may contain duplicate values as two doctors may be on
the same shift. Therefore,DoctorID should be chasen as the primary key and ShiftID
as the alternate key.

-------------------------------------------------------------------------------------------------------------------------

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.

The following list bescribes the entities,attributes,and relationship of the
preceding ER diagram:


* Entities-patient and Doctor
* Attributes of patient_Name,Patient_Id,and Date_admitted
* Attributes of Doctor-Doc_ID,Doc_Name, and Specialization
* Type of Relationship_ Many-to-many

------------------------------------------------------------------------------------------------------

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.

The following listdescribes the entition,attributes,and relationship of the preceding ER diagram:


*  Entities - Department and Employee
*  Attributes of Department - DepID,DepName,and DepHead
*  Attributes of Employee -EmpID,EmpName,and DepID
*  Type of Relationship- Many-to-one

---------------------------------------------------------------------------------------------------------

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 following list describes the entities, the attibues, and relationship of the
preceding ER diagram.
*  Entities- Customer and Account
*  Attributes of Customer- Customer Name, SSN Number,and Customer Address
*  Attributes of Account- Account Number and Balance
*  Type of Relationship - One-and-Many

--------------------------------------------------------------------------------------------------------------------

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.

---------------------------------------------------------------------------------------------------------------------------------

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.