Database Entities and Entity Relationship

Explain the difference between a weak and a strong entity set.

Answer: A strong entity set has a primary key. All tuples(rows) in the set are distinguishable
by that key. A weak entity set has no primary key unless attributes of the strong entity set on
which it depends are included. Tuples in a weak entity set are partitioned according to their
relationship with tuples in a strong entity.
We can convert any weak entity set to a strong entity set by simply adding
appropriate attributes. Why, then, do we have weak entity sets?
Answer: We have weak entities for several reasons:
We want to avoid the data duplication and consequent possible inconsistencies caused by
duplicating the key of the strong entity.
Weak entities reflect the logical structure of an entity being dependent on another entity.
Weak entities can be deleted automatically when their strong entity is deleted.
Weak entities can be stored physically with their strong entities.
When designing an E-R diagram for a particular enterprise, you have several
alternatives from which to choose. What criteria should you consider in making the
appropriate choice?
Answer: The criteria to use are intuitive design accurate e!pression of the real"world
concept and efficiency. A model which clearly outlines the ob#ects and relationships in an
intuitive manner is better than one which does not because it is easier to use and easier to
change. $eciding between an attribute and an entity set to represent an ob#ect and deciding
between an entity set and relationship set influence the accuracy with which the real"world
concept is e!pressed. %f the right design choice is not made inconsistency and&or loss of
information will result. A model which can be implemented in an efficient manner is to be
preferred for obvious reasons.
Entity Relationship Diagram Exercises
'or each of the following narratives draw an ()$ diagram which indicates the entities and
their attributes (if noted). %dentify and label significant relationships between pairs of entities.
Exercise
*reate a ()$ for each of the following descriptions.
a) (ach of the +winby *orporation,s divisions is composed of many departments. (ach of
the departments has many employees assigned to it but each employee works for only
one department. (ach department is managed by one employee and each of these
managers can manage only one department at a time.
b) $uring some period of time a customer can rent many videotapes from the -ig.id
store. (ach of the -ig.id,s videotapes can be rented to many customers during that
period of time.
c) An airline can be assigned to fly many flights but each flight is flown by only one airline.
d) The /wikTite *orporation operates many factories. (ach factory is located in a
region. (ach region can be 0home0 to many of /wikTite,s factories. (ach factory
employs many employees but each of these employees is employed by only one
factory.
e) An employee may have earned many degrees and each degree may have been
earned by many employees.
Exercise !
1ick and +hovel *onstruction *ompany is a multi"state building contractor speciali2ing in
medium"priced town homes. Assume that 1ick and +hovel,s main entities are its customers
employees pro#ects and equipment. A customer can hire the company for more than one
pro#ect and employees sometimes work on more than one pro#ect at a time. (quipment
however is assigned to only one pro#ect at a time. $raw an ()$ showing those entities.
Exercise "
'ast'light Airlines is a small air carrier operating in three north"eastern states. 'ast'light is
in the process of computerising its passenger reservation system. The following data items
have been identified: reservation code, flight number, flight date, origin, destination,
departure time, arrival time, passenger name, seat number, reservation agent
number, and reservation agent name. 'or e!ample flight number 343 which is scheduled
every Tuesday and Thursday leaves Augusta 5aine at 6:73am and arrives in 8ashua
8ew 9ampshire at :4::;am. <ou can assume that the 'ast 'reight reservation system will
detect automatically whether empty seats are available. $raw the (ntity )elationship
$iagram for this system.
Exercise #
1repare an (ntity )elationship $iagram for 1atty,s $aycare *entre. 'or each entity indicate
the primary key by underlining attribute(s) making up primary key and also specify :
additional significant attribute. %dentify and label significant relationships between pairs of
entities (either ::: : : 5 or 5:8).
1atty,s 1layschool is a child daycare centre. A parent registers their child or children at the
school using a registration form. A parent can submit more than one registration form. (ach
room in the daycare is assigned an age group. 'or e!ample an infant is under : yearof age
and toddlers are from : to 3 years of age. A child is assigned to a room based on their age
and availability of space. A room may be assigned one or more employees. An employee
can only be assigned to one room. The minimum number of employees required for a room
is determined by the number of children assigned to the room and the child:staff ratio
identified by the government. 'or e!ample one employee can care for = infants or >
toddlers.

$ore exercises%
:. A large bank operates several divisions. %nformation Technology (%T) is operated as one
of these divisions. Within the %T division are many departments that are managed by one
manager and all %T employees belong to one of these departments. The %T division
assigns it?s employees to one or more on going pro#ects in the bank. A pro#ect may be
planned but not have any employees assigned to it for several months. (ach pro#ect will
have a single employee assigned who acts as a pro#ect leader.
7. A hardware store sells several home workshop products to the public (such as power
saws and sanders). (ach product has several different manufacturers who manufacture
it and prices are different for products made by different manufacurers. (ach time one
or more products are sold to a customer an invoice is created which lists the date items
purchased and their prices and then the total purchase and ta! amounts.
3. The 5inistry of Transportation (5@T) supplies department keeps track of all the items
(furniture and equipment such as a chair or printer) in the 5inistry offices. There are
several 5@T buildings and each one is given a different name to identify it. (ach item is
assigned a unique %$ when it is purchased. This %$ is used to keep track of the item
which is assigned to a room within a building. (ach room within a building is assigned to
a department and each department has a single employee as it?s manager.
A. A cooking club organi2es several dinners for it?s members. The purpose of the club is to
allow several members to get together and prepare a dinner for the other members. The
club president maintains a database that plans each meal and tracks which members
attends each dinner and also keeps track of which members creates each dinner. (ach
dinner serves many members and any member is allowed to attend. (ach dinner has an
invitation. This invitation is mailed to each member. The invitation includes the date of
the dinner and location. (ach dinner is based on a single entrBe and a single dessert.
This entrBe and dessert can be used again for other dinners.
=. A-* *onsulting is a small"si2ed consulting firm in the %T industry. A-*,s business is
managing several +ystems $evelopment pro#ects by assigning staff consultants to these
pro#ects as their skills are needed. (ach employee is designated to have one primary
skill but there may be other employees with the same primary skill. A consultant may
work on one or more pro#ects or may not yet be assigned to a pro#ect. The company
charges for each pro#ect by billing each consultant?s hours worked by the billing rate. The
hourly billing rate is dependant on the employee,s primary #ob skill.
C. A company purchases products and sells them to it?s customers. (ach time a sale
occurs an invoice is created listing the customer name and a list of purchase product
descriptions the supplier name for the products and the price of each product. The
product number identifies each product and will appear again if another customer
purchases the same product. (ach supplier can supply many products which we can
sell but each product has only one supplier.
;. <ou are asked to create a database to produce a report of customer details. The report
is to list the cutomer name account balance credit limit and other customer details. The
report will also list the customer account rep (one of our sales employees). @ur sales
reps manage many customers each but each customer will be managed by only one
account rep at any one time. 9owever your design should allow for customer?s being
managed by many account reps as it is possible that some of our employees may leave
the company " thus requiring new account reps for a customer.
>. A company operates a warehouse parts supply business. The company has several
warehouses located in Toronto which each store several hundreds of automotive parts.
We need to keep a record of how many parts are 0on hand0 " meaning inventory levels
that tell us how many we have for each part. To help us organi2e our parts each part is
assigned a specific classification. There are A classifications that we use to organi2e
hundreds of parts.
6. To keep track of office furniture computers printers and so on the '@D8$%T company
requires the creation of a simple database. (ach piece of office furniture computer or
printer is given an identification number. (ach item is then placed in a room of one of
three buildings. The building manager is responsible for the items in their building.

Reference
http:&&cs.senecac.on.ca&Edbs74:&pages&()$.htm
$ore Exercises &cont'
:. *onstruct an (") diagram for a car"insurance company whose customers own one or
more cars each. (ach car has associated with it 2ero to any number of recorded
accidents.
7. *onstruct an (") 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 e!aminations
conducted.
3. A university registrars office maintains data about the following entities: (a) courses
including number title credits syllabus and prerequisitesF (b) course offerings
including course number year semester section number instructor(s) timings and
classroomF (c) students including student"id name and programF and (d) instructors
including identiGcation number name department and title. 'urther the enrollment of
students in courses and grades awarded to students in each course they are enrolled for
must be appropriately modeled. *onstruct an (") diagram for the registrar,s office.
$ocument all assumptions that you make about the mapping constraints.
Recommended answers%
1. Car insurance tables:
person (driver"id name address)
car (license yearmodel)
accident (report"number date location)
participated(driver"id license report"number damage"amount)
2. ospital tables:
patients (patient"id name insurance date"admitted date"checked"out)
doctors (doctor"id name speciali2ation)
test (testid testname date time result)
doctor"patient (patient"id doctor"id)
test"log (testid patient"id) performed"by (testid doctor"id)
!. "ni#ersity registrar.s tables:
student (student"id name program)
course (courseno title syllabus credits)
course"offering (courseno secno year semester time room)
instructor (instructor"id name dept title)
enrols (student"id courseno secno semester year grade)
teaches (courseno secno semester year instructor"id)
requires (maincourse prerequisite)
A. $esign an (") diagram for keeping track of the e!ploits of your favourite sports team.
<ou should store the matches played the scores in each match the players in each
match and individual player statistics for each match. +ummary statistics should be
modeled as derived attributes.
=. (!tend the (") diagram of the previous question to track the same information for all
teams in a league.
Reference
http:&&www.cs.ucla.edu&classes&fall4A&cs:A3&solutions&chap7.pdf