tutorial_01_updated_06-10-2015

8/17/2019 Tutorial_01_updated_06-10-2015

1/8

1

Tutorial 01

1. UPS prides itself on having up-to-date information on the processing and current location of

each shipped item. To do this, UPS relies on a company-wide information system. Shipped

items are the heart of the UPS product tracking information system. Shipped items can be

characterized by item number (unique), weight, dimensions, insurance amount, destination,

and final delivery date. Shipped items are received into the UPS system at a single retail

center. Retail centers are characterized by their type, uniqueID, and address. Shipped items

make their way to their destination via one or more standard UPS transportation events (i.e.,

flights, truck deliveries).These transportation events are characterized by a unique

scheduleNumber, a type (e.g, flight,truck), and a deliveryRoute. Please create an Entity

Relationship diagram that captures this information about the UPS system. Be certain to

indicate identifiers and cardinality constraints. Convert the ER into relational model.

2. A university wants to set up a database to record details about its staff, and the departments

they belong to. They intend to record the following information.

a. For each member of staff, their staff identity number, name, job title, and salary.

b. For each department, its name and address.

c. For each member of staff all departments that they belong to. It is required that

every member of staff belongs to at least one department.

d. For each department, the head of department. It is required that each department

has exactly one head of department.

Draw an ER diagram that expresses the requirements for the database. Make sure that you

capture all the constraints on the data mentioned above. Convert the ER into relational model.

3. Assume we have the following application that models soccer teams, the games they play, and

the players in each team. In the design, we want to capture the following:

We have a set of teams; each team has an ID (unique identifier), name, main stadium,

and to which city this team belongs.

Each team has many players, and each player belongs to one team. Each player has a

number (unique identifier), name, DoB, start year, and shirt number that he uses.


2/8

2

Teams play matches, in each match there is a host team and a guest team. The match

takes place in the stadium of the host team.

For each match we need to keep track of the following: The date on which the game

is played, the final result of the match the players participated in the match. For each

player, how many goals he scored, whether or not he took yellow card, and whether

or not he took red card. During the match, one player may substitute another player.

We want to capture this Substitution and the time at which it took place.

Each match has exactly three referees. For each referee we have an ID (unique

identifier), name, DoB, years of experience. One referee is the main referee and the

other two are assistant referee.

I.

Design an ER diagram to capture the above requirements. State any assumptions

you have that affects your design). Make sure cardinalities and primary keys are

clear.

II. Map the ERD in above scenario to create the relational model.

4. The following narrative describes a simplified version of the organization of Olympic facilities

planned for the summer Olympics. Draw an EER diagram that shows the entity types,

attributes, relationships, and specializations for this application. State any assumptions you

make and map the ERD for the following scenario to create the relational model.The Olympic facilities are divided into sports complexes. Sports complexes are divided into

one-sport and multisport types. Multisport complexes have areas of the complex designated

for each sport with a location indicator (e.g., center, North East corner, south, etc.). A

complex has a location, chief organizing individual, total occupied area, and so on. Each

complex holds a series of events (e.g., the track stadium may hold many different races). For

each event there is a planned date, duration, number of participants, number of officials, and

so on. A roster of all officials will be maintained together with the list of events each official

will be involved in. Different equipment is needed for the events (e.g., goal posts, poles,

parallel bars) as well as another set of equipment is needed for maintenance of each sports

complex (e.g. cleaning equipment, grass cutters, etc. ). The two types of facilities (one-sport

and multisport) will have different types of information. For each type of facility, there are


3/8

3

number of amenities available (e.g. internet café, gym, pool, etc.) together with an

approximate budget for each amenity.

5. PART A

A database will be made to store information about patients in a hospital. On arrival, each

patient’s personal details (name, address, and telephone number) are recorded where possible,

and they are given an admission number. They are then assigned to a particular ward (plastic

surgery, general medicine, pregnancy, cardiac surgery, head and neck). In each ward there are

a number of doctors and nurses. A patient will be treated by one doctor and several nurses

over the course of their stay, and each doctor and nurse may be involved with several patients

at any given time.

I. Identify the entities, attributes, relationships, and cardinality ratios from the

description.

II. Draw an entity-relationship diagram showing the items you identified.

Part B

Design The Hospital Database and Tables as follows.

1. Ward Table

2.Nurse

3. Doctor


4/8

4

4. Patient

5. NurseTreatPatient

Part C

Insert the data to the tables as follows.

1. Ward Table

2. Nurse Table

3. Doctor Table


5/8

5

4. Patient Table

5. NurseTreatPatient Table

Part D

Write the sql statements for the followings.

1) List the all table’s details.

2) List out the followings.

i) Doctor’s names

ii) Nurse’s Names

ii) Ward’s Names.

iii) Patient’s Names

3) List out the patient’s name list who is admitted in ward Number 5(w005)

4) List out the followings

i) List of doctors in ward number 1(w001)

ii) List of Nurses in ward number 5(w005)


6/8

6

ii) List of patients in ward number 4(w004)

5) List out nurse’s name and his contact number in ward number 5(w005)

6) List out patients’ name, address and his contact number in ward number 3(w003)

7) List the doctors’ name, patients’ names and nurses’ names in ward number 5(w005)

8) Modify the patient table by adding new column as admitted date.

(Admitted date should be a date before end of September 2015).

9) List out patients (name and admitted date) those who have admitted on or before 21st

September 2015.

10) Update

i) Patient number p3’s ward number to w5.

ii) Update doctor sunil’s ward number to w3.

iii) Update nurse tharanga’s ward number to w4.

11) Check whether the modification which was done in question 9 applied to the respective

tables.

12) Select number of doctors assigned to each ward that they work.

13) Select the name of the doctors who are working in the ward that the Nurse tharanga and

rajitha works.

14) Select doctors who are examining more than one patient.

15) Select doctors in ward number w001 who are examining more than one patient.

16) Select number of patients who are facing Plastic Surgery.

17) Select doctors those who are not examining any patient.

18) Count the number of patient admitted in the hospital.

19) Count the number of patients' admitted in the ward number 5.

20) List the patient's names according to the ward they are admitted in.

21) List the patient's names according to the doctors' name they are treated by.

22) Insert a column to patient table as age (int) and display the patients whose age is greater than

50 years old.


7/8

7

6. Create a Database called AccountHolders

i. Create tables according to the following definition.

Branch(bnamevarchar(18),city varchar(18));

Customers(cnamevarchar(19),city varchar(18));

Deposit (actnovarchar(5) ,cnamevarchar(18) , bname

Varchar(18) , amount decimal(8,2) ,adate date);

Borrow(loannovarchar(5), cname varchar2(18),

Bnamevarchar2(18), amount number (8,2))

ii. Insert the data as shown below.


8/8

8

iii. Write the SQL Queries for obtaining following outputs.

a. Find all loan no. & amount of the Perry ridge branch where the amount is

Greater than 1300

b.

Find Customers who are having an account and also having a lone.

c. Find the customers who are having loan, but not having an account.

d. Find total number of customers who are having a account, but not having a loan

e. Find customers who are borrowing loan more than 2000 and amount date higher than95-01-25

f. Find the total number of amount borrowed

g.

Find the maximum and minimum amount that has been borrowed

h. Find the customers whose name begin with letter ‘A’ and ‘S’

i. Find the customers whose name has the pattern ‘NIL’

j. List the number of customers with respect to the amount of deposit.

tutorial_01_updated_06-10-2015

Documents