Electrical & Computer Engineering
EECE 433 Database Systems Assignment 2 Relational Schema
In this assignment, you’ll work in groups of up to 2 students to create relational schemas for 4 cases.
You must use erdplus.com to chart your diagrams. You must also personalize each diagram with your
names through a label. “Un-personalized” diagrams will be revoked regardless of their content.
Plagiarism will be strictly penalized. Please do not share your solutions. 5 points will be dedicated to
the format/presentation of the assignment report.
Exercise 1 Beirut Fashions (20 points)
Beirut Fashions is an exclusive custom fashion designer business. Map the ER diagram for Beirut
Fashions Operations Database into a relational schema. You’ve already created its ERD in a previous
assignment based on the following requirements. The Beirut Fashions Operations Database will keep
track of the following:
• For each designer: a unique designer identifier and unique SSN as well as the name (composed
of first and last name). (1 points)
• For each customer: a unique customer’s identifier as well as his or her name and multiple
phone numbers. (1 points)
• For each tailoring technician: a unique SSN as well as his or her name (composed of first and
last name). (1 points)
• For each outfit: a unique outfit’s identifier as well as the outfit’s planned date of completion
and its unreasonable price. (1 points)
• For each fashion show: a unique show identifier as well as the date of the show and location.
(1 points)
• Each designer designs many (one or more) outfits. Each outfit has only one designer. (3 points)
• Each outfit is sold (in advance) to exactly one customer. Customers can buy one or many
outfits (Beirut Fashions will not keep track of customers who have not made any purchases
yet). (3 points)
• Each tailoring technician must work on at least one outfit but can work on many. Each outfit
has at least one tailoring technician working on it but can have many. (3 points)
• Beirut Fashions will keep track of the date when a tailoring technician started working on a
particular outfit. (3 points)
• Each designer can participate in several fashion-shows but does not have to participate in
any. Each fashion show can feature one or two Beirut Fashions designers. (Beirut Fashions
will not keep track of fashion shows that do not feature Beirut Fashions designers.) (3 points)
Imad Moukadem 1
Exercise 2 Caracalla (25 points)
Caracalla is a rental business that rents musical instruments to bands. Map the ER diagram for
Caracalla Operations Database into a relational schema. You’ve already created its ERD in a previous
assignment based on the following requirements. The Caracalla Operations Database will keep track
of instruments, bands, repair technicians, and shows.
• For each instrument, Caracalla will keep track of a unique instrument serial number as well as
the instrument model and brand, the year when the instrument was made, and the age
(measured in years) of the instrument. (2 points)
• The customers of Caracalla are bands. For each band, Caracalla will keep track of the unique
band name and unique band identifier as well as the band’s address, contact person’s name,
and multiple phone numbers. (2 points)
• Repair technicians maintain the instruments. For each technician, Caracalla will keep track of
a unique SSN as well as a name, address, and multiple phone numbers. (2 points)
• Caracalla will record information about shows that its customer bands perform in. For each
show, it will keep track of a unique show identifier composed of the show venue name and
date. For each show, it will also keep track of show type and show name (a show may or may
not have a name). (2 points)
• A band does not have to rent any instruments but may rent up to 30. Each instrument may
be rented by one band or by no bands at all. (3 points)
• A repair technician maintains one or many instruments. Each instrument is maintained by one
technician. (3 points)
• A band may perform in many shows but does not have to perform in any. Each show must
have at least one band performing but may have many bands performing. For each instance
Imad Moukadem 2
of a band performing in a show, we keep track of the duration (in minutes) of that band’s
performance in that show. (5 points)
• For each band, Caracalla keeps track of the number of shows that the band performs in. (3
points)
• For each show, Caracalla keeps track of the number of bands that participated in the show.
(3 points)
Exercise 3 AUB’s Consulting (25 points)
AUB’s Consulting (AUB Inc) is a consulting company. Map the ER diagram for AUB’s Consulting
Operations Database into a relational schema. You’ve already created its ERD in a previous
assignment based on the following requirements. AUB Inc Operations Database will keep track of the
following:
• For each consultant: a unique cid, as well as the consultant’s name, one or more skills, start
date with the company, and the number of years with company. (2 points)
• For each project manager: a unique pmid, the project manager’s name. (2 points)
• For each customer: a unique custid, well as the customer’s name composed of customer’s
first and last name, and a phone number. (2 points)
• For each supervisor: a unique supid, as well as the supervisor’s name, number of supervised
contracts, and (if supervisor owns AUB Inc. stock) number of stocks owned. (Not every
supervisor owns AUB Inc. stock.) (2 points)
Imad Moukadem 3
• For each contract: a contract id and contract start date. Two or more contracts can have the
same contract id, but all contracts for the same customer will have different contract ids. (2
points)
• Each contract is managed by one project manager. A project manager manages one contract
or no contracts at all. (3 points)
• Each contract has at least three consultants assigned to it. A consultant is assigned to one
contract. (3 points)
• A customer has at least one contract but may have many. Each contract is associated with
one customer. (3 points)
• Each contract is supervised by one supervisor. Each supervisor must supervise at least one
contract but can supervise many contracts. (3 points)
• Each supervisor has one or more consultants assigned as administrative helpers. A consultant
can be assigned as administrative helper to between zero and many supervisors. We keep
track of how many hours per week a particular consultant is working as an administrative
helper for a particular supervisor. (3 points)
Imad Moukadem 4
Exercise 4 Nijme (25 points)
“Nijme” is a professional soccer team. You’ve already written out all requirements for the ER
diagram for the “Nijme” Revenue Database shown below in a previous assignment. Map the ER
diagram for “Nijme” Revenue Database into a relational schema.
Imad Moukadem 5
