CA1 Questions for ppt presentation

Ppt presentation must contain the following points:

1. Construct an ER diagram.
2. List your assumptions and clearly indicate the cardinality mappings as well as
any role indicators in your ER diagram.
3. Map the ERD in the relational model corresponding to the described application.
4. Also make sure to have the primary keys and foreign keys clearly.

Question no. 1 to 20 for the first 20 students respectively. Similarly repeat the same
questions for the next 20 students and follow the same pattern for other roll numbers
also (including stream change and lateral candidates). [Check the “Question Mapping”
file]. Similar contents of the ppts will be rejected.

Please note the following

1. Prepare a presentation on the assigned topic and upload (in .pdf

format) positively by 29-01-2024 (Monday) 11.00 AM. Post this the drive link
will be closed.
2. Save the file with the naming format "Your Name_Roll no" only. DO NOT
DEVIATE FROM THIS.
3. The file size should not exceed the stipulated size of 4MB.

Date of ppt presentation:

29.01.2024 (1:30 pm – 2:50 pm) – First 25 Students.
30.01.2024 (11:30 am – 1:30 pm) – Leftover Students

1
1 Suppose you are given the following requirements for a simple database for the
National Hockey League (NHL):
• the NHL has many teams,
• each team has a name, a city, a coach, a captain, and a set of players,
• each player belongs to only one team,
• each player has a name, a position (such as left wing or goalie), a skill level, and a
set of injury records,
• a team captain is also a player,
• a game is played between two teams (referred to as host_team and guest_team)
and has a date (such as May 11th, 1999) and a score (such as 4 to 2).
2 A university registrar’s office maintains data about the following entities:
 courses, including number, title, credits, syllabus, and prerequisites;
 course offerings, including course number, year, semester, section
number, instructor(s), timings, and classroom;
 students, including student-id, name, and program;
 instructors, including identification number, name, department, and title.
Further, the enrollment of students in courses and grades awarded to students in each
course they are enrolled for
must be appropriately modeled. Construct an E-R diagram for the registrar’s office.
Document all assumptions that you make about the mapping constraints.
3 E-R diagram for a car-insurance company whose customers own one or more cars
each. Each car has associated with it zero to any number of recorded accidents.
4 E-R diagram for a hospital with a set of patients and a set of medical doctors.
Associate with each patient a log of the various tests and examinations conducted.
5 E-R diagram which models an online bookstore.
6 Consider a university database for the scheduling of classrooms for final exams. This
database could be modeled as the single entity set exam, with attributes course-name,
section- number, room-number, and time. Alternatively, one or more additional
entity sets could be defined, along with relationship sets to replace some of the
attributes of the exam entity set, as

• course with attributes name, department, and c-number

• section with attributes s-number and enrollment, and dependent as a weak
entity set on course
• room with attributes r-number, capacity, and building
Show an E-R diagram illustrating the use of all three additional entity sets listed.
7 Company organized into DEPARTMENT. Each department has unique name and a
particular employee who manages the department. Start date for the manager is
recorded. Department may have several locations.
• A department controls a number of PROJECT. Projects have a unique name,
number and a single location.
• Company’s EMPLOYEE name, ssno, address, salary, sex and birth date are
recorded. An employee is assigned to one department, but may work for several
projects (not necessarily controlled by her dept). Number of hours/week an employee
works on each project is recorded; The immediate supervisor for the employee.
• Employee’s DEPENDENT are tracked for health insurance purposes (dependent
name, birthdate, relationship to employee).

2
8 E-R diagram for keeping track of the exploits of your favourite sports team. You
should store the matches played, the scores in each match, the players in each match
and individual player statistics for each match. Summary statistics should be
modelled as derived attributes.
9
A salesperson may manage many other salespeople. A salesperson is managed by
only one salespeople. A salesperson can be an agent for many customers. A
customer is managed by one salespeople. A customer can place many orders. An
order can be placed by one customer. An order lists many inventory items. An
inventory item may belisted on many orders. An inventory item is assembled from
many parts. A part may be assembled into many inventory items. Many employees
assemble an inventory item from many parts. A supplier supplies many parts. A
part may be supplied by many suppliers.
10 E-R diagram of the bank. It provides different kinds of bank accounts. and loans. It
operates number of branches.
11 ER Diagram for the School Management System.
12 E-R diagram of Library Management System.
13 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.
• 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:
o The date on which the game is played
o The final result of the match
o 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.
o 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.
14 The Advising Center at a University would like an "Advising Website" to be created
so they can better manage the student requests for advising appointments. The
Advising Center would like to assign appropriate academic or industry advisors to
students based on their expertise.
Every advisor has an area (or areas) of expertise, that is, they are knowledgeable in
providing career advice for particular career areas (e.g. Arts, Education,
Engineering, Healthcare, Marketing, etc...)
3
 Students come to the Advising Center to get advice in a particular career area.
Students who log into the system will have a profile with their basic information –
first name, last name, computingID, phone number, email, and a career area which
they select from a list of career areas.
Both academic and industry advisors have their basic information – first name, last
name, advisorID, office location, phone, email, and area(s) of expertise (area of
expertise taken from the same list of career areas the students have).
Let's assume each student is interested in only one career area (e.g. Engineering)
whereas advisors can have more than one career area listed as their areas of
expertise.
Students can request an advising session and, based on their career area, an
appropriate advisor will be assigned.
An advising session will be scheduled between the student and an advisor at a
specific date and time, in a particular location (either Rice 103, Rice 204, or Rice
304), and for a specific career area (for example, this advising session is to discuss
the Engineering career area). At the end of the advising session, the student has the
opportunity to rate their experience with a simple numeric rating (e.g. 1 to 10) and
a free-form text box.
At various times, the Advising Center would want to gather information about the
number of advising sessions that took place per career area, the total number of
unique students who requested advising sessions (students can request multiple
advising sessions during an academic year), the average rating given, or the advisor
who was rated the highest, etc.
15 Consider the following database scenario for a hospital
Hospital staff consist of doctors and nurses.
Each hospital staff has a staffID, name (first name, middle name, and last name),
and phone numbers.
Doctors have an office and up to 3 specializations.
Doctors have nurses that work with/assist them.
Each nurse has one specialization and belongs to a specific department in the
hospital.
All nurses work with doctors.
Many nurses can work with one doctor.
Doctors sometimes consult with a colleague (who is another doctor).
Doctors treat patients.
Patient information collected by the hospital consists of patientID, name, insurance,
date of admission, and checked out date.
A patient can undergo a number of tests.
Doctors perform tests.
Each test has a unique ID, name, a result, and a date and time the test was performed.
16 ER diagram for each of the following descriptions:
a) Each company operates four departments, and each department belongs to one
company.
4
 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 dependants,
and each dependant 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.
17 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.
18 Read the following case study, which describes the data requirements for a video
rental company. The video rental company has several branches throughout the
USA. The data held on each branch is the branch address made up of street, city,
state, and zip code, and the telephone number. Each branch is given a branch
number, which is unique throughout the company. Each branch is allocated staff,
which includes a Manager. The Manager is responsible for the day-to-day running
of a given branch. The data held on a member of staff is his or her name, position,
and salary. Each member of staff is given a staff number, which is unique throughout
the company. Each branch has a stock of videos. The data held on a video is the
catalog number, video number, title, category, daily rental, cost, status, and the
names of the main actors, and the director. The catalog number uniquely identifies
each video. However, in most cases, there are several copies of each video at a
branch, and the individual copies are identified using the video number.A video is
given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The
status indicates whether a specific copy of a video is available for rent. Before hiring
a video from the company, a customer must first register as a member of a local
branch. The data held on a member is the first and last name, address, and the date
that the member registered at a branch. Each member is given a member number,
which is unique throughout all branches of the company. Once registered, a member
is free to rent videos, up to maximum of ten at any one time. The data held on each
video rented is the rental number, the full name and number of the member, the
video number, title, and daily rental, and the dates the video is rented out and date
returned. The rental number is unique throughout the company.
19 The Hudson Engineering Group (HEG) has contacted you to create a conceptual
model whose application will meet the expected database requirements for the
company’s training program. The HEG administrator gives you the description (see
below) of the training group’s operating environment. The HEG has 12 instructors
and can handle up to 30 trainees per class. HEG offers five “advanced technology”
courses, each of which may generate several classes. If a class has fewer than 10
trainees, it will be cancelled. Therefore, it is possible for a course not to generate
any classes. Each class is taught by one instructor. Each instructor may teach up to 5
 two classes or may be assigned to do research only. Each trainee may take up to two
classes per year.
20 The music database stores details of a personal music library, and could be used to
manage your MP3, CD, or vinyl collection. Because this database is for a personal
collection, it’s relatively simple and stores only the relationships between artists,
albums, and tracks. It ignores the requirements of many music genres, making it
most useful for storing popular music and less useful for storing jazz or classical
music.
We first draw up a clear list of requirements for our database:

 The collection consists of albums.

 An album is made by exactly one artist.
 An artist makes one or more albums.
 An album contains one or more tracks
 Artists, albums, and tracks each have a name.
 Each track is on exactly one album.
 Each track has a time length, measured in seconds.
 When a track is played, the date and time the playback began (to the nearest
second) should be recorded; this is used for reporting when a track was last
played, as well as the number of times music by an artist, from an album, or
a track has been played.