DBMS Lecture 6.pdfjjjjjjjjjjjjjjjjjjjjjjjj

Slides ñnnñnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

1. LECTURE: SESSION 6
Dr. Debasmita Dey
21 August 2024

2. WHAT WE'LL DO TODAY
Practice Problems In-class assignment

3. PRACTICE PROBLEM 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 E-R diagram that captures this information about the UPS
system. Pleaseindicate identifiers and cardinality constraints.

4. Please convert the ER diagram into a relational database
schema. Be certain to indicate primary keys and referential
integrity constraints.

5. Please identify an attribute in the above ER diagram that
might represent a composite attribute

6. Please identify the attribute in the ER diagram that could
represent a derived attribute, if any.

7. PRACTICE PROBLEM 2
Create an ER diagram for each of the following descriptions:
(a) Each company operates four departments, and each department belongs to one
company.
(b) Each department in part (a) employs one or more employees, and each employee
works for one department.
(c) Each of the employees in part (b) may or may not have one or more dependents,
and each dependent belongs to one employee.
(d) Each employee in part (c) may or may not have an employment history.
(e) Represent all the ER diagrams described in (a), (b), (c), and (d) as a single ER
diagram.

8. ASSIGNMENT 1
You are required to create a conceptual data model of the data requirements for a
company that specializes in IT training. The company has 30 instructors and can
handle up to 100 trainees per training session. The company offers five advanced
technology courses, each of which is taught by a teaching team of two or more
instructors. Each instructor is assigned to a maximum of two teaching teams or may
be assigned to do research. Each trainee undertakes one advanced technology
course per training session.
(a) Identify the main entity types for the company.
(b) Identify the main relationship types and specify the multiplicity for each
relationship. State any assumptions you make about the data.
(c) Using your answers for (a) and (b), draw a single ER diagram to represent the data
requirements for the company.

9. THANK YOU